Remote PostgreSQL
Remote PostgreSQL
Section titled “Remote PostgreSQL”Running workers against a remote PostgreSQL — a managed provider or a
database in another network — changes two constraints that are invisible
locally: every connection counts against a server-side cap, and every
statement pays the network round trip. This page covers sizing workers
against max_connections, running multiple workers through a transaction
pooler, and the configuration that recovers throughput at high RTT.
Connection Budget
Section titled “Connection Budget”Each worker holds, under load:
pool max_size # pool of the worker process+ 2 # LISTEN connections (tasks, ping)+ 1 # runner heartbeat connection+ isolated children × isolated.pool max_size # isolated child pools+ shared async children × shared_async.pool max_size # shared async child pools+ shared thread children × shared_thread.pool max_size # shared thread child poolsEach max_size is the resolved value: a PoolSize, or the size that
PoolSizeFromSeats() derives from the seats of a shared child or, for the
pool of the worker process, of the profile (see
Sizes Derived from the Seats).
At defaults (a pool of the worker process of 5 for 8 seats, an isolated pool
max_size of 1) with a profile of 8 isolated children:
5 + 2 + 1 + 8 × 1 = 16 connections per workerTask code that sends through the app adds the task_send_pool of its child
kind (default 2) in its child for each event loop that it sends from:
8 × 2 = 16 more with one sending loop per child, 32 in all. See
Worker Profiles.
The pool of the worker process writes the outcome of each task. At a high RTT
each outcome holds a connection longer, so the worker process needs more
connections for the same rate: set
WorkerProfile(pool=ParentPool(max_size=PoolSize(connections=N))). See
The Pool of the Worker Process.
Producers hold their own engine pools of pool_size + max_overflow
connections each (default 5 + 10): one in the resource set of each event loop
that makes broker calls, one in the set of the sync runner loop for sync
calls, and the sync send pool for sync sends without an idempotency key. A
producer that sends only with send() can open 15 + 15 = 30 at the
defaults. Each set adds 2 listener connections when it waits for a result,
and one more pool of pool_size + max_overflow when session_database_url
differs (see
Connection Ceiling).
Size against the server’s effective capacity, not its nominal
max_connections: subtract superuser-reserved slots and any connections
held by the provider’s infrastructure. A managed instance with
max_connections=50 and 3 reserved slots fits two workers of 8 isolated
children on direct connections (2 × 16 = 32), not three (48). When
their task code sends, each worker can open 16 more, and one worker fits.
When workers exhaust the server’s slots, connection attempts fail with:
FATAL: remaining connection slots are reserved for roles with the SUPERUSER attributeWorkers retry through this, but tasks claimed during the outage wait for recovery instead of completing promptly. If this appears in worker logs, the deployment needs a pooler or fewer direct consumers.
Multiple Workers: Transaction Pooling
Section titled “Multiple Workers: Transaction Pooling”Run multiple workers against a slot-capped server through a transaction-mode
pooler (PgBouncer or the provider’s built-in equivalent). Point
database_url at the pooled endpoint and session_database_url at a direct
endpoint — LISTEN requires a real session and does not survive transaction
pooling:
from pydantic import SecretStrimport os
from horsies import AppConfig, PostgresConfig
config = AppConfig( broker=PostgresConfig( database_url=SecretStr(os.environ['DATABASE_URL_POOLED']), session_database_url=SecretStr(os.environ['DATABASE_URL_DIRECT']), pgbouncer_transaction_mode=True, ),)Direct slots consumed per worker drop to the 2 LISTEN connections;
everything else multiplexes over the pooler’s server pool. See
PgBouncer pool size for
default_pool_size and max_client_conn, and
PgBouncer Troubleshooting for
provider-specific endpoints and failure modes.
Transaction pooling taxes per-worker throughput: a server connection is pinned for the whole transaction, and at high RTT the statements of one transaction arrive a round trip apart, so server connections sit idle in transaction between them. Expect a single pooled worker to run ~25–35% slower than the same worker on a direct connection. The pooler still wins past one worker — it is the only way to run several workers inside a small slot budget.
Prefetch Hides Round Trips
Section titled “Prefetch Hides Round Trips”With prefetch_buffer=0 (the default), a worker claims new tasks only
when a process slot is free, and each claim pass costs a round trip. At 30ms+ RTT this serializes into the task rate and caps
throughput near the number of children in tasks per second, regardless of task duration.
Set a prefetch buffer and claim lease for remote deployments:
from horsies import AppConfig, PostgresConfig
config = AppConfig( prefetch_buffer=24, # claim ahead: 2–3× the children claim_lease_ms=120_000, # required when prefetch_buffer > 0 broker=PostgresConfig(database_url=SecretStr(os.environ['DATABASE_URL'])),)At 33ms RTT this raises drain throughput roughly 2–3× over the default. Soft-cap semantics and fairness trade-offs are covered in Concurrency.
Health Checks at High RTT
Section titled “Health Checks at High RTT”Three settings can check a pooled connection at checkout, at one round trip
each. The two pre-ping settings check a connection only when it waited in the
pool for more than 1 s (PRE_PING_AFTER_IDLE_S). A connection checked in more
recently is used with no check, so a burst of statements pays no checks. The
check of a child pool runs at every checkout.
| Setting | Default | Scope |
|---|---|---|
worker_pool_pre_ping |
False |
the engine of the worker process |
pool_pre_ping |
True |
every other engine: producers, the web UI, task sends from a child |
check of IsolatedPool and SharedPool |
False |
the pool of each child, per child kind in the worker profile |
The worker and its children send most writes as one statement in autocommit, so a check doubles their round trips. At the defaults:
| Write | Sender | Round trips |
|---|---|---|
| Finalize of a plain task that succeeded (attempt, history row, capacity wake) | worker | 1 |
| Failure of a task, a timeout or a crash: the read of the retry facts, then the attempt with the retry or the history row (and the capacity wake of a plain task) | worker | 2 |
| Failure that is never retried: a worker-level failure, a result that does not decode | worker | 1 |
| Success of a workflow task, phase 1 (attempt, history row, phase-2 record of the node) | worker | 1 |
| Phase 2 of a workflow task (node, dependents, workflow completion, capacity wake) | worker | 7 to 12, in one transaction; see Worker Architecture |
Claim pass (horsies_claim) |
worker | 1 |
| Wake of a retry that comes due | worker | 1 |
| RUNNING update of a plain task, with its first runner heartbeat | child | 1 |
| Finalizing marker | child | 1 |
| RUNNING update of a workflow task and the start of its node | child | 4 (BEGIN, update, node, COMMIT) |
Without a check, a connection that died while idle fails the first statement instead of a transparent reconnect:
- The finalize of the worker is a retryable connection error. The worker runs it again on a new connection, and a statement whose reply was lost is classified on that run, so it is not applied twice.
- The claim loop retries a pass that failed with a connection error.
- A RUNNING update of a child ends as
OWNERSHIP_UNCONFIRMED: the task staysCLAIMEDuntil its claim lease returns it to the queue. A failed finalizing marker is logged; the runner heartbeats of the worker cover the row until the worker reads the end of the task.
Turn the checks on for workers that sit idle for long stretches behind NAT or load balancers that kill idle connections, where stale connections occur:
from horsies import ( AppConfig, IsolatedChildren, IsolatedPool, PostgresConfig, SharedAsyncChildren, SharedPool, WorkerProfile,)
config = AppConfig( broker=PostgresConfig( database_url=SecretStr(os.environ['DATABASE_URL']), worker_pool_pre_ping=True, ), worker_profiles={ 'idle_behind_nat': WorkerProfile( isolated=IsolatedChildren(count=8, pool=IsolatedPool(check=True)), shared_async=SharedAsyncChildren( count=2, seats=50, pool=SharedPool(check=True), ), ), },)TCP Keepalives for Idle Pooled Connections
Section titled “TCP Keepalives for Idle Pooled Connections”pool_pre_ping, worker_pool_pre_ping and the check of each child pool are
checkout-time guards: they detect a dead connection before a query runs, not
one that dies in-flight. The pre-ping settings check only a connection that
waited in the pool for more than 1 s, so a connection that dies within 1 s of
its check-in fails its next statement with a retryable connection error.
An idle pooled connection reaped server-side between checkouts surfaces as a
mid-query OperationalError on the next claim or heartbeat — absorbed by the
worker’s retry loop, but logged as noise.
tcp_keepalives (default on) closes this gap at the socket layer. libpq
enables keepalives by default but leaves the idle interval at the OS default
(often 7200s), longer than a pooler’s idle-reap window. Horsies sets
keepalives_idle=30, so a dropped socket is detected within ~30s and recycled
before the next query lands on it. The keepalives apply to the broker engine
pool and each child-process pool; the LISTEN/NOTIFY listener keeps its own.
PlanetScale’s PgBouncer pooler (:6432) reaps idle connections within ~1–2h;
the defaults keep those sockets warm with no configuration. Lower the idle
window only for poolers that reap faster than 30s:
config = AppConfig( broker=PostgresConfig( database_url=SecretStr(os.environ['DATABASE_URL']), tcp_keepalives_idle=15, tcp_keepalives_interval=5, ),)TCP User Timeout for Dead Connections
Section titled “TCP User Timeout for Dead Connections”Keepalives probe only an idle connection. When a connection dies without a
reset from the server side and horsies then writes to it (for example, the
first statement on a pooled connection), the sent data stays unacknowledged.
Keepalives do not probe in that state, so the kernel retransmission limit
decides: about 15 minutes on Linux with the default tcp_retries2=15. The
worker process is blocked for that time, and the pool’s 30-second checkout
timeout applies only after the write fails.
tcp_user_timeout_ms (default 30000) sets libpq tcp_user_timeout. Data
that stays unacknowledged for that time closes the connection, so the write
fails within 30 seconds and the pool opens a new connection. The setting
applies to the broker engines, each child-process pool, and the
LISTEN/NOTIFY listener. It has an effect only on systems that support
TCP_USER_TIMEOUT (Linux).
- A long query is not affected: the server acknowledges the sent query while the client waits for the result.
- With keepalives on, an idle dead connection is closed after about 40 seconds instead of 60, because the user timeout also bounds unanswered keepalive probes.
- Set
0to use the operating system default.
Things to Avoid
Section titled “Things to Avoid”Don’t run multiple direct-connection workers against a small max_connections.
Each worker needs its full budget at load; two workers on a 50-slot
instance exhaust it and stall both. Use the pooled setup above.
Don’t enable prefetch without a claim lease.
prefetch_buffer > 0 requires claim_lease_ms; prefetched claims without
a lease have no expiry for other workers to reclaim.
Don’t point session_database_url at the pooled endpoint.
LISTEN registrations vanish when the pooler reassigns the server
connection; workers stop receiving wake notifications and fall back to
polling intervals.
