Postgres – The Production Checklist

Everything up to here has been about writing SQL. This post is the list of things that have to be true before a database is in front of users, in roughly the order they bite.

Connections

Postgres forks a process per connection, and the default ceiling is 100. A modest application fleet wants far more than that, and raising the number is the wrong fix — a thousand backends means a thousand processes competing for the same CPUs.

SHOW max_connections;

SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'active')             AS active,
       count(*) FILTER (WHERE state = 'idle')               AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM   pg_stat_activity
WHERE  backend_type = 'client backend';

Use a pooler. Every application framework has a client-side pool, and that is the first step: keep it small — ten per instance is plenty — and make sure it is actually shared rather than created per request.

Beyond a handful of instances you want a server-side pooler as well, PgBouncer or pgcat, in transaction mode: a client gets a backend for the duration of a transaction rather than a connection. That is what lets 2,000 application connections sit on 40 backends.

Transaction mode has one rule you must know: anything that spans transactions on the same connection breaks. Session-level advisory locks, SET without SET LOCAL, LISTEN/NOTIFY, and server-side prepared statements — check that your driver disables those or uses a compatible protocol.

The idle in transaction count in that query is the one to alert on. Those hold locks and block cleanup, so cap it at the database rather than trusting applications:

ALTER DATABASE stayhub SET idle_in_transaction_session_timeout = '60s';
ALTER DATABASE stayhub SET statement_timeout = '30s';
ALTER DATABASE stayhub SET lock_timeout = '5s';

The settings worth changing

The defaults are tuned for a small machine, not yours. Five matter; the rest can wait until you have a measurement.

SELECT name, setting, unit, source
FROM   pg_settings
WHERE  name IN ('shared_buffers','effective_cache_size','work_mem','maintenance_work_mem',
                'random_page_cost','max_connections','wal_level');

source tells you whether a value is the default or something someone set — which is the first thing to check when a server behaves unlike its twin.

Autovacuum

MVCC leaves dead row versions behind, and autovacuum reclaims them. It is on by default and usually fine. The two ways it stops being fine are worth recognising.

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS pct_dead,
       last_autovacuum, last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

It cannot keep up. The default threshold triggers a vacuum when 20% of a table is dead — on a table with a hundred million rows that is twenty million dead rows before it starts. Lower it per table for the busy ones:

ALTER TABLE bookings SET (autovacuum_vacuum_scale_factor = 0.02,
                          autovacuum_analyze_scale_factor = 0.01);

Something is holding it back. Vacuum cannot remove a row version that any open transaction might still need. One long-running query, one abandoned idle in transaction connection, or one stale replication slot pins the horizon and dead rows accumulate everywhere — including tables that connection never touched. When bloat is growing and autovacuum is running constantly, look for the oldest transaction before touching any setting.

Backups

Two kinds, and you want both.

# logical: portable, restores to a different version, slow on large databases
pg_dump --format=custom --file=stayhub.dump "$DATABASE_URL"
pg_restore --dbname="$TARGET_URL" --clean --if-exists stayhub.dump

pg_dump gives you a consistent snapshot without blocking writers, and it is the right tool up to a few hundred gigabytes and for moving data between environments. It is a snapshot: whatever happened since is gone.

For anything you cannot lose an hour of, you want physical backup plus WAL archiving — a base backup, plus every WAL segment since, which together allow recovery to any second in between. pgBackRest and barman are the tools; managed providers do this for you and call it point-in-time recovery.

The only part that actually matters: restore it. On a schedule, into a real database, and time how long it takes. A backup nobody has restored is a hypothesis, and the number you need during an incident is how long recovery takes — which you cannot discover during the incident.

The queries to have ready

-- what is running right now, oldest first
SELECT pid, state, now() - xact_start AS txn_age, wait_event_type, left(query, 60)
FROM   pg_stat_activity
WHERE  backend_type = 'client backend' AND state <> 'idle'
ORDER  BY xact_start;

-- who is blocked, and by whom
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       left(blocked.query, 40) AS blocked_query, left(blocking.query, 40) AS blocking_query
FROM   pg_stat_activity blocked
JOIN   pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- what is taking the space
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM   pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

Keep these somewhere you can reach without thinking. The second one — the blocking chain — is the query you will want at the worst possible moment, and pg_blocking_pids is hard to remember under pressure.

What to watch

• Connection count against max_connections — alert at 80%.
• Replication lag, if you have a replica. It grows quietly and matters suddenly.
• Transaction age. The oldest open transaction, and the age of the oldest unfrozen transaction id — the second is the one that ends in a forced shutdown if ignored long enough.
• Cache hit ratio, from pg_stat_database. Below about 95% on a steady workload means the working set no longer fits.
• Disk free. A full disk stops Postgres accepting writes, and WAL that cannot be archived is a common way to fill one.

Managed Postgres

RDS, Cloud SQL, Azure Database, Neon, Supabase. They handle backups, failover, patching and point-in-time recovery, which is most of the operational burden and worth the money for almost every team.

What they do not do: choose your indexes, write your queries, size your connection pool, or stop a migration taking an exclusive lock. Everything in the rest of this track is still yours.

Two practical notes. You will not have superuser — the account you get is a role with a subset of privileges, so anything needing ALTER SYSTEM goes through their parameter groups instead. And a major version upgrade is still a scheduled event with downtime, so the argument for starting on a current version applies exactly as it did in the first post.

The checklist

1. Application connects as a role that cannot drop tables.
2. A connection pooler, sized deliberately.
3. statement_timeout, lock_timeout and idle_in_transaction_session_timeout set on the database.
4. pg_stat_statements enabled.
5. The five settings above reviewed against the machine.
6. Autovacuum monitored; thresholds lowered on the busiest tables.
7. Backups running, and a restore actually performed and timed.
8. Alerts on connections, replication lag, transaction age and disk.
9. Migrations that use lock_timeout, CONCURRENTLY and NOT VALID.
10. TLS enforced, with sslmode=verify-full from the application.

That is the track. Eighteen posts from a container to a checklist, and every query in them was run against a real database before it shipped.