PostgreSQL Performance Tuning for Production

PostgreSQL’s default configuration is conservative—designed to work on minimal hardware without consuming resources. Production workloads need tuning. Here’s how to optimize PostgreSQL for performance.

Connection Management

Pool Connections

PostgreSQL forks a process per connection. Too many connections:

Memory overhead (~10MB per connection)
Context switching
Lock contention

Without pooling:
App (100 instances) × 10 connections = 1000 PostgreSQL processes

With pooling:
App → PgBouncer (20 connections) → PostgreSQL

PgBouncer configuration:

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Pool modes:

Session: Connection per client session (least pooling)
Transaction: Connection per transaction (recommended)
Statement: Connection per statement (most pooling, limited features)

max_connections

Set based on actual needs:

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Set appropriately (with pooler, can be low)
max_connections = 100

More isn’t better. Each connection uses memory and resources.

Memory Configuration

shared_buffers

PostgreSQL’s main memory cache:

# Typically 25% of RAM
shared_buffers = 4GB  # On 16GB server

Too small: Excessive disk I/O
Too large: Diminishing returns, less for OS cache

work_mem

Memory for sorts and joins:

# Per-operation, not per-connection
work_mem = 256MB  # Be careful with concurrent queries

-- Check if queries spill to disk
EXPLAIN (ANALYZE, BUFFERS) SELECT ...

-- Look for: "Sort Method: external merge Disk"

Calculate carefully: 100 concurrent queries × 256MB = 25GB

effective_cache_size

Hint to planner about available cache:

# RAM - shared_buffers
effective_cache_size = 12GB  # On 16GB server

Doesn’t allocate memory; helps query planning.

maintenance_work_mem

Memory for maintenance operations:

maintenance_work_mem = 1GB  # Higher is fine for VACUUM, CREATE INDEX

Only used by maintenance operations, not queries.

Query Optimization

Indexes

Create indexes based on actual query patterns:

-- Find missing indexes
SELECT
    schemaname, relname,
    seq_scan, idx_scan,
    n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 10000
ORDER BY seq_scan - idx_scan DESC;

Common index types:

-- B-tree (default) - equality, range
CREATE INDEX idx_users_email ON users(email);

-- Partial index - specific subset
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Covering index - avoid table lookup
CREATE INDEX idx_orders_user ON orders(user_id)
INCLUDE (status, total);

-- Expression index
CREATE INDEX idx_users_lower_email ON users(lower(email));

Analyze Query Plans

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2019-01-01';

Look for:

Seq Scan on large tables (usually bad)
High “actual time” vs “rows”
“Buffers: shared read” (disk I/O)
Nested loops with many iterations

Statistics

Keep statistics current:

-- Analyze after bulk changes
ANALYZE orders;

-- Check statistics freshness
SELECT
    schemaname, relname,
    last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

Autovacuum handles this, but manual ANALYZE after bulk operations.

Query Patterns

Pagination done right:

-- Bad: OFFSET scales poorly
SELECT * FROM orders ORDER BY id OFFSET 10000 LIMIT 20;

-- Good: Keyset pagination
SELECT * FROM orders
WHERE id > 12345
ORDER BY id
LIMIT 20;

*Avoid SELECT :

-- Bad: Fetches all columns
SELECT * FROM orders WHERE id = 123;

-- Good: Fetch what you need
SELECT id, status, total FROM orders WHERE id = 123;

VACUUM and Bloat

Understanding MVCC Bloat

PostgreSQL’s MVCC creates dead tuples:

-- Check bloat
SELECT
    schemaname, relname,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Dead tuples cause:

Wasted space
Slower index scans
Larger backups

Autovacuum Tuning

Default autovacuum can fall behind:

# More aggressive for busy tables
autovacuum_vacuum_scale_factor = 0.02  # 2% instead of 20%
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_limit = 1000    # More work per cycle

Per-table settings for hot tables:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit = 2000
);

Monitoring VACUUM

-- Current vacuum activity
SELECT * FROM pg_stat_progress_vacuum;

-- Last vacuum times
SELECT
    schemaname, relname,
    last_vacuum, last_autovacuum,
    vacuum_count, autovacuum_count
FROM pg_stat_user_tables;

Checkpoints and WAL

checkpoint_completion_target

Spread checkpoint I/O:

checkpoint_completion_target = 0.9  # Spread over 90% of interval

Reduces I/O spikes during checkpoints.

wal_buffers

WAL buffer size:

wal_buffers = 64MB  # Higher for write-heavy workloads

synchronous_commit

Trade durability for speed:

# For some tables, async commit is acceptable
synchronous_commit = off  # Fastest, risk of losing recent transactions
synchronous_commit = local  # Local flush, async replication
synchronous_commit = on  # Default, wait for local flush
synchronous_commit = remote_apply  # Wait for replica apply

Can be set per-transaction for specific use cases.

Monitoring

Key Metrics

-- Connection usage
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Cache hit ratio (should be > 99%)
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

-- Index usage
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Table I/O
SELECT
    schemaname, relname,
    heap_blks_read,
    heap_blks_hit,
    idx_blks_read,
    idx_blks_hit
FROM pg_statio_user_tables;

pg_stat_statements

Track query performance:

-- Enable extension
CREATE EXTENSION pg_stat_statements;

-- Find slow queries
SELECT
    query,
    calls,
    total_time / 1000 as total_sec,
    mean_time / 1000 as mean_sec,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

Long-Running Queries

SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Hardware Considerations

SSD is Essential

PostgreSQL benefits enormously from SSD:

Random I/O is the bottleneck
WAL writes are latency-sensitive
Vacuum performance improves

RAM for Working Set

Ideally, working set fits in memory:

shared_buffers + OS cache
Check cache hit ratio
More RAM = less I/O

CPU Cores

PostgreSQL scales with cores for:

Parallel queries (PostgreSQL 9.6+)
Concurrent connections
Background workers

Key Takeaways

Use connection pooling (PgBouncer); don’t run thousands of connections
Set shared_buffers to ~25% of RAM
Set work_mem carefully; it’s per-operation
Create indexes based on actual query patterns (check pg_stat_statements)
Use EXPLAIN ANALYZE to understand query plans
Tune autovacuum for busy tables; defaults may not keep up
Monitor cache hit ratio, connection count, and vacuum progress
SSD is essential for production PostgreSQL
Keep working set in memory when possible

PostgreSQL tuning is iterative. Measure, change one thing, measure again.