PostgreSQL Unique Constraints vs Application-Level Checks for Idempotency

Part of: Database Unique Constraints & Upserts

In distributed payment gateways and high-throughput microservices, the question of where to enforce uniqueness is as important as whether to enforce it. Application-level checks — a SELECT before an INSERT, or a Redis key lookup — look fast and simple. Under concurrent retry load they contain a Time-of-Check to Time-of-Use (TOCTOU) window that produces duplicate records even when the logic looks correct. This runbook walks through the exact conditions under which a PostgreSQL UNIQUE constraint eliminates that race, provides copy-pasteable schemas and upsert patterns for Python, Go, Node.js, and Java, and closes with the failure scenarios and observability hooks you need to catch problems in production.

Prerequisites

Before applying this guide you should understand:

Step 1 — Understand the TOCTOU failure mode

Application-level uniqueness checks follow a read-then-write pattern: check whether the key exists, then insert if absent. Under concurrent load two requests can read “key absent” simultaneously, both proceed to insert, and both succeed — producing a duplicate.

The diagram below shows the race:

TOCTOU race condition in application-level uniqueness checks Two concurrent HTTP requests both read "key absent" before either has written, so both proceed to INSERT and both succeed, creating a duplicate record in the database. Request A App Server PostgreSQL POST /charge (key=K1) SELECT … WHERE key=K1 0 rows — key absent POST /charge (key=K1) — retry SELECT … WHERE key=K1 0 rows — key absent INSERT key=K1 (A wins lock) INSERT key=K1 (B also inserts!) Duplicate charge committed

A UNIQUE constraint evaluated at commit time closes this window entirely. The second INSERT receives SQLSTATE 23505 (unique_violation) before any business logic runs.

Step 2 — Create the idempotency keys table

The table below separates key state, payload metadata, and cached responses to prevent index bloat from large JSON blobs. The UNIQUE constraint on (tenant_id, idempotency_key) is the race-free enforcement point.

CREATE TABLE idempotency_keys (
    id             UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id      UUID         NOT NULL,
    idempotency_key VARCHAR(255) NOT NULL,
    status         VARCHAR(32)  NOT NULL
                   CHECK (status IN ('pending', 'completed', 'failed')),
    response_payload JSONB,
    last_heartbeat_at TIMESTAMPTZ,
    created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    expires_at     TIMESTAMPTZ  NOT NULL,
    CONSTRAINT uk_tenant_key UNIQUE (tenant_id, idempotency_key)
);

-- Active-lookup partial index (excludes expired rows from scans)
CREATE INDEX idx_idempotency_active_lookup
    ON idempotency_keys (tenant_id, idempotency_key)
    WHERE expires_at > NOW();

-- TTL cleanup index
CREATE INDEX idx_idempotency_expired_cleanup
    ON idempotency_keys (expires_at)
    WHERE expires_at < NOW();

-- Orphan-detection index
CREATE INDEX idx_idempotency_stale_pending
    ON idempotency_keys (last_heartbeat_at)
    WHERE status = 'pending';

Use a composite key (tenant_id, idempotency_key) for multi-tenant isolation — single-column keys collapse all tenants into one constraint and enable cross-tenant lock contention during upserts.

For idempotency key generation, prefer UUIDv7 — its time-sortable layout minimises B-tree page splits under high insert rates compared with random UUIDv4. For stateless key reconstruction, use SHA-256(client_id || normalized_payload) so a client can recompute the same key after a crash without storing state.

Step 3 — Write the atomic upsert

The INSERT ... ON CONFLICT DO UPDATE pattern registers the key and conditionally promotes pending → completed in one round-trip, with no separate application SELECT.

INSERT INTO idempotency_keys
    (tenant_id, idempotency_key, status, response_payload, last_heartbeat_at, expires_at)
VALUES
    ($1, $2, 'pending', NULL, NOW(), NOW() + INTERVAL '24 hours')
ON CONFLICT (tenant_id, idempotency_key) DO UPDATE
    SET
        response_payload  = CASE
            WHEN idempotency_keys.status = 'completed'
            THEN idempotency_keys.response_payload
            ELSE EXCLUDED.response_payload
        END,
        status            = CASE
            WHEN idempotency_keys.status = 'pending'
            THEN 'completed'
            ELSE idempotency_keys.status
        END,
        last_heartbeat_at = NOW()
