Second, any system has its performance limit. In the case of high concurrent reading and writing, and the load is close to the limit, the performance index of PG can still keep hyperbola or even logarithmic curve, and it will not drop after reaching the peak, while MySQL will obviously drop after a peak (after version 5.5, there is a plug-in in enterprise edition that can be improved a lot, but it needs to be paid).
Thirdly, PG has been in an advantageous position in the field of GIS for many years, because it is rich in geometric types, in fact, it is more than geometric types. PG has a large number of data types, such as dictionaries, arrays, bitmaps and so on. Compared with mysql, it is much worse. Instagram adopts PGSQL because PG's spatial database extension POSTGIS is far stronger than MYSQL's my spatial.
Fourthly, the "lock-free" feature of PG is very prominent, even including the operation of arranging data space like a vacuum, which is related to the MVCC implementation of PGSQL.
Fifth, PG can use functions and conditional indexes, which makes the tuning of PG database very flexible. mysql does not have this function, and conditional indexes are very important in web applications.
Sixth, PG has strong SQL programming ability (9.x Turing is complete and supports recursion! ), there are very rich statistical functions and statistical grammar support, such as analysis function (the name of Oracle, called window function in PG), you can also write stored procedures in many languages, and also support R very well. At this point, MYSQL is far behind, and many analysis functions are not supported. The internal data storage of Tencent is mainly MYSQL, but the data analysis is mainly HADOOP+PGSQL (I heard that it is from Li Yuanjia, but it has not been verified).
Seven, PG has a variety of cluster architectures to choose from. plproxy can support statement-level mirroring or fragmentation, slony can perform field-level synchronization settings, and standby can build a WAL file-level or streaming read-write separation cluster. Synchronization frequency and cluster strategy can be easily adjusted, and the operation is very simple.
Eight, the string length of general relational database is limited, about 8k, and the function of infinite text type is limited, so it can only be accessed as external big data. The text type of PG can be accessed directly, and the SQL syntax has built-in regular expressions, which can be indexed, full-text searched or used xml xpath. With PG, you can save the document database.
Nine, for WEB applications, the characteristics of replication are very important. Mysql is also asynchronous replication now, and pgsql can achieve synchronous, asynchronous and semi-synchronous replication. Also, mysql synchronization is based on binlog replication, similar to oracle Golden Gate, based on stream replication. It is difficult to achieve synchronization, which is more suitable for remote replication. The replication of pgsql is based on wal, which can realize synchronous replication. At the same time, pgsql also provides streaming replication.
X. pgsql supports numa architecture better than mysql, and its reading performance is better than MYSQL. Pgsql submission can be completely asynchronous, but mysql's memory table is not practical (because of table lock).