0 releases 36 tags

RSS feed

• v1.7.3 736d376858

  Compare

  fix(db): raise pgxpool MaxConns + PG max_connections + set PG resources (#1221)

  Ghost released this 2026-04-28 15:21:24 -04:00 | 130 commits to main since this release

  Summary

  Follow-up to #1215 and #1220. Both of those addressed individual slow
  queries; this PR addresses concurrency — under today's scraper load
  the cursor query is fast on average (mean 42ms) but the connection pool
  saturates and queue depth blows up at the Go HTTP layer.

  Diagnosis

  pg_stat_statements (added in #1215) made this possible to see:

  max_ms   mean_ms  calls    template
  10,755     41.8   94,009   plain cursor pagination, no filter
   7,277    214.7   14,770   ILIKE substring filter
   7,638    330.8    7,656   ILIKE substring + is_latest filter
  

  Mean times are healthy. But max_exec_time of 7–10s on the cursor
  query, combined with sustained ~15 req/s from scrapers (ServiceNow's
  148.139.x.x range, anonymous node user-agent, etc) saturates
  MaxConns=30 × 2 pods = 60. New requests queue at the Go HTTP layer;
  nginx-side p99 hits 35s; eventually scrapers time out at 60s, retry, and
  amplify the queue.

  Today's two ongoing alerts are both this pattern:

  • 17:35–17:40 UTC Availability dropped below 95% — 4,526 GET requests
    in 5 min, hundreds of 504s
  • 18:17 UTC Publish Endpoint Latency re-fire — same scraper
    concurrency dragging the publish path

  Critically, the symptoms today were also visible during yesterday's
  incident, but yesterday's broken cursor (#1215) was the dominant cause.
  After #1215 the cursor is fast individually; concurrency now becomes the
  next bottleneck.

  Changes

  pgxpool (internal/database/postgres.go)

  Setting	Before	After
  MaxConns	30	60 per pod
  MinConns	5	10 per pod
  MaxConnIdleTime	30 min	(unchanged)
  MaxConnLifetime	2 h	(unchanged)

  2 pods × 60 = 120 total app connections. Cuts queue depth roughly in
  half at current scraper load.

  PG cluster (deploy/pkg/k8s/postgres.go)

  • max_connections: 100 → 200 — required to support the larger pool.
    120 (app) + ~10 (PG internals: autovacuum, replication, admin) + 70
    headroom.
  • Added explicit resources: block — previously the pod had no resource
    limits, making node-level OOM behaviour unpredictable.

  	Request	Limit
  memory	512Mi	4Gi
  cpu	200m	1500m

  Resource budget

  Node	Now	After
  dy89 (PG node)	39% mem (~2.4 GiB / 6 GiB)	~65% mem worst-case
  (~3.9 GiB)
  2yxm	36% mem	unchanged

  Both nodes well within capacity. CPU usage <50% on both, plenty of
  headroom.

  PG worst-case memory math:

  • 200 conns × ~15 MB per backend = 3 GiB
  • • shared_buffers 128 MiB
  • • maintenance_work_mem, OS overhead, etc.
  • ≈ 3–4 GiB total

  Deployment caveat

  max_connections is a postmaster-level setting → CNPG triggers a PG
  restart on the change. Same in-pod restart shape as the
  pg_stat_statements deploy yesterday — registry pods see ~30s of DB
  unavailability, covered by v1.7.1's retry-with-backoff (8 attempts, 1→8s
  capped). One registry pod may bounce once before recovering, like
  yesterday.

  Time the merge for a low-traffic UTC window.

  Order of operations matters within the deploy itself:

  1. Pulumi applies the new CNPG spec → PG restarts with
     max_connections=200
  2. Rolling deploy of registry pods picks up MaxConns=60 config
  3. New conns are accepted up to 200 limit

  Pulumi's standard ordering does step 1 before step 2 in this scenario
  (Pulumi resource graph: CNPG cluster precedes Deployment). If for any
  reason it doesn't, the worst case is too many connections errors
  during a small window — Self-correcting once the rollout completes.

  Test plan

  • go build ./... clean for app + deploy
  • make lint clean
  • go test -race ./internal/database/... green
  • On merge: staging deploy applies the spec change cleanly; PG
    restarts; pool reaches 60 max
  • On prod deploy: same; verify no too many connections errors
    during the rollout window
  • After deploy: query SHOW max_connections returns 200; query
    pg_stat_statements after a scraper burst, confirm max_exec_time for
    cursor query no longer hits 10s

  Out of scope

  • ILIKE substring search (server_name ILIKE '%foo%') is
    unindexable. Three pg_stat_statements variants run with means 141–333ms
    and max 4–7s. Worth replacing with a pg_trgm GIN index or full-text
    search in a separate PR.
  • Per-IP nginx rate limiting — defends against scraper retry storms
    regardless of pool size.
  • Cache-Control on /v0/servers — let nginx absorb
    scraper-repeated cursors.
  • Pre-existing superfluous WriteHeader warnings — separate Huma
    framework issue.

  🤖 Generated with Claude Code

  Co-authored-by: Claude Opus 4.7 (1M context) noreply@anthropic.com

  Downloads

  • Source code (ZIP)
    0 downloads
  • Source code (TAR.GZ)
    1 download