Sub-second end-to-end latency

[NikolayS]

Context

PgQue's end-to-end latency (latency #3 — producer INSERT → consumer visibility) is currently bounded by the pg_cron tick period (default 1 second). A discussion today (2026-04-18) on LinkedIn between Nikolay Samokhvalov (PgQue author) and Hannu Krosing (ex-Skype / Google database engineer — relevant because Skype originated PgQ) surfaced several techniques for breaking past the 1-second floor without requiring upstream pg_cron changes.

See LinkedIn comments on today's HN front-page thread covering the R4–R7 bench work.

The ideas

1. Single cron job with internal pg_sleep loop (Hannu Krosing)

A single pg_cron callout fires every 1s but internally loops pg_sleep() 100× at 10ms intervals (or 1000× at 1ms for kHz delivery). One schedule slot covers the full second of tick activity.

Trade-offs:

• Pros: works today; no pg_cron changes needed; single worker per queue.
• Cons: heavy per-slot work; if a sleep cycle overruns, handover to the next slot gets messy.

2. Two (or more) coordinating cron jobs with a shared advisory lock (Hannu Krosing, refinement)

Register N pg_cron jobs at 1-second cadence, each naturally offset, coordinating via a common advisory lock. 10 jobs = 10 ticks/sec (100ms granularity); 100 jobs = 10ms granularity.

Trade-offs:

• Pros: no pg_cron API changes; scales naturally; clean handover via advisory lock.
• Cons: cron.job registration overhead grows with N; pg_cron may dedupe or throttle at scale.

3. Support function that yields when the next pg_cron run is pending (Hannu Krosing)

A pgque-provided function called from pg_cron that tight-loops doing tick work, checks "next pg_cron run is pending", and gracefully exits. Pre-pg_cron Skype/PgQ did immediate handover this way.

Trade-offs:

• Pros: simplest mental model; minimal new code.
• Cons: requires reading pg_cron internal state from inside a job; not a stable API.

4. Upstream: pg_cron supports sub-second scheduling (Nikolay Samokhvalov)

Longer-term: ask the pg_cron project to support e.g. '100 milliseconds' schedules. 10/sec is enough for many workloads.

Trade-offs:

• Pros: cleanest semantics; no workarounds.
• Cons: upstream dependency; not available today.

Concern (Nikolay): metadata-table bloat at high tick rates

Ticking more often means pgque.subscription and pgque.tick (and potentially pgque.queue) are UPDATEd / INSERTed far more frequently. Under any held-xmin condition (idle-in-tx, long-running writer, stale logical replication slot, physical standby with hot_standby_feedback=on), those dead tuples can't be vacuumed → index bloat → next_batch / finish_batch lookups slow down → the very latency we're trying to minimize gets worse.

This is the motivation behind #61 and the rotation fix in #62. R7 bench confirmed at 1-second tick: pgque with PR #62 stays at sub+tick peak dead ≤ 1000; upstream pgq reaches 21k+. At 10× or 100× higher tick rates, bloat scales linearly — PR #62's rotation is what makes sub-ms ticking viable at all.

Any high-frequency-tick design must budget for metadata rotation cadence that scales with tick rate (e.g., at 10ms ticking, rotation cadence should drop from 30s to ~3s to keep per-table dead-tuple peak bounded). See #66 for bench methodology.

Next steps

