Stage 3 · Build

Relational Databases (PostgreSQL)

PostgreSQL Configuration

shared_buffers, work_mem, max_connections, checkpointing, and autovacuum.

7 min readDatabases for SREBuild

Memory Settings

PostgreSQL memory settings directly impact performance. The three critical settings are shared_buffers (database cache), work_mem (sort/hash operations), and effective_cache_size (planner hint).

Recommended memory settings


-- shared_buffers: 25% of system RAM (up to 8GB)
-- This is PostgreSQL's own cache for table and index data
ALTER SYSTEM SET shared_buffers = '4GB';

-- work_mem: memory per sort/hash operation
-- Be careful: this is PER OPERATION, not per query
-- A query with 4 sorts uses 4x work_mem
ALTER SYSTEM SET work_mem = '256MB';

-- effective_cache_size: planner hint for total available cache
-- Set to 50-75% of total RAM (includes OS page cache)
ALTER SYSTEM SET effective_cache_size = '12GB';

work_mem is per-operation, not per-query. A complex query with multiple sorts and hash joins can consume many multiples of work_mem. Set it conservatively to avoid OOM on concurrent connections.

work_mem is multiplicative

If you have 100 concurrent connections each running a query with 4 sort operations, that is 100 x 4 x 256MB = 100GB of memory. Size work_mem based on max_connections multiplied by expected concurrent operations.

Connection Settings

Connection configuration


-- Maximum concurrent connections
-- Default is 100 — often too low for production
ALTER SYSTEM SET max_connections = 200;

-- Superuser reserved connections
-- Ensures you can always connect for emergencies
ALTER SYSTEM SET superuser_reserved_connections = 3;

-- Connection timeout (seconds)
ALTER SYSTEM SET tcp_keepalives_idle = 60;
ALTER SYSTEM SET tcp_keepalives_interval = 10;
ALTER SYSTEM SET tcp_keepalives_count = 6;

-- Statement timeout (kill queries running too long)
ALTER SYSTEM SET statement_timeout = '30s';

The superuser_reserved_connections ensures that even if all regular connections are exhausted, a DBA can still connect using the superuser role. This is critical for emergency debugging.

Checkpoint Settings

Checkpoints flush dirty pages from memory to disk. PostgreSQL uses WAL (Write-Ahead Logging) to ensure durability, but checkpoints are needed to make old WAL segments recyclable.

Checkpoint tuning


-- Checkpoint timeout: how often to checkpoint regardless of WAL volume
ALTER SYSTEM SET checkpoint_timeout = '10min';

-- Checkpoint completion target: spread I/O over this fraction
-- of the checkpoint interval (0.0-1.0)
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

-- Max WAL size before forced checkpoint
ALTER SYSTEM SET max_wal_size = '4GB';

-- Min WAL size
ALTER SYSTEM SET min_wal_size = '1GB';

checkpoint_completion_target = 0.9 spreads checkpoint I/O over 90% of the checkpoint interval, reducing I/O spikes. Without this, checkpoints create sudden bursts of disk writes that cause latency spikes.

Autovacuum Configuration

Autovacuum reclaims space from dead tuples and updates table statistics. Proper tuning prevents table bloat that degrades performance.

Autovacuum tuning


-- Enable autovacuum (should always be on in production)
ALTER SYSTEM SET autovacuum = on;

-- Scale factor: trigger vacuum when this fraction of rows are dead
-- Default 0.2 (20%) is too conservative for large tables
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;

-- Analyze threshold: trigger analyze when this many rows change
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;

-- Cost delay: throttle autovacuum I/O to avoid impacting queries
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

-- Max workers: parallel vacuum operations
ALTER SYSTEM SET autovacuum_max_workers = 4;

For a 100M row table, the default scale factor of 0.2 means autovacuum waits until 20M dead tuples accumulate. Setting it to 0.05 triggers at 5M dead tuples, keeping the table healthier.

Autovacuum is not optional

Disabling autovacuum is one of the most common PostgreSQL misconfigurations. Without it, dead tuples accumulate, tables bloat, indexes become inefficient, and transaction ID wraparound can force the database into read-only mode.

WAL Settings

WAL configuration


-- WAL level: replica enables streaming replication
ALTER SYSTEM SET wal_level = 'replica';

-- WAL compression: reduce disk usage at CPU cost
ALTER SYSTEM SET wal_compression = on;

-- WAL buffer size: increase for write-heavy workloads
ALTER SYSTEM SET wal_buffers = '64MB';

-- Synchronous commit: set to off for async replication
-- WARNING: can lose transactions on crash
ALTER SYSTEM SET synchronous_commit = on;

wal_level = replica is required for streaming replication and PITR. If you do not need replication, wal_level = minimal reduces WAL volume but prevents point-in-time recovery.

Production Checklist

Set shared_buffers to 25% of RAM (max 8GB)
Set effective_cache_size to 50-75% of RAM
Set work_mem conservatively based on concurrent connections
Enable synchronous_commit for durability (unless async replication)
Set max_connections to match your connection pool, not application count
Enable autovacuum with tuned scale factors
Set statement_timeout to prevent runaway queries
Configure superuser_reserved_connections for emergency access
Enable WAL compression for write-heavy workloads
Set checkpoint_completion_target to 0.9

Use postgresql.conf.d for overrides

Create a postgresql.conf.d/ directory and include drop-in files for environment-specific overrides. This separates your customizations from the main config and makes upgrades easier.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.