PgQue · Design Brief

Partition KeysOrdered, parallel consumption — the log-native way

slug partition-keys version v0.4 (draft) Phase 1 converged 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

Phase 1 · converged, build now

  • Partition key on send()-sourced events
  • N independent slot consumers, stable affinity
  • Per-key order + single processor (G1, G2)
  • skip failure policy (default)

Phase 2 · follow-up / out

  • pause strict order — open mechanic (defer w/o retry-count)
  • 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 (Phase 1); pause = Phase 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

Three review rounds against the real engine. Round 2 verified G1's ev_id ordering and the G2 single-owner lock are real. Round 3 declared Phase 1 (skip) converged / implementation-ready and split pause out as a follow-up: withholding a blocked key's later event has an unsolved mechanic — a server-side drop would lose the event, and event_retry would wrongly count it toward DLQ — so strict pause needs a defer-without-retry-increment primitive that doesn't exist yet. It also corrected the security model: the filter hook is admin-only, so the consumer wrappers reach it by co-ownership with the installer, not by a role grant.

06Sprint plan

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

Phase 1 (S1–S2) is the converged, build-now slice. Phase 2 (S3, pause) is gated on two open items: a defer-without-retry-increment primitive (O1) and bounding the hot-blocked-key cost (O2). Phase 1 alone covers the per-tenant ordered, parallel consumption the motivating workload needs.