Investigate Slow Vector Search in Supabase

The agent starts from the schema, not from guesswork. In a repo like supabase-community/nextjs-openai-doc-search it opens supabase/migrations/20230406025118_init.sql and reads the match_page_sections function defined there: a plpgsql function over the nods_page_section table that filters on length(content) >= min_content_length, keeps rows where (embedding <#> embedding) * -1 > match_threshold, orders by embedding <#> embedding and limits to match_count. The same migration creates the embedding vector(1536) column and no index on it, and supabase/migrations holds no ivfflat or hnsw index anywhere, so at 200,000 rows the function computes a distance for every row and sorts them all.

Next it reads the caller. pages/api/vector-search.ts is an edge route that waits on an OpenAI moderation request and a text-embedding-ada-002 embedding request before it ever calls the database, then invokes supabaseClient.rpc('match_page_sections', ...) with match_threshold 0.78, match_count 10 and min_content_length 50 hard-coded. The agent checks supabase/config.toml (Postgres major_version 15), the client in package.json (@supabase/supabase-js ^2.13.0, next pinned 13.2.4), and lib/generate-embeddings.ts, the script behind pnpm run embeddings, to see how rows are written: one insert per section in a loop against nods_page and nods_page_section, with no batching.

To rank the suspects it uses the Supabase connection: an admin signs in to the Supabase MCP server once with POST /me/connections and the run names "supabase" in a connections field, so the agent can list the actual indexes and run its checks instead of asking you to paste output. Each suspect goes into the report with the check that confirms or clears it: for the missing index, the create index statement using hnsw versus explain (analyze, buffers) on the rpc showing a sequential scan; for the filter expressions, whether length(content) and the threshold computation force a full distance computation before the sort; for the route, timing the rpc alone against the whole handler to see whether the 2.5 s sits in Postgres or in the two OpenAI round trips.

It also reads the last 30 commits with git log --stat to see what changed before the slowdown, and components/SearchDialog.tsx, which posts to /api/vector-search, to confirm the timeout a user sees is the search route and not another caller. The repository has no tests and its .github/workflows/build.yml stops at pnpm install, so there is no CI output to lean on. The evidence is the function body, the route code, the migration and the checks it ran.

What comes back is one file, incident.md, listed on the finished session as an artifact you download from an api.prix.dev URL. The report opens with a timeline: when the table passed 200,000 rows, when match_page_sections started reporting 2.5 s per call, when the search endpoint began timing out, each entry tied to a commit from git log or a note that nothing changed.

The body ranks the suspects, each with file and line evidence. In a repository shaped like this starter, the top of the list is the missing index on the embedding column created in supabase/migrations/20230406025118_init.sql, with the exact create index statement that would fix it; behind it sit the per-row length(content) filter and the threshold expression in match_page_sections that compute a distance for every row before the sort, and the edge route in pages/api/vector-search.ts that spends time on OpenAI moderation and embedding calls before Postgres is reached. Every suspect carries the check that confirms or clears it, such as explain (analyze, buffers) on the rpc call, or timing the rpc alone against the full route.

Alongside the report, the transcript at GET /o/{org}/sessions/{id}/messages keeps every step: each file read, each query the agent ran through the Supabase connection with its output, and each suspect it ruled out and why. Because the prompt says to change no code, the run produces no commit and no pull request; the deliverable is the report and the record behind it.

Point your database alerting at your server and, when a slow-query alert names a Supabase function or table, have the server send this same request with the repository in resources. Each alert starts its own run in its own sandbox, so an alert storm on one service produces one incident report per alert rather than a queue on one engineer. Because resources names a GitHub repository, the Rush GitHub App has to be installed on that repository for the run to clone it.

Set a webhook URL on the API key and Rush POSTs run.completed or run.failed when each run's turn finishes, with the session URL, so your server can page on-call with the report link when it is ready. A key runs at most max_concurrent sessions at once (4 by default) and answers 429 past that, so a burst of alerts should be queued on your side and retried with a fresh Idempotency-Key per run.

On a laptop this investigation means checking out the repo, hunting down Supabase credentials, and running explain against a production project from a terminal you hope is read-only. On Rush the run works in its own cloud sandbox: the repo arrives through the GitHub App, the project is read through the signed-in Supabase connection, and the prompt forbids changes, so the project stays read-only.

The result is checkable rather than claimed. The trajectory keeps every query the agent ran and its output, so an on-call engineer can rerun any check in the report before opening Supabase themselves. And because each run is independent, the same request can investigate the same slow function across a dozen projects, each with its own incident.md.

Will it change anything in my database?

How does the agent reach my Supabase project?

Does it need the GitHub App?

What if the problem is the application code, not Postgres?

Can I ask follow-up questions after the report?