What is a relational database's system catalog (data dictionary), and why do engines store their own metadata as ordinary queryable tables?
answer
- Database describing itself
- Metadata as rows = free WAL, txn, perms
- Every statement resolves names via catalog
- Standard INFORMATION_SCHEMA vs native dictionary
- DDL = insert/delete catalog rows
basics
~20 sThe catalog is the database describing itself: tables that list tables, columns, indexes, constraints, views, roles and statistics. Keeping metadata in ordinary tables lets the engine reuse its own storage, transactions and permissions, so you read metadata with a plain SELECT.
solid answer
~50 sThe **system catalog** (data dictionary) is the engine's self-description: internal tables recording every table, column, type, index, constraint, view, sequence, routine, role, grant, plus the optimizer's statistics. Engines keep it as tables because that costs them nothing extra. The catalog then inherits the page/buffer-pool storage layer, write-ahead logging and crash recovery, backup, permission checks, and above all the SQL query processor. So metadata is readable with the same `SELECT` you use on user data, which is what makes ORMs, migration tools, schema-diff tools and ops dashboards possible without a proprietary API. Two reading surfaces usually exist: the SQL-standard `INFORMATION_SCHEMA` views (portable, lowest common denominator) and the engine's native catalog (`pg_catalog`-style tables, `sys.*`, `DBA_*` views) which is richer and cheaper. DDL is what mutates the catalog — `CREATE TABLE` is largely an insert of catalog rows — and on most modern engines that happens inside a transaction, so a failed migration leaves no half-created object.
code
sql · 5 linesSELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'customer'
ORDER BY ordinal_position;go deeper
Say what the catalog holds (tables, columns, indexes, constraints, permissions), that it is stored as tables, and that you read it with SELECT to discover a schema.
Add why tables: it reuses storage, WAL, backup, permissions and the SQL engine; and note that DDL is essentially catalog row changes, often transactional.
Bring in the two surfaces and their cost difference, permission filtering, cached plan invalidation on DDL, and the fact that catalog reads can be slow at scale.
Frame object count and catalog churn as a capacity dimension of the design, and reason about which introspection belongs in tooling/CI versus runtime.
## What the catalog is A database must remember, for itself, what exists inside it: which tables are defined; each table's columns with their order, type, nullability and default; which indexes exist and over which columns; primary keys, foreign keys and check constraints; views and their stored definitions; sequences; stored routines; roles and the grants attached to every object; and statistics describing the data distribution the optimizer plans against. That whole bundle of self-description is the **system catalog**, also called the **data dictionary**. Without it the engine could not parse a single statement. `SELECT name FROM customer` is meaningless until the parser resolves `customer` to an object id, checks that `name` exists on it, learns its type, and confirms the caller is allowed to read it. Every statement you run touches the catalog before it touches a data page. ## Why metadata is stored as tables Almost every mainstream engine stores the catalog as real tables inside the database, in the same file/page format as user data. The reason is reuse. The engine already has a storage layer, a buffer pool, write-ahead logging, crash recovery, backup and point-in-time restore, a permission system and a full SQL processor. Putting metadata in tables means: - **Durability and recovery for free.** Catalog changes are logged like any other write, so a crash mid-`CREATE INDEX` recovers to a consistent dictionary. - **Transactional DDL.** Because catalog rows obey the same MVCC/locking rules as user rows, engines can wrap `CREATE`/`ALTER`/`DROP` in a transaction and roll it back. PostgreSQL and SQL Server do this fully; MySQL 8 gives atomic single-statement DDL; Oracle commits implicitly around DDL, so it is atomic per statement but not groupable. - **Queryability.** This is the practical payoff. Metadata answers arrive through normal SQL, so tooling — ORMs introspecting a schema, Liquibase/Flyway checking state, schema-diff in CI, a dashboard listing largest tables — needs no special protocol. - **Security reuse.** Catalog views filter by the caller's privileges, so an unprivileged user typically sees only objects they may touch. A small bootstrap problem follows: to read the table that describes tables, the engine must already know that table's shape. Engines solve it by hard-coding the layout of a handful of bootstrap catalog relations in the binary and reading the rest normally. ## The two surfaces `INFORMATION_SCHEMA` is the SQL-standard, read-only **view** layer: `TABLES`, `COLUMNS`, `KEY_COLUMN_USAGE`, `REFERENTIAL_CONSTRAINTS` and so on, with portable column names. The engine's native catalog is the real thing underneath — `pg_class`/`pg_attribute` in a `pg_catalog`-style engine, `sys.objects`/`sys.columns`, Oracle's `DBA_*`/`ALL_*`/`USER_*` dictionary views. Native surfaces expose everything the engine knows (physical size, index usage counters, statistics, storage options); the standard surface exposes only what the standard defines. ## What DDL does to it `CREATE TABLE` mostly inserts rows: one describing the relation, one per column, more for constraints and the backing index of a primary key, plus a physical file created on disk. `DROP TABLE` deletes those rows and unlinks the file. `ALTER TABLE ADD COLUMN` with no rewrite may be a single catalog update. This is why 'DDL is fast' is sometimes true and sometimes not: catalog work is small, data rewriting is not. ## Practical cautions **Never write to catalog tables directly.** Most engines forbid it outright or require a special flag; a hand-edited dictionary can desynchronize from the on-disk files and corrupt the database. Use DDL. **Catalog reads are not free.** `INFORMATION_SCHEMA` views are wide joins with permission filters; on a database with hundreds of thousands of objects they can take seconds. Prefer native catalog tables for hot introspection paths, and cache the answer. **Object count is a capacity dimension.** Every table, index, column, partition and temporary table is catalog rows. Systems that create thousands of temp tables or schemas per hour grow and churn the catalog itself, which slows planning and needs cleanup like any other table. **What you see depends on who you are.** Two users querying the same view legitimately get different row counts because of grants — a common source of 'the table is missing' confusion.
- Can you UPDATE a catalog table directly to rename a column or drop a constraint?You should not, and most engines block it or require a special maintenance flag. The catalog is coupled to physical files, dependency records and cached plans, so a hand-edit desynchronizes metadata from storage and can corrupt the database. The supported path is DDL, which updates every dependent catalog row and invalidates caches consistently.
- Is DDL transactional? What happens if a migration fails halfway?It depends on the engine. Where catalog rows follow normal MVCC rules — PostgreSQL, SQL Server — you can wrap several DDL statements in one transaction and roll the whole thing back, so a failed migration leaves nothing behind. MySQL 8 makes each DDL statement atomic but not groupable, and Oracle issues an implicit commit around DDL, so on those engines a multi-step migration needs its own compensating logic.
A library's card catalogue kept on the same shelves as the books: same building, same rules, same fire insurance — and you look things up the same way you read anything else.
saying these in an interview costs you the question
- Thinking the catalog is a separate config or flat file outside the database rather than tables in it
- Believing INFORMATION_SCHEMA is a PostgreSQL/MySQL invention rather than part of the SQL standard
- Assuming catalog queries are instant and safe to run per request in application hot paths
- Claiming DDL never participates in transactions on any engine
- Proposing to fix a schema problem by UPDATE-ing dictionary rows directly