Your Postgres Get-or-Create Has a Race. Postgres 19's ON CONFLICT DO SELECT Closes It

• Book: Database Playbook
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

Two requests to tag an article postgres arrive a few milliseconds apart. Both look up the tag. Neither finds it. Both insert it. One gets an id back. The other gets SQLSTATE 23505, unique_violation, and your API returns a 500 for a request that did nothing wrong.

Get-or-create is the most boring query in your codebase, and the obvious ways to write it on PostgreSQL 18 are all wrong somewhere. Some race. Some return nothing. One writes to the table every time you read from it. On 12 February 2026, commit 88327092ff added INSERT ... ON CONFLICT DO SELECT, and it is in PostgreSQL 19 RC 1, released 15 October 2026. GA is planned for 19 November 2026, barring delays. Until then it is a release candidate, so don't plan a production migration around it yet.

Everything below uses the default READ COMMITTED isolation level and one table. Open two psql sessions, A and B, and follow along.

CREATE TABLE tags (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

SELECT, then INSERT: the race you write first

This is the version most code starts with. Look, and insert if missing.

-- Session A
BEGIN;
SELECT id FROM tags WHERE name = 'postgres';  -- no rows

-- Session B
BEGIN;
SELECT id FROM tags WHERE name = 'postgres';  -- no rows

-- Session A
INSERT INTO tags (name) VALUES ('postgres') RETURNING id;

-- Session B
INSERT INTO tags (name) VALUES ('postgres') RETURNING id;
-- blocks: A holds an uncommitted entry in the unique index

-- Session A
COMMIT;
-- Session B now fails with unique_violation (23505)

The BEGIN does not save you. A SELECT that finds nothing locks nothing, so the transaction has no row to protect. A 2020 write-up on concurrency-safe upserts reaches the same verdict: wrapping the read and the write in a transaction "is not going to help in any way here." The unique constraint keeps the data correct. Your code is what breaks.

DO NOTHING RETURNING goes quiet on conflict

ON CONFLICT has been around since 9.5 and fixes the error. It also takes away the row you wanted.

INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO NOTHING
RETURNING id;

When the tag is new, you get its id. When it already exists, you get zero rows. The PostgreSQL 18 INSERT docs are explicit: "Only rows that were successfully inserted or updated will be returned." A skipped row is neither. A 2020 pgsql-hackers thread that proposed a SELECT conflict action put it plainly: "in case of conflict no value is returned."

So you add a second statement, a SELECT by name. That is two round trips, and if a DELETE lands between them the SELECT comes back empty as well. Correct code ends up wrapping both in a retry loop.

DO UPDATE writes a row so it can read one

The common workaround is a no-op update, because DO UPDATE always returns a row.

INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
RETURNING id;

It works. It also turns every "get" into a write, and Postgres does not skip a write because the values match. The trigger functions docs describe the "normal behavior which always performs a physical row update regardless of whether or not the data has changed." Each of those writes costs you something:

• Every lookup leaves a dead tuple behind. "An UPDATE or DELETE of a row does not immediately remove the old version of the row," per the vacuuming docs, so VACUUM ends up cleaning after your reads.
• Changes to table and index files are logged to WAL first, so replicas and backups carry the no-op writes too. Unless the update qualifies as a heap-only tuple (HOT) update, the new row version also needs new index entries.
• With DO UPDATE, row-level BEFORE UPDATE triggers can run, and statement-level UPDATE triggers run whether or not any row was updated. An updated_at trigger now bumps the timestamp on every lookup.
• The INSERT docs note that "all rows will be locked when the ON CONFLICT DO UPDATE action is taken." Two transactions fetching the same hot tag queue behind each other until the first one commits.

Laurenz Albe's write-up at CYBERTEC lists the same two headline costs: a dead row for VACUUM and, without HOT, an update to every index "even though all data stay the same."

The CTE + UNION trick misses a row it waited for

The cleverer pre-19 answer does it in one statement: try the insert in a data-modifying CTE, and union in a lookup for the case where it was skipped.

WITH ins AS (
  INSERT INTO tags (name) VALUES ('mvcc')
  ON CONFLICT (name) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM tags WHERE name = 'mvcc'
LIMIT 1;

No write on the conflict path, one round trip. Now race it.

-- Session A
BEGIN;
INSERT INTO tags (name) VALUES ('mvcc');

-- Session B: run the CTE query above
-- blocks on A's uncommitted index entry

-- Session A
COMMIT;
-- Session B returns zero rows

Two documented rules combine here. The PostgreSQL 18 concurrency docs say that in READ COMMITTED, DO NOTHING "may have insertion not proceed for a row due to the outcome of another transaction whose effects are not visible to the INSERT snapshot." And the WITH docs say all sub-statements "are executed with the same snapshot." B's snapshot was taken before A committed. The insert waits for A, sees the conflict, and skips. The fallback SELECT reads from that same older snapshot, where the row does not exist yet. Both branches come back empty, and you are back to a retry loop.

Postgres 19's DO SELECT, and what to ship before GA

The new conflict action returns the existing row and writes nothing.

INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id;

Run the same two-session race against it. The PostgreSQL 19 concurrency docs say that if the conflict comes from a transaction not yet visible to the INSERT, "the command will wait for that transaction to commit or roll back, then return the conflicting row if it was committed (even though that row was not visible when the command started)." That is the exact case the CTE trick gets wrong. In READ COMMITTED, each row is "guaranteed to either insert or return the conflicting row."

Nothing is modified on the select path. The 19 INSERT docs say "the new values will be identical to the old values, since no modification takes place." So you get no new row version, no dead tuple for VACUUM, and no row-level UPDATE triggers. You also get no row lock, unless you ask for one:

INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT FOR UPDATE
RETURNING id, (old.id IS NULL) AS created;

FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE lock the conflicting row, for when you are about to modify it in the same transaction. The created column uses the old/new aliases in RETURNING that arrived in PostgreSQL 18. The 19 docs say old values can be non-NULL when the proposed row conflicts with an existing one. A freshly inserted row has no old version, so old.id IS NULL means this call created the tag.

The rules, from the same INSERT page:

• RETURNING is mandatory, and so is a conflict target such as (name).
• An optional WHERE filters which existing rows come back. With FOR UPDATE/SHARE, rows that fail the WHERE are still locked.
• Only unique indexes and NOT DEFERRABLE constraints can be arbiters. Exclusion constraints are not supported.
• FOR UPDATE/SHARE needs UPDATE privilege on at least one column.

Client libraries are starting to pick it up. Sequel 5.108.0, the Ruby toolkit, added :select and :select_where options to insert_conflict for PostgreSQL 19+.

As of today PostgreSQL 19 is a release candidate. The RC 1 announcement says it "will be mostly identical to the initial release," plans RC 2 for 12 November and GA for 19 November 2026. The release notes still read "Release date: 2026-??-??". Test against RC 1. Do not run it in production, and do not assume the feature is on your managed Postgres until your provider ships 19.

Until then, put the retry loop in a function. In PL/pgSQL, a VOLATILE function (the default) takes a fresh snapshot for each query, so the second pass of the loop sees the row a concurrent transaction just committed.

CREATE FUNCTION tag_id(p_name text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE
  v_id bigint;
BEGIN
  LOOP
    SELECT id INTO v_id FROM tags WHERE name = p_name;
    IF FOUND THEN RETURN v_id; END IF;

    INSERT INTO tags (name) VALUES (p_name)
    ON CONFLICT (name) DO NOTHING
    RETURNING id INTO v_id;
    IF FOUND THEN RETURN v_id; END IF;
  END LOOP;
END $$;

When you move to 19, the whole function collapses into the three-line DO SELECT statement at the start of this section.

---

If this was useful

Get-or-create is the kind of query that looks finished in review and fails under load, and the fix depends on how your store handles snapshots, locks and row versions. The Database Playbook is about that layer: choosing a store for the workload you have, and knowing which of its guarantees your code is leaning on. If this post had you rereading your upsert helpers, that is the book I wrote for it.

Comments

[Mehdi Mohseni]

It's worth testing on the RC: so far ON CONFLICT evaluates the identity default for the proposed row before the conflict check. On 18, three DO NOTHING inserts of 'postgres' followed by a new tag gave the new tag id 4, not 2. If DO SELECT keeps that, a hot get-or-create path burns an id on every lookup.