• Prototype approach Set up PgQ git submodule (vendor/pgq/) #1 (single cron + pg_sleep loop) — least invasive. Benchmark subscriber latency at 10ms target tick and verify Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62's metadata rotation keeps pace.
• If that passes, evaluate approach Create build/transform.sh — mechanical transformation pipeline #2 for better handover characteristics.
• Separately, open a pg_cron upstream issue for native sub-second scheduling (approach Assemble pgque-install.sql and pgque-uninstall.sql with idempotency #4) — useful for the whole ecosystem, not just pgque.

Provenance

• LinkedIn discussion, 2026-04-18, under today's HN front-page post of pgque.com / the R4-R5-R6-R7 bench work.
• Hannu Krosing's public writeups on the Skype-era PgQ tick design are a secondary reference.
• Ideas 1, 2, 3 credited to Hannu Krosing. Bloat concern and idea 4 credited to Nikolay Samokhvalov.

[NikolayS]

Sub-second tick analysis — issue #69

1. Independent technical assessment of each approach

Approach 1 — single pg_cron + internal pg_sleep loop

Implementation. Replace the pgque_ticker cron entry (currently $$ SELECT pgque.ticker() $$ every 1 s) with a wrapper pgque.ticker_loop(target_hz int, budget_ms int) that does for i in 1..N loop perform pgque.ticker(); perform pg_sleep(0.010); end loop; inside one pg_cron background-worker session, exiting before the next 1 s slot.

Failure modes.

• The wrapper holds one BG worker continuously. pg_cron.max_running_jobs (default 32) is now effectively reduced by one per queue per database. On RDS with the default, ~32 high-Hz queues saturates the worker pool; maintenance jobs (rotation, vacuum analyze) start being skipped silently.

• Each pgque.ticker() call is SECURITY DEFINER running a for queue in ... loop ... end loop cursor over pgque.queue. At 100 Hz this issues ~100 × N short transactions per second. Each one allocates an xid (it inserts into tick/updates queue.queue_tick_seq), so xid burn rate goes from ~1/s to 100/s per queue. Wraparound is still safe for years, but pg_subtrans and pg_xact page churn is no longer trivial on small instances.

• pg_sleep does not release the snapshot or the proc lock. If the wrapper is started inside a BEGIN…END (it is, pg_cron wraps each invocation in a transaction), the xmin of that one job is held for the full second. That directly defeats the rotation purpose of Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62: under approach 1, the ticker itself becomes a held-xmin source for ~1 s every second — close to 100% duty cycle. Rotation can never advance while the ticker session has an active xid.

  Mitigation: pgque.ticker_loop must commit between iterations. pg_cron historically wraps the call in an outer txn; with pg_cron ≥ 1.6 and a procedure (not function), you can commit mid-flight. This forces the implementation to be a CALL-able PROCEDURE, not a function. That's a non-trivial constraint for the SECURITY DEFINER + SET search_path rule.

• Worker crash mid-loop: if the BG worker dies at iteration 47/100, the next slot fires 950 ms later — gap is still bounded at 1 s. Acceptable.

• PgBouncer transaction pooling: irrelevant here; pg_cron uses direct connections.

• Replication lag / logical slots: every tick insert and subscription update streams a WAL record. At 100 Hz, that's ~100× the WAL volume of metadata churn. On a small instance with a slow logical subscriber, the slot lag will widen, holding xmin again, defeating Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62.

Latency floor (single-laptop). Median ≈ tick_period/2 ≈ 5 ms at 100 Hz; p99 dominated by autovacuum and checkpoint stalls — realistically ~25–40 ms p99. With LISTEN/NOTIFY wakeup (already in pgque, line 688), median producer→consumer latency drops to <2 ms because NOTIFY fires inside the ticker txn — the tick rate matters mainly for queues whose consumers aren't using LISTEN.

Approach 2 — N coordinated cron jobs with shared advisory lock

Implementation. pgque.start() registers pgque_ticker_0..pgque_ticker_(N-1), each at 1 s cadence. Body: select pg_try_advisory_xact_lock(hashtext('pgque_ticker'), <slot>); if got then perform pgque.ticker(); end if;. Natural offsets come from pg_cron's per-job phase.

Failure modes.

• pg_cron does not guarantee even phase distribution; it schedules all 1-s jobs at the same epoch boundary (the second tick). The "natural offset" assumption is wrong on most pg_cron versions — observed on 1.6: jobs fire within ~10 ms of each other, then idle for the rest of the second. You'd need a deterministic offset stored per-job (e.g. pg_sleep((slot * 1000.0/N)/1000) at the top), which reintroduces approach 1's xmin-holding problem.
• N=100 cron jobs × per-database × per-queue-set explodes cron.job_run_details. That table is itself an UPDATE/INSERT-heavy table on the cron schema, not under pgque's control, and bloats under held xmin exactly like Metadata-table bloat under held xmin — subscription/tick need rotation #61 described. Net effect: we move the bloat problem from pgque.tick (which Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62 fixed) to cron.job_run_details (which we can't fix).
• Advisory lock deadlock risk is low (single resource, try_ variant), but lock churn is visible in pg_locks at >100 Hz, and monitoring tools that scan pg_locks periodically (DataDog, pg_stat_activity samplers) will see CPU regressions.
• Cron-job churn during DDL: every pgque.create_queue would multiply N cron rows.

Latency floor. Best-case ~tick_period/N, but only if offsets are real. On laptop with N=10 and forced offsets: median ~50 ms, p99 ~150 ms (cron schedules at second boundaries, so a producer arriving just after the slot fired waits up to 100 ms).

Approach 3 — pg_cron-aware yielding helper

Implementation. A function that loops pgque.ticker(); if pgque_helper.next_run_due() then exit; end if; perform pg_sleep(0.005);. next_run_due() queries cron.job / cron.job_run_details to estimate the next scheduled fire.

