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 ships two unrelated features both called InnoDB compression, and picking the wrong one costs memory, CPU, or both. Here is how each actually works and when either still pays.
PK-less InnoDB tables silently tax replication and tooling. GIPK mode fixes them at CREATE TABLE time, if you know what my_row_id changes and what it quietly breaks.
The index existed and the seek was in the plan, but the estimate said one row and the table said fourteen million. How auto-update thresholds, sampling, and the ascending-key trap produce bad estimates, and how to triage them with sys.dm_db_stats_properties.
The JSON keyword in MariaDB is a compatibility alias over LONGTEXT. It migrates schemas cleanly, then surprises you at scale: full parses per row, whole-document rewrites per update, and equality semantics that differ from MySQL.
MySQL 8.0 replaced block nested loop with a real hash join, and half the join-tuning advice from the 5.7 era is now wrong. When the optimizer hashes, when an index beats it, and when it loses.
When an AUTO_INCREMENT column hits its type's ceiling, every insert on that table fails at once. Field notes on the incident shape, a fleet-wide detection query, signed vs unsigned headroom, and migrating to BIGINT without downtime.
Row-based binary logging is the right default, but it makes binlog volume a function of how many rows you touch. This covers FULL vs MINIMAL row images, partial JSON logging, expiry settings, and the big-UPDATE binlog explosion.
A replica that cannot survive kill -9 is a replica you rebuild at 3 a.m. This covers relay_log_recovery, the repository history that made replicas crash-safe, GTID auto-positioning, and why you should crash replicas on purpose.
An UPDATE that touches no indexed column can skip index maintenance entirely, if the page has room. How HOT updates work, why fillfactor matters, and how to measure the ratio in pg_stat_user_tables.
innodb_dedicated_server sizes your buffer pool and redo from detected hardware. On a truly dedicated box it is right; inside a memory-limited container it can size you straight into an OOM kill.
MySQL gives every connection a thread; PostgreSQL gives every connection a process. That one difference changes capacity planning, pooling strategy, and what breaks under transaction pooling.
MariaDB's system-versioned tables keep row history automatically — no triggers, no shadow tables. How FOR SYSTEM_TIME works, what storage growth really costs, and the limitations worth knowing first.