Skip to content

PostgreSQL connection pool configuration

Problem

Both apps used pg.Pool with zero explicit configuration — relying on library defaults (max 10, no idle timeout, no connection timeout). On Vercel serverless:

  • Cold-start bursts can exhaust the default 10-connection pool silently
  • Idle connections severed by Neon/PG without a handler cause unhandled 'error' events, crashing the process
  • No connection timeout means requests hang indefinitely if the pool is exhausted

Evidence:

Proposal

  1. Add explicit pool options: max, idleTimeoutMillis, connectionTimeoutMillis
  2. Drive values from environment variables (PG_POOL_MAX, PG_POOL_IDLE_TIMEOUT_MS, PG_POOL_CONNECTION_TIMEOUT_MS) with sensible defaults
  3. Attach pool.on('error') handler to both apps logging structured JSON
  4. Add env vars to Turbo passthrough and .env.local.template file.

Acceptance criteria

  • Pool options configurable via env vars with defaults
  • pool.on('error') handler prevents unhandled crashes
  • Env vars documented in .env.local.template
  • Turbo env array updated
  • npm run check passes (typecheck verified)
  • Existing DB tests pass

Hardening pass (2026-07-09)

Follow-up review flagged duplication/drift, ad-hoc logging, missing serverless tuning, and thin test coverage. Implemented:

Shared pool factory

  • New subpath export @open-learning-hub/server/pg (packages/server/src/pg/pool.ts): createPgPool + resolvePgPoolOptions centralize defaults, clamping (max 1..100, timeouts/maxUses preserve a legitimate 0), and the bound 'error' handler. pg declared as a peerDependency.
  • LMS (dialect.ts / client.ts) and CMS (db/index.ts) consume the shared factory instead of hand-rolling the pool.

Config correctness + serverless tuning

  • CMS pool inputs are now clamped by the shared factory (previously raw Number(x) || default, which discarded a legitimate 0).
  • Added PG_POOL_ALLOW_EXIT_ON_IDLE (default true) and PG_POOL_MAX_USES (default 7500) for Fluid Compute / Neon-pooler resilience; documented in both .env.local.template, turbo.json, and the README pooling section.
  • Migration scripts open a dedicated tiny pool (max: 2) against the unpooled endpoint (lms db-migrate.ts, cms migrate.ts).

Logging

  • Pool errors route through the canonical redacting logger where available (apps inject @/lib/log); the shared factory falls back to a framework-agnostic structured console error line (see .cursor/rules/091-structured-logging.mdc).

Vercel

  • regions pinned + functions.maxDuration set in both apps/lms/vercel.json and apps/cms/vercel.json.
  • Verified via vercel env ls: DATABASE_URL + DATABASE_URL_UNPOOLED present for olh-lms (Prod/Preview/Dev) and olh-cms (Prod/Preview); DB_DRIVER/STORAGE_DRIVER/BLOB_READ_WRITE_TOKEN set for CMS. PG_POOL_* intentionally left unset (code defaults are the baseline).

Tests

  • packages/server: pg factory unit tests (clamping, 0 preservation, onPoolError invocation, default structured-log fallback).
  • LMS: env() PG_POOL_* parse/clamp tests + dialect.ts postgres option pass-through.
  • CMS: postgres pool-wiring test asserting parsed PG_POOL_* forwarding.

Out of scope

  • Connection retry/backoff logic (would require app-level wrapper around Kysely)
  • Switching to @neondatabase/serverless driver (separate evaluation)
  • Connection validation query on checkout (pg doesn't support this natively without a wrapper)

Notes / decisions log

  • 2026-07-07: Proposed. Planned defaults mirror pg library defaults (max=10, idle=30s, conn=5s) to avoid deployment drift. CMS may continue reading pool vars directly so runtime validation stays feature-scoped.
  • 2026-07-09: Hardening pass. Extracted a shared @open-learning-hub/server/pg factory (consumed by LMS + CMS + migration scripts), added serverless tuning (allowExitOnIdle, maxUses), routed pool errors through the canonical logger, pinned Vercel regions/maxDuration, verified Neon env wiring via vercel env ls, and backfilled unit tests. CMS pool inputs are now clamped through the shared factory (superseding the original "CMS reads directly" allowance).