Pinned Worker Connection Pool¶

Verified by tests

PinnedConnectionPoolPrimitivesTests, PinnedPoolRegistrationTests, PinnedConnectionPoolIntegrationTests — library CI run #31657041675 (2026-08-13)

Whizbang background workers normally share the pgbouncer-fronted connection pool with application traffic. Each transaction's return to the pool triggers DISCARD ALL to reset session state — a cheap call individually (~60 µs) but one that fires on every transaction, including the hot polling loops.

On the production-shaped bulk-import workload that motivated the feature, DISCARD ALL accounted for 22.5 % of total database time. The pinned worker connection pool eliminates that overhead for the workers that actually drive the hot path.

When to enable¶

The pinned pool is opt-in and off by default. Enable it when:

Hot worker DISCARD ALL cost is observably eating a meaningful slice of DB time (use pg_stat_statements, filtered by dbid to isolate your service's database).
You have a way to provide a direct PostgreSQL connection string that bypasses pgbouncer (typically pointing at port 5432 rather than the pgbouncer port 6432).
The host environment can spare the extra direct PG connections — see Connection budget below.

Leave it off for development, single-instance deployments, or any environment where the pgbouncer hop is already low-latency and the worker fleet is small.

How it works¶

graph LR
  Worker[Background worker<br/>tier-1+2 eligible] -->|Borrow| Pool[Pinned pool<br/>Size=1..N]
  Pool -->|Direct| PG[(PostgreSQL<br/>port 5432)]

  RegularCode[Application code<br/>+ ineligible workers] -->|Pool acquire| PgBouncer[pgbouncer<br/>port 6432]
  PgBouncer -.->|DISCARD ALL<br/>per txn| PG

  Listener[NOTIFY listener<br/>1 per pod] -->|Direct LISTEN| PG

Eligible workers borrow a connection from the pinned pool for the duration of their transaction, then return it. The connection itself stays open for ConnectionLifetimeSeconds (default 30 min) before being recycled. No DISCARD ALL fires because the connection never re-enters a multi-tenant pool.

Worker eligibility (tiers)¶

Eligibility is tier-driven, not per-worker config. The tiers reflect measured DB pressure, not arbitrary categorization.

Tier 1 — always pinned when enabled¶

Worker	Why it's tier 1
`ClaimWorker`	Hot poll loop — calls `claim_work` + 3× `claim_orphaned_*` per wake; biggest single DB-time consumer
`OutboxPublishWorker`	Drains outbox work (`fetch_outbox_batch`), publishes, flushes completions
`HeartbeatWorker`	Upserts `wh_service_instances` on its cadence (30 s default, 60 s when the alive-lock is held); always-on
`LeaseRenewalWorker`	Flushes batched `wh_active_streams.lease_expiry` renewals as requests arrive; always-on

Tier 2 — flush channels (opt-in, default on)¶

Controlled by IncludeFlushWorkers (default true).

Worker	What it flushes
`InboxHandlerWorker`	Calls `commit_handler_batch` (the two-tier handler-commit orchestrator)
`OutboxCompletionFlushWorker`	Calls `complete_outbox_published` for completed sends
`PerspectiveCompletionFlushWorker`	Calls `process_perspective_event_completions` for projected events
`FailureFlushWorker`	Calls `report_failures` for batched failure rollups

Not eligible¶

These workers do not borrow from the pinned pool — pinning them would cost a connection slot for marginal benefit.

TransportConsumerWorker, ServiceBusConsumerWorker — transport I/O dominates; DB time is a small share.
PerspectiveMigrationWorker, OrphanInboxJanitor — one-shot at startup.
MaintenanceWorker, BackupTickCoordinator, WhizbangShutdownService, IdleActivityTouchHookBinder — periodic at minute-scale or lifecycle-only.
DeadLetterRecoveryWorker, RecentlyProcessedEventCacheSweepWorker — periodic at minute-scale, marginal benefit.

Opting in a custom worker¶

Consumer code (typically inside a service that defines its own background worker) can add custom workers to the eligibility set:

services.AddHostedService<MyBulkImportPollingWorker>();
services.AddPinnedWorker<MyBulkImportPollingWorker>();

The opt-in is additive; the tier-1+2 defaults remain in effect. Use ExcludeWorkers (below) to remove a tier-default worker by short name.

Configuration¶

The options class is WhizbangPinnedPoolOptions. The registration helper takes a configure callback so the consumer can bind from any configuration source — typically Whizbang:Workers:PinnedPool in appsettings.json / env vars (kept consistent with devops conventions), but the binding itself happens in the consumer (matches the pattern used by AddWhizbangAzureBlobOffload and keeps Whizbang.Core AOT-clean).

Property	Type	Default	Description
`ConnectionStringName`	string?	`null`	Preferred. Name of a key under standard `ConnectionStrings:*` configuration to resolve the direct conn string from (e.g. `"appservice-db-direct"`). Wins over `ConnectionString` when both are set.
`ConnectionString`	string?	`null`	Inline direct (non-pgbouncer) PG conn string. Fallback when `ConnectionStringName` is unset or unresolved. At least one of the two sources must resolve for the pool to take effect.
`Enabled`	bool	`false`	Master switch. `false` → workers stay on pgbouncer.
`Size`	int	`1`	Number of pinned connections held open.
`IncludeFlushWorkers`	bool	`true`	Whether tier-2 flush workers also pin.
`ExcludeWorkers`	IList<string>	`[]`	CLR short names (no namespace) of workers to skip. Escape hatch.
`ConnectionLifetimeSeconds`	int	`1800`	Auto-recycle a connection after this many seconds.
`BorrowTimeoutMilliseconds`	int	`5000`	How long a worker waits to borrow before throwing.

Connection-string source: name vs inline¶

The pool resolves its connection string in this order:

ConnectionStringName — looked up via IConfiguration.GetConnectionString(name). Recommended for production because it keeps the secret-bearing string under standard ConnectionStrings:* configuration alongside the rest of the app's database secrets — key-vault, env-var, and log-redaction conventions all apply uniformly.
ConnectionString — used when ConnectionStringName is unset OR the named key doesn't resolve. Convenient for tests / dev rigs / single-line registration.
Neither set → silent no-op (pool returns NoOpPinnedConnectionPool, workers stay on pgbouncer). Matches Enabled=false behaviour so the registration is safe to call unconditionally.

When configuring a real service, the typical pattern is a sibling under ConnectionStrings:*:

// appsettings.json
{
  "ConnectionStrings": {
    "appservice-db":        "Host=…;Port=6432;…", // pgbouncer (existing)
    "appservice-db-direct": "Host=…;Port=5432;…"  // direct (new)
  },
  "Whizbang": {
    "Workers": {
      "PinnedPool": {
        "Enabled": true,
        "ConnectionStringName": "appservice-db-direct"
      }
    }
  }
}

Minimal registration¶

services.AddWhizbangPinnedWorkerPool(opts => {
  // Bind everything from the configured section — both ConnectionStringName
  // and any other settings come through this single call.
  builder.Configuration.GetSection("Whizbang:Workers:PinnedPool").Bind(opts);
});
// Swap the default no-op pool for the PostgreSQL implementation
// (from Whizbang.Data.Postgres; call AFTER AddWhizbangPinnedWorkerPool).
services.AddWhizbangPostgresPinnedPool();

AddWhizbangPinnedWorkerPool alone registers only the options, the eligibility registry, and a NoOpPinnedConnectionPool default. The PostgreSQL-backed pool is swapped in by AddWhizbangPostgresPinnedPool() (in Whizbang.Data.Postgres), which is safe to call unconditionally — it leaves the no-op in place when Enabled=false or no connection string resolves.

The simpler "everything in code" shape, when you don't want config-driven values (test/dev):

services.AddWhizbangPinnedWorkerPool(opts => {
  opts.ConnectionString = builder.Configuration.GetConnectionString("WorkerDirect");
  opts.Enabled = true;
});
services.AddWhizbangPostgresPinnedPool();

Tuning size¶

services.AddWhizbangPinnedWorkerPool(opts => {
  opts.ConnectionString = builder.Configuration.GetConnectionString("WorkerDirect");
  opts.Enabled = true;
  opts.Size = 2;                                // splits ClaimWorker from flush workers
  opts.IncludeFlushWorkers = true;
});

Surgical exclusion¶

services.AddWhizbangPinnedWorkerPool(opts => {
  opts.ConnectionString = builder.Configuration.GetConnectionString("WorkerDirect");
  opts.Enabled = true;
  opts.Size = 1;
  opts.ExcludeWorkers = new[] { "HeartbeatWorker" };  // investigating heartbeat under pinning
});

Connection budget¶

The total direct PG connections per pod increases by Size. Sizing math:

direct_pg_conns_per_pod = 1 (LISTEN)
                       + Size (pinned pool)
total_direct_pg_conns   = num_pods × direct_pg_conns_per_pod

At Size=1 and 50 pods, that's 100 direct PG conns plus whatever pgbouncer is using. Match this against your PostgreSQL max_connections and pgbouncer's default_pool_size. If you have headroom for more direct conns, raising Size is reasonable.

Size=1 is the right starting point. Raise to 2 if you observe pinned-pool borrow timeouts in logs (worker queuing on a single wire is hurting flush latency).

Validation¶

The registration extension validates Size > 0 at startup when Enabled=true and an inline ConnectionString is set — catching the "Size left at 0 while enabled" misconfiguration before it silently degrades to pgbouncer overhead. If neither ConnectionStringName nor ConnectionString resolves to a non-empty string, the feature silently no-ops — workers continue using pgbouncer. This is intentional: it lets you ship the registration code into all environments and toggle on per-environment via configuration.

Observability¶

Three metrics surface the pinned pool's behaviour (meter Whizbang.Workers.PinnedPool):

Metric	Type	Tags	What it shows
`whizbang.workers.pinned_pool.borrow.duration`	histogram (ms)	`worker`	Time from borrow request to connection handed out — should be near-zero in steady state.
`whizbang.workers.pinned_pool.borrow.timeouts`	counter	`worker`	Borrow-timeout count — if non-zero, raise `Size` or investigate which worker is holding too long.
`whizbang.workers.pinned_pool.connection_recycles`	counter	—	Connection recycles driven by `ConnectionLifetimeSeconds`.

Pair these with pg_stat_statements to confirm the post-enable DISCARD ALL share has dropped.

Migration from un-pinned¶

The feature is purely additive — no code changes required in workers. Worker classes don't know they're being pinned; the borrow is plumbed through the existing IWorkCoordinator resolution path. Toggling Enabled between true and false is safe at any time (a rolling deploy is sufficient — no migration window required).

Worker classification — which workers are NOTIFY-driven, channel-driven, or timer-driven.
Instance liveness — the direct LISTEN connection and the advisory-lock liveness signal.
PerspectiveWorker NOTIFY wake — the LISTEN/NOTIFY consumer side.