Postgres introspector is too slow for databases with many schemas and tables

[rubenferreira97]

When using Kysely with a Postgres database containing many schemas and tables, migrateToLatest() takes ~500 seconds to start up, even when no migrations need to be applied.

My production database has ~40 schemas with ~200 tables each (~8,000 tables total). Just calling migrateToLatest() blocks the application for minutes.

Root cause

After profiling, I found two main bottlenecks:

1. #parseTableMetadata is O(n²)

PostgresIntrospector.#parseTableMetadata uses Array.find() to look up each table by name and schema for every column row:

return columns.reduce<TableMetadata[]>((tables, it) => {
  let table = tables.find(
    (tbl) => tbl.name === it.table && tbl.schema === it.schema,
  )
  // ...
}, [])

For each of the n columns, it linearly scans the growing tables array, resulting in O(n²) time complexity. With ~50,000 columns, this in-memory step alone takes ~183 ms per call.

2. #doesTableExist fetches all tables in the database

The migrator's #doesTableExist calls getTables({ withInternalKyselyTables: true }) which introspects every table in every schema, then filters in JavaScript:

const tables = await this.#props.db.introspection.getTables({
  withInternalKyselyTables: true,
})

return tables.some(
  (it) => it.name === tableName && (!schema || it.schema === schema),
)

With 8,000 tables, this fetches tens of thousands of column rows from pg_catalog twice (once for the migration table, once for the lock table), just to check if two specific tables exist. This is the dominant contributor to the ~500s startup.

Benchmark: #parseTableMetadata (in-memory only)

Scenario	Total Columns	Before (ms)	After (ms)	Speedup
Small (1 schema, 5 tables, 5 cols)	25	0.002	0.002	1.1x
Medium (2 schemas, 20 tables, 10 cols)	400	0.051	0.026	2.0x
Large (3 schemas, 50 tables, 15 cols)	2,250	0.731	0.133	5.5x
XL (5 schemas, 100 tables, 20 cols)	10,000	11.46	0.61	18.9x
XXL (10 schemas, 200 tables, 25 cols)	50,000	182.69	2.85	64x

Benchmark: getTables() full roundtrip (SQL → parse)

Scenario	Specs	Fetch All (ms)	Fetch Filtered, 1 table (ms)	Speedup
S	1 schema, 10 tables	4.8	2.5	1.9x
M	5 schemas, 50 tables	5.1	2.4	2.1x
L	10 schemas, 100 tables	8.2	3.5	2.3x
XL	20 schemas, 500 tables	22.4	3.3	6.8x
XXL	50 schemas, 1,000 tables	44.1	2.0	22x

Proposed fix

I've split the fix into two PRs:

1. PR perf: optimize PostgresIntrospector #parseTableMetadata with Map-based lookups #1705: perf/pg-parse-table-metadata: Replace Array.find() with a nested Map<schema, Map<table, TableMetadata>> for O(1) lookups → O(n) total complexity. No API changes.

2. PR feat: add introspector filtering #1706: feat/add-introspector-filtering: Add optional schemas?: string[] and tables?: string[] filters to DatabaseMetadataOptions. This allows pushing filtering down to SQL (via WHERE ... IN (...)) in all 4 dialect introspectors. The migrator's #doesTableExist is updated to query only the specific table it needs instead of fetching everything.

Environment

• Kysely 0.28.x
• PostgreSQL 16
• Node.js v25
• Database: ~40 schemas, ~200 tables each (~8,000 tables, ~50,000+ columns)

---

Disclosure: This issue was written with AI assistance. The problem identification, solution design, benchmarks, and code review were done manually.

[koskimas]

@rubenferreira97 I don't know why @igalklebanov closed the PRs, maybe you had a discussion somewhere else?

But I see at least one issue: this is a breaking change for all 3rd party dialects. The code now expects all dialects to implement the filters. Kysely has tens of 3rd party dialects in addition to the ones in the core. You can't make breaking changes like this.

I think things could be fixed by not removing:

    return tables.some(
      (it) => it.name === tableName && (!schema || it.schema === schema),
    )

So dialects that don't implement the filters work as before, but the new dialects that do implement them are fast.

There might be other issues, but I didn't have time to review more throughly.

Just keep in mind the 3rd party dialects and full backwards compatibility.

[igalklebanov]

Hey 👋

@koskimas there's a Discord thread. tldr: enforcement of the contribution guidlines' process of ask to be assigned -> discuss -> get assigned -> submit a pull request to reduce "drive by pull requests" - which, like many other projects, we're getting more of nowadays. GitHub are working on settings that'll support this automatically.

[igalklebanov]

Regarding the problem, I currently think that we need an optional function on dialects' adapters that does the cheapest check possible, and fallback to the current implementation if it's not implemented.

[rubenferreira97]

@koskimas Thanks for taking the time to review the code. As @igalklebanov mentioned, I jumped the gun a bit before asking to be assigned, which goes against the contribution guidelines. That’s my mistake, and I’m happy to follow the proper process. Just to provide context in this issue for future readers: @igalklebanov mentioned on Discord that these PRs are not the right architectural solution for this problem.

Regarding your technical feedback, I can easily fix the breaking change in the original PR by maintaining the original fallback filter logic. However, I am now unsure if this is the architectural direction you both want to move forward with.

We realistically have two distinct bottlenecks here that require different solutions:

• Migrator Startup: We need to do less work here. We don't need to fetch all tables; we just need to check if a specific table exists. My solution (PR feat: add introspector filtering #1706) was to improve the Introspection API so we can ask for only the tables we need.
• Introspector Bottleneck: PostgresIntrospector.#parseTableMetadata is extremely slow. I switched the inner Array.find to a Map lookup (perf: optimize PostgresIntrospector #parseTableMetadata with Map-based lookups #1705), which fixes the severe CPU bottleneck internally with zero API changes. This optimization benefits the entire PGDatabase Introspector, not just the migration startup.

With this said, would you like me to update the original PR to incorporate @koskimas's suggestion for #1706, or would you prefer to handle this issue internally yourselves?

[koskimas]

@igalklebanov can decide the architecture with you. I'm fine with both ideas.