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.
The maintenance plan said ONLINE = ON, so the rebuild was supposed to be invisible. Instead it queued behind a nine-minute report, blocked every query on the orders table, and the Saturday morning checkout went down. How online rebuilds actually work, and the options that make them survivable.
The subscriber was forty-five minutes behind and the on-call message said replication was broken. Replication was not broken — one DELETE of two million rows was replaying as two million singleton statements through a single-threaded agent. How to read the pipeline and find the real bottleneck.
I once watched a Profiler trace against a production instance take the box from 2,000 batches a second to timeout errors in ninety seconds. Extended Events is the replacement, but only if you write sessions with the same discipline Profiler never forced on you.
Every morning at 8:15 the buffer cache hit ratio sagged, page life expectancy fell off a cliff, and someone asked if the server needed more RAM. The server needed an index. How to read buffer pool counters without the folklore, and what memory pressure actually looks like.
CPU at eleven percent, disks idle, and 1,200 connections timing out: the instance had burned through every worker thread and the queue was growing by the second. What worker threads actually are, what eats them, and why raising the ceiling is usually the wrong first move.
A killed six-hour batch job once rolled back for nearly five hours, holding its locks the whole time. With accelerated database recovery the same class of kill returns the database in seconds — but the persisted version store becomes a new thing to watch.
We moved a 190-million-row nightly-rebuilt lookup table from InnoDB to Aria and cut the rebuild from 2 h 10 m to 34 minutes — until one kernel panic turned crash recovery into a 47-minute repair job.
The events table stalled at 4,100 inserts per second on a 96-core box with idle disks, and every waiter was queued on PAGELATCH_EX for the same page. Why ever-increasing keys funnel inserts into one latch queue, and what actually breaks the queue.
A 09:20 deploy flipped a hot query from index seek to table scan and p95 latency went from 40 ms to 2.8 seconds. Query Store flagged the regression by 09:35, a forced plan held the line for two days, and the real fix shipped Thursday. The full playbook.
One statistics update at 9:14 AM recompiled a stored procedure against the biggest customer's parameters, and every small customer went from 50 ms to six seconds. The patterns, the fixes, and their honest costs.
CPU was at 34 percent, the disks were bored, and 380 sessions were queued on PAGELATCH_EX against page 2:1:1 in tempdb. Why one data file serializes allocation, how many files to add, and the version store case files alone will not fix.
The sync job double-counted rows for three weeks because someone said 'change tracking' and someone else heard 'CDC'. What each feature actually captures, what each one costs, and a four-question test for picking the right one.