Postgres best practices

Use when writing or reviewing Postgres SQL, migrations, indexes, transactions, and query performance.

Schema design

• Prefer explicit types and constraints (NOT NULL, CHECK, FK) over app-only validation.
• Use surrogate keys (uuid, bigserial) when natural keys are unstable or wide.
• Normalize to avoid update anomalies; denormalize only with a measured read benefit and a plan to keep data consistent.

Indexing

• Index columns used in WHERE, JOIN, ORDER BY, and foreign keys.
• Composite indexes: column order matches filter selectivity (most selective / equality first when common).
• Avoid redundant indexes that duplicate another index’s left prefix.
• Revisit indexes after real query patterns and EXPLAIN (ANALYZE, BUFFERS) on slow paths.

Queries