Choosing Postgres indexes in Drizzle

The invoice queries you wrote last chapter return instantly against fifty seeded rows, because each one reads the whole table top to bottom and fifty rows is too few to feel that. Point the same query at a real tenant with fifty thousand invoices and it reads all fifty thousand to return the dozen it needs. The page that felt instant now crawls.

The fix is an index. The trap is the reflex “add an index to make it fast,” which buries a table under indexes that slow every write and earn nothing back. This lesson teaches the skill in between: reading a schema and its queries to know which columns deserve an index, which kind, and which to leave alone.

How an index works

Layer one: the shortcut

A table with no index is an unordered pile of rows. To answer where id = 42, the database can only start at the top and check each row, often reading to the end to confirm there is just one match. That is a sequential scan . On fifty rows it costs nothing; on fifty thousand it is the whole problem.

An index is a separate, sorted structure stored alongside the table. It maps each key to its row’s location and keeps the keys in order, so the database jumps straight to the row it wants, the way the index at the back of a book sends you to page 214 instead of flipping through every page.

Sequential scan

no shortcut — the table is an unordered pile

⇣ ⇣ ⇣ ▼        

id 17 read

id 3 read

id 88 read

id 42 ✓ match

id 9  

id 56  

id 21  

id 70  

reads rows until it finds the match — here it touched 4

Index scan

a separate, sorted copy of the key

▶

3 → row

9 → row

17 → row

21 → row

42 → row

56 → row

70 → row

88 → row

▶

✓ the row id 42

jumps straight to the key — one entry, then the row

An index changes the path to a row, never the row itself.

Layer two: the planner chooses

The database does not use an index just because one exists. Every query goes through the query planner , which uses statistics about the table’s size and value distribution to estimate the cost of each path, scanning the table or walking the index, and picks the cheaper one.

So an index is an option you offer the planner, not a command. This explains two things you will keep meeting. An index no query benefits from sits unused, still taxing every write. And the planner will rightly skip an index on a tiny table, or when a predicate matches most rows, because a straight scan beats bouncing between the index and the table.

Layer three: the cost

An index never changes a query’s result, only the path the engine takes to it, and that speed is not free. An index is a sorted copy of its key columns, so it takes disk space and must stay in sync with the table: every insert, update, or delete that touches an indexed column updates the index too. Add a row, and the database threads it into the sorted position of every index on the table. Ten indexes mean ten extra writes per row change. The trade-off runs one way: indexes speed up reads by taxing writes.

One last term. A predicate’s selectivity is the fraction of rows it keeps. where status = 'pending', when 5% of invoices are pending, is highly selective: it slices the table to a sliver. where status = 'paid', when 90% are paid, is not. Selectivity is the hinge the next section turns on.

Watch video

Spanning Tree’s 13-minute animation builds the sorted-tree structure under every B-tree index from the ground up, the same “shortcut” picture this section asks you to hold.

The four triggers: when a column earns an index

Picture yourself reviewing a pull request, a schema and the queries that run against it. For each column you ask one question: does it earn an index?

The default answer is no. Most columns in most tables never get one, because an index is a measured response to a read pattern, not a precaution. You are scanning for the four signals that flip the answer to yes.

Foreign-key columns

Start here, because this one is a trap.

Postgres does not automatically index foreign-key columns. Writing .references(() => orgs.id) in the schema chapter created a constraint: Postgres now refuses to insert an invoice pointing at an organization that does not exist. It did not create an index. You get the constraint for free; the index stays your job.

Every join probes the child table by its foreign key: “give me the line items where invoiceId = ?”. Every cascade delete on the parent does the same, since deleting an invoice sends Postgres looking for its line items. Without an index on invoiceId, both become sequential scans. On your dev seed the scan is invisible, so the missing index ships; months later, joins and cascade deletes on a large table slow to a crawl, with no error to point at.

So the call: every foreign-key column gets a B-tree index by default. This sits next to a correctness concern, so you ship it on day one without waiting for evidence, the same way you ship the constraint. As a reviewer it becomes a reflex: a .references(...) with no matching index(...) gets flagged.

Selective where predicates

A column you filter on earns an index only if the filter is selective.

