IdempotencyGENERALDATABASE-SPECIFICSIMPLIFIED

Duplicate Detection

Recognising that this work has already been done — atomically, at the right scope, within a bounded window.

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

How do I tell, safely and concurrently, whether I have already processed this?

The requirement

Whatever the source — a client retry, a webhook redelivery, a queue redelivery, a job rerun — each unit of work must take effect once.

The obvious build

Keep a table of processed ids. Before doing the work, check whether the id is there. If not, do the work and add it. Look it up, then insert.

Why it breaks

Two concurrent copies both check, both find nothing, both proceed. The window between the read and the write is where every duplicate this design was meant to prevent gets in (Backend Races).

How it breaks in production

Two concurrent copies both check, both find nothing, both proceed. The window between the read and the write is where every duplicate this design was meant to prevent gets in (Backend Races).
Recording after the work means a crash in between leaves the work done and unrecorded, so the next attempt does it again.
Recording before the work means a crash leaves the id marked done and the work never performed — a silent loss with no error anywhere.
The set of ids grows without bound and becomes the largest table in the system, with an index on the hot path of every write.
The id chosen is not stable across attempts — a message id that changes on producer retry, a hash that includes a timestamp — so real duplicates are invisible to the check.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

Duplicate detection has exactly three requirements: a stable identity for the unit of work, an atomic claim on that identity, and a bounded window over which claims are remembered. Miss any one and it does not work.
Stable identity means the same value on every attempt at the same intent and a different value for different intents. That is the same property as an idempotency key, arrived at from the consumer side (Idempotency Keys).
The atomic claim is what removes the race. INSERT ... ON CONFLICT DO NOTHING and SET key val NX are the two common forms; both decide a single winner with no observable moment between testing and acting (Atomic Operations).
A unique constraint is not a safety net around application logic — it *is* the concurrency control. The application reads its verdict from the affected-row count.
The window is finite by necessity: you cannot remember every id forever. Its length must exceed the maximum interval over which a duplicate can arrive — the provider's retry ceiling, the broker's redelivery horizon, your documented idempotency promise.
Detection and idempotency are related but not identical. Deduplication *implements* idempotency by suppressing repeats; an operation can also be idempotent by construction, with nothing to detect (Idempotency in Backends).

Three requirements, and what breaks when each is missing

Duplicate detection fails in three distinct ways, and knowing which one you have is the difference between a fix and a rewrite. Identity failures mean the mechanism never sees the duplicate. Atomicity failures mean it sees it too late. Window failures mean it has forgotten.

They also have different symptoms. An identity failure produces duplicates at a steady rate under all conditions. An atomicity failure produces duplicates only under concurrency, so it correlates with load and is invisible in staging. A window failure produces duplicates only from late-arriving attempts, so it correlates with upstream incidents.

That symptom-to-cause mapping is worth internalising, because "we have duplicates" is where the investigation starts and these three are where it ends.

Requirement	What it means	Symptom when missing	When it shows up
Stable identity	The same value on every attempt at one intent	Duplicates at a constant rate; dedupe counter always zero	Always — including in tests, if anyone looks
Different identity per intent	Distinct values for genuinely distinct requests	Legitimate requests silently swallowed	Rarely, and reported as "my second payment vanished"
Atomic claim	Test and act in one operation	Duplicates under concurrency only	Under load; never in staging
Shared transaction with the effect	Claim and effect commit together	Claim without effect, or effect without claim	After a crash or a deploy
Correct scope	Per principal, per consumer	Cross-tenant suppression or disclosure	On the day a second tenant or consumer appears
Bounded window	Claims expire	Store growth; insert latency degradation	Months later, as a slow-write incident
Window > redelivery horizon	Expiry outlasts any possible duplicate	Duplicates from late retries only	After an upstream outage, when the backlog lands

The claim is the whole mechanism

Everything else here is bookkeeping around one operation: a write that succeeds for exactly one of N concurrent attempts. The database's unique index already provides that, and it provides it across connections, across processes and across instances — which is the part an application-level lock cannot do (A Mutex on Server A Does Nothing About Server B).

The pattern generalises past deduplication. Any time you need exactly one of several concurrent requests to proceed — claiming a job, allocating a seat, electing a leader for a task — the same shape applies: attempt a constrained write and read the outcome, rather than checking a condition and then acting on it (Atomic Operations).

The version below also shows the part that is easy to omit: the claim and the effect in one transaction. Without that, the two can diverge, and a divergence between "we recorded that we did it" and "we did it" is the hardest class of bug in this module to detect after the fact.

