Overview

The default postgresql.conf ships conservatively — assuming shared hosting with 1 GB RAM. These settings are calibrated for a dedicated server with 8 vCPU and 32 GB RAM running PostgreSQL 16.

Use pgTune to generate a baseline, then refine from there.

postgresql.conf (key sections)

# ── Memory ────────────────────────────────────────────────────
shared_buffers          = 8GB        # 25% of total RAM
effective_cache_size    = 24GB       # 75% of total RAM (planner hint)
work_mem                = 64MB       # per sort/hash op; watch for OOM with many connections
maintenance_work_mem    = 2GB        # for VACUUM, CREATE INDEX

# ── WAL / Durability ──────────────────────────────────────────
wal_buffers             = 64MB
checkpoint_completion_target = 0.9
max_wal_size            = 4GB
min_wal_size            = 1GB

# For high-write workloads on reliable hardware:
# synchronous_commit    = off        # risk: up to wal_writer_delay of data loss

# ── Planner ───────────────────────────────────────────────────
random_page_cost        = 1.1        # SSD — lower than default 4.0
effective_io_concurrency = 200       # SSD IOPS hint
default_statistics_target = 250      # better query plans (default: 100)

# ── Parallel queries ──────────────────────────────────────────
max_parallel_workers_per_gather = 4
max_parallel_workers            = 8
max_parallel_maintenance_workers = 4

# ── Connections ───────────────────────────────────────────────
max_connections         = 200        # pair with PgBouncer for real apps

# ── Autovacuum ────────────────────────────────────────────────
autovacuum_max_workers  = 4
autovacuum_vacuum_cost_delay = 2ms   # default 2ms is good for SSD
autovacuum_vacuum_scale_factor   = 0.02   # vacuum at 2% dead tuples
autovacuum_analyze_scale_factor  = 0.01   # analyze at 1% changes

# ── Logging (useful for slow-query analysis) ──────────────────
log_min_duration_statement = 1000   # log queries > 1 second
log_checkpoints            = on
log_lock_waits             = on
log_temp_files             = 0

PgBouncer (pgbouncer.ini)

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr       = 0.0.0.0
listen_port       = 6432
auth_type         = scram-sha-256
pool_mode         = transaction       # best for most web apps
max_client_conn   = 2000
default_pool_size = 20               # actual Postgres connections per database
reserve_pool_size = 5
reserve_pool_timeout = 3
log_connections   = 0
log_disconnections = 0

After editing, reload

sudo -u postgres psql -c "SELECT pg_reload_conf();"
# Some settings (max_connections, shared_buffers) need a full restart:
sudo systemctl restart postgresql