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.
MySQL 8.0.18 gave us EXPLAIN ANALYZE, and most people read its output wrong. Here is how the per-loop numbers actually work, the misreads I keep seeing, and when the tree format tells the truth tabular EXPLAIN hides.
MySQL EXPLAIN and PostgreSQL EXPLAIN describe query plans in different vocabularies with different cost models. This guide translates between them and shows what each output quietly hides.
The MySQL slow query log is still the best forensic record of a bad deploy, but only if you configure it deliberately and digest it into query families. This is the workflow I actually run.
MySQL 8.x gives you a rich optimizer hint toolbox, and PostgreSQL deliberately refuses to. Here is what a PostgreSQL practitioner actually does instead: statistics, rewrites, planner GUCs, and pg_hint_plan as a genuine last resort.
AUTO_INCREMENT and PostgreSQL identity columns solve the same problem with different machinery. This guide covers gaps, counter persistence, sequence caching, and the migration steps that actually bite.
Doublewrite exists for one narrow disaster: a torn page that redo cannot repair. This is an honest look at how it works, what it really costs in I/O, how to measure that overhead yourself, and when disabling it is actually defensible.
GROUP BY, DISTINCT, derived tables, and window functions can all materialize internal temp tables, and when those overflow memory your query is suddenly doing disk I/O nobody planned. Here is how the TempTable engine works and how to find the offenders.
When checkpoint age catches up with redo capacity, InnoDB stops the world to flush, and your p99 pays for it. Here is how innodb_redo_log_capacity works in 8.0.30+, how to size it against write bursts, and how furious flushing looks in the metrics.
MySQL spent two decades tightening sql_mode; PostgreSQL shipped strict and stayed there. Here is what that history means for a migration: the queries that break, the data that will not load, and the bug classes that disappear.
MySQL fakes booleans with TINYINT(1); PostgreSQL has the real thing. The migration is easy until an ORM mapping or a stray value of 2 makes it interesting. Here is the complete type-mapping story.
InnoDB prevents phantoms by locking gaps; PostgreSQL prevents them with snapshots and serialization checks. Migrating between the two means retraining your instincts about blocking, deadlocks, and retries.
One idle REPEATABLE READ session can pin millions of undo records and slowly melt a busy MySQL server. History list length is the canary; this is how to watch it, find the blocker, and keep undo growth under control.