Gap-free per-stream sequence numbers in PostgreSQL

Keep one counter row per stream and increment it with UPDATE ... RETURNING inside the transaction that applies the change. The row lock serializes writers to that stream until commit, so sequence order equals commit order, and a rollback undoes the increment, so no number is skipped. A plain SEQUENCE gives neither guarantee.

CREATE TABLE streams (
  id   text   PRIMARY KEY,         -- e.g. 'vehicle:42'
  head bigint NOT NULL DEFAULT 0
);
CREATE TABLE stream_events (
  stream_id text   NOT NULL REFERENCES streams (id),
  seq       bigint NOT NULL,
  type      text   NOT NULL,
  data      jsonb  NOT NULL,
  PRIMARY KEY (stream_id, seq)     -- also the replay index
);

BEGIN;
-- ... apply the state change here ...
UPDATE streams SET head = head + 1 WHERE id = $1 RETURNING head; -- lock held until commit
INSERT INTO stream_events (stream_id, seq, type, data)
VALUES ($1, $2, $3, $4);         -- $2 is the head returned above
COMMIT;
-- only now publish the envelope (stream, epoch, seq, data)

#Why does it work?

• The UPDATE holds a row lock on the counter until commit or rollback. A second writer to the same stream waits, then increments the committed value. Under READ COMMITTED this is transparent; under REPEATABLE READ the waiting writer fails with SQLSTATE 40001 and must retry.
• The increment and the event row commit or roll back together, so a rollback never leaves a hole. Publish only after the commit: a message sent and then rolled back would have its number reused by different content.
• A SEQUENCE fails on both counts. nextval is not transactional, so rolled-back transactions leave gaps, and a client cannot tell a harmless gap from a lost message. Values are also handed out in call order, not commit order: transaction B can commit 6 while A, holding 5, is still open, and a reader that has seen 6 resumes past 5 for good.
• head is only meaningful inside one sequence space. A restore or migration that resets it must change the epoch in the envelope, so clients resync instead of dropping valid messages as duplicates.

#When should you not use it?

• On a very hot stream the counter row becomes a single-writer queue. Keep those transactions short.
• When the order is decided outside PostgreSQL (a Kafka partition, Redis Streams, JetStream), use that log's positions with a prev check on the client instead of a second counter.