Running Postgres in production
Most Postgres outages come from a few everyday things: too many connections, a transaction left open, vacuum falling behind, or one role with far too much power. This lesson gives you the daily habits and the exact settings and queries a DBA uses to keep a busy database healthy.
By the end you can
- Explain why connections are expensive, and set up PgBouncer in transaction mode with Prisma
- Pick starting values for
shared_buffers,work_memandeffective_cache_size, and know whywork_memis dangerous - Explain MVCC, dead rows, VACUUM and transaction ID wraparound in plain words
- Run a daily health check with SQL: sessions, blocking, cache hits, dead rows, XID age
- Create app, reporting and migration roles with least privilege
The everyday picture
Think about what keeps a big store running on an ordinary day. At the counters, a floor manager controls the queue, so 200 customers share 10 cashiers instead of each customer wanting a cashier of their own. That is connection pooling.
On the shelves, staff remove expired yoghurt every morning. If nobody does, the shelf fills with stock nobody can sell, and fresh stock has no space. That is vacuum. In the back office, CCTV and a daily report show problems early, before customers complain. That is monitoring. And the storeroom key goes only to the people who need it: cashiers cannot open the cash safe, and the cleaner cannot change prices. Those are roles.
None of these are exciting. Skip any one of them for a few weeks, and the store slowly stops working. Postgres is the same.
Connections are expensive: pool them
Each Postgres connection is a separate operating system process on the server. It takes memory (a few MB at rest, much more while sorting), and every connection makes some internal work more expensive for all the others. Postgres's default max_connections is 100. It is common to raise it to a few hundred, but thousands of direct connections usually make the server slower, not faster. A database with 16 cores does useful work for roughly a few dozen queries at a time. The rest wait.
Apps open far more connections than that. Each NestJS instance has its own Prisma pool. In Prisma 5 and 6 the default size is 2 × CPU cores + 1, so 6 API containers on 4-core machines open about 54 connections before any worker or cron job starts. Add autoscaling on a festival night, and you hit FATAL: sorry, too many clients already (Scaling up vs scaling out).
The fix is a connection pooler. PgBouncer is the standard one. Apps connect to PgBouncer as if it were Postgres. PgBouncer keeps a small pool of real connections to Postgres and lends one to a client only while it needs it.
default_pool_size = 20. PgBouncer showed 20 active clients and 180 waiting, and Postgres saw only 20 app connections. A client gets a server connection for one transaction, then gives it back.PgBouncer has three pool modes. In session mode a client keeps its server connection until it disconnects, which saves little. In transaction mode a client holds a server connection only for one transaction. This is the mode you want, and the one Prisma supports. Statement mode is stricter still and breaks multi-statement transactions.
Transaction mode has one rule: nothing may outlive a transaction, because the next transaction may run on a different server connection. Session-level SET, advisory locks taken with pg_advisory_lock, LISTEN and temporary tables kept across transactions do not work reliably. Use SET LOCAL inside a transaction instead of SET. Prepared statements used to break too. PgBouncer 1.21 and newer can track protocol-level prepared statements when max_prepared_statements is above 0, which is what Prisma uses.
Quick check
PgBouncer runs in transaction mode. Which code breaks?
A session-level advisory lock belongs to the server connection that took it. The worker's later transactions, and its unlock, may run on other server connections, so the lock is not where the worker thinks it is and may never be released. SET LOCAL lasts exactly one transaction, which is what transaction mode needs, and a single statement is its own transaction.