curiositech/postgresql-optimization

PostgreSQL Optimization

Overview

Expert in PostgreSQL performance tuning, query optimization, and database administration. Specializes in EXPLAIN analysis, indexing strategies, connection pooling, partitioning, and production-grade PostgreSQL operations.

Decision Points

Index Type Selection Decision Tree

Query Pattern Analysis:
├── Equality lookups (=, IN)?
│   ├── High cardinality column → B-tree index
│   └── Low cardinality (<100 unique) → Consider partial indexes or skip
├── Range queries (>, <, BETWEEN)?
│   ├── Date/time columns → B-tree index (created_at DESC for recent data)
│   ├── Large table (>1M rows) + sparse data → BRIN index
│   └── Regular ranges → B-tree index
├── Full-text search or JSONB containment?
│   ├── JSONB @>, ?, ?& operators → GIN index with jsonb_path_ops
│   ├── Full-text search (tsvector) → GIN index
│   └── JSONB keys/values extraction → GIN index
├── Array operations (ANY, ALL)?
│   └── GIN index on array column
└── Exact hash lookups only?
    ├── Large table + no range queries → HASH index
    └── Default → B-tree index (more flexible)

Query Optimization Strategy

| EXPLAIN Output Indicator | Decision Path | Action | |--------------------------|---------------|---------| | "Seq Scan" on large table (>10k rows) | Missing index | Create B-tree index on WHERE clause columns | | "Nested Loop" with high cost | Inefficient join | Add index on join keys, consider hash/merge join | | "Sort" with "external merge" | Insufficient memory | Increase work_mem or add index to avoid sort | | High "Rows Removed by Filter" | Late filtering | Move WHERE conditions earlier, use partial index | | "Bitmap Heap Scan" with low selectivity | Index not selective enough | Create multi-column index or partial index |

Connection Pool Sizing

Application Analysis:
├── Request pattern: Burst traffic?
│   ├── Yes → pool_mode = transaction, max_client_conn = 5x default_pool_size
│   └── No → pool_mode = session, max_client_conn = 2x default_pool_size
├── Query duration: Long-running queries (>30s)?
│   ├── Yes → pool_mode = session, higher reserve_pool_size
│   └── No → pool_mode = transaction for better throughput
└── Database connections available:
    ├── max_connections > 200 → default_pool_size = max_connections / 4
    └── max_connections ≤ 200 → default_pool_size = 20-25

Failure Modes

Index Bloat Death Spiral

• Symptoms: Queries slowing down over time despite unchanged code, pg_stat_user_tables shows high n_dead_tup
• Diagnosis: SELECT pg_size_pretty(pg_relation_size('table_name')) shows growing size but SELECT count(*) stable
• Fix: Regular VACUUM or tune autovacuum (reduce autovacuum_vacuum_scale_factor to 0.1)

Connection Pool Exhaustion

• Symptoms: "remaining connection slots are reserved" errors, app timeouts
• Diagnosis: SELECT count(*) FROM pg_stat_activity WHERE state = 'active' near max_connections
• Fix: Implement PgBouncer with transaction pooling, reduce connection timeouts in app

Query Plan Regression

• Symptoms: Previously fast queries become slow after data growth or schema changes
• Diagnosis: EXPLAIN shows different plan, pg_stat_statements shows exec_time spike
• Fix: Run ANALYZE table_name, consider query hints or restructure query

Deadlock Cascade

• Symptoms: Multiple transactions failing with deadlock errors, application retries creating more deadlocks
• Diagnosis: Check pg_logs for deadlock details, identify conflicting lock patterns
• Fix: Establish consistent lock ordering, reduce transaction scope, add explicit locking

Memory Configuration Mismatch

• Symptoms: OOM kills or "could not allocate memory" errors despite available RAM
• Diagnosis: shared_buffers + work_mem * max_connections exceeds available memory
• Fix: Reduce work_mem or max_connections, or increase shared_buffers to reduce double-buffering

Worked Examples

Optimizing a Slow Analytics Query

Scenario: Dashboard query taking 45 seconds to load monthly sales data

-- Initial slow query
SELECT 
  u.name, 
  COUNT(o.id) as order_count,
  SUM(o.total_amount) as revenue
FROM users u
JOIN orders o ON o.user_id = u.id 
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2024-02-01'
GROUP BY u.id, u.name
ORDER BY revenue DESC;

Step 1: Analyze current plan

EXPLAIN (ANALYZE, BUFFERS) [query above];
-- Result: Seq Scan on orders (cost=0.00..450000 rows=2000000)
-- Shows: scanning 2M rows to find 50k matching rows

Expert catches: Date range filter eliminating 95% of rows suggests need for date index Novice misses: Might try to optimize the JOIN first instead of the WHERE filter

Step 2: Create targeted index

-- Index for date filtering and JOIN
CREATE INDEX CONCURRENTLY idx_orders_created_user 
ON orders (created_at, user_id) 
INCLUDE (total_amount);

Step 3: Re-analyze

EXPLAIN (ANALYZE, BUFFERS) [query];
-- New result: Index Scan using idx_orders_created_user (cost=0.43..15000 rows=50000)
-- Query time: 45s → 1.2s

Expert catches: INCLUDE clause enables index-only scan, eliminating table lookup Novice misses: Would create separate indexes instead of one covering index

Quality Gates

• [ ] All tables >10k rows have appropriate indexes for common WHERE clauses
• [ ] No sequential scans on tables >100k rows in production query plans
• [ ] Average query execution time <500ms for 95th percentile
• [ ] Database cache hit ratio >95% (check pg_stat_database.blks_hit/(blks_read+blks_hit))
• [ ] Index size <3x table size for any single table
• [ ] Connection pool utilization <80% during peak load
• [ ] No queries using >25% of work_mem causing disk sorts
• [ ] Autovacuum completing within maintenance windows
• [ ] No tables with >20% dead tuple ratio
• [ ] All foreign keys have corresponding indexes for JOIN performance

NOT-FOR Boundaries

Do NOT use this skill for:

• Database schema design → Use database-modeler for ERD design and normalization decisions
• Application-level caching → Use redis-caching-expert for cache layer architecture
• Data pipeline optimization → Use data-pipeline-engineer for ETL performance
• Backup and disaster recovery → Use site-reliability-engineer for backup strategies
• Cross-database migrations → Use database-migration-expert for schema migrations
• Real-time streaming → Use event-streaming-architect for Kafka/streaming solutions

Delegate when:

• Query optimization requires application logic changes → Application architect
• Performance issues stem from network latency → Infrastructure team
• Database choice evaluation needed → Data architect
• Compliance or security requirements → Database security specialist