PostgreSQL Performance Tuning: Indexing, Query Plans, and Configuration

PostgreSQL defaults are conservative. This guide covers indexing strategies, query optimization, and configuration tuning Ventra Rocket applies to production databases.

1. EXPLAIN ANALYZE

Always measure before optimizing:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

Key metrics:

• Seq Scan → missing index (usually bad on large tables)
• Index Scan → index used (good)
• actual time=X..Y → real execution time
• rows=X vs estimated Y → large difference = stale statistics

-- Update statistics
ANALYZE users;

2. Indexing Strategies

-- Composite index — most selective column first
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Partial index — only relevant rows
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering index — avoids table lookup
CREATE INDEX idx_orders_covering ON orders(user_id)
  INCLUDE (total_amount, status, created_at);

-- GIN for JSONB
CREATE INDEX idx_metadata ON products USING gin(metadata);

3. Query Optimization

-- CTEs for complex queries
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
    AND created_at >= NOW() - INTERVAL '12 months'
  GROUP BY 1
),
ranked AS (
  SELECT *, RANK() OVER (ORDER BY revenue DESC) AS rank
  FROM monthly_revenue
)
SELECT month, revenue, rank FROM ranked ORDER BY month;

4. Connection Pooling with PgBouncer

[databases]
mydb = dbname=mydb server_pool_size=20

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

5. Table Partitioning

CREATE TABLE orders (
  id BIGSERIAL,
  user_id BIGINT,
  total NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

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

6. Key postgresql.conf Settings

# Memory (25% of RAM for shared_buffers)
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB

# SSD optimization
random_page_cost = 1.1
effective_io_concurrency = 200

# Autovacuum (more aggressive)
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005

Conclusion

PostgreSQL performance is 80% indexing and 20% configuration. Measure with EXPLAIN ANALYZE, use connection pooling, partition large time-series tables. These techniques have helped Ventra Rocket reduce query times from seconds to milliseconds.