PostgreSQL Performance Optimization - Index Strategies & Query Tuning

# Shared memory - 25% of total RAM
shared_buffers = 4GB

# Memory per query operation
work_mem = 64MB

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

# OS cache estimate - 75% of total RAM
effective_cache_size = 12GB

# WAL settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Parallel queries
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Random disk read cost (lower for SSD)
random_page_cost = 1.1   # HDD: 4.0, SSD: 1.1
effective_io_concurrency = 200  # for SSD

-- Basic usage
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'active'
ORDER BY created_at DESC LIMIT 20;

-- Add BUFFERS and TIMING for detailed output
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2025-01-01'
ORDER BY o.total DESC;

# Log queries taking longer than 500ms
log_min_duration_statement = 500

# Enable pg_stat_statements extension
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Composite index: frequently used WHERE + ORDER BY combination
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at DESC);

-- Partial index: only index active records (smaller, faster)
CREATE INDEX idx_orders_active
ON orders (customer_id, created_at)
WHERE status = 'active';

-- Covering index: add required columns with INCLUDE for index-only scan
CREATE INDEX idx_orders_covering
ON orders (customer_id)
INCLUDE (total, status);

-- GIN index for JSONB field
CREATE INDEX idx_products_metadata
ON products USING GIN (metadata jsonb_path_ops);

-- Query: search within JSONB
SELECT * FROM products
WHERE metadata @> '{"color": "red", "size": "L"}'::jsonb;

-- GIN index for full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX idx_articles_search
ON articles USING GIN (search_vector);

-- Auto-update with trigger
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

-- BRIN index for log table (created_at naturally increases)
CREATE INDEX idx_logs_created
ON access_logs USING BRIN (created_at)
WITH (pages_per_range = 32);

-- Size comparison (100M row table)
-- B-tree: ~2.1 GB
-- BRIN:   ~48 KB

# Increase autovacuum worker count
autovacuum_max_workers = 5          # default: 3

# Run more frequently
autovacuum_naptime = 30s           # default: 1min

# Accumulate fewer dead tuples before triggering
autovacuum_vacuum_scale_factor = 0.05   # default: 0.2
autovacuum_analyze_scale_factor = 0.025  # default: 0.1

# I/O throttling - can be more aggressive on SSD
autovacuum_vacuum_cost_limit = 1000  # default: 200
autovacuum_vacuum_cost_delay = 5ms   # default: 2ms (PG 15+)

-- Aggressive vacuum for write-heavy table
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_limit = 2000
);

-- Check dead tuple status
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 4) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- BAD: reads all columns, index-only scan not possible
SELECT * FROM orders WHERE customer_id = 42;

-- GOOD: only required columns, index-only scan with covering index
SELECT id, total, status FROM orders WHERE customer_id = 42;

-- Pagination: OFFSET slows down with large values
-- BAD: skips 1M rows to read 20
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000;

-- GOOD: keyset pagination (cursor-based)
SELECT * FROM orders
WHERE id > 1000000
ORDER BY id LIMIT 20;