PostgreSQL Performance Tuning 2026: Indexing, Configuration and Optimization

# postgresql.conf — production tuning
# Rule of thumb: 25% of RAM for shared_buffers

# Memory
shared_buffers = 4GB            # 25% of RAM (16GB server)
effective_cache_size = 12GB     # ~75% of RAM (tells planner how much OS cache is available)
work_mem = 64MB                 # per sort/hash operation (watch out: multiplies per connection)
maintenance_work_mem = 1GB      # for VACUUM, CREATE INDEX, ALTER TABLE

# Checkpoints
checkpoint_completion_target = 0.9  # spread checkpoint writes over 90% of interval
wal_buffers = 64MB                  # WAL buffer size (auto-tuned to 1/32 shared_buffers)
max_wal_size = 4GB                  # max WAL before checkpoint

# Query planning
default_statistics_target = 200    # more column statistics (default 100)
enable_partitionwise_join = on      # optimize joins on partitioned tables
enable_partitionwise_aggregate = on

# Parallelism
max_parallel_workers_per_gather = 4  # parallel query workers
max_parallel_workers = 8

# Connections
max_connections = 100  # use connection pooling (PgBouncer) instead of high max_connections

# WAL and replication
wal_level = replica      # for streaming replication
synchronous_commit = off  # async commit (small risk of data loss on crash = much faster writes)
                          # Use ON for financial/critical data

-- B-Tree (default) — for equality and range queries
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Partial index — index only relevant rows (much smaller, faster)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Covering index — include columns to avoid table fetch
CREATE INDEX idx_users_email_covering ON users (email)
INCLUDE (id, name, role);

-- GIN index — for full-text search, JSONB, arrays
CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('english', title || ' ' || content));
CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_events_payload ON events USING GIN (payload);  -- JSONB

-- BRIN index — for naturally ordered large tables (time-series, logs)
-- Much smaller than B-Tree, great for append-only data
CREATE INDEX idx_logs_timestamp ON application_logs USING BRIN (created_at);

-- Expression index
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- Now use: WHERE LOWER(email) = LOWER($1)

-- Check unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE 'pg_%'
ORDER BY tablename;

-- Check index size
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- EXPLAIN ANALYZE: identify slow queries
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.name, SUM(oi.total)
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at > NOW() - INTERVAL '30 days'
GROUP BY o.id, u.name
ORDER BY SUM(oi.total) DESC
LIMIT 20;

-- Look for:
-- Seq Scan on large tables -> needs index
-- Hash Join with large hash -> work_mem too low or missing index
-- Sort -> can often be replaced with index scan
-- rows=1000 (actual rows=100000) -> stale statistics -> ANALYZE table

-- Fix statistics issues
ANALYZE orders;
ANALYZE order_items;

-- Or set higher statistics target for key columns
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE orders;

-- Avoid SELECT * — fetch only needed columns
-- Bad: SELECT * FROM orders WHERE ...
-- Good: SELECT id, status, total FROM orders WHERE ...

-- Use EXISTS instead of COUNT for existence checks
-- Bad: SELECT COUNT(*) > 0 ...
-- Good:
SELECT EXISTS (SELECT 1 FROM orders WHERE user_id = 1 AND status = 'pending');

-- Use LIMIT when you don't need all rows
SELECT title FROM posts WHERE author_id = 1 ORDER BY created_at DESC LIMIT 5;

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

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction        # most efficient for stateless backends
max_client_conn = 1000         # max connections to PgBouncer
default_pool_size = 20         # connections to actual PostgreSQL
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
log_connections = 0            # disable for high-traffic
log_disconnections = 0

# Connect app to port 6432 instead of 5432
# DATABASE_URL=postgresql://user:pass@localhost:6432/mydb

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 20;

-- Table bloat check
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;

-- Run VACUUM if dead_pct > 20%
VACUUM ANALYZE orders;
VACUUM FULL orders;  -- full rebuild, locks table, use only during maintenance window

-- Range partitioning by date (excellent for time-series data)
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    user_id BIGINT,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Indexes on partitions (auto-inherited in PG 13+)
CREATE INDEX ON events (created_at, user_id);

-- Drop old partitions without VACUUM overhead
DROP TABLE events_2024_01;  -- instant, no locks on parent

-- Automate partition creation (use pg_partman extension)
-- Or: schedule monthly DDL in CI/cron

# SQLAlchemy: route reads to replica, writes to primary
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

PRIMARY_URL = "postgresql://user:pass@primary-host:5432/mydb"
REPLICA_URL = "postgresql://user:pass@replica-host:5432/mydb"

primary_engine = create_engine(PRIMARY_URL, pool_size=10, max_overflow=20)
replica_engine = create_engine(REPLICA_URL, pool_size=20, max_overflow=40, pool_pre_ping=True)

PrimarySession = sessionmaker(bind=primary_engine)
ReplicaSession = sessionmaker(bind=replica_engine)

# Route reads to replica
def get_read_session():
    return ReplicaSession()

# Route writes to primary
def get_write_session():
    return PrimarySession()