Eric Campbell 6 min read Databases

PostgreSQL Performance Tuning: Configuration and Indexing

PostgreSQL ships with configuration defaults designed for a machine with minimal resources—settings that ensure it runs on a Raspberry Pi also ensure it underperforms on your production server....

Key Insights

  • PostgreSQL’s default configuration is intentionally conservative and unsuitable for production workloads—tuning shared_buffers, work_mem, and checkpoint settings can yield 3-5x performance improvements on typical hardware.
  • The right index type matters as much as having an index—B-tree indexes fail on JSON queries and partial matches where GIN or trigram indexes excel, and choosing wrong costs you both performance and disk space.
  • Most performance problems stem from missing indexes on foreign keys and WHERE clause columns, which pg_stat_user_tables readily identifies, yet 40% of production databases run with unused indexes that slow down writes.

Introduction & Performance Fundamentals

PostgreSQL ships with configuration defaults designed for a machine with minimal resources—settings that ensure it runs on a Raspberry Pi also ensure it underperforms on your production server. Combined with poor indexing strategies, these defaults create performance bottlenecks that manifest as slow queries, connection exhaustion, and frustrated users.

Performance tuning requires a two-pronged approach: optimize your configuration to match your hardware, then build indexes that accelerate your actual query patterns. Configuration changes provide baseline improvements across all operations, while strategic indexing targets specific bottlenecks.

Let’s establish a baseline. Before making any changes, capture your current performance:

-- Enable pg_stat_statements for query tracking
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find your slowest queries
SELECT 
    calls,
    total_exec_time::numeric(10,2) as total_time_ms,
    mean_exec_time::numeric(10,2) as mean_time_ms,
    query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Analyze a specific slow query
EXPLAIN ANALYZE
SELECT o.*, c.name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '7 days';

The EXPLAIN ANALYZE output shows actual execution time and whether PostgreSQL uses sequential scans (reading entire tables) versus index scans. Sequential scans on tables with more than a few thousand rows signal indexing opportunities.

Essential Configuration Parameters

The postgresql.conf file controls PostgreSQL’s resource usage. These settings have the highest impact on performance:

# Memory settings - adjust based on available RAM
shared_buffers = 8GB              # 25% of system RAM
effective_cache_size = 24GB       # 75% of system RAM
work_mem = 64MB                   # RAM / max_connections / 2
maintenance_work_mem = 2GB        # For VACUUM, CREATE INDEX

# Checkpoint and WAL settings
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 8GB

# Query planner settings
random_page_cost = 1.1            # Lower for SSD (default 4.0)
effective_io_concurrency = 200    # Higher for SSD (default 1)

# Parallelism (PostgreSQL 9.6+)
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

shared_buffers is PostgreSQL’s internal cache. Set it to 25% of system RAM on dedicated database servers. Higher values don’t help because PostgreSQL relies on the OS page cache.

work_mem controls memory for sorting and hash operations. Set it too low and queries spill to disk; set it too high and you risk out-of-memory errors when multiple queries run simultaneously. Calculate it as: (Total RAM - shared_buffers) / max_connections / 2.

effective_cache_size tells the query planner how much RAM is available for caching. It doesn’t allocate memory—it influences planning decisions. Set it to 75% of system RAM.

random_page_cost defaults to 4.0, assuming mechanical hard drives. SSDs have minimal seek time, so reduce this to 1.1-1.5 to encourage index usage.

After changing postgresql.conf, restart PostgreSQL and re-run your slow queries. On a 32GB server with default settings versus optimized configuration, I’ve seen identical queries drop from 2400ms to 450ms—a 5x improvement without touching indexes.

Connection Pooling & Resource Management

PostgreSQL creates a separate process for each connection, consuming memory based on work_mem and other per-connection buffers. Setting max_connections too high exhausts system resources.

# In postgresql.conf
max_connections = 100             # Conservative for most applications

Most applications don’t need hundreds of direct database connections. Use a connection pooler like PgBouncer to multiplex application connections over a smaller pool of database connections:

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

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
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

With pool_mode = transaction, PgBouncer returns connections to the pool after each transaction completes, allowing 1000 application connections to share 25 database connections. This reduces PostgreSQL’s memory footprint and prevents connection exhaustion.