RETURNING status, response_payload;

If RETURNING status = 'completed' and response_payload is non-null, return the cached response immediately — do not re-execute business logic.

Runtime implementations

Python (psycopg3)

import psycopg
from psycopg.rows import dict_row

UPSERT = """
INSERT INTO idempotency_keys
    (tenant_id, idempotency_key, status, response_payload, last_heartbeat_at, expires_at)
VALUES (%s, %s, 'pending', NULL, NOW(), NOW() + INTERVAL '24 hours')
ON CONFLICT (tenant_id, idempotency_key) DO UPDATE
    SET status = CASE
            WHEN idempotency_keys.status = 'pending' THEN 'completed'
            ELSE idempotency_keys.status END,
        response_payload = CASE
            WHEN idempotency_keys.status = 'completed' THEN idempotency_keys.response_payload
            ELSE EXCLUDED.response_payload END,
        last_heartbeat_at = NOW()
RETURNING status, response_payload;
"""

def handle_request(conn, tenant_id: str, idempotency_key: str, process_fn):
    with conn.transaction():
        with conn.cursor(row_factory=dict_row) as cur:
            cur.execute(UPSERT, (tenant_id, idempotency_key))
            row = cur.fetchone()

        if row["status"] == "completed" and row["response_payload"]:
            return row["response_payload"]   # cached — skip business logic

        result = process_fn()

        with conn.cursor() as cur:
            cur.execute(
                "UPDATE idempotency_keys SET status='completed', response_payload=%s "
                "WHERE tenant_id=%s AND idempotency_key=%s",
                (result, tenant_id, idempotency_key),
            )
        return result

Go (pgx/v5)

package idempotency

import (
    "context"
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

const upsertKey = `
INSERT INTO idempotency_keys
    (tenant_id, idempotency_key, status, response_payload, last_heartbeat_at, expires_at)
VALUES ($1, $2, 'pending', NULL, NOW(), NOW() + INTERVAL '24 hours')
ON CONFLICT (tenant_id, idempotency_key) DO UPDATE
    SET status = CASE
            WHEN idempotency_keys.status = 'pending' THEN 'completed'
            ELSE idempotency_keys.status END,
        response_payload = CASE
            WHEN idempotency_keys.status = 'completed' THEN idempotency_keys.response_payload
            ELSE EXCLUDED.response_payload END,
        last_heartbeat_at = NOW()
RETURNING status, response_payload`

func HandleRequest(ctx context.Context, pool *pgxpool.Pool,
    tenantID, key string, process func() ([]byte, error)) ([]byte, error) {

    tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.RepeatableRead})
    if err != nil {
        return nil, err
    }
    defer tx.Rollback(ctx)

    var status string
    var cached []byte
    err = tx.QueryRow(ctx, upsertKey, tenantID, key).Scan(&status, &cached)
    if err != nil {
        return nil, err
    }
    if status == "completed" && cached != nil {
        _ = tx.Commit(ctx)
        return cached, nil
    }

    result, err := process()
    if err != nil {
        return nil, err
    }
    _, err = tx.Exec(ctx,
        `UPDATE idempotency_keys SET status='completed', response_payload=$1
         WHERE tenant_id=$2 AND idempotency_key=$3`,
        result, tenantID, key)
    if err != nil {
        return nil, err
    }
    return result, tx.Commit(ctx)
}

Node.js (node-postgres)

const UPSERT = `
INSERT INTO idempotency_keys
    (tenant_id, idempotency_key, status, response_payload, last_heartbeat_at, expires_at)
VALUES ($1, $2, 'pending', NULL, NOW(), NOW() + INTERVAL '24 hours')
ON CONFLICT (tenant_id, idempotency_key) DO UPDATE
    SET status = CASE
            WHEN idempotency_keys.status = 'pending' THEN 'completed'
            ELSE idempotency_keys.status END,
        response_payload = CASE
            WHEN idempotency_keys.status = 'completed' THEN idempotency_keys.response_payload
            ELSE EXCLUDED.response_payload END,
        last_heartbeat_at = NOW()
RETURNING status, response_payload`;

