Complete Guide to PostgreSQL Tuning
Tune PostgreSQL for high throughput: server config, indexes, query plans, connection pooling, partitioning, vacuum and monitoring, with real values.
Introduction
PostgreSQL ships with conservative defaults — shared_buffers at 128MB, work_mem at 4MB — sized so the server starts on almost any hardware. That’s fine for a laptop, wrong for production. A tuned instance typically means: more memory for caching, checkpoints that don’t stall writes, indexes that match real queries, a pool in front of limited connections, and autovacuum that keeps pace with your write volume.
This guide covers server-level and operational tuning: configuration, indexes, query plans, pooling, partitioning, vacuum, and monitoring. For query-level rewrites and EXPLAIN deep-dives, see the Complete Guide to SQL Query Optimization — this guide is about the engine room, not the SQL.
When to Tune What
Match the symptom to the lever before touching configuration:
| Symptom | First lever |
|---|---|
| Slow queries on large tables | Indexes + EXPLAIN (ANALYZE, BUFFERS) |
| High CPU, planner picking bad plans | Statistics (ANALYZE), random_page_cost, jit |
| Cache hit ratio < 95% | shared_buffers, effective_cache_size |
| Writes stall in bursts | Checkpoint tuning (max_wal_size, completion target) |
Hundreds of connections, too many clients | PgBouncer + lower max_connections |
| Tables >10M rows slowing everything | Partitioning |
| Table bloat, dead tuples piling up | Autovacuum tuning per table |
| Disk I/O saturated on SSD | random_page_cost, effective_io_concurrency |
Rule of thumb: measure first with pg_stat_statements and EXPLAIN, change one thing, measure again. Tuning without a baseline is guessing.
When tuning is the wrong tool: if a single query is slow because it’s badly written, no config fixes it — rewrite the query. If the workload outgrew one node, the answer is read replicas or sharding, not more RAM.
Establish a Baseline
Before changing anything, capture the numbers you’ll compare against:
# Quick benchmark with pgbench (built into PostgreSQL)
pgbench -i -s 50 mydb # initialize, scale factor 50
pgbench -c 20 -j 4 -T 60 mydb # 20 clients, 4 threads, 60s
# → reports tps and latency; save it
-- Enable pg_stat_statements (needs a restart after enabling)
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Top queries by total time — your optimization targets
SELECT
calls,
ROUND(total_exec_time::numeric, 0) 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;
Configuration Tuning
Memory settings
# postgresql.conf — values below assume a dedicated 8GB DB server
# Shared buffers — ~25% of RAM
shared_buffers = 2GB
# Effective cache size — ~75% of RAM (planner hint, not an allocation)
effective_cache_size = 6GB
# Work mem — per sort/hash node, per connection
# total worst case ≈ work_mem * max_connections * sorts_per_query
work_mem = 64MB
# Maintenance work mem — VACUUM, CREATE INDEX, ALTER TABLE
maintenance_work_mem = 512MB
# WAL buffers — auto (-1) picks 1/32 of shared_buffers; set explicitly for heavy writes
wal_buffers = 16MB
Connections and parallelism
# Keep this LOW — PostgreSQL handles ~100-300 active connections well,
# not 1000. Put a pool in front instead of raising it.
max_connections = 200
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# JIT — helps big analytical queries, hurts short OLTP ones.
# If your workload is mostly short queries, consider jit = off.
jit = on
Checkpoint and I/O tuning
# Spread checkpoints to avoid write stalls
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
# On SSD/NVMe — the defaults assume spinning disks
random_page_cost = 1.1 # default 4 penalizes index scans on SSD
effective_io_concurrency = 200 # how many parallel I/O requests the OS handles well
Huge pages and OS-level
# postgresql.conf
huge_pages = try # use THP if the OS provides it
# /etc/sysctl.d/99-postgresql.conf — Linux hosts
vm.swappiness = 10 # prefer keeping data in RAM over swap
vm.overcommit_memory = 2 # prevents the OOM killer from shooting postgres
# Transparent HugePages: 'madvise' works better than 'always' for postgres
echo madvise > /sys/kernel/mm/transparent_hugepage/enabled
Autovacuum tuning
autovacuum = on
autovacuum_max_workers = 6 # default 3 is often too few for write-heavy DBs
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05 # vacuum when 5% of table is dead
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02
For hot tables, tune per table instead of globally — a 500M-row table vacuumed at 5% accumulates 25M dead tuples first:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2
);
Tuning Profiles by Server Size
Config values scale with hardware. Starting points for a dedicated PostgreSQL host — adjust after benchmarking:
| Parameter | 4 GB | 16 GB | 64 GB |
|---|---|---|---|
shared_buffers | 1GB | 4GB | 16GB |
effective_cache_size | 3GB | 12GB | 48GB |
work_mem | 16MB | 32–64MB | 64–128MB |
maintenance_work_mem | 256MB | 512MB | 2GB |
wal_buffers | 16MB | 16MB | 32–64MB |
max_connections | 100 | 200 | 300 |
max_parallel_workers_per_gather | 2 | 4 | 8 |
Two caveats. work_mem is multiplied by active connections and by sort/hash nodes per query, so on high-connection workloads keep it low and raise it per-session for reports (SET work_mem = '256MB'). And max_connections stays modest at every size — a pool handles the crowd better than kernel-scheduled backends ever will.
A companion repository ships ready-made postgresql.conf files for these profiles plus a PgBouncer config and the monitoring queries from this guide.
Indexing Strategies
B-tree (default)
-- Single column index
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Composite index (column order matters — equality first, then range)
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- Partial index — index only the rows that matter
CREATE INDEX idx_orders_pending ON orders(customer_id)
WHERE status = 'pending';
Covering index (INCLUDE)
-- Extra columns ride along so the query never touches the heap
CREATE INDEX idx_orders_covering ON orders(customer_id, status)
INCLUDE (total, created_at);
-- This query becomes index-only:
SELECT total, created_at FROM orders
WHERE customer_id = 42 AND status = 'pending';
Expression index
CREATE INDEX idx_orders_lower_email ON orders(LOWER(email));
-- The query must match the expression exactly
SELECT * FROM orders WHERE LOWER(email) = 'alice@example.com';
GIN index (JSONB, arrays, trigrams)
CREATE INDEX idx_events_data ON events USING GIN (data);
CREATE INDEX idx_tags ON articles USING GIN (tags);
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));
BRIN index (large ordered tables)
-- Block Range INdex — kilobytes instead of megabytes, ideal for
-- time-ordered data where the physical order matches the column
CREATE INDEX idx_logs_brin ON logs USING BRIN (created_at);
When NOT to index
- Small tables (~1000 rows or fewer) — sequential scan wins
- Columns rarely filtered on
- Write-heavy, read-rarely columns — every index taxes every write
- Low-cardinality columns (boolean, status with 3 values) — partial index instead
- Always create with
CONCURRENTLYon live tables:CREATE INDEX CONCURRENTLY idx_x ON t(col)— it skips the write lock at the cost of a slower build
Query Optimization
EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;
-- What to look for:
-- Seq Scan on a big table → probably a missing index
-- Rows estimated 1, actual 400000 → stale stats, run ANALYZE
-- Sort spilling to disk (external merge) → raise work_mem for that query
-- Nested Loop with millions of loops → missing index on the inner side
Common query anti-patterns
-- BAD: OR blocks index usage
SELECT * FROM orders WHERE customer_id = 1 OR status = 'pending';
-- GOOD: split into UNION ALL
SELECT * FROM orders WHERE customer_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND customer_id != 1;
-- BAD: leading wildcard defeats B-tree
SELECT * FROM customers WHERE name LIKE '%alice%';
-- GOOD: trigram index
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_customers_name_trgm ON customers USING GIN (name gin_trgm_ops);
-- BAD: function on the indexed column
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- GOOD: range on the raw column
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- BAD: SELECT * pulls everything, blocks index-only scans
-- GOOD: select only what you need
SELECT id, total, status FROM orders WHERE customer_id = 42;
Connection Pooling
PostgreSQL forks a process per connection — each costs ~5-10MB plus context-switch overhead. Past a few hundred active connections, throughput collapses.
PgBouncer
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256 # md5 still works; scram is the safe default now
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300
| Mode | Description | Best for |
|---|---|---|
| session | One server per client | Long-lived connections, prepared statements, temp tables |
| transaction | Server per transaction | Most apps — best utilization; watch out for session state |
| statement | Server per statement | Simple single-statement queries only |
Transaction mode breaks SET, prepared statements, LISTEN/NOTIFY, and session-level temp tables — check your ORM’s session usage before enabling it.
Partitioning
Range partitioning (time-series)
CREATE TABLE events (
id BIGSERIAL,
created_at TIMESTAMPTZ NOT NULL,
data JSONB
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Automate monthly partitions
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');
List partitioning (by category)
CREATE TABLE orders_by_region (
id BIGSERIAL,
region TEXT NOT NULL,
total NUMERIC
) PARTITION BY LIST (region);
CREATE TABLE orders_us PARTITION OF orders_by_region FOR VALUES IN ('US');
CREATE TABLE orders_eu PARTITION OF orders_by_region FOR VALUES IN ('EU', 'UK');
CREATE TABLE orders_other PARTITION OF orders_by_region DEFAULT;
Hash partitioning (even distribution)
CREATE TABLE users_hashed (
id BIGSERIAL,
email TEXT,
data JSONB
) PARTITION BY HASH (id);
CREATE TABLE users_hash_0 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_hash_1 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_hash_2 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_hash_3 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Partition pruning only kicks in when queries filter on the partition key — a table partitioned by created_at queried by customer_id gets zero benefit and pays planning overhead on every query.
Verify pruning works before calling it done: EXPLAIN the query and confirm only the relevant partitions appear in the plan (Append → a handful of partition scans, not all of them). Keep the count sane — hundreds of partitions slow planning noticeably, so prefer monthly or quarterly boundaries over daily ones unless data volume forces otherwise. Each partition needs its own indexes; CREATE INDEX on the parent propagates automatically on PG11+.
Vacuum Management
-- Table bloat: dead tuples as % of live rows
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size,
n_live_tup,
n_dead_tup,
ROUND(n_dead_tup::FLOAT / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
-- Manual vacuum with stats refresh
VACUUM (ANALYZE, VERBOSE) orders;
-- VACUUM FULL rewrites the table and holds an exclusive lock — plan for it
VACUUM FULL orders;
-- Is autovacuum running right now, and on what?
SELECT pid, phase, heap_blks_total, heap_blks_scanned
FROM pg_stat_progress_vacuum;
Dead tuples above ~10-20% on hot tables mean autovacuum isn’t keeping up — tighten autovacuum_vacuum_scale_factor per table before reaching for VACUUM FULL.
Monitoring
Tuning is a loop, not an event. Watch these signals over time — not just after changes, but as baseline telemetry: query timing via pg_stat_statements, connections in pg_stat_activity, dead-tuple ratio, cache hit ratio, and replication lag if you run standbys. A slow drift in any of them tells you the next lever before it becomes an incident.
-- Slow queries live: log_min_duration_statement = 100 in postgresql.conf
-- What's running right now
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- Index usage — near-zero scans are removal candidates
SELECT
schemaname,
tablename,
indexname,
idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;
-- Cache hit ratio per table (aim >95%)
SELECT
relname,
ROUND(heap_blks_hit::FLOAT / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio ASC
LIMIT 20;
-- Database-level cache hit ratio
SELECT
datname,
ROUND(blks_hit::FLOAT / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database;
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Query suddenly slow after deploy | Stale stats or new plan | ANALYZE the tables; compare plans before/after with EXPLAIN |
FATAL: too many connections | No pool, or pool exhausted | PgBouncer transaction mode; audit apps holding idle connections (pg_stat_activity) |
| Write latency spikes every ~5min | Checkpoint I/O burst | checkpoint_completion_target = 0.9, raise max_wal_size |
| Autovacuum always behind | Workers too few or scale factor too high | autovacuum_max_workers = 6, per-table scale factor 0.01 |
| Index not being used | Low selectivity, stale stats, or function on column | ANALYZE; check EXPLAIN costs; rewrite the predicate |
work_mem spills (temp files in logs) | Sorts/hashes exceeding work_mem | Raise work_mem for that workload, or fix the query |
| Disk full from WAL | max_wal_size misread or archive_command failing | Check pg_stat_archiver failures; fix archiving before WAL fills the disk |
Best Practices
- Set
shared_buffersto ~25% of RAM — the single most impactful setting on a dedicated DB host - Create indexes before loading data — or use
CREATE INDEX CONCURRENTLYon live tables - Run
ANALYZEafter bulk loads — the planner works with fresh statistics - Pool connections — PostgreSQL handles ~100-300 active connections well, not thousands
- Watch cache hit ratio — aim for >95%; below that, add RAM or fix queries
- Partition tables past ~10M rows — but only when queries filter the partition key
- Use
pg_stat_statements— tune what actually runs, not what you think runs - Tune autovacuum per hot table — global scale factors underserve big tables
- Use
EXPLAIN (ANALYZE, BUFFERS)— never guess what the planner is doing - Change one setting at a time — benchmark each change or you’ll never know what helped
Common Mistakes
- Running production on defaults —
shared_buffers = 128MBbarely caches anything - Over-indexing — every index taxes every write; drop unused ones
- Skipping
ANALYZEafter bulk loads — the planner bets on stale stats VACUUM FULLduring peak hours — it locks the whole table- Raising
max_connectionsto 500+ without a pool — context switches eat the CPU - Indexing low-cardinality columns — a boolean index is nearly useless
- Setting
work_memhigh globally —work_mem × connections × sortscan OOM the box - Ignoring bloat — dead tuples accumulate until sequential scans crawl
- Tuning without a baseline — no
pgbench/pg_stat_statementsdata means no proof it helped - Treating
effective_cache_sizeas an allocation — it’s a planner hint, not reserved memory
See Also
Frequently Asked Questions
How much RAM should I allocate to PostgreSQL?
Put ~25% into shared_buffers and tell the planner about ~75% via effective_cache_size. The rest belongs to the OS page cache and per-connection work_mem. For a 16GB dedicated server: shared_buffers = 4GB, effective_cache_size = 12GB, work_mem around 32-64MB depending on max_connections.
When should I use partitioning vs indexing?
Indexes for queries filtering specific columns and returning small subsets. Partitioning for tables over ~10M rows where queries filter the partition key (usually time). They're complementary — partitioned tables still need indexes inside each partition. For a deeper look at index internals, see Complete Guide to MongoDB Indexing for how another engine approaches the same problem.
How do I find unused indexes?
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_total_relation_size(schemaname || '.' || indexname) DESC;
Zero scans since the last stats reset makes it a removal candidate — but check pg_stat_reset() timing first so you don't drop an index used only in month-end reports. Drop with DROP INDEX CONCURRENTLY.
Why is my cache hit ratio low even with more shared_buffers?
shared_buffers isn't the whole cache story — PostgreSQL also relies on OS page cache, and a cold cache right after restart reads from disk. If the working set simply exceeds RAM, no setting fixes it: reduce the working set (indexes, partitioning, archival) or add memory. Check pg_statio_user_tables to see which tables are actually missing the cache.
What does work_mem actually control?
Memory per sort or hash operation, per query node, per connection — not a global pool. work_mem = 64MB with 200 connections running multi-sort queries can demand gigabytes. If temp file entries appear in logs or EXPLAIN shows external merge, raise it for that workload; if connections are many, raise it cautiously.
Related Resources
Database Denormalization: Storage for Read Speed
A practical guide to database denormalization: when to trade storage for read performance, common patterns, and how to keep derived data consistent.
GuideSQL CTEs: Common Table Expressions Explained
A practical guide to SQL CTEs: non-recursive and recursive expressions, readability, performance, and when to use them over subqueries.
GuideData Migration: Zero-Downtime Strategies That Work
Plan, dual-write, backfill, and validate a zero-downtime data migration — with schema evolution patterns and rollback procedures.
RecipeSeed Database
How to seed databases with realistic data for development, testing, and staging environments using seed scripts, migrations, and factories across PostgreSQL, MongoDB, and Prisma.
GuideFull-Text Search: Implement Search That Actually Works
A practical guide to full-text search: PostgreSQL tsvector, Elasticsearch indexing, query design, relevance tuning, and building search that users trust with autocomplete, faceting, and typo tolerance.
GuideComplete Guide to SQL Query Optimization
Find and fix slow SQL queries: read EXPLAIN plans, index the right way, kill N+1 queries, and apply partitioning, pooling, and caching on PostgreSQL and MySQL.