Nine indexes on one queue table, five of them partial, three of them on a jsonb path

Nakodo's background work is one Postgres table called jobs, drained by a cron every five minutes. The whole pipeline it runs is described in plain English on how it works: research a brand's site, look for creators, check them, write and send email, read the replies. Every step of that is rows in this table.

The schema has 25 tables and 55 indexes. Nine of them are on jobs, which is more than any other table has, and five of the six partial indexes in the whole database are here. That distribution is not an accident, and it is the most useful thing I have learned about queue tables in Postgres.

A queue table is mostly history

export const jobs = pgTable("jobs", {
  id: uuid().primaryKey().defaultRandom(),
  type: text({ enum: JOB_TYPES }).notNull(),
  campaignId: uuid().references(() => campaigns.id, { onDelete: "cascade" }),
  payload: jsonb().$type<JobPayload>().notNull(),
  dedupeKey: text(), // e.g. "enrich:UC123"; only one open job per key
  status: text({ enum: ["pending", "running", "done", "failed"] }).notNull().default("pending"),
  attempts: integer().notNull().default(0),
  runAfter: timestamp({ withTimezone: true }).notNull().defaultNow(),
  lockedAt: timestamp({ withTimezone: true }),
  lastError: text(),
  runId: uuid().references(() => runs.id, { onDelete: "set null" }),
  createdAt: createdAt(),
  finishedAt: timestamp({ withTimezone: true }),
});

At any moment a handful of these rows are pending or running and the rest are done or failed. Almost every question the app asks is about the handful: what can I claim now, does this key already have open work, what is this campaign still waiting for. Almost every row in the table is irrelevant to all of those questions.

An ordinary index does not know that. It indexes the finished rows too, gets bigger every day, and makes the planner walk entries that can never match. A partial index does know it:

index("jobs_open_campaign_idx")
  .on(t.campaignId)
  .where(sql`${t.status} in ('pending', 'running')`),

which drizzle-kit turns into exactly what you would have written by hand:

CREATE INDEX "jobs_open_campaign_idx" ON "jobs" USING btree ("campaign_id")
  WHERE "jobs"."status" in ('pending', 'running');

The index now holds tens of rows rather than hundreds of thousands, it stays that size for ever (finished jobs are deleted after 14 days by the nightly maintenance run), and it costs nothing to maintain on the 99% of writes that are status changes into done.

The catch, and it is a sharp one: the planner will only use a partial index if it can prove your query implies the index predicate. The predicate here is a literal status in ('pending', 'running'), so a query that asks for status <> 'done' and status <> 'failed' describes the same rows and gets a sequential scan. Partial indexes make the exact phrasing of a WHERE clause load-bearing, which means the predicate has to be written once in the application and reused. Ours is, in both the TypeScript and the SQL that reads it.

The dedupe index does something a constraint cannot

uniqueIndex("jobs_open_dedupe_uq")
  .on(t.dedupeKey)
  .where(sql`${t.status} in ('pending', 'running')`),

CREATE UNIQUE INDEX "jobs_open_dedupe_uq" ON "jobs" USING btree ("dedupe_key")
  WHERE "jobs"."status" in ('pending', 'running');

The rule is "only one open job per dedupe key". Not one job ever: the same creator is read again weeks later, the same campaign searches again tomorrow. A UNIQUE table constraint cannot express that, because constraints cannot be partial in Postgres. Only a unique index can, which is the reason to reach for uniqueIndex rather than unique even when you are not writing an expression.

Two things follow from it that are easy to get wrong.

dedupeKey is nullable, and in a unique index nulls do not conflict with each other, so any number of jobs with no dedupe key can be open at once. That is the behaviour we want (one-off work has no key) and it is worth saying out loud, because "unique" and "at most one" are not the same statement when nulls are allowed.

And the insert that relies on it is a bare conflict clause:

const rows = await db.insert(jobs).values(newJobs.map(...)).onConflictDoNothing().returning({ id: jobs.id });
return rows.length;

