Comparison of MySQL and PostgreSQL (Reference)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.


Comparison of MySQL and PostgreSQL, which to choose

In order to understand the difference between PostgreSQL and MySQL, I search the key words: MySQL vs PostgreSQL, and see some of the first page. The following is a brief summary:

The difference between MySQL and PostgreSQL

MySQL application developers to create out of DBMS; and PostgreSQL is created by the developers of DBMS database [1].

In other words, MySQL tends to user's point of view, to answer the question "what is the problem you want to solve"; while PostgreSQL tend to view, answer the question of "how to solve the problem of database" [2].

MySQL in general will be verifying the validity of the data to the client; PostgreSQL in the legitimacy to do more strict . For example, MySQL insert " 2012-02-30" this time, will be successful, but the result will be " 0000-00-00" PostgreSQL; is not allowed to insert thisvalue.

Typically, PostgreSQL is thought to feature rich, while MySQL is considered faster. But the point is in the MySQL 4.x / PostgreSQL 7.x thing, now the situation has changed, PostgreSQL has been greatly improved in the 9.x version of speed, and MySQL characteristics are also on the increase[3].

The structure, MySQL is divided into two layers: the upper SQL layer and several storage engines (such as InnoDB, MyISAM). PostgreSQL is only one of the storage engine to provide these two functions.

The two database systems can be applied in the context is optimization, customization, precisely what better performance is difficult to. MySQL project began to focus on speed, and PostgreSQL began to focus on the characteristics and specifications.

Choose which?

May be due to historical reasons, MySQL is more prevalent in some developers. Haven't heard of PostgreSQL at least when we go to school, was not MS SQL Server is MySQL, while the MySQL is open source. In fact PostgreSQL until 8 official support for Windows system.

If not what historical reasons (such as the system is based on MySQL years), or technical accumulation (MySQL master many colleagues), so I think PostgreSQL will not be wrong.

Interestingly, I search for " Google switch PostgreSQL to; mysql", results in the first page is full of " Switch to PostgreSQL from MySQL" second pages, one is from PostgreSQL to MySQL, but only one, but the reason is not to say that PostgreSQL is not good, but because the MySQL experience more[4].

Appendix: a simple test

Finally I attach oneself toMySQL v5.5 And PostgreSQL v9.1 In Mac OS, a simple test on ML. MySQL is installed with brew, PostgreSQL is used in Heroku to provide app green version. Because they are installed by default, the default configuration, so the comparison results only for reference.

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 null
Indexes:
    "tweets_tweet_test_pkey" PRIMARY KEY, btree (id)

The test data is 100W real Twitter data:

$ head -n4 out_pg.sql INSERT INTO tweets_tweet_test VALUES (287343655826624513, '2013-01-05 07:44:00+08', 'KevinPetrollini', 395657199, 'Kevin', '', 'zh', 'web', 'Give me the whip. ',' ', 0,' '); INSERT INTO tweets_tweet_test VALUES (287142303481597952,'2013-01-04 18:23:54+ 08','mikaweixian', 65584780,'M I K A.', '','zh','Instagram', 'I don't know travel back every day so stuffy I is not used to a person
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Ignatz at December 11, 2013 - 8:43 AM