Postgres composite index column order: why the usage index is (user_id, created_at), measured on 2 million rows

Before every chat request reaches Claude, the kit runs the same aggregate: count this user's requests since the first of the month (UTC), and sum their tokens and cost. That one query is both the quota check and the usage dashboard. The table it reads, usage_events, gets one row per model request, which makes it the fastest-growing table in the app. Its only index is (user_id, created_at). I seeded 2 million rows and timed the query with that index, with the columns reversed, with user_id alone, with no index, and with a covering index. Postgres composite index column order turned out to be worth a 10x difference. The fastest variant is one Drizzle cannot declare, and db:push deletes it.

The query and its index

From src/lib/metering/meter.ts:

const monthStart = new Date();
monthStart.setUTCDate(1);
monthStart.setUTCHours(0, 0, 0, 0);

const [row] = await db()
  .select({
    requests: sql<number>`count(*)`.mapWith(Number),
    inputTokens: sql<number>`coalesce(sum(${usageEvents.inputTokens}), 0)`.mapWith(Number),
    // ...outputTokens, costMicroUsd the same way
  })
  .from(usageEvents)
  .where(and(eq(usageEvents.userId, userId), gte(usageEvents.createdAt, monthStart)));

And the index, declared in the table callback in schema.ts:

(table) => [
  index("usage_events_user_created_idx").on(table.userId, table.createdAt),
],

The query has one equality condition (user_id = ?) and one range condition (created_at >= month start). That combination is what decides the order.

Measured on 2 million rows

Setup: Postgres 16, the kit's migrations, then 2 million events for 1,000 users, spread at random over May to October 2026. Each user's rows end up scattered across the table, the way they do when many users' requests interleave. Then VACUUM ANALYZE. The query asked for one user's October, which matched 355 rows. Timings are five EXPLAIN ANALYZE runs each, with a warm cache:

• (user_id, created_at), as shipped: bitmap index scan, 357 buffers, 1.0–1.4 ms.
• (created_at, user_id), reversed: bitmap index scan, 1,647 buffers, 10.2–16.2 ms.
• (user_id) alone: bitmap index scan, 1,947 buffers, 3.6–5.1 ms.
• No index: parallel sequential scan, about 26,700 buffers, most read from disk, 71–99 ms.
• Covering index, (user_id, created_at) INCLUDE (input_tokens, output_tokens, cost_micro_usd): index-only scan, 6 buffers, 0.12–0.20 ms.

A B-tree sorts by its first column, then by the second within each value of the first. With (user_id, created_at), all of one user's entries sit together in time order, so Postgres descends once to that user's October and reads 355 entries. It then visits the table for each one, and because the rows are scattered that is roughly a page per row, which is where the 357 buffers come from. Reverse the columns and October is contiguous for everyone: about a sixth of the index. Postgres walks that whole range checking user_id in every entry. The user_id-only index finds all six months of this user's roughly 2,000 rows and then fetches each from the table just to discard the ones before October. One user's half year is still fewer entries than everyone's month, which is why it beat the reversed composite here.

The rule that falls out: equality columns first, the range column last.

The covering index, and the push that drops it

INCLUDE stores extra columns in the index leaves without making them part of the sort order. Because the three summed columns are in the index, Postgres can answer the whole aggregate from it and skip the table, which is the 6-buffer result above. It costs space: 95 MB against 75 MB for the plain composite on this data, about 27% more. Index-only scans also lean on the visibility map that VACUUM maintains, which is why the load was followed by a vacuum.

Drizzle 0.45.2's index builder offers using, concurrently, with and where, and no include. So a covering index has to live in a hand-written migration (drizzle-kit generate --custom) that the schema knows nothing about. That is where it gets fragile. I ran drizzle-kit push, the command behind the kit's npm run db:push, against the database holding the covering index. It printed:

 Warning  You are about to execute current statements:
DROP INDEX "ev_cover";
CREATE INDEX "usage_events_user_created_idx" ON "usage_events" USING btree ("user_id","created_at");
[✓] Changes applied

It exited 0 without asking. push makes the database match the schema, and to the schema that index is noise. If push is how you sync a database, a hand-made index survives until the next push. Either keep that database on migration files only (see shipping a Drizzle schema change when the deploy does not run migrations), or settle for the plain composite. At about a millisecond for a month of one user's rows, the plain composite is a fine place to stop.

Things that bite

• The cost grows through the month. Early in the month the query matches a handful of rows and late in the month a few hundred. With the right index, the work tracks this user's month rather than the table's size.
• New filters go before the range. If the dashboard ever splits usage by route, the index becomes (user_id, route, created_at), with every equality column first and the range last.
• Anonymous traffic shares the table. The demo's ip:<addr> identities are just more user_id values. The same index serves them, and they count against the same quota query.
• Test plans on realistic data. On a few thousand rows the planner may prefer a sequential scan whatever you build, and that is correct. Seed something near your real size before you judge an index.

Shipwright, the Next.js and Claude starter kit this blog documents, ships usage_events with this index in this order. The metering schema behind it is in tracking LLM token costs per user, and the foreign keys that lack an index are covered in Postgres does not index foreign keys for you.