PgQue · Design Brief
Two tiers of per-key consumption, matched to two real needs. Mutual exclusion (Tier A) — one worker per key at a time — reuses what PgQue already ships: cooperative consumers plus a per-key advisory lock, near-zero new engine code. Ordered per key (Tier B) — Kafka's partition model, order within a key — adds hash-routed slots and pays for strict order with read amplification and a fixed partition count. Pick the tier the workload needs.
PgQue is an ordered, immutable log, not a job queue. One in-order consumer can't keep up with millions of events; naive multi-worker consumption breaks per-key order. The motivating workload (a multi-tenant storage service evaluating PgQue to replace pg-boss) has two needs, not one — and they want different guarantees:
Migrations · mutual exclusion
Lifecycle events · ordered
FileCreated, FileDeleted, FileOverwritten — millionsBoth needs are "per-key", but the guarantee differs — so the cost differs, and so do the features. Tier B is the partition-keys feature (ordered slots). Tier A is a plain-queue recipe (mutual exclusion + producer idempotency) that uses no slots — it's the migration case, kept deliberately separate so partition keys don't drift toward a mutable job queue. "Tier A/B" below is just shorthand. Don't pay for ordering you don't need.
| Tier | Guarantee | Built on | Cost |
|---|---|---|---|
| A | Mutual exclusion — one worker per key (G2) | cooperative consumers (exist) + per-key pg_try_advisory_lock | ≈ none · no new tables · no N |
| B | Ordered per key — FIFO within a key (G1 + G2) | N hash-routed slot subscriptions via extra_where | N× read amplification · fixed N · slot assignment |
Cooperative consumers cannot provide Tier B: they split by tick window,
not key, so a key scatters across workers (round-1 finding A1, confirmed in
next_coop_batch). Tier A leans on exactly that throughput-spreading and
adds the per-key lock for serialization.
User stories · the acceptance contract
Both features share one user-story layer — the language the specs, this brief, and the acceptance suites all speak. US-12 is partition keys (ordered slots); US-13 is producer idempotency (TTL dedup on a plain queue). Each story maps to an executable check.
US-12 · partition keys
send(…, partition_key) → ev_extra1ev_id orderpartition_slot_status — owner pid + lag (R7)US-13 · producer idempotency
deduped=true:v2 not hidden by :v1maint() reaps expired dedup rows
Executable: tests/acceptance/us12_partition_keys.sql and
tests/acceptance/us13_producer_idempotency.sql; the two-session
facets of 12.4 / 12.5 live in tests/two_session_slot_claim.sh.
A recipe over existing primitives on a plain queue, not
new engine code — and a separate feature from partition keys. The
migration use case lives here: producer idempotency + consumer mutual exclusion.
It needs no slots, no fixed N, no ordering — the "key" is a lock key, not a
partition. Producer dedup is spec'd on its own in
blueprints/idempotency/SPEC.md (ships as Phase 1B); keeping migrations
out of the partition-keys feature stops it drifting toward a mutable job queue.
Mechanism
send(queue, type, payload, partition_key) → key into ev_extra1pg_try_advisory_lock(hashtextextended(key,0))Why ack-and-drop is safe
Idempotency: a complementary layer, not the same thing
The idempotency ask is a second layer, not a restatement of partition
keys. Consumer-side mutual exclusion (above) prevents duplicate
work: residual duplicates ack-drop, the idempotent handler runs once.
Producer-side dedup — a TTL window (the SQS/NATS model) —
additionally prevents duplicate inserts, keeping the log small when many
instances race to enqueue the same key. Use the consumer layer for correctness;
add the producer TTL window when insert volume matters. Measured side by side in
blueprints/partition-keys/repro/.
Key-scope footgun. The TTL key must encode the desired
effect, not just the entity. Key on migrate:tenant, ship
migration v1 then v2 inside the window, and v2 is silently suppressed as a
"duplicate" — a correctness bug, not a caveat. Use
migrate:tenant:version. Reproduced both ways in the repro
(--tier hazard).
When order is the point (lifecycle events). Within one queue, events sharing a partition key are consumed in order by a single worker at a time; different keys run in parallel — with no per-event locks and no new mutable state. This is where fixed N, read amplification, and slot assignment live — the inherent price of strict ordering. G2 is also Tier A's guarantee; Tier B adds 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.
Single processor per key. At most one worker holds an unacked event for K at any instant.
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.
Tier A · build now
send() partition key → ev_extra1Tier B · ordered, follow-up
skip policy; engine untouched; persisted Npause strict order — open mechanic, gated on O1/O2| ID | Tier | Decision | Choice (v0.7) |
|---|---|---|---|
| D1 | A·B | Where the key lives | ev_extra1, send()-sourced queues only |
| D6 | A·B | Producer signature | send(queue, type, payload, partition_key =>) |
| A1 | A | Tier A serialization | cooperative consumers + per-key pg_try_advisory_lock; contended + idempotent → ack-drop. No N, no assignment |
| A2 | A | Idempotency ask | two complementary layers — consumer mutual-exclusion (dup work) + optional producer TTL window (dup insert); not a substitute for partition keys |
| D8–D10 | B | Assignment, resize, no member table | claim not assign; core slot_lock_key + partition_slot_status view; epoch-gated online resize; no heartbeat/lease table (see §09 + SPEC) |
| D3 | B | Slot count N | Fixed, persisted & enforced per (queue, consumer) |
| D4 | B | Assignment function | (hashtextextended(key,0) % N + N) % N (stable, sign-safe) |
| D7 | B | Slot & single-owner | slot = consumer "<c>#k/N"; receive lock; claim via advisory/static |
| D2 | B | Failure policy | skip default; pause = follow-up |
| D5 | B | State budget | happy & skip: none. pause: compact blocked-key marker |
Three review rounds against the real engine (G1 ordering + G2 single-owner lock verified real), then reframed into two tiers after consumer Q&A: the mutual-exclusion need (migrations) is met by Tier A with almost no engine change, so it leads. pause strict failure ordering stays a Tier-B follow-up — withholding a blocked key's later event needs a defer-without-retry-increment primitive that doesn't exist yet.
1A is the partition-keys feature: send(partition_key),
fixed-N hash-routed slots, skip policy. 1B is producer
TTL dedup — a separate send-layer feature
(blueprints/idempotency/SPEC.md), independent of 1A; together with
consumer mutual exclusion it covers the migration case on a plain queue.
2 optimizes read amplification only if benchmarks demand it.
3 is pause strict ordering, gated on a
defer-without-retry-increment primitive (O1) and bounding hot-blocked-key cost (O2).
An "assignment" question exists only in Tier B (Tier A has no slots
— its advisory lock is per key, not per slot). Tier B slots are
claimed, not assigned: no consumer-group leader, no
PartitionAssignor, no stop-the-world rebalance. Here the
database is the coordinator — a worker doesn't get told
which slot to take, it claims a free one (or takes a static
worker i → slots {i, i+M…} share if workers have stable identity).
Claim loop · client-side
pg_try_advisory_lock per slot 0..N-1receive_partitionedScale 8 → 10 workers
Log & cursor — G2 receive lock (blocking for update): one active batch per slot; a second worker blocks, then is handed the same batch idempotently — never a divergent batch, never loss or delivery reorder.
Processing exclusivity — session claim (non-blocking try, held across process→ack): closes the process→ack gap the receive lock doesn't span (single in-flight processor) and steers workers onto distinct slots. Skipping it risks duplicate/interleaved processing of one batch — not pure liveness.
N is fixed (D3) and is the read-amplification multiplier (§05 — every
slot scans the full stream), so N is chosen to match the parallelism you need,
not inflated to dodge a future resize. Even spread under key skew (a
hot slot stays hot) and online resize of N (which reshuffles keys across slots,
breaking per-key order mid-flight) — the latter now has a sanctioned future
path (the epoch-gated begin_resize→drain→complete_resize
state machine in core, Phase 3), immutable N in Phase 1. R3, R4, D9.
Rotation pinning (R7) — the sharpest cost of N. Rotation waits for the slowest slot's cursor, so one stalled or lagging slot pins rotation for the whole queue and old event tables stop dropping — the bloat pgque exists to avoid, back with nicer names. It worsens as N grows. A per-slot lag alert is mandatory for any slot deployment, and N stays minimal — read-amp and rotation pressure both push the same way: keep N small.
Coordination: mechanism vs policy
slot_lock_key namespace, a read-only partition_slot_status view, and the resize guardTwo caveats worth stating
begin_resize → drain → complete_resize state machine in core (Phase 3); immutable N in Phase 1
Reframe for the Kafka mind: pgque routing is consume-time (the log carries
only the key; hash%N is applied at receive), so a resize moves no data
and never blocks producers — no "events stamped under the old N" problem.