During the ARES migration we built an internal tool called the Schema Dictionary: an explorer that maps every legacy table and column to its v2 counterpart. With two schemas totalling thousands of tables in flight, “where does this column live now?” was a question someone asked every day, and the tool was the answer.
There was just one problem: the page took 94 seconds to load.
The obvious suspects were innocent
The tool’s data model was trivial — read the schema metadata, join it with our mapping annotations, render a tree. No business tables were touched at all. Yet it was the slowest page in the entire admin.
The culprit was the metadata read itself. Like most people, we’d written the introspection against information_schema — the SQL-standard views (information_schema.columns, .tables, .key_column_usage, and friends) that every tutorial reaches for. It’s portable, it’s readable, and on a schema this size it’s brutally slow.
Why information_schema crawls
information_schema isn’t a set of tables — it’s a layer of views defined on top of PostgreSQL’s real system catalogs. And those views do a lot of well-meaning work you didn’t ask for:
- Per-row privilege checks. The views filter to objects the current role can see, which means functions like
pg_has_role()evaluated over and over — for every column of every table. - Standards-compliance joins. Mapping PostgreSQL’s internal model onto the SQL standard’s shape takes multi-way joins across
pg_class,pg_attribute,pg_type,pg_namespaceand more — repeated inside each view you touch. - Optimizer opacity. Query several of these views together (columns + constraints + foreign keys, as any schema explorer does) and the planner struggles to push conditions down through the view stack. Row estimates go sideways; nested loops bloom.
None of this matters when you look up one table. It matters enormously when a tool enumerates everything — twice, in our case, because we introspected both the legacy schema and the v2 schema to draw the mapping.
The fix: ask the catalogs directly
PostgreSQL’s real metadata lives in pg_catalog — pg_class (relations), pg_attribute (columns), pg_constraint (keys and FKs), pg_namespace (schemas), pg_index, pg_type. Querying them directly skips the view layer entirely: no per-row privilege theater, no standards translation, and the planner sees plain tables with real statistics.
The rewrite was a day of work. A handful of queries shaped roughly like:
SELECT c.relname AS table_name,
a.attname AS column_name,
t.typname AS data_type,
a.attnotnull AS not_null
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
WHERE n.nspname = 'public'
AND c.relkind = 'r'
AND a.attnum > 0
AND NOT a.attisdropped;…plus the same treatment for constraints and foreign keys, and one pass to stitch results in application code instead of joining views against views in SQL.
Result: 94 seconds → 1.9 seconds. Same data, same page, same database. Fifty times faster by walking through a different door.
The honest trade-offs
- Portability is gone.
information_schemaworks on any SQL database;pg_catalogis PostgreSQL’s own. For an internal tool pinned to PostgreSQL forever, that trade costs nothing. - Catalogs are internal API. They’re remarkably stable (the queries above run unchanged from PG 11 to PG 18 — we know, because our legacy database was on 11), but they can change between major versions. Read the release notes when you upgrade.
- You re-own privilege filtering. The views hid objects you couldn’t access; raw catalogs show everything. For an admin-only tool that’s fine — just don’t forget it in user-facing contexts.
The takeaway
If a page that only reads metadata is slow, suspect the metadata layer itself. information_schema is a convenience view with real costs, and at “explore the whole schema” scale those costs dominate everything else. The catalogs underneath answer the same questions — just without the ceremony.
Questions about PostgreSQL internals or migration tooling? Reach out.