PostgreSQL Performance Tuning: Indexing, Query Optimization & Configuration

-- postgresql.conf or ALTER SYSTEM
ALTER SYSTEM SET log_min_duration_statement = 500;  -- Log queries > 500ms
ALTER SYSTEM SET log_statement = 'none';             -- Don't log all queries
SELECT pg_reload_conf();

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find top 10 slowest queries by total time
SELECT 
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

-- Single column
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Partial index (only index what you query)
CREATE INDEX idx_orders_pending ON orders (created_at) 
WHERE status = 'pending';
-- This index is tiny and lightning fast for: WHERE status = 'pending'

-- Index for sorting
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
-- Covers: WHERE user_id = X ORDER BY created_at DESC

-- Index: (user_id, status, created_at)
-- Uses index:
WHERE user_id = 1                                    -- Yes
WHERE user_id = 1 AND status = 'active'              -- Yes
WHERE user_id = 1 AND status = 'active' AND created_at > '2026-01-01'  -- Yes

-- Does NOT use index efficiently:
WHERE status = 'active'                              -- No (skips first column)
WHERE created_at > '2026-01-01'                      -- No (skips first two columns)

-- JSONB queries
CREATE INDEX idx_metadata ON products USING GIN (metadata);
-- Now fast: WHERE metadata @> '{"color": "red"}'

-- Full-text search
CREATE INDEX idx_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));
-- Now fast: WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & tuning')

-- Array contains
CREATE INDEX idx_tags ON posts USING GIN (tags);
-- Now fast: WHERE tags @> ARRAY['javascript']

-- Geographic queries (with PostGIS)
CREATE INDEX idx_location ON stores USING GIST (location);

-- Range types
CREATE INDEX idx_booking_dates ON bookings USING GIST (date_range);
-- Fast: WHERE date_range && '[2026-01-01, 2026-01-31]'

-- For tables where data is naturally ordered (e.g., time-series)
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);
-- Tiny index size, great for append-only tables with billions of rows

-- If you only need user_id and email:
CREATE INDEX idx_users_covering ON users (status) INCLUDE (user_id, email);

-- This query now reads ONLY the index, never touches the table:
SELECT user_id, email FROM users WHERE status = 'active';

-- Bad: reads all columns from disk
SELECT * FROM orders WHERE user_id = 123;

-- Good: only reads what's needed (can use index-only scan)
SELECT id, total, status FROM orders WHERE user_id = 123;

-- Slow on large tables (exact count)
SELECT COUNT(*) FROM orders;  -- Scans entire table

-- Fast approximate count
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

-- Fast exact count with conditions (if indexed)
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Make sure there's an index on status

-- Bad: inserting one by one in a loop
INSERT INTO logs (message) VALUES ('event 1');
INSERT INTO logs (message) VALUES ('event 2');
-- 1000 round trips for 1000 rows

-- Good: batch insert
INSERT INTO logs (message) VALUES 
  ('event 1'), ('event 2'), ('event 3'), ...;
-- 1 round trip

-- Good: COPY for bulk loading
COPY logs (message) FROM '/path/to/data.csv' CSV;
-- Fastest method for bulk data

-- Slow with large subquery result
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE country = 'IN');

-- Fast (stops at first match)
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM users u 
  WHERE u.id = o.user_id AND u.country = 'IN'
);

-- Bad: OFFSET scans and discards rows (gets slower with higher pages)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;
-- Scans 10020 rows, returns 20

-- Good: keyset pagination (constant speed)
SELECT * FROM products 
WHERE id > 10000          -- Last seen ID
ORDER BY id 
LIMIT 20;
-- Scans only 20 rows

# postgresql.conf

# Total RAM dedicated to PostgreSQL shared memory
# Rule: 25% of total system RAM
shared_buffers = 4GB          # For a 16GB server

# Memory per operation (sort, hash join, etc.)
# Rule: 64MB-256MB depending on query complexity
work_mem = 128MB

# Memory for maintenance (VACUUM, CREATE INDEX, etc.)
maintenance_work_mem = 1GB

# OS cache hint
# Rule: 50-75% of total RAM
effective_cache_size = 12GB

# Larger WAL for write-heavy workloads
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB

# Spread checkpoints to avoid I/O spikes
checkpoint_completion_target = 0.9

max_connections = 200

# But actually, use connection pooling!
# PgBouncer or Supavisor in front of PostgreSQL

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

[pgbouncer]
listen_port = 6432
pool_mode = transaction    # Best for web apps
max_client_conn = 1000     # Clients connect to PgBouncer
default_pool_size = 20     # PgBouncer opens only 20 connections to PostgreSQL

-- Update statistics (helps query planner make better decisions)
ANALYZE orders;

-- Reclaim dead tuple space
VACUUM orders;

-- Full maintenance (rewrites table, locks it)
VACUUM FULL orders;      -- Use sparingly, causes downtime

-- Check dead tuples
SELECT relname, n_dead_tup, n_live_tup, 
       round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables 
ORDER BY n_dead_tup DESC 
LIMIT 10;

-- Find unused indexes (wasting disk and slowing writes)
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 = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Drop unused indexes
DROP INDEX idx_never_used;

-- Tables with too many sequential scans (need indexes)
SELECT 
  relname,
  seq_scan,
  idx_scan,
  round(100.0 * seq_scan / greatest(seq_scan + idx_scan, 1), 2) AS seq_scan_pct,
  pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC
LIMIT 10;

-- Find slow queries
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- Analyze a query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- Check index usage
SELECT indexname, idx_scan FROM pg_stat_user_indexes WHERE tablename = 'orders';

-- Table sizes
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::text)) 
FROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(tablename::text) DESC;

-- Kill long-running query
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE duration > interval '5 minutes';

-- Current connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;