What is the difference between pg database and mysql

The difference between PG and MySQL: 1. PG supports more data types, while MySQL does not support JSON and XML data types; 2. PG has higher scalability, while MySQL has poor scalability ; 3. PG only supports a single storage engine, while MySQL supports multiple storage engines; 4. PG is more suitable for complex queries and high concurrency situations, and in specific usage scenarios, MySQL may have better performance, etc. PG and MySQL are both excellent relational database management systems. Choose according to specific needs and usage scenarios.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

PostgreSQL (often called PG) and MySQL are both widely used relational database management systems (RDBMS). Although they are both relational databases used to store and manage data, they are very different in some aspects, as described below:

1. Data types: PostgreSQL supports more data types, such as ranges Types, network address types, JSON and XML data types, etc. MySQL does not support these types.

2. Extensibility: PostgreSQL is highly extensible and can use custom data types, functions and operators to extend its functionality. MySQL has poor scalability and requires the use of plug-ins or stored procedures to achieve similar functions.

3. ACID compatibility: PostgreSQL is a fully ACID compatible database, while MySQL only supports ACID in specific storage engines (such as InnoDB).

4. Multi-version Concurrency Control (MVCC): PostgreSQL uses MVCC to provide an advanced transaction isolation level, which means that multiple transactions can read and write to the database at the same time without interfering with each other. MySQL also supports MVCC, but its implementation is different.

5. Storage engine: MySQL supports multiple storage engines, including MyISAM and InnoDB. Each storage engine has its own characteristics, advantages and disadvantages. PostgreSQL only supports a single storage engine.

6. SQL standard compatibility: PostgreSQL is more compliant with SQL standards, while MySQL adopts its own implementation in some aspects.

7. Performance: In some specific usage scenarios, MySQL may perform better. But in the case of complex queries and high concurrency, PostgreSQL may perform better.

In short, PG and MySQL are both excellent relational database management systems, with different characteristics, advantages and disadvantages. Which one to choose depends on specific needs and use cases.

MySQL

MySQL is relatively young, first appearing in 1994. It claims to be the most popular open source database. MySQL is the M in LAMP (a software package for web development, including Linux, Apache, and Perl/PHP/Python). Most applications built on the LAMP stack use MySQL, including well-known applications such as WordPress, Drupal, Zend, and phpBB.

From the beginning, MySQL was designed to be a fast web server backend, using the fast Index Sequential Access Method (ISAM) and not supporting ACID. After rapid early development, MySQL began to support more storage engines and implemented ACID through the InnoDB engine. MySQL also supports other storage engines, provides the function of temporary tables (using the MEMORY storage engine), and realizes high-speed reading of the database through the MyISAM engine. In addition, there are other core storage engines and third-party engines.

MySQL has very rich documentation, including many high-quality free reference manuals, books and online documents, as well as training and support from Oracle and third-party vendors.

MySQL has experienced changes in ownership and some dramatic events in recent years. It was originally developed by MySQL AB and then sold to Sun for $1 billion in 2008. Sun was acquired by Oracle in 2010. Oracle supports multiple versions of MySQL: Standard, Enterprise, Classic, Cluster, Embedded and Community. Some of them are free to download, while others are paid. Its core code is based on the GPL license, and commercial licenses are available for developers and manufacturers who do not want to use the GPL license.

Now, there are many more databases to choose from based on the original MySQL code, because several core MySQL developers have released MySQL forks. One of the original MySQL creators, Michael "Monty" Widenius, seemed to regret selling MySQL to Sun, so he developed his own MySQL fork, MariaDB, which is free and licensed under the GPL. Drizzle, a branch created by the well-known MySQL developer Brian Aker, has been extensively rewritten, especially optimized for multi-CPU, cloud, network applications and high concurrency.

PostgreSQL

PostgreSQL bills itself as the world’s most advanced open source database. Some fans of PostgreSQL say it's comparable to Oracle, but without the hefty price tag and arrogant customer service. It has a long history, originally developed at the University of California, Berkeley, in 1985 as a successor to the Ingres database.

PostgreSQL is a completely community-driven open source project maintained by more than 1,000 contributors around the world. It provides a single fully functional version, unlike MySQL which provides multiple different community editions, commercial editions and enterprise editions. PostgreSQL is based on the free BSD/MIT license, and organizations can use, copy, modify, and redistribute the code as long as they provide a copyright notice.

Reliability is PostgreSQL's highest priority. It is known for its rock-solid quality and well-engineered support for high-transaction, mission-critical applications. PostgreSQL's documentation is very good, with a large number of free online manuals and archived reference manuals for older versions. PostgreSQL's community support is excellent, as is commercial support from independent vendors.

Data consistency and integrity are also high-priority features of PostgreSQL. PostgreSQL fully supports ACID features, provides strong security guarantees for database access, and makes full use of enterprise security tools, such as Kerberos and OpenSSL. You can define your own checks to ensure data quality based on your own business rules. Among the many management features, point-in-time recovery (PITR) is a great feature. It is a flexible high-availability feature that provides the ability to create hot backups and snapshots and restores for failure recovery. But this is not all of PostgreSQL. The project also provides several methods to manage PostgreSQL to achieve high availability, load balancing, replication, etc., so that you can use the functions that suit your specific needs.

Platform

Both MySQL and PostgreSQL appear on some high-traffic Web sites:

MySQL: Slashdot, Twitter, Facebook and Wikipedia

PostgreSQL: Yahoo uses a modified PostgreSQL database to handle the billions of events per day, as well as Reddit and Disqus

