The NULL Trap in Postgres Idempotency Locks

Most modern languages tell you null == null is true. SQL quietly disagrees — and the gap is wide enough to drive duplicate welcome emails, double charges, and replayed webhooks straight through a UNIQUE constraint you thought was airtight.

It trips human developers who carry the equality intuition over from Python, JS, or Go. It trips coding agents even harder: ask one to "enforce idempotency on these columns" and it will happily generate a nullable unique key and never flag it — because nothing in its training says a nullable unique column is the absence of a guarantee, not a slightly loose one.

If you are using PostgreSQL to enforce at-most-once write semantics—preventing duplicate welcome emails, double-billing, or webhook replays—you are likely relying on a UNIQUE constraint combined with ON CONFLICT DO NOTHING.

There is a silent trap in this pattern.

The Trap: NULL is not a value

In Postgres, NULL represents an unknown. By standard SQL behavior, NULL = NULL evaluates to NULL (not true), and two NULLs are never treated as equal — including inside a unique index.

So if your composite unique constraint includes an optional column (say partner_id or period), and an incoming event leaves that column NULL, the database will not raise a conflict.

A row with (user_id='u1', kind='welcome', period=NULL) does not conflict with another identical row. Your database-level mutex drops open, duplicates flood through, and you find out when the "I got the welcome email twice" tickets start rolling in.

(Note: Postgres 15 added UNIQUE NULLS NOT DISTINCT, which fixes this at the engine level. But relying on it creates silent behavior divergence the moment someone runs an older Postgres locally, and it isn't portable — MySQL and SQLite share the SQL-standard default. Partial unique indexes (WHERE period IS NULL) also work, but they multiply with every nullable column and bury the constraint's intent. The sentinel pattern stays put and reads the same everywhere.)

The Fix: A Sentinel Instead of NULL

Eliminate NULL from your unique constraints. Make every key column NOT NULL with a sentinel default, and have the application pass that sentinel for the "not applicable" state.

-- ❌ The Trap (UNIQUE allows NULL and treats NULLs as distinct)
CREATE TABLE events_buggy (
    id bigserial PRIMARY KEY,
    user_id text NOT NULL,
    kind text NOT NULL,
    period text,
    UNIQUE (user_id, kind, period)
);
-- Both inserts succeed. Your idempotency lock is broken.
INSERT INTO events_buggy (user_id, kind, period) VALUES ('u1', 'welcome', NULL);
INSERT INTO events_buggy (user_id, kind, period) VALUES ('u1', 'welcome', NULL);

-- ✅ The Fix: NOT NULL + sentinel
CREATE TABLE events_safe (
    id bigserial PRIMARY KEY,
    user_id text NOT NULL,
    kind text NOT NULL,
    period text NOT NULL DEFAULT '',
    UNIQUE (user_id, kind, period)
);
-- First wins; the concurrent retry conflicts and is safely ignored.
INSERT INTO events_safe (user_id, kind, period) VALUES ('u1', 'welcome', '')
    ON CONFLICT (user_id, kind, period) DO NOTHING;
INSERT INTO events_safe (user_id, kind, period) VALUES ('u1', 'welcome', '')
    ON CONFLICT (user_id, kind, period) DO NOTHING;

Pick a sentinel that can never be a real value. '' is perfect for a text key. The trap to avoid is carrying the habit to other types: partner_id integer NOT NULL DEFAULT 0 looks fine until 0 is a legitimate partner ID, and a nil-UUID has the same problem. If the column can't hold an out-of-domain value, keep it text, or reach for UNIQUE NULLS NOT DISTINCT / an expression index instead. The goal is one canonical "not applicable" key — not a new collision.

What the constraint buys you — and what it doesn't

State this plainly, because it's the assumption that bites juniors and agents alike: this constraint guarantees one row, not one side effect.

If you commit the row and then crash before the email actually sends, the retry finds the row already there and skips the send — the user gets nothing. Send before you commit, and a rollback leaves you free to send again — the user gets two. At-most-once delivery requires tying the side effect to the row's transaction: an outbox, or a status column you flip and read back inside the same unit of work.

The unique constraint is the foundation, not the whole building. It collapses concurrent retries into a single row and gives you a database-level guarantee without application-layer locks. It does not, by itself, make your email send exactly once. Build the delivery semantics on top of it.

That foundation is a non-negotiable standard across our Go backends. A tiny schema change that closes a whole class of duplicate-write bugs in multi-tenant and event-driven systems.