Every application that signs up users, deduplicates incoming webhooks, or tags objects by an external ID ends up writing the same pattern: insert a row, and if one with that key already exists, fetch it instead. PostgreSQL has never had one statement for that. You wrote INSERT ... ON CONFLICT DO NOTHING RETURNING *, checked whether a row came back, and if it didn't, ran a second SELECT to get the row somebody else had already inserted. Two statements, two round trips, every time the row already existed.
PostgreSQL 19, currently at release candidate 1, adds ON CONFLICT DO SELECT. It folds that fallback SELECT into the INSERT itself. I pulled postgres:19rc1, built the old pattern and the new one side by side, and measured what actually changes.
The headline number
I created a table with a unique email column, pre-seeded one row, then ran 2,000 get-or-create calls against that same row through two code paths: the old race-safe pattern (INSERT ... ON CONFLICT DO NOTHING RETURNING *, falling back to a SELECT when no row came back) and the new one (INSERT ... ON CONFLICT (email) DO SELECT RETURNING *). Every call in this run hits the conflict, so the old pattern pays for both statements on every single call.
| run | old pattern (median, total) | new pattern (median, total) | round trips (old vs new) |
|---|---|---|---|
| 1 | 0.225 ms, 517 ms | 0.138 ms, 339 ms | 4,000 vs 2,000 |
| 2 | 0.263 ms, 650 ms | 0.118 ms, 286 ms | 4,000 vs 2,000 |
| 3 | 0.259 ms, 577 ms | 0.121 ms, 307 ms | 4,000 vs 2,000 |
The round-trip count is exact, not estimated: the old path always issues one INSERT plus, on every conflict, one more SELECT; the new path issues one statement regardless. Over 2,000 calls that's 4,000 statements against the server versus 2,000. Wall-clock time over the three runs dropped by 35% to 56%, all on a local Docker container talking over loopback, where the per-round-trip cost is already as small as it gets. On a connection with real network latency to the database, cutting a round trip in half matters more, not less, but I didn't have a way to measure that honestly here, so I'm reporting only what I actually timed.
That 100%-conflict run is the ceiling, not a realistic workload. I reran it with 10,000 calls at a 30% conflict rate, mixing fresh emails with ones already in the table, which is closer to what an actual signup or webhook-dedup endpoint sees:
| run | old pattern, total |
DO SELECT, total |
difference |
|---|---|---|---|
| 1 | 3,697 ms | 3,411 ms | 7.7% faster |
| 2 | 3,913 ms | 3,313 ms | 15.4% faster |
| 3 | 3,683 ms | 3,463 ms | 6.0% faster |
Single digits to mid-teens, not the 35-56% from the all-conflict case, which is exactly what you'd expect: the benefit scales with how often you actually hit a conflict, and most real tables don't see every call collide.
The number that argues against it
That result only holds because every call in the benchmark above hit a conflict. I reran the same 2,000-call benchmark with a brand-new, never-seen email on every call, so there's never a conflict and both patterns do exactly one INSERT:
| run | old pattern (median) | new pattern (median) |
|---|---|---|
| 1 | 0.386 ms | 0.415 ms |
| 2 | 0.435 ms | 0.481 ms |
| 3 | 0.561 ms | 0.519 ms |
No consistent winner. The new pattern was marginally slower in two of three runs and marginally faster in one, which is noise, not a trend. ON CONFLICT DO SELECT only pays off in proportion to how often your inserts actually conflict. If most of your get-or-create calls are genuinely creating, not getting, this feature doesn't help you, and it isn't advertised as helping in that case either — the saving is specifically the fallback SELECT you no longer run, and if you never ran one, there's nothing to remove.
Under concurrency
The point of a get-or-create pattern is usually to survive concurrent callers racing for the same key, so I tested that directly. Twenty threads, each on its own connection, all targeting the same email address. My first attempt at this gave the wrong answer, which is worth describing before the real one.
What I got wrong on the way
I first wrote the naive comparison as: SELECT for the row, and if it's missing, INSERT. I expected this to blow up under twenty concurrent threads and it didn't — zero errors, three times in a row. I nearly concluded that the textbook check-then-insert race was more theoretical than real on modern hardware, which would have been a real finding worth publishing and also wrong.
What actually happened is that twenty threads each doing connect, SELECT, then INSERT don't line up precisely enough on their own; by the time most of them reach the INSERT, an earlier thread has usually already committed, so the later ones correctly see the row on their SELECT and never attempt a duplicate insert. The race window exists, but my threads weren't actually walking through it at the same moment — the harness was accidentally serializing the part that was supposed to collide.
I fixed it with a threading.Barrier(20), so every thread runs its SELECT and then stops, and all twenty only proceed to the INSERT once every one of them has already seen "no row". That's what a real race looks like: many callers reading stale state before any of them has a chance to write.
With the barrier in place, three runs:
naive check-then-insert: 20 concurrent clients, 19 errors, 1 row(s) in table
ON CONFLICT DO SELECT : 20 concurrent clients, 0 errors, 1 row(s) in table
Identical on all three runs: 19 of 20 naive inserts failed with
error: duplicate key value violates unique constraint "race_users_email_key"
DETAIL: Key (email)=(contested@example.com) already exists.
while DO SELECT, forced through the exact same barrier, had zero errors across all 60 threads I ran through it. That's the actual value of the feature: it isn't really about shaving a round trip off a lucky case, it's a single statement that is correct under contention by construction, where the two-statement version needs you to remember to catch the unique-violation and retry.
What it costs
Nobody mentions this in the PostgreSQL 19 release notes I read, so I checked it myself. DO SELECT, like DO NOTHING, still attempts the insert before it discovers the conflict, and a serial or identity column's sequence advances on every attempt, successful or not.
I inserted one row (id 1), then ran five consecutive conflicting DO SELECT calls against it, then inserted a genuinely new row:
SELECT currval(pg_get_serial_sequence('seq_test','id')) AS sequence_after_5_conflicts;
-- 6
INSERT INTO seq_test (email) VALUES ('b@example.com') RETURNING id;
-- 7
Five conflicting calls burned five sequence values, so the next real row got id 7 instead of id 2. If you use bigserial this is irrelevant; if you use plain serial (a 32-bit int) on a table that gets hit by get-or-create traffic from many concurrent callers, this is the same gap-filling behaviour ON CONFLICT DO NOTHING has always had, now also true of DO SELECT, and worth knowing before you assume your IDs are dense.
A documented claim, checked two ways
The release notes say that on the DO SELECT path "the new values will be identical to the old values, since no modification takes place." I checked this both as a storage-level claim and a trigger-level one, and got two different answers.
At the storage level it holds exactly. I reset pg_stat_user_tables, ran three conflicting DO SELECT calls, and both n_tup_ins and n_tup_upd stayed at zero. No tuple was written.
At the trigger level it doesn't hold the way I expected. I attached a BEFORE INSERT trigger that logs its own firing, then ran a conflicting DO SELECT call:
NOTICE: trigger fired: op=INSERT, email=t@example.com
id | hits
----+------
1 | 0
The trigger fired, every time, including on calls that resolved entirely through the conflict path. PostgreSQL still attempts the insert before it knows there's a conflict, and BEFORE INSERT triggers run during that attempt regardless of how it's resolved. If you have a BEFORE INSERT trigger doing anything with a side effect — writing to an audit table, calling an external check, incrementing a counter by hand instead of through the heap — it runs on every DO SELECT call that reaches your table, not only the ones that actually insert a row. "No modification takes place" is true of the row; it isn't true of your trigger code.
FOR UPDATE really locks
DO SELECT supports FOR UPDATE/FOR SHARE, documented to lock the conflicting row like a normal SELECT ... FOR UPDATE would. I had one connection grab that lock and hold it open for 1.5 seconds before committing, and a second connection try to grab the same lock 0.3 seconds after the first:
holder_got_lock_at: 0.0009s
waiter_blocked_for: 1.2013s
1.5 seconds of hold time minus 0.3 seconds of head start is 1.2 seconds, which is exactly how long the second connection blocked. The lock is real, not advisory in name only.
What it refuses
Three error cases, with the exact messages PostgreSQL 19rc1 gave me:
-- no conflict target
ERROR: ON CONFLICT DO SELECT requires inference specification or constraint name
HINT: For example, ON CONFLICT (column_name).
-- conflict target with no RETURNING
ERROR: ON CONFLICT DO SELECT requires a RETURNING clause
-- conflict target on a column with no matching unique index
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
The optional WHERE clause worked exactly as the documentation's own example describes: inserting a conflicting row with a different name returned the existing row; inserting again with the same name returned zero rows and no error, rather than failing.
Watching it in production
EXPLAIN (ANALYZE) names the conflict path directly, which means you can tell a DO SELECT resolution apart from a real insert without touching application code:
Insert on trig_test (actual time=0.866..0.871 rows=1.00 loops=1)
Conflict Resolution: SELECT
Conflict Arbiter Indexes: trig_test_email_key
Tuples Inserted: 0
Conflicting Tuples: 1
-> Result (actual time=0.044..0.045 rows=1.00 loops=1)
Trigger t_ins: time=0.245 calls=1
Conflicting Tuples and Tuples Inserted give you, per statement, exactly the split I measured with pg_stat_user_tables above, and the per-trigger timing line confirms the trigger cost is real and attributable, not hidden inside the insert.
Run it yourself
docker run -d --name pg19 -e POSTGRES_PASSWORD=postgres -p 55432:5432 postgres:19rc1
sleep 5
docker exec pg19 psql -U postgres -c \
"CREATE TABLE users (id serial primary key, email text unique not null);"
docker exec pg19 psql -U postgres -c \
"INSERT INTO users (email) VALUES ('a@example.com');"
# get-or-create in one statement
docker exec pg19 psql -U postgres -c \
"INSERT INTO users (email) VALUES ('a@example.com')
ON CONFLICT (email) DO SELECT RETURNING *;"
The round-trip benchmark itself, with psycopg2:
import psycopg2, time
DSN = dict(host='localhost', port=55432, user='postgres', password='postgres', dbname='postgres')
conn = psycopg2.connect(**DSN); conn.autocommit = True
cur = conn.cursor()
t0 = time.perf_counter()
cur.execute(
"INSERT INTO users (email) VALUES ('a@example.com') "
"ON CONFLICT (email) DO SELECT RETURNING id, email")
row = cur.fetchone()
print(time.perf_counter() - t0, row)
If your code has a get-or-create pattern that currently does an INSERT ... ON CONFLICT DO NOTHING followed by a conditional SELECT, and you're already on PostgreSQL 19 or planning the upgrade, replacing it with DO SELECT removes a round trip on every call that actually conflicts, for free, and makes the whole thing correct under concurrent callers without you writing a retry loop. Check first whether any BEFORE INSERT trigger on that table does something you don't want repeated on every conflict, since it will run regardless, and check your sequence usage if the table is serial-keyed and gets heavy contention.
Top comments (0)