Skip to content

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.

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 pools

Each 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 worker

Task 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 attribute

Workers 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.

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 SecretStr
import 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.

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.

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 stays CLAIMED until 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,
),
)

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 0 to use the operating system default.

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.