PostgreSQL UPSERT Interview Questions: Concurrent Inserts, Unique Constraints, and Lost Updates

Two workers read a counter of 100. Each adds 10, then writes 110 with INSERT ... ON CONFLICT DO UPDATE. Both statements succeed. The counter ends at 110. The database enforced uniqueness, yet the application lost one increment.

That distinction is central to PostgreSQL UPSERT interview questions: a unique key identifies the row, the update expression defines the mutation, and a request ID identifies a replay. Solving one does not automatically solve the others. This guide walks through concurrent inserts, stale replacements, conditional updates, and uncertain retries with concrete examples.

Evidence boundary: Official facts below come from PostgreSQL 18 documentation. The schedules, SQL, and interview follow-ups are original teaching examples, reviewed against those docs rather than executed against a database. No candidate reports are used, and the exercises do not establish how frequently any employer asks these questions.

What does PostgreSQL UPSERT actually guarantee?

Official fact: ON CONFLICT DO UPDATE provides an atomic insert-or-update outcome, barring an independent error. It uses a unique index or eligible constraint to arbitrate the conflict; excluded refers to the proposed insertion values. A conditional update can deliberately choose not to change the conflicting row. PostgreSQL INSERT documentation.

Use a small schema whose identity is unambiguous:

CREATE TABLE counters (
    account_id bigint PRIMARY KEY,
    total bigint NOT NULL
);

INSERT INTO counters AS c (account_id, total)
VALUES (42, 10)
ON CONFLICT (account_id)
DO UPDATE SET total = c.total + EXCLUDED.total
RETURNING account_id, total;

Worked interpretation: For a missing account, this inserts an initial total of 10. For an existing account, it adds 10 to the stored total. That is appropriate only if the incoming value represents a delta. If the caller sends a complete balance of 110, the same expression would add the whole balance and produce the wrong result.

In an interview, explain the input contract before the syntax. “This field contains an increment” is a stronger answer than “UPSERT handles races.” Also state whether absent accounts should be created. An update-only balance adjustment may need to reject a missing account instead.

What happens when two sessions insert the same key?

Official fact: PostgreSQL's unique-index checks account for transaction visibility. When a potential duplicate belongs to an unfinished transaction, the check can wait for that transaction, then reconsider the conflict. A preceding application SELECT cannot replace this arbitration. Index uniqueness checks.

Original two-session exercise: Start with no account 42. Both transactions use the additive statement above at Read Committed. Hold session A open after its statement, before committing.

Step	Session A	Session B	Expected reasoning
1	Begins and inserts (42, 10)	Has not written	A's row is not committed
2	Remains open	Attempts (42, 10)	B waits on the conflicting transaction
3	Commits	Resumes conflict handling	B adds 10 to the existing total
4	Finished	Commits	Stored total is 20

Change one event: A rolls back at step 3. B can then insert its own row, leaving 10 after B commits. Change B to DO NOTHING: after A commits, B skips the insertion and the total remains 10.

Ask what B does after the blocker commits versus aborts. Then introduce a client timeout while B waits. A timeout seen by the caller is a separate observation from a confirmed transaction rollback; the recovery logic needs to establish which outcome occurred.

Why can an atomic UPSERT still lose an update?

Original failure analysis: Suppose account 42 already contains 100. A and B separately read that value and each computes 110 in application code. Both send this replacement:

INSERT INTO counters AS c (account_id, total)
VALUES (42, 110)
ON CONFLICT (account_id)
DO UPDATE SET total = EXCLUDED.total;

The conflict handling serializes the writes, but both writes request the same absolute value. The final 110 faithfully reflects the SQL. It does not reflect the intended two increments. Neither a unique constraint nor the word “atomic” reconstructs the missing business operation.

With c.total + EXCLUDED.total and an incoming delta of 10, the two successful operations produce 120. That fixes this stale replacement example. It does not make a retry harmless: replaying one successful increment produces 130.

Incoming meaning	Update policy	Concurrent behavior in this example	Replay consequence
Absolute replacement: 110	Assign incoming total	A later replacement can erase an earlier change	Repeating it can overwrite newer state
Delta: +10	Add to stored total	Both successful increments count	A duplicate increments again
Versioned snapshot	Accept only a newer version	Older snapshots cannot replace newer ones	Same version is skipped under the stated policy
Identified operation	Deduplicate request and mutate together	Distinct operations can each apply once	A retained matching request is recognized

This matrix is a design inference, not a guarantee attached to every UPSERT. Choose the row that matches the domain. A stock snapshot, a vote, and a payment command have different meanings even when all three use the same SQL feature.

Which unique constraint should the conflict target use?

Official fact: A primary key enforces uniqueness and non-null values. A regular unique constraint treats nulls as distinct by default; NULLS NOT DISTINCT changes that treatment. Composite uniqueness applies to the combination of columns. PostgreSQL constraints.

Design inference: A multi-tenant import might identify a record by (tenant_id, external_id). Using external_id alone could merge records belonging to different tenants. Allowing a nullable external ID could admit repeated rows that the application thought were duplicates. Define identity before deciding what an UPSERT should update.

A conflict target is also not a general exception handler. If an account has unique email and unique external ID, handling the external-ID conflict does not make an incompatible email assignment valid. Inspect the violated constraint before translating a unique-violation error into “already processed.” Otherwise, an unrelated business conflict can masquerade as a successful retry.

