PostgreSQL vs MySQL

读书笔记：

https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/

https://www.quora.com/Why-is-MySQL-more-popular-than-PostgreSQL

先看了一篇文章比较了pg和mysql，结论是pg在数据仓库场景，复杂sql性能，语法支持比mysql更强大。而mysql更专注于web应用。下面是具体的对比：

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.) Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. 

注：pg提到WAL，不是啥新技术。

那么为什么mysql比pg更流行呢？从quora上找了篇文章。原因大致如下：早期竞争的时候，mysql安装更简单，各种支持更完善，最终市场占有率大，dba更多。

1，A huge component is community.  MySQL has so many more projects around it for administration, access and enhancement.  MySQL Replication was also something that was done easy and did not require senior staff. (or so people thought, until a year later they realized the asynchronous nature created drift).  Until the recent editions, Postgres required 3rd party tools for replication, such as slony.

Good Postgres DBAs are harder to find than MySQL as well, which can be a key component.  I believe Postgres is a great RDBMS.  Performant, richly featured and proven.  However, if I can't get a team to support Postgres, why choose it?  I'm lucky that I have good Postgres experience on my DBA team at Palomino, but we don't get to flex our muscles much because there aren't many opportunities.

Between the community, replication, and staffing, MySQL makes a more compelling choice for many smaller companies.

2，PostgreSQL was slow to produce a native Win32 build.  Their solution for the Windows platform for years was to tell people to build from source on Cygwin, and it might work, but you're on your own.  They eventually offered a Windows build, but by then they were already far behind in marketshare.  At the time the relative marketshare ratio between the two was about 4:1 in favor of MySQL.

It was also much easier to get started learning MySQL.  Getting it running out of the box and connecting as a default user was so simple anyone could do it.  Getting PostgreSQL configured, creating a user, etc. involved many steps.

And finally, as usual, it comes down to marketing.  MySQL executed an inbound marketing strategy very well, creating community and an ecosystem of companion products that increased adoption.  The online manual with comments, the forums, the forge, the meetups and conferences, all served to welcome people who were interested.  The PostgreSQL community has a lot of great people in it, but by comparison it felt indifferent.

3，MySQL is more popular than PostgreSQL for historical reasons. These are the major ones (in retrospect):

• MySQL was more leaner and faster in some (widely used) use cases since it had less features.

• Even though it was not the best, MySQL's replication system was very simple to setup and maintain.

• MySQL provided various storage engines which helped early adopters configure the database for various workloads without changing the DB design nor the application code. For a  fast but less feature rich application like analytics one would use MyISAM while one would use InnoDB for applications that required high performance transactions and ACID. MySQL made it very easy to achieve this by switching the storage engines without any change to the DB design.

其实个人看来，pg并没有颠覆的特性足以撼动mysql的地位，所以导致不温不火的原因吧。