---
title: Inserting State Transitions in Postgres
teaser: 'An append-only status model gives you full history, but concurrent transitions
  can produce contradictory records. Here''s how to prevent that.

  '
tags: databases,postgresql,sql
author: Thiago Araújo Silva
published_on: 2026-08-24
---

In [Modeling State Transitions in Postgres][modeling-post], 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:

| Step | Transaction A                  | Transaction B                  |
|------|--------------------------------|--------------------------------|
| 1    | `BEGIN`                        |                                |
| 2    | Read current status: `pending` |                                |
| 3    |                                | `BEGIN`                        |
| 4    |                                | Read current status: `pending` |
| 5    | Insert `approved`              |                                |
| 6    | `COMMIT`                       |                                |
| 7    |                                | Insert `denied`                |
| 8    |                                | `COMMIT`                       |

Both succeed. The `user_statuses` table now looks like this:

| id | user_id | status     | created_at          |
|----|---------|------------|---------------------|
| 1  | 1       | `pending`  | 2026-07-10 09:00:00 |
| 2  | 1       | `approved` | 2026-07-15 11:00:00 |
| 3  | 1       | `denied`   | 2026-07-15 11:00:01 |

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:

```sql
-- 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.

<aside class="info">
  <p>When two transactions update the same row, Postgres
  <a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS">locks it</a>
  for the first one. The second blocks until the first commits,
  then overwrites the value. In our case, the outcome is the same
  regardless, so the lock isn't doing anything relevant.</p>
</aside>

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.

<aside class="info">
  <p>If you try to get the best of both worlds by adding an audit
  log alongside the <code>users.status</code> column, the row
  itself ends up in the
  right state (last writer wins), but the log records both
  transitions as starting from <code>pending</code>. The log's conflicting
  history unmasks the problem the column silently hid. The log is
  append-only, so concurrent inserts each create their own row
  rather than competing for the same one.</p>
</aside>

## 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:

```sql
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;
```

<aside class="info">
  <p>This technique works just as well with a
  <code>users.status</code> column. If
  your transitions trigger side effects, you need the same lock
  regardless of how you store state.</p>
</aside>

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

| Step | Transaction A                           | Transaction B                     |
|------|-----------------------------------------|-----------------------------------|
| 1    | `BEGIN`                                 |                                   |
| 2    | `SELECT ... FOR UPDATE` (acquires lock) |                                   |
| 3    |                                         | `BEGIN`                           |
| 4    |                                         | `SELECT ... FOR UPDATE` (blocked) |
| 5    | Read current status: `pending`          |                                   |
| 6    | Insert `approved`                       |                                   |
| 7    | `COMMIT` (releases lock)                |                                   |
| 8    |                                         | (unblocked, acquires lock)        |
| 9    |                                         | Read current status: `approved`   |
| 10   |                                         | ...                               |

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][read-committed], the default
[transaction isolation level][isolation-levels] 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:

```sql
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:

| Step | Transaction A                         | Transaction B                  |
|------|---------------------------------------|--------------------------------|
| 1    | `BEGIN`                               |                                |
| 2    | Lock user row                         |                                |
| 3    |                                       | `BEGIN`                        |
| 4    |                                       | Lock user row (blocked)        |
| 5    | Read status: `pending`                |                                |
| 6    | `pending -> approved`? Valid. Insert. |                                |
| 7    | `COMMIT`                              |                                |
| 8    |                                       | (unblocked)                    |
| 9    |                                       | Read status: `approved`        |
| 10   |                                       | `approved -> denied`? Invalid. |
| 11   |                                       | `ROLLBACK`                     |

## What about serializable isolation?

Postgres offers another approach: set the transaction isolation
level to [`SERIALIZABLE`][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][predicate-locks] (SIRead locks). These start at
tuple granularity but [escalate to page or relation level][lock-escalation]
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][pred-lock-tuning] 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.

[modeling-post]: https://thoughtbot.com/blog/modeling-state-transitions-in-postgres
[read-committed]: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED
[isolation-levels]: https://www.postgresql.org/docs/current/transaction-iso.html
[serializable]: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE
[predicate-locks]: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE
[lock-escalation]: https://wiki.postgresql.org/wiki/Serializable
[pred-lock-tuning]: https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-TRANSACTION