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.
InnoDB and PostgreSQL both detect and break deadlocks, but they detect them differently, report them differently, and reward different diagnosis habits. A field guide for migrating teams.
Deadlocks are not random noise; each one is a recorded collision with a cause. This is the repeatable workflow I use to capture, parse, and permanently fix InnoDB deadlocks.
MySQL 8.0 moved row-lock diagnosis to performance_schema.data_locks and data_lock_waits. Here is how I read those tables, use sys.innodb_lock_waits, and build a blocker tree during an incident.
Both MySQL 8.0 and PostgreSQL support SELECT ... FOR UPDATE SKIP LOCKED, which makes database-backed job queues practical. The claim patterns, locking details, and operational costs differ.
A single idle transaction can make an innocent ALTER TABLE queue the entire application behind it. This is my working process for diagnosing MySQL metadata lock waits and running DDL safely.
InnoDB defaults to REPEATABLE READ and PostgreSQL defaults to READ COMMITTED, and the difference is not cosmetic. Here is what actually changes for application behavior after a migration.
The most repeatable plan regression in MySQL: add LIMIT to an ORDER BY query and the optimizer abandons your selective index for the ordering one. Why it happens, how to spot it, and the fixes that hold.
The wrong-index ORDER BY LIMIT regression bites MySQL and PostgreSQL alike: the planner walks an ordering index expecting early matches that never come. Here is why LIMIT tempts both optimizers, and how to diagnose and fix it in each.
Most bad MySQL plans trace back to one number: a row estimate built from twenty sampled pages. Field notes on innodb_stats_persistent, ANALYZE TABLE, eq_range_index_dive_limit, and 8.0 histograms.
Both engines can combine multiple single-column indexes to answer one query, but MySQL's index_merge and PostgreSQL's bitmap scans differ in reliability and reach. That difference changes how you design composite indexes after a migration.
optimizer_switch is where MySQL hides two decades of query-planner history, and a handful of its flags cause real production regressions. Field notes on the ones that bite and how to test them one at a time.
InnoDB stores the table inside the primary key; PostgreSQL stores rows in a heap with every index as an equal citizen. That one storage decision changes index design, covering queries, and primary key sizing.