PostgreSQL Configuration for Production: The Settings That Actually Matter

The default PostgreSQL configuration is tuned for compatibility, not performance. It ships with settings calibrated for a machine with 128MB of RAM in 1996 — shared_buffers = 128MB, work_mem = 4MB, max_connections = 100. Running these defaults on a modern 4GB VPS means your database is using a fraction of the available memory and hitting connection limits long before the hardware is stressed.

Tuning PostgreSQL configuration is not complicated. There are eight settings that account for the vast majority of the performance difference between a default install and a production-ready one. This guide covers all eight with the specific values to use, why they matter, and how to verify they are working.

Before You Change Anything: Understand Your Hardware

Every tuning recommendation is relative to your server's RAM and CPU count. Know your numbers before setting values:

# RAM
free -h
# or
cat /proc/meminfo | grep MemTotal

# CPU cores
nproc

# Storage type (SSD vs HDD matters for random_page_cost)
cat /sys/block/sda/queue/rotational
# 0 = SSD, 1 = HDD

The examples below are calibrated for a 4GB RAM VPS with 2 CPU cores — a common starting point for self-hosted Node.js apps.

Finding postgresql.conf

# Show config file location
sudo -u postgres psql -c "SHOW config_file;"

# Typical locations:
# Ubuntu/Debian: /etc/postgresql/16/main/postgresql.conf
# Docker postgres image: /var/lib/postgresql/data/postgresql.conf

After editing, reload without restarting (most settings apply on reload):

sudo systemctl reload postgresql
# Or from psql:
SELECT pg_reload_conf();

Some settings require a full restart (shared_buffers, max_connections, wal_buffers). Check which ones:

SELECT name, context FROM pg_settings WHERE name IN (
  'shared_buffers', 'work_mem', 'max_connections',
  'effective_cache_size', 'wal_buffers', 'checkpoint_completion_target'
);
-- context = 'postmaster' → requires restart
-- context = 'sighup' → reload is sufficient

The Eight Settings

1. shared_buffers — Postgres's Primary Cache

The amount of memory PostgreSQL uses for caching data pages. The default (128MB) is tiny. The standard recommendation is 25% of total RAM.

# Default
shared_buffers = 128MB

# For 4GB RAM VPS
shared_buffers = 1GB

# For 8GB RAM VPS
shared_buffers = 2GB

# For 16GB+ RAM VPS
shared_buffers = 4GB   # Cap around 4-8GB — OS cache handles the rest above that

Why 25% and not more? PostgreSQL relies on the operating system's page cache for the rest. Over-allocating shared_buffers can reduce the OS cache available, which is counterproductive. The OS cache is often more efficient at caching frequently accessed data anyway.

Requires restart.

2. effective_cache_size — Tells the Query Planner About OS Cache

This setting does not allocate memory — it tells the query planner how much total memory is available for caching (Postgres buffer + OS cache combined). The planner uses this to decide whether to use an index scan or a sequential scan.

# Default
effective_cache_size = 4GB

# For 4GB RAM VPS
effective_cache_size = 3GB   # shared_buffers (1GB) + OS cache (~2GB)

# For 8GB RAM VPS
effective_cache_size = 6GB

Setting this too low causes the planner to avoid index scans it should use. Setting it approximately right (total RAM minus overhead) is sufficient.

3. work_mem — Memory Per Sort or Hash Operation

Memory available for sorting and hash join operations. The default (4MB) means Postgres spills to disk for any sort over 4MB. With modern data sizes, this happens constantly.

# Default
work_mem = 4MB

# For a web API workload (multiple concurrent connections)
work_mem = 16MB

# For a reporting/analytics workload (fewer, heavier queries)
work_mem = 64MB

Critical caveat: work_mem is per operation, not per query, and not per connection. A complex query with 5 sort steps can use 5 × work_mem. With 20 active connections, total consumption could reach 20 connections × 5 operations × 16MB = 1.6GB. Set this thoughtfully relative to your connection count.

The signal that work_mem is too low: EXPLAIN ANALYZE shows "Sort Method: external merge Disk" — Postgres is sorting to disk.

-- Check if sorts are spilling to disk
SELECT query, sort_space_used, sort_space_type
FROM pg_stat_statements
JOIN pg_sort_stats ON ...;
-- Or look for "external merge Disk" in EXPLAIN ANALYZE output

4. maintenance_work_mem — Memory for VACUUM, CREATE INDEX, ALTER TABLE

Used during vacuum, index creation, and table alterations. The default is 64MB. Increasing it makes these operations faster.

# Default
maintenance_work_mem = 64MB

# For 4GB+ RAM
maintenance_work_mem = 256MB

This only applies during maintenance operations — it does not affect regular query memory. Set it higher than work_mem since maintenance operations run infrequently and benefit from more memory.

5. checkpoint_completion_target — Spread Write I/O

PostgreSQL periodically writes dirty pages to disk (a "checkpoint"). By default, it tries to complete this in 50% of the checkpoint interval — which means a burst of write I/O. Setting this to 0.9 spreads writes more evenly across the full interval.

# Default
checkpoint_completion_target = 0.5

# Recommended
checkpoint_completion_target = 0.9

