# Database CPU pinned

**Symptom:** Supabase CPU at or near 100%; pages time out across the app, and queue jobs slow down with it.

## Find the query

1. Supabase → Query performance (backed by `pg_stat_statements`): sort by total time. One statement usually dominates.
2. Find its caller by searching the repos in `packages/db/src/repos/` for the table and predicate.
3. High cache hit + high mean time + huge call count means a missing or unusable index, not an under-sized database.

Past case: `jobExecutionsRepo.getLatestSuccessfulDocumentSync` did a `LIKE` on `job_executions.key` with an expression sort. It took ~53% of DB time because the audit-run drawer called it on every open. The fix was a partial `text_pattern_ops` index plus a backfill so the query stopped firing.

## Stabilize

- If the caller is behind a flag, turn it off.
- If it's a queue job, lower that topic's concurrency.
- If it's a page load, it usually can't be switched off. Move straight to the index.

## Adding an index during an incident

- Large tables (`patient_charts`, `job_executions`, `audit_runs`): `CREATE INDEX CONCURRENTLY` by hand, off-peak if at all possible. Even a concurrent build can saturate disk I/O and briefly break the app.
- Then land the same index in the Drizzle schema with an `IF NOT EXISTS` migration, so the tracked migration is a no-op on prod.
- If a backfill reduces the query's load, build the index **before** running the backfill. Backfills often read through the same slow query.