PgQue · Design Brief

Partition KeysOrdered, parallel consumption — the log-native way

slug partition-keys version v0.3 (draft) review rounds 1+2 applied engine untouched

Within one queue, events that share a partition key are consumed in order by a single worker at a time; events with different keys are consumed in parallel. Order within a key, parallelism across keys — Kafka's partition model, achieved by routing, with no per-event locks and no new mutable state.

01The problem

PgQue is an ordered, immutable log, not a job queue. The motivating workload (a multi-tenant storage service evaluating PgQue to replace pg-boss) emits millions of file-lifecycle events — FileCreated, FileDeleted, FileOverwritten. They must be processed in order per tenant, but order across tenants does not matter. One in-order consumer can't keep up; naive multi-worker consumption breaks per-tenant order.

02The guarantee

G1

Per-key affinity + FIFO. Every event of key K maps to one slot hashtextextended(K,0) % N; within it, non-retried events of K arrive in ev_id order, never to another slot.

G2

Single processor per key. At most one worker holds an unacked event for K at any instant.

G3

Failure boundary. pause: no later K event until the failed one is acked or dead-lettered. skip (v0.1 default): at-least-once, order not held after a failure.

03Architecture

producers send(queue, type, payload, partition_key => K) → ev_extra1 engine · sacred — UNCHANGED append-only tables · global ev_id / ev_txid order next_batch · get_batch_cursor(extra_where) · rotation — no edits to batch_event_sql full stream full stream full stream slot 0 · sub#0/N own cursor extra_where: h%N==0 keys in ev_id order one worker slot 1 · sub#1/N own cursor extra_where: h%N==1 keys in ev_id order one worker slot N-1 · sub#k/N own cursor extra_where: h%N==N-1 keys in ev_id order one worker each slot = independent subscription · own cursor → no data loss one key → one slot → per-key order · distinct keys → parallel cost: N× read amplification (each slot scans the full stream, server-side hash-filtered)
Slots are independent subscriptions filtering via the existing extra_where hook. The PgQ engine is not modified.

04Scope

In · v0.1

  • Partition key on send()-sourced events
  • Stable hashtextextended(key,0) % N affinity
  • Per-key order + single processor (G1, G2)
  • skip failure policy (default)

Deferred / out

  • pause policy — specified, ships v0.2
  • Producer idempotency / dedup window
  • Trigger-sourced queues · dynamic N
  • Hot-partition mitigation · read-amp optimization

05Key decisions

IDDecisionChoice (v0.2)
D1Where the key livesev_extra1, send()-sourced queues only
D2Failure policyskip default; pause ships v0.2
D3Slot count NFixed, persisted & enforced per (queue, consumer)
D4Assignment function(hashtextextended(key,0) % N + N) % N (stable, sign-safe)
D5State budgetHappy path & skip: none. pause: a compact blocked-key marker (per failing key, not per event)
D6Producer signaturesend(queue, type, payload, partition_key =>)
D7Slot & single-ownerslot = consumer "<c>#k/N"; receive lock

Round 1 re-grounded the mechanism (slots are independent subscriptions, not a coop overlay). Round 2 verified it against the engine: G1's ev_id ordering is real (order by 1, preserved through the filter) and the G2 single-owner lock is the tested #97 double-delivery guard. It also corrected the security trust boundary (the filter hook is admin-only, so the consumer wrappers are SECURITY DEFINER) and moved pause's blocked-set to a durable per-failing-key marker.

06Sprint plan

S1  producer + key plumbing S2  slot consumers (skip) S3  pause policy (v0.2) S4  docs + read-amp benchmark

Open for round 2: the exact retry_queue predicate that rebuilds a slot's blocked-key set after a crash (the crux of crash-safe pause), and whether N× read amplification at target throughput justifies a single-reader/dispatch optimization in v0.1.