Two concurrent PostgreSQL transactions can attempt to insert the same unique key before either transaction has committed. INSERT ... ON CONFLICT resolves this race through the unique index chosen as the conflict arbiter, rather than by running a separate existence test before the insert.
That distinction matters because a prior SELECT cannot reserve the absence of a row under ordinary Read Committed execution. Another transaction can insert the same key after the check. Conflict arbitration places the decision inside the write operation, where PostgreSQL can coordinate with concurrent index activity.
The arbiter is a unique index
For ON CONFLICT DO UPDATE, PostgreSQL must identify the uniqueness rule that decides whether the proposed row conflicts. A conflict target can name columns or expressions for unique-index inference, or it can name a constraint explicitly.
CREATE TABLE account_state (
account_id bigint PRIMARY KEY,
revision bigint NOT NULL,
payload jsonb NOT NULL
);
INSERT INTO account_state (account_id, revision, payload)
VALUES (42, 8, '{"status":"active"}')
ON CONFLICT (account_id)
DO UPDATE SET
revision = EXCLUDED.revision,
payload = EXCLUDED.payload;Here the primary-key index is the arbiter for account_id. The executor is not interpreting every possible collision as permission to run the update branch. The selected arbiter defines the conflict relevant to this statement.
Unique-index inference also makes the statement less dependent on a particular constraint name. If an equivalent unique index replaces an older one, an inference target based on the indexed columns can continue to identify the applicable uniqueness rule.
Conflict detection is part of insertion
A conventional check-then-insert sequence has two independent database operations:
SELECT 1 FROM account_state WHERE account_id = 42;
INSERT INTO account_state (account_id, revision, payload)
VALUES (42, 8, '{"status":"active"}');The first statement reports only what its snapshot permits it to see. It does not establish that a later insert owns the key. Concurrent sessions can both observe no matching row and then compete on the unique index.
ON CONFLICT removes that gap from the application-level decision. PostgreSQL attempts the insertion while coordinating uniqueness with other transactions. A competing transaction can make the proposed key unavailable even when its row was not visible to the command’s initial Read Committed snapshot.
This produces a notable visibility boundary. Under Read Committed isolation, ON CONFLICT DO UPDATE can update a row created by a concurrent transaction even when no version of that row was conventionally visible to the command snapshot at statement start. Conflict handling and ordinary snapshot visibility are related but not identical mechanisms.
Speculative insertion avoids exposing a failed candidate as final
PostgreSQL’s implementation uses speculative insertion for the ON CONFLICT path. A candidate heap tuple is inserted in a provisional state while the unique indexes are checked. If no relevant conflict exists, the speculative tuple is completed as a normal insertion. If an arbiter conflict is found, the speculative insertion is canceled and the configured conflict action runs.
This mechanism prevents a simple uniqueness failure from being the only possible outcome after the candidate has entered the insertion machinery. It also gives concurrent index operations a protocol for waiting on, or resolving against, an insertion whose final status has not yet been decided.
The provisional tuple is an internal concurrency mechanism, not an extra logical row exposed to SQL clients. The statement still presents an insert, update, or no-op result according to its conflict clause and any independent errors.
DO NOTHING can suppress an insert against an unseen row
ON CONFLICT DO NOTHING has a subtle Read Committed behavior. An insertion can be skipped because of a concurrent transaction even when the conflicting row is not visible to the command snapshot.
INSERT INTO account_state (account_id, revision, payload)
VALUES (42, 8, '{"status":"active"}')
ON CONFLICT (account_id) DO NOTHING;A zero-row insertion result therefore does not imply that a normal snapshot-based query in the same command context had already observed the conflicting tuple. Unique enforcement must account for concurrent writes, while snapshot visibility follows transaction-isolation rules.
This difference is especially relevant when application code treats DO NOTHING as a test for pre-existing visible state. It is more precise to treat it as a statement that the proposed row did not become a new row because a usable uniqueness rule prevented that outcome.
DO UPDATE locks the conflicting row
When the alternate action is DO UPDATE, PostgreSQL identifies and locks the conflicting row before applying the update action. A WHERE condition on the update does not move conflict detection after that predicate. The conflict is identified first, the row is locked, and the condition then decides whether the update is applied.
INSERT INTO account_state (account_id, revision, payload)
VALUES (42, 9, '{"status":"paused"}')
ON CONFLICT (account_id)
DO UPDATE SET
revision = EXCLUDED.revision,
payload = EXCLUDED.payload
WHERE account_state.revision < EXCLUDED.revision;If the existing row already has revision 10, the predicate can reject the update. The row still participated in conflict handling and locking. This makes the predicate a filter on the alternate update, not a filter on uniqueness arbitration itself.
A single statement cannot update the same target row twice
INSERT ... ON CONFLICT DO UPDATE is deterministic with respect to target rows. One execution cannot affect the same existing row more than once. If multiple proposed rows map to the same arbiter key and would cause repeated updates of one target row, PostgreSQL raises a cardinality violation rather than choosing an input order as the winner.
INSERT INTO account_state (account_id, revision, payload)
VALUES
(42, 11, '{"source":"a"}'),
(42, 12, '{"source":"b"}')
ON CONFLICT (account_id)
DO UPDATE SET
revision = EXCLUDED.revision,
payload = EXCLUDED.payload;The two input rows collide on the same target identity. Treating this as two sequential updates would make the result depend on row-processing order, so PostgreSQL rejects the ambiguous effect.
Arbiter constraints have boundaries
The arbiter path is not a general constraint-recovery mechanism. ON CONFLICT DO UPDATE uses suitable unique indexes or NOT DEFERRABLE unique constraints. Exclusion constraints are not supported as arbiters for that update action, and deferrable uniqueness does not fit an operation that must resolve the collision during the statement’s insertion path.
Partitioning adds another boundary. An ON CONFLICT DO UPDATE against a partitioned table cannot use the update branch to change the conflicting row’s partition key when that change would require moving the row to another partition.
These limits follow from the operation’s core contract: conflict arbitration must identify a specific existing row through an immediately usable uniqueness rule and resolve the proposed insertion atomically against that row.
Atomicity does not remove contention
ON CONFLICT closes a race in application-level insert-or-update logic, but it does not make a heavily contested key cheap. Concurrent statements targeting the same arbiter value still serialize around index conflict resolution and row locking. The database can guarantee a coherent outcome while sessions wait for transactions that own relevant index entries or target rows.
A workload with thousands of independent keys can therefore behave very differently from one where many writers converge on a single key. Both use the same SQL construct, but the second workload concentrates synchronization on the same uniqueness and row-locking resources.
The useful property of ON CONFLICT is not absence of contention. It is that the uniqueness decision, concurrent insertion protocol, and alternate action form one database operation, so a key collision is resolved within PostgreSQL’s concurrency control rather than through a vulnerable application-side existence check.