Most comparisons between MySQL and PostgreSQL only go so far as to look at syntax differences and include benchmark screenshots. The things that really concern a database administrator such as how each database engine performs when under maintenance load, what occurs during a schema change at 2 AM, and actually achieving zero data loss are rarely covered. The following eight differences matter a great deal when you're the one who has to answer the phone.
The vacuum prevents writes from taking place, whereas InnoDB purge does not.
PostgreSQL's VACUUM (together with autovacuum) recovers space from dead tuples, and a complete VACUUM acquires an exclusive lock which prevents writes throughout the process. MySQL's InnoDB purge thread removes the old row versions in the background without stopping writes, but it does not reclaim disk space as PostgreSQL's VACUUM does; deleted rows are simply marked as free internally. It's a different compromise, addressing the same fundamental issue (MVCC has to eventually get rid of old versions), and as a result it affects how you adjust your settings for situations with a high volume of writes. With PostgreSQL, a typical solution is to reduce the autovacuum scale factor to 0.01 (so that a vacuum is initiated when 1% of the tuples are dead rather than at the default 20%) in order to have it run more frequently in smaller chunks rather than making one long and painful pass.
Postgres features a true doomsday clock: transaction ID wraparound.
People who have only used MySQL are surprised by this. PostgreSQL makes use of 32-bit transaction IDs, offering about four billion possible values, and if autovacuum doesn't succeed in freezing old transaction IDs quickly enough, a wraparound will eventually occur and the database will be forced into a read-only state that is only accessible in an emergency situation in order to protect data integrity. It's worth actually keeping an eye on this: whenever the age of the oldest unfrozen XID in any database exceeds 1.5 billion, that should be taken as a sign to stop what you're doing and deal with the situation. MySQL avoids the problem altogether by using 64-bit transaction IDs, which in turn means one fewer pager alert to concern oneself with.
Three dot Row-Level Security is a feature that is specific to Postgres when dealing with multi-tenant data.
When you are developing a multi-tenant system, Postgres provides Row-Level Security (RLS) which enables you to apply per-row access policies directly within the database, so that a query initiated by tenant A will never actually return rows belonging to tenant B, with enforcement taking place at the database level rather than relying solely on application code. MySQL does not have any real equivalent to this feature. If your current arrangement depends entirely on application logic to keep the different tenants' data separate, then this should be given serious consideration.
Zero-downtime schema changes work very differently on each engine.
The engines no longer lock the table in all cases, although the mechanisms are entirely different. MySQL makes use of external tools: gh-ost reads the binlog events and rebuilds the table without having to use a trigger (with built-in throttling), whereas pt-online-schema-change achieves this by means of triggers on a shadow table. PostgreSQL, by contrast, wraps most DDL operations in a transaction which can be rolled back immediately, CREATE INDEX CONCURRENTLY creates an index without blocking writes (even though it requires scanning the table twice), and since version 11 adding a column with a non-volatile default has become instantaneous rather than requiring the entire table to be rewritten.
The pg_hba.conf setup differs from the User@Host approach in that they are two distinct authentication models.
MySQL implements access control on an account basis, such that a user entry like 'app_user'@'10.0.0.%' includes both identity and a subnet mask within the account; in contrast, Postgres completely separates the two aspects, the pg_hba.conf file determining which users are allowed to connect from where using which authentication method, independently of the role. Although this involves more components, it is also the more finely detailed of the two approaches when actual fine-grained network-level control is required, and is arguably the most granular connection-authentication configuration available in any mainstream RDBMS.
On both engines, durability can be adjusted rather than being turned on and off, but the dials are not the same.
With regard to MySQL, the setting innodb_flush_log_at_trx_commit is the one that matters: 1 provides full durability (involving an fsync on each commit), 2 gives you a roughly one-second period of risk should the operating system crash, and 0 is the fastest option but also the least safe. Postgres has a corresponding setting called synchronous_commit, which offers greater detail: it can be set to on (flushed to disk), remote_write (safely written to the standby's OS buffer), remote_apply (only returns after the standby has actually applied it), or off. When you are aiming for a particular RPO target, it is with this setting - and its equivalent in MySQL - that the target RPO is actually determined, not in your backup schedule.
In Postgres, aggregation is pushed down to the partitions, whereas in MySQL the indexes remain local.
Postgres allows aggregation to be pushed down to each partition separately and then combines the results, and the ATTACH/DETACH PARTITION operations are nearly instantaneous metadata operations. With MySQL's partitioning there is a real limitation that it's important to be aware of before making a commitment: each index is local to its partition and there is no global index that spans the partitions. This design choice has effects on query planning that are not apparent until you encounter it in a production environment.
The monitoring dashboard should not have the same appearance on both sides
SHOW ENGINE INNODB STATUS is by far the best diagnostic tool available for MySQL, even though it is dense, unattractive, and in fact a treasure trove once you know how to interpret it. With PostgreSQL, the equivalent information is distributed among a number of different views: pg_stat_activity can be used to identify connections that are stuck in "idle in transaction", pg_stat_statements provides per-query timing data (including mean time, number of calls, and total time), and n_dead_tup / n_mod_since_analyze can be used to detect table bloat before it turns into a vacuum emergency. You'll miss various issues if you set up alerting for a PostgreSQL instance by using a MySQL runbook as your basis. None of this is really an argument that one engine is "better." It's that they fail differently, tune differently, and expect a different mental model from whoever's on call.
This post is adapted from a longer MySQL vs PostgreSQL DBA guide covering storage architecture, WAL internals, and query planning in more depth.
Top comments (0)