Inserting State Transitions in Postgres

In Modeling State Transitions in Postgres, we replaced a status column on users with an append-only user_statuses table. That design gives us full history that can be queried efficiently for the most common cases.

But it introduces an edge case worth addressing: what happens when two transactions try to change the same user’s status at the same time? With a column, the overwrite is harmless at the database level. With an append-only model, both records survive, so the problem needs to be addressed explicitly.

The race condition

Say user Alice is in pending status. Two admins change her status at the same time: one approves, the other denies.

If the timing is unlucky, both transactions read the current status before either commits, and both insert their own row:

Both succeed. The user_statuses table now looks like this:

Alice was approved and denied within a second. Both admins saw pending and acted on it independently, without knowledge of each other’s decision.

How a status column on users avoids this

With a status column on users (the more common design), both transactions would do:

-- Transaction A
UPDATE users SET status = 'approved' WHERE id = 1;

-- Transaction B
UPDATE users SET status = 'denied' WHERE id = 1;

Postgres serializes the updates, so the last writer wins. There’s only one column holding one value, so the database never reaches a contradictory state.

That said, a silent overwrite isn’t necessarily harmless in a real application. The second admin undoes the first one’s decision without knowing it happened. And if there are side effects tied to the transition, like sending emails or calling external APIs, both fire even though only one transition should have gone through.

Why append-only doesn’t get serialization for free

With a column, one value overwrites another. With inserts, both rows end up in the table, and the history contains a transition that should never have happened. There’s no overwrite to mask the problem. Either way, neither approach prevents concurrent transitions on its own.

Handling concurrency in an append-only model

We need a way to make the second transaction wait until the first finishes. SELECT ... FOR UPDATE does this by locking a row for the duration of the transaction. The parent users row is a natural choice since it already exists and is unique per user:

BEGIN;

-- Lock the user row until this transaction finishes
SELECT id FROM users WHERE id = 1 FOR UPDATE;

-- Read current status
-- Check if the transition is valid
-- Insert the new status

COMMIT;

With that, here’s what happens with two concurrent transactions:

Transaction B now sees approved as the current status, not pending. It can make an informed decision about what to do next.

This works under READ COMMITTED, the default transaction isolation level in Postgres. No configuration changes needed.

Adding a transition check

The lock serializes access, but it doesn’t reject invalid transitions. Transaction B still runs its insert unless we check:

BEGIN;

SELECT id FROM users WHERE id = 1 FOR UPDATE;

-- Read current status
SELECT status
FROM user_statuses
WHERE user_id = 1
ORDER BY created_at DESC, id DESC
LIMIT 1;
-- Returns: 'approved'

-- Is approved -> denied a valid transition?
-- No. Roll back.

ROLLBACK;

The transition rules are application logic. A simple map of allowed transitions is enough:

null     -> pending
pending  -> approved, denied
approved -> (terminal)
denied   -> pending

Transaction B reads approved, checks the map, and rolls back because approved to denied is not allowed. Alice stays approved.

The full sequence with both the lock and the check:

What about serializable isolation?

Postgres offers another approach: set the transaction isolation level to SERIALIZABLE. Instead of locking up front, both transactions proceed optimistically. At commit time, Postgres checks whether the result is consistent with some serial execution order. If not, it aborts one transaction with a serialization error.

This would also prevent the race condition above, but it has practical downsides:

False positives. Postgres tracks reads using predicate locks (SIRead locks). These start at tuple granularity but escalate to page or relation level to conserve memory. When that happens, two transactions operating on different users whose rows happen to live on the same heap page will conflict even though their data doesn’t overlap.

Retry logic. The aborted transaction gets an error, not a blocked wait. The application must catch it and retry, which adds complexity. With SELECT FOR UPDATE, the second transaction simply waits and then proceeds with fresh data.

Overhead. Tracking predicate locks across all serializable transactions has a memory and CPU cost. Postgres provides tuning parameters to control this, but it’s additional operational complexity.

SELECT FOR UPDATE is the simpler and more predictable choice for this problem. The cost is minimal: it’s a primary key lookup and a row-level lock held only for the duration of the transaction.

Wrap-up

Any real application that validates transitions or triggers side effects like emails and API calls needs a lock to serialize concurrent state transitions, regardless of whether you use a column or an append-only table. The column approach masks the problem by silently overwriting, but the side effects still fire twice.

With SELECT FOR UPDATE on the parent row, the second transaction blocks until the first commits, then reads the updated state. A transition check inside the locked section rejects invalid transitions. Since you need the lock either way, the append-only model doesn’t add complexity. It just makes the concurrency requirement explicit, and you get full history in return.

The transition check shown here uses a hardcoded map. In practice, this validation lives in your application code.