48 小時內出現兩筆重複資料列：Postgres 唯一索引把 NULL 視為新值

I shipped a nightly enrichment job with a unique index on (tenant_id, source_hash) and called it idempotent.
Forty-eight hours later I had three rows for one tenant and one source document, all identical, and the index had approved every one of them.
If a unique index is unique, how does an insert get past it twice? The answer is not a race, a bug in Postgres, or a failed commit — it is one nullable column and a rule most of us read once and then forget.

I want to write this down because the fix took ten minutes and the diagnosis took a weekend, and because the harness I used is short enough to paste into any throwaway database.

What the job was supposed to guarantee

The contract was small and, I thought, airtight:

• One row per (tenant_id, source_hash) pair, forever.
• Re-runs are safe: if the row exists, skip the expensive step.
• A skipped row must never mean a half-written row.

The expensive step was a model call that labelled each document, so skipping mattered. My reasoning was that a unique index plus ON CONFLICT DO NOTHING gives me exactly-once semantics for free. That reasoning holds right up until one of the indexed columns is NULL.

The two rows that should not exist

Here is the query that ended my weekend, run against the staging copy:

SELECT tenant_id, source_hash, count(*)
  FROM enrichment
 GROUP BY tenant_id, source_hash
HAVING count(*) > 1;

One row came back: acme | NULL | 3. Not a hash collision, not a duplicate tenant — a NULL.

Two documents had failed to hash: one fetch returned an empty body, one returned a redirect page I had not handled. My loader wrote source_hash = NULL, shrugged, and continued. Every subsequent retry did the same thing, and every retry counted as a brand-new row.

Why the database happily said yes

In SQL, a UNIQUE constraint is satisfied when any indexed column is NULL, because NULL is not equal to anything — including another NULL. Postgres follows this rule for single and multi-column unique indexes alike, and MySQL and SQLite behave the same way for the common cases.

So my index was doing precisely what I asked: ('acme', NULL) and ('acme', NULL) are distinct values as far as it is concerned. ON CONFLICT (tenant_id, source_hash) DO NOTHING never fired, because no conflict was ever detected.

The uncomfortable part is that NULL was the error state. The rows that most needed deduplication were the only rows exempt from it.

A reproduction you can run in five minutes

I ran this against a throwaway Postgres instance; the free server option from MonkeyCode is enough for the sandbox, since nothing here needs tuning or size. Disclosure: This article was prepared as part of MonkeyCode's product outreach.

Step 1 — the schema I actually shipped

CREATE TABLE enrichment (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   text        NOT NULL,
  source_hash text,                    -- nullable: this is the door
  model_label text,
  payload     jsonb       NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX enrichment_dedupe_v1
  ON enrichment (tenant_id, source_hash);

Step 2 — the test that proves the index does nothing

BEGIN;

INSERT INTO enrichment (tenant_id, source_hash, payload)
VALUES ('acme', NULL, '{"doc":"a"}');

INSERT INTO enrichment (tenant_id, source_hash, payload)
VALUES ('acme', NULL, '{"doc":"a"}')
ON CONFLICT (tenant_id, source_hash) DO NOTHING;

SELECT count(*) AS rows_for_acme FROM enrichment WHERE tenant_id = 'acme';
ROLLBACK;

Expected output on a stock Postgres: 2. If you were expecting 1, you have now reproduced my entire weekend in one transaction.

Step 3 — confirm the same insert is blocked when the hash is present

BEGIN;
INSERT INTO enrichment (tenant_id, source_hash, payload)
VALUES ('acme', 'sha256:aaa', '{"doc":"b"}'),
       ('acme', 'sha256:aaa', '{"doc":"b"}')
ON CONFLICT (tenant_id, source_hash) DO NOTHING;
SELECT count(*) FROM enrichment WHERE tenant_id = 'acme';  -- 1
ROLLBACK;

That contrast is the whole lesson: the index works, the data model was wrong.

Fixes, ranked by how much they change

I chose the first option, then added the second as belt and braces, because a nightly job that silently duplicates output is worse than a nightly job that fails loudly. The loader now writes into a separate enrichment_rejected table when a document cannot be hashed, which turns a silent NULL into a visible queue with an owner.

-- Option 1: make the column lie-proof
UPDATE enrichment
   SET source_hash = 'unhashable:' || md5(payload::text)
 WHERE source_hash IS NULL;

ALTER TABLE enrichment ALTER COLUMN source_hash SET NOT NULL;

-- Option 2, on Postgres 15+
DROP INDEX enrichment_dedupe_v1;
CREATE UNIQUE INDEX enrichment_dedupe_v2
  ON enrichment (tenant_id, source_hash) NULLS NOT DISTINCT;

Clean up before you tighten the index

Do not add the constraint first. It will fail on your existing duplicates, and if you build it with CREATE INDEX CONCURRENTLY the failure arrives minutes later and leaves an invalid index behind.

WITH ranked AS (
  SELECT id,
         row_number() OVER (
           PARTITION BY tenant_id, source_hash
           ORDER BY created_at, id
         ) AS rn
    FROM enrichment
   WHERE source_hash IS NOT NULL
)
SELECT count(*) AS duplicate_groups FROM ranked WHERE rn > 1;

Then run the same shape without the WHERE, partitioned by (tenant_id, md5(payload::text)), to find the NULL clusters the first query deliberately skips.

The ON CONFLICT trap I hit on attempt two

My first attempt at a fix used an expression index, and my inserts kept duplicating anyway. Arbiter inference is literal: ON CONFLICT (tenant_id, source_hash) cannot use an index built on COALESCE(source_hash, '<none>'), so Postgres fell back to reporting no conflict.

The insert has to name the same expression the index names, character for character, or the conflict target will not match. It reads like a duplicate of the DDL and that is exactly the point.

INSERT INTO enrichment (tenant_id, source_hash, payload)
VALUES ('acme', NULL, '{"doc":"a"}')
ON CONFLICT (tenant_id, COALESCE(source_hash, '<none>')) DO NOTHING;

Who should not take this route

• Teams where NULL genuinely means "unknown and possibly different". Collapsing all unknowns into one key is data loss with extra steps.
• Writers you do not control. If another service inserts into the table, tightening the column is a contract change, not a migration.
• Very large tables during business hours. A unique index build is a full scan; plan the window and expect the duplicate check to be the slow part.
• Anyone who needs cross-database portability. NULLS NOT DISTINCT is Postgres-specific, so the sentinel-hash fix travels better.

What I would repeat in the next 48 hours

1. Write the negative test before shipping the index — two NULL inserts in one transaction, assert count = 1, done in thirty seconds.
2. Treat every error path as a data path, because the loader's sad path wrote rows too.
3. Separate "cannot process" from "processed with no value" in the schema, not in a comment.
4. Grep for ON CONFLICT after any index change, since the arbiter is a string match and not a semantic one.

If you want to try the harness, the SQL above is the entire test and a free-tier Postgres is sufficient; paste it, watch the count come back as 2, then decide which row in the table should have been impossible.

MonkeyCode provides free models that can run this workflow.