Both MySQL and PostgreSQL run on multiple operating systems, such as Linux, Unix, and Mac OS X with Windows. They are all open source and free, so the only cost when testing them is your time and hardware. They are flexible and scalable and can be used on both small systems and large distributed systems. MySQL goes further than PostgreSQL in one area, that is, its tentacles extend to the embedded field, which is achieved through libmysqld. PostgreSQL does not support embedded applications and still adheres to the traditional client/server architecture.

MySQL is generally considered to be a fast database backend for websites and applications, capable of fast reading and large number of query operations, but it is not satisfactory in terms of complex features and data integrity checking.

PostgreSQL is a serious, full-featured database for transactional enterprise applications, supporting strong ACID features and many data integrity checks. Both of them are very fast on certain tasks, and the behavior of different MySQL storage engines is quite different. The MyISAM engine is the fastest because it only performs few data integrity checks. It is suitable for sites with a lot of back-end read operations, but it is a disaster for read/write databases containing sensitive data because the MyISAM table It may eventually become damaged. MySQL provides tools to repair MySQL tables, but for sensitive data, InnoDB that supports ACID features is a better choice.

In contrast, PostgreSQL is a fully integrated database with a single storage engine. You can improve performance by adjusting parameters in the postgresql.conf file, as well as adjust queries and transactions. The PostgreSQL documentation provides a very detailed introduction to performance tuning.

Both MySQL and PostgreSQL are highly configurable and can be optimized for different tasks. They all support extensions to add additional functionality.

A common misunderstanding is that MySQL is easier to learn than PostgreSQL. Relational database systems are very complex, and the learning curves of these two databases are actually similar.

Standards Compatibility

PostgreSQL aims for SQL compatibility (the current standard is ANSI-SQL:2008). MySQL is compatible with most SQL, but it also has its own extensions that can support NoSQL features, which are introduced in the reference manual. There are pros and cons to each approach. Compliance with standards makes database administrators, database developers, and application developers more comfortable because it means they only have to learn one set of standards, features, and commands. This will save time, improve efficiency, and not be locked into a specific vendor.

People who support the use of non-standard custom functionality argue that this allows new features to be adopted quickly without having to wait for the standards process to complete. The ANSI/ISO standard is constantly evolving, so standard compatibility is also a changing target: the well-known relational databases Microsoft SQL Server, Oracle and IBM DB2 are only partially compatible with the standard.

The difference between MySQL and PostgreSQL (pg database)

MySQL is a DBMS created by application developers; while PostgreSQL is a DBMS created by database developers.

In other words, MySQL tends to the user's perspective and answers the question "What problem do you want to solve"; while PostgreSQL tends to the theoretical perspective and answers the question "How should the database solve the problem" ".

MySQL generally leaves data legality verification to the customer; PostgreSQL is stricter in terms of legality. For example, when inserting the time "2012-02-30" into MySQL, it will succeed, but the result will be "0000-00-00"; PostgreSQL does not allow this value to be inserted.

Generally, PostgreSQL is considered feature-rich, while MySQL is considered faster. But this point of view is basically a matter of MySQL 4.x / PostgreSQL 7.x. Now the situation has changed. PostgreSQL has greatly improved in speed in version 9.x, and MySQL features are also increasing.

Architecturally, MySQL is divided into two layers: the upper SQL layer and several storage engines (such as InnoDB, MyISAM). PostgreSQL has only one storage engine that provides both features.

Both of these two database systems can be optimized and customized according to the application situation. It is difficult to accurately say which one has better performance. The focus of the MySQL project was on speed from the beginning, while the focus on PostgreSQL was on features and specifications from the beginning.

Advantages of PostgreSQL over MySQL

1. The standard implementation of SQL is better than MySQL, and the function implementation is more rigorous;

2. Functional support for stored procedures Better than MySQL, it has the ability to cache execution plans locally;

3. It has complete support for table connections, complete optimizer functions, supports many index types, and has strong complex query capabilities;

4. The main table of PG is stored in a heap table, while MySQL uses an index to organize the table, which can support a larger amount of data than MySQL.

5. PG's primary and secondary replication is physical replication. Compared with MySQL's binlog-based logical replication, data consistency is more reliable, replication performance is higher, and the impact on host performance is smaller.

6. MySQL's storage engine plug-in mechanism has the problem of complex locking mechanisms affecting concurrency, but PG does not exist.

MySQL’s advantages over PG:

1. Innodb’s MVCC mechanism based on rollback segments is superior to the XID-based MVCC mechanism in which PG’s old and new data are stored together. . New and old data are stored together, and VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, causing the overall concurrency capability of the database to decrease. Moreover, if VACUUM is not cleaned up in time, it may cause data expansion;

2. MySQL uses indexes to organize tables. This storage method is very suitable for queries and delete operations based on primary key matching, but there are constraints on the table structure design;

3. MySQL's optimizer is relatively simple, and the implementation of system tables, operators, and data types is very streamlined, which is very suitable for simple query operations;

4. The implementation requirements of MySQL partition tables The inheritance table-based partition implementation that is superior to PG is mainly reflected in the large difference in processing performance when the number of partitions reaches thousands or tens of thousands.

5. MySQL's storage engine plug-in mechanism makes its application scenarios more extensive. For example, in addition to innodb being suitable for transaction processing scenarios, myisam is suitable for static data query scenarios.

The above is the detailed content of What is the difference between pg database and mysql. For more information, please follow other related articles on the PHP Chinese website!