For batch ingestion, reduce multiple incoming versions of the same identity according to a documented ordering rule before issuing a single UPSERT. PostgreSQL does not allow one ON CONFLICT DO UPDATE statement to affect the same target row repeatedly. “Take the last input row” is unsafe unless input order itself has defined business meaning. INSERT conflict rules.

How should conditional updates and RETURNING be interpreted?

Original versioned-snapshot exercise: Assume a separate account_snapshots table with account_id as its primary key and non-null total and version columns. The source assigns a strictly increasing version per account.

INSERT INTO account_snapshots AS s (account_id, total, version)
VALUES (42, 120, 8)
ON CONFLICT (account_id)
DO UPDATE SET total = EXCLUDED.total,
              version = EXCLUDED.version
WHERE s.version < EXCLUDED.version
RETURNING account_id, total, version;

If stored version 9 arrives before incoming version 8, the older snapshot is skipped. Equal versions are also skipped. This policy assumes that equal versions cannot legitimately carry different payloads; otherwise the application should detect and report that contradiction.

Official fact: A conflicting row can be locked but not updated when the WHERE condition is false, and such a row is absent from RETURNING. An empty result therefore does not necessarily mean the account is missing. RETURNING behavior.

The response contract should distinguish “accepted new snapshot” from “ignored stale snapshot.” If the caller requires the current value, perform a deliberate read with an appropriate concurrency contract. Do not quietly label an empty result a database failure and retry forever.

There is another snapshot trap. At Read Committed, DO NOTHING can skip because of a conflicting transaction whose row was not visible to the insertion's initial snapshot. A same-statement fallback SELECT may therefore fail to find the row the caller expected. A later statement obtains a new snapshot; concurrent deletion still needs its own handling. Read Committed behavior.

How do you make retries safe after an uncertain commit?

Original recovery design: Give each logical operation a stable request ID. In one database transaction, claim that ID in a request table, apply the delta only for a newly claimed request, and store the result. A duplicate checks a request fingerprint before returning the saved outcome. Retrying with a new ID would describe a new operation.

The important atomic boundary is the request record plus the mutation. If the request is marked complete before the counter changes, a crash can suppress a required increment. If the counter changes before the request record commits separately, a crash can permit a duplicate increment. Keeping them in one transaction removes those two split-commit schedules.

Retention is part of the contract. Once the request record expires, the database may no longer recognize an old replay.

Official fact: Serialization failures require retrying the complete transaction, including the logic that chose its SQL and values. Deadlock failures may also warrant a retry. A unique violation is context-dependent, rather than universally retryable. Serialization failure handling.

For the example counter, reread and recompute after a known transaction abort if the algorithm depends on prior reads. For an ambiguous network outcome, first reconcile the stable request identity. Keep these recovery paths separate: “definitely aborted” and “possibly committed” are different facts.

What should a concurrency test assert?

Original test plan: Use two independent database sessions and an explicit pause before A commits. Starting two requests close together is insufficient evidence that their conflicting writes overlapped. Observe B waiting, then release A and inspect both responses and the committed row.

Run the replacement and additive variants against the same starting total of 100. Assert 110 for two stale replacements and 120 for two distinct increments. Next replay one increment with its original request ID: the deduplicated design should preserve 120 and return the recorded result. Replaying without deduplication should expose the extra increment.

Include a payload mismatch for a reused request ID, an older snapshot, and a conflict on the second unique constraint. These cases test the response contract as well as the final value. A successful HTTP response alone cannot prove that the intended mutation happened.

For contention, measure lock-wait time separately from statement execution after the wait. A correct single-row counter can still become a throughput bottleneck when every worker targets that row. Consider sharding only after stating how the resulting totals will be read and reconciled; it changes the design beyond the UPSERT statement.

Practice questions and a defensible answer sequence

Practice question	UPSERT follow-up
Implement an Idempotent Versioned Database Update	Defend version ordering, equal-version conflicts, and replay behavior.
Implement idempotent request handling with idempotency keys	Separate stable request identity from the resource primary key.
Defend a Project's Schema, Indexes, and Concurrency Model	Explain which schema invariant the unique index enforces.
Design concurrency-safe shared payment account API	Trace two account mutations and an uncertain response.
Write PostgreSQL updates with date filters	Practice expressing an update policy explicitly in SQL.

Build a concise answer in this order: identify the business key; define whether the payload is a replacement, delta, or snapshot; trace two writers; explain the returned result; then handle a retry after a lost response. Each step answers a different correctness question.

For a final self-check, change one assumption at a time. Let the first writer abort. Send the same request twice. Give equal versions different totals. Add a second unique constraint. A strong explanation predicts the changed result instead of repeating that UPSERT is atomic.

Continue with the PostgreSQL interview guide to connect these row-level decisions to MVCC, indexes, and transaction isolation.

Sources and Further Reading

• PostgreSQL INSERT and ON CONFLICT: conflict handling and returned rows.
• PostgreSQL index uniqueness checks: waiting and transaction visibility.
• PostgreSQL constraints: composite identity and null semantics.
• PostgreSQL transaction isolation: Read Committed snapshots.
• PostgreSQL serialization failure handling: transaction retry scope.
• PostgreSQL explicit locking: lock waits and deadlock behavior.