perf(queue_storage): live terminal counter for queue_counts_exact

[hardbyte]

Context

PR #289 added QueueStorage::queue_counts_fast — an index-only depth probe for high-cadence pollers (admin dashboards, depth-target producers, soak observability). It is not a replacement for queue_counts_exact: it under-counts completed while the live done_entries segment hasn't rolled up, and the exact path is still what admin tooling reaches for when the live terminal segment matters.

queue_counts_exact itself stays slow at deep backlog. Two CTEs dominate:

• live_terminal: count(*) FROM done_entries WHERE queue = $1. The per-slot done_entries child tables have a composite index leading with (queue, priority, enqueue_shard, lane_seq), so a queue-only filter can in principle index-only-scan — but that still has to count every matching terminal row, and append-heavy recent rows aren't reliably index-only until vacuum marks pages all-visible.
• live_running's lease_claims × lease_claim_closures anti-join — separate problem, not in scope here.

A naive done_entries(queue) index would mostly duplicate the leading columns of the existing composite index. It might cut buffer reads, but it's still O(live terminal rows) per call.

The better shape is an exact rollup that makes queue_counts_exact O(queue slots × shards), not O(done rows).

Proposal

Add a live terminal counter alongside queue_terminal_rollups.pruned_completed_count, keyed naturally on the same dimensions the hot path already partitions on:

Counter	Key shape
queue_terminal_live_counts.completed	(ready_slot, queue, priority, enqueue_shard)

