History of the question
This ambiguity emerged with PostgreSQL 9.5's introduction of native UPSERT functionality via the ON CONFLICT clause. Prior to this release, developers implemented idempotent inserts using complex PL/pgSQL loops or error-prone application-side logic. The RETURNING clause has long been essential for retrieving UUIDs or serial IDs, but its interaction with the dual-path execution model of UPSERT—where the statement may result in either an INSERT or an UPDATE—created a subtle semantic gap that confuses even senior engineers regarding which row version is actually returned.
The problem
When an INSERT ... ON CONFLICT ... DO UPDATE statement encounters a unique violation, it pivots to updating the existing row. The RETURNING clause subsequently references the final persisted state of that row. However, if your application logic depends on values that were generated for the attempted insertion—such as created_at timestamps, default expressions, or application-computed values—the statement instead returns the pre-existing row's stale data. This silent substitution causes cache desynchronization, audit trail corruption, and subtle race conditions where downstream systems receive temporally inconsistent metadata.
The solution
The EXCLUDED pseudo-table provides a window into the proposed insertion values that triggered the conflict. By explicitly referencing EXCLUDED.column_name within your RETURNING clause or the UPDATE set list, you guarantee access to the intended new data regardless of which execution path was taken.
INSERT INTO user_sessions (user_id, login_count, last_seen, session_token) VALUES (1001, 1, NOW(), gen_random_uuid()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_sessions.login_count + 1, last_seen = EXCLUDED.last_seen, session_token = EXCLUDED.session_token RETURNING session_id, user_id, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type, session_token, EXCLUDED.last_seen AS intended_timestamp;
In this pattern, EXCLUDED.last_seen and EXCLUDED.session_token ensure the application receives the fresh values from the attempted insertion, even when the database performs an update instead.
Concurrent loyalty point accrual
A fintech platform processing high-frequency micro-transactions encountered phantom reward calculations. When two parallel requests attempted to credit points to the same user account simultaneously, the PostgreSQL database correctly maintained atomicity, but the Redis cache layer received stale updated_at timestamps from the RETURNING clause. This caused the cache to reject legitimate point increments as outdated, leading to revenue leakage and customer complaints about missing rewards.
Solution A: Distributed locking with Redis
The engineering team initially proposed acquiring distributed locks in Redis before executing the database transaction. This approach would serialize conflicting operations and guarantee sequential consistency. However, it introduced a single point of failure, added 12-18ms of network latency per request, and created complex deadlock scenarios when transactions aborted after acquiring locks. The operational overhead of lock management and potential for cascading failures made this architecture unsustainable at scale.
Solution B: Application-side read-modify-write
Another suggestion involved querying the record existence first with a SELECT, then deciding between INSERT or UPDATE in application code. While conceptually simple, this pattern fails catastrophically under concurrent load due to READ COMMITTED isolation allowing non-repeatable reads between the check and the write. Implementing SERIALIZABLE isolation to prevent race conditions would have caused excessive serialization failures and retry storms, while explicit table locks would have throttled throughput to unacceptable levels.
Solution C: Proper EXCLUDED utilization
The selected approach重构 the query to leverage EXCLUDED for all mutable values in the RETURNING clause. By referencing EXCLUDED.points and EXCLUDED.calculated_at, the application consistently received the intended metadata from the insertion attempt, regardless of whether the operation resulted in a new row or an update.
Chosen solution and result
The team implemented Solution C across the rewards microservice. This eliminated the cache inconsistency issues without adding network hops or compromising isolation levels. Point accrual accuracy improved to 99.99%, database CPU utilization dropped by 35% due to reduced query round-trips, and the system successfully handled Black Friday traffic spikes without manual intervention.
How does PostgreSQL determine which unique index to use for conflict detection when multiple indexes exist on a table?
PostgreSQL requires explicit arbiter specification in the ON CONFLICT clause. When you write ON CONFLICT (column_list), the planner selects the unique index whose indexed columns exactly match the provided list in order. If multiple indexes exist on identical columns, it chooses the one created first. For partial unique indexes (those with WHERE clauses) or expression indexes, you must use ON CONFLICT ON CONSTRAINT constraint_name syntax; otherwise, the engine throws an error stating it cannot infer the arbiter index. Candidates frequently assume the database automatically selects the "most selective" index or overlook that functional indexes require explicit constraint naming.
Why might an UPSERT statement silently lose updates when multiple transactions conflict on the same key under READ COMMITTED isolation?
This occurs due to the re-evaluation behavior of the UPDATE clause. When Transaction A inserts a row and commits, Transaction B—waiting on the row lock—re-executes its UPDATE predicate against the newly visible row. If the UPDATE logic uses absolute assignment (e.g., SET balance = 100) rather than relative arithmetic referencing EXCLUDED (e.g., SET balance = account.balance + EXCLUDED.amount), Transaction B overwrites Transaction A's changes entirely. Many candidates incorrectly assume that UPSERT implies automatic merging or accumulation, failing to recognize that the DO UPDATE clause requires explicit handling of EXCLUDED values to achieve idempotent accumulation semantics.
What is the precise difference between checking xmax = 0 versus xmax IS NULL to determine if an UPSERT performed an insert, and why does this distinction matter for HOT updates?
In PostgreSQL, xmax stores the transaction ID of the deleting or updating transaction. For newly inserted rows, xmax is initialized to 0, never NULL. Candidates often incorrectly check xmax IS NULL to detect inserts, which always returns false. The xmax = 0 check reliably identifies insertions versus updates. This distinction becomes critical with HOT (Heap Only Tuple) updates, where PostgreSQL optimizes performance by updating rows in-place on the same page without modifying indexes. While xmax correctly indicates the row was touched, understanding that 0 signifies "no previous updater" while non-zero indicates versioning prevents logical errors when calculating row generation numbers or implementing custom change data capture logic that must distinguish between births and mutations.