perf: enable TCP_NODELAY on sockets - #4336

Correctness:

- Windows writes idle and interval together through `SIO_KEEPALIVE_VALS` and
  cannot leave one of them alone, so an unset one was being sent as 0 ms rather
  than left at the system default. Substitute libpq's own Windows values there
  (2h idle, 1s interval), as `pqSetKeepalivesWin32` does.
- Normalize the parameters in one place, `TcpKeepalive::normalized()`: zero
  means "system default", and fractional durations round up to whole seconds.
  `socket2` truncates, so a sub-second value reached `setsockopt()` as a literal
  0, which Linux rejects with `EINVAL`; only the URL parser normalized before,
  leaving both builders (and MySQL, which has no URL form) exposed.
- A value the kernel rejects now fails with `Error::Configuration` naming the
  parameters instead of a bare `Io(EINVAL)` that mentions neither.
- `?keepalives_idle=0` on its own left keepalive off, because 0 was folded to
  "unset" before deciding whether any parameter had been given. Any of the three
  now turns keepalive on, as in libpq.
- Negative values clamp to "system default" as libpq's `strtol` path does,
  rather than failing to parse.
- `build_url()` emits the keepalive parameters, so they survive `to_url_lossy()`
  alongside `sslmode` and `statement-cache-capacity`.
- Raise the workspace `tokio` floor to 1.27, the first release with
  `AsFd`/`AsSocket` on `TcpStream`, which `SockRef::from()` requires.

Docs:

- Correct the platform-support paragraph: `idle` is silently dropped on OpenBSD,
  Haiku and Vita, and `retries` fails the connection rather than being ignored
  where `TCP_KEEPCNT` is missing.
- List the four URL parameters in the `PgConnectOptions` table with a note on
  the semantics, and say on the MySQL side that no URL form exists.
- Cite transact-rs#4336 for `TCP_NODELAY` rather than transact-rs#3055, which was reverted by transact-rs#4022.
- Correct the socket2 dependency comment: `tokio` only bundles socket2 0.6 from
  1.47 onwards.

Tests:

- Exercise the `setsockopt()` path on a loopback socket, reading the values back
  and covering the zero, sub-second and kernel-rejected cases. Only the builder
  and URL parsing were covered before, so none of the above was observable.

Co-Authored-By: Claude Fable 5 <[email protected]>

- awa: git pin moved to main c3a503e (0.7.0-alpha.1; no stable 0.7
  yet). awa main moved to sqlx 0.9 and OpenTelemetry 0.32, so the
  adapter follows: sqlx 0.9 (dynamic schema-qualified SQL wrapped in
  AssertSqlSafe) and opentelemetry/_sdk/-otlp 0.32 to keep a single
  opentelemetry in the graph.
- river v0.40.0 -> v0.47.0 (golang 1.25 -> 1.27 builder; 0.45+ needs Go
  1.26). JobTimeout now equals RescueStuckJobsAfter: since v0.41.1 a
  JobTimeout of -1 means stuck jobs are never rescued, which would leave
  jobs from a killed worker running forever in the chaos phases.
- pg-boss 12.26.0 -> 12.35.1. Queue depth is a live count on the queue
  table: getQueueStats() has returned an array with a cached count since
  12.24, so queue_depth was missing and depth-target mode never enqueued
  on the previous pin. Descriptor version now read from the package.
- oban ~> 2.24.1, postgrex ~> 0.22.4; lifeline moved to the top-level
  config key.
- procrastinate 3.10.0, absurd-sdk 0.5.0 (descriptor version and schema
  version now read at runtime; they were hardcoded to 0.3.0), pgmq
  v1.13.0 image, psycopg 3.3.6. pgque submodule to main 95d5c3c; the
  released sql/pgque.sql the adapter loads is byte-identical.
- postgres 18.3 -> 18.6-alpine. The healthcheck probes over TCP so the
  initdb bootstrap server (unix socket only) no longer reports healthy
  and then drops the first harness connection.
