Atomic Upserts in PostgreSQL: Eliminate Race Conditions with INSERT … ON CONFLICT

The most common way developers handle duplicate records in a database is a check-then-act pattern: you run a SELECT to see if a row exists, and if it doesn't, you issue an INSERT. In a multi-user environment, this pattern is fundamentally broken. Two concurrent processes might check for the same record, both find it missing, and both attempt to insert it, resulting in a unique constraint violation error for one of the processes.

How INSERT … ON CONFLICT works

PostgreSQL added native upsert support in version 9.5 with the INSERT … ON CONFLICT clause. The statement attempts an insert; if a unique constraint violation occurs on the specified conflict target, the engine branches to either DO NOTHING or DO UPDATE you provide. Because the whole operation runs inside a single transaction, there is no window for another session to insert the same key.

Requirements

• The table must have a unique index or primary key on the conflict column(s).
• Without that index PostgreSQL falls back to a sequential scan to detect conflicts, which destroys performance as the table grows.

Worked example: counting user logins

Suppose we want to track how many times each user has logged in. The table looks like this:

CREATE TABLE user_login_stats (
    user_id INT PRIMARY KEY,
    login_count INT DEFAULT 0,
    last_login TIMESTAMP
);

The following statement inserts a new row for a user or increments the counter if the row already exists:

INSERT INTO user_login_stats (user_id, login_count, last_login)
VALUES (101, 1, CURRENT_TIMESTAMP)
ON CONFLICT (user_id)
DO UPDATE SET
    login_count = user_login_stats.login_count + 1,
    last_login = EXCLUDED.last_login;

The special alias EXCLUDED holds the values from the proposed insert, letting us reference them without repeating the VALUES list.

Performance considerations and limitations

Because the conflict check relies on the unique index, make sure that column is indexed. High concurrency on the same key can still cause serialization failures; the upsert avoids the duplicate‑key error but the application should be ready to retry the transaction if PostgreSQL aborts with a serialization error.

Complex expressions in the DO UPDATE clause increase planning time and lock duration, so keep them simple when throughput matters.

Verifying the implementation

1. Check the server version: SELECT version(); – you need 9.5 or later.
2. Run a test upsert and inspect the affected row count: INSERT INTO user_login_stats (user_id, login_count, last_login) VALUES (101, 1, CURRENT_TIMESTAMP) ON CONFLICT (user_id) DO UPDATE SET login_count = user_login_stats.login_count + 1, last_login = EXCLUDED.last_login RETURNING *;
3. After a batch of upserts, compare pg_stat_user_tables before and after to confirm that insert and update counts match expectations.

With these steps you can replace fragile application‑level checks with a reliable, atomic operation that scales safely.