Inserting State Transitions in Postgres

# Inserting State Transitions in Postgres Source: [https://thoughtbot.com/blog/inserting-state-transitions-in-postgres](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres) In[Modeling State Transitions in Postgres](https://thoughtbot.com/blog/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](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#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: StepTransaction ATransaction B1`BEGIN`2Read current status:`pending`3`BEGIN`4Read current status:`pending`5Insert`approved`6`COMMIT`7Insert`denied`8`COMMIT`Both succeed\. The`user\_statuses`table now looks like this: iduser\_idstatuscreated\_at11`pending`2026\-07\-10 09:00:0021`approved`2026\-07\-15 11:00:0031`denied`2026\-07\-15 11:00:01Alice 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](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#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](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#why-append-only-doesn39t-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](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#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: StepTransaction ATransaction B1`BEGIN`2`SELECT \.\.\. FOR UPDATE`\(acquires lock\)3`BEGIN`4`SELECT \.\.\. FOR UPDATE`\(blocked\)5Read current status:`pending`6Insert`approved`7`COMMIT`\(releases lock\)8\(unblocked, acquires lock\)9Read 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](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED), the default[transaction isolation level](https://www.postgresql.org/docs/current/transaction-iso.html)in Postgres\. No configuration changes needed\. ## [Adding a transition check](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#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: StepTransaction ATransaction B1`BEGIN`2Lock user row3`BEGIN`4Lock user row \(blocked\)5Read status:`pending`6`pending \-\> approved`? Valid\. Insert\.7`COMMIT`8\(unblocked\)9Read status:`approved`10`approved \-\> denied`? Invalid\.11`ROLLBACK`## [What about serializable isolation?](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#what-about-serializable-isolation) Postgres offers another approach: set the transaction isolation level to[`SERIALIZABLE`](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-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](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE)\(SIRead locks\)\. These start at tuple granularity but[escalate to page or relation level](https://wiki.postgresql.org/wiki/Serializable)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](https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-TRANSACTION)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](https://thoughtbot.com/blog/inserting-state-transitions-in-postgres#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\.