RFC: v0.7 storage engine — segment storage + cursor allocator

[hardbyte]

Status

Design RFC, not implementation. This issue exists to collect input on the shape of the next storage engine before any code is written. Comments and pushback welcome.

Motivation

The 0.6 release blocker work in #169 confirmed (long-horizon benchmark, 2026-05-17) that even with ADR-012/023/025/026 in place, Awa's queue_storage degrades under a pinned MVCC horizon — 799 → 387 jobs/s at 800 offered/s in a 2h pinned phase. That is the same class of behaviour every per-row-state-machine Postgres queue has (River, Oban, pg-boss, Graphile, pgmq); pgque holds at 799/s by trading feature surface (no per-row retries, heartbeats, cancellation) for immunity.

The 0.6 plan is to land the live terminal counter (#290) and an open-ended HOT-update audit pass to take the visible hot-table scans out of the operator path. That treats the symptoms. The architectural fix lives one level deeper: the lifecycle table itself is the wrong shape for sustained, long-reader-exposed workloads.

The spike investigation captured this in docs/issue-169-storage-spike.md. The high-level direction:

• Append-only rotation segments for job lifecycle, not a mutable lifecycle table.
• Tiny per-segment claim ledger (the only mutable hot rows in the design).
• Cursor allocator moving readers forward across segments rather than scanning prunable history.

The spike concluded the storage shape is right but the claim allocator is the load-bearing piece — the design has to start there, not at the segment format.

Scope of this RFC

What we want input on:

1. Claim allocator design. What is the simplest allocator that gives SKIP LOCKED-equivalent fairness, supports per-row retries / heartbeats / cancellation, and does NOT degrade under a pinned MVCC horizon? Is it a single advisory-locked cursor table? A per-shard cursor with hash-routed jobs? Something else?
2. Receipt plane integration. ADR-023's ring partitioning + ADR-026's narrow terminal history are good ideas that should survive the redesign. How do they compose with rotation segments?
3. Migration story. Awa just did a 0.5→0.6 canonical→queue_storage migration. A 0.6→0.7 segment-storage migration is a real cost. What does the cutover shape look like — staged like 0.5.x→0.6, or a hard cutoff?
4. Comparable designs. What can we learn from FoundationDB's record layer, ScyllaDB's commit log, or any append-only segment design in the queue space? The pgque design is close in spirit to what we want; what stops it from supporting per-row retries / heartbeats?
5. TLA+ coverage. Awa's existing TLA+ models check queue_storage's lane identity, receipt claims, and terminal rollups. What needs to be re-modelled before this design ships?

What is out of scope for the RFC:

• Code. No PRs against this issue until the design is settled.
• Benchmarks of unimplemented designs. The acceptance bar will be a fresh long_horizon run against an actual implementation.
• Storage migration tooling specifics — that comes after the design.

Constraints

• Postgres-only (ADR-001). No extensions Awa doesn't already require.
• PG14+ floor, PG18 supported.
• Public API compatibility with v0.6 where feasible — the storage swap should be operator-visible (migration step) but not handler-visible.
• All ADR-029 transactional follow-up semantics must compose with the new design.

How to contribute

Comments on this issue; pull requests against docs/adr/ for a draft ADR-030 once a direction has consensus. Reference the spike doc and the long-horizon benchmark evidence at #169 when arguing for or against a direction.

Related

• Validate MVCC / dead-tuple behaviour under sustained load #169 — the 0.6 release-blocker investigation that produced this design pressure
• perf(queue_storage): live terminal counter for queue_counts_exact #290 — the 0.6 stable gate for the live terminal counter; orthogonal to this RFC
• 0.6 release readiness: align TLA+ models, docs, and queue-storage replacement gates #197 — 0.6 release readiness tracker
• docs/issue-169-storage-spike.md — spike notes that originated this direction
• ADR-012, ADR-019, ADR-022, ADR-023, ADR-025, ADR-026, ADR-029 — the prior architectural decisions this RFC will need to compose with or supersede

[hardbyte]

Segment-engine validation plan after the benchmark spike

The segment-engine spike in hardbyte/postgresql-job-queue-benchmarking#32 is useful evidence, but it should be treated as a minimal-floor validation only. It shows that a single-queue append/rotate shape plus a sequence cursor can be pin-immune in the base event stream and fast in short bursts. It does not yet validate the full AWA contract: priorities, enqueue shards, retries, heartbeats, callback waits, unique/dedup, exact counts, transactional follow-up jobs, or migration/cutover.

Now that #355 removes the hot queue_claim_heads routing-cache writes, the segment work should focus on which remaining semantics can stay append-only, which require bounded mutable control rows, and which would reintroduce hot per-job mutation.

Surface	Likely storage class	Mutable state introduced	Pin risk	Validation needed
Immediate enqueue / ready payload	Append-only event segments plus small segment metadata	None on the event rows	Low if segment metadata is not touched per job	Commit ordering, sequence allocation, segment rotation under burst and sustained load
Claim allocator for (queue, priority, enqueue_shard)	Sequence/cursor allocation over indexed segment scan	Possibly bounded per-lane cursor metadata	High if implemented as per-claim UPDATE; low if sequence-backed or append-only	Fairness and starvation across queue, priority, and shard dimensions
Claim attempt evidence	Append-only claim batches / receipt plane	Bounded active-attempt metadata	Low if receipt rings remain append/truncate oriented	Compatibility with ADR-023 receipt IDs, stale-writer checks, and rescue cursors
Successful completion	Append-only closure / compact completion batches	Bounded folded rollup state	Low if folded only past MVCC horizon	Preservation of ADR-026 terminal history/count semantics
Retry, backoff, DLQ, cancel	Append closure/tombstone plus new ready/deferred fact	Risk if retry state mutates the original ready row	Medium until shape is fixed	Exact counts, retry visibility, delayed readiness, and prune interaction
Heartbeat / progress	Bounded active-lease or attempt-state control plane	Mutable rows proportional to active long-running attempts	Bounded, but still pin-visible	Long handlers with periodic heartbeat and stale rescue
Deadline and callback-timeout rescue	Deadline-indexed active claim/wait table, or append facts plus rescue cursors	Bounded active waits/claims	Bounded by active waits, not throughput	Timeout rescue races, callback resolution, and pruning after closure
Priority aging	Allocator policy over lane metadata	Should not rewrite ready rows	Low for MVCC if computed at claim time	Hot high-priority queue plus old low-priority jobs, with starvation checks
Unique / dedup keys	Small control table or append claim/release ledger	Potentially mutable per unique key	Workload-dependent; high if hot keys update/delete	Decide whether v0.7 segment engine supports this initially, then stress high-cardinality and hot-key cases
Exact queue/admin counts	Append deltas plus async rollups	Folded counters/rollups only	Low if folding defers under pinned horizons; high if hot counters update per job	Pinned readers with admin polling and exact count queries
Public/history/admin reads	Read-only views over retained history and rollups	None on hot path	Low if they do not scan prunable rings	Ensure long admin readers do not block event-segment prune
Transactional follow-up jobs	Ordinary enqueue facts written in the same transaction for worker/callback paths	Extra append write amplification	Low if no callback registry hot row becomes the bottleneck	Atomic follow-up enqueue with claim closure and callback resolution per ADR-029
Migration / cutover	Staged compatibility layer or opt-in engine	Transitional dual surfaces	High operational risk, not primarily MVCC risk	Separate ADR section before implementation: rollout, rollback, mixed workers, and schema compatibility

Design constraints I would carry into an ADR/prototype:

• No per-job UPDATE/DELETE on the ready event stream.
• Mutable state must be bounded by active attempts, active waits, or lane count, not by total throughput.
• Per-claim allocator state must not be a hot updated row. Prefer sequence-backed cursors or append-only allocator evidence.
• Prunable segment tables should not be normal public/admin read surfaces.
• Prune/truncate paths need a cold-slot proof, a short lock_timeout, retry behavior, and a recheck after acquiring locks.
• Exact counts should be derivable from append-only deltas plus folded rollups, with folding deferred while the MVCC horizon is pinned.
• Claim fairness is part of the storage design, not a later scheduler tweak.
• TLA+ coverage should include claim allocation, claim/closure/prune, rescue, transactional follow-up enqueue, and prune lock ordering.

Validation cells before an implementation PR:

1. Clean-host sustained ceiling: 3k/s and 5k/s, 128 workers, 60 minutes, with wait-event sampling.
2. Pinned sustained run: 3k/s, 128 workers, 60 minutes with an idle-in-transaction pin plus 10-minute recovery window.
3. Prune-off / rotate-only control to isolate whether sustained collapse comes from allocation, insert pressure, or TRUNCATE/lock contention.
4. Batched claim/ack prototype, because the spike still pays one connection per worker and one round trip per claim.
5. Fairness prototype over (queue, priority, enqueue_shard) rather than the spike's single queue.
6. Semantic stress cells for long heartbeat handlers, retries/backoff, callback waits, unique/dedup, exact counts, and long admin readers.
7. Migration/cutover sketch before code: opt-in storage engine, rollback story, mixed worker behavior, and how existing queue tables are left intact or bridged.

Recommendation: keep hardbyte/postgresql-job-queue-benchmarking#32 as feasibility evidence for the append-only ring plus cursor allocator floor. The next AWA step should be a design PR/ADR for this classification and the validation matrix, then a narrow prototype for batching, fairness, and prune behavior before attempting the full v0.7 engine.