"duplicate key value violates unique constraint" on your upsert: the race ON CONFLICT does not save you from

verbatim errorERROR: duplicate key value violates unique constraint "users_email_key" DETAIL: Key (email)=(team@acme.io) already exists. CONTEXT: SQL statement "INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO NOTHING"

Problem

An "idempotent" signup path that inserts with ON CONFLICT DO NOTHING still threw, under load:

ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(team@acme.io) already exists.
CONTEXT: SQL statement "INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO NOTHING"

The conflicting-arbiters guarantee was supposed to make this impossible. Two concurrent requests with the same email were all it took.

Root cause

ON CONFLICT handles conflicts against committed rows. Two transactions insert the same email concurrently:

T1 inserts, not yet committed.
T2 inserts — the unique index blocks T2 internally (this is the subtle part) waiting for T1 to commit or roll back.
T1 commits. T2's wait resolves as a conflict, and ON CONFLICT fires... usually.

But if the insert carries ON CONFLICT DO UPDATE with a WHERE that filters the row out, or the conflict involves deferred unique constraints, or T1 aborted after T2's speculative insertion began, the conflict resolution can be rejected with the plain duplicate-key error. Deferred constraints specifically bypass ON CONFLICT entirely — it cannot fire on a constraint checked at commit time.

fix preview — first 3 of 6 lines (sql), truncated:

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email text NOT NULL,
… 3 more lines in the fix

🔒 the fix — including 3 code blocks — is members-only. $1/mo unlocks everything.