Measure the sidebar, Open Quickly and autocomplete on 100 schemas and 50,000 tables #3661

Problem

Some users work in databases with many schemas, for example one per tenant with the same table names in each (#3431). Nothing in TablePro tests or times the sidebar, Open Quickly or autocomplete on a catalog that size, so a slowdown there would only show up in a user's database.

What the code and docs say today:

• Open Quickly reads the other schemas' tables with one query per schema on every engine except PostgreSQL, PGlite, SQL Server and DuckDB (features/open-quickly.mdx, "Other schemas"). On Oracle, 100 schemas means 100 queries before the panel stops reading Loading….
• Autocomplete keeps the columns of at most 300 tables (apple/TablePro/Core/Autocomplete/SQLSchemaProvider.swift:18) and holds the whole table list in memory (SQLSchemaProvider.swift:36).
• The sidebar is an NSOutlineView subclass (apple/TablePro/Views/Sidebar/DatabaseTreeOutlineView.swift:92). How long it takes to list, filter and expand 50,000 tables has never been measured.

Proposed solution

• A script that builds three fixtures: PostgreSQL with 100 schemas of 500 tables each, Oracle with the same shape, and MySQL with one database of 50,000 tables.
• Measure on each fixture:
  • Sidebar: connect to the first full listing, one keystroke in its search field, expanding a schema.
  • Open Quickly: opening the panel, and the first result for a name that exists in every schema.
  • Autocomplete: the popup after FROM and after schema., and resident memory once the table list is loaded.
• Set a budget for each from the first run. The main thread never blocks for more than 100 ms during any of them.
• Fix whatever misses its budget, starting with the slowest. For the per-schema reads in Open Quickly, read every schema in one catalog query where the engine allows it.

Done when the measurements are written down with their budgets, and every step meets its budget on every fixture.

Alternatives considered

The schema filter narrows the sidebar and Open Quickly to the schemas it keeps (features/connection-window.mdx, "Filtering schemas"). It does not help someone who needs every schema.

Platform

macOS

Related database type

N/A / General