StackPractices
advanced By Mathias Paulenko

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:

SymptomFirst lever
Slow queries on large tablesIndexes + EXPLAIN (ANALYZE, BUFFERS)
High CPU, planner picking bad plansStatistics (ANALYZE), random_page_cost, jit
Cache hit ratio < 95%shared_buffers, effective_cache_size
Writes stall in burstsCheckpoint tuning (max_wal_size, completion target)
Hundreds of connections, too many clientsPgBouncer + lower max_connections
Tables >10M rows slowing everythingPartitioning
Table bloat, dead tuples piling upAutovacuum tuning per table
Disk I/O saturated on SSDrandom_page_cost, effective_io_concurrency
Mermaid flowchart LR diagram

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:

Parameter4 GB16 GB64 GB
shared_buffers1GB4GB16GB
effective_cache_size3GB12GB48GB
work_mem16MB32–64MB64–128MB
maintenance_work_mem256MB512MB2GB
wal_buffers16MB16MB32–64MB
max_connections100200300
max_parallel_workers_per_gather248

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 CONCURRENTLY on 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
ModeDescriptionBest for
sessionOne server per clientLong-lived connections, prepared statements, temp tables
transactionServer per transactionMost apps — best utilization; watch out for session state
statementServer per statementSimple 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

SymptomLikely causeFix
Query suddenly slow after deployStale stats or new planANALYZE the tables; compare plans before/after with EXPLAIN
FATAL: too many connectionsNo pool, or pool exhaustedPgBouncer transaction mode; audit apps holding idle connections (pg_stat_activity)
Write latency spikes every ~5minCheckpoint I/O burstcheckpoint_completion_target = 0.9, raise max_wal_size
Autovacuum always behindWorkers too few or scale factor too highautovacuum_max_workers = 6, per-table scale factor 0.01
Index not being usedLow selectivity, stale stats, or function on columnANALYZE; check EXPLAIN costs; rewrite the predicate
work_mem spills (temp files in logs)Sorts/hashes exceeding work_memRaise work_mem for that workload, or fix the query
Disk full from WALmax_wal_size misread or archive_command failingCheck pg_stat_archiver failures; fix archiving before WAL fills the disk

Best Practices

  • Set shared_buffers to ~25% of RAM — the single most impactful setting on a dedicated DB host
  • Create indexes before loading data — or use CREATE INDEX CONCURRENTLY on live tables
  • Run ANALYZE after 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 = 128MB barely caches anything
  • Over-indexing — every index taxes every write; drop unused ones
  • Skipping ANALYZE after bulk loads — the planner bets on stale stats
  • VACUUM FULL during peak hours — it locks the whole table
  • Raising max_connections to 500+ without a pool — context switches eat the CPU
  • Indexing low-cardinality columns — a boolean index is nearly useless
  • Setting work_mem high globally — work_mem × connections × sorts can OOM the box
  • Ignoring bloat — dead tuples accumulate until sequential scans crawl
  • Tuning without a baseline — no pgbench/pg_stat_statements data means no proof it helped
  • Treating effective_cache_size as 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.