Index & Query Rules
Query and index standards prevent production regressions when ORMs generate SQL reviewers never see until p99 latency spikes.
//
Search across all documentation pages
Query and index standards prevent production regressions when ORMs generate SQL reviewers never see until p99 latency spikes.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, o.placed_at
FROM app.orders o
WHERE o.customer_id = $1
AND o.status = 'open'
ORDER BY o.placed_at DESC
LIMIT 50;Gate: No nested loop on million-row seq scan for API hot paths. Require index-friendly plan or documented exception in PR.
When to reach for this: API query review, BI access policies, and SME sign-off on ORM-generated SQL.
-- API list endpoint backing query
CREATE INDEX idx_orders_customer_status_placed
ON app.orders (customer_id, status, placed_at DESC)
WHERE status IN ('open', 'pending');
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, placed_at
FROM app.orders
WHERE customer_id = 42
AND status IN ('open', 'pending')
ORDER BY placed_at DESC
LIMIT 50;Review checklist for PR:
LIMIT or bounded date range presentEXPLAIN uses index scan or bitmap index scan on large tableSELECT * on wide JSONB tables in hot pathpg_stat_statements mean time baseline recorded for changed query| Rule | Rationale |
|---|---|
| EXPLAIN before merge | Catches planner surprises pre-prod |
| Bound row counts in APIs | Prevents OOM and runaway bills |
| Index predicate columns leftmost | Btree prefix matching |
| Avoid functions on indexed columns in WHERE | Unless expression index exists |
| Review N+1 ORM patterns | Hundreds of single-row lookups |
-- BAD: unbounded
SELECT * FROM app.events WHERE tenant_id = $1;
-- GOOD: cursor or keyset pagination
SELECT id, created_at, type
FROM app.events
WHERE tenant_id = $1 AND created_at < $2
ORDER BY created_at DESC
LIMIT 100;EXPLAIN row estimatestatement_timeoutANALYZE - temporary, not accepted for prod API-- One clear purpose per index
CREATE INDEX idx_events_tenant_created
ON app.events (tenant_id, created_at DESC);(tenant_id) when (tenant_id, created_at) exists(status, customer_id) but query filters customer_id first. Fix: Match filter selectivity order.ANALYZE in migration pipeline.plan_cache_mode if needed.| Alternative | Use When | Don't Use When |
|---|---|---|
| Materialized view | Expensive read pattern stable | Real-time consistency required |
| Read replica routing | Heavy reporting | Need latest writes |
Covering index INCLUDE | Index-only scan win | Write amplification too high |
For new queries on large tables, use ANALYZE in staging with realistic data volume. BUFFERS shows read amplification.
App author + DBA/SME on hot paths. Automate pg_stat_statements regression alerts in mature teams.
No - raw SQL still needs EXPLAIN and LIMIT rules.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 19, 2026