The First Row May Not Be the First Fact
TL;DR
An auto-incrementing primary key is excellent at giving a row an identity. It is usually a bad way to infer what happened first.
With concurrent transactions, one request can reserve ID 41, pause before committing, and another can reserve and commit ID 42. A reader can observe 42 first and then later observe 41. A cursor that records 42 as its high-water mark has now skipped a committed row.
Do not ask one number to answer several different questions. Decide whether a feature needs stable identity, commit visibility, per-object version order, business-event time, or a global append order. Then make that particular relationship part of the data model and its write path.
An ID can be allocated before its fact exists
Consider an ordinary PostgreSQL table:
create table invoices (
id bigint generated always as identity primary key,
account_id uuid not null,
status text not null
);
Two transactions run at about the same time:
transaction A transaction B
------------- -------------
insert → id = 41
insert → id = 42
commit
select id from invoices order by id
→ 42
commit
select id from invoices order by id
→ 41, 42
Nothing has gone wrong. A sequence hands out values without holding a transaction open behind every other writer. PostgreSQL documents that nextval values are not reclaimed after an abort, and that sequence state changes are not rolled back with the transaction. That is a concurrency trade-off, not a defect. PostgreSQL sequence functions
The uncomfortable consequence is that a primary-key number describes allocation, not the moment the row became a visible committed fact. id = 42 can be visible while id = 41 still belongs only to an uncommitted transaction. If A later rolls back, 41 may never become a row at all.
This matters most when an implementation quietly turns an ID into a stream position:
select *
from invoices
where id > :last_seen_id
order by id;
If a polling client sees 42, stores last_seen_id = 42, and A commits afterward, the row with ID 41 no longer qualifies. The cursor did not merely tolerate a gap. It made a claim about order that the write path never promised.
COMMIT is the boundary at which a transaction's changes become visible and durable to other sessions. An identity value being allocated earlier is not that boundary. PostgreSQL COMMIT documentation
“First” has several legitimate meanings
The error is not using an ID. The error is leaving the word first undefined.
| If the feature means… | It needs… | An autogenerated ID provides it? |
|---|---|---|
| “Is this the same row?” | Stable identity | Yes |
| “Which change belongs after the current version of this account?” | A per-account serialized revision | No |
| “Which business event happened earlier?” | A domain event time plus a rule for ties and corrections | No |
| “Which writes became visible to readers first?” | A committed-publication contract | No |
| “What is the next item in one append-only log?” | A log owner that serializes appends | No |
Some uses of ORDER BY id remain completely sensible. It can give an administrative table a deterministic presentation order. It can be a convenient implementation detail for a one-time scan inside a stable snapshot. It can partition an export after the exporter has deliberately established a safe boundary.
But those are weaker claims than “this row was created later,” “clients will see every row after this cursor,” or “this event supersedes the previous one.” As soon as the product relies on one of those claims, the order must be stated and protected.
The same distinction appears at an API boundary. A Signed Webhook Is Not a Freshness Guarantee argues that a delivery ID and an authenticated payload do not establish a resource's current version. A local database ID has the same limitation: identity is not an ordering contract just because it is numeric.
Put ordering where contention is acceptable
For a stateful aggregate, the usual requirement is not a global order across every invoice. It is a total order for one account, document, or workflow. Put a revision on that aggregate and allocate it in the same transaction that records the new fact.
begin;
update accounts
set next_invoice_revision = next_invoice_revision + 1
where id = :account_id
returning next_invoice_revision;
-- Suppose this returns 18.
insert into invoice_events (account_id, revision, kind, payload)
values (:account_id, 18, 'invoice_issued', :payload);
commit;
The account row is now the serialization point. Concurrent writers for the same account contend on that update, so only one can receive revision 18. A uniqueness constraint makes the promise durable rather than conventional:
alter table invoice_events
add constraint invoice_events_account_revision_key
unique (account_id, revision);
That design has a real cost: unrelated accounts proceed concurrently, but writers for one account may wait. That is exactly the cost the feature was already asking someone to pay. Pretending the global primary-key sequence supplied a per-account order only hides the contention until a reader observes the wrong result.
If writers need optimistic concurrency instead, expose the current revision in the API and require it on an update:
update invoices
set status = 'paid', revision = revision + 1
where id = :invoice_id
and revision = :expected_revision;
Zero rows updated means the caller wrote against an older state and must reread, merge, or deliberately reject the change. It is not a reason to overwrite with the numerically larger primary key.
PostgreSQL's Serializable isolation can protect broader invariants, but it is not a shortcut for skipping the contract. It can abort a transaction when concurrent work cannot be represented as a serial execution, and callers must retry the whole transaction after a serialization failure. PostgreSQL transaction isolation The application still has to say which rows participate in the invariant and how a retry preserves the original intent.
Cursors need a publication rule, not a hopeful high-water mark
The broken id > last_seen_id query is tempting because it is cheap and looks monotonic in a development database. It becomes dangerous when a consumer treats its returned maximum as evidence that no smaller ID can appear later.
There are several honest designs, and they are not interchangeable:
- A change feed can be owned by a database or log system that publishes a committed position and lets consumers resume from that position.
- A transactional outbox can record a business change and its publication obligation together; its relay then gives downstream consumers a separately defined delivery contract. When a Commit and an Email Disagree covers why that local atomic boundary is useful but does not make external delivery exactly once.
- A snapshot export can record a deliberate cutoff and reconcile changes after that cutoff, rather than treating an incidental maximum ID as a watermark.
- A small product may choose an explicit time overlap—re-read a recent window and deduplicate by row identity—when delayed visibility is acceptable and the duplicate policy is clear.
The last option is not elegant, but it can be correct. It trades extra reads and durable deduplication for a simpler ingestion path. What is not correct is calling it an ordered cursor while silently relying on sequence allocation to settle a commit-order question.
Do not replace one ambiguous clock with another
A common reaction is to sort by created_at instead. That only works after defining what the timestamp means.
Was it set by the client, application server, or database? Does it mean the business occurrence, the start of the write, or the commit's visibility? What happens when two rows have the same precision, a client clock is wrong, or a late correction describes an earlier real-world event?
Timestamps are often valuable data. They are not an automatic total order. Store the time that the domain actually needs, preserve a stable row or event identity for ties, and add a version or publication position when the consumer must decide what comes next.
This is especially important for agent and workflow tools. A tool that says “return items after ID 900” is communicating an ordering guarantee whether its authors intended one or not. If the back end cannot guarantee a complete continuation from that cursor, expose a different contract: a snapshot token, a committed change cursor, an operation status, or an explicitly lossy recent window. A precise limitation is much kinder to a caller than a numerical field that looks authoritative.
Test the late lower ID
Ordering bugs survive ordinary tests because one test process usually inserts and commits in sequence. Add the interleaving that production will eventually create:
Given: transaction A has inserted a row and received ID 41, but not committed
And: transaction B inserts ID 42 and commits
When: a cursor consumer records 42 as its high-water mark
And: transaction A commits
Then: the consumer must still receive row 41, or the API must document that it may not
Repeat it with a rollback in A, concurrent writers for one aggregate, and a reader that loses its response and retries. Those cases make the hidden question visible: is the number an identity, a version, or a delivery position?
Trade-offs
Explicit ordering is not free. Per-aggregate revisions create hot rows for exceptionally busy aggregates. A globally serialized log limits throughput or requires a specialized system. Change feeds and outboxes add retention, monitoring, and recovery work. Snapshot tokens can pin resources or complicate expiry. Overlap-and-deduplicate costs reads and storage.
For a screen that merely shows recent records, none of that may be worth it. A primary-key sort can be good enough when it is advertised as a display choice rather than a completeness guarantee.
My preference is to pay for the narrowest ordering rule that protects a real user-visible promise. Per-document collaboration needs a different rule from a financial ledger, and both differ from an operations dashboard. The data model should reveal that choice.
References
- PostgreSQL, Sequence Manipulation Functions
- PostgreSQL, COMMIT
- PostgreSQL, Transaction Isolation
- This repository, A Signed Webhook Is Not a Freshness Guarantee
- This repository, When a Commit and an Email Disagree
Final Take
An identity column answers which row is this? It does not automatically answer what happened first?
When that second question matters, name the order, put its serialization point where the write can enforce it, and test the late lower ID. A number that merely looks like a timeline is not a timeline.
Changelog
- 2026-11-11: Initial publication on sequence allocation versus visible and business order.