PostgreSQL Performance Optimization 2026: Complete Tuning Guide

-- Essential indexing patterns

-- 1. Composite indexes (column order matters!)
CREATE INDEX idx_orders_user_status_date 
  ON orders (user_id, status, created_at DESC);

-- Query that benefits (uses all columns left-to-right)
SELECT * FROM orders 
WHERE user_id = 'uuid' AND status = 'pending'
ORDER BY created_at DESC;

-- 2. Partial indexes (index only what you query)
CREATE INDEX idx_orders_pending 
  ON orders (user_id, created_at) 
  WHERE status = 'pending';

-- Much smaller index, faster for common query
SELECT * FROM orders 
WHERE user_id = 'uuid' AND status = 'pending';

-- 3. Expression indexes
CREATE INDEX idx_users_email_lower 
  ON users (LOWER(email));

-- Enables fast case-insensitive lookup
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 4. GIN indexes for JSONB
CREATE INDEX idx_products_metadata 
  ON products USING GIN (metadata jsonb_path_ops);

-- Fast JSONB containment queries
SELECT * FROM products 
WHERE metadata @> '{"category": "electronics"}';

-- 5. Covering indexes (include columns to avoid table lookup)
CREATE INDEX idx_orders_covering 
  ON orders (user_id, status) 
  INCLUDE (total, created_at);

-- Index-only scan possible
SELECT total, created_at FROM orders 
WHERE user_id = 'uuid' AND status = 'completed';

-- 6. BRIN indexes for time-series data
CREATE INDEX idx_events_created_brin 
  ON events USING BRIN (created_at);

-- Very small index for naturally ordered data
-- Works well when data is inserted in order

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.name as user_name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Common optimization patterns

-- 1. Avoid SELECT * in production
-- Bad
SELECT * FROM orders WHERE user_id = 'uuid';
-- Good
SELECT id, status, total, created_at FROM orders WHERE user_id = 'uuid';

-- 2. Use EXISTS instead of IN for subqueries
-- Slower
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- Faster
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id AND o.total > 1000
);

-- 3. Pagination with keyset instead of OFFSET
-- Slow for large offsets
SELECT * FROM orders ORDER BY created_at DESC OFFSET 10000 LIMIT 20;
-- Fast regardless of position
SELECT * FROM orders 
WHERE created_at < '2026-01-01T00:00:00Z'
ORDER BY created_at DESC 
LIMIT 20;

-- 4. Use CTEs wisely (they can be optimization fences)
-- This CTE is materialized (can be slower)
WITH user_orders AS (
  SELECT * FROM orders WHERE user_id = 'uuid'
)
SELECT * FROM user_orders WHERE status = 'pending';

-- Use NOT MATERIALIZED if you want the planner to inline
WITH user_orders AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE user_id = 'uuid'
)
SELECT * FROM user_orders WHERE status = 'pending';

-- 5. Batch operations
-- Instead of many single inserts
INSERT INTO events (type, data) VALUES ('view', '{}')
-- Use batch insert
INSERT INTO events (type, data) VALUES 
  ('view', '{}'),
  ('click', '{}'),
  ('purchase', '{}');

-- Or COPY for bulk loading
COPY events (type, data) FROM '/path/to/data.csv' WITH CSV;

# postgresql.conf optimizations
# Adjust based on your hardware and workload

# Memory Settings
shared_buffers = 8GB                    # 25% of RAM
effective_cache_size = 24GB             # 75% of RAM
work_mem = 256MB                        # Per-operation memory
maintenance_work_mem = 2GB              # For VACUUM, CREATE INDEX

# Write-Ahead Log
wal_level = replica                     # For replication
max_wal_size = 4GB
min_wal_size = 1GB
wal_compression = on
checkpoint_completion_target = 0.9

# Query Planning
random_page_cost = 1.1                  # Lower for SSDs (default 4.0)
effective_io_concurrency = 200          # For SSDs
default_statistics_target = 200         # Better query plans

# Parallelism
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
parallel_leader_participation = on

# Connection Management
max_connections = 200                   # Use connection pooling!
idle_in_transaction_session_timeout = 10min

# Logging (for debugging)
log_min_duration_statement = 1000       # Log queries > 1s
log_lock_waits = on
log_temp_files = 0                      # Log all temp file usage
auto_explain.log_min_duration = 1000    # Auto-explain slow queries

// Application-level connection pooling with pg
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  
  // Pool configuration
  max: 20,                    // Maximum connections
  min: 5,                     // Minimum connections
  idleTimeoutMillis: 30000,   // Close idle connections
  connectionTimeoutMillis: 5000,
  
  // Statement timeout
  statement_timeout: 30000,   // 30 seconds
});

// Proper connection handling
async function queryWithConnection<T>(
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release();
  }
}

// Transaction helper
async function transaction<T>(
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

// Usage
await transaction(async (client) => {
  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [100, fromAccountId]
  );
  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [100, toAccountId]
  );
});

-- Essential monitoring queries

-- 1. Find slow queries
SELECT 
  query,
  calls,
  mean_exec_time::numeric(10,2) as avg_ms,
  total_exec_time::numeric(10,2) as total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 2. Table and index sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) as indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 3. Unused indexes
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_scan as times_used
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. Cache hit ratio (should be > 99%)
SELECT 
  sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) as cache_hit_ratio
FROM pg_stat_database;

-- 5. Active connections
SELECT 
  state,
  count(*),
  array_agg(pid) as pids
FROM pg_stat_activity
GROUP BY state;

-- 6. Lock monitoring
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
JOIN pg_catalog.pg_stat_activity blocking_activity 
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;