How YugabyteDB’s UPSERT Works Under the Hood and When to Be Careful

Concrete Problem

When building a distributed PostgreSQL‑compatible database, developers often need a safe way to insert or update a row without racing with concurrent writers. In pure PostgreSQL this is handled by the INSERT … ON CONFLICT clause (UPSERT). The question is: does YugabyteDB’s YSQL engine preserve the same semantics, and what are the hidden costs?

Thesis

YugabyteDB implements UPSERT as a single Paxos proposal per key, giving you the same ACID guarantees as PostgreSQL while keeping the distributed transaction overhead low. However, high contention and large batch UPSERTs can still hurt performance, so you need to understand the trade‑offs.

How UPSERT Is Implemented in YSQL

• YSQL accepts the INSERT … ON CONFLICT syntax exactly as PostgreSQL does. The clause can reference a unique or exclusion constraint.
• When a UPSERT is executed, the coordinator node builds a Paxos proposal for the affected key. The proposal contains the entire insert or update operation.
• All replicas holding that key run the proposal in a two‑phase commit: first, they agree on the value; second, they commit it to the Write‑Ahead Log (WAL).
• Because the proposal is atomic per key, concurrent UPSERTs on the same key are serialized without a traditional lock‑table, avoiding deadlocks.

Practical Example

Below is a minimal example that shows how to upsert a user profile based on a unique email address. Replace {yugabyte-host} and {port} with your cluster’s details.

# Connect to YSQL
ysqlsh -h {yugabyte-host} -p {port} -U yugabyte

# Create table with a unique constraint
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email TEXT NOT NULL,
  last_seen TIMESTAMP,
  UNIQUE (email)
);

# Upsert: insert or update the last_seen column
INSERT INTO users(id, email, last_seen)
VALUES (1, '[contact removed]', NOW())
ON CONFLICT (email) DO UPDATE
SET last_seen = EXCLUDED.last_seen;

After running the statement twice, a simple SELECT COUNT(*) FROM users; will return 1, confirming that the row was updated rather than duplicated.

Verifying Correctness

1. Single‑transaction test: Run the UPSERT in the YSQL CLI and immediately query SELECT * FROM users WHERE email='[contact removed]'; to see the updated last_seen.
2. WAL inspection: Enable log_statement=all in postgresql.conf and look for a single WAL entry for the key. The entry should include the Paxos proposal identifier.
3. Stat tables: Query pg_stat_user_tables and pg_stat_activity to confirm that the UPSERT appears as one transaction and that no lock waits are recorded.
4. Concurrent load: Use a simple load generator that issues 1000 UPSERTs on the same email address. Measure average latency; a sudden spike indicates Paxos round‑trip contention.

Trade‑offs and Limitations

• High contention: When many nodes try to upsert the same key simultaneously, the Paxos algorithm may need multiple rounds, increasing latency. In extreme cases, the coordinator node can become a bottleneck.
• Large batch UPSERTs: Sending hundreds of UPSERTs in one request can overwhelm the coordinator, leading to back‑pressure. It is safer to batch at the application level (e.g., 50–100 rows per request).
• Non‑indexed constraints: If the ON CONFLICT clause references a column that is not part of a unique index, YugabyteDB will perform a full table scan to enforce the constraint, which is expensive.
• Version support: UPSERT is stable from YSQL 2.0 onward and available in all 3.x releases. Older versions may lack certain optimizations.

Actionable Takeaway

Use INSERT … ON CONFLICT for simple upsert scenarios where the conflict target is a unique index. Keep batch sizes moderate and monitor pg_stat_user_tables for high contention. If you anticipate heavy contention on a single key, consider sharding the key space or redesigning the application logic to reduce simultaneous writes.

Conclusion

YugabyteDB gives you PostgreSQL‑compatible UPSERT with distributed‑transaction guarantees, but the underlying Paxos coordination introduces subtle performance considerations. By following the verification steps above and watching for contention, you can confidently use this feature in production workloads.