Detecting a duplicate before doing the work

Check, then act

if await db.exists('SELECT 1 FROM processed WHERE key = $1', [key]) {
  return                                  // already done
}

await doTheWork(payload)                  // <- two attempts can both be here
await db.none('INSERT INTO processed (key) VALUES ($1)', [key])

Claim, then act, in one transaction

await db.tx(async (t) => {
  const claimed = await t.result(
    `INSERT INTO processed (consumer, key, at)
          VALUES ($1, $2, now())
     ON CONFLICT (consumer, key) DO NOTHING`,
    [consumer, key])

  if (claimed.rowCount === 0) {
    metrics.increment('duplicate_suppressed', { consumer, source })
    return                                // another attempt owns this key
  }

  await doTheWork(t, payload)             // same transaction as the claim
})
// A rollback undoes both. A crash leaves neither. A concurrent attempt
// loses the claim and does nothing.

On the left there is a window between the existence check and the insert during which a second attempt passes the same check, and a crash between the work and the insert leaves the work unrecorded. On the right the unique index admits one claimant with no window, and because the claim and the effect share a transaction there is no state in which one exists without the other.

Choosing where the identity comes from

GENERALThe ordering of preference holds broadly; what varies is which identifiers your upstreams actually provide. Check rather than assume — some providers document an id that is stable per delivery rather than per event, which looks identical in the payload and is useless for deduplication.

The most common reason a correct-looking implementation detects nothing is that the identity was taken from the transport rather than from the intent. Transport identifiers are generated per attempt by design — that is what makes them useful for tracing — so they differ between the original and the retry.

The rule is to take identity from the layer that knows the intent. The client knows that two HTTP attempts are one payment. The producer knows that two enqueues are one order. The provider knows that two deliveries are one event. Each of those is upstream of the transport that duplicates.

Where no such identifier exists, the honest options are to introduce one — require a key, add a business unique constraint — or to accept that duplicates cannot be detected and design the effect to be naturally idempotent instead.

What identity should the claim be made on?

Which identifier is stable across every attempt at this one intent?

Client-supplied idempotency key

when A public API where two identical requests can both be legitimate.

cost Client cooperation, a key store, expiry and scoping rules (Idempotency Keys).

Provider event id

when Inbound webhooks — providers assign a stable id per event.

cost None beyond storage; confirm in their docs that it is stable across retries (Webhook Idempotency).

Producer-assigned business key

when Queue consumers: an order id, a user id plus a period.

cost The producer must set it; a producer retry must reuse it.

Natural unique constraint on the business table

when The domain already forbids duplicates: one invoice per period, one vote per poll.

cost Only available where the domain provides it; cannot express "one attempt" separately from "one row".

Content hash

when Genuinely content-addressed work: file ingestion, cache fills, document indexing.

cost Cannot distinguish two legitimate identical requests — which is fatal for payments and fine for uploads.

Broker message id

when Almost never.

cost Changes on producer retry, so it misses a large share of real duplicates.

How to build it

Most important first.

Choose the identity from the producer, not the transport. An order id, an event id, a client-supplied key — never a broker message id or an arrival timestamp.
Claim with one statement and branch on rows affected. Never read first (Atomic Operations).
Put the claim and the effect in one transaction when both are in the same database. That is the only configuration in which the pair is genuinely atomic (Where the Transaction Boundary Goes).
Where they cannot share a transaction, record an intermediate state before the effect so recovery can query the external system rather than guessing (The Dual Write Problem).
Scope the claim: (consumer, key) or (principal, key). Two different consumers of the same event must each get to process it once.
Set the window from the longest redelivery horizon you face, plus margin, and index the expiry column so cleanup is a range scan.
Prefer a natural unique constraint on the business table where one exists — one shipment per order — over a separate dedupe table. It cannot drift out of sync with the data it protects (Database Constraints).

What can go wrong

Failure modes

A SELECT before the INSERT, which is the same bug in every language and looks correct in review.
Deduplication in process memory or a per-instance cache, which works until the second instance (Stateless Services).
Deduplicating on a transport identifier that changes between attempts, so the mechanism runs and detects nothing.
A dedupe table with no expiry, growing until its index dominates the write path.
Expiry shorter than the redelivery horizon, silently reopening the window for exactly the late duplicates it was built for.
The claim and the effect in separate transactions, so a rollback of one leaves the other.
A unique-constraint violation caught by a broad exception handler and logged as an error, so the duplicate is neither suppressed nor visible.
Bloom-filter or probabilistic detection used where a false positive means silently dropping a real payment.

