Second, any system has its performance limit, in the high concurrency read/write, load close to the limit, PG's performance indicators can still maintain a hyperbolic or even logarithmic curve, to the peak after no longer decline, while the MySQL clearly appear a peak after the decline (after version 5.5, there is a plugin in the enterprise version can be improved a lot, but need to pay for it).
Three.
PG has been dominant in GIS for many years because of its wealth of geometry types, in fact more than just geometry, PG has tons of dictionaries, arrays, bitmaps
and other data types, compared to mysql, which is much worse. instagram is the result of PG's spatial database extension, POSTGIS, which is far better than MYSQL. instagram adopted PGSQL because PG's spatial database extension POSTGIS is much stronger than MYSQL's my
spatial.
Four, PG's "no locking" feature is very prominent, even including vacuum such as organizing data space operations, this and PGSQL's MVCC implementation has something to do.
fifth, PG can use functions and conditional indexes, which makes the PG database tuning is very flexible, mysql does not have this feature, conditional indexes in the web application is very important.
Sixth,
PG has extremely strong SQL programming capabilities (9.x
Turing complete, support for recursion!)
PG has extremely strong SQL programming capabilities (9.x
Turing ready, recursive support!), there is a very rich support for statistical functions and statistical syntax, such as analytic functions (ORACLE's call, PG is called the window function), but also can be used in a variety of languages to write the
storage process, for the support of the R is also very good. At this point, MYSQL is far worse, many analytics are not supported, Tencent's internal data storage is mainly MYSQL, but data analysis is mainly
HADOOP + PGSQL.
Seven, PG has a variety of cluster architectures to choose from, plproxy can support statement-level mirroring or slicing, slony can be set up for field-level synchronization, standby can be built on the basis of a cluster architecture, plproxy can be used to support statement-level mirroring or slicing, slony can be used to build the field-level synchronization, standby can be used to build a cluster architecture. synchronization settings, standby can build WAL file-level or streaming read/write separation cluster, synchronization frequency and cluster policy adjustment is convenient, the operation is very simple.
VIII.
General relational database strings have a limited length of 8k or so, unlimited TEXT type of functionality is limited, only as an external big data access. PG's TEXT
type can be directly accessed, SQL syntax built-in regular expressions, can be indexed, but also full-text search, or use xml xpath. with PG, the document database can be saved.
nine, for
WEB applications, the characteristics of the replication is very important, mysql is now asynchronous replication, pgsql can do synchronous, asynchronous, semi-synchronous replication. There is also mysql synchronization is based on
binlog replication, similar to oracle golden
gate, is based on stream replication, to achieve synchronization is very difficult, this way is more suitable for off-site replication, pgsql replication based on wal, you can do synchronous replication. At the same time, pgsql also
provides stream replication.
Ten, pgsql's support for numa architecture is a bit stronger than mysql's, and it's a bit better than MYSQL's performance for reads. pgsql commits can be completely asynchronous, whereas mysql's in-memory tables aren't practical enough (because of table locks)
Lastly, here's a couple of things that I feel are not as good as MySQL's about PG.
First, MySQL has some useful ops support like slow-query.log , which PG can definitely customize out of the box, but it would be nice if it could be configured to use it.
The second is mysql's innodb engine, which can fully optimize the use of all the system's memory, and PG's less-than-full use of memory under mega-memory.
The third point is that MySQL replication can be done with multi-level slave repositories, but prior to 9.2 PGSQL could not be used with a slave with a slave repository.
Fourth point, from the test results, mysql 5.5 performance improvement is very big, standalone performance is stronger than pgsql, 5.6 should be much stronger.
Fifth, for web applications, mysql 5.6's built-in MC API functionality is very good, PGSQL is worse.
Some other things:
Both pgsql and mysql have commercial companies behind them, and neither is a company. Most developers, for the most part, are paid a salary.
To say that mysql executes much faster than pgsql is not true, the speeds are close, and a lot depends on your configuration.
For stored procedures, functions, views and such, both databases can now support them.
Also there is no absolute good or bad between multi-threaded and multi-process architecture, oracle is multi-process on unix and multi-threaded on windows.
Many pg apps are also 24/7 apps like skype. Recent versions of VACUUM basically don't affect PGSQL running, after 8.0 PGSQL can run on windows without cygwin.
As for transaction support, mysql and pgsql are fine.