What is the difference between pg database and mysql?

Difference: 1. MySQL prefers the user's perspective; pg database prefers the theoretical perspective. 2. MySQL generally leaves data legality verification to customers; pg database is more strict in terms of legality. 3. In terms of SQL standard implementation, pg database is more complete than MySQL, and its function implementation is more rigorous.

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 temporary table functions (using the MEMORY storage engine), and implements high-speed read databases 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 hundreds of millions of events per day, as well as Reddit and Disqus

MySQL and PostgreSQL can run on multiple operating systems, such as Linux, Unix, Mac OS X and 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 created by database developers Created DBMS.

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 , The function support of stored procedures is better than MySQL, and it has the ability to cache execution plans locally;

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

4. PG main table 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.

Advantages of MySQL over PG:

1. Innodb's MVCC mechanism based on rollback segments is superior to the XID-based MVCC mechanism in which PG's new and old 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.

Related recommendations: "mysql tutorial"

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!