PostgreSQL ON CONFLICT Upsert: Patterns, Performance, and Pitfalls

PostgreSQL's INSERT ... ON CONFLICT provides atomic upserts that remove race conditions from check‑then‑insert patterns. This post shows a worked event‑ingestion example, how to verify the result, and notes performance trade‑offs when conflict rates are high.

The Race Condition That Breaks Your Data

You're building a user profile service. Two requests arrive simultaneously: both try to create a profile for the same email address. Your application code does a SELECT to check existence, finds nothing, then runs INSERT. Both succeed. Now you have duplicate profiles for one email.

This is the classic check-then-act race condition. Application-level locks don't help across multiple app instances. Advisory locks add complexity. The database already has the right tool: INSERT ... ON CONFLICT, PostgreSQL's atomic upsert since version 9.5.

\n\n

Why ON CONFLICT Works Where Application Logic Fails

The clause executes as a single statement. PostgreSQL acquires the necessary index locks, checks the unique constraint, and either inserts or updates—all without releasing the lock between check and write. No window exists for a concurrent transaction to slip in.

Compare the two approaches:

\n\n\n\n\n\n\n\n\n

Approach	Race Window	Lock Scope	Round Trips
SELECT then INSERT	Between statements	Application or advisory	2+
ON CONFLICT	None	Index tuple (microseconds)	1

The index lock is held only for the duration of the index lookup and tuple write—typically microseconds. This is why upsert scales better than application-level serialization.

\n\n

Concrete Example: Idempotent Event Ingestion

Imagine an event pipeline where the same event ID may be delivered multiple times. You need exactly-once processing into an events table.

CREATE TABLE events (\n    event_id      uuid PRIMARY KEY,\n    payload       jsonb NOT NULL,\n    received_at   timestamptz NOT NULL DEFAULT now(),\n    processed_at  timestamptz\n);

The primary key on event_id is the conflict target. Here's the upsert:

INSERT INTO events (event_id, payload, received_at)\nVALUES ($1, $2, now())\nON CONFLICT (event_id) DO UPDATE SET\n    payload = EXCLUDED.payload,\n    received_at = LEAST(events.received_at, EXCLUDED.received_at);

Run this from your consumer. The EXCLUDED table reference holds the values that would have been inserted. LEAST preserves the earliest receipt timestamp. If the row exists, processed_at stays untouched—omitted columns keep their current values, not defaults.

To verify the behavior in psql:

-- First insert\nINSERT INTO events (event_id, payload) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', '{\"type\":\"click\"}');\n-- Returns: INSERT 0 1\n\n-- Duplicate event_id, different payload\nINSERT INTO events (event_id, payload) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', '{\"type\":\"view\"}')\nON CONFLICT (event_id) DO UPDATE SET payload = EXCLUDED.payload;\n-- Returns: INSERT 0 1 (command tag shows INSERT, but row was updated)\n\n-- Check result\nSELECT * FROM events WHERE event_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';\n-- payload is now {\"type\":\"view\"}, received_at unchanged

The command tag always reads INSERT regardless of whether an update occurred. Use RETURNING or a subsequent SELECT to distinguish.

\n\n

Where Performance Bends: High Conflict Rates

Under low contention, upsert costs nearly the same as a plain INSERT. But when conflict rates climb—say, 30% or more of attempts hit existing keys—index contention becomes measurable. Each conflict requires an exclusive lock on the index tuple, blocking other writers targeting the same key.

Symptoms appear as increased lock wait events in pg_stat_activity and rising write latency. Mitigations:

Batch upserts into fewer statements with VALUES (...), (...) to amortize lock acquisition.
If the workload is append-heavy with rare updates, consider ON CONFLICT DO NOTHING and handle updates separately.
Partition tables by time or tenant to spread index contention across separate indexes.

Expression indexes need explicit conflict targets. Given CREATE UNIQUE INDEX ON users (lower(email)), you must write ON CONFLICT (lower(email))—the bare column name won't match.

\n\n

Limitations to Keep in Mind

No MERGE equivalence: PostgreSQL 15 added MERGE, but it is not a drop-in replacement for all upsert cases.
ON CONFLICT cannot reference other tables in the DO UPDATE clause.
Triggers fire for both insert and update paths; TG_OP tells you which.
Logical replication applies upserts as separate insert or update operations on subscribers.

\n\n

Actionable Checklist

Identify every SELECT ... WHERE unique_col = $1 followed by INSERT in your codebase.
Verify a unique index or primary key exists on the conflict column(s).
Replace the pair with a single INSERT ... ON CONFLICT statement.
Add RETURNING if you need to know whether an insert or update occurred.
Monitor lock wait times and conflict-related statistics after deployment.

Start with the highest-contention code path. The reduction in round trips and eliminated race conditions usually pays off immediately.

PostgreSQL Upsert Patterns: When ON CONFLICT Beats Application-Level Logic

The Race Condition That Breaks Your Data

Why ON CONFLICT Works Where Application Logic Fails

Concrete Example: Idempotent Event Ingestion

Where Performance Bends: High Conflict Rates

Limitations to Keep in Mind

Actionable Checklist

0 replies

Your perspective belongs here.