The effect: more consistent write latency. Less pronounced write spikes. Generally better for SSD-based systems.

6. wal_buffers — Write-Ahead Log Buffer

Memory for buffering WAL (Write-Ahead Log) data before it is written to disk. The default (-1, which means auto-set to 1/32 of shared_buffers) is usually fine after tuning shared_buffers, but setting it explicitly to 16MB avoids a restart if you adjust shared_buffers later.

# Default (auto)
wal_buffers = -1

# Explicit value — good for write-heavy workloads
wal_buffers = 16MB

Requires restart.

7. random_page_cost — Tell Postgres About Your Storage Type

The query planner uses random_page_cost to estimate the cost of a random disk read. The default (4.0) assumes a spinning disk where random reads are expensive. For an SSD, random reads are nearly as fast as sequential reads.

# Default (HDD assumption)
random_page_cost = 4.0

# For SSD
random_page_cost = 1.1

# For NVMe (fastest)
random_page_cost = 1.0

With the default value on an SSD, the planner often incorrectly prefers sequential scans over index scans, because it overestimates the cost of the random I/O an index scan requires. Setting this correctly is one of the most impactful changes for index-heavy workloads.

Verify your storage type:

cat /sys/block/sda/queue/rotational
# 0 = SSD, use 1.1
# 1 = HDD, keep at 4.0

8. max_connections — Set Lower Than You Think

The default (100) sounds like enough. But Postgres allocates shared memory proportional to max_connections for things like lock tables and process state — even if those connections are never used. Reducing max_connections to what you actually need frees that memory for the cache.

# Default
max_connections = 100

# With PgBouncer handling multiplexing (recommended)
max_connections = 50   # Actual Postgres connections — PgBouncer handles the rest

# Without PgBouncer, single-server app
max_connections = 100  # Keep default, but no higher unless needed

If you are running PgBouncer in front of Postgres, max_connections in Postgres can be set to default_pool_size in PgBouncer plus a buffer for superuser connections (typically +10).

Requires restart.

The Complete postgresql.conf Block

A production-ready config for a 4GB RAM, 2-core VPS with SSD storage:

# postgresql.conf — 4GB RAM, 2 CPU, SSD

# Memory
shared_buffers            = 1GB
effective_cache_size      = 3GB
work_mem                  = 16MB
maintenance_work_mem      = 256MB
wal_buffers               = 16MB

# Planner
random_page_cost          = 1.1       # SSD
effective_io_concurrency  = 200       # SSD (use 2 for HDD)
default_statistics_target = 100       # More accurate query planning

# Write behaviour
checkpoint_completion_target = 0.9
min_wal_size              = 1GB
max_wal_size              = 4GB

# Connections
max_connections           = 50        # With PgBouncer; use 100 without

# Logging (useful for development, reduce in prod)
log_min_duration_statement = 1000     # Log queries over 1 second
log_checkpoints            = on
log_connections            = off      # Too noisy in prod
log_disconnections         = off
log_lock_waits             = on
deadlock_timeout           = 1s

# Autovacuum — keep tables clean
autovacuum                 = on
autovacuum_max_workers     = 3
autovacuum_vacuum_scale_factor = 0.1  # Vacuum after 10% of rows change (default 20%)
autovacuum_analyze_scale_factor = 0.05

Verifying the Changes

After reloading (or restarting), confirm the settings took effect:

-- Verify key settings
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
  'shared_buffers', 'work_mem', 'effective_cache_size',
  'random_page_cost', 'max_connections', 'wal_buffers',
  'checkpoint_completion_target'
);

-- Check if shared_buffers is actually being used
SELECT
  blks_hit,
  blks_read,
  round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'yourdb';
-- Target: cache_hit_ratio > 99%
-- If below 95%, either shared_buffers is too small or your working set exceeds RAM

A cache hit ratio below 95% means Postgres is reading from disk frequently — either shared_buffers is too small, your working data set is larger than available RAM, or you have missing indexes causing full table scans.

Using PGTune as a Starting Point

For any server not covered by the examples above, PGTune generates a baseline configuration:

# Or use the web version: https://pgtune.leopard.in.ua

# Example output for 8GB RAM, 4 CPUs, SSD, web application:
# shared_buffers = 2GB
# effective_cache_size = 6GB
# maintenance_work_mem = 512MB
# checkpoint_completion_target = 0.9
# wal_buffers = 16MB
# default_statistics_target = 100
# random_page_cost = 1.1
# work_mem = 6990kB    ← tuned for connection count
# huge_pages = off
# min_wal_size = 1GB
# max_wal_size = 4GB

PGTune is a good starting point, not a final answer. Validate with pg_stat_database cache hit ratios and EXPLAIN ANALYZE on your actual slow queries.

What These Settings Do Not Fix

Configuration tuning fixes resource allocation and planner behaviour. It does not fix:

• Missing indexes — no amount of shared_buffers makes a sequential scan on a million-row table fast

• N+1 queries — configuration does not reduce round trips

• Unvacuumed tables with high dead row percentages

• Poorly written queries with cross joins or non-SARGable predicates

Run EXPLAIN ANALYZE on your slow queries before and after tuning. If a query is slow because of a sequential scan on a large table, an index will help more than any configuration change.