Most Supabase apps don’t have slow-query logging on, don’t tag queries by route, don’t know the index advisor is a built-in extension, and won’t notice when pg_stat_statements silently evicts a regression. The first incident teaches every lesson at the same wrong moment. This article gives you the prompt to set it up before that incident — and explains the gaps the prompt closes.
The Problem You Don’t Have Until You Have It Badly
Production Supabase project. Something gets slow. A customer messages support. You open the dashboard.
The Query Performance panel shows top queries by total execution time — over the last two years of stats. Half of it is from features you’ve shipped and removed. There’s no Postgres slow query log, because log_min_duration_statement defaulted to -1 and nobody changed it. There’s no way to see which Next.js route a slow query came from, because every PostgREST connection looks identical in pg_stat_activity. The Performance Advisor is flashing yellow on six items you’ve never opened.
So you guess. You EXPLAIN ANALYZE the query that looks slow. You add an index. You ship. The dashboard is faster, but you don’t know whether you fixed the customer’s problem or just one of several. You file a ticket to “set up proper monitoring.” That ticket will not get done until the next incident.
The kind of question your Supabase telemetry should answer in 30 seconds:
- Which route fired the query that hung for 8 seconds at 14:23 UTC?
- Of the top 10 queries by total time, which are real app traffic vs. pg-boss polling vs. Realtime internals?
- Which queries gained more than 2× call volume since last week?
- Which indexes have never been scanned and are dead weight on every INSERT?
- For this specific slow query, does the planner think a new index would help — and by how much?
If you can’t answer those today, you don’t need a metrics product. You need the boring observability primitives turned on, and an AI agent that can read them.
The first incident teaches every lesson at the same wrong moment. The cost of doing it in advance with a prompt that already knows the edges is an afternoon.
What “Setting Up Supabase Observability Properly” Means
Five pieces. None hard. Most teams have zero wired.
1. The right extensions installed. pg_stat_statements is on by default. index_advisor and hypopg are pre-allowlisted but not installed by default — index_advisor takes a slow query and tells you “yes, a new index would drop total_cost from 3,400 to 280, here’s the CREATE INDEX.” Most teams don’t know it exists.
2. Slow query logging enabled across every role. Default is -1, disabled. The setting is log_min_duration_statement, and the Supabase Dashboard does not surface it in any Postgres Config panel. SQL is the only path. The path is not ALTER SYSTEM — that’s blocked. It’s ALTER ROLE on every connection role, gated by Supabase’s supautils extension. Setting it once on postgres will not catch your PostgREST app traffic.
3. Per-route query attribution. PostgREST sessions look identical from the database side — same application_name, same role. When a slow query shows up in pg_stat_statements, you have no way to know which Next.js route fired it. The fix is a pgrst.db_pre_request hook that reads a custom HTTP header on every PostgREST call and writes app:/the/route/path into the session’s application_name. Two pieces: a Postgres function set on the authenticator role, and middleware in your app that injects the header.
4. Structured logging on hot endpoints. Slow query logging tells you which DB call was slow. Route-level structured logging tells you which app code path it came from, how long the whole request took, and what the inputs were. One line at the success path of every cron handler, webhook, and background worker: console.log(JSON.stringify({ event: 'getSchedules_done', duration_ms, items_returned })). Without it, a regression that doubles a cron’s wall-clock time stays invisible until a customer-side symptom shows up.
5. The Supabase MCP authenticated. This is the multiplier. Once Claude can call execute_sql, get_logs, and get_advisors directly against your project, the diagnostic loop collapses. “Show me the top 10 slow queries, then for each one tell me which route it came from, then for each route find the call site in the codebase, then for each call site propose a fix” — one conversation instead of three days of tickets.
The Prompt
Copy this into Claude Code (or any agent with the Supabase MCP attached). Phase 0 forces the agent to discover what’s already wired before recommending changes.
Set up performance observability for this Supabase project so a future
incident can be diagnosed in minutes instead of days.
═══ PHASE 0 — DISCOVER FIRST ═══
Before changing anything, inspect the project and write your findings to
`docs/supabase-observability.md`. Cover:
- Confirm this is Supabase (vs Neon / RDS / Aiven — the supautils path
below is Supabase-specific). Note the Postgres version.
- Framework + router pattern. (We used Next.js 16 App Router with the
proxy middleware; yours may be Pages, Remix, SvelteKit, Astro, etc.)
- Supabase client wrapper file(s). Where the server-side client gets
created, where service-role client gets created, whether the wrapper
is React.cache()'d.
- "Hot endpoints" — list every route that's called from a cron, a
webhook, every page render, or a background job. These are the
structured-logging targets in step 4.
- Whether the Supabase MCP is connected and authenticated. If not, the
agent should ask the user to run `/mcp` before continuing.
- Existing pg_stat_statements.max value, log_min_duration_statement
value, and the route-attribution hook status on the authenticator role.
Then write the implementation plan in the same doc. Stop. I'll confirm
before you implement.
═══ PHASE 1 — IMPLEMENT (after spec approval) ═══
1. EXTENSIONS. Confirm pg_stat_statements is installed. Install
index_advisor and hypopg if absent:
CREATE EXTENSION IF NOT EXISTS hypopg;
CREATE EXTENSION IF NOT EXISTS index_advisor;
Sanity-check by running index_advisor on a trivial select and
confirming it returns a result row (it should, even if cost_after
isn't lower).
2. SLOW QUERY LOGGING — THE SUPAUTILS GOTCHA.
DO NOT use `ALTER SYSTEM SET log_min_duration_statement = ...`.
Supabase blocks it ("permission denied to set parameter"). The
Dashboard Postgres Config UI also does not surface this setting.
The supported path is `ALTER ROLE`, gated by the `supautils`
extension. Apply to ALL FIVE connection roles, not just postgres
(PostgREST goes through `authenticator` and assumes
`authenticated`/`anon`/`service_role` — setting only on postgres
logs admin sessions only):
ALTER ROLE "postgres" SET "log_min_duration_statement" = '1000ms';
ALTER ROLE "authenticator" SET "log_min_duration_statement" = '1000ms';
ALTER ROLE "authenticated" SET "log_min_duration_statement" = '1000ms';
ALTER ROLE "anon" SET "log_min_duration_statement" = '1000ms';
ALTER ROLE "service_role" SET "log_min_duration_statement" = '1000ms';
Verify all five rolconfigs include the setting:
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname IN
('postgres','authenticator','authenticated','anon','service_role');
Test end-to-end: run `SELECT pg_sleep(1.5), 'test' AS marker, now();`
then wait 60-90 seconds (Supabase log ingest has lag) and confirm via
the MCP `get_logs({ service: 'postgres' })` that a line of the form
`duration: NNNN.NNN ms statement: SELECT pg_sleep(1.5), 'test' ...`
appears.
3. PER-ROUTE QUERY ATTRIBUTION via PostgREST's db_pre_request hook.
Write a migration that:
a) creates a `tag_application_name()` SECURITY DEFINER function in
the public schema. Body reads `current_setting('request.headers',
true)::json->>'x-app-route'` and calls `set_config('application_
name', left('app:'||route, 63), true)`. Wrap in `EXCEPTION WHEN
OTHERS THEN NULL` — a failing db_pre_request 5xxs every request.
b) `ALTER ROLE authenticator SET pgrst.db_pre_request = 'public.tag_application_name';`
c) `NOTIFY pgrst, 'reload config';`
App side (Next.js App Router reference):
- In `proxy.ts` / middleware, inject `x-app-route` header on every
inbound request. COLLAPSE UUID SEGMENTS to `[id]` so the tag stays
inside Postgres's NAMEDATALEN cap (63 bytes) AND doesn't leak IDs
into pg_stat_activity logs.
- In the Supabase server client wrapper, forward `x-app-route` via
`global.headers`. Wrap in React.cache() to avoid re-creating the
client per call.
- In the service-role client (used by background workers without a
request context), wrap the `headers()` call in try/catch and fall
through to `app:bg` so background work is also tagged.
Verify by snapshotting `pg_stat_activity` during peak traffic — every
PostgREST-attributed `active` session should have `application_name`
starting `app:`.
4. STRUCTURED LOGGING on hot endpoints.
For every route identified in Phase 0 as called from a cron, webhook,
background worker, or every-page render: add a JSON log line on the
SUCCESS PATH at the end of the handler, of the form:
console.log(JSON.stringify({
event: '<routeName>_done',
duration_ms: Date.now() - startTime,
<key metrics: rows_returned, items_processed, etc>,
}));
THE BUG THAT BIT US: cron-style routes that only log on the FAILURE
path are silent during slow regressions. A route that started taking
31 seconds (out of a 45s Lambda budget) was healthy by HTTP status
for months. A single success-path log would have surfaced it.
Match the log shape to your provider's structured-logging parser
(CloudWatch Logs Insights, Vercel logs, etc) — usually one-line JSON
is correct.
5. RAISE pg_stat_statements.max — REQUIRES RESTART.
Check `tracked_now` vs `pg_stat_statements.max`:
SELECT
(SELECT count(*) FROM pg_stat_statements) AS tracked_now,
current_setting('pg_stat_statements.max')::int AS configured_max;
Default is 5000. If `tracked_now / configured_max > 0.80`, statements
are evicting in LRU order — losing low-volume queries (admin paths,
rarely-fired routes) which is exactly where regressions hide.
Raise via Supabase Dashboard → Database → Configuration → Postgres
Config. THIS SETTING IS POSTMASTER-CONTEXT — requires a database
RESTART, not just a reload. Schedule the restart window with the
user; it drops all in-flight connections. Recommended target: 15000.
6. SUPABASE MCP — confirm Claude can read the data.
Verify these MCP tools work against this project:
- execute_sql (smoke-test with `SELECT 1`)
- get_logs(service: 'postgres')
- get_advisors(type: 'performance')
If get_advisors returns a 60k+ char response (likely on a project
with history), write the top 3 findings inline and spill the rest to
a workspace file rather than dumping into context.
═══ PHASE 2 — VERIFY before shipping ═══
- SELECT confirms log_min_duration_statement on all 5 roles.
- SELECT confirms pgrst.db_pre_request set on the authenticator role.
- Live pg_sleep test query appears in get_logs after ~90s wait.
- pg_stat_activity peak snapshot shows app:* application_name on every
PostgREST session.
- One hot endpoint deployed with the {event, duration_ms} log; verified
in the provider's log viewer.
- get_advisors returns at least one finding (almost always will).
- index_advisor returns a cost_before / cost_after row for a real slow
query from pg_stat_statements top-10.
Ship in 2-3 PRs, not one. The migrations land first (safe, additive).
The app-side header-injection and structured-log lines land second
(reviewable diff). The pg_stat_statements.max raise happens last, in its
own change window, because of the restart.
What the prompt is doing:
- Discovery before action. Phase 0 identifies what’s already wired and avoids recommending duplicates.
- Every anti-pattern named inline. Every “DO NOT” is a path that got punished today. AI agents don’t get those from official docs.
- Mandatory verification. The
pg_sleeptest is the single most valuable line. Without it, the agent reports “logging is configured” when it isn’t. - MCP as a first-class component. The whole point is Claude becomes the operator afterwards.
- Restart-required called out at the point of action, not buried in a footnote.
Four Gotchas From Actually Doing This
ALTER SYSTEM SET log_min_duration_statement fails with “permission denied”.
The Postgres docs say ALTER SYSTEM is the canonical path. Supabase doesn’t grant the postgres superuser to anyone, and the Dashboard’s Postgres Config UI doesn’t surface this setting in its searchable list. The actual path is Supabase’s supautils extension, which allowlists specific settings for role-level configuration via ALTER ROLE. You learn this either by Googling, by asking the Supabase Dashboard’s own AI assistant, or by trying ALTER SYSTEM and reading the error.
Lesson: managed Postgres has a parallel allowlist system that overrides what the upstream docs say is possible. When ALTER SYSTEM fails, the next question isn’t “how do I get superuser” — it’s “which extension is gating this and what’s in its allowlist?”
Setting log_min_duration_statement on postgres alone logs admin sessions and skips your entire app.
Role-level config applies at connection time. PostgREST connects as authenticator, then SET ROLEs to authenticated, anon, or service_role. A setting on postgres only applies to direct postgres connections — MCP, psql, Studio. Every customer-facing query above the threshold inherits one of the other four roles’ configs and goes uncaptured. You see your own admin queries logged and conclude logging is on.
Lesson: when a Postgres setting is role-level, the unit of work is “every role that initiates connections,” not “one place.” Five roles for Supabase.
“Evicting silently when full” is the worst-case observability failure mode. The signal you lose is exactly the signal you wanted.
pg_stat_statements.max defaults to 5000 and silently evicts in LRU order.
Postgres allocates a fixed-size shared memory block at startup. Once full, every new query shape evicts the least-recently-used entry. No error, no metric, no warning. The signal you lose is exactly the signal you wanted — rare-but-interesting low-volume queries from admin paths, recently-deployed routes, and dashboards that render once a day. On the project I set up today, the cap was 96.7% full after two years of history, with 28 entries turning over in the 10 minutes between two probes in the same session. Raising the cap needs a database restart because it’s a postmaster-context allocation.
Lesson: “evicting silently when full” is the worst-case observability failure mode. Check tracked_now / configured_max as part of every observability setup, not just performance reviews.
current_setting('pgrst.db_pre_request', true) returns NULL even when the hook is working.
The hook is set on the authenticator role via ALTER ROLE. current_setting() reads the current session’s GUC value, and your session isn’t authenticator. You can stare at a NULL for ten minutes wondering if the migration applied, then check pg_roles.rolconfig and find the hook set on authenticator exactly as intended. We almost wrote up a false-alarm P1 on this.
Lesson: when a config is per-role, the verification query reads from pg_roles.rolconfig for the target role, not current_setting() from your own session.
A handful of smaller ones went straight into the prompt rather than the article: pg_stat_statements totals over a multi-year reset window are mostly history not current state; PostgREST emits FROM "public"."table" not FROM table so naive LIKE filters miss real findings; the outlier-by-call-count detector floods with noise from pg-boss and Auth unless you filter system traffic first; get_advisors can return 68,000-character responses that overflow the context window if you don’t spill to disk. Each one cost an iteration this morning. Each is now a “do this, not that” callout in the prompt.
Cost
A focused afternoon. Migration is small. App-side changes are mechanical: one middleware edit, two client-wrapper edits, three or four structured-log lines on the hottest routes. The MCP setup is a one-shot OAuth flow. The only piece needing deliberate scheduling is the pg_stat_statements.max raise — it needs a restart window because the cap is postmaster-context.
After the setup, every future “the app feels slow” report becomes a SQL query against pg_stat_statements joined to a app:/route/path attribution joined to a forensic duration: NNNN ms statement: log line — read directly by Claude via the MCP and turned into a finding with a CREATE INDEX at the end.
The Broader Point
The Supabase platform has every observability primitive a serious operator needs. None are on by default, all are reachable from an afternoon of setup work, and each surface has a sharp edge that punishes the obvious wrong path. The cumulative cost of learning all the edges by hitting them during incidents is weeks distributed over months. The cost of doing it in advance with a prompt that already knows the edges is an afternoon.
Built on PlanB, a Bubble.io backup service. Stack: Next.js 16 App Router, Supabase Postgres 15 with pg_stat_statements / index_advisor / hypopg, Postgres logs via the Supabase MCP. Work done with Claude Code (Opus 4.7) over an afternoon — three commits, one verified end-to-end through pg_sleep + get_logs — about four hours of human review on top.