Database Unique Constraints & Upserts

Part of: Backend Implementation & Storage Patterns

In distributed payment systems and microservice pipelines, network timeouts routinely fire after a request reaches the server but before the client receives a response. The client retries; the server may execute the same operation twice. The only mechanism that eliminates this race condition without coordination overhead is a database-level unique constraint on the idempotency key — the relational engine’s lock manager serializes conflicting inserts so that the second writer either blocks until the first commits or receives an immediate conflict error. This page covers the guarantee that model provides, how PostgreSQL and MySQL implement it via upsert syntax, the edge cases that break it, and the operational work required to keep it healthy at scale.

---

Guarantee Model

Database unique constraints provide exactly-once write semantics within a single database shard for any set of concurrent writers sharing the same primary storage. The guarantee holds as long as:

1. Every insert that carries a business effect bundles the idempotency key insertion and the business logic mutation inside a single atomic transaction.
2. The unique constraint is defined at the database level, not enforced by application code before the insert.
3. The application layer, on receiving a constraint violation, fetches and returns the previously committed response rather than bubbling an error.

The guarantee breaks under the following conditions:

• Cross-shard or multi-region active-active topologies — two shards each enforce uniqueness locally but not globally. A key inserted to region A is invisible to region B until replication catches up; during the lag window a second writer in region B can commit a duplicate.
• Clock skew with time-based TTL eviction — if an idempotency key expires at the database level before the client’s retry window closes, a late retry can execute the operation a second time as though it were a fresh request.
• Read-then-write application checks — a SELECT followed by a conditional INSERT is not atomic. Two concurrent threads can both observe the key as absent and both proceed to insert; only the constraint catches the second one if it is properly defined.

For multi-region deployments, layer Redis cache-based deduplication as a fast-fail pre-filter in front of the relational store to absorb cross-region retry traffic before it reaches the constraint layer.

---

Core Algorithm

The upsert pattern bundles key registration, business logic, and response caching into one atomic unit. The sequence below shows the write path for a payment charge request:

The critical invariant: the idempotency key upsert and the business logic mutation share the same transaction boundary. If the ledger insert fails, the entire transaction rolls back, leaving the idempotency key unregistered so the client can safely retry. This is the integration point described in transaction scoping and atomic operations — business logic and key registration must commit together or not at all.

---

Implementation Variants

PostgreSQL — ON CONFLICT DO UPDATE

The recommended PostgreSQL pattern stores the serialized response alongside the key so retries receive an identical reply without re-executing any logic:

CREATE TABLE idempotency_keys (
    key_hash      TEXT        NOT NULL,
    tenant_id     UUID        NOT NULL,
    status        TEXT        NOT NULL DEFAULT 'pending',
    response_body JSONB,
    response_code SMALLINT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at    TIMESTAMPTZ NOT NULL,
    CONSTRAINT uq_idempotency_key UNIQUE (key_hash, tenant_id)
);
CREATE INDEX idx_ik_expires ON idempotency_keys (expires_at);

-- Within the transaction that also runs business logic:
INSERT INTO idempotency_keys (key_hash, tenant_id, status, expires_at)
VALUES ($1, $2, 'pending', now() + INTERVAL '24 hours')
ON CONFLICT (key_hash, tenant_id) DO UPDATE
    SET status = idempotency_keys.status   -- no-op update to trigger RETURNING
RETURNING status, response_body, response_code,
          (xmax = 0) AS was_inserted;

xmax = 0 is a PostgreSQL internals trick: on a freshly inserted row the transaction max (xmax) is zero; on a conflicted update it holds the conflicting transaction ID. This gives the application a single round-trip to determine whether it must execute business logic or return the cached response.

PostgreSQL — ON CONFLICT DO NOTHING

Use DO NOTHING when the response body is large and you do not want to store it in the key row. The trade-off is a second round-trip to fetch the cached response:

INSERT INTO idempotency_keys (key_hash, tenant_id, status, expires_at)
VALUES ($1, $2, 'pending', now() + INTERVAL '24 hours')
ON CONFLICT (key_hash, tenant_id) DO NOTHING;

-- Check whether the insert landed:
SELECT status, response_body, response_code
  FROM idempotency_keys
 WHERE key_hash = $1 AND tenant_id = $2;

If the SELECT returns a row with status = 'completed', replay the cached response. If it returns status = 'pending', the original request is still in flight — return 409 Conflict with a Retry-After header.

