--- title: Inserting State Transitions in Postgres | daily.dev description: An append-only state transitions table in Postgres avoids the silent overwrite problem of a status column, but introduces a new risk: two concurrent... canonical: https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Inserting State Transitions in Postgres | daily.dev og:description: An append-only state transitions table in Postgres avoids the silent overwrite problem of a status column, but introduces a new risk: two concurrent... og:url: https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk og:image: https://api.daily.dev/og/posts/n9li6cthk.png og:image:alt: Inserting State Transitions in Postgres og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Inserting State Transitions in Postgres **[thoughbot](https://daily.dev/sources/thoughbot)** · 6 min read · 3 upvotes · 0 comments ## Summary An append-only state transitions table in Postgres avoids the silent overwrite problem of a status column, but introduces a new risk: two concurrent transactions can both read the same prior status and insert contradictory rows. The fix is to use SELECT ... FOR UPDATE to lock the parent user row, forcing the second transaction to wait and read fresh state before validating the transition against an allowed-transitions map. SERIALIZABLE isolation is presented as an alternative but rejected due to false positives from predicate lock escalation, required retry logic, and added overhead. SELECT FOR UPDATE is recommended as the simpler, more predictable solution. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How do I prevent two concurrent transactions from inserting contradictory rows into an append-only status history table in Postgres? Lock the parent row with SELECT id FROM users WHERE id = ? FOR UPDATE before reading the current status and inserting a new transition. This forces a second concurrent transaction to block until the first commits, then it reads the updated status instead of stale data, letting it make an informed decision or reject an invalid transition. This works under Postgres's default READ COMMITTED isolation with no configuration changes. _daily.dev surfaces practical patterns like this for developers hardening concurrent database writes._ ### Why doesn't Postgres SERIALIZABLE isolation solve concurrent state transition race conditions cleanly? SERIALIZABLE works but has practical downsides: predicate locks (SIRead locks) start at tuple granularity but escalate to page or relation level to save memory, causing false positive conflicts between unrelated rows sharing a heap page. Aborted transactions return errors requiring application retry logic instead of simply blocking, and tracking predicate locks across transactions adds memory and CPU overhead versus a simple row lock. _Developers weighing isolation strategies for their database can compare trade-offs like these on daily.dev._ ### Does using a single status column instead of an append-only status history table avoid concurrency issues in Postgres? No, it only masks the problem rather than solving it. With a status column, Postgres serializes updates so the last writer silently wins, but this means the second admin's change overwrites the first without anyone knowing, and any side effects tied to the transition, like sending emails or calling external APIs, still fire for both attempts even though only one transition should have succeeded. _Teams choosing between schema designs for auditability can weigh these trade-offs on daily.dev._ ## Similar posts on daily.dev - [Modeling State Transitions in Postgres](https://daily.dev/posts/modeling-state-transitions-in-postgres-9lqvxgfog) · thoughbot · 0 upvotes · 0 comments - [Implementing Durable Workflows on Postgres Without an External Orchestrator](https://daily.dev/posts/implementing-durable-workflows-on-postgres-without-an-external-orchestrator-hn7fsiivi) · InfoQ · 0 upvotes · 0 comments - [Potential Consequences of Using Postgres as a Job Queue](https://daily.dev/posts/potential-consequences-of-using-postgres-as-a-job-queue-kcv0c9q1v) · Lobsters · 70 upvotes · 0 comments - [Row locks at the page level](https://daily.dev/posts/row-locks-at-the-page-level-o9spsmh7b) · Planet PostgreSQL · 0 upvotes · 0 comments --- Tags: [#database](https://daily.dev/tags/database), [#sql](https://daily.dev/tags/sql), [#postgresql](https://daily.dev/tags/postgresql) [View this post on daily.dev](https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Inserting State Transitions in Postgres","url":"https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk"},"datePublished":"2026-08-24T00:02:40.309Z","dateModified":"2026-09-13T20:59:35.791Z","description":"An append-only state transitions table in Postgres avoids the silent overwrite problem of a status column, but introduces a new risk: two concurrent...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/3815772322cad9f46dd76c1419756b30?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/3815772322cad9f46dd76c1419756b30?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"thoughbot","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"thoughbot","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/9fe12feb57f944a29ff63a79e350081a","url":"https://daily.dev/sources/thoughbot"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":3},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"database,sql,postgresql","timeRequired":"PT6M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"thoughbot","item":"https://daily.dev/sources/thoughbot"},{"@type":"ListItem","position":3,"name":"Inserting State Transitions in Postgres"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/inserting-state-transitions-in-postgres-n9li6cthk#faq","mainEntity":[{"@type":"Question","name":"How do I prevent two concurrent transactions from inserting contradictory rows into an append-only status history table in Postgres?","acceptedAnswer":{"@type":"Answer","text":"Lock the parent row with SELECT id FROM users WHERE id = ? FOR UPDATE before reading the current status and inserting a new transition. This forces a second concurrent transaction to block until the first commits, then it reads the updated status instead of stale data, letting it make an informed decision or reject an invalid transition. This works under Postgres's default READ COMMITTED isolation with no configuration changes. daily.dev surfaces practical patterns like this for developers hardening concurrent database writes."}},{"@type":"Question","name":"Why doesn't Postgres SERIALIZABLE isolation solve concurrent state transition race conditions cleanly?","acceptedAnswer":{"@type":"Answer","text":"SERIALIZABLE works but has practical downsides: predicate locks (SIRead locks) start at tuple granularity but escalate to page or relation level to save memory, causing false positive conflicts between unrelated rows sharing a heap page. Aborted transactions return errors requiring application retry logic instead of simply blocking, and tracking predicate locks across transactions adds memory and CPU overhead versus a simple row lock. Developers weighing isolation strategies for their database can compare trade-offs like these on daily.dev."}},{"@type":"Question","name":"Does using a single status column instead of an append-only status history table avoid concurrency issues in Postgres?","acceptedAnswer":{"@type":"Answer","text":"No, it only masks the problem rather than solving it. With a status column, Postgres serializes updates so the last writer silently wins, but this means the second admin's change overwrites the first without anyone knowing, and any side effects tied to the transition, like sending emails or calling external APIs, still fire for both attempts even though only one transition should have succeeded. Teams choosing between schema designs for auditability can weigh these trade-offs on daily.dev."}}]} ```