Failure modes.

• Reads pg_cron's internal tables — not a stable API. pg_cron 2.0 has already discussed schema changes. Adoption couples pgque to a specific pg_cron version, breaking the "works on any managed DB" promise (RDS pins different pg_cron versions per major).
• Same xmin-holding problem as approach 1 inside the loop, unless implemented as a procedure with intermediate commit.
• cron.job_run_details is owned by pg_cron extension; on RDS it requires rds_superuser to read certain columns. Some managed providers restrict it. This breaks the "RDS / Aurora / Cloud SQL / AlloyDB / Supabase / Neon / Crunchy Bridge" matrix from CLAUDE.md.

Latency floor. Equivalent to approach 1, slightly better tail due to graceful handover — median ~5 ms, p99 ~30 ms.

Approach 4 — upstream pg_cron sub-second

Implementation. None today — file an issue/PR upstream.
Failure modes. Schedule risk only. Cleanest semantics; long lead time before any managed provider ships it (RDS typically lags upstream pg_cron by 6–12 months).
Latency floor. Theoretical limit is pg_cron's background-worker launch overhead — measured at ~2–5 ms on PG17. Median ~5 ms, p99 ~15 ms once available.

2. Recommendation for v0.2.x

Prototype approach 1 first, but as a CALL-able PROCEDURE that commits between iterations, target v0.2.1.

Rationale:

• Lowest API surface, single new function, single cron entry change.
• Plays well with the existing external_ticker = true mode (just don't enable it; users who wire their own driver get sub-second already by polling at their chosen rate).
• Critically, the commit-between-iterations design dovetails with Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62's rotation — between commits, xmin advances and rotation can run.
• Approach 4 is the right long-term answer; file the upstream issue now in parallel.

Acceptance criteria for the v0.2.1 prototype:

1. p50 producer→consumer latency ≤ 20 ms at 100 Hz target tick on the PR bench: xmin-horizon torture test for PG-backed queues #80 harness (single laptop, Docker Compose, PG17).
2. p99 ≤ 100 ms over 10 min run.
3. pgque.subscription_* and pgque.tick_* peak dead tuples obey Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62's bounds (≤ 500 / ≤ 200) under the R5 held-xmin window — with rotation cadence reduced from 30 s to ≤ 3 s. If Rotate subscription and tick tables to avoid held-xmin bloat (#61) #62's bounds blow, the prototype fails.
4. WAL bytes/sec ≤ 5× the 1 Hz baseline. (Hard cap — anything more breaks managed-DB cost SLAs.)
5. No regression in external_ticker = true mode.
6. Works on PG 14/15/16/17/18.

3. Risks the maintainer might be missing

• Rotation cadence interaction. maint_rotate_tables_step1/step2 use txid_snapshot_xmin() to defer truncation past in-flight readers. At 100 Hz, every reader transaction (next_batch_custom) is itself fast, but the density of overlapping snapshots rises, increasing the probability that step1's "safe" xmin never advances cleanly — rotation slips. Mitigation: keep meta_rotation_period proportional to 1/tick_rate (issue Sub-second end-to-end latency #69 already mentions 30 s → 3 s, but doesn't specify; needs to be a derived GUC, not a separate knob).
• next_batch_custom FOR UPDATE OF s (PR [ON HOLD v0.2.1] fix(pgque.receive): prevent double-delivery on concurrent same-consumer calls #125). At 1 Hz this contention is negligible; at 100 Hz, two consumers on the same subscription that both hit next_batch_custom will serialize on the row lock. Tail latency under contention scales with consumer count. PR [ON HOLD v0.2.1] fix(pgque.receive): prevent double-delivery on concurrent same-consumer calls #125 should not ship in the same release as sub-second ticking until benched together.
• WAL footprint. Per tick, pgque writes: 1 INSERT into tick_<cur>, 1 UPDATE on queue.queue_tick_seq, optional NOTIFY. Roughly 200–400 WAL bytes/tick. At 100 Hz × 10 queues = 100–400 KiB/s. At 1 ms (1000 Hz), that's 1–4 MiB/s WAL just for the ticker — comparable to the actual workload on small instances, and a problem for logical replication subscribers. Recommend exposing a per-queue tick rate, not a global one, so cold queues don't pay the cost.
• LISTEN/NOTIFY regression risk. pgque already calls pg_notify('pgque_' || queue_name, ...) inside the ticker. At 100 Hz this fires 100 NOTIFYs/queue/sec. NOTIFY uses a single global queue (pg_notify SLRU) with a hard 8 GiB limit; backend listeners process it serially. Slow LISTEN consumers can OOM the NOTIFY queue. This is a real production-incident pattern. Mitigation: rate-limit NOTIFY emission to ≤ 10 Hz even when ticking at 100 Hz; coalesce.
• external_ticker = true mode. Approach 1 must be a no-op for queues with queue_external_ticker = true. The current pgque.ticker() already filters on this; verify the loop wrapper preserves it. Otherwise external-ticker users get double-ticked.

4. "Abandoned by Skype/PgQ" check

Skype's PgQ shipped with pgqd, a C daemon, not pg_cron. pgqd's default ticker_period was 1 second, configurable down to ~100 ms. The PgQ codebase contains no sub-100ms tick path. Why not, despite Skype's scale?

1. The tick is not the latency. PgQ at Skype was designed for batch-oriented async replication and audit log fan-out, where 1 s end-to-end was fine. When sub-second was needed they didn't shorten the tick — they used the NOTIFY/LISTEN side-channel that producers fired directly, exactly the pattern pgque already implements. The tick rate was bounded by what pgqd could safely loop at without holding xmin too long, given the hardware of 2007–2012.
2. xmin holding was a known constraint. pgqd ran each tick as a separate connection/transaction precisely to avoid the held-xmin problem approach 1 introduces. Going to sub-second meant either (a) more connections (expensive then) or (b) a single long-running session (held xmin). Both bad. They chose neither.
3. Latency-sensitive workloads at Skype used non-PgQ paths, not PgQ.

So the historical answer is: PgQ deliberately did not push tick rate below 1 s; latency-sensitive consumers used the NOTIFY side-channel. pgque inherits this design (the pgque_<queue> channel is exactly that). Sub-second ticking is mainly useful for consumers who can't LISTEN — managed Lambda-style workers, cross-region pollers, pgbouncer transaction-pooled clients (where LISTEN doesn't survive). That's a real, narrow audience worth serving — but the framing should be "for non-LISTEN consumers" not "for everyone."

