Honor poll_timeout in the PostgreSQL provider via LISTEN/NOTIFY (long-polling)

Summary

The PostgreSQL provider ignores the poll_timeout argument on both dispatcher fetch paths, so it behaves as a short-polling provider. Honoring it via LISTEN/NOTIFY would let idle dispatchers block until work actually arrives instead of re-querying on a fixed interval.

Current state in src/provider.rs:

• line 1693 — fetch_orchestration_item(..., _poll_timeout: Duration, ...)
• line 2842 — fetch_work_item(..., _poll_timeout: Duration, ...)

Both are underscore-prefixed, i.e. deliberately unused. There is no LISTEN/NOTIFY usage in the crate.

Why this is the interesting lever

The core runtime already does all the plumbing. microsoft/duroxide/docs/long-polling-behavior-plan.md defines the contract:

Long-polling providers (e.g., Azure Service Bus, Redis with BLPOP): MAY block up to poll_timeout waiting for work to arrive. Should return early if work becomes available.

Short-polling providers (e.g., SQLite, PostgreSQL): Ignore poll_timeout and return immediately. The dispatcher handles the waiting.

So poll_timeout is already threaded through the Provider trait, and RuntimeOptions already exposes dispatcher_long_poll_timeout (default 30s) and dispatcher_min_poll_interval (default 100ms). PostgreSQL is simply on the short-polling side of that split today.

The consequences at idle:

• every dispatcher issues a query every dispatcher_min_poll_interval (100ms by default) whether or not there is work, which is continuous load proportional to dispatcher count rather than to actual throughput;
• newly enqueued work waits up to min_poll_interval before anything notices it, which is added latency on every hop of an orchestration — and orchestrations are many hops.

Lowering min_poll_interval trades the second against the first. Long-polling removes the trade: latency drops toward the notification round-trip while idle query volume drops toward zero.

No core change is required — the trait signature already accommodates this, so it is additive within this crate.

Suggested approach

Honor poll_timeout by waiting on a Postgres notification instead of returning None immediately:

1. NOTIFY on the enqueue paths (orchestration items and work items), or via triggers on the queue tables.
2. In the fetch methods, when the initial query finds nothing, wait on a dedicated listener connection (sqlx's PgListener) for up to poll_timeout, then re-query once woken.
3. Return early as soon as work is claimed; return None when poll_timeout expires.

Things worth being careful about:

• Missed-notification race — establish the listener before the initial query, so a notification arriving between the query and the wait is not lost.
• Notifications are hints, not delivery — after waking, still perform the normal locking fetch. Several dispatchers may wake for one item and all but one will find nothing; that is expected and must not error.
• Connection cost — LISTEN needs its own connection outside the pooled request path. Worth confirming how this interacts with DUROXIDE_PG_POOL_MAX and with PgBouncer-style poolers, which do not support LISTEN in transaction pooling mode. A capability check with graceful fallback to current behavior may be warranted.
• Degradation — if anything goes wrong with the listener, falling back to returning immediately preserves today's semantics exactly, so this can ship behind a safe default.

Relationship to existing work

Complementary rather than overlapping — these reduce the cost of each poll, whereas this issue reduces how often polling happens at all:

• #7 Add PostgreSQL batched worker fetch
• #18 perf(worker_queue): partial index + two-phase fetch_work_item dequeue
• #19 perf(indexes): make mostly-NULL secondary indexes partial

The waldemort/batched-worker-fetch-pg branch still carries _poll_timeout unused, so it does not overlap with this.

Context

Raised from a throughput analysis for the PG Light Cycle control plane, which is moving its durable workflow execution onto Duroxide backed by Azure Database for PostgreSQL Flexible Server. Of the levers identified there, this was the largest single one.

Start in src/provider.rs at fetch_orchestration_item around line 1693 and fetch_work_item around line 2842, then read microsoft/duroxide/docs/long-polling-behavior-plan.md and the Provider trait and RuntimeOptions references. Trace the enqueue paths and sqlx PgListener constraints, paying attention to the missed-notification race and pooled connections. Done means both fetch paths honor poll_timeout with LISTEN/NOTIFY, re-query after notifications, preserve normal claiming behavior, and safely fall back on listener failure.