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. 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.
The build-now slice — almost entirely a recipe over existing primitives, not new engine code.
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/.
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.6) |
|---|---|---|---|
| D1 | A·B | Where the key lives | ev_extra1, send()-sourced queues only |
| D6 | A·B | Producer signature | send(queue, type, payload, partition_key =>) |
| D8 | A | Tier A serialization | cooperative consumers + per-key pg_try_advisory_lock; contended + idempotent → ack-drop. No N, no assignment |
| D9 | A | Idempotency ask | two complementary layers — consumer mutual-exclusion (dup work) + optional producer TTL window (dup insert); not a substitute for partition keys |
| 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.
Tier A (A1–A2) is the converged, build-now slice — it unblocks the migration
workload and the idempotency ask with almost no engine change. Tier B (B1–B2) adds
strict per-key order for streams that need it; pause (B3) is 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
Correctness — G2 receive lock (blocking for update): two workers targeting slot k → exactly one gets the batch; the other blocks. Ordering can never break.
Distribution — advisory slot lock (non-blocking try): steers workers onto distinct slots. Pure liveness — skipping it loses even spread, never G1/G2.
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) are deferred — R3, R4.