On this page
Pool sizing#
- Default server cap is 100 concurrent connections (
--max-connections/PLOMID_MAX_CONNECTIONS). The engine serializes mutations behind a single writer, so connections past the low hundreds queue rather than add write throughput. - Size pools to concurrent active writers, not total clients. A pooler (PgBouncer-style) in front multiplexes many application clients onto fewer server connections.
- Budget worst-case memory as
max_connections × max_copy_buffer_bytes(defaults: 100 × 64 MiB ≈ 6.4 GiB if every connection COPYs at once — the realistic mix is far lower, but the product is the bound that must fit). - Watch the
connection_rejectedwarning: it means the pool is too small or the cap too low.
Timeouts#
| Setting | Default | Purpose |
|---|---|---|
statement_timeout |
30s | Kills runaway statements; override per session with SET for analytics. |
idle_in_transaction_session_timeout |
60s | Closes transactions a client abandoned mid-way, releasing locks. |
SET statement_timeout = '120s'; -- long analytics in this session onlyTighten globally, loosen per session — never disable server-wide because one workload wants it.
Retries#
Retry safely on: connection loss before commit, statement timeouts on idempotent reads, serialization-style conflicts after rollback. Rules:
- Roll back the failed transaction first — a failed statement aborts it; continuing on the same transaction fails.
- Retry idempotent operations only. Make writes idempotent with natural keys +
ON CONFLICT DO NOTHING / DO UPDATE. - Bound retries (count + backoff + jitter); log and surface after exhaustion.
- Never retry
CREATE/DROP DATABASEinside a transaction — those statements cannot run in a transaction block at all.
Migrations#
- Apply schema changes as ordered, idempotent migration files (explicit
CREATE TABLE IF NOT EXISTS, additiveALTER, backfill, then constrain). UPDATE…FROMandCOPYare unsupported inside explicit transactions — run those steps in autocommit.- Vector index creation (
USING hnswet al.) must also run in autocommit. - Test every migration against a copy first; keep a cold backup before production migrations.
Startup and shutdown#
- On startup: connect, run
SELECT version();plus a cheap catalog probe, then open the pool. Fail fast if the probe fails. - On shutdown: stop accepting work, drain in-flight requests, close the pool (
pool.end()/pool.close()), then exit. Idle-in-transaction timeouts bound how long a drain can stall.
Error mapping#
Permission denials arrive as SQLSTATE 42501, unknown databases as 3D000, TLS refusals as 28000, timeouts/cancels as 57014, aborted transactions as 25P02. Full table: Errors. Map these to typed application errors once, at the boundary — not inline at every call site.
Was this page helpful? Thanks — noted locally, nothing is sent anywhere.