Production PostgreSQL Performance Tuning: Memory, Indexes, and PgBouncer Connection Pooling

PostgreSQL is one of the most capable relational databases in existence, but its default configuration file (postgresql.conf) is notoriously conservative. Out of the box, PostgreSQL is configured to run on minimal hardware specifications (allocating only 128MB of shared buffer memory) to guarantee it boots on any machine.

Running production traffic on stock PostgreSQL settings results in unnecessary disk I/O, slow query latencies, table bloat, and connection starvation.

This guide details how to tune PostgreSQL 16/17 memory, disk I/O, autovacuum parameters, and connection pooling with PgBouncer to handle thousands of concurrent queries with sub-millisecond response times.

---

1. Memory Configuration Formulas

PostgreSQL relies heavily on both its own internal cache (shared_buffers) and the Linux kernel’s page cache (effective_cache_size).

Open your configuration file (typically /etc/postgresql/<version>/main/postgresql.conf or /var/lib/pgsql/data/postgresql.conf):

# ==========================================
# Memory Configuration for a Dedicated 32GB RAM Server
# ==========================================

# 25% of total system RAM for internal buffer cache
shared_buffers = 8GB

# 50% to 75% of total system RAM (planner estimate of OS page cache)
effective_cache_size = 24GB

# Memory allocated per sort, hash, or join operation (NOT per connection)
work_mem = 64MB

# Memory used for VACUUM, CREATE INDEX, and foreign keys
maintenance_work_mem = 2GB

Critical Rules for work_mem:

[!WARNING] work_mem is allocated per sort or hash operation per query. A single complex query with multiple joins and sort clauses can consume 3–4 times the work_mem value. If you set work_mem = 1GB and run 100 concurrent queries, your server will rapidly trigger the Linux Out-of-Memory (OOM) killer. Keep work_mem between 32MB and 128MB, adjusting per-session only for heavy analytical queries:

SET work_mem = '512MB';
SELECT * FROM large_table ORDER BY created_at;
RESET work_mem;

---

2. Tune Disk and SSD Cost Parameters

PostgreSQL was designed when spinning hard disk drives (HDDs) were standard. The default query planner assumes reading non-sequential disk blocks is 4 times slower than sequential reads:

# Default assumptions for rotating magnetic media:
# seq_page_cost = 1.0
# random_page_cost = 4.0

On modern NVMe and SSD drives, random reads are almost as fast as sequential reads. Leaving random_page_cost at 4.0 tricks the query planner into choosing slow sequential table scans instead of utilizing fast B-Tree indexes.

For NVMe/SSD storage, adjust this setting immediately:

random_page_cost = 1.1

---

3. Optimize Write-Ahead Logging (WAL) and Checkpoints

Frequent checkpoints cause “checkpoint spikes”—bursts of heavy disk write activity that introduce random latency spikes to user transactions.

Tune the Write-Ahead Log (WAL) to smooth out write I/O:

# Amount of memory for unwritten WAL data
wal_buffers = 16MB

# Maximum distance between automatic WAL checkpoints
max_wal_size = 16GB
min_wal_size = 2GB

# Spread checkpoint writes evenly across the checkpoint interval
checkpoint_completion_target = 0.9

# Maximum time between automatic WAL checkpoints
checkpoint_timeout = 15min

Setting checkpoint_completion_target = 0.9 instructs PostgreSQL to complete checkpoint writes gradually over 90% of the duration before the next checkpoint, preventing sudden I/O saturation.

---

4. Tune Autovacuum to Eliminate Table Bloat

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When a row is updated or deleted, the old row version (“dead tuple”) remains on disk until cleaned by autovacuum.

On busy tables with millions of updates, default autovacuum triggers too slowly, leading to massive table bloat, bloated indexes, and wasted memory.

Make autovacuum significantly more aggressive and responsive:

# Enable autovacuum daemon
autovacuum = on

# Increase worker threads from default 3 to 5
autovacuum_max_workers = 5

# Trigger vacuum when 10% (instead of 20%) of rows are updated/deleted
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

# Minimum dead tuples before vacuuming starts
autovacuum_vacuum_threshold = 50

# Decrease sleep delay when autovacuum hits cost limit (from 20ms to 2ms)
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000

---

5. Connection Pooling: Why You Need PgBouncer

Each direct connection to PostgreSQL forks an independent OS backend process requiring 5MB to 10MB of baseline RAM plus internal lock overhead.

When your application server (e.g., Node.js, Next.js, Django, Puma) opens hundreds or thousands of concurrent connections, PostgreSQL spends more CPU time on context switching and memory allocation than actually executing SQL.

Application Threads (1,000+ connections) 
       ↓
PgBouncer (Transaction Pool: port 6432)
       ↓
PostgreSQL Server (30-50 persistent backend processes: port 5432)

Install and Configure PgBouncer

Install PgBouncer:

sudo apt install -y pgbouncer

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
# Format: dbname = host=... port=... dbname=...
production_db = host=127.0.0.1 port=5432 dbname=production_db

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

# Listen on all interfaces or localhost
listen_addr = 0.0.0.0
listen_port = 6432

# Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling delivers the highest concurrency
pool_mode = transaction

# Maximum client connections allowed to connect to PgBouncer
max_client_conn = 5000

# Actual concurrent connections forwarded to PostgreSQL
default_pool_size = 40
reserve_pool_size = 5
max_db_connections = 50

Create /etc/pgbouncer/userlist.txt with user credentials:

"db_user" "SCRAM-SHA-256$..."

Enable and start PgBouncer:

sudo systemctl enable --now pgbouncer

By routing application traffic through port 6432 in transaction pool mode, thousands of client connections are served effortlessly using fewer than 50 real PostgreSQL backend worker processes.

---

6. Identify Slow Queries with pg_stat_statements

You cannot optimize what you do not measure. PostgreSQL includes an extension called pg_stat_statements that tracks execution statistics for every query pattern running on the server.

Enable it in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Restart PostgreSQL, connect with psql, and create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Find Top 5 Queries Consuming the Most Total CPU Time:

SELECT 
    round(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage_cpu,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Analyze Execution Plans with EXPLAIN (ANALYZE, BUFFERS)

Whenever a query is slow, inspect how PostgreSQL executes it:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 45120 ORDER BY created_at DESC LIMIT 20;

Look specifically for:

• Seq Scan: Indicates a missing index on customer_id.
• Buffers: shared read=...: Indicates data is being read from slow disk storage rather than the RAM buffer pool.
• Sort Method: external merge Disk: Indicates work_mem is insufficient for the sort, forcing PostgreSQL to spill data to temporary disk files.