Postgres performance optimization and best practices. Use when writing or reviewing SQL queries, designing schemas, configuring connection pooling, fixing N+1 queries, creating indexes, implementing row-level security, diagnosing slow queries with EXPLAIN ANALYZE, or optimizing database performance.
Postgres Best Practices
Comprehensive performance optimization guide for Postgres. 31 rules across 8 categories, prioritized by impact — from critical (query performance, connection management) to incremental (advanced features).
Rule Categories by Priority
Priority
Category
Impact
Prefix
1
Query Performance
CRITICAL
query-
2
Connection Management
CRITICAL
conn-
3
Security & RLS
CRITICAL
security-
4
Schema Design
HIGH
schema-
5
Concurrency & Locking
MEDIUM-HIGH
lock-
6
Data Access Patterns
MEDIUM
data-
7
Monitoring & Diagnostics
LOW-MEDIUM
monitor-
8
Advanced Features
LOW
advanced-
Quick Reference
1. Query Performance (CRITICAL)
query-missing-indexes - Add indexes on WHERE and JOIN columns
query-composite-indexes - Create composite indexes for multi-column queries
query-covering-indexes - Use covering indexes to avoid table lookups
query-partial-indexes - Use partial indexes for filtered queries
query-index-types - Choose the right index type for your data
2. Connection Management (CRITICAL)
conn-pooling - Use connection pooling for all applications
