pg_net Churn Reduction (#3121)

pg_net’s own response-table cleanup was the single most expensive statement on the prod Supabase DB. This runbook documents the root cause, the fix shipped in migration 321_reduce_pgnet_churn, and the one-time bloat reclaim that must be run by hand.

Symptom

From pg_stat_statements on prod (vuvwmfxssjosfphzpzim):

Statement	Calls	Total time	Mean
WITH rows AS (SELECT ctid FROM net._http_response WHERE created < now() - $1 ORDER BY created LIMIT $2) DELETE ...	152,471	1,782.0 s	11.69 ms
SELECT net.http_post(...)	164,335	906.9 s	5.52 ms

~2,690 s combined — more than every application query put together. And net._http_response held only ~360 live rows but occupied 49 MB (heavy insert/delete bloat), which is why each cleanup DELETE cost 11.69 ms.

Root cause

Volume. Exactly one cron job POSTs via pg_net on a hot cadence: email-notify-sweep (installed by migration 125_email_notify_sweep_cron), scheduled * * * * * — every minute = 1,440 http_post/day. Over ~114 days that is ~164k calls, matching the observed 164,335 exactly. account-deletion-sweep (jobid 2) runs once daily and is negligible; the other cron jobs (app_logs_retention, purge-tax-exemption-data-nightly, smoke-account-daily-topup) do not use pg_net at all.

The sweep is only a backstop for the convert-by-email pipeline. The happy path is the inline .finally hook in /internal/queue-convert (see workers/api/src/services/email-notify.ts → sweepPendingResultEmails); the sweep merely catches the rare case where the isolate was killed before the inline send. The API side already enforces a 60s olderThanMs floor, so polling every minute buys almost nothing.

Cleanup cost + bloat. pg_net (0.19.5 on prod) runs the response cleanup DELETE opportunistically on every background-worker iteration, governed by pg_net.ttl. Prod default was 6 hours, so six hours of once-a-minute responses stayed hot and, with the insert/delete churn, bloated the table to 49 MB. The DELETE’s ORDER BY created LIMIT scan pays for that bloat on every call.

Fix — migration 321 (schedule + config)

Shipped in supabase/migrations/20260924000000_320_reduce_pgnet_churn.sql:

1. Cut volume 5x. Re-schedule email-notify-sweep from * * * * * to */5 * * * *. 1,440/day → 288/day. Common-case email latency is unaffected (inline hook sends immediately); worst-case backstop latency for an isolate-killed conversion rises from ~1 min to ~5 min — fine for a rare retry net.
2. Shrink the response table. ALTER DATABASE postgres SET pg_net.ttl = '1 hour' (from 6h) so rows are trimmed sooner and the live set — and thus every cleanup scan — stays small. ALTER DATABASE persists across restarts and applies to new pg_net worker connections without touching the extension-owned net._http_response table. The statement is wrapped in a DO block that degrades to a NOTICE if the role lacks the grant (e.g. on a local stack) so the always-applicable cron change still lands.

pg_net version targeted: 0.19.5 (pg_net.ttl and pg_net.batch_size are the config surface; there is no separate cleanup-interval GUC — cleanup runs inside the worker loop, so ttl is the lever that controls table size).

Applying

This migration is NOT applied to prod by the change that introduced it — it is a version-controlled deliverable only. Apply it in a maintenance window:

-- via CI migration runner, or manually as the postgres role
\i supabase/migrations/20260924000000_320_reduce_pgnet_churn.sql

-- confirm
SELECT jobname, schedule FROM cron.job WHERE jobname = 'email-notify-sweep';
-- expect: */5 * * * *
SHOW pg_net.ttl;   -- new worker connections; may need a moment to pick up
SELECT name, setting FROM pg_settings WHERE name = 'pg_net.ttl';

If ALTER DATABASE was skipped (privilege), set the TTL manually as the postgres/superuser role. On PG15+ you can also grant the parameter:

ALTER SYSTEM SET pg_net.ttl TO '1 hour';
SELECT pg_reload_conf();
-- or, per-DB (preferred, matches migration 321):
ALTER DATABASE postgres SET pg_net.ttl = '1 hour';

One-time bloat reclaim (manual — step C)

The 49 MB of existing bloat is NOT reclaimed automatically. After the volume and TTL changes are live and the live-row count has dropped (give it > 1h so the shorter TTL has trimmed the backlog), reclaim it in a low-traffic window. VACUUM FULL cannot run inside a migration transaction and takes an ACCESS EXCLUSIVE lock (briefly blocks pg_net writes), so run it by hand:

-- check size + live rows first
SELECT pg_size_pretty(pg_total_relation_size('net._http_response')) AS total,
       (SELECT count(*) FROM net._http_response) AS live_rows;

-- reclaim (fast — the table should be tiny by now; ACCESS EXCLUSIVE lock)
VACUUM FULL net._http_response;

-- verify it shrank
SELECT pg_size_pretty(pg_total_relation_size('net._http_response')) AS total;

Prefer pg_repack (installed, v1.5.2) if a lock-free reclaim is required while pg_net is actively serving requests:

-- from a shell with pg_repack client installed
pg_repack -d postgres -t net._http_response

Expected impact

• net.http_post volume: ~5x lower (1,440 → 288/day).
• Resident _http_response rows: ~360 → ~60 (1h TTL vs 6h).
• After the reclaim, the cleanup DELETE scans a small unbloated heap, so its per-call cost and its share of instance DB time fall from the #1 line item to a rounding error.

Monitoring after the change

-- http_post + cleanup cost trend (reset the view first to measure a clean window)
SELECT calls, total_exec_time, mean_exec_time, left(query, 60) AS q
FROM pg_stat_statements
WHERE query ILIKE '%net._http_response%' OR query ILIKE '%net.http_post%'
ORDER BY total_exec_time DESC;

-- sweep is still doing its job (rows should almost always be processed: 0)
SELECT * FROM cron.job_run_details
WHERE jobname = 'email-notify-sweep'
ORDER BY start_time DESC LIMIT 20;

If the every-5-minute cadence proves too slow for the convert-by-email backstop in practice, */2 * * * * is a reasonable middle ground; do not return to * * * * *.