Choose PostgreSQL indexes from real query patterns

A slow endpoint is not a reason to index every column. The delay may come from excessive relation loading, a large response, lock waits or a query shape that does not match existing indexes. Start with the actual query, representative values and dataset size.

Describe the access pattern

Consider a dashboard showing the latest fifty orders for one merchant and one status. Filtering, ordering and limiting form one access pattern. Record its frequency, expected result size and cost. Fixing an N+1 query or selecting fewer columns may be more valuable than another index.

SELECT id, created_at, total
FROM orders
WHERE tenant_id = :tenant AND status = :status
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Candidate to evaluate, not a universal index:
CREATE INDEX orders_tenant_status_recent
ON orders (tenant_id, status, created_at DESC, id DESC);

Match the column order to the query

This candidate places tenant and status equality before the requested ordering. The identifier makes order deterministic when timestamps tie. It may not serve an unfiltered status list or a cross-tenant report equally well. Index behavior depends on index type, database version and data distribution; simple leading-column advice is not a complete account of every possible plan.

Read the plan, then measure

EXPLAIN describes the planned work. EXPLAIN ANALYZE actually executes the statement, so use it with appropriate care. Compare estimated and actual rows, execution time, sorting and reads. Sequential scanning can be reasonable when a query needs much of the table. Seeing “Index Scan” does not by itself prove a faster user experience.

Account for write costs

Indexes consume storage and add maintenance work as relevant data changes. A logistics system with frequent updates may speed up one dashboard while making ingestion more expensive. Measure write latency and index size. Do not remove a rarely used index without checking scheduled workloads and constraints.

Test representative distributions

Uniform fixtures can conceal one huge tenant among many small tenants, or a status shared by most records. Test common and unusual values. Include response serialization and network timing in endpoint measurement rather than attributing every delay to SQL.

Roll out with a reason and a baseline

Keep before-and-after measurements and choose an index creation strategy suitable for the production workload and database version. Observe both read and write behavior after deployment. Document the query that justifies each index so future schema changes preserve the original reasoning.

Scenario: one unusually large tenant

A query tested on a small tenant may look excellent while a large tenant’s dashboard remains slow. Keep representative test identifiers covering volume and status distribution, and compare how many rows the plan examines to return fifty results. If a deep page uses a large offset, revisit pagination itself; an index may not remove all the work implied by skipping many rows.

Document the indexing decision

Record the query served, dataset size, parameters and read/write measurements before and after the change. Assign ownership for reviewing the index when filters or ordering evolve. This small record prevents a costly index from surviving a removed feature and explains column ordering to the next developer, reducing near-duplicate indexes created under different names.

Prepared by: Noor Yasser