where status = 'pending', when 5% of rows are pending, is worth indexing: the index hands the engine that small slice and skips the other 95%. where status = 'paid', when 90% of rows are paid, is not. Even with the index in place, the planner refuses it, because reading the index and then fetching nearly every row is slower than scanning the table once. A low-selectivity index is the worst of both worlds: it taxes your writes and the planner ignores it.

As a rule of thumb, the predicate should slice the table to roughly 5 to 10% or less before an index pays off. The planner makes the real call at query time from statistics it keeps current automatically, so you never tune them by hand.

Hold onto the low-selectivity case: a column useless to index as a whole can still earn one on its rare slice, and there is a tool for exactly that.

order by keys for pagination

The cursor pagination you built last chapter sorts every page by a stable key plus a tiebreaker, like order by createdAt desc, id desc. That sort runs on every request. Without an index in that exact order, the engine sorts the entire table from scratch each time.

A sort that runs on every request is a trigger. The index that satisfies it is a composite, multiple columns in the directions the query sorts, which we return to later in this lesson.

Unique constraints

This one is a freebie. Marking a column .unique() in the schema chapter made Postgres create a unique B-tree index under the hood, because enforcing uniqueness requires a sorted structure to check each new value against. The index already exists.

So the call here is a don’t: never add a separate index(...) for a column that already has .unique(). You would get two indexes doing one job at double the write cost.

Each chip is a column on the invoices schema (or a related table) paired with the query pattern that hits it. Apply the four triggers — and the discipline of *not* indexing — to decide which earns an index. Drag each item into the bucket it belongs to, then press Check.

Index it The read pattern earns the cost

Leave it An index would cost more than it returns

organizationId — every tenant query joins and filters on it

customerId — used to load all of one customer’s invoices

status — your dashboard filters = 'pending', and only ~5% of rows are pending

status — your report filters = 'paid', and ~90% of rows are paid

notes — free text shown on the detail page, never filtered or sorted

email — already declared .unique() in the schema

createdAt — the sort key your cursor pagination orders by on every page

Check Reset

If the 90%-paid case and the already-.unique() case made you pause before dropping them in “Leave it,” that pause is the point: those two separate “index to be safe” from reasoning about cost. The 5%-pending column has a sharper answer than a plain index, so keep it in mind for partial indexes.

Declaring indexes in Drizzle

Indexes go in the table definition’s third argument, the same callback that holds the composite constraints from the schema chapter: it returns an array of builders.

export const invoices = pgTable('invoices', {
  // columns…
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  uniqueIndex('invoices_external_id_unique').on(t.externalId),
]);

The callback receives the columns as t and returns an array, alongside the composite unique and check constraints from the schema chapter.

export const invoices = pgTable('invoices', {
  // columns…
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  uniqueIndex('invoices_external_id_unique').on(t.externalId),
]);

index(name).on(column) declares an index. With no modifier it emits a B-tree, the default and the right choice almost every time. .on() takes the column reference, not a string.

export const invoices = pgTable('invoices', {
  // columns…
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  uniqueIndex('invoices_external_id_unique').on(t.externalId),
]);

Always pass an explicit name, following the convention below.

export const invoices = pgTable('invoices', {
  // columns…
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  uniqueIndex('invoices_external_id_unique').on(t.externalId),
]);

uniqueIndex(...) works like index(...) but also enforces uniqueness, rejecting a duplicate at write time. Use it when the column must be unique; use plain index(...) when you only want read speed.

← Previous 1 / 1 Next →

The name matters. Omit it and Drizzle derives one from things like column order, so it shifts when you reorder or rename columns, and the migration tool in the next chapter writes that name verbatim into your SQL. A drifting name produces noisy migration diffs and turns a real collision, two indexes sharing a name, into a silent surprise instead of an error. The convention:

• idx_<table>_<col> for a B-tree, extended to idx_<table>_<col>_<col2> when it spans columns.
• idx_<table>_<col>_gin for a GIN index, idx_<table>_<col>_partial for a partial one (both covered below).
• <table>_<col>_unique for a unique index, extended with more columns as needed.

Note

What you get for free, and what you don’t.

