PostgreSQL Performance Tuning: The Settings That Actually Matter

Overview

The default postgresql.conf is tuned for a machine with 128 MB of RAM from 2004. Every production Postgres instance runs with settings that are wrong for its hardware, and most people fix this by copying a random blog post's recommendations. Here's what each setting actually does and how to pick values that aren't guesses.

Start with the bottleneck, not the config

Before touching a single parameter, find out what's slow. The most common mistake is tuning memory when the problem is a missing index or a connection pileup.

-- What are the slowest queries?
SELECT
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

If pg_stat_statements isn't loaded, add it:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart, then CREATE EXTENSION pg_stat_statements; in your database. This one view tells you more about performance than every configuration parameter combined.

Memory settings

shared_buffers

Postgres's own cache. The most common mistake is setting it too high — beyond 25–30% of RAM, it fights with the OS page cache for the same memory and performance drops[reference:0].

For anything larger, 25% is a safe ceiling. The OS cache handles the rest, and it's usually better at it.

effective_cache_size

Not a cache allocation — a hint to the query planner about how much memory is available for caching, including the OS page cache. Set it to about 50–75% of total RAM. This doesn't allocate anything; it just influences whether the planner chooses an index scan or a sequential scan.

# For a 32 GB machine
effective_cache_size = 24GB

work_mem

Memory per sort or hash operation, per connection. The name is misleading — a query with four sorts uses four times this amount. The default 4 MB is too small for analytics and fine for OLTP.

# For an analytics workload
work_mem = 256MB

# Per-session override for a heavy query
SET work_mem = '1GB';
SELECT ... ;

Setting it globally high with many connections is how you get OOM kills. If you have 200 connections and set work_mem = 256MB, a single complex query could allocate 200 × 256MB = 51 GB. Set it conservatively globally and override per-session for heavy reports.

maintenance_work_mem

Used by VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, this is allocated per maintenance operation, not per connection, so you can be generous:

maintenance_work_mem = 1GB

The autovacuum settings nobody touches

Autovacuum defaults are too conservative for modern workloads. Dead tuples accumulate, bloat grows, and queries slow down because the planner's statistics are stale[reference:1].

# Defaults are 20% + 50 rows, which is far too high for large tables
autovacuum_vacuum_scale_factor = 0.05    # vacuum at 5% dead tuples
autovacuum_analyze_scale_factor = 0.02   # analyze at 2% changed

# Make autovacuum more aggressive in general
autovacuum_vacuum_cost_limit = 2000      # default 200
autovacuum_naptime = 30s                 # default 1min

The scale factor is the important one. With a 10-million-row table and the default 20%, autovacuum doesn't start until 2 million rows are dead. By then, performance has already degraded.

Check which tables are falling behind:

SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Any table with dead_pct above 10% and no recent autovacuum is a problem.

Connections: the hidden killer

Every Postgres connection is a process, and each one costs 5–10 MB of memory[reference:2]. An application with 200 workers opening direct connections uses 200 database connections. Beyond 200–300 connections, throughput decreases rather than increases — lock contention and context switching eat the gains[reference:3].

Use PgBouncer. It sits between your application and Postgres, and in transaction mode, 200 application workers share 20–50 actual database connections.

# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

Then point your app at port 6432 instead of 5432. That's the whole change.

Checkpoint tuning

Postgres writes dirty pages to disk during checkpoints. If checkpoints happen every few minutes during normal load, you get I/O spikes that slow everything down[reference:4].

# These defaults are too low for write-heavy workloads
max_wal_size = 4GB          # default 1GB
min_wal_size = 1GB

checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

Check how often checkpoints happen:

grep "checkpoint" /var/log/postgresql/postgresql-*.log | tail -20

If you see them more than every 10–15 minutes under normal load, raise max_wal_size.

Quick wins for specific workloads

What not to do

• Don't set shared_buffers to 80% of RAM. This is advice from MySQL, where it applies to innodb_buffer_pool_size. Postgres relies on the OS page cache and doesn't need it.
• Don't tune by copying a config from a blog. Your workload isn't theirs. Use pg_stat_statements and pg_stat_user_tables to find your own bottlenecks.
• Don't set work_mem globally high. Per-session overrides for heavy queries are safer.
• Don't ignore autovacuum. It's the most common cause of "the database got slow over time" and the easiest to fix.

Testing changes

Change one thing at a time and measure. Use pgbench for a synthetic baseline, or replay production queries from pg_stat_statements:

pgbench -i -s 100 mytestdb
pgbench -c 20 -j 4 -T 60 mytestdb

Twenty clients, four threads, sixty seconds. Run it before and after each config change. If you can't measure the difference, you haven't improved anything.