Claude Code for PostgreSQL Performance: Indexes, Query Plans, and Partitioning

PostgreSQL performance optimization is systematic: start with EXPLAIN ANALYZE to understand the query plan, check index usage, add missing indexes, and reformulate expensive queries. Partial indexes cover common filter patterns with a fraction of the storage. Table partitioning enables partition pruning for time-series data. PgBouncer handles connection multiplexing for serverless workloads. Claude Code analyzes query plans, suggests index strategies, generates partition schemes, and writes the configuration for production PostgreSQL deployments.

CLAUDE.md for PostgreSQL Performance

## PostgreSQL Context
- Version: PostgreSQL 16+ (Logical Replication, incremental sort improvements)
- Connection pooling: PgBouncer transaction mode (required for serverless)
- Extensions: pg_stat_statements (enabled), pg_trgm (trigram search), pgvector (embeddings)
- Monitoring: track_io_timing=on, track_functions=all for pg_stat_statements
- Autovacuum: aggressive settings for high-write tables (scale_factor=0.01, cost_delay=2)
- Indexes: analyze with pg_stat_user_indexes — drop unused (idx_scan = 0 after 30 days)
- Table sizes: use pg_size_pretty(pg_total_relation_size()) to check before/after

Reading EXPLAIN ANALYZE

-- Always include these options for complete plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    o.id,
    o.status,
    o.total_cents,
    c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
  AND o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Common plan nodes and what they mean:
-- Seq Scan: Full table scan — might need an index
-- Index Scan: Uses B-tree index for lookup + fetch — good
-- Index Only Scan: Uses covering index — best (no heap fetch)
-- Bitmap Heap Scan: Multiple row fetch via bitmap — good for range scans
-- Hash Join: Larger table hashed in memory — watch for high Memory Usage
-- Nested Loop: Inner query runs N times for each outer row — bad at scale
-- Sort: Sorts in memory (workmem) or to disk — check "(Disk: )" in output

-- Red flags in output:
-- "rows=1 loops=50000" — N+1 pattern
-- "Buffers: read=50000 hit=100" — low cache hit
-- "Sort Method: external merge Disk: 48kB" — needs work_mem increase
-- "actual time=5000..5001" — last row almost as slow as first: parallel needed

Index Design Patterns

-- Pattern 1: Composite index for common WHERE + ORDER BY
-- Query: WHERE status = X ORDER BY created_at DESC LIMIT N
CREATE INDEX CONCURRENTLY idx_orders_status_created 
    ON orders (status, created_at DESC);
-- CONCURRENTLY builds without table lock (safe for production)

-- Pattern 2: Partial index for common filter values
-- If 90% of queries are for 'pending' orders only
CREATE INDEX CONCURRENTLY idx_orders_pending_created 
    ON orders (created_at DESC) 
    WHERE status = 'pending';
-- Much smaller — only ~10% of rows instead of 100%

-- Pattern 3: Covering index (include non-key columns to avoid heap fetch)
-- Query: SELECT id, customer_id, total_cents FROM orders WHERE status = 'pending'
CREATE INDEX CONCURRENTLY idx_orders_status_covering
    ON orders (status)
    INCLUDE (id, customer_id, total_cents);
-- Enables Index Only Scan — zero heap access

-- Pattern 4: Expression index for case-insensitive search
CREATE INDEX CONCURRENTLY idx_customers_email_lower
    ON customers (lower(email));
-- Query can use: WHERE lower(email) = lower('[email protected]')

-- Pattern 5: Trigram index for LIKE/ILIKE anywhere search
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_orders_notes_trgm
    ON orders USING gin (notes gin_trgm_ops);
-- Query: WHERE notes ILIKE '%shipping delay%' uses this index

-- Pattern 6: JSONB index for deep property access
CREATE INDEX CONCURRENTLY idx_orders_metadata_source
    ON orders ((metadata->>'acquisition_source'));
-- Or GIN for arbitrary JSONB queries:
CREATE INDEX CONCURRENTLY idx_orders_metadata_gin
    ON orders USING gin (metadata);

-- Check index usage after deploying (wait 24h for realistic traffic)
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- Identify missing indexes (sequential scans on large tables)
SELECT
    relname AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup,
    seq_scan - idx_scan AS seq_vs_idx
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
  AND seq_scan > idx_scan
ORDER BY seq_scan DESC;

Table Partitioning

-- Range partitioning for time-series orders (most common pattern)
CREATE TABLE orders (
    id              UUID NOT NULL DEFAULT gen_random_uuid(),
    customer_id     UUID NOT NULL,
    status          TEXT NOT NULL,
    total_cents     INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE orders_2026_10 PARTITION OF orders
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE orders_2026_11 PARTITION OF orders
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE orders_2026_12 PARTITION OF orders
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- Default partition for data that doesn't fit (important for safety)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Indexes on each partition (or create on parent after Pg 11)
CREATE INDEX ON orders (created_at, status);

-- Automate partition creation (run monthly via pg_cron)
CREATE OR REPLACE FUNCTION create_monthly_partition(year INT, month INT)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := format('orders_%s_%s', year, lpad(month::TEXT, 2, '0'));
    start_date := make_date(year, month, 1);
    end_date := start_date + INTERVAL '1 month';
    
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );
    
    RAISE NOTICE 'Created partition %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- Verify partition pruning is working
EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';
-- Should show: "Partitions selected: 1" in the Append node

Query Optimization Patterns

-- Rewrite correlated subquery as JOIN
-- SLOW: correlated subquery runs once per outer row
SELECT 
    c.id,
    c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- FAST: lateral or aggregated join
SELECT 
    c.id,
    c.name,
    COALESCE(o.order_count, 0) AS order_count
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) o ON c.id = o.customer_id;

-- Efficient pagination with keyset (no OFFSET for large pages)
-- SLOW: OFFSET 10000 scans and discards 10000 rows
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- FAST: keyset pagination using last seen value
SELECT * FROM orders
WHERE created_at < '2026-10-15T12:00:00Z'  -- last_created_at from previous page
ORDER BY created_at DESC
LIMIT 20;

-- Window function instead of self-join
-- SLOW: self-join to calculate running total
SELECT 
    o.id,
    o.total_cents,
    (SELECT SUM(total_cents) FROM orders o2 WHERE o2.created_at <= o.created_at) AS running_total
FROM orders o;

-- FAST: window function
SELECT 
    id,
    total_cents,
    SUM(total_cents) OVER (ORDER BY created_at) AS running_total
FROM orders;

PgBouncer Configuration

# pgbouncer.ini — transaction mode for serverless/high-connection environments
[databases]
myapp = host=postgres port=5432 dbname=myapp pool_size=20

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction mode: connection returned to pool after each transaction
# Use for serverless functions, microservices
pool_mode = transaction

# Connection limits
max_client_conn = 1000       # Max incoming connections from app
default_pool_size = 20       # Max connections to PostgreSQL per database
reserve_pool_size = 5        # Extra connections for bursts
reserve_pool_timeout = 5     # Seconds to wait before using reserve

# Keep-alive
server_idle_timeout = 600
client_idle_timeout = 0      # No client timeout (app manages this)

# Logging
log_connections = 0          # Too noisy for production
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

For the Drizzle ORM that generates the SQL queries this guide optimizes, see the Drizzle ORM guide for query patterns and schema definitions. For the advanced PostgreSQL features in TimescaleDB for time-series data, the TimescaleDB guide covers hypertables and continuous aggregates. The Claude Skills 360 bundle includes PostgreSQL skill sets covering index design, query plan analysis, and partitioning strategies. Start with the free tier to try query optimization generation.