PostgreSQL 17 Performance Tuning: Queries, Indexes & Configuration

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

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

-- EXPLAIN ANALYZE: See actual execution plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id
ORDER BY post_count DESC
LIMIT 20;

-- Sample output:
-- Limit (actual time=245.3..245.4 rows=20)
--   -> Sort (actual time=245.3..245.3 rows=20)
--     Sort Key: (count(p.id)) DESC
--     -> HashAggregate (actual time=243.1..244.2 rows=5000)
--       -> Hash Left Join (actual time=12.5..180.3 rows=45000)
--         -> Seq Scan on users u (actual time=0.1..8.2 rows=5000)  ← Needs index!
--             Filter: (created_at > '2026-01-01')
--             Rows Removed by Filter: 95000
--         -> Hash (actual time=3.2..3.2 rows=45000)
--           -> Seq Scan on posts p (actual time=0.1..2.1 rows=45000)

-- B-Tree: Equality and range queries (most common)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_posts_created ON posts(created_at DESC);

-- Composite index: WHERE author_id = ? AND published = true ORDER BY created_at
CREATE INDEX CONCURRENTLY idx_posts_author_published 
ON posts(author_id, published, created_at DESC);

-- Partial index: Only index active users (skips 90% of rows)
CREATE INDEX CONCURRENTLY idx_active_users 
ON users(email) WHERE deleted_at IS NULL;

-- GIN: Full-text search
CREATE INDEX CONCURRENTLY idx_posts_search 
ON posts USING gin(to_tsvector('english', title || ' ' || content));

-- GIN: JSONB containment (@> operator)
CREATE INDEX CONCURRENTLY idx_posts_tags 
ON posts USING gin(tags);
-- Enables: WHERE tags @> '["react", "performance"]'

-- BRIN: Time-series data (very compact, good for 100M+ row tables)
CREATE INDEX CONCURRENTLY idx_logs_created 
ON audit_logs USING brin(created_at) WITH (pages_per_range = 32);

-- Covering index: includes extra columns to avoid table lookup
CREATE INDEX CONCURRENTLY idx_users_email_covering 
ON users(email) INCLUDE (name, role);
-- SELECT name, role FROM users WHERE email = ? → index-only scan!

-- ❌ BAD: OFFSET pagination (slow for deep pages)
SELECT * FROM posts ORDER BY created_at DESC OFFSET 100000 LIMIT 20;
-- Scans 100,020 rows to return 20!

-- ✅ GOOD: Keyset pagination (constant speed on any page)
SELECT id, title, created_at FROM posts
WHERE created_at < '2026-03-15T10:30:00Z'  -- Last item from previous page
ORDER BY created_at DESC
LIMIT 20;

-- ❌ BAD: Function on indexed column (kills the index)
SELECT * FROM users WHERE LOWER(email) = 'ashutosh@ih.com';

-- ✅ GOOD: Use a functional index if you must
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- ❌ BAD: SELECT * (fetches all columns including large text/jsonb)
SELECT * FROM posts WHERE author_id = 123;

-- ✅ GOOD: Select only needed columns
SELECT id, title, excerpt, created_at FROM posts WHERE author_id = 123;

-- ❌ BAD: IN with large subquery
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = true);

-- ✅ GOOD: EXISTS (stops at first match)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active = true);

-- ✅ Batch operations: INSERT multiple rows in one statement
INSERT INTO posts (title, content, author_id) VALUES
  ('Post 1', 'Content 1', 1),
  ('Post 2', 'Content 2', 1),
  ('Post 3', 'Content 3', 2);
-- 1 network roundtrip instead of 3

# postgresql.conf — Production tuning for 16GB RAM, 4 CPU cores

# Memory
shared_buffers = 4GB              # 25% of RAM
effective_cache_size = 12GB       # 75% of RAM (includes OS cache)
work_mem = 64MB                   # Per-operation sort/hash memory
maintenance_work_mem = 1GB        # For VACUUM and CREATE INDEX

# WAL (Write-Ahead Log)
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Query planner
random_page_cost = 1.1            # SSD (default 4.0 is for spinning disks!)
effective_io_concurrency = 200     # SSD parallel reads

# Parallelism
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

# Autovacuum (prevent table bloat)
autovacuum_vacuum_scale_factor = 0.05   # Vacuum more frequently
autovacuum_analyze_scale_factor = 0.02  # Update stats more frequently

# Logging (for pg_stat_statements)
shared_preload_libraries = 'pg_stat_statements'
log_min_duration_statement = 200   # Log queries slower than 200ms