PostgreSQL Best Practices

Query Optimization

EXPLAIN ANALYZE (CRITICAL)

• Use EXPLAIN ANALYZE to understand query plans
• Identify slow operations: Seq Scan, Nested Loop
• Check row estimates vs actual rows
• Monitor buffers: shared hit vs read

Indexing (CRITICAL)

• B-tree: default, most use cases
• GIN: JSONB, arrays, full-text search
• GiST: geometry, range types
• BRIN: large sequential tables (time-series)
• Partial indexes: filtered queries
• Covering indexes (INCLUDE): avoid heap fetches