- versions.py: awa/awa-canonical/awa-docker record the awa version and
  SHA from awa-bench/Cargo.lock rather than the ../awa checkout; pgque's
  block no longer carries the awa checkout's SHA.

Smoke (200/s x 2 replicas, warmup/clean/kill-worker/recovery): all
eight systems complete every phase. awa on this pin is capped near
117 jobs/s: sqlx-core 0.9.0 dropped TCP_NODELAY (restored upstream in
transact-rs/sqlx#4336, unreleased), so every small round trip waits on
delayed ACK. Restoring the flag locally gives 400/s with producer call
p50 2.8 ms vs 86 ms.

…ow 2048 rows (#506)

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* fix(queue-storage): guard legacy cursor replay and model async failover

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* perf(queue-storage): reuse the claimer lease across claims

Every claim_runtime_batch_with_aging_for_instance call read the queue's
claimer target and this instance's claimer lease before claiming: two extra
round trips and two transactions per claim. With few free worker permits
each claim returns about one job, so that was two transactions per job.

The lease row is now cached per (queue, instance) for 250 ms and never used
within a second of its expiry. A successful heartbeat refresh updates the
cached row, and a lost lease (refresh matches no row, or acquisition finds
no free slot) drops it. The adaptive claimer target is a 500 ms control
signal, so reading it at most every 250 ms changes nothing material.

Depth-target bench, W=4, 1 ms jobs, PG 18.6: transactions per completed
job 2.35 -> 1.39, throughput 358 -> 392 jobs/s.

* fix(queue-storage): forget cached claimer leases on reset

reset() truncates queue_claimer_leases; a claim within the cache TTL could
otherwise reuse a lease that no longer exists and bypass the bounded-claimer
gate.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* fix(queue-storage): guard legacy cursor replay and model async failover

* test(queue-storage): group claimer cache coverage for stacked merge safety

* docs: clarify COPY fallback and async claim durability boundaries

…claim concurrency bound (#510)

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* perf(queue-storage): reuse the claimer lease across claims

Every claim_runtime_batch_with_aging_for_instance call read the queue's
claimer target and this instance's claimer lease before claiming: two extra
round trips and two transactions per claim. With few free worker permits
each claim returns about one job, so that was two transactions per job.

The lease row is now cached per (queue, instance) for 250 ms and never used
within a second of its expiry. A successful heartbeat refresh updates the
cached row, and a lost lease (refresh matches no row, or acquisition finds
no free slot) drops it. The adaptive claimer target is a 500 ms control
signal, so reading it at most every 250 ms changes nothing material.

Depth-target bench, W=4, 1 ms jobs, PG 18.6: transactions per completed
job 2.35 -> 1.39, throughput 358 -> 392 jobs/s.

* fix(queue-storage): forget cached claimer leases on reset

reset() truncates queue_claimer_leases; a claim within the cache TTL could
otherwise reuse a lease that no longer exists and bypass the bounded-claimer
gate.

* perf(worker): share one LISTEN connection across queues and back off idle polls

Each queue dispatcher held its own PgListener connection, so a runtime with N
queues pinned N pool connections and N LISTEN backends; at 200 queues the
default pool is exhausted before any job runs. One notify hub per runtime now
LISTENs on every queue channel and signals a per-queue Notify that the
queue's dispatchers wait on. Empty polls double their sleep from
poll_interval up to the new QueueConfig::idle_poll_interval (2s) while
notifications are available, so idle cost no longer scales with queue count
at the 200ms cadence; poll-only runtimes keep polling at poll_interval.

* perf(worker): bound concurrent claim round-trips per runtime

With one listener per runtime a large pool no longer pins a connection per
queue, so a many-queue runtime could open hundreds of claim transactions at
once; each claim locks 40-60 relations and the server spent its time in the
lock manager (claim mean 10 ms to 58 ms at 200 queues on 4 CPUs). A runtime-
wide semaphore, ClientBuilder::max_concurrent_claims (default a quarter of
the pool, clamped to 4..=64), caps the claims in flight across all queues
and claimers.

* docs(changelog): shared listener, idle back-off, claim bound

* docs(configuration): keep the idle poll backoff notes with the poll settings

CLAUDE.md had a duplicated "no default database" sentence, the `ductus dev`
gotcha twice, dev.rs twice in "Where things are", D:\target-win mangled into a
tab, and a literal newline inside `newline='\n'`. The TCP_NODELAY note now
points at transact-rs/sqlx#4336 (merged after 0.9.0): bump sqlx when a release
has it.

Co-Authored-By: Claude Opus 5.5 <[email protected]>
Signed-off-by: Todd Horst <[email protected]>

## Problem

sqlx 0.9.0 counts a transaction only after Postgres acknowledges its
`BEGIN`. If the future is dropped while `BEGIN` is still in flight (a
cancelled REQ history read, a client that disconnects, a timeout), sqlx
doesn't queue a `ROLLBACK`. The pool's release ping then reads the late
`BEGIN` reply, and the writer pool hands the connection to the next
borrower while it is still inside that transaction. The reader pool
already closes such connections in its release check
(`Db::read_pool_options`). The writer pool has no release check.

Load tests showed what happens next:

- `there is already a transaction in progress` (25001) on the next event
read's `BEGIN`.
- Sometimes a burst of `current transaction is aborted` (25P02). The
leftover from the event read is `READ ONLY`, so the next write fails and
aborts it, and every later statement on that connection fails until the
connection is closed. During a burst, ban checks failed closed with 503
and window reads returned 500.
- A read-write leftover would also leave autocommit writes uncommitted,
and the next read's `ROLLBACK` would discard them.

## Change

- Pin every sqlx crate to transact-rs/sqlx#4394 (merged, not yet
released). That commit takes the transaction count before the `BEGIN`
round trip, so a cancelled begin queues its `ROLLBACK`. The crates share
types, so they have to move together. The pin also picks up the other 23
commits between v0.9.0 and that fix, including the libsqlite3-sys fix
`sqlx-sqlite` was already pinned for.
- Add a regression test,
`writer_pool_postgres_tests::begin_cancelled_in_flight_leaves_no_writer_transaction_open`.
It holds `BEGIN` in flight behind a paused proxy and times it out, for
both the event read's `BEGIN ... READ ONLY` and a plain `begin()`. It
then asserts that the next borrower's autocommit write commits and that
its backend is `idle`, not inside a transaction.

Other upstream changes in the pin that reach the Postgres path:
`TCP_NODELAY` on sockets (transact-rs/sqlx#4336), reuse of the TLS
connector across connection attempts (transact-rs/sqlx#4242), a fix for
a memory leak when the statement cache is disabled
(transact-rs/sqlx#4337), a describe-time generic-plan change used only
by the macros (transact-rs/sqlx#4286), and the `whoami` default-user fix
(transact-rs/sqlx#4303). The rest are SQLite, MySQL, CLI, or docs
changes.

Remove the pin once sqlx 0.9.1 ships.

## What got simpler

The lockfile loses its second `sqlx-core` copy, which the
sqlx-sqlite-only pin used to pull in. No runtime code changes. A
pool-level release check would have fixed the same bug, but it costs a
round trip on every writer release.

Comments that described the 0.9.0 behavior as current are refreshed: the
reader pool's release check (`Db::read_pool_options`) and the lease
heartbeat (`EntityDeletionStore::heartbeat`). The reader test
`proof_timeout_mid_begin_leaves_no_reader_transaction_open` asserted
that the release check closed the connection left mid-`BEGIN`. With the
pin, sqlx rolls that transaction back itself, so the test now asserts
the check finds nothing to close. That also catches a sqlx regression
the check would otherwise hide.

Identified debt (not in this PR): the release check's open-transaction
arm, and the reader-session `default_transaction_isolation` pin it
depends on, are now only a backstop against a sqlx regression. They
could be removed in a follow-up.

## Testing

- Against sqlx 0.9.0 (main's `Cargo.toml`/`Cargo.lock` at 962b3fd), the
new writer test fails for each `BEGIN` form on its own. The event read
fails with `cannot execute INSERT in a read-only transaction`, and after
a plain `begin()` the next borrower's backend is not `idle`. With the
pin it passes 5/5.
- Single-machine load-test A/B against main (962b3fd), with three main
runs and two pin runs. Main logged 25001 and 25P02 in every run (41/80,
10/9, 12/18); the pin logged none. Client error rate, latency, and
throughput matched within run-to-run noise.
- Full suite on the pin: every `just ci` recipe, the PostgreSQL lane,
and `just test`. The only failure from the pin was the reader test
above. Reds that don't involve sqlx: the desktop Tauri recipes
(`alsa-sys` needs ALSA headers on the build host),
`mobile-check`/`mobile-test` (a Flutter SDK `ColorScheme` type clash),
and two `buzz-acp` `git_bootstrap` tests (no git credentials on the
host). Two bridge search-timeout tests failed only under the full
parallel lane; run alone they pass on both the pin and 0.9.0.
- Two later commits change only tests, comments, and `Cargo.lock` (the
lock now moves only the sqlx crates). At those heads: the PostgreSQL
lane, the unit lane, and workspace clippy pass. Three tests failed once
under a contended full lane, and each passed 3/3 alone on both the pin
and 0.9.0. The two transaction tests fail on 0.9.0 and pass on the pin.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

---------

Signed-off-by: Luke Tornquist <[email protected]>
Co-authored-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
Co-authored-by: Claude Opus 5.5 <[email protected]>

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* fix(queue-storage): guard legacy cursor replay and model async failover

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* perf(queue-storage): reuse the claimer lease across claims

Every claim_runtime_batch_with_aging_for_instance call read the queue's
claimer target and this instance's claimer lease before claiming: two extra
round trips and two transactions per claim. With few free worker permits
each claim returns about one job, so that was two transactions per job.

The lease row is now cached per (queue, instance) for 250 ms and never used
within a second of its expiry. A successful heartbeat refresh updates the
cached row, and a lost lease (refresh matches no row, or acquisition finds
no free slot) drops it. The adaptive claimer target is a 500 ms control
signal, so reading it at most every 250 ms changes nothing material.

Depth-target bench, W=4, 1 ms jobs, PG 18.6: transactions per completed
job 2.35 -> 1.39, throughput 358 -> 392 jobs/s.

* fix(queue-storage): forget cached claimer leases on reset

reset() truncates queue_claimer_leases; a claim within the cache TTL could
otherwise reuse a lease that no longer exists and bypass the bounded-claimer
gate.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* fix(queue-storage): guard legacy cursor replay and model async failover

* test(queue-storage): group claimer cache coverage for stacked merge safety

* docs: clarify COPY fallback and async claim durability boundaries

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* fix(queue-storage): guard legacy cursor replay and model async failover

* perf(queue-storage): commit claim transactions asynchronously

A claim transaction holds its lane's queue_claim_heads row lock until it
commits, and the post-commit cursor advance takes the same row. Both
committed synchronously, so every claim spent a WAL fsync inside the lock.
Under load each freed worker permit claims one job, which made claiming one
fsync-bounded, lane-serialised transaction per job.

The claim, claim_batch and cursor-advance transactions now begin with
`SET LOCAL synchronous_commit = off`, sent in the same round trip as BEGIN.
A claim lost to a server crash leaves its job claimable again, which
at-least-once delivery already allows. A synchronous commit flushes all
earlier WAL, so a completion or enqueue can never be durable while the
claim it depends on is not. Enqueues, completions and every other
transaction stay synchronous.

Job-queue bench, 2 replicas x 200 jobs/s offered, 8 workers each,
PostgreSQL 18.6 on a SATA SSD (~7 ms fsync), stock sqlx 0.9.0:

                     completed/s  claim p50  claim p99  backlog
  before                     320    6468 ms    9761 ms     3494
  after                      383      10 ms      67 ms        3

LWLock:WALWrite samples fell from 137 to 38 over the same window.

* perf(queue-storage): write enqueue batches with a single unnest INSERT

The INSERT writers for ready_entries, ready_segments and deferred_jobs built
multi-row VALUES statements with one bind per column per row, so every batch
size produced new SQL text to parse and plan. They now send each column as
one array through a fixed-text INSERT ... SELECT FROM unnest(...).

The COPY entry points (QueueStorage::enqueue_params_copy, insert_many_copy
and the Python *_copy methods) route batches below COPY_MIN_ROWS (2048) to
that INSERT. Below the threshold the INSERT is faster than COPY, and with
sqlx-core 0.9.0 every COPY also pays a ~40 ms Nagle/delayed-ACK stall: the
driver no longer sets TCP_NODELAY and sends CopyData and CopyDone as
separate small writes (fixed upstream in transact-rs/sqlx#4336, not yet
released). Large batches keep COPY.

Measured on PostgreSQL 18.6, one connection, stock sqlx-core 0.9.0
(ms per transaction):

  rows   COPY   unnest      (with TCP_NODELAY: COPY / unnest)
     5   41.4    0.55                          0.46 / 0.36
   128   41.8    0.93                          1.01 / 0.89
   512    2.3    1.77                          1.87 / 1.36
  2048   19.6    5.07                          2.87 / 3.32
  8192   28.7   12.96                          8.13 / 11.73

Producer call p50 in the job-queue bench (2 replicas x 200 jobs/s) drops
from 86 ms to ~6 ms on stock sqlx.

The existing COPY tests now exercise the INSERT path; new tests at the
threshold keep the CSV encoder covered for both the canonical and
queue-storage COPY writers.

* fix(queue-storage): advance a claim cursor only past recorded claims

The post-claim cursor advance ran in its own transaction after the claim
committed, and moved the lane's claim sequence unconditionally. If the
primary failed after acknowledging a claim but before that claim reached a
standby, the advance could retry through the pool against the promoted
standby and move its cursor past a ready row whose claim never happened
there; the job would then be skipped permanently. Asynchronous claim commits
widen that window for synchronous-replication setups; with asynchronous
replication it existed already.

Advances derived from a claim now carry the claimed row's ready slot,
generation and lane position, and only apply while a
ready_claim_attempt_batches row covering that position is visible (an
index probe on one partition). A lagging cursor is already safe: the next
claim's spent-prefix path repairs it.

* perf(queue-storage): reuse the claimer lease across claims

Every claim_runtime_batch_with_aging_for_instance call read the queue's
claimer target and this instance's claimer lease before claiming: two extra
round trips and two transactions per claim. With few free worker permits
each claim returns about one job, so that was two transactions per job.

The lease row is now cached per (queue, instance) for 250 ms and never used
within a second of its expiry. A successful heartbeat refresh updates the
cached row, and a lost lease (refresh matches no row, or acquisition finds
no free slot) drops it. The adaptive claimer target is a 500 ms control
signal, so reading it at most every 250 ms changes nothing material.

Depth-target bench, W=4, 1 ms jobs, PG 18.6: transactions per completed
job 2.35 -> 1.39, throughput 358 -> 392 jobs/s.

* fix(queue-storage): forget cached claimer leases on reset

reset() truncates queue_claimer_leases; a claim within the cache TTL could
otherwise reuse a lease that no longer exists and bypass the bounded-claimer
gate.

* docs: keep the SQLx TCP_NODELAY release gate explicit

* fix(queue-storage): guard legacy cursor replay and model async failover

* test(queue-storage): group claimer cache coverage for stacked merge safety

* docs: clarify COPY fallback and async claim durability boundaries