(Or (ready_slot, queue, priority) if enqueue_shard granularity isn't worth the extra rows.)

Write paths to update:

• insert_done_rows_tx — group by (ready_slot, queue, priority, enqueue_shard) and INSERT … ON CONFLICT … DO UPDATE SET completed = completed + EXCLUDED.completed once per group per batch.
• Terminal row removal: retry_job_tx (terminal → retryable), move_failed_to_dlq (terminal → DLQ), any other terminal-deleting path. Decrement by the moved count.

Prune path: when a ready_slot is pruned, fold its live counters into queue_terminal_rollups.pruned_completed_count for that (queue, priority) (sum or max, matching the existing aggregator), then DELETE FROM queue_terminal_live_counts WHERE ready_slot = $slot.

Exact query becomes:

exact_completed =
    sum(queue_terminal_rollups.pruned_completed_count) +
    sum(queue_terminal_live_counts.completed)
WHERE queue = ANY($1)

Both are bounded by num_ready_slots × num_priority × num_enqueue_shards. No done_entries scan.

Risks

• Write-path contention: the new counter is hit by every insert_done_rows_tx batch. Keep it naturally sharded by (ready_slot, queue, priority, enqueue_shard) rather than a single hot (queue) row to avoid serialising completions.
• WAL / throughput regression: each batch grows by N upserts (one per group). Benchmark before merging. Worst-case skew is single-queue single-priority single-shard workloads where every batch hits the same row — measure that explicitly.
• Correctness on counted-removal paths: retry_job_tx and DLQ move have to decrement the correct (ready_slot, queue, priority, enqueue_shard) group, which means they need to read the row being moved before decrementing. Verify by hand against each terminal-removal call site.

Out of scope here

• The lease_claims × lease_claim_closures anti-join cost in live_running. Still slow at high open-claim counts in receipt-plane modes. Worth a separate ticket.
• Bench harness polling cadence (separate small change to switch the long-horizon depth poller off the 200 ms tight loop).
• The "could we just add done_entries(queue) and move on?" branch — worth measuring before discarding, but the bet is that it cuts I/O but not the row count and so doesn't pay back the index maintenance cost. PR perf(queue_storage): add queue_counts_fast for high-cadence pollers #289 ships the depth-probe API regardless.

Empirical first step (optional)

Before the denormalisation lands, a narrow regression bench against the current composite index — count(*) FROM done_entries_$slot WHERE queue = $1 per slot, summed in the harness — would calibrate how much of the gap a queue-only index could close. If that's already within target latency for admin UI, the denormalisation may be overkill. The expectation is that it isn't.

Refs: #289, ADR-019, ADR-026 (narrow terminal history).

Acceptance criteria

• queue_counts_exact.completed no longer scans done_entries — the
  query reads only queue_terminal_rollups.pruned_completed_count and
  the new queue_terminal_live_counts table, bounded by
  num_ready_slots × num_priority × num_enqueue_shards.
• Every code path that adds or removes a terminal row keeps the
  counters correct: completion (insert_done_rows_tx), retry-from-
  terminal (retry_job_tx), DLQ move (move_failed_to_dlq and the
  DLQ-on-fail completion branches), discard, and prune (folds slot
  live counts into pruned_completed_count then clears).
• Tests cover increment (completion), decrement (retry-from-terminal,
  DLQ move, discard), prune-fold (live + rollup converge), and
  recovery/rebuild from done_entries if such a path is added.
• Benchmark includes the skewed worst case — one queue, one priority,
  one enqueue shard, high completion rate — so the write-path
  contention on the resulting hot counter row is measured before
  merging. Compare to the current queue_counts_exact cost at the
  same workload to confirm the trade is net positive at the relevant
  backlog sizes.

[hardbyte]

Design decisions (2026-06-02)

After weighing in-row vs separate-table approaches, this issue lands as a separate queue_terminal_live_counts table keyed by (ready_slot, queue, priority, enqueue_shard).

Why a separate table, not in-row

An in-row live_completed_count column on queue_terminal_rollups or queue_lanes would recreate the hot-counter shape v016 removed: every completion batch for one hot queue serializes on one row, creates HOT-update churn, and is vulnerable to pinned-xmin bloat. It also either keeps prune scanning done_entries_$slot to subtract pruned live rows, or loses the slot-local accounting needed to fold/delete cleanly.

The separate table matches the existing ring + shard identity and lets prune fold one slot's counters without scanning terminal rows.

Why keep enqueue_shard in the key

Dropping it saves a few counter rows but concentrates completion writes exactly where ADR-025 was trying to spread row locks. The cardinality is bounded — queue_slot_count * priorities * enqueue_shards — and the default is still one shard, so the worst case is one queue, one priority, one enqueue shard. That worst case is the bench gate for this PR.

If that worst case regresses badly under measurement, the next lever is a synthetic counter stripe. Not added until the simple shard-aligned design fails measurement.

Write amplification

Acceptable if accounted per group per batch, not per terminal row. With AWA_COMPLETION_BATCH_SIZE=512, a skewed full batch adds one counter upsert for 512 terminal inserts. The dangerous case isn't bytes per job — it's row-lock serialization when flushes are small or many fleet-wide completion flushers multiply.

Naming

live_terminal_count (not completed). The existing queue_counts_exact.completed is a misnomer — it does count(*) FROM done_entries WHERE queue = ANY(...), so failed and cancelled terminal rows contribute too. A test even asserts a failed done row makes .completed == 1 before discard. We carry forward the same semantics under an honest name.

Implementation surfaces

• Increment in insert_done_rows_tx.
• Also increment in the fused receipt fast path that inserts directly into done_entries instead of going through that helper (queue_storage.rs near line 6223).
• Decrement on every terminal delete path: retry-from-terminal, single/bulk DLQ move, discard, SQL compatibility delete functions if DELETE FROM awa.jobs can still route to queue storage.
• Prune folds queue_terminal_live_counts WHERE ready_slot = $slot into queue_terminal_rollups, then deletes that slot's live counter rows in the same transaction as the child-table truncate.
• Migration/backfill: build live counters from existing done_entries before switching queue_counts_exact to the counter path.
• Update the correctness mapping/docs — they currently say completed totals are not maintained as hot counters.

Counter table schema setup

• PK only, no secondary indexes
• fillfactor=50
• Aggressive autovacuum settings (per-table override)
• Benchmark records completion rate, WAL/job, dead tuples, tuple-lock waits

Explicit non-goals for this PR

• The exact path's lease_claims × lease_claim_closures anti-join for running counts. Stays scan-based.
• Queue-storage health metrics scanning done_entries for failed terminal rows.

Keeping #290 to one clean design change. Those other counts can move to live counters later behind separate issues if measurement warrants it.

Acceptance bar (as the #169 release decision gate)

• perf(queue_storage): live terminal counter for queue_counts_exact #290 implementation merged
• Bench validates the single-queue / single-priority / single-shard worst case
• Fresh long_horizon re-run on post-perf(queue_storage): live terminal counter for queue_counts_exact #290 main shows measurable pinned-horizon improvement (does not have to match pgque)