Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that keep PostgreSQL, MySQL, MariaDB, and SQL Server easier to operate.
Find the queries dragging your Postgres down, read their plans, and fix them in order of impact.
Read the guide →Which indexes to add, which to drop, and how to spot redundant index debt before it costs you writes.
Read the guide →How autovacuum works, when it falls behind, and how to keep bloat from eating your storage and your buffer cache.
Read the guide →Sizing pools, PgBouncer modes, and how to stop idle-in-transaction sessions from exhausting max_connections.
Read the guide →Track lag, WAL throughput, and replication slots before they turn into stale reads or a full disk.
Read the guide →
Backups, restores, PITR, archive gaps, and recovery drills for PostgreSQL.
AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL, AlloyDB, and managed PostgreSQL monitoring notes.
Connection storms, pool saturation, PgBouncer behavior, and max_connections pressure.
CPU, memory, disk, temp files, checkpoints, buffers, and workload pressure.
Index design, index debt, reindexing, constraint indexes, and evidence-driven DDL.
Lock waits, deadlocks, blocking chains, idle transactions, and transaction hygiene.
Galera clustering, InnoDB and Aria internals, replication, optimizer behavior, and MariaDB operations notes.
InnoDB internals, replication, locking, query performance, and MySQL operations notes.
HNSW, IVFFlat, recall, embedding search, and production RAG performance notes.
Replica lag, WAL growth, failover readiness, hot standby behavior, and replication slots.
Query plans, EXPLAIN ANALYZE, planner regressions, pagination, joins, and statistics.
Wait statistics, tempdb, Query Store, Always On availability groups, and SQL Server operations notes.
Autovacuum, dead tuples, bloat, wraparound, MVCC, freeze age, and visibility.
A selective query took 1.5 seconds as a parallel seq scan on NVMe and 45 ms as an index scan — same data, same index, one GUC apart. random_page_cost 4.0 is seek-time math running on hardware with no seek time.
PgBouncer's transaction pooling mode is the right call for most fleets, but it silently breaks prepared statements, SET, advisory locks, and LISTEN/NOTIFY. Here is how to run it without the 3am surprises.
Hit-ratio dashboards kept telling me the buffer cache was fine while storage latency tripled. pg_stat_io finally shows who is reading, writing, extending, and fsyncing — split by backend type and I/O context.
Partition pruning is free; partitionwise joins and aggregates are not. What enable_partitionwise_join and enable_partitionwise_aggregate actually change, and when they backfire on many partitions.
Parallel query makes big scans faster until forty concurrent queries all want workers at once. The knobs, the cost model, Gather vs Gather Merge, and why Workers Launched says zero.
Concurrent FOR SHARE locks and foreign key checks consume MultiXact IDs, and multixact has its own wraparound emergency separate from XID wraparound. How to monitor mxid_age(datminmxid), SLRU pressure, and vacuum's freezing before it stops your database.
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.
At peak our p99 commit latency jumped from 4 ms to 2.8 seconds while CPU pinned at 100% and the disks sat idle. pg_stat_activity showed 1,400 sessions queued on wait_event='WALInsertLock' — this is how we found it and what actually…
We lost a night of order events because someone treated NOTIFY as a durable queue. It is a signal bus with an 8000-byte payload limit and no replay — here is how to use it without learning that the hard…
For latest-five-orders-per-customer against a selective driving set, a LATERAL join over the right composite index reads hundreds of rows where a window function churns through millions. The catch is knowing when that flips.