## I have read the
[CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md)
file.
YES
## What kind of change does this PR introduce?
Bug fix (performance), plus a regression-guard test suite and docs.
## What is the current behavior?
Studio's introspection queries in `@supabase/pg-meta` do `O(catalog)`
work for per-table requests. On databases with very large catalogs
(hundreds of thousands of relations/constraints — real deployments reach
this) they take tens of seconds per dashboard interaction, trip
`statement_timeout`, and create heavy CPU/memory pressure when several
tabs open concurrently. Two instances of the same bug class:
**1. Table Editor query (`getTableEditorSql`)** — fetches metadata for
ONE table by OID, but five catalog scans are unscoped and only filtered
at the top-level join:
- `primary_keys` CTE — scans all of `pg_index` (`where i.indisprimary`)
- `index_cols` CTE — scans all unique indexes
- `relationships` CTE — scans every FK in `pg_constraint` (and is
scanned twice by the two subplans)
- `uniques` subquery (inside `columns`) — scans all single-column unique
constraints
- `check_constraints` subquery (inside `columns`) — scans all
single-column check constraints
The planner cannot push the outer join qual into grouped / `distinct on`
subqueries, so each is computed over the full catalog and thrown away.
`tables-paginated.ts` was previously rewritten to avoid exactly this
pattern; the single-table query never got the same treatment.
**2. Entity definitions (`getTableDefinitionSql` /
`getEntityDefinitionsSql`)** — the vendored `pg_get_tabledef` plpgsql
function scans the entire `information_schema.columns` view once **per
column** (plus `information_schema.tables` once per call) just to decide
whether a name needs double-quoting — a pure string property of a name
it already holds — and its per-index partial-index lookup casts
`relnamespace::regnamespace::text` across every `pg_class` row. On a
12K-table catalog this makes a single entity's DDL cost ~3.7s and a
default 100-entity definitions page ~6 minutes.
## What is the new behavior?
**Fix 1 — scope the Table Editor CTEs to the requested OID** (`id` is
validated non-null and interpolated via `literal()`, same as the
existing `base_table_info` filter):
- `primary_keys` / `index_cols`: `and i.indrelid = <id>`
- `relationships`: `and (c.conrelid = <id> or c.confrelid = <id>)`
- `uniques` / `check_constraints`: `and conrelid = <id>`
Semantics are unchanged: the top-level select already filtered every CTE
to the target table, so rows for other tables were computed and
discarded. The `pg_index`/`pg_constraint` lookups become index scans
returning a handful of rows. One residual scan is structural: PostgreSQL
has no index on `pg_constraint.confrelid`, so the incoming-FK half of
`relationships` is a single filtered seq scan of `pg_constraint` — still
one cheap pass instead of materializing every FK row twice.
**Fix 2 — remove the O(catalog) scans inside `pg_get_tabledef`**: the
information_schema uppercase checks are replaced with direct regex tests
on the name in hand (preserving the original's `quote_ident` behavior
for schemas that need quoting), and the partial-index lookup is scoped
by the already-resolved table OID. Original statements are kept as
comments, matching the vendored file's convention.
**Regression guard** — so this bug class stays out:
- `test/db/stress-catalog.ts` builds a synthetic catalog (default 2,000
tables with PKs, unique + check constraints, FK chains and an FK hub;
`PG_META_STRESS_TABLES` scales it to incident size).
- `test/db/plan-guard.ts` provides `EXPLAIN (ANALYZE, FORMAT
JSON)`-based budget assertions: a query's plan may only seq-scan a
scaling catalog if its budget entry carries a written structural
justification (e.g. no index on `pg_constraint.confrelid`; no index on
`pg_class.relnamespace` for per-schema listings), plus a per-query time
bound (the only guard available for opaque plpgsql internals like
`pg_get_tabledef`).
- `test/sql/studio/catalog-plan-guard.test.ts` applies budgets to the
hot-path studio queries: table editor, constraints, FK listing, entity
types, tables-paginated, columns, indexes, table/entity definitions,
views. Reverting either fix makes the suite fail immediately with the
offending scans listed.
- `test/sql/studio/table-editor.test.ts` (new — none existed) asserts
the Table Editor query's semantics: primary keys, unique indexes, both
FK directions, `is_unique`, check definitions, column comments.
- A new package `README.md` documents the plan-guard budget entry as a
requirement for any new introspection query.
### Validation (synthetic 12,000-table catalog, PostgreSQL 17.6)
- **Output equivalence, fix 1:** for 12 relation types (regular,
composite PK, partitioned parent + partition, view, materialized view,
constraint-free table, FK hub/chain/tail, and a fixture with
enums/domains/generated/identity columns and duplicate check
constraints), the `entity` jsonb from the old and new query is
byte-identical.
- **Output equivalence, fix 2:** byte-identical DDL across 13 fixture
combinations (serial/identity/generated/array columns, case-sensitive
and keyword names, mixed-case schemas, partitions, unlogged +
reloptions, partial/expression indexes, external PK/FK/comments/trigger
variants).
- **Performance, fix 1:** Table Editor query `EXPLAIN ANALYZE` ~1,630ms
→ ~30ms (~50×); the gap grows with catalog size since the old query is
O(catalog) per call.
- **Performance, fix 2:** single entity definition 3,672ms → 63ms; a
100-entity definitions page ~6min → 0.87s. The plan-guard bound for
`getEntityDefinitionsSql` tightens accordingly from 15s/25 entities to
3s/100 entities (330ms measured at default test scale).
Verified locally: `catalog-plan-guard` (12 tests), `table-editor`,
`tables-paginated` (16 tests) pass; `typecheck` clean.
### Rollout
Per review, the new behavior ships **dark** behind the
`pgMetaScopedIntrospection` ConfigCat flag (default off = legacy SQL,
kept as full duplicated templates in pg-meta and verified byte-identical
to the pre-PR queries). Studio reads the flag in the query hooks and
threads it through (flag state is part of the React Query keys). The
rollout is staged in the ConfigCat dashboard via user-email targeting
(like every other ConfigCat flag): target the reporting user's email
first, then a percentage rollout, then 100%. Server-side AI callers of
`getEntityDefinitionsSql` stay on the legacy path. Once fully rolled
out, delete the legacy templates + flag in a cleanup PR.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Closes: PGMETA-122
<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit
- **Bug Fixes**
- Improved table editor SQL to correctly scope primary keys, indexes,
uniques, checks, and relationships to the selected table.
- Optimized table definition SQL to reduce unnecessary catalog scanning
for uppercase-name detection and partial-index detection.
- **Tests**
- Added SQL generator tests for table editor metadata (keys, indexes,
relationships, comments, and constraints).
- Added catalog query plan guard coverage with a stress catalog and
EXPLAIN-based scoping/performance budgets.
- **Documentation**
- Expanded documentation on catalog query plan safeguards and how to
keep new introspection queries properly scoped.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
---------
Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
## Context
Currently when retrieving row counts of a table in the Table Editor,
we're using a `COUNT_ESTIMATE` pg function
([ref](https://github.com/supabase/supabase/blob/master/packages/pg-meta/src/sql/studio/database/get-count-estimate.ts#L5))
to retrieve an estimate (instead of checking `pg_class` -> `reltuples`)
as that would theoretically provide a more accurate representation.
However, in a read only context, that function can't be used - users
will run into `cannot execute CREATE FUNCTION in a read-only
transaction`, so we need to fallback to just checking `pg_class` in this
scenario.
The logic's already set up as we were previously looking into allowing
users to use a read replica to power the dashboard, but we also need to
consider members with read-only roles within the organization, so this
PR updates the logic a little to factor that in.
## To test
- [ ] With a read-only role, open the table editor and verify that we're
not using the count estimate function to retrieve the table row counts
<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit
* **New Features**
* Updated the invite member dialog to open in a larger size for better
usability.
* **Bug Fixes**
* Improved table row count behavior so it now respects read-only access
and permission limits more reliably.
* Count estimates should now be shown more consistently across different
database contexts.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
## Context
Just some clean up as I was going through stuff
- `useExecuteSqlQuery` is deprecated and not used at all
- As such `execute-sql-query` is technically irrelevant, the more
relevant file is `execute-sql-mutation`
- Hence opting to consolidate `execute-sql-query` into
`execute-sql-mutation`
- Also removing `ExecuteSqlError` since its just re-exporting the
`ResponseError` type
There's a lot of file changes but its essentially just updating the
importing statements across the files
## Context
Related to FE-2557
Part of shifting manually written dashboard queries into
packages/pg-meta where
- pg-meta can be code owners of
- we can write tests for the queries
This PR just shifts all the `.sql.ts` files that we previously created
into packages/pg-meta
There's still other areas where we need to shift over as well which I'll
address in subsequent PRs
## Notable changes
- `getTableRowsCountSql` -> Opted to shift `formatFilterValue` logic out
before calling this method (ref `table-rows-count-query`)
- `getDeleteOldCronJobRunDetailsByCtidSql` -> Opted to shift
`validatePageNumber` logic out before calling this method (ref
`CronJobsTab.useCleanupActions`)
Previously `connectionString` was passed into the query key for
`table-rows`. Since `connectionString` is unstable, it would cause the
query key to change randomly, often making the user experience brief
random "no rows found" states while data loaded for the new key.
This PR uses `readReplicaIdentifier` as a stable version of
`connectionString`, maintaining the existing functionality while
avoiding the unnecessary data reloads.
Note that there are still quite a few places where `connectionString` is
passed into the query key. We'll fix these as follow up PRs.
I'm opting to also include the fix from
https://github.com/supabase/supabase/pull/43572 in here for consistency.
**To test:**
- Ensure table editor functions normally
- Use the table editor with a read replica selected
## Context
Part of dashboard scalability project
Opting to use the connection string of the project's read replica (if
available) for read queries on the database.
Trialing with the Table Editor as a first pass - changes involved will
opt to use replica connection string for `useTableRowsQuery`,
`useTableRowsCountQuery`, and `useForeignKeyConstraintsQuery`
There's definitely optimizations to be done for deciding which replica
to use - but am starting off with a rather naive logic to prioritize
replicas in the same region as the project.
## Changes involved
- We're no longer passing `connectionString` as a param into the
affected hooks, the `connectionString` is derived from within those
hooks instead
- Change is feature flagged, so things should be status quo if flag is
off (use primary database's connection string)
- Added `useConnectionStringForReadOps` hook which returns the replica's
connection string if (Otherwise defaults to primary database connection
string)
- Feature flag is on
- Project has a replica available
## To test
- [ ] Verify that the table editor works as expected for a project that
has read replicas (There shouldn't be any change really)
- [ ] Also just double check that updating cells in the table editor
works as well (There's no change there, we're using the primary DB's
connection string for mutation ops)
- [ ] ^ Same thing for a project that doesn't have read replicas
- [ ] ^ Same thing for local / self-host
* Add custom types for queries, mutations and infinite queries.
* Migrate all queries to use the new type.
* Migrate all infinite queries to useCustomInfiniteQueryOptions.
* Migrate all mutations to use useCustomMutationOptions.
* Add type to all imports in `types` folder.
* feat: optimized users page
* Update UI
* Reinstate footer with count if mode is freeform
* Simplify disabled sort in performance mode
* Clean
* Small fix
* Final fixes
* Shift users SQL query to packages/pg-meta
* Nit unrelated: Clear query params from useLogsUrlState when going to logs tab of a selected user
* Shift user count SQL and get count estimate SQL into packages/pg-meta
* Fix
* Nit
* Nit
* Minor nits
* Refactor UX for searching
---------
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
fix: handle undefined count when fetchint table rows
count can be undefined in the return from fetching table rows (it uses
executeSqlQuery under the hood which has no type safety) so we need to
deal with that case
* fix: estimate number of users when count is large
In the Auth Users table, we always fetch an exact count of users. This
can be a problem for projects with many (>50K) users as the count(*)
might cause performance issues on the database. We already have logic on
the Table Editor to only run automatic count estimates (fetching the
exact count only if usr requests it), this change ports the same logic
over to Auth Users.
* Nit refactor
---------
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* chore(studio): move Query to pgMeta add tests
- Move the Query builder from studio to pgMeta
- Add e2e tests over the generated sql to ensure syntax and runtime
result over pg database
- fix bug with orde by for table with undefined column
* chore: fix query import path
* chore: set ES target for lint
* chore: add github action for pg-meta test package
* chore: add tsconfig to sparse checkout
* chore: remove useExecuteSqlQuery part 1
* fix invalidation
* fixes
* more fixes
* I fixed it, but tested on preview instead of local 🤦🏻
* only refetch table rows and not count on row updates
* removed unneeded invalidations and prefetched new table after create
* feat: user impersonation
* refactor
* add anon impersonation
* add user impersonation to graphiql
* thanks Ivan for this one
* add radio buttons
* progress
* working user selector
* adds loading and error states to user selector
* nicer popover button
* default graphiql to use service role key
* styling updates
* fix padding in graphiql
* add service role warning to graphiql
* add user impersonation to realtime inspector
* add feature flag
* use Alert_Shadcn_ instead of old Alert
* Update apps/studio/lib/role-impersonation.ts
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* update title
* Update apps/studio/components/interfaces/RoleImpersonationSelector/UserImpersonationSelector.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Update apps/studio/components/interfaces/RoleImpersonationSelector/Icons.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Update apps/studio/components/interfaces/RoleImpersonationSelector/Icons.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Update apps/studio/components/interfaces/RoleImpersonationSelector/Icons.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Update apps/studio/components/interfaces/RoleImpersonationSelector/Icons.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Update apps/studio/components/interfaces/RoleImpersonationSelector/Icons.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Update apps/studio/components/interfaces/RoleImpersonationSelector/UserImpersonationSelector.tsx
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* add constant padding at the bottom of user selector
* update graphiql buttons font size
* make rls warning dismissable
* use text-foreground on rls warning title
* fix no results while impersonating a role
* fix error line number when impersonating a role
---------
Co-authored-by: Jonathan Summers-Muir <MildTomato@users.noreply.github.com>
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
* Move all studio files from /studio to /apps/studio.
* Move studio specific prettier ignores.
* Fix the ui references from studio.
* Fix the css imports.
* Fix all package.json issues.
* Fix the prettier setup for the studio app.
* Add .turbo folder to prettierignore.
* Fix the github workflows.