• A primary key is unique and indexed automatically, so you never index it yourself.
• A .unique() column is indexed automatically, and that index enforces uniqueness.
• A .references(...) foreign key is not indexed; only the constraint is created.

That last line is the foreign-key trap from earlier: Postgres will not warn you, so you have to remember it.

Choosing an index type: B-tree first

You have decided a column earns an index. The second decision is which kind.

Almost always it is a B-tree , the default. Because it stores keys in sorted order, one B-tree serves an enormous range of query shapes: =, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL, and ORDER BY in either direction. Drizzle’s plain index(...).on(col) gives you one. The rule is blunt: if you are not sure, it is B-tree. Roughly 95% of the indexes in a typical web app schema are B-trees.

The other types each exist for one query shape a plain B-tree cannot serve. Treat them as exceptions, each triggered by something you see in the query.

Composite indexes and the leftmost-prefix rule

This is the order by index from the triggers section, in full. A composite index covers more than one column, and the key is its sort order: an index on (a, b, c) is sorted by a first, then by b within each equal-a group, then by c within each equal-a-and-b group. It works like a contact list sorted by last name, then first name, then middle name.

That order produces the leftmost-prefix rule: the index serves any query that uses a prefix of the columns, starting from the left.

• where a = ? works, because the index is sorted by a first.
• where a = ? and b = ? works.
• the full where a = ? and b = ? and c = ? works.
• order by a, b, c works, because that is exactly its sort order.

But it does not serve where b = ? alone. In the contact list you can find every “Smith” instantly, but not everyone named “Jordan,” because the Jordans are scattered one inside each last-name group; b is sorted only within each a, never globally. So column order in a composite index is a real decision.

where org_id = 7

one contiguous block — jump straight to it

org_id

created_at

7

2026-01-02

7

2026-01-05

7

2026-02-11

9

2026-01-03

9

2026-01-09

9

2026-03-01

12

2026-01-04

12

2026-02-20

◀

where created_at = '2026-01-03'

the early-January dates are scattered — not globally sorted, so the index can’t help

org_id leads → equal orgs sit together same early-January date, three different groups

A composite index on (org_id, created_at) is sorted by org_id first — so an org_id filter lands a contiguous block, but a created_at filter can’t use the sort.

In what order do the columns go? Equality predicate first, the range or sort key second, the tiebreaker last. Apply it to the cursor index pagination needs:

index('idx_invoices_org_created_at_id').on(
  t.organizationId,
  t.createdAt.desc(),
  t.id.desc(),
);

Each line earns its place. organizationId is the equality predicate every tenant query filters by, so it leads. createdAt.desc() is the sort key, in the exact descending direction the query orders. id.desc() is the tiebreaker that makes the order total. The .desc() encodes each direction into the index, and this is the index the cursor pagination query depends on.

Watch video

Supabase walks a slow recent-signins query into a multi-column index and shows why column order decides which queries it serves: the leftmost-prefix rule, demonstrated live.

Partial indexes: index only the rows that matter

Recall the low-selectivity case from the triggers section: a column whose filtered value is rare overall, so a full index is not worth it. A partial index is the rescue. It covers only the rows matching a predicate you attach with .where(...), so instead of indexing every invoice’s dueDate, you index it on the pending ones alone.

A partial index is smaller on disk, updates only when a matching row changes, and makes a globally non-selective column indexable on its rare slice. Reach for it on soft-delete filtering (where deleted_at is null, where most rows are live), status workflows where the hot state is rare (pending in a sea of paid), or any tenant carve-out that cannot be its own column.

Full index

index('idx_invoices_due_date').on(t.dueDate)

Indexes every invoice, paid, void, and draft alike. If the only query that uses it is the pending-invoices dashboard, most of the index is dead weight: disk and write cost on every invoice change, all to serve a query that only ever reads the pending slice.

Partial index

index('idx_invoices_pending_partial')
  .on(t.dueDate)
  .where(sql`status = 'pending'`)

Indexes only the pending invoices. When pending is 5% of the table, this index is roughly a twentieth of the size, updates only when a pending row changes, and serves the dashboard just as fast. The non-selective column became selective once you carved out its hot slice. Note the predicate is a raw sql template, not the eq() helper; the next paragraph says why.

