Plastic Labs Internal Wiki

Recently Updated

Connection-Saturation Monitoring (Supavisor / shared Postgres)

6 min read

Connection-Saturation Monitoring

Legacy. This page covers the Supavisor pooler in front of a shared, schema-isolated Postgres database. Production moved to AlloyDB with per-tenant databases and managed connection pooling on 2026-07-22. The failure mode there is different, because pooling cannot cross databases. See alloydb-connection-saturation for current guidance. Keep this page for older incidents and for the lock_monitoring.sql diagnostics, which still work against any Postgres.

A pooler-proof, always-on monitor for the connection-saturation incidents on the shared (schema-isolated) Honcho database behind the Supavisor transaction pooler — where client connections spike from a steady ~300 to the 9,000 EMAXCONN ceiling and the pool collapses.

Download the full script: lock_monitoring.sql — install + diagnostic runbook in one file. This page explains it; the file is the source of truth.

Related: honcho-debugging · architecture

When to use this

  • Supavisor client connections are climbing toward 9,000 / you’re seeing EMAXCONN (“max client connections reached”).
  • A tenant-facing outage where “everything is locked up” and you can’t even open a psql session to investigate.
  • Postmortem: you need to know what the database was doing during a past spike.

The whole point: you cannot run ad-hoc diagnostics during a spike — your client can’t get a pooler slot, it’s competing with the avalanche. This monitor records the evidence continuously, from inside Postgres, so it keeps working straight through a collapse.

Why pg_cron (and what it can / can’t see)

The samplers run as pg_cron jobs, which execute in Postgres background workers — they do not go through Supavisor. The server side (the ~360 backends Supavisor opens) is never the thing that saturates, so there’s always room to run. That’s why this keeps recording at 9,000 client connections when nothing else can connect.

LayerWhat saturatesVisible to this monitor?
Client side (Supavisor)the 9,000 figure — app connections queued on the pooler❌ Not directly (lives in Supavisor’s own metrics)
Server side (Postgres backends)~360 backends; why they stall✅ Yes — this is where the cause shows up

So this monitor shows you the cause (which backends are stuck, on what, and whether the lock table is filling) — the early-warning signal that precedes the client-side avalanche. For the client-side count you still need Supavisor’s Prometheus metrics.

What it installs

Everything lives in the public schema (empty on tenant DBs — Honcho’s tables live in the per-tenant DB.SCHEMA), prefixed mon_:

  • mon_conn_samples — connection-state breakdown over time (the “is it tipping?” timeline)
  • mon_activity_samples — detailed non-idle / waiting backends (who’s stuck, on what, blocked-by whom)
  • mon_lock_samples — lock-table pressure (catches the “out of shared memory” precursor)
  • mon_stmt_samples — pg_stat_statements snapshots (query-level cost attribution)

Three pg_cron jobs: a 5-second sampler, a 1-minute statement snapshot, and a 10-minute retention prune (7-day window). Full DDL + schedule in lock_monitoring.sql, §1–§2.

Cost / safety: negligible — the samplers read in-memory views (pg_stat_activity, pg_locks) and the tables self-prune to 7 days. Prefer the '5 seconds' schedule over a self-looping minute job (a pg_sleep loop holds one transaction open ~56 s/min and pins the xmin horizon).

Quick start

  1. Enable pg_cron (and pg_stat_statements) in the Supabase dashboard → Database → Extensions.
  2. Run lock_monitoring.sql §1–§2 via the SQL editor or a session-pooler (port 5432) connection.
  3. Verify it’s recording: 
    SELECT jobname, schedule, active FROM cron.job WHERE jobname LIKE 'mon-%';
SELECT jobname, status, start_time FROM cron.job_run_details
WHERE jobname LIKE 'mon-%' ORDER BY start_time DESC LIMIT 10;

Diagnostic runbook (run after a spike)

Connect via the session pooler (5432), not the transaction pooler. Start with Q0 to find the window, then set w.lo/w.hi in the rest. Full set is in lock_monitoring.sql §4 — the two that matter most:

Q0 — find the spike window (search a wide range; don’t pre-bias it):