MySQL — INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO idempotency_keys (key_hash, tenant_id, status, expires_at)
VALUES (?, ?, 'pending', DATE_ADD(NOW(), INTERVAL 24 HOUR))
ON DUPLICATE KEY UPDATE
    status = IF(status = 'pending', 'pending', status),
    updated_at = NOW();

SELECT ROW_COUNT() AS rows_affected;
-- rows_affected = 1 → new insert; = 2 → conflict (existing row updated)

Note that MySQL’s ON DUPLICATE KEY UPDATE always counts a conflict as 2 affected rows, not 1. This is the only reliable way to distinguish a new insert from a replay in MySQL without a second SELECT.

Partial Unique Index (PostgreSQL)

When idempotency keys should only be unique within a specific lifecycle state — for example, only among active subscriptions, not cancelled ones — a partial index scopes the constraint without growing index size:

CREATE UNIQUE INDEX uq_active_payment_key
    ON idempotency_keys (key_hash, tenant_id)
    WHERE status NOT IN ('cancelled', 'expired');

This is particularly useful in multi-tenant SaaS platforms where the same idempotency key may legitimately recur across separate billing periods after a subscription is cancelled.

Variant Comparison

---

Edge Cases & Failure Scenarios

---

Schema Design for Idempotency Tables

Key Hashing

Store SHA-256(idempotency_key) rather than the raw key string. This keeps the unique index compact (32 bytes fixed-width vs. up to 255 bytes variable), prevents PII leakage in query logs and slow-query reports, and normalises key length for consistent B-tree page utilization. Use a dedicated column for the tenant or client identifier rather than embedding it in the key hash — composite constraints (key_hash, tenant_id) are more flexible than opaque blobs.

Time-Ordered Identifiers Reduce Index Hotspots

Sequential UUIDs (v4) generate random index pages and cause B-tree splits across the entire key space. Use ULIDs or UUIDv7 for row primary keys (not the idempotency key hash, which is fixed) to keep new inserts on the rightmost index page and reduce write amplification. This matters at > 5,000 inserts per second on a single PostgreSQL primary.

Covering Index for Upsert Validation

Add a covering index that includes status, response_body, and response_code so the upsert path executes as an index-only scan without touching the heap:

CREATE UNIQUE INDEX uq_ik_covering
    ON idempotency_keys (key_hash, tenant_id)
    INCLUDE (status, response_code, response_body);

PostgreSQL 11+ supports INCLUDE columns on unique indexes. This eliminates the heap fetch that would otherwise be required to read response_body after a conflict.

---

Operational Concerns

TTL and Partition Pruning

The idempotency key storage TTL management page covers the full retention policy decision tree, but the core operational constraint is this: the server-side TTL must be at least 2× the client’s maximum retry window. If a client retries for up to 10 minutes, keys must live for at least 20 minutes. PCI-DSS and SOX mandates typically require 90-day retention for transactional records, which conflicts with aggressive key pruning — keep a thin audit_log table with immutable key_hash, tenant_id, created_at, and outcome columns separate from the hot idempotency table so the hot table can be pruned aggressively while the audit record persists.

For tables exceeding 10 million rows, declare range partitions by created_at with weekly intervals and drop old partitions to avoid full-table vacuum overhead:

CREATE TABLE idempotency_keys (
    key_hash   TEXT NOT NULL,
    tenant_id  UUID NOT NULL,
    status     TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE ik_2026_w26 PARTITION OF idempotency_keys
    FOR VALUES FROM ('2026-06-23') TO ('2026-06-30');

Dropping a partition (DROP TABLE ik_2026_w25) is metadata-only and does not trigger autovacuum.

SRE Alert Thresholds

Connection Pool Considerations

Every upsert opens and holds a database connection for the duration of the transaction. Under retry storms, connection exhaustion occurs before constraint violations — clients queue waiting for a free connection rather than receiving a fast 409. Set statement_timeout = 5000 (ms) on the idempotency transaction and configure pgBouncer in transaction mode so connections are returned to the pool between the upsert and any downstream business logic that does not need the same transaction.

---

Related

• Backend Implementation & Storage Patterns — parent section covering all storage-layer idempotency mechanisms
• PostgreSQL Unique Constraints vs. Application-Level Checks — deep comparison of enforcement strategies, benchmark data, and ORM compatibility
• Transaction Scoping & Atomic Operations — how to correctly bundle idempotency key registration with business mutations in a single transaction
• Redis Cache-Based Deduplication — fast-fail pre-filter layer to absorb retry storms before they reach the relational constraint
• Idempotency Key Storage TTL Management — retention policy design, partition pruning schedules, and compliance-aware expiry strategies