PostgreSQL standalone production configuration: memory, WAL, autovacuum, logging and backups

#1. OS preparation

• Data on a dedicated XFS volume (noatime), with WAL on a separate volume for busy systems.
• vm.swappiness = 1, and transparent huge pages off (or madvise).
• Huge pages for large shared_buffers (huge_pages = try, with vm.nr_hugepages sized to fit).

sudo dnf install -y postgresql17-server postgresql17-contrib
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb   # use --data-checksums via PGSETUP_INITDB_OPTIONS

Enable data checksums at initdb (--data-checksums; the default from PostgreSQL 18).

#2. Core parameters (32 GB RAM, 8 vCPU, SSD)

# postgresql.conf
listen_addresses = '*'
max_connections = 200                 # use PgBouncer rather than raising this
shared_buffers = 8GB                  # ~25% of RAM
effective_cache_size = 24GB           # ~75% of RAM
work_mem = 32MB                       # per sort or hash node, per query
maintenance_work_mem = 1GB
huge_pages = try

# WAL and checkpoints
wal_level = replica
max_wal_size = 8GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on

# planner (SSD)
random_page_cost = 1.1
effective_io_concurrency = 200

# parallelism
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_parallel_workers = 8

#3. Autovacuum

autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000

For very large tables, set per-table thresholds (ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01)).

#4. Logging

logging_collector = on
log_min_duration_statement = 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 1s
log_line_prefix = '%m [%p] %q%u@%d app=%a '
shared_preload_libraries = 'pg_stat_statements'

#5. Access (pg_hba.conf)

# TYPE    DATABASE  USER        ADDRESS          METHOD
local     all       postgres                     peer
hostssl   sales     app_rw      10.20.1.0/24     scram-sha-256
hostssl   all       +dba_group  10.0.5.0/24      scram-sha-256

password_encryption = scram-sha-256, ssl = on, and no trust entries.

#6. Connection pooling

PgBouncer in transaction mode in front of busy apps: thousands of client connections map onto about 50–100 server connections.

#7. Backups with pgBackRest

# /etc/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=4
repo2-type=s3
repo2-s3-bucket=pg-backups-prod
repo2-s3-region=ap-south-1
repo2-s3-endpoint=s3.ap-south-1.amazonaws.com
repo2-retention-full=8
compress-type=zst

[sales]
pg1-path=/var/lib/pgsql/17/data

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=sales archive-push %p'

pgbackrest --stanza=sales stanza-create
pgbackrest --stanza=sales --type=full backup
pgbackrest --stanza=sales check

Schedule weekly full and daily differential backups, and test a point-in-time restore.

Tip

Use PGTune as a starting point, then adjust using pg_stat_statements, checkpoint logs and autovacuum logs from your real workload.