Comparison between mysql and postgresql _ MySQL

Comparison between mysql and postgresql (for reference) bitsCN.com

To understand the difference between PostgreSQL and MySQL, I searched for the keyword MySQL vs PostgreSQL and read several articles on the first page. The following is a summary:

Difference between MySQL and PostgreSQL

MySQL is the DBMS created by application developers, while PostgreSQL is the DBMS [1] created by database developers.

In other words, MySQL tends to be user-oriented. The answer is "What problem do you want to solve", while PostgreSQL tends to be theoretical, the answer is "how should the database solve the problem" [2].

MySQL generally submits the data validity verification to the customer. PostgreSQL is more rigorous in terms of legality. For example, when "" is inserted in MySQL, it will succeed, but the result will be "0000-00-00". PostgreSQL cannot insert this value.

In general, PostgreSQL is considered to have many features, while MySQL is considered to be faster. However, this idea is basically about MySQL 4.x/PostgreSQL 7.x. now the situation has changed. the speed of version x has been greatly improved, and MySQL features are also being added [3].

In terms of architecture, MySQL is divided into two layers: the upper SQL layer and several storage engines (such as InnoDB and MyISAM ). PostgreSQL only has one storage engine to provide these two features.

Both database systems can be optimized and customized for application scenarios, so it is difficult to accurately describe which performance is better. MySQL projects focus on speed at the beginning, while PostgreSQL focuses on features and standards at the beginning.

Which one is selected?

It may be due to historical reasons that MySQL is more popular among developers. At least we never heard of PostgreSQL at school. at that time, it was neither ms SQL Server nor MySQL, But MySQL was open source. In fact, PostgreSQL does not officially support Windows until 8.0.

If there are no historical reasons (for example, the system has been based on MySQL for many years), or the reasons for technical accumulation (many MySQL experts among colleagues), then I don't think there is anything wrong with PostgreSQL.

Interestingly, when I search for "switch postgresql to mysql" on Google, the first page in the result is "Switch to PostgreSQL from MySQL ", on the second page, there is finally one from PostgreSQL to MySQL, but there is only one, and the reason is not that PostgreSQL is not good, but because of the author's MySQL experience [4].

Appendix: Simple Test

Finally, attach my ownMySQL v5.5AndPostgreSQL v9.1Simple tests on Mac OS and ML. MySQL is installed with brew, and PostgreSQL uses the green version of the app provided by Heroku. Because they are both installed by default and configured by default, the comparison results are for reference only.

Table structure:

Column       |           Type           | Modifiers -------------------+--------------------------+----------- id                | bigint                   | not null created_at        | timestamp with time zone | not null from_user         | character varying(40)    | not null from_user_id      | bigint                   | not null from_user_name    | character varying(40)    | not null geo               | character varying(256)   | not null iso_language_code | character varying(8)     | not null source            | character varying(64)    | not null text              | character varying(170)   | not null to_user           | character varying(40)    | not null to_user_id        | bigint                   | not null to_user_name      | character varying(40)    | not nullIndexes:    "tweets_tweet_test_pkey" PRIMARY KEY, btree (id)

The test data is million lines of real Twitter data:

$ Head-n4 out_pg.sqlINSERT INTO tweets_tweet_test VALUES (287343655826624513, '2017-01-05 07:44:00 + 08', 'kevinpetrollini ', 2013, 'Kevin', ', 'zh ', 'web', 'Give me the whip. ', '', 0,''); insert into tweets_tweet_test VALUES (287142303481597952, '2017-01-04 18:23:54 + 08', 'mikaweixian', 2013,'m I K. ', '', 'zh', 'Instagram ', 'Unknown