The NULL Trap in Postgres Idempotency Locks

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

But there is a silent trap in this pattern.

The Trap: NULL != NULL

In Postgres, NULL represents an unknown value. By standard SQL behavior, two NULL values are never considered equal.

If your composite unique constraint includes optional columns (e.g., partner_id or period_key), and an incoming event leaves that column NULL, the database will not trigger 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 only find out when the "users got the welcome email twice" support tickets start rolling in.

(Note: Postgres 15 introduced UNIQUE NULLS NOT DISTINCT to solve this at the engine level. However, relying on it creates silent behavior divergence if a developer is running Postgres 14 locally, and the engine-level fix isn't portable since MySQL and SQLite share the same default SQL-standard behavior. You could also use partial unique indexes (WHERE period IS NULL), but those multiply combinatorially with every nullable column and obscure the constraint's intent. The sentinel pattern remains the most robust architectural choice.)

The Fix: The Empty-String Sentinel

To make your idempotency bulletproof across all environments, eliminate NULL from your unique constraints entirely.

Make every key column NOT NULL DEFAULT '' (or a domain-appropriate sentinel), and have your application layer pass the empty string as 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: The Empty-String 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 succeeds, concurrent retries are safely caught and 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;

This ensures concurrent retries are collapsed into a single row, maintaining database-level guarantees without requiring complex application-layer locks.

This is a non-negotiable standard across our Go backends. It is a tiny schema change that prevents massive headaches in multi-tenant or event-driven systems.