Postgres does not index foreign keys for you: the Drizzle index this schema is missing, measured

A foreign key in Postgres guarantees that subscriptions.user_id points at a real user. It does not make looking up a user's subscriptions fast. Postgres indexes the referenced side, because the parent's primary key always has an index, but it never adds a Postgres foreign key index on the referencing column. The docs say so directly: "the declaration of a foreign key constraint does not automatically create an index on the referencing columns." The kit's migrations create exactly one secondary index, on usage_events. The column that getUserPlan filters on for every chat request has none. I loaded 200,000 users into a throwaway Postgres 16 and measured what that costs, then hit a trap in the obvious fix.

Which columns are exposed

Four tables point at user, all with ON DELETE cascade:

• billing_customers.user_id: also the table's primary key, so it is indexed.
• subscriptions.user_id: no index. getUserPlan reads it on every chat request.
• session.userId and account.userId: no index. Better Auth owns these tables.

The query that runs on every chat request is this one, from src/lib/billing/subscription.ts:

const [row] = await db()
  .select({ plan: subscriptions.plan })
  .from(subscriptions)
  .where(
    and(
      eq(subscriptions.userId, userId),
      inArray(subscriptions.status, [...ACTIVE_STATUSES]),
    ),
  )
  .orderBy(desc(subscriptions.updatedAt))
  .limit(1);

What I measured

Setup: the kit's two migrations, then 200,000 users, 200,000 subscriptions, 600,000 sessions and 200,000 accounts, generated with generate_series, followed by VACUUM ANALYZE. Timings are EXPLAIN ANALYZE execution times with a warm cache.

The plan lookup. Without an index, Postgres ran a parallel sequential scan. Each of its two workers discarded 100,000 rows to find one, touching 2,795 buffers, in 15.9 to 19.2 ms across five runs. With an index on user_id, it was an index scan touching 7 buffers, in 0.085 to 0.117 ms. That is more than 100 times faster, on a query that sits in front of every model call.

Size decides whether you notice. With no index, the same lookup took about 0.3 ms at 2,000 subscriptions, 2 to 4 ms at 20,000, and 15 to 20 ms at 200,000. A new product will not feel it. A product that works will, and the scan cost grows with the table while the indexed lookup stays flat.

Deleting a user. This is the cost people forget. Each cascade has to find the child rows, and without an index that means a scan per child table. Deleting one user, inside a transaction I rolled back:

• No indexes: 63.8 ms in total. The session cascade took 40.6 ms, account 12.9 ms and subscriptions 9.9 ms. billing_customers, indexed through its primary key, took 0.3 ms.
• Indexes on all three columns: 1.0 ms in total.

Account deletions are rare, but a GDPR purge or a spam cleanup deletes thousands of users in one go, and each of those scans runs once per deleted user.

The indexes are not free. At this size the one on subscriptions.user_id took 5.3 MB, against 22 MB for the table, and the session one took 10 MB. Every insert also has to update them. For columns you filter or cascade on, that is the right trade.

The fix in Drizzle, and the migration trap

The kit already declares an index this way on usage_events. For subscriptions it is a third argument to pgTable:

export const subscriptions = pgTable(
  "subscriptions",
  {
    // ...columns unchanged
  },
  (table) => [index("subscriptions_user_id_idx").on(table.userId)],
);

npm run db:generate then emits a plain build:

CREATE INDEX "subscriptions_user_id_idx" ON "subscriptions" USING btree ("user_id");

On a small table, ship it as is. On a large, busy one, read the Postgres docs first. A standard build "locks the table to be indexed against writes". Inserts, updates and deletes "will block until the index build is finished". During that time no subscription changes land, and on session nobody can sign in. The documented way out is CREATE INDEX CONCURRENTLY, which builds without blocking writes. The catch is that it "cannot" run "within a transaction block".

And drizzle-kit migrate runs every pending migration inside one transaction. I tried it: I edited the generated file to CONCURRENTLY and ran drizzle-kit migrate against a fresh database. It exited 1 with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Because all pending migrations share that transaction, the fresh database ended up with nothing applied: no tables, and no rows in drizzle.__drizzle_migrations.

So for a big table, run the concurrent build by hand against production, outside any transaction, before deploying anything that depends on it. On my 200,000-row table it took a quarter of a second. Then make the schema agree, so the next db:generate does not try to create the index again. The ordering rules are the ones in shipping a Drizzle schema change when the deploy does not run migrations. An additive index is the easy case: it can always go first.

Things that bite

• A failed concurrent build leaves debris. If the build hits a deadlock or a uniqueness violation, the docs say it leaves behind an "invalid" index that queries ignore but writes still maintain. Drop it and run the build again.
• One column or two. getUserPlan filters on user_id and sorts by updated_at. With one subscription row per user, the single-column index is enough. If users collect many rows over time, (user_id, updated_at) lets Postgres skip the sort. Put the equality column first and the sort column second.
• Better Auth's tables are yours to index. The schema comment asks you to regenerate them with the Better Auth CLI rather than hand-edit them, so keep added indexes in a separate migration you can see.
• Check with a real plan. EXPLAIN (ANALYZE, BUFFERS) on the exact query is the evidence. A plan that says Seq Scan with large "Rows Removed by Filter" numbers means a missing index.

Shipwright, the Next.js and Claude starter kit this blog documents, ships this schema on Drizzle and postgres.js. It does not include these indexes yet, and adding them is one short migration.