Indexes & Constraints cheat sheet
B-tree, partial, and covering indexes in Postgres, plus how warehouses replace indexes with pruning and clustering.
Index types (Postgres)
create index on orders (customer_id)- B-tree — the default and right answer for equality and range predicates on ordered types.
create index on orders (customer_id, ordered_at desc)- Composite index. Only useful when the leading column appears in the predicate; column order is not decorative.
create index on events using gin (payload)- GIN for JSONB containment and full-text search. Larger and slower to write than B-tree, so index the paths you query.
create index on orders (ordered_at) where status = 'open'- Partial index. Small and fast when queries always filter on the same subset — ideal for work-queue tables.
create index on customers (lower(email))- Expression index. Required for the index to be used by where lower(email) = ..., which a plain index cannot serve.
Constraints
primary key (order_id)- Uniqueness plus not-null plus an implicit index. Every OLTP table should have one that is never reused.
foreign key (customer_id) references customers (customer_id)- Enforced in Postgres; declarative-only in most warehouses. Test relationships in dbt rather than trusting the DDL.
unique (tenant_id, external_id)- The constraint that stops duplicate ingestion at the door and makes ON CONFLICT upserts possible.
check (amount >= 0)- Cheap invariant enforcement at write time. Catches the sign-flip bug before it reaches a dashboard.
not valid / validate constraint- Add a constraint without a full-table lock, then validate separately during a quiet window.
Reading plans
explain (analyze, buffers) select ...- Postgres. Compare estimated versus actual rows — a large gap usually means stale statistics or a correlated predicate.
Seq Scan on a large table- Not always wrong. Above roughly 5 to 10 percent selectivity a sequential scan genuinely beats random index lookups.
analyze orders- Refresh statistics after bulk loads. The planner makes bad choices from stale row counts, not from missing cleverness.
Warehouses have no indexes
cluster by (ordered_at, customer_id)- Snowflake's index analogue. It reorders micro-partitions so filters prune; it does not create a lookup structure.
add search optimization- Snowflake's actual point-lookup accelerator. Use it for selective equality lookups on high-cardinality columns.
partition by date(ordered_at) cluster by customer_id- BigQuery. Partitioning prunes whole blocks; clustering sorts within them. Together they replace most index thinking.
zorder / liquid clustering- Databricks Delta. Liquid clustering supersedes static ZORDER for new tables and adapts as query patterns change.
Maintenance
reindex concurrently- Rebuild a bloated index without blocking writes. Bloat grows fastest on frequently updated tables.
select * from pg_stat_user_indexes where idx_scan = 0- Finds unused indexes. Every one costs write throughput and storage — drop the ones nothing reads.
create index concurrently- Always use this in production. A plain CREATE INDEX takes a write lock for the duration of the build.
From DataLane — tutorials at/blog, practice SQL live in theplayground.