Find Slow Postgres Queries in a Supabase App

The run reaches the database through your Supabase connection: an admin signs in once to Supabase's official MCP server at https://mcp.supabase.com/mcp and the run names that connection, so no database password sits in the request. From there the agent queries the pg_stat_statements view in the extensions schema, ordering by total time to list the 10 slowest statements, then reads the schema of the tables those queries touch. In this template the schema is defined by supabase/migrations/20250130181641_todo_list.sql, which creates public.todo_list with a single index, the primary key todo_list_pkey on id, and by supabase/migrations/20250107210416_MFA.sql, which creates the authenticative.is_user_authenticated() function that the row level security policy "Owner can do everything" calls on every statement.

Next it matches each slow statement to the code that sends it. In a Next.js app like Razikus/supabase-nextjs-template the queries are not raw SQL strings but supabase-js builder calls in nextjs/src/lib/supabase/unified.ts: getMyTodoList builds this.client.from('todo_list').select('*').range(page * pageSize - pageSize, page * pageSize - 1).order(order) and adds .eq('done', done) when a filter is set, with createTask, removeTask and updateAsDone sending the writes. nextjs/src/app/app/table/page.tsx calls supabase.getMyTodoList(1, 100, 'created_at', filter) on every task-list load, and nextjs/src/app/app/storage/page.tsx lists files through this.client.storage.from('files').list(myId) in the same SassClient wrapper. It reads the generated Database types in nextjs/src/lib/types.ts to map the builder calls back to columns, then searches the repository for each table and filter pair until every statement has a calling file and line.

For each match the agent checks what the planner does. The RLS policy appends owner = auth.uid() to every todo_list query, and with only todo_list_pkey on id there is no index on owner, so the plan for getMyTodoList is a sequential scan filtered by the policy; EXPLAIN on the same query shape shows it. That evidence backs a suggestion like an index on (owner, done) or (owner, created_at), matched to the filters unified.ts applies and the created_at order that table/page.tsx passes. It also notes what it verified from supabase/config.toml: db major_version 15 and max_rows 1000 on the API. Row counts and timings come from the linked project's pg_stat_statements, seeded with enough rows that unindexed scans are measurably slow.

The run is read-only: the agent creates no index, changes no data, and writes one file, slow-queries.md, which comes back as the run's artifact rather than a pull request. Every query it ran stays in the transcript, so a database admin can audit exactly what touched the project.

What comes back is the finished session with slow-queries.md in its artifacts, each with a name, a mime_type and a url on api.prix.dev that downloads with no key for an hour. The report lists the 10 queries ranked by total time, and each entry has the statement as Postgres recorded it in pg_stat_statements, its mean and total time, the calling file and line in the repository (here, nextjs/src/lib/supabase/unified.ts and the page in nextjs/src/app/app/table that calls it), and a suggested index or rewrite with the reasoning.

A schema section records what the agent verified against the migrations: which indexes exist on todo_list, what the policy "Owner can do everything" adds to each query, and which statements map to builder calls in the SassClient wrapper. Statements it could not match to code are listed as unmatched, so the report does not hide that gap. The transcript at GET /o/{org}/sessions/{id}/messages keeps every query with its output, so each number in slow-queries.md can be checked against what the database answered.

Because nothing changed, there is nothing to merge; applying an index stays a decision you take through your own migration flow. When one ships, a follow-up POST to /o/{org}/sessions/{id}/messages asks the same session to re-read pg_stat_statements and add a before-and-after entry, and the sandbox wakes where it left off.

Point the trigger you already have at this run: when your monitoring fires a slow-query alert, or on a schedule, your server POSTs this same request. Each run reads pg_stat_statements at that moment through the same Supabase connection, so a report written last month is never the answer to this week's slowdown. Because resources names the repository, the Rush GitHub App must be installed on it; a run takes one repository, so a monorepo with several apps gets one report per app.

Set a webhook URL on the API key and Rush POSTs run.completed, run.failed or run.cancelled when each turn finishes, with the session URL; your server files the report only once the run has settled. Send an Idempotency-Key header so a retried alert does not start a duplicate run; a key runs at most max_concurrent sessions at once, 4 by default, past which the API answers 429.

On a laptop this job means opening a psql session to a production Supabase project, running the pg_stat_statements queries, pasting the output into a chat window, and losing the trail of which answer came from which row. On Rush the agent runs in its own sandbox with the Supabase connection named on the run, keeps every query and its result in the transcript, and returns one ranked report; no laptop needs direct database access.

The report is checkable instead of claimed: every timing traces to a recorded query and every index suggestion to a verified migration. The run is read-only, so the worst a bad run produces is a wrong report, never a schema change you roll back. The same request can sweep several Supabase projects at once, one connection and one ranked report each.

Does the run change my database?

How does the agent sign in to Supabase?

What if pg_stat_statements has no data yet?

Which queries would it flag in this template?

Can I get the index as a migration file?