ON CONFLICT DO NOTHING with no conflict target handles a violation of any unique index, partial ones included, so enqueueing twenty jobs of which three already have open work inserts seventeen and returns seventeen ids. The moment you want DO UPDATE instead, you have to name an inference target, and for a partial index that means repeating the index predicate in the statement (ON CONFLICT (dedupe_key) WHERE status in (...) DO UPDATE), because Postgres has to pick the index out by its definition. That asymmetry between DO NOTHING and DO UPDATE is the single most surprising thing about partial unique indexes. The related trap is self-conflict inside one statement: two jobs with the same key in the same VALUES list are fine under DO NOTHING (the second one sees the first and is skipped), but the same batch under DO UPDATE raises "ON CONFLICT DO UPDATE command cannot affect row a second time". If you ever move from one to the other, dedupe the batch in application code first.

Three indexes on expressions, two of them into the jsonb

Some job types name their campaign in the column, and some only name it in the payload, because a job about a creator can serve several campaigns. Both have to be findable:

index("jobs_open_payload_campaign_idx")
  .on(sql`(${t.payload}->>'campaignId')`)
  .where(sql`${t.status} in ('pending', 'running')`),
index("jobs_open_payload_channel_idx")
  .on(sql`(${t.payload}->>'channelId')`)
  .where(sql`${t.status} in ('pending', 'running')`),

CREATE INDEX "jobs_open_payload_campaign_idx" ON "jobs" USING btree (("payload"->>'campaignId'))
  WHERE "jobs"."status" in ('pending', 'running');

The double parentheses in the generated SQL are not style. An index on an expression needs the expression in its own parentheses, and getting that wrong is a syntax error rather than a silent problem, which is the one merciful thing here. What is not merciful is the matching: the query has to use the same expression, character for character in spirit. payload->>'campaignId' matches, payload->'campaignId' (returning jsonb rather than text) does not, and neither does a cast bolted onto one side. Our reader writes the same thing the index does:

sql`${jobs.payload}->>'campaignId' = ${campaignId}`

An alternative was a GIN index on the whole payload, which would have answered every possible payload question. It would also have been far larger, and we only ever ask two. Two small btree indexes on two known paths beat one general index you cannot predict the cost of.

The third expression index is the one that pays for fairness between campaigns:

index("jobs_served_idx")
  .on(t.type, sql`coalesce(${t.finishedAt}, ${t.lockedAt})`, t.campaignId)
  .where(sql`${t.status} in ('done', 'running')`),

The question it answers is "when was each campaign last served work of this type", and the answer has two sources: a finished job has a finished_at, a job still running was last served when it was locked. Without the index that is a CASE or a coalesce in the query, evaluated per row over the whole history. With it, the coalesce is materialised in index order, and the predicate restricts it to done and running so failed and pending rows do not clutter it. Note that this index's predicate is a different pair of statuses from the other four, which is the clearest possible demonstration that the predicate belongs to the question, not to the table.

What Drizzle gives you, and what to watch

The whole vocabulary is index(), uniqueIndex(), .on() with either columns or sql fragments, .where(sql\...) and .using("gin", ...). The schema's two GIN indexes (one on a text array, one on a to_tsvector expression) use the same builder.

What to watch is how changes are migrated. In our history, one index was dropped and recreated three times across migrations, because an edit to an index's columns or predicate is not an alteration in Postgres; it is a DROP INDEX plus a CREATE INDEX. drizzle-kit generate writes exactly that, which is correct and also a lock on a table big enough to matter. Read the generated SQL, and if the table is large, replace the pair with CREATE INDEX CONCURRENTLY by hand and keep the migration honest about what it is doing.

The last thing worth saying is the cost. Nine indexes on a write-heavy table is not free: every insert and every status change maintains all nine. The reason it is affordable here is that the table never grows, because finished rows are deleted on a 14-day window. A queue table with no retention policy and nine indexes is a different and much worse story, and the retention is the decision that makes the indexing strategy work at all.