postgresql-optimization

PostgreSQL Optimization

Workflow for diagnosing and fixing PostgreSQL performance — query tuning, indexes, EXPLAIN analysis, server configuration, and maintenance.

Use this skill when

• Optimizing slow PostgreSQL queries
• Designing or reviewing indexing strategies
• Analyzing performance and identifying bottlenecks
• Tuning server configuration
• Managing production maintenance (VACUUM, ANALYZE, bloat)
• Setting up monitoring and alerting

Do not use this skill when

• You need schema design guidance (use postgresql or general DB skill)
• The bottleneck is at the application layer, not the database
• You need a DB-agnostic optimization guide

Workflow

1. Identify the performance problem: slow query, missing index, config, or maintenance
2. Measure current state with EXPLAIN ANALYZE before any change
3. Apply targeted fix at the right layer (index, query, config, maintenance)
4. Verify improvement with a second EXPLAIN ANALYZE
5. Monitor over time to confirm the fix holds under load

Phase 1: Performance Assessment

• Check PostgreSQL version and available features
• Review config: shared_buffers, work_mem, effective_cache_size, autovacuum
• Identify slow queries via pg_stat_statements or slow query log
• Analyze CPU, memory, disk I/O
• Map bottleneck category: scan type, join strategy, I/O, locking, vacuum lag

Phase 2: Query Analysis (EXPLAIN)

• Read node types: Seq Scan, Index Scan, Index Only Scan, Hash Join, Nested Loop, Merge Join
• Compare estimated vs actual row counts — large mismatch = stale statistics (run ANALYZE)
• Find expensive operations: high cost nodes, high actual rows, large loops
• Identify opportunities: missing index, suboptimal join order, unnecessary sorts

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Phase 3: Indexing Strategy

Pattern	Index type
Equality + range on 1 column	B-tree
Multi-column equality	Composite B-tree, most selective first
JSONB containment	GIN
Full-text search	GIN on tsvector
Range overlap, scheduling	GiST on range type
Hot subset (WHERE status='active')	Partial index
Avoid heap reads for extra columns	Covering index with INCLUDE
Time-series, naturally ordered	BRIN