Shulou(Shulou.com)06/01 Report--

What is the difference between a pg database and a mysql database? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Differences: 1. MySQL tends to the user's point of view; pg database tends to the theoretical perspective. 2. MySQL generally gives the verification of the validity of the data to the customer; pg database is relatively strict in terms of the difficulty of legitimacy. 3. In the standard implementation of SQL, pg database is more perfect than MySQL, and the functional implementation is more rigorous.

MySQL

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

Initially, MySQL was designed to be a fast Web server backend, using a fast index sequence access method (ISAM) and not supporting ACID. After early and rapid development, MySQL began to support more storage engines and implemented ACID through the InnoDB engine. MySQL also supports other storage engines, providing temporary table functionality (using the MEMORY storage engine), high-speed database reading through the MyISAM engine, as well as other core storage engines and third-party engines.

MySQL has a wealth of documentation, with many good quality free reference manuals, books and online documentation, as well as training and support from Oracle and third-party vendors.

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

Now, there are more databases to choose from based on the original MySQL code, as several core MySQL developers have released MySQL branches. Michael "Monty" Widenius, one of the original founders of MySQL, seems to regret selling MySQL to Sun, so he developed his own MySQL branch, MariaDB, which is free and based on the GPL license. Drizzle, a branch created by the well-known MySQL developer Brian Aker, has done a lot of rewriting, especially for multi-CPU, cloud, web applications and high concurrency.

PostgreSQL

PostgreSQL boasts itself as the most advanced open source database in the world. Some fans of PostgreSQL say it is comparable to Oracle and is not so expensive and arrogant customer service. It has a long history and was first 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 1000 contributors worldwide. It provides a single, fully functional version, unlike MySQL, which offers multiple different community, business, and enterprise editions. PostgreSQL is based on a free BSD/MIT license that allows organizations to use, copy, modify, and redistribute code, as long as a copyright is provided.

Reliability is the highest priority of PostgreSQL. It is known for its rock-solid quality and good engineering, supporting high-transaction, mission-critical applications. PostgreSQL is not always well documented, providing a large number of free online manuals and archived reference manuals for older versions. PostgreSQL's community support is great, as well as business support from independent vendors.

Data consistency and integrity are also high priority features of PostgreSQL. PostgreSQL fully supports ACID features. It provides a strong security guarantee for database access and makes full use of enterprise security tools such as Kerberos and OpenSSL. You can define your own checks and ensure data quality according to your own business rules. Among the many management features, point-in-time recovery (PITR) is a great feature, a flexible and highly available feature that provides capabilities such as creating hot backups and snapshots and restores against failed restores. But that's not what PostgreSQL is all about. The project also provides several ways to manage PostgreSQL for high availability, load balancing, replication, and so on, so you can use features 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 every day, as well as Reddit and Disqus

Both 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 of testing them is your time and hardware. They are flexible and scalable and can be used on small and large distributed systems. MySQL goes a step further than PostgreSQL in one area, that is, its tentacles extend to the embedded domain, which is achieved through libmysqld. PostgreSQL does not support embedded applications and still adheres to the traditional client / server architecture.

MySQL is generally regarded as a fast database back-end for websites and applications, which can perform fast reading and a large number of query operations, but it is not satisfactory in terms of complex features and data integrity checking.

PostgreSQL is a serious and fully functional database for transactional enterprise applications, supporting strong ACID features and many data integrity checks. Both of them have very fast speed on some tasks, and the behavior of different storage engines of MySQL is quite different. The MyISAM engine is the fastest because it performs few data integrity checks and is suitable for sites with more back-end read operations, but it is a disaster for read / write databases that contain sensitive data because MyISAM tables can eventually become corrupted. MySQL provides tools for repairing MySQL tables, but InnoDB that supports the ACID feature is a better choice for sensitive data.

PostgreSQL, by contrast, is a fully integrated database with a single storage engine. You can improve performance by adjusting the parameters of the postgresql.conf file, or you can 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 adding additional functionality through extensions.

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

Standard compatibility

PostgreSQL is designed to achieve SQL compatibility (the current standard is ANSI-SQL:2008). MySQL is compatible with most SQL, but has its own extensions to support NoSQL features, which are described in the reference manual. Each approach has its advantages and disadvantages. Compatibility with standards makes database administrators, database developers, and application developers more comfortable because it means they only need to learn one set of standards, one set of features, and one set of commands. This will save time, improve efficiency, and will not be targeted at specific vendors.

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

The difference between MySQL and PostgreSQL (pg database)

MySQL is the DBMS; created by the application developer and PostgreSQL is the DBMS created by the database developer.

In other words, MySQL tends to answer the question "what problem do you want to solve" from the user's perspective, while PostgreSQL tends to answer the question "how the database should solve the problem" from a theoretical point of view.

MySQL generally gives the verification of data validity to customers; PostgreSQL is more strict in terms of the difficulty of legitimacy. For example, when you insert "2012-02-30" into MySQL, it will succeed, but the result will be "0000-00-00"; PostgreSQL does not allow this value.

In general, PostgreSQL is considered feature-rich, while MySQL is considered faster. But this view is basically in the case of MySQL 4.x / PostgreSQL 7.x, and things have changed. PostgreSQL has greatly improved the speed of version 9.x, while 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 these two functions.

These two database systems can be optimized and customized according to the situation of the application, so it is difficult to say exactly which performance is better. The MySQL project started with a focus on speed, while PostgreSQL started with features and specification standards.

Advantages of PostgreSQL over MySQL

1. The standard implementation of SQL is more perfect than that of MySQL, and the functional implementation is more rigorous.

2. The functional support of stored procedures is better than that of MySQL, and it has the ability of local cache to execute plans.

3. The table join is fully supported, the function of the optimizer is complete, many index types are supported, and the complex query ability is strong.

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

5. The active and standby replication of PG belongs to physical replication. Compared with MySQL binlog-based logical replication, the consistency of data is more reliable, the replication performance is higher, and the impact on host performance is less.

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

Advantages of MySQL over PG:

1. Innodb's MVCC mechanism based on rollback segment is superior to XID-based MVCC mechanism in which PG new and old data are stored together. When the new and old data are stored together, the VACUUM needs to be triggered regularly, which will bring redundant IO and database object locking overhead, resulting in a decline in the overall concurrency ability of the database. And VACUUM cleaning is not timely, it may also lead to data inflation.

2. MySQL uses indexes to organize tables, which is very suitable for query and deletion operations based on primary key matching, but there are constraints on table structure design.

3. The optimizer of MySQL is simple, and the implementation of system tables, operators and data types is very concise, which is very suitable for simple query operations.

4. The implementation of MySQL partition table is better than that of PG based on inheritance table, which is mainly reflected in the great difference in processing performance when the number of partitions reaches tens of thousands.

5. The plug-in mechanism of MySQL storage engine makes its application scenarios more extensive. For example, except that innodb is suitable for transaction processing scenarios, myisam is suitable for static data query scenarios.

Thank you for reading! After reading the above, do you have a general understanding of the difference between pg database and mysql database? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.