How to Tune PostgreSQL for Performance

A default PostgreSQL installation is deliberately conservative — it targets the lowest common hardware denominator and survives on 256 MB of RAM. Production databases need deliberate tuning. This guide walks through the five highest-impact areas: shared_buffers, work_mem, autovacuum, index hygiene, and the pg_stat_statements extension for query-level visibility. All examples target PostgreSQL 15/16 on a dedicated or semi-dedicated server.

Find Your Configuration File

PostgreSQL reads postgresql.conf at startup. Its location varies by distribution.

# Debian/Ubuntu (package install)
/etc/postgresql/16/main/postgresql.conf

# Fedora/RHEL/Rocky
/var/lib/pgsql/16/data/postgresql.conf

# Arch (from pacman)
/var/lib/postgres/data/postgresql.conf

You can always ask the running instance:

psql -U postgres -c 'SHOW config_file;'

After editing postgresql.conf, most parameters take effect with a reload; a few (including shared_buffers) require a full restart.

# Reload (most settings)
sudo systemctl reload postgresql

# Full restart (shared_buffers, max_connections, etc.)
sudo systemctl restart postgresql

# Fedora/RHEL: service name includes the major version
sudo systemctl restart postgresql-16

shared_buffers — The Primary Cache

shared_buffers is the chunk of RAM PostgreSQL reserves for caching data pages. The kernel page cache also caches disk reads, so the two layers cooperate. The traditional starting point is 25 % of total RAM; on dedicated servers with 16 GB or more you can push to 30–40 %.

# In postgresql.conf
shared_buffers = 4GB          # for a 16 GB dedicated server
effective_cache_size = 12GB   # tell the planner how much total cache exists
                              # (shared_buffers + OS page cache estimate)

effective_cache_size does not allocate memory — it is a planner hint. Set it to roughly 75 % of total RAM so the planner favours index scans over sequential scans on large tables.

Check the current value without restarting:

psql -U postgres -c 'SHOW shared_buffers;'

work_mem — Per-Sort and Per-Hash Memory

work_mem caps the memory available to a single sort or hash operation per node per query. A complex query with five sort nodes can use 5 × work_mem. Multiply that by the number of concurrent connections and you can exhaust RAM fast. Start conservatively and raise it for specific sessions or roles when needed.

# postgresql.conf — a reasonable default for mixed OLTP workloads
work_mem = 16MB

# Grant more memory to a reporting role at session level without touching global config
psql -U postgres -c "ALTER ROLE reporting SET work_mem = '256MB';"

Signs that work_mem is too low: EXPLAIN ANALYZE shows "Batches: N" in Hash nodes (N > 1 means it spilled to disk) or external merge sorts in Sort nodes.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000;

Autovacuum — Keep the Engine Clean

PostgreSQL's MVCC model leaves dead row versions ("bloat") after UPDATE and DELETE. Autovacuum reclaims them. Disabling it is almost always a mistake; tuning it is often necessary on write-heavy tables.

Global Defaults

# postgresql.conf
autovacuum = on                          # never turn this off
autovacuum_max_workers = 4               # default 3; raise on multi-core servers
autovacuum_naptime = 30s                 # how often the daemon wakes
autovacuum_vacuum_cost_delay = 2ms       # lower = faster vacuums, more I/O
autovacuum_vacuum_scale_factor = 0.01    # trigger at 1% dead rows (default 0.2)
autovacuum_analyze_scale_factor = 0.005  # trigger ANALYZE at 0.5% changes

The scale factors matter most on large tables. A table with 100 million rows at the default 0.2 scale won't trigger autovacuum until 20 million dead rows accumulate.

Per-Table Overrides for High-Traffic Tables

psql -U postgres -d mydb <<'SQL'
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay   = 2,
  autovacuum_analyze_scale_factor = 0.005
);
SQL

Monitor Vacuum Activity

psql -U postgres -d mydb -c "
SELECT relname,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;"

Index Hygiene

Indexes speed reads but slow writes and consume disk. Keep only the indexes that queries actually use.

Find Unused Indexes

psql -U postgres -d mydb -c "
SELECT schemaname,
       relname        AS table,
       indexrelname   AS index,
       idx_scan       AS scans
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND indisunique IS FALSE
ORDER BY pg_relation_size(indexrelid) DESC;"

Indexes with zero scans since the last statistics reset are candidates for removal. Verify they are not used by foreign key constraints before dropping.

Find Missing Indexes (Sequential Scans on Large Tables)

psql -U postgres -d mydb -c "
SELECT relname,
       seq_scan,
       seq_tup_read,
       idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND pg_relation_size(relid) > 10485760  -- tables larger than 10 MB
ORDER BY seq_tup_read DESC;"

Rebuild Bloated Indexes

# Non-blocking (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

pg_stat_statements — Query-Level Profiling

pg_stat_statements is a core extension that tracks cumulative execution statistics for every distinct query. It is the fastest way to find the queries burning the most CPU or I/O.

Enable the Extension

# postgresql.conf — must be loaded at startup
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all        # also tracks nested statements
pg_stat_statements.max = 10000        # how many distinct queries to track

sudo systemctl restart postgresql      # restart required for shared_preload_libraries

# Create the extension in each database you want to monitor
psql -U postgres -d mydb -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'

Find Your Most Expensive Queries

# Top 10 queries by total execution time
psql -U postgres -d mydb -c "
SELECT left(query, 80)          AS query,
       calls,
       round(total_exec_time::numeric, 2)  AS total_ms,
       round(mean_exec_time::numeric,  2)  AS mean_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;"

# Reset statistics after a tuning change to get a clean baseline
psql -U postgres -c 'SELECT pg_stat_statements_reset();'

Verification

After applying changes, confirm settings are live and check cache hit ratios.

psql -U postgres -d mydb -c "
SELECT
  sum(heap_blks_hit)  AS cache_hits,
  sum(heap_blks_read) AS disk_reads,
  round(
    100.0 * sum(heap_blks_hit)
    / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0),
  2) AS hit_ratio_pct
FROM pg_statio_user_tables;"

A healthy OLTP database typically shows a cache hit ratio above 95 %. If it is lower, shared_buffers or effective_cache_size may need adjustment, or your working set simply exceeds available RAM.

Troubleshooting

• PostgreSQL fails to start after raising shared_buffers: The system's SHMMAX or huge pages limit may be too low. On Linux, check /proc/sys/kernel/shmmax and consider enabling huge pages (huge_pages = try in postgresql.conf).
• OOM killer terminates PostgreSQL: work_mem multiplied by connections multiplied by sort nodes exceeded physical RAM. Lower work_mem globally and raise it only for specific roles.
• Autovacuum cannot keep up: Increase autovacuum_max_workers, lower autovacuum_vacuum_cost_delay, or add per-table storage parameters to the most active tables.
• pg_stat_statements view is empty: Confirm shared_preload_libraries includes pg_stat_statements and you restarted (not just reloaded) the service after the change.
• REINDEX CONCURRENTLY fails: Requires PostgreSQL 12+. On older versions, use CREATE INDEX CONCURRENTLY on a new index, swap, then drop the old one.