Tune PostgreSQL Performance on AlmaLinux 9

Key parameters in postgresql.conf

Memory

# 25% of total RAM
shared_buffers = 2GB

# For complex queries; 50–75% of RAM
effective_cache_size = 6GB

# Per-query working memory (lower if many connections)
work_mem = 64MB

# For maintenance operations (VACUUM, CREATE INDEX)
maintenance_work_mem = 512MB

Checkpoints

# Spread checkpoint I/O over longer interval
checkpoint_completion_target = 0.9

# Time between automatic checkpoints
checkpoint_timeout = 15min

# Max WAL size before forcing checkpoint
max_wal_size = 4GB
min_wal_size = 1GB

Planner

# Assume SSD/fast storage (lower = prefer index scans)
random_page_cost = 1.1

# Match OS CPU count
effective_io_concurrency = 200
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Connections

max_connections = 200

# Use PgBouncer for >200 connections

Logging (for slow query analysis)

log_min_duration_statement = 1000    # log queries > 1s
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0

Apply changes

systemctl reload postgresql 2>/dev/null || \
rc-service postgresql-14 reload 2>/dev/null || \
rcctl reload postgresql

Useful extensions

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;