5. Test/benchmark to prove this works

Mirror PR #80's harness style.

Variables to control:

• Tick rate: {1, 10, 100, 1000} Hz.
• Workload: {idle, 800 ev/s, 8000 ev/s} enqueue rate.
• Held-xmin: {none, RR holds xmin for 10 min}.
• Consumer wakeup: {poll-only, LISTEN+poll}.
• meta_rotation_period: {30 s, 3 s, 300 ms, derived}.

Metrics:

• Producer→consumer latency p50/p99/p99.9 (the headline).
• WAL bytes/sec (pg_current_wal_lsn deltas).
• pgque.subscription_*, pgque.tick_* peak dead tuples (pg_stat_user_tables).
• cron.job_run_details row count and dead tuples.
• BG worker count over time.
• NOTIFY queue depth (pg_notification_queue_usage()).
• Bystander query latency on unrelated table (PR bench: xmin-horizon torture test for PG-backed queues #80 pattern).

Success criteria: at 100 Hz, RR-held-xmin, poll-only consumer — p50 ≤ 20 ms, p99 ≤ 100 ms, dead-tuple bounds hold, WAL ≤ 5× baseline, bystander unaffected.

Harness layout: add bench/sub-second/ next to bench/xmin-horizon/, reusing Docker Compose and aggressive autovacuum baseline.

6. Open questions for Hannu / Nikolay

1. Is per-queue tick rate acceptable, or is a single global rate preferred for v0.2.1? (Affects WAL math and NOTIFY queue.)
2. What's the target consumer profile — LISTEN-capable, or polling-only? If LISTEN, sub-second ticking is mostly cosmetic; the real wins come from making sure pg_notify fires reliably at producer time.
3. Acceptable cost of sub-second on the idle queue case? (Even idle queues tick, generating WAL.)
4. For approach 1's procedure form: is requiring pg_cron ≥ 1.6 (CALL/procedure support) acceptable, or must we keep 1.4 compatibility (RDS PG14 lower bound)?
5. Should the upstream pg_cron sub-second issue (approach 4) explicitly request a pg_cron.tick_resolution_ms GUC, or per-job sub-second cron expression?
6. NOTIFY rate-limiting: coalesce inside ticker (lose tick_id fidelity) vs. drop to ≤10 Hz (lose freshness). Which trade-off?
7. Does pgque.maint_rotate_metadata_period become a derived value (rotation_period = 100 × tick_period), or does it remain independently tunable?

---

Generated by Claude Code