Fixing race condition duplicate rows with a unique index

Race condition duplicate rows are one of those bugs that hide for months. Two requests arrive at nearly the same moment, both decide a row does not exist yet, and both insert it. Application code cannot fully prevent that, because the gap between the check and the write is always there. The database can. This post walks through how I fixed it on Buffdudes, the fitness platform I run the backend for: a partial unique index that works with soft deletes, a single conflict-safe insert, a migration that cleaned up the existing copies, and two follow-up bugs the fix exposed.

How the duplicates got in

Buffdudes awards medals to users, and two code paths wrote them. Level medals were inserted with no check at all. Another path read first to see if the user already had the medal, then wrote it if not.

Both are open to races. The first has no guard. The second has a guard that only works when requests take turns. When two requests for the same user run at once (retries and double taps are the usual causes), both reads come back empty and both inserts succeed.

When I counted on a production snapshot, there were 177 user and medal pairs stored more than once, and 384 extra copies in total.

The symptom users saw: a popup that kept coming back

Duplicate rows rarely announce themselves as duplicates. Here the visible bug was a "new medal" popup that would not go away.

Marking a medal as seen updated one copy. The other copies were still unseen, so the next time the app asked for unseen medals, the same medal came back and the popup appeared again. From the user's side, the app was ignoring them. From the data's side, it was doing exactly what it was told, once per copy.

That is worth remembering when a bug report says "the app keeps showing me X". Check for duplicate rows before you check the client.

Why check-then-insert can't stop race condition duplicate rows

The usual first fix is a better check: wrap the read and the write in a transaction, or add a lock in the application. Neither closes the gap on its own. At the default isolation level in Postgres, two transactions can both read "no row" and both insert. An in-process lock does nothing once there are two server instances.

The only place that sees every write is the database, and the tool it gives you is a unique index. With one in place, the second insert fails or, with the right clause, quietly does nothing. The race still happens, but it can no longer leave two rows behind.

A partial unique index that respects soft deletes

Medals on Buffdudes are soft-deleted: a row gets a deleted_at timestamp instead of being removed. A plain unique index on (user_id, medal_id) would count deleted rows too, so a user could never earn a medal again after one was deleted.

A partial unique index solves that. It enforces uniqueness only over rows where deleted_at IS NULL, so there can be at most one live copy of each medal per user, and any number of deleted ones. The PostgreSQL partial index docs include a partial unique index among their examples.

The index cannot be created while duplicates exist, so the migration cleans up first. The rule: keep the earliest copy of each medal, mark it seen if any copy was seen, and soft-delete the rest. In SQL, with names simplified:

WITH ranked AS (
  SELECT id,
         row_number() OVER w AS rn,
         bool_or(seen) OVER w AS any_seen
  FROM user_medals
  WHERE deleted_at IS NULL
  WINDOW w AS (PARTITION BY user_id, medal_id ORDER BY created_at, id
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
UPDATE user_medals m
SET seen       = CASE WHEN r.rn = 1 THEN r.any_seen ELSE m.seen END,
    deleted_at = CASE WHEN r.rn > 1 THEN now() ELSE m.deleted_at END
FROM ranked r
WHERE m.id = r.id;

CREATE UNIQUE INDEX user_medals_one_active
  ON user_medals (user_id, medal_id)
  WHERE deleted_at IS NULL;

The full window frame on bool_or matters: without it, the earliest row would only see itself and miss a later copy that was seen. Soft-deleting the extras instead of removing them also means the cleanup can be undone if the rule turns out to be wrong for some case.

One helper, one conflict-safe insert

With the index in place, every medal write goes through one helper, and that helper inserts with ON CONFLICT DO NOTHING. For a partial unique index, the conflict target repeats the index predicate so Postgres can match it:

INSERT INTO user_medals (user_id, medal_id)
VALUES ($1, $2)
ON CONFLICT (user_id, medal_id) WHERE deleted_at IS NULL DO NOTHING
RETURNING id;

If the row is new, RETURNING gives back its id. If a live copy already exists, nothing is inserted and nothing is returned, and the caller knows the medal was already there. The syntax for the conflict target is in the INSERT reference. pganalyze has a good write-up on how INSERT ON CONFLICT compares to MERGE under concurrent inserts, and why ON CONFLICT is the one that holds up.

Routing every path through the helper matters as much as the index. The two original paths had drifted apart in how they wrote medals, which is part of how the bug started. One function means one behaviour.

The follow-up bugs: transactions and revives

Adding a unique index changes what can fail, and two problems surfaced after the main fix.

A unique violation inside a transaction aborts everything

Some medal inserts ran inside the workout's transaction, the one that saves a whole workout. In Postgres, any error inside a transaction aborts it, and every later statement fails until rollback. So a unique violation on a medal would have thrown away the user's workout save.

Those inserts now use the transaction's own manager together with the conflict-safe insert, so a duplicate medal is a no-op inside the transaction instead of an error. Reviving a soft-deleted medal (clearing its deleted_at) can still hit the unique index if a live copy appeared in the meantime, so revives run inside a savepoint. If the revive fails, only the savepoint rolls back and the workout save carries on.

Revive only rows that were deleted

The second bug was quieter. When a racing request had just inserted a fresh active copy, the revive path treated it as a medal coming back from deletion and logged the medal-earned event a second time. Revive now only touches rows that are really soft-deleted (WHERE deleted_at IS NOT NULL). An active row is left alone, and the event fires once.

Both bugs are typical of this kind of fix. Once the database starts refusing duplicates, every caller that assumed inserts always succeed needs a second look. I'd budget review time for that, the same way I would when auditing the indexes on a production snapshot.

Should you check for an existing row before inserting?

You can, for a nicer error message, but it will not prevent duplicates on its own, because two requests can pass the check at the same time. Put a unique index on the columns that define "the same thing", use ON CONFLICT DO NOTHING (or DO UPDATE) for the write, and treat the pre-check as optional. If the table uses soft deletes, make the unique index partial on deleted_at IS NULL.

The short version

• Count your duplicates on real data first; the size of the cleanup shapes the migration.
• Clean up with a rule you can explain (earliest copy wins, keep the seen state), then add a partial unique index.
• Send every write through one helper using ON CONFLICT DO NOTHING.
• Recheck any caller that runs inside a transaction, and use a savepoint where a conflict is still possible.

Declare the partial index on the model as well, so schema tooling never removes it; I explained why in TypeORM synchronize dropped my indexes. For more on the backend this came from, see how Buffdudes scaled to 60k users or the backend work I take on.