Tune PostgreSQL Performance on Arch Linux

postgresql.conf

# Memory
shared_buffers          = 2GB       # 25% of RAM
effective_cache_size    = 6GB       # 50-75% of RAM
work_mem                = 64MB      # per-sort/hash
maintenance_work_mem    = 512MB

# Checkpoints
checkpoint_completion_target = 0.9
checkpoint_timeout      = 15min
max_wal_size            = 4GB
min_wal_size            = 1GB

# Planner
random_page_cost        = 1.1       # SSD
effective_io_concurrency = 200
max_parallel_workers_per_gather = 4
max_parallel_workers    = 8

# Connections
max_connections         = 200       # use PgBouncer for more

# Slow query logging
log_min_duration_statement = 1000  # ms
log_checkpoints         = on
log_lock_waits          = on

Reload

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

Top slow queries

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