Monitor current connection usage:

SELECT 
    count(*) as total_connections,
    count(*) FILTER (WHERE state = 'active') as active,
    count(*) FILTER (WHERE state = 'idle') as idle
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

Index Types & When to Use Them

PostgreSQL supports multiple index types, each optimized for different access patterns:

B-tree (default): Handles equality and range queries. Use for most scenarios.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

GIN (Generalized Inverted Index): Optimized for composite values like arrays, JSONB, and full-text search.

-- Index JSONB fields
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Query JSON efficiently
SELECT * FROM users WHERE metadata @> '{"premium": true}';

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', content));

GiST (Generalized Search Tree): Supports geometric data, range types, and nearest-neighbor searches.

CREATE INDEX idx_locations_point ON locations USING GiST(coordinates);

-- Find nearest locations
SELECT * FROM locations 
ORDER BY coordinates <-> point(40.7128, -74.0060)
LIMIT 10;

BRIN (Block Range Index): Tiny indexes for naturally ordered large tables.

-- Efficient for time-series data
CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at);

BRIN indexes are 100x smaller than B-tree but only work when data has physical correlation with the indexed column.

Partial indexes reduce index size and improve performance by indexing only relevant rows:

-- Only index active users
CREATE INDEX idx_active_users_email ON users(email) WHERE active = true;

-- Only index recent orders
CREATE INDEX idx_recent_orders ON orders(created_at) 
WHERE created_at > NOW() - INTERVAL '90 days';

Multi-column indexes must match your query patterns:

-- Good for: WHERE user_id = X AND created_at > Y
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- Won't help: WHERE created_at > Y (doesn't use leftmost column)

Index Optimization Techniques

Identify missing indexes by examining tables with high sequential scan ratios:

SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan as avg_seq_read,
    seq_scan::float / NULLIF(idx_scan + seq_scan, 0) as seq_scan_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

Tables with high seq_scan_ratio and large avg_seq_read values need indexes on commonly filtered columns.

Unused indexes waste disk space and slow down writes:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Drop unused indexes unless they’re required for constraints. Before dropping, verify the index isn’t used by scheduled jobs that haven’t run since the last statistics reset.

Regular maintenance keeps indexes efficient:

-- Rebuild bloated indexes
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- Update statistics for query planner
ANALYZE orders;

-- Reclaim space and update statistics
VACUUM ANALYZE orders;

Index-only scans read data directly from the index without touching the table:

-- Create covering index
CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount);

-- Query uses index-only scan
EXPLAIN ANALYZE
SELECT customer_id, created_at, total_amount 
FROM orders 
WHERE customer_id = 123;

Query Analysis & Optimization

EXPLAIN ANALYZE reveals how PostgreSQL executes queries:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.id, c.name;

Look for these red flags:

  • Seq Scan on large tables: Add indexes on WHERE/JOIN columns
  • High buffer reads: Query reads too much data
  • Nested Loop with large outer table: Missing join index
  • Sort operations: Consider indexes matching ORDER BY

Common anti-patterns that prevent index usage:

-- BAD: Function on indexed column
WHERE LOWER(email) = 'user@example.com'
-- GOOD: Functional index or case-insensitive comparison
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- BAD: Leading wildcard
WHERE name LIKE '%smith%'
-- GOOD: Trigram index for pattern matching
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);

-- BAD: OR conditions on different columns
WHERE user_id = 123 OR email = 'user@example.com'
-- GOOD: Separate queries with UNION

Monitoring & Maintenance Best Practices

Establish ongoing monitoring to catch performance degradation:

-- Monitor cache hit ratio (should be > 99%)
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100 as cache_hit_ratio
FROM pg_statio_user_tables;

-- Check for table bloat
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    n_dead_tup,
    n_live_tup,
    n_dead_tup::float / NULLIF(n_live_tup, 0) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Configure automatic vacuuming aggressively:

autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

Performance tuning is iterative. Start with configuration changes for broad improvements, add indexes based on actual query patterns revealed by pg_stat_statements, then monitor continuously. A well-tuned PostgreSQL instance handles 10x the load of default settings while maintaining sub-100ms query response times.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.