async function handleRequest(pool, tenantId, idempotencyKey, processFn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL REPEATABLE READ');
    const { rows } = await client.query(UPSERT, [tenantId, idempotencyKey]);
    const { status, response_payload } = rows[0];

    if (status === 'completed' && response_payload) {
      await client.query('COMMIT');
      return response_payload;
    }

    const result = await processFn();
    await client.query(
      `UPDATE idempotency_keys SET status='completed', response_payload=$1
       WHERE tenant_id=$2 AND idempotency_key=$3`,
      [result, tenantId, idempotencyKey]
    );
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Java (JDBC)

import java.sql.*;

public class IdempotencyHandler {

    private static final String UPSERT = """
        INSERT INTO idempotency_keys
            (tenant_id, idempotency_key, status, response_payload, last_heartbeat_at, expires_at)
        VALUES (?, ?, 'pending', NULL, NOW(), NOW() + INTERVAL '24 hours')
        ON CONFLICT (tenant_id, idempotency_key) DO UPDATE
            SET status = CASE
                    WHEN idempotency_keys.status = 'pending' THEN 'completed'
                    ELSE idempotency_keys.status END,
                response_payload = CASE
                    WHEN idempotency_keys.status = 'completed' THEN idempotency_keys.response_payload
                    ELSE EXCLUDED.response_payload END,
                last_heartbeat_at = NOW()
        RETURNING status, response_payload
        """;

    public String handleRequest(Connection conn, String tenantId,
                                String key, Supplier<String> process) throws SQLException {
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        try (PreparedStatement ps = conn.prepareStatement(UPSERT)) {
            ps.setString(1, tenantId);
            ps.setString(2, key);
            ResultSet rs = ps.executeQuery();
            rs.next();
            String status   = rs.getString("status");
            String cached   = rs.getString("response_payload");

            if ("completed".equals(status) && cached != null) {
                conn.commit();
                return cached;
            }
            String result = process.get();
            try (PreparedStatement upd = conn.prepareStatement(
                    "UPDATE idempotency_keys SET status='completed', response_payload=? "
                    + "WHERE tenant_id=? AND idempotency_key=?")) {
                upd.setString(1, result);
                upd.setString(2, tenantId);
                upd.setString(3, key);
                upd.executeUpdate();
            }
            conn.commit();
            return result;
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }
}

Step 4 — Add a Redis fast-path pre-filter

For workloads exceeding ~500 requests/second against the same key space, add Redis SET NX as a pre-filter before touching PostgreSQL. The single-command atomic check-and-set produces no race condition of its own:

SET idempotency:{tenant}:{key} "processing" NX EX 300

If the command returns nil (key already set), the request is a duplicate — return the cached response without querying the database. Never separate SETNX and EXPIRE into two commands; a process crash between them leaves keys without expiry and blocks all future retries for that key.

The TTL of 300 seconds must exceed your maximum observed processing window. For payment flows with external provider calls, use 600 seconds (10 minutes).

Step 5 — Verify the implementation

Simulate a duplicate request

-- Terminal 1: begin transaction but do not commit yet
BEGIN;
INSERT INTO idempotency_keys
    (tenant_id, idempotency_key, status, expires_at)
VALUES (
    '00000000-0000-0000-0000-000000000001',
    'test-key-duplicate',
    'pending',
    NOW() + INTERVAL '1 hour'
);

-- Terminal 2 (different session): attempt the same insert
INSERT INTO idempotency_keys
    (tenant_id, idempotency_key, status, expires_at)
VALUES (
    '00000000-0000-0000-0000-000000000001',
    'test-key-duplicate',
    'pending',
    NOW() + INTERVAL '1 hour'
);
-- Terminal 2 blocks here until Terminal 1 commits or rolls back.

-- Terminal 1: commit
COMMIT;
-- Terminal 2 receives: ERROR 23505 unique_violation — constraint enforced correctly.

Inspect active lock contention

SELECT l.locktype, l.mode, a.pid, a.state, a.wait_event, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation::regclass = 'idempotency_keys'::regclass
  AND NOT l.granted;

Check orphaned pending keys

SELECT id, tenant_id, idempotency_key, created_at, last_heartbeat_at
FROM idempotency_keys
WHERE status = 'pending'
  AND last_heartbeat_at < NOW() - INTERVAL '5 minutes';

Confirm index usage on active lookups

EXPLAIN (ANALYZE, BUFFERS)
SELECT status, response_payload
FROM idempotency_keys
WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
  AND idempotency_key = 'test-key-duplicate'
  AND expires_at > NOW();
-- Must show "Index Scan using idx_idempotency_active_lookup"

Failure scenarios & debugging

Failure Scenario Remediation Steps Observability Hooks
Deadlock on concurrent ON CONFLICT updates — ERROR: deadlock detected during high-throughput retries to the same key 1. Run the pg_locks query above to identify contention. 2. Wrap with advisory locks: SELECT pg_advisory_xact_lock(hashtext($1 || $2)). 3. Sort incoming requests by idempotency_key at the API gateway to enforce deterministic lock ordering. pg_lock_wait_seconds histogram; alert when p99 > 2 s sustained for 60 s
Cache stampede — Redis TTL expires mid-spike — hundreds of identical requests bypass the pre-filter and hit PostgreSQL simultaneously 1. Introduce jittered TTLs: EX = base_seconds + rand(0, base_seconds * 0.2). 2. Deploy a background refresher at 80% of TTL. 3. Add a circuit breaker that queues duplicates during stampede recovery. idempotency_cache_hit_ratio gauge; alert when ratio drops below 0.90 for 30 s
Orphaned pending keys block retries — process killed after acquiring key but before committing; status = 'pending' never transitions Deploy scheduled cleanup; transition stale pending keys to failed if last_heartbeat_at < NOW() - INTERVAL '5 minutes'. Use heartbeat updates every 10 s during processing. Alert when COUNT(*) WHERE status='pending' AND last_heartbeat_at < NOW() - INTERVAL '5 min' > 0
Constraint bypass due to missing transaction wrap — business logic runs outside the idempotency transaction, so a mid-operation crash leaves no cached response but the key shows completed Audit code paths to confirm business mutation and UPDATE idempotency_keys SET status='completed' are in the same BEGIN/COMMIT block. Add integration tests that kill the process between steps and confirm rollback. Trace span transaction.commit must span both the upsert and the domain mutation

SRE / observability checklist

Instrument these specific signals for production alerting:

  1. idempotency_db_violations_total (Prometheus counter) — increments on every caught SQLSTATE 23505; non-zero values in a 1-minute window indicate active retry storms.
  2. idempotency_cache_hit_ratio (gauge) — Redis fast-path hit rate; alert if it falls below 0.90 for more than 30 seconds.
  3. pg_lock_wait_seconds (histogram, label: relation="idempotency_keys") — row-level lock contention; alert if p99 exceeds 2 seconds.
  4. idempotency_orphaned_pending_total (gauge) — count of status='pending' rows older than 5 minutes; should be 0 at steady state.
  5. OpenTelemetry span db.idempotency.upsert — capture db.statement, db.rows_affected, and outcome (inserted | conflict_resolved | conflict_skipped).
  6. Structured log field constraint_status — emit conflict_resolved, duplicate_returned, or inserted on every request so reconciliation queries can count deduplication events without touching the database.
{
  "level": "info",
  "idempotency_key": "018f9c2a-7b4d-7c8e-9a1f-3d5e6f7a8b9c",
  "tenant_id": "acme-payments",
  "constraint_status": "conflict_resolved",
  "retry_count": 2,
  "region": "us-east-1",
  "latency_ms": 3.2
}

SLO targets:

  • p99 deduplication latency: <50 ms (Redis cache hit), <150 ms (database constraint path)
  • Constraint violation rate: <0.01% of total requests
  • Lock wait timeout: alert if pg_lock_wait_seconds p99 > 2 s for more than 60 s
  • Orphaned pending keys: alert if count > 0 for more than 5 minutes