Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.
When a bad migration forced a real point-in-time recovery, our restore took nine and a half hours against a two-hour RTO — replaying 1.8 TB of WAL is single-threaded, and we had never once timed it. The drill routine, the pause-before-promote trick, and the RTO arithmetic.
A bulk UPDATE of 1.8 million rows took 41 minutes instead of 3 because of one audit trigger, and the replica had a different opinion about the result than the primary. Here is how MySQL and PostgreSQL triggers actually differ: per-row vs per-statement firing, NEW and OLD semantics, and replication behavior.
A booking platform stored start and end columns in MySQL, and its overlap queries were slow and quietly wrong at boundaries. Here is how tstzrange, GiST, and exclusion constraints fixed both, and what stays manual on MySQL.
The same covering index took our orders query from 900 ms to 35 ms on MySQL, yet PostgreSQL still did one heap fetch per row until a manual VACUUM. Here is the visibility-map mechanics behind that difference, and how to monitor it.
A firmware-updated RAID controller spent a month writing occasional garbage pages into our events table, and we found out from a customer report, not from the database — because checksums were off. The initdb flag, the offline enable, the hidden WAL cost, and the forensics toolkit.
At 03:12 the primary vanished mid-query and the log only said "terminated by signal 9: Killed" — no ERROR, no WARNING, nothing. The kernel had picked our biggest backend. The memory accounting that explains why, the overcommit fix, and the container variant.
One MySQL upsert statement burned 40,000 auto-increment IDs a second on the update path and deadlocked 180 times an hour under batch load. Here is what ON DUPLICATE KEY UPDATE actually does, what PostgreSQL ON CONFLICT does instead, and how to build idempotent writes on both.
A failed three-statement MySQL migration left my schema half-applied at 2 a.m. with no way back. Here is why PostgreSQL's transactional DDL prevents exactly that, and how migration tooling treats the two engines differently.
Our 13-to-16 cutover with pg_upgrade --link took six minutes of actual upgrade and four hours of hangover: no planner statistics, one missing extension, and a standby rsync that silently synced nothing. The rehearsal routine and fallback plan that made the next one boring.
One slow night turned our hourly roll-up into three concurrent copies fighting over the same summary table, and two other jobs had been failing silently for six weeks. The overlap guard, the failure-monitoring query, and the pg_cron foot-guns we now check on every upgrade.
Deleting one tenant row took forty-five minutes because a foreign key trigger was sequentially scanning a 90-million-row events table — inside the deleting transaction, while the lock queue grew behind it. The detection query, the rollback-safe EXPLAIN trick, and the cascade math.
A report query ran in 26 seconds on PostgreSQL 11 and 480 milliseconds on MySQL 8 with identical SQL, then got slow again on PostgreSQL 12 for a new reason. The WITH clause means different things to each planner, and this is the map of those differences.