PostgreSQL duplicate key violation: determine whether retry or data repair is correct

The symptoms

• •PostgreSQL log or driver reports a 23505 SQLSTATE on a statement returning from an INSERT, INSERT ... ON CONFLICT DO UPDATE, or UPDATE that touches a unique/primary index; the message typically contains 'duplicate key value violates unique constraint'.
• •Application error mapping surfaces 'UniqueViolation' or 'IntegrityError' on what the caller thought was a fresh row; retries succeed for some calls but a stable fraction repeatedly returns the same violation.
• •Logical-replication workers or ETL pipelines fail with 23505 against target tables that have materialized unique constraints, often under concurrent load or after a schema backfill.
• •A row that appears visually identical to an existing record by business key (email, external_id, slug) causes the failure even though no manual duplicate was created, suggesting sequence/identity misalignment or a partial-index trap.

Likely causes

• •Two callers raced on the same business key and the second commit observed the first commit's index entry; without explicit conflict handling, both attempts cannot succeed.
• •A sequence (SERIAL/IDENTITY) is shared across sessions or restored from a backup to a value below the current max, so new inserts collide with pre-existing rows.
• •An ON CONFLICT clause targets an index whose column list differs from the business expectation (e.g., a partial unique index the planner doesn't see, or a different column order).
• •Replication or a reset-replica operation replayed rows whose index keys already exist, producing a 23505 that is not a transient race.
• •Application-level upserts re-insert what should be an UPDATE because the WHERE clause misses existing rows, leading to repeated 23505s on the same candidate value.
• •Schema/maintenance work added or restored a unique constraint that the data currently violates, so the first INSERT after the constraint is created fails deterministically.

First ten minutes

1. 01Capture the exact SQLSTATE and error message from the PostgreSQL log or driver buffer; confirm the class is 23 (Integrity Constraint Violation) and subclass 23505 specifically before any retry discussion.
2. 02Identify the constraint name reported in the message and resolve it via pg_constraint to know the table, column list, and whether it is primary key, unique, partial, deferrable, or a unique index backing a constraint.
3. 03Reproduce or bound the violation: run a read-only SELECT against the candidate key, then attempt the INSERT outside the transaction with the same parameter binding to see whether it fails deterministically or only under concurrency.
4. 04Check whether the failing code path expects retry semantics: look for explicit UPSERT (ON CONFLICT), savepoint/ROLLBACK TO SAVEPOINT, or a try/except translating 23505 into a follow-up UPDATE; absence of these is a strong signal that 'retry' is the wrong default.

Evidence to collect

• •The exact 23505 message text including the constraint/relation name and the conflicting key value reported by PostgreSQL.
• •pg_constraint (and pg_indexes/pg_class) for the named constraint: columns, index method, predicate if it is a partial index, deferral setting, and ownership.
• •A bounded read-only cross-check: a SELECT that finds existing rows matching the candidate key, plus a count to estimate blast radius rather than a single value.
• •Concurrency context for the failing session: isolation level, whether the session is in a transaction, and whether other writers are active against the same index (consult pg_stat_activity/pg_locks just before the next attempt).
• •Statement history from pg_stat_statements if available, plus the application-side stack trace mapping the 23505 to a specific code path so the retry/repair decision can be scoped to that path.

Where to look

• •PostgreSQL server log (log_destination = csvlog or stderr) at ERROR level, filtered for SQLSTATE 23505 and the named relation/constraint to confirm the exact key.
• •System catalogs: pg_constraint joined to pg_class (relname) and pg_attribute (attnum) to retrieve the column list of the offending unique constraint.
• •pg_indexes for the underlying index, plus pg_index.indpred to surface partial-index predicates that the application's ON CONFLICT may not match.
• •pg_stat_activity and pg_locks at the boundary of the failing transaction to see whether the violation coincides with another inserter's granted lock, distinguishing contention from deterministic collision.
• •pg_stat_statements for the failing query's call/execution averages, and pg_stat_database / pg_stat_user_tables for write-rate context to decide if concurrency is the likely trigger.

Diagnostic steps

1. 01Confirm SQLSTATE: parse the error message for 'duplicate key value violates unique constraint' and record the SQLSTATE; 23505 is the canonical signal, but each driver also exposes a typed exception (e.g., psycopg.errors.UniqueViolation) that should be matched.
2. 02Map constraint to columns: SELECT conname, conrelid::regclass, pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = '<reported_conname>' to get the index target the database enforced.
3. 03Prove existence of the colliding row: SELECT 1 FROM <table> WHERE <key_columns>=<candidate_values> FOR UPDATE SKIP LOCKED LIMIT 1 inside a single-row read-only probe to show whether a deterministic duplicate exists.
4. 04Distinguish collision from contention: if the row exists with an uncommitted version visible to no other transaction, the failure is a race; if it exists in a committed state matching the candidate key exactly, the failure is a hard violation and retry will not change the outcome.
5. 05Check whether the failing path was built for upsert: look for ON CONFRRONT (sic: ON CONFLICT) targets; verify that the conflict_target matches the constraint's columns and any partial-index predicate, otherwise the conflict handling is effectively absent even though it appears in the SQL.
6. 06Rule out sequence replay: SELECT last_value, is_called FROM <sequence_regclass> and compare against MAX(<id_column>); a last_value <= MAX is a strong indicator that the violation is mechanical, not a race.
7. 07Rule out replication/seed replay: confirm the row's provenance by inspecting xmin/txid_current context or replica mode; logical-replication replay against a table that already has the row produces 23505s that retry will not fix.

Common mistakes

• •Adding a sleep-and-retry loop around a 23505 without verifying that the conflict target column set matches the violation; the loop then becomes a silent source of partial writes with no compensating effect.
• •Assuming 'unique violation = race' and resending the INSERT; when the conflicting row is already committed, retries amplify load and write-amplify the log without converging to success.
• •Treating a 23505 from ON CONFLICT DO NOTHING as a silent no-op without logging the constraint name; the error is then dismissed even when it indicates a partial-index mismatch or a miswired conflict_target.
• •Performing cleanup scripts (DELETE/duplicates) before identifying which key family collides, producing more 23505s on adjacent inserts because the underlying constraint was never re-evaluated.
• •Patching the application to use a fallback UPDATE without first checking that the row is reachable by the same key path the original INSERT used; the path divergence produces 'no rows updated' and a hidden duplicate elsewhere.

Safe fixes

• •If and only if the diagnostic shows an existing committed row matching the candidate key, route the caller to an UPDATE of that row in the same transaction; or wrap the INSERT into INSERT ... ON CONFLICT (<key_cols>) DO UPDATE SET <non_key_cols>=EXCLUDED.<non_key_cols> after verifying the conflict target matches the constraint.
• •If and only if the diagnostic shows a race (no committed duplicate but a concurrent inserter), apply bounded retry with linear/jittered backoff inside the same transaction or per-request; cap retries to a small N and fail closed rather than retry indefinitely.
• •If the sequence is the cause (last_value <= MAX(<id_column>)), rebuild the sequence via setval(<sequence_regclass>, MAX(<id_column>)) in a maintenance window using SELECT setval(...) where the argument is the observed maximum from a read-only probe; do not arbitrarily pick a high number.
• •If a partial unique index is the culprit and ON CONFLICT omits the predicate, add WHERE <matches_index_predicate> to the conflict_target or align the application query so the predicate holds; document the change in the migration/constraint diff.
• •If logical replication produces the violation, resolve the conflict on the subscriber side by skipping the offending replication message and verifying downstream state, not by retrying the producer.
• •Do not DROP a unique constraint based on a single 23505; instead, validate the constraint against pg_constraint and the column list before any schema edit, and prefer dropping/recreating with CREATE UNIQUE INDEX CONCURRENTLY where supported.
• •Do not mass-delete 'duplicates' before classifying them: any cleanup must be gated on the column set of the reported constraint and on a read-only count that bounds scope.

Prove the fix

1. 01Re-run the originally failing statement path against a controlled fixture or staging and assert that the SQLSTATE is no longer returned for at least N independent invocations where N is the documented retry cap or merge-window depth.
2. 02Verify that the new code path is observably distinct: confirm that UPDATE-bound remediations produce the expected updated_columns state via a SELECT returning those columns after the write; confirm that retry-bound remediations log a transition from a non-23505 soft failure (e.g., serialization failure) to success within bounded attempts.
3. 03Re-check pg_constraint and pg_indexes after the fix to ensure the unique object is intact (no accidental drop), and confirm pg_stat_user_tables shows the dead/insert counters moving consistent with the new code path rather than oscillating.
4. 04Confirm the application-side counter/log for 23505 events on this relation drops to expected baseline (often zero) for an observation window that covers at least one full peak write-interval, as visible in pg_stat_statements or application telemetry.
5. 05Cross-check by re-reading the candidate key with FOR KEY SHARE/FOR UPDATE in a separate session to prove the row state matches the post-fix expectation (existing row updated, or no row written when retry is appropriate).

Prevention and next steps

• •Standardize the conflict-target definition across application and migrations: one canonical unique index per business key, with conflict_target columns and any partial predicate replicated exactly in every ON CONFLICT clause.
• •Sequence hygiene: after any restore, clone, or pg_resetwal event, run setval against the column's sequence using MAX(<column>) as the read-only anchor, and forbid app-level insertion from skipping the sequence.
• •Wrap write paths with explicit error-type handling so UniqueViolation (23505) is logged with constraint name and key value, not swallowed; treat this as a per-path event metric, not a generic exception.
• •Prefer INSERT ... ON CONFLICT as the single meshed write primitive where the business intent is upsert; avoid mixing raw INSERT with separate UPDATE paths that can desynchronize under concurrency.
• •Add a migration-time data audit before any new unique constraint: scan the table for existing duplicates against the proposed key and fail the migration if any are found, rather than discovering them at the first INSERT after deploy.

Safe commands and checks

SELECT conname, conrelid::regclass, pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = '<reported_constraint_name>';
SELECT indexrelid::regclass, indisunique, indiskey, pg_get_expr(indpred, indrelid) FROM pg_index WHERE indexrelid::regclass::text = '<index_name>';
SELECT 1 FROM <table> WHERE <col_a> = '<value_a>' AND <col_b> = '<value_b>';
SELECT relname, n_tup_ins, n_tup_upd, n_live_tup FROM pg_stat_user_tables WHERE relname = '<table_name>';
SELECT datname, usename, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle';
SELECT sequence_name, last_value, is_called FROM <sequence_regclass>;
SELECT MAX(<id_column>) FROM <table>;
SELECT total_exec_time, calls, mean_exec_time, query FROM pg_stat_statements WHERE query ILIKE '%<table_or_constraint>%' ORDER BY total_exec_time DESC LIMIT 10;