SELECT date_trunc('minute', sampled_at) AS minute,
       max(s.total) AS peak_backends, max(s.lock_waiters) AS lock_waiters,
       max(s.idle_in_txn) AS idle_in_txn, max(s.active) AS active
FROM (
  SELECT sampled_at,
         sum(backend_count) total,
         sum(backend_count) FILTER (WHERE wait_event_type='Lock') lock_waiters,
         sum(backend_count) FILTER (WHERE state='idle in transaction') idle_in_txn,
         sum(backend_count) FILTER (WHERE state='active') active
  FROM public.mon_conn_samples
  WHERE sampled_at BETWEEN '<start>' AND '<end>'
  GROUP BY sampled_at
) s GROUP BY 1 ORDER BY peak_backends DESC LIMIT 30;

Q-idle — what’s holding transactions open (the idle-in-transaction culprits, by the last query they ran before going idle):

SELECT left(query, 90) AS last_query_before_idle,
       count(*) AS samples,
       max(xact_age) AS longest_held,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY xact_age) AS median_held
FROM public.mon_activity_samples
WHERE state = 'idle in transaction'
GROUP BY 1 ORDER BY longest_held DESC LIMIT 30;

How to read it (decision tree)

  1. Q1 timeline shape (mon_conn_samples over the window): which column rises first?
    • sharp active burst (e.g. 15 → 250 in one second), advisory ~0 → synchronized poll herd (deriver claim queries firing fleet-wide in lockstep).
    • Lock/advisory climb → genuine lock contention.
    • wall of idle in transaction → transactions held open (see below).
    • a ~30-second sawtooth → the no-jitter deriver poll herd, worsened by a mass restart re-phasing the fleet.
  2. Q2 top statement (mon_activity_samples grouped): active on the queue … work_unit_key claim → deriver poll; Lock/advisory on INSERT … messages → the message advisory lock; idle in transaction with large longest_txn → a held transaction.
  3. Q3 lock table vs ceiling (mon_lock_samples vs max_locks_per_transaction × (max_connections + max_prepared_transactions)): peak near the ceiling → lock-table exhaustion (the “restart fixes it” signature) → raise max_locks_per_transaction.
  4. Q-idle: ordinary CRUD (INSERT session_peers, INSERT messages) sitting idle-in-transaction for minutes, with no external call between query and commit, means transactions held open across event-loop suspensions under overload (and/or our session layer beginning the transaction eagerly).

Worked example — the 2026-06-02 outage

A ~4.5-hour collapse (client connections 300 → ~9,000, onset ~02:10 ET) was diagnosed entirely from these tables:

  • Trigger: mon_conn_samples showed active backends slamming from ~15 to 251 of ~368 in single seconds, all running the deriver queue-claim query across many tenant schemas at once — a synchronized poll herd (no jitter on the 30s poll + the auto-upgrade restarting instances in lockstep).
  • Not locks: advisory waits were ~0; mon_lock_samples peaked at 2,272 vs a 32,000 ceiling — both lock hypotheses ruled out.
  • Idle-in-transaction: Q-idle showed core write-path CRUD held idle 1–2 min with no external call — transactions held open across event-loop starvation, widened by the HonchoAsyncSession eager BEGIN.

Fixes that followed: jitter the deriver poll + fail-fast connection acquisition (deriver-polling-resilience spec), a partial index on unprocessed queue rows, and pacing the rollout (and not recovering by full DB restart, which re-synchronizes the fleet).

Server-side settings worth requesting from Supabase

Not part of the script, but they make the next incident legible (all but the last are SIGHUP, no restart):

ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET max_locks_per_transaction = 256;  -- from 64; raises the tipping point (RESTART)
SELECT pg_reload_conf();

These feed the Postgres logs already shipped to Logflare. Consider auto_explain (auto_explain.log_min_duration = '100ms') to capture real query plans under load.

Teardown

SELECT cron.unschedule('mon-db-state-sampler');
SELECT cron.unschedule('mon-stmt-sampler');
SELECT cron.unschedule('mon-db-state-retention');
DROP FUNCTION IF EXISTS public.mon_sample_db_state(), public.mon_sample_statements();
DROP TABLE IF EXISTS public.mon_conn_samples, public.mon_activity_samples,
                     public.mon_lock_samples, public.mon_stmt_samples;

Graph View