Choosing MySQL- or PostgreSQL-Compatibility Before Backtrack and Autovacuum Make the Choice for You

In practice, you pick the engine your team already runs. Tools, ORMs, backups, and the people themselves are already tied to MySQL or PostgreSQL. Fighting that rarely pays.

But "compatible" is not "identical," and one difference shows up before you've written a single query: Aurora PostgreSQL does not run vacuum the way stock PostgreSQL runs it.

Quick background, if you haven't lived in this corner of Postgres: every delete or update leaves dead rows behind, and vacuum is the background process that cleans them up. Vacuum also has a deadline to meet. PostgreSQL numbers its transactions with a counter that eventually wraps around, and every table must be fully vacuumed before its counter gets close to the limit (autovacuum_freeze_max_age) — or old data becomes unreadable.

Aurora adds adaptive autovacuum (rds.adaptive_autovacuum, on by default). As a table's transaction counter approaches that limit, Aurora automatically turns vacuum up. Stock PostgreSQL will not do that on its own; you'd have to notice the climb and tune by hand.

Adaptive autovacuum covers the wraparound deadline. It does nothing for a second problem, and this one exists only because of how Aurora is built.

In ordinary PostgreSQL replication, a replica replays the primary's changes onto its own copy of the data, and the two copies mostly stay out of each other's way. Aurora does not work that way. The writer and all the read replicas share one storage layer. That is why Aurora replicas are quick to create and don't fall behind on replay — and it is also why a query running on a reader can reach back and stall cleanup on the writer.

The chain goes like this. A long-running query on a reader still needs the old versions of the rows it is reading, so the replica tells the writer to keep them. PostgreSQL calls this hot_standby_feedback. In standard RDS PostgreSQL it is a setting you can turn on or off. In Aurora PostgreSQL it is on by default and cannot be turned off. The writer's vacuum must now leave those old rows alone, so cleanup on the writer stalls for as long as the query on the reader runs.

You notice it like this: vacuum looks like it is running, but the CloudWatch metric MaximumUsedTransactionIDs keeps climbing toward your autovacuum_freeze_max_age value and will not come down. When you see that, do not start tuning vacuum on the writer. Look for long-running queries, or sessions sitting idle inside a transaction, on the readers. That is the actual blocker.

One more asymmetry, on the MySQL side: Backtrack — Aurora MySQL's ability to rewind the whole cluster back to a moment in the past (page 7) — does not exist for PostgreSQL at all. If your recovery runbook assumes it does, you will find that out in the middle of an incident.