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 75% rule is folklore, not a sizing method. This field guide covers working-set reality, online resizing, warmup dump and restore, and the two counters that actually tell you whether the buffer pool is big enough.
MySQL's utf8 was never really UTF-8, and fixing it shaped a decade of schema decisions. Here is how PostgreSQL handles encoding and collation, and where migrations from utf8mb4 go wrong.
AWS RDS PostgreSQL removes server ownership, but it does not remove query risk. This field guide shows what to watch across CloudWatch, Database Insights, Enhanced Monitoring, and PostgreSQL itself.
Azure Database for PostgreSQL has strong platform metrics, Query Store, enhanced metrics, and portal dashboards. This guide explains how to turn those signals into PostgreSQL incident evidence.
Google Cloud SQL for PostgreSQL gives you Cloud Monitoring, logs, and Query Insights. This field guide shows how to connect those GCP signals to query, lock, vacuum, and replica evidence.
Subtransactions are cheap until you cross 64 of them in one transaction or pile up enough of them under load. Then SubtransSLRU contention shows up as global slowness nobody can explain.
LISTEN/NOTIFY is a lovely way to push events without extra infrastructure, until the global notify queue fills, a slow listener blocks commits, and you learn it was never built to be your message bus.
Long analytics queries on our replica kept getting cancelled mid-run. Turning on hot_standby_feedback stopped the cancellations instantly — and then the primary started bloating. That trade is the whole story.
A table with one million live rows was holding five million dead ones, and queries were scanning all six. Understanding xmin, xmax, and how PostgreSQL decides what a query can see explains where the dead rows come from.
Switching PgBouncer to transaction pooling tripled our connection efficiency and broke prepared statements, SET commands, and advisory locks all at once. The fix starts with understanding what a pooled connection no longer guarantees.
A one-line ALTER COLUMN TYPE took an ACCESS EXCLUSIVE lock and rewrote a 150-million-row table for forty minutes. Some type changes are free; the ones that aren't have a safe pattern that avoids the outage.
VACUUM FULL would have locked a 200GB table for over an hour in the middle of the day. pg_repack rebuilt it online with only a momentary lock, and nobody noticed. Here's how it works and when to trust it.