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.
Our orders table got vacuumed three times a day and its relfrozenxid still crept 2 million xids a week — until one Tuesday the anti-wraparound vacuum read all 420 GB of it in a single afternoon. The freeze knobs, the eager-versus-lazy mechanics, and the per-table fix.
We flipped password_encryption and pg_hba.conf in the same change window and locked out 41 of 46 roles on an inherited cluster. The correct order is the reverse — rotate passwords while pg_hba still says md5, then flip it last.
Our orders table carried a 13.6 GB btree on a status column with five distinct values across 400 million rows — two years after upgrading to PostgreSQL 14. One REINDEX CONCURRENTLY brought it back at 2.4 GB. The upgrade grants the capability; it never rebuilds anything for you.
One Debezium-style connector on a busy primary cost us a third of a core, twenty percent more WAL, and — when the consumer stalled over a long weekend — 92 GB of retained WAL. The full cost sheet, and the benchmark I should have run first.
Monday 09:00, the entire application tier was locked out: 'Host 10.0.4.17 is blocked because of many connection errors; unblock with mysqladmin flush-hosts'. A new Kubernetes liveness probe had spent the weekend opening TCP connections and closing them without a handshake.
During our hourly import burst, backends spent a third of their time in buffer write waits while the checkpointer dumped 25 GB in 90-second spikes. Smoothing the bgwriter flattened checkpoint IO and cut burst p99 from 400 ms to under 90 ms — the full knob-by-knob breakdown.
The finance report had run with NOLOCK for two years to 'avoid blocking.' Then a reconciliation found the same invoice counted twice in one run and missing in the next — no dirty data involved, just an allocation-ordered scan walking through a page split. The full honesty about WITH (NOLOCK).
The telemetry table had monthly partitions plus a MAXVALUE catch-all, and a cron job that added next month's partition — until the cron host was decommissioned and nobody noticed for eleven months. 900 million rows sat in pmax, and REORGANIZE PARTITION had to move every one of them.
MariaDB 10.6 goes end of life in July 2026, so we rehearsed the jump to 11.4 LTS on a restored backup. The upgrade itself took eleven minutes; finding the removed config variable, the renamed client tools, and the one query whose plan flipped took three weeks. Here is the rehearsal method.
Our order numbers lived on AUTO_INCREMENT until a second write node appeared and the increment-and-offset trick started to creak. Sequences gave us controlled, cacheable, gap-tolerant numbers — here is the full cost sheet.
The alert fired 21 days before expiry: Ssl_server_not_after on the primary. We rotated CA and server certs online with ALTER INSTANCE RELOAD TLS and zero downtime — then broke exactly one account, the one created with REQUIRE SUBJECT against the old certificate's CN.
We turned on server_audit with QUERY events on a Friday, logged 41 GB over the weekend, filled the disk, and added 30% to write-path p99. The compliant configuration we ended up with logs 2 GB a day and nobody notices it. Here is the delta.