PostgreSQL Memory Tuning (`shared_buffers`) & PgBouncer Connection Pool Sizer

Postgres Connection Pooling: PgBouncer Sizing & Serverless IOPS

Your app has 500 serverless function instances, each opening 10 database connections. Postgres has 5,000 connections -- and it falls over at 500. Every Postgres connection is a backend process consuming ~5-10MB RAM plus context-switch overhead; past a few hundred connections, throughput collapses while the database spends its life scheduling processes instead of running queries. This is the #1 Postgres production failure mode, and the fix is always the same: PgBouncer in transaction pooling mode between the app and the database.

The sizing math: application connection demand divided by safe Postgres max_connections (typically 100-300 depending on RAM) = pooling ratio needed. 5,000 app connections / 200 server connections = 25:1 -- trivial for PgBouncer in transaction mode, where server connections are borrowed per-transaction and returned. shared_buffers: 25% of RAM is the classic guidance (so the OS page cache gets the rest -- Postgres relies on the OS cache heavily). work_mem: the dangerous one -- it is per-sort-operation per-connection; 100 connections x 64MB work_mem = 6.4GB that can materialize under load. Set it from (RAM minus shared_buffers) / max_connections / 3, and raise per-query only where EXPLAIN shows disk sorts.

Serverless Postgres (Neon, Supabase) changes the constraints. Connection limits are lower and autoscaling compute means your pooler must handle scale-to-zero (cold starts + pooled connections = latency spikes if misconfigured). Neon pooled connection strings route through their pooler -- use them, and set idle timeouts aggressively since serverless bills for active compute. IOPS: provisioned vs autoscale -- bursty workloads on autoscale IOPS can surprise bills; steady high-throughput workloads often cost less on provisioned. The sizer takes your app connection count, query profile, and platform choice into pooler config, Postgres memory settings, and IOPS tier recommendations.

The mistakes that keep recurring: session-mode pooling for stateless APIs (holds server connections for idle clients -- use transaction mode), no statement timeout (one bad query pins a server connection and queues everything behind it -- set statement_timeout to 30s), and skipping the pooler entirely because the ORM has pooling (ORM pools are per-instance -- 500 instances times 10 pooled each is still 5,000 connections; you need the central pooler). Monitor pg_stat_activity for wait events: if you see many clients waiting on ClientRead, the pooler is starved; if the database shows high CPU with low connection count, the problem is queries, not pooling.