Postgres connection management: pool, streamed backfills, bounded concurrency, backoff, single-writer lock

[balegas]

Today every backfill opens a fresh Postgres connection (apps/engine/src/engine.rs:1410-1413) and collects the entire matching result set into a Vec<Row> (apps/engine/src/pg.rs:269-276); N concurrent shape creations = N connections + N full result sets in memory. The ingestor reconnect loop retries on a flat 500ms sleep (replication.rs:42-56).

Scope (aligned in discussion):

• Connection pool (e.g. deadpool-postgres) replacing connection-per-backfill; the ingestor keeps its dedicated session (slot reads are session-stateful).
• Streamed backfills: portal/FETCH-based cursor reads, appended to the shape stream in chunks — no full-result-set materialization. Bounds memory per backfill regardless of shape breadth.
• Bounded backfill concurrency: a semaphore on simultaneous snapshots (excess queue; no HTTP-layer admission control for now — explicitly out of scope, revisit under load testing).
• Slow-query protection: kill backfills that produce no first row within a timeout (upstream uses 30s) — protects the pool from index-less predicates.
• Exponential backoff (1s→10s, jittered) for all PG reconnects, replacing flat 500ms.
• Error taxonomy: classify PG errors retryable vs fatal-config (wrong wal_level, bad credentials, insufficient privileges) — fatal errors surface loudly instead of retry-looping. Upstream's db_connection_error.ex is a ready-made list.
• Single-writer guard: pg_advisory_lock(hashtext(slot_name)) held by the ingestor — two engines peeking+advancing the same slot today would silently corrupt.

Out of scope: admission control / 503 shedding (deferred); TLS (tracked separately in its own issue).

🤖 Generated with Claude Code