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 nightly export OOM-killed itself trying to load ten million rows into memory at once. The fix wasn't more RAM — it was telling PostgreSQL to hand the rows over a batch at a time with a server-side cursor.
An index that had grown to three times the size of its table was the reason a lookup got slow. B-tree indexes bloat from page splits and churn, and the fix is more nuanced than 'just REINDEX'.
A single connection sitting in 'idle in transaction' can hold locks, pin the vacuum horizon, and quietly bloat every table in the database. I learned this the hard way from a debugger someone left paused overnight.
Everyone learns about transaction ID wraparound eventually. Then one day the log says 'MultiXactId members limit exceeded' and you discover PostgreSQL has a second counter that can run out — and it's usually your foreign keys filling it.
Swapping two rows' unique values failed halfway through with a duplicate-key error, even though the end state was perfectly valid. Deferrable constraints let PostgreSQL check at commit instead of after every row.
Every query takes lightweight relation locks you never think about. Cross the fast-path limit of 16 per backend, usually via heavy partitioning, and the shared lock manager turns into a bottleneck under concurrency.
Loading millions of rows with single-row INSERTs is the slowest possible choice. COPY, batching, building indexes after the load, and managing WAL turn an overnight job into a coffee break.
The planner assumes your columns are independent. When they're actually correlated, its row estimates collapse, it picks a nested loop over a hash join, and a fast query becomes a slow one. CREATE STATISTICS fixes the math.
Foreign tables look local, which is the problem. A join the planner can't push down pulls an entire remote table across the network row by row, and a clean-looking query becomes a multi-minute transfer.
Parallel query can turn a 30-second scan into 6 seconds, or it can spawn six workers that fight over the same disk and finish slower than one. Knowing when it helps is mostly knowing when it doesn't.
An OS upgrade changed glibc's collation, your text indexes were now sorted by rules that no longer match, and PostgreSQL had no idea. Queries return wrong results and unique constraints let duplicates in.
Triggers are convenient and almost invisible, which is exactly why a bulk update can grind to a halt. Each row fires logic you forgot was there, and the cost compounds quietly until a batch job exposes it.