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.
A restart with a cold buffer pool can turn into a twenty-minute incident. How innodb_buffer_pool_dump_pct, scheduled dumps, load status checks, and readiness gates make restarts boring.
Your MySQL dashboards encode years of operational knowledge, and none of them work after a PostgreSQL migration. This is the translation table I wish I had: every major MySQL signal mapped to its PostgreSQL equivalent.
After five custom executions PostgreSQL starts weighing a cached generic plan against what those custom plans cost — and on skewed data the generic plan it settles on can be terrible. How the heuristic works, how to diagnose it, and when plan_cache_mode saves you.
Deep OFFSET pagination punishes InnoDB and PostgreSQL alike. Here is how keyset pagination works in each engine, including composite cursors, row-value comparisons, and covering-index tricks.
Our nightly feed sync needed to upsert forty million rows and delete what disappeared upstream. MERGE did it in one statement — then threw unique violations ON CONFLICT never produced, and that failure is the real lesson.
EXPLAIN tells you which plan MySQL chose; optimizer_trace tells you which plans it rejected and why. Per-session capture, reading considered_execution_plans, cost numbers, truncation traps, and a worked wrong-index case.
Foreign keys feel free until the parent row gets hot. InnoDB's FK checks take shared locks on the parent, cascades multiply your lock footprint, and the contention never names the FK as the cause.
The ticket said search was fast for admins and took nine seconds for tenants — same query, same table. The difference was a policy that never appeared in any SQL anyone had written.
A crashed replica can come back looking perfectly healthy while quietly diverging. Here is the position-tracking machinery, the replay and skip failure modes, and the config that makes crashes boring.
pgloader is the fastest way to move a MySQL schema and its data into PostgreSQL, but it is not a full migration. These field notes cover the casting rules, the gaps, and the verification pass.
performance_schema has a reputation for overhead it no longer deserves. Field notes on which instruments and consumers to enable, how to measure the memory cost, and how digests catch query regressions before customers do.
Every MySQL durability argument comes down to two knobs. What innodb_flush_method and innodb_flush_log_at_trx_commit actually change, what a setting of 2 really risks, and why O_DIRECT is usually but not always right.