Two things to nail down. First, write the .where() predicate as a raw sql`status = 'pending'` template, not with the eq() helper. A known bug makes eq() in a partial-index predicate emit a parameter placeholder instead of a literal, producing invalid SQL at index creation; the sql template is the correct form.

Second, the watch-out that catches everyone: the planner uses a partial index only when the query’s where includes the same predicate. A where status = 'pending' query uses idx_invoices_pending_partial; a where status = 'active' query does not, because the index lacks those rows. Even where status in ('pending') might not line up. A partial index serves only the slice it was built for.

Expression indexes: index a computed value

Here is a quiet way to lose an index. You build a plain B-tree on email, then write where lower(email) = ? for a case-insensitive lookup. The index is useless: wrap a column in any function, whether lower, date_trunc, or a cast, and a plain index on the raw column cannot be used, because it stores email, not lower(email). You are back to a sequential scan.

The fix is an expression index: index the expression the query computes, not the raw column.

index('idx_users_email_lower').on(sql`lower(${t.email})`)

Now where lower(email) = ? has an index sorted on exactly the value it compares. The signal is a where predicate that wraps a column in a function. The watch-out is the partial-index one, stricter: the index expression must match the query expression character-for-character. Slip a coalesce wrapper or an extra cast into the query and the match breaks, the index goes unused, and you are scanning again.

One alternative is worth naming. When a computed value is read often, store it in a generated column (a real column Postgres keeps in sync, from the schema chapter) and put a normal index on that. Use the expression index when the lookup is occasional, the generated column when the value is hot.

Unique indexes and the partial unique

.unique() and uniqueIndex(...) both create a unique B-tree, but only uniqueIndex accepts a .where(...) clause. That gives you a partial unique index: uniqueness enforced only among the rows matching a predicate, which a plain unique constraint cannot express.

Take “one primary contact per organization.” That is not “the is_primary column is unique,” because many rows have is_primary = false and those should not collide. You want uniqueness only among the rows where is_primary = true:

uniqueIndex('org_members_one_primary_unique')
  .on(t.organizationId)
  .where(sql`${t.isPrimary} = true`)

The same pattern handles soft deletes. unique on (org_id, slug) where deleted_at is null constrains only the live rows, so a slug can be reused once the row holding it is archived. Whenever the rule is “unique, but only among a subset,” reach for the partial unique index.

GIN: the index for composite-value columns

Everything so far indexes one scalar value per row. But two of the query shapes you built ask not “what is the value in this column?” but “is this thing inside this column?” Full-text search asks whether a word appears in a document; a jsonb containment query asks whether a fragment sits inside a JSON blob. A B-tree cannot answer either, because it sorts whole values, and “contains” has no useful sort order.

That is what a GIN index is for. It inverts the mapping: instead of each row to its value, it maps each contained value back to the rows that hold it, every word to its documents, every JSON fragment to its blobs.

Both features you built last chapter were waiting for it:

• The tsvector full-text column needs index('idx_invoices_search_vector_gin').using('gin', t.searchVector). The .using('gin', ...) modifier selects GIN instead of the default B-tree.
• The jsonb column you queried with @> needs index('idx_events_payload_gin').using('gin', t.payload). GIN covers array membership the same way.

The cost: GIN is slower to write, because it indexes every contained token, so a fifty-word document threads fifty entries into the index. For these query shapes that is no trade at all; a B-tree cannot answer “contains” however you build it. One refinement: for jsonb columns queried only with @> containment, never with the key-existence ? operators, the jsonb_path_ops operator class produces a smaller, faster index.

From query shape to index type

The lesson is not the list of types; it is the order of questions that lands you on one. Start from the query shape and let the type fall out:

Which index type?

Note

Named so you recognize them. A handful of other index types exist; you will not reach for them building a 2026 web app, but know the names. BRIN (block-range) is tiny and suits very large, naturally ordered columns, such as an append-only event log past tens of millions of rows. GiST and SP-GiST serve geometric and range types. Hash indexes are crash-safe now, but a B-tree almost always wins.

What an index costs, and the day-one rule

When a query feels slow, the instinct is to add an index. But every index is a standing cost you pay forever, whether or not it ever helps.

