Check-then-insert: the race you've already shipped
Somewhere in most codebases there is an "is this email taken?" query followed by an INSERT. It looks correct — and it's a race. Between your check and your insert, another transaction can do both. No isolation level shy of SERIALIZABLE saves you, because each transaction's check honestly saw a world without the row.
Watch the duplicate land
The same person double-clicks 'Sign up'. Two app servers each run: check, then insert.
A> BEGIN;
BEGIN
A> SELECT count(*)::int AS existing FROM signups WHERE email = 'bob@example.com';
existing
----------
0
(1 row)
B> BEGIN;
BEGIN
B> SELECT count(*)::int AS existing FROM signups WHERE email = 'bob@example.com'; -- B's check also passes — A hasn't committed anything
existing
----------
0
(1 row)Both checks passed, so both insert. Nothing blocks, nothing errors.
A> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1
A> COMMIT;
COMMIT
B> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1
B> COMMIT;
COMMIT
A> SELECT count(*)::int AS bobs FROM signups WHERE email = 'bob@example.com'; -- the "impossible" duplicate
bobs
------
2
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
No error, no blocking — both transactions did exactly what they were told. The application's uniqueness "rule" lived only in application code, and concurrent code paths don't read each other's minds. (This is write skew's little sibling: two decisions, each valid in its own snapshot, jointly wrong.)
A check in code is not a constraint
This race ships silently — no error, no lock wait, nothing in the logs — and surfaces as "impossible" duplicate rows weeks later. If uniqueness matters, declare it UNIQUE.
The fix is a constraint, not cleverer code
Uniqueness is the database's job. With UNIQUE, the same race becomes a wait-then-error — and ON CONFLICT turns even that error into a plan:
Same double-click, but now the table has UNIQUE (email).
A> BEGIN;
BEGIN
A> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1B's insert can't decide yet — the winner hasn't committed. It waits on A's transaction.
B> INSERT INTO signups (email) VALUES ('bob@example.com');
⏳ B is waiting for a lock…
A> COMMIT;
COMMIT
⏵ B's blocked statement fails:
ERROR: 23505: duplicate key value violates unique constraint "signups_email_key"unique_violation — the race is now loud instead of silent.
ON CONFLICT DO NOTHING absorbs the duplicate instead of erroring:
B> INSERT INTO signups (email) VALUES ('bob@example.com')
ON CONFLICT (email) DO NOTHING; -- INSERT 0 0
INSERT 0 0And DO UPDATE makes it an upsert — here just to read back the existing row's id:
B> INSERT INTO signups (email) VALUES ('bob@example.com')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
RETURNING id, email;
id | email
----+-----------------
1 | bob@example.com
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The middle beat deserves a pause: B's insert had to wait. A unique index can't accept or reject the duplicate until the first insert's fate is known, so B queues on A's transaction — the same transactionid wait you saw in lock queues — and fails only once A commits. Then the manual's escape hatch: "ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error" — DO NOTHING absorbs the duplicate, and DO UPDATE (upsert) comes with a strong guarantee: "ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency."
The lesson under all three beats: a SELECT can't enforce uniqueness, because by the time you act on its answer it's already stale. Invariants belong in the schema, where UNIQUE turns the silent duplicate into a loud 23505 and ON CONFLICT turns that 23505 into control flow — atomically, no retry loop required. Keep the check-first query if you like the friendlier error message, but treat it as UX, not integrity. That same constraint, paired with RETURNING, is the backbone of the next lesson: idempotency keys.