How does PostgreSQL native replication compare to MySQL?
I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example, as are AWS RDS standbys and read replicas. Streaming replication is used almost universally with PostgreSQL.
Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's improving slowly with each major release. The ability for pg_basebackup to take a copy of the system using streaming replication (and do so from another standby) is a big help.
In general, a feature simply won't be released in PostgreSQL until it's production ready. Bugs happen, like in any software, but they're typically fixed shortly after they're identified. Really major new features sometimes have bugs and issues discovered after the .0 release, but if so fixing them is a high priority; bugs aren't just left around.
I'm not aware of any serious issues with streaming replication - sync or async - nor have I seen any reported for quite a while. They were less stable than Pg's usual standard in the .0 releases of the major versions they were introduced in, but both matured quickly and are thoroughly production-ready.
(Update: There was a specific bug in the new 9.3 version prior to 9.3.4 that did cause replication issues in some cases; users of 9.3 should update to 9.3.4 immediately. Older versions are not affected.)
The only caveat I want to mention is a a minor detail with synchronous replication: If you commit on the master, then cancel the query after it commits while it waits for the replica to confirm, it's treated as commited on the master even before it's replicated. You get the same effect by restarting the master while waiting for the replica to reply. In practice this is an irrelevance, but it's about the only problem I can think of.
Pg's native replication is quite different to MySQL's.
MySQL uses logical replication where it sends the logical changes made to table data, table structure, etc, and the replica applies those changes.
PostgreSQL's replication is lower level (in 9.5 and below; future versions may also add logical replication). It sends the blocks that changed in the tables. It's simpler, easier to get right, and imposes lower load on the replica server, but consumes more network bandwidth and requires more storage on the master to hold not-yet-replicated changes. It's best configured to use streaming replication with WAL-archiving fallback, making it more complex to configure than MySQL's. It replicates low-level changes like VACUUM activity, not just tuple changes, keeping the on-disk state of the replica the same as that of the master. It is incapable of replicating only one database; the whole system must be replicated, which can be frustrating if you have one big, high churn and unimportant database and one small, low-churn and vital database.
All in all, it depends on what you want to do with it.
I view PostgreSQL's replication as considerably better for replicas used for backup, high availability and disaster recovery. Particularly so when combined with point in time recovery (PITR).
On the other hand, it's not as good for read-only reporting replicas because the need to delay the application of replicated data while running long transactions means that you need to either let it cancel very long running queries or fall greatly behind the master, consuming more disk space on the master and forcing it to work harder to keep up.
There's ongoing work to enable logical replication in PostgreSQL, where the logical changes to table structure, table contents, etc are replicated, rather than their on-disk state. Pg's catalog design and support for user-defined everything makes this quite a complex task. Some of the groundwork has been put in place for 9.4, but full logical replication is unlikely to be usable before 9.6 or later.