supabase/packages/pg-meta/README.md

@supabase/pg-meta

SQL builders for Postgres catalog introspection, shared by Supabase Studio and postgres-meta. Each builder in src/sql/ returns a safe, parameterized SQL fragment (SafeSqlFragment) that a caller executes against a user's live database to read schema metadata — tables, columns, constraints, indexes, relationships, entity definitions, and so on.

The studio/ subtree holds the queries the Studio dashboard runs on every page open (Table Editor, Database pages, entity lists, definitions).

Catalog query plan guard

Why this exists

These queries run against the user's live catalog, whose size we don't control. A real production catalog had ~267K pg_class rows. Before #47894, several CTEs in the Table Editor query were unscoped: they scanned pg_index/pg_constraint across the whole catalog regardless of which table was being opened. That turned a single Table Editor open into O(catalog) sequential scans — 30–58s of work, tripping statement timeouts, on large catalogs.

The fix scoped those CTEs to the requested table OID. To keep that class of regression out for good, the package has a plan guard: a test suite that builds a large synthetic catalog and asserts, via EXPLAIN (ANALYZE, FORMAT JSON), that each hot-path query's plan stays scoped.

• test/db/stress-catalog.ts — builds a synthetic stress schema (default 2000 tables, plus a view, a materialized view, and a partitioned table).
• test/db/plan-guard.ts — explainAnalyze() + assertPlanWithinBudget() and the tolerated tiny-catalog set.
• test/sql/studio/catalog-plan-guard.test.ts — one budget per covered query.

THE RULE for new queries

Every new introspection query added under src/sql/ that runs on a user's live catalog must get a budget entry in test/sql/studio/catalog-plan-guard.test.ts.

Sequential scans over catalogs that scale with schema size — pg_class, pg_attribute, pg_index, pg_constraint, pg_attrdef, pg_description, pg_depend, pg_policy, pg_trigger, pg_rewrite, … — are only acceptable with a written structural justification. An unscoped scan with no such justification is a bug: scope the query to the requested OID/schema so it uses an index instead.

How to add a budget entry

Run the query through the harness against the stress catalog and set a budget:

test('getMyNewSql: plan stays scoped', async () => {
  const result = await explainAnalyze(db, getMyNewSql({ id: someTableId }))
  assertPlanWithinBudget(result, {
    // Omit allowedSeqScans entirely for a per-object query that must be fully
    // index-scoped. Add an entry only for a structurally unavoidable scan:
    allowedSeqScans: {
      pg_constraint: {
        max: 2,
        reason: 'no index on pg_constraint.confrelid — incoming-FK lookup',
      },
    },
    maxExecutionTimeMs: 1000, // default; loosen only with a comment
  })
})

What's tolerated

• Tiny, fixed-size catalogs (pg_namespace, pg_foreign_table, pg_foreign_server, pg_foreign_data_wrapper, pg_enum, pg_proc) are always tolerated. The planner full-scans them because they hold a handful of rows and don't grow with table count. See TINY_NON_SCALING_CATALOGS.
• Justified structural scans on scaling catalogs — e.g. there is no index on pg_constraint.confrelid, so the incoming-FK half of a relationships lookup must seq scan; a per-schema listing can't prune pg_class because there is no index on relnamespace alone. Each such scan carries a reason string and a max node count.

Budget judgment:

• Per-object queries (single id/table) allow no seq scans on scaling catalogs except structurally unavoidable ones (each with a reason).
• Per-schema / list queries may need one structural scan (e.g. pg_class has no relnamespace-only index) — allow it with a reason and keep a time bound.

Reproduce at scale locally

The default of 2000 tables keeps CI fast. To investigate closer to real incident scale, crank the table count:

PG_META_STRESS_TABLES=12000 pnpm --filter @supabase/pg-meta test catalog-plan-guard