What can race

The central race of the module: two attempts checking concurrently and both finding nothing. Only an atomic claim closes it.
The claim committing while the effect is still in flight, so the record and the reality disagree for a window.
Expiry deleting a claim microseconds before a late duplicate arrives.
Two consumers of the same event claiming under the same scope, so one legitimately misses work that was addressed to it — a scoping bug that looks like message loss.
A stale-claim sweeper reclaiming an entry whose original attempt is still running, permitting the duplicate it exists to prevent (The Idempotency Key Flow).

Security

Scope claims to the principal or consumer. A globally-scoped claim table lets one caller suppress another's work by pre-claiming a predictable id (Multi-Tenancy).
Do not let the identity be freely chosen by an untrusted caller without scoping; a claim is a small denial-of-service primitive when the namespace is shared.
Bound identity length and claims per principal per window. Attacker-supplied keys are an attacker-controlled write channel (Resource Limits).
For operations that grant value, a suppressed duplicate is a security control: without it, a replayed message is a replayed credit (Replay Attacks).

Misreads

"Check then insert is fine, duplicates are rare." Duplicates are rare per request and certain across enough requests, and they cluster exactly when the system is under stress.
"The unique constraint is a backstop." It is the mechanism. The application code around it is bookkeeping.
"Catching the unique violation is ugly, we should check first." Catching it is the correct implementation of a claim; checking first is the bug.
"Deduplication and idempotency are the same." Deduplication is one way to achieve idempotency. SET status = 'paid' is idempotent and deduplicates nothing (Idempotency vs Deduplication).
"We can deduplicate at the edge for the whole system." Every hop that can redeliver needs its own detection; the edge only covers the first one (At-Least-Once Delivery).

Operating it

How you see it in production

A duplicates_suppressed counter labelled by source — client retry, webhook, queue redelivery, job rerun. Each source has a different fix.
Unique-constraint violation counts from the database, which report the truth whether or not the application code handles them well.
Dedupe store size and oldest entry age, to confirm expiry is running and the window is what you believe it is.
A periodic reconciliation query for duplicate business effects — two payments for one order, two shipments for one line item. It finds what the counters miss, because it measures the outcome rather than the mechanism.
Claim-insert latency p99, since it sits on the hot path of every protected operation.

What changes at 10x and 100x

The dedupe store takes a write for every attempt including every duplicate, so it is often the highest-write table in the path it protects.
At 10x, its index is the cost centre; at 100x, partitioning by time turns expiry from a mass delete into a partition drop (Should I Add an Index?).
Window length multiplied by attempt rate is the steady-state size, and it is the only capacity number that matters.
Probabilistic structures trade memory for a false-positive rate. That trade is acceptable for "have I seen this URL before" and unacceptable for "have I already paid this invoice" (Bloom Filter in DSA covers the structure itself).

What this costs

A dedupe table costs a write and an index on every protected operation, to prevent a small fraction of them from duplicating. That ratio is the price of the guarantee and it is worth paying on anything with a financial effect.
A natural unique constraint on the business table is cheaper and cannot drift, and it only exists where the domain happens to provide one.
A longer window catches later duplicates and costs storage linearly.
Claiming before the work makes concurrency safe and introduces the stuck-claim problem, which needs a sweeper and a threshold (The Idempotency Key Flow).

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALStable identity, atomic claim, bounded window — required regardless of stack.
DATABASE-SPECIFICPostgres reports the claim outcome directly via ON CONFLICT DO NOTHING RETURNING; MySQL needs a duplicate-key catch or INSERT IGNORE, whose broader error suppression hides genuine problems such as truncation; Redis SET NX PX claims and expires in one command but cannot share a transaction with a relational write. All three are correct atomic claims; only the reporting and the transactional reach differ.
SIMPLIFIEDThis lesson assumes a single store can hold the claim. When the claim and the effect must live in different systems, the guarantee weakens to "the claim is atomic, the pair is not", and the recovery path becomes reconciliation rather than rollback.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designidempotency-vs-deduplication idempotency-keys

Databasetransactions-and-acid concurrency-anomalies should-i-add-an-index

Securityreplay-attacks

Performancehot-keys

Concurrencycompare-and-swap atomicity-illusion local-lock-not-distributed reasoning-about-races finding-the-critical-section

DSAbloom-filter

Domains that do not exist yet

— Distributed Systems — deduplication windows, and why every practical system trades an unbounded memory of the past for a bounded one.