Postgres says NULL is not equal to NULL, which is a bug in one of our unique indexes and a feature in another

Our schema has six unique indexes and not one of them is there for data hygiene. Each one is a product rule that would otherwise have to be a read followed by a write, which is to say a race.

Two of them are the interesting ones, and they are interesting for the same reason pointed in opposite directions: Postgres treats NULL as not equal to any other NULL, including another NULL. In one index that property is the entire mechanism. In another, it would have made the index do nothing at all, and the failure would have been invisible.

The rule that cannot live in application code

Start with the easy one, because it sets up why these are indexes and not if statements.

// Unique constraint enforced at DB level. Prevents race-condition duplicate submissions
// even when two requests from the same participant arrive at the same millisecond
uniqueIndex('uq_answers_participant_question')
    .on(t.session_id, t.question_id, t.participant_id),

One answer per person per question. The obvious implementation is to check first: look for an existing answer, and if there is none, insert. That is two statements with a gap between them, and the gap is where the bug lives.

This gap gets hit for real, not theoretically. A phone on pub WiFi with one bar, a player who taps and sees nothing happen and taps again, a retry inside the client when a request looks like it timed out but actually landed. Two requests, same participant, same question, arriving close enough together that both reads see no existing answer and both inserts proceed.

The index closes the gap because it is the only check in the system that is atomic with the write. The second insert fails. Note the migration this arrived in is called 0006_concurrent_load_hardening.sql, which is the most useful thing about it: a hand-named migration tells the next reader what the three statements inside it have in common, which an auto-generated 0006_tough_ink does not.

The two indexes that are the game mode

Our leaderboard supports two modes. Team mode gives a table one shared score and ranks tables, which is how a pub quiz has worked forever. Individual mode ranks every player separately, which suits a standing room or a remote office event.

That means the uniqueness rule for a leaderboard row is different depending on the mode:

• Team mode: one row per table, per session.
• Individual mode: one row per player, per session.

The table has both table_id and a nullable participant_id, with participant_id null in team mode. So the tempting single index is:

UNIQUE (session_id, table_id, participant_id)

This looks like it covers both cases and it is useless for one of them.

In team mode every row has participant_id IS NULL. Under a standard unique index, Postgres considers two rows with (session, table, NULL) and (session, table, NULL) to be distinct, because the NULLs are not equal to each other. The constraint is satisfied. You can insert that row as many times as you like.

So you ship it, and in individual mode everything behaves, and in team mode, which is the mode nearly every venue uses, the constraint is decoration. You find out when a table appears twice on the leaderboard with different scores, in a pub, with the screen on the wall.

The fix is two partial unique indexes, one per mode, each with a WHERE that makes the other mode's rows invisible to it:

// Team mode: one row per table per session (participant_id is NULL)
uniqueIndex('uq_leaderboard_team')
    .on(t.session_id, t.table_id)
    .where(sql`${t.participant_id} IS NULL`),
// Individual mode: one row per participant per session
uniqueIndex('uq_leaderboard_individual')
    .on(t.session_id, t.participant_id)
    .where(sql`${t.participant_id} IS NOT NULL`),

Now team mode's rule does not mention participant_id at all, so there is no NULL to be distinct from anything. The predicate does the work the column was failing to do.

Two things to note about this shape.

First, the two predicates are exact complements, IS NULL and IS NOT NULL, so every row is covered by exactly one index. Not zero, which would be a hole, and not two, which would mean a row had to satisfy both rules. Writing them as complements is what makes "two indexes" equivalent to "one rule that switches on mode", and it is worth checking deliberately, because a pair of partial indexes whose predicates overlap or leave a gap is a far more confusing bug than no index at all.

Second, Postgres 15 added NULLS NOT DISTINCT on unique constraints, which would fix the narrow NULL problem. It would still be the wrong tool here. The requirement is not "treat NULLs as equal", it is "the uniqueness key is a different set of columns depending on the mode". Only the predicate expresses that.

There is a practical payoff too, which is what made me notice the design rather than just inherit it. Drizzle's onConflictDoUpdate needs a conflict target. With one index you would be guessing; with two named partial indexes, the code that writes a leaderboard row names the index for the mode it is in, and the upsert is a single statement either way.

The same NULL semantics, now load bearing

Here is the one that depends on exactly the behaviour that broke the last one.

// Accept-invite flow: look up pending invite by token
uniqueIndex('uq_venue_members_invite_token').on(t.invite_token),

invite_token is nullable, and the column comment explains why:

invite_token: text('invite_token'), // null after accepted; unique so we can look up by token

On our top plan a venue can have several hosts, invited by email. An invite row is created with a token, the token goes in a link, and when the invite is accepted the token is cleared so the link cannot be used again.

Which means a venue that has been running for a year has a pile of rows with invite_token = NULL. Under a unique index. And they coexist happily, because NULL is not equal to NULL, so no two of them ever collide.

That is the same rule that made the leaderboard index a no-op, and here it is precisely what we want: unique among the rows that have a token, unconstrained among the rows that do not. A sparse unique index, for free, with no predicate needed.

I like this pair because it kills the instinct to learn a rule of thumb. "Do not put nullable columns in unique indexes" would have cost us this one. The real rule is narrower and you have to think every time: a NULL in a unique index means this row opts out of the constraint. Decide whether that is what you meant.

One where the index is slightly wrong

Being honest about the set, because they are not all clean.

// Prevent the same email from having two active memberships in one venue
uniqueIndex('uq_venue_members_venue_email').on(t.venue_id, t.invited_email),

The comment says "two active memberships". The index does not say "active". Membership rows carry a status of pending, active or revoked, and the file is explicit that revoking sets status='revoked' rather than deleting, so the audit trail survives.

So: invite someone, revoke them, then invite them again. The row still exists with their email, the unique index refuses the new insert, and the re-invite has to be written as an update to the revoked row rather than a fresh insert. That is a defensible design, and it is not what the comment describes. If the intent really were "one active membership per email", this would want a predicate like the leaderboard pair has:

WHERE status <> 'revoked'

It is on the list. I am including it because a post about how good constraints are should probably admit that the gap between what a constraint enforces and what its comment claims is the usual place these things go wrong, and it is far harder to spot than a missing index, since everything works until somebody is re-invited.

And one that is a unique index on a hash

// SHA-256 hash of the Supabase session JWT `jti` claim (unique per token).
// We store the hash so a leaked row can't be used to forge a session.
session_hash: text('session_hash').notNull(),
// ...
uniqueIndex('uq_user_sessions_hash').on(t.session_hash),

This backs a cap on concurrent logins, so one subscription is not shared across a dozen devices. Two details worth copying: the uniqueness is on the hash rather than the token, so the unique index can also be the upsert target on the hot path; and the row never contains the token itself, so a dump of this table buys an attacker nothing.

The through line

Every one of these replaces a check in application code with a check the database performs atomically, which is the only kind that survives two phones tapping at once. Picking the key, though, is a modelling problem, and the one property you have to hold in your head is that a NULL in a unique index is a row saying "not me". Sometimes that is a hole in your rule. Sometimes it is the rule.