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.
We partitioned a table by month and the dashboard was still slow. Partitioning only helps when the planner can prune — and ours couldn't, because the queries didn't filter on the partition key.
I turned on parallel query globally and made the database slower. The lesson: parallelism is a trade, not free speed, and it only pays on big work with cores to spare.
We scaled reads to a replica and started getting bug reports about data that 'disappeared' right after saving. The cause was replication lag, and the fix was being honest about which reads can tolerate it.
Average latency looked great while one tenant's pages timed out. Tenant skew, plan caching against the wrong tenant, and noisy neighbors are the things that actually break multi-tenant Postgres.
One ALTER TABLE froze the whole app for ninety seconds, and it wasn't even slow. It was stuck behind a long query, holding a lock everyone else then queued behind. Here's how I stopped doing that.
JSONB shipped us fast and then quietly got slow. The lessons that stuck: index for your real query shape, watch TOAST on wide documents, and stop selecting the whole blob.
An index-only scan that still hammered the heap taught me the plan name lies. The real story is the visibility map, vacuum health, and whether the index actually covers the query.
Update-heavy tables get slow when every update creates index churn. HOT updates and fillfactor can keep common updates local, cheaper, and easier for vacuum to clean.
The PostgreSQL planner is only as good as its estimates. Extended statistics help when correlated columns, multi-column filters, and skewed values make ordinary statistics lie.
Prepared statements reduce parse and planning overhead, but generic plans can become a production regression when tenant skew, partial indexes, and parameter-sensitive predicates arrive.
Not every PostgreSQL performance incident is a bad query. Too many connections, idle transactions, pool starvation, and client backpressure can make normal SQL look slow.
Many PostgreSQL performance incidents are wait incidents. pg_stat_activity wait events help separate lock waits, I/O waits, client stalls, WAL pressure, and CPU saturation.