Postgres Indexing for Builders, From Slow Queries to EXPLAIN

How should builders index the access pattern, not the table? #

Slow list pages, dashboards, and search boxes trace to two causes far more often than anything else: a missing index on the filtered or joined columns, or an N+1 pattern firing hundreds of queries where one would do. Both yield to schema and query changes. Neither needs a bigger instance.

Think of an index as a library card catalog. Without it, every lookup means walking every shelf. With it, you go straight to the aisle. The workflow: find the slow query in logs, read its plan, add the narrowest index serving the access pattern, verify the plan changed, migrate safely. Repeat per endpoint. Most apps need a handful of well-chosen indexes, not wallpaper.

Which index fits which access pattern? #

Postgres offers several index types, but the default B-tree fits most app queries, and the index types docs say so directly: B-trees handle equality and range queries on sortable data, which describes the bulk of WHERE, JOIN, and ORDER BY clauses. Start every user-owned table with an index on the owner column and every foreign key. Ownership filters and joins run on nearly every request.

Composite indexes serve multi-column filters in one structure, with column order matching the query: equality columns first, then range or sort columns. A filter on status plus created-at ordering wants one composite index, not two single-column ones the planner must awkwardly combine. The CREATE INDEX reference documents multicolumn support along with ordering options like DESC and NULLS LAST for mixed sort queries.

Partial indexes cover hot subsets: active orders, unread notifications, published posts. They stay small and fast by skipping cold rows entirely, using a WHERE predicate right in the index definition. Unique constraints double as indexes while enforcing correctness, so prefer them over uniqueness checks in application code.

Resist indexing everything. Each index taxes writes and storage, and unused indexes are pure overhead. Supabase's managing indexes guide puts the tradeoff plainly: indexes speed reads at the cost of writes and space, and the planner may ignore indexes it judges unhelpful. Index the queries you have, measured, not the queries you imagine.

-- B-tree for ownership filter plus ordering, one structure
CREATE INDEX idx_orders_owner_status_created
  ON orders (owner_id, status, created_at DESC);

-- Partial index for the hot subset only
CREATE INDEX idx_orders_unbilled
  ON orders (owner_id, created_at)
  WHERE billed = false;

-- Uniqueness that doubles as an index
CREATE UNIQUE INDEX idx_users_email ON users (email);

How do you read EXPLAIN without fear? #

EXPLAIN shows the plan, not the data. It displays how tables get scanned and which join algorithms bring rows together, with cost estimates for startup and total. Learn three signals and you can diagnose most slow queries.

Sequential scans on large tables mean no usable index: the database reads every row. The docs' own example contrasts a Seq Scan on an unindexed table with an Index Scan once a usable index exists. Nested loops over large inputs mean the join lacks index support on the inner side. High actual rows versus planned rows mean stale statistics, fixed with ANALYZE before buying hardware.

Compare before and after, always. Capture the plan for the slow query, add the candidate index, re-run EXPLAIN plus timing. A good change shows index scans replacing sequential scans with order-of-magnitude drops on representative data. If the plan does not move, the index serves a different query shape than the one running. Recheck column order and filter expressions instead of adding more indexes hopefully.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE owner_id = 'u_123' AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Wrap write statements in a transaction with rollback when using ANALYZE, per the docs' own warning: EXPLAIN ANALYZE actually executes. Reading plans should never modify data by accident.

How do you find and fix N plus one queries? #

The N+1 pattern loads a list with one query, then fires one more query per row for related data. Twenty orders become twenty-one queries. Two hundred become two hundred and one. It hides inside ORMs and lazy loaders, invisible until staging falls over.

Fixes in preference order: join or eager-load the relation in the original query, batch-load with a dataloader keyed by foreign key, or denormalize the hot field when reads dwarf writes. Detect them with query logging in development and per-request query counts in staging. Any list endpoint issuing triple-digit queries is guilty until proven innocent.

// N+1: one query per order inside the loop
const orders = await db.orders.list({ owner: userId });
for (const o of orders) {
  o.customer = await db.customers.get(o.customerId);
}

// Fixed: one join, one round trip
const orders = await db.orders.listWithCustomers({ owner: userId });

How do you migrate indexes safely in production? #

Index builds can lock writes on large tables. Standard builds block inserts, updates, and deletes until done, which on a live database can feel like an outage you scheduled yourself. The CREATE INDEX docs describe the alternative: CONCURRENTLY builds the index without locking out writes, at the cost of more total work and longer build time.

CREATE INDEX CONCURRENTLY idx_orders_owner_created
  ON orders (owner_id, created_at DESC);

Keep migrations small, reversible, and ordered: add the index, verify the plan in production, then remove the old workaround. Never bundle index creation with destructive changes in one migration. Failed concurrent builds can leave invalid indexes behind that still cost write overhead, so check build results and drop the debris.

Watch for bloat afterward. Heavy churn grows index bloat that maintenance reclaims. Monitor index sizes and unused-index reports quarterly, drop what nothing uses, and keep migration files as the deployable record of every schema decision. Catalogs stay fast when someone tends them. That someone is you, for one afternoon a quarter.

See how BYOB wires Postgres through Supabase

Who this is for (and who should skip it)? #

This post helps builders whose list pages slowed because the access pattern lacks an index or the ORM fires N plus one queries. If you run Postgres or Postgres compatible data and you can read EXPLAIN, the habits here pay back in one afternoon.

Skip deep index tuning if your app runs on Cloudflare D1 or a similar edge store where BYOB already handles the Drizzle adapter and migrations. Focus on owner filters and foreign keys first, then measure before adding more indexes.

• Best for developers fixing slow list pages with B-tree and composite indexes.
• Best for startups reading EXPLAIN plans and removing N-plus-one queries before larger hardware.
• Best for freelancers migrating indexes safely on live client databases.

What we learned building this? #

BYOB projects use Cloudflare D1 with a Drizzle adapter rather than a self hosted Postgres, so schema and index changes ship as small reversible migrations with concurrent style care for live data, as described in the BYOB Supabase integration guide and the D1 docs linked there. In generated apps every user owned table gets an owner column index and foreign key indexes first, then composite or partial indexes only where EXPLAIN shows sequential scans on large tables. That keeps writes lean and avoids wallpaper indexes that the planner ignores.