There are three costs:

1. The write tax. Every insert, update, or delete that touches an indexed column must update the index too. Ten indexes on a hot table mean ten extra writes for every row you change, paid forever.
2. Disk. Each index is a full sorted copy of its key columns. A dozen on a wide table is real storage, and real backup and replication weight.
3. Planner cost. More indexes mean a larger search space on every query: a small per-query tax, and more chances to pick the wrong path.

So an index is a measured response to a read pattern, never preemptive. The rule this course adopts:

• Ship on day one: foreign-key indexes and unique indexes. Both sit close to correctness: the foreign-key index prevents the silent slow-cascade trap, and the unique index is the constraint. They go in with the schema, no evidence required.
• Everything else waits for evidence. No index goes in until EXPLAIN ANALYZE shows the query needs it. “Feels slow” is not evidence.

Two more watch-outs:

• Low-cardinality columns — booleans, small enums, anything with a handful of distinct values — are usually worse indexed than scanned. The index cannot be selective enough to beat the scan, so the planner ignores it while you pay the write tax. To index one rare value, use a partial index on that value, not a full index on the column.
• The production lock-out. Adding an index to a table taking live writes locks those writes for the whole build, unless you build it with CONCURRENTLY. On a busy table that lock can mean a visible outage, so any index on a table with real write traffic uses CONCURRENTLY.

A table takes about 10,000 inserts per minute. One admin report, run a few times a day, filters where status = 'archived' — and archived rows are 0.5% of the table. What’s the right index call?

A plain B-tree on status.

A partial index on dueDate (or whatever the report reads), with .where(sql\status = ‘archived’`)`.

No index — let the report take the sequential scan.

Index every column the report touches, so the planner has options.

Indexing the invoices table

Now apply both frames, triggers and types, to a table you already have. Here is the full index set for the invoices schema, each index named by its trigger and type.

export const invoices = pgTable('invoices', {
  // … columns from the schema chapter
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  index('idx_invoices_customer_id').on(t.customerId),
  uniqueIndex('invoices_org_external_id_unique').on(
    t.organizationId,
    t.externalId,
  ),
  index('idx_invoices_org_created_at_id').on(
    t.organizationId,
    t.createdAt.desc(),
    t.id.desc(),
  ),
  index('idx_invoices_pending_partial')
    .on(t.dueDate)
    .where(sql`status = 'pending'`),
  index('idx_invoices_search_vector_gin').using('gin', t.searchVector),
]);

Trigger: foreign key. Type: B-tree default. Both columns are joined and filtered constantly, and Postgres indexes neither automatically, so these two lines close the silent slow-cascade trap.

export const invoices = pgTable('invoices', {
  // … columns from the schema chapter
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  index('idx_invoices_customer_id').on(t.customerId),
  uniqueIndex('invoices_org_external_id_unique').on(
    t.organizationId,
    t.externalId,
  ),
  index('idx_invoices_org_created_at_id').on(
    t.organizationId,
    t.createdAt.desc(),
    t.id.desc(),
  ),
  index('idx_invoices_pending_partial')
    .on(t.dueDate)
    .where(sql`status = 'pending'`),
  index('idx_invoices_search_vector_gin').using('gin', t.searchVector),
]);

Trigger: webhook idempotency. Type: unique index. It rejects a duplicate provider event id, so a retried webhook cannot create a duplicate invoice. The org column leads because uniqueness is per-tenant, and the same index serves lookups by external id.

export const invoices = pgTable('invoices', {
  // … columns from the schema chapter
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  index('idx_invoices_customer_id').on(t.customerId),
  uniqueIndex('invoices_org_external_id_unique').on(
    t.organizationId,
    t.externalId,
  ),
  index('idx_invoices_org_created_at_id').on(
    t.organizationId,
    t.createdAt.desc(),
    t.id.desc(),
  ),
  index('idx_invoices_pending_partial')
    .on(t.dueDate)
    .where(sql`status = 'pending'`),
  index('idx_invoices_search_vector_gin').using('gin', t.searchVector),
]);

Trigger: cursor pagination’s order by. Type: composite B-tree. Equality column (organizationId) first, sort key (createdAt desc) next, tiebreaker (id desc) last, in the exact directions the query orders.

export const invoices = pgTable('invoices', {
  // … columns from the schema chapter
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  index('idx_invoices_customer_id').on(t.customerId),
  uniqueIndex('invoices_org_external_id_unique').on(
    t.organizationId,
    t.externalId,
  ),
  index('idx_invoices_org_created_at_id').on(
    t.organizationId,
    t.createdAt.desc(),
    t.id.desc(),
  ),
  index('idx_invoices_pending_partial')
    .on(t.dueDate)
    .where(sql`status = 'pending'`),
  index('idx_invoices_search_vector_gin').using('gin', t.searchVector),
]);

Trigger: the hot pending-invoices dashboard query. Type: partial index. pending is a small slice, so the index stays tiny and cheap, indexing only the hot rows of a column that is not selective overall.

export const invoices = pgTable('invoices', {
  // … columns from the schema chapter
}, (t) => [
  index('idx_invoices_org_id').on(t.organizationId),
  index('idx_invoices_customer_id').on(t.customerId),
  uniqueIndex('invoices_org_external_id_unique').on(
    t.organizationId,
    t.externalId,
  ),
  index('idx_invoices_org_created_at_id').on(
    t.organizationId,
    t.createdAt.desc(),
    t.id.desc(),
  ),
  index('idx_invoices_pending_partial')
    .on(t.dueDate)
    .where(sql`status = 'pending'`),
  index('idx_invoices_search_vector_gin').using('gin', t.searchVector),
]);

Trigger: full-text search. Type: GIN. GIN is the only structure that serves tsvector search.

← Previous 1 / 1 Next →

Every index traces back to a named trigger and a justified type, and together they cover nearly every artifact from the last two chapters. That is what indexes following the read patterns instead of leading them looks like.

Each index is a hypothesis that a query needs it. Ship the two correctness-adjacent ones, the foreign-key and unique indexes, on day one; confirm the rest with EXPLAIN ANALYZE two lessons from now.

Now write a set yourself. The exercise gives you a trimmed org_members table. Add the foreign-key index, the composite unique that carries the tenant column, and a partial unique index for one primary member per organization. The probes insert two non-primary members in one org (must succeed) and two primary members in one org (must be rejected), so you see the partial unique fire.

Add three indexes to org_members in the third-argument array. (1) A B-tree index on the org_id foreign key — Postgres won't add it for you. (2) A composite unique index on (org_id, user_id), tenant column first, so a user can't be added to the same org twice. (3) A partial unique index enforcing one primary member per org — unique on org_id where is_primary = true. Write the partial predicate as a raw sql template, not eq().

Check schema Feedback Reset

Answer

export const orgMembers = pgTable('org_members', {
  id: integer('id').primaryKey(),
  orgId: integer('org_id').notNull().references(() => orgs.id),
  userId: integer('user_id').notNull(),
  isPrimary: boolean('is_primary').notNull().default(false),
}, (t) => [
  index('idx_org_members_org_id').on(t.orgId),
  uniqueIndex('org_members_org_id_user_id_unique').on(t.orgId, t.userId),
  uniqueIndex('org_members_one_primary_unique')
    .on(t.orgId)
    .where(sql`is_primary = true`),
]);

The composite unique leads with orgId (tenancy first); the partial unique constrains only the is_primary = true rows, so two non-primary members coexist in one org but two primaries can’t. Write the predicate as a raw sql`is_primary = true` template, never eq(), or the generated CREATE INDEX emits an invalid parameter placeholder.

External resources

Use The Index, Luke!

use-the-index-luke.com

The canonical free explainer for the B-tree and leftmost-prefix model this lesson's intuition comes from.

Drizzle ORM — Indexes & constraints

orm.drizzle.team

The declaration reference for index, uniqueIndex, .on(), .where(), and .using(...).

PostgreSQL docs — Chapter 11: Indexes

postgresql.org

The authoritative reference on every index type, plus partial, expression, and multicolumn indexes.

pganalyze Index Advisor

pganalyze.com

Paste a query and a schema and get a recommended index — the measure-don't-guess discipline as a tool.