-- Step 1: Identify slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Step 2: Analyze a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets
-- Step 3: Create a targeted index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status = 'pending'; -- partial index reduces size
-- Step 4: Verify the index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Confirm: Index Scan on idx_orders_customer_status, lower actual time
-- Step 5: Update statistics if needed after bulk changes
ANALYZE orders;
-- Create GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- Efficient JSONB containment query (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';
-- Extract nested value
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';
VACUUM and Bloat Monitoring
-- Check tables with high dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
-- Manually vacuum a high-churn table and verify
VACUUM (ANALYZE, VERBOSE) orders;
Replication Lag Monitoring
-- On primary: check standby lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
(sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;
Constraints
MUST DO
Use EXPLAIN (ANALYZE, BUFFERS) for query optimization
Verify indexes are actually used with EXPLAIN before and after creation
Use CREATE INDEX CONCURRENTLY to avoid table locks in production
Run ANALYZE after bulk data changes to refresh statistics
Monitor autovacuum; tune autovacuum_vacuum_scale_factor for high-churn tables
Use connection pooling (pgBouncer, pgPool)
Monitor replication lag via pg_stat_replication
Use prepared statements to prevent SQL injection
Use uuid type for UUIDs, not text
MUST NOT DO
Disable autovacuum globally
Create indexes without first analyzing query patterns
Use SELECT * in production queries
Ignore replication lag alerts
Skip VACUUM on high-churn tables
Store large BLOBs in the database (use object storage)
Deploy index changes without verifying the planner uses them
Output Templates
When implementing PostgreSQL solutions, provide:
Query with EXPLAIN (ANALYZE, BUFFERS) output and interpretation
Index definitions with rationale and pre/post verification
