The SQL-standard INFORMATION_SCHEMA views and an engine's native catalog (for example a pg_catalog-style set of system tables, or sys.* / DBA_* dictionary views) both describe the same database. How do they differ, and when would you choose each?

middleshould knowfreq 45%

answer

Standard views = portable projection over the real dictionary
Native = physical, statistics, usage counters
No portable answer to 'is this index used'
Standard views can be seconds slow at 100k+ objects
Native shapes shift across major versions

basics

~20 s

INFORMATION_SCHEMA is a standard, portable, read-only view layer with the same column names across engines, but only standard concepts. The native catalog is the real underlying dictionary: engine-specific, far richer, usually faster. Use the standard for portable tooling, native for depth and performance.

solid answer

~50 s

INFORMATION_SCHEMA is defined by the SQL standard: a schema of read-only views (TABLES, COLUMNS, TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, …) with stable, portable names. It is a projection implemented on top of the engine's real dictionary, and it is the lowest common denominator — it describes standard concepts only, and only in standard vocabulary.

The native catalog is the dictionary itself: pg_class/pg_attribute/pg_index style tables, sys.objects/sys.indexes, Oracle's ALL_*/DBA_* views. It exposes everything the engine knows — physical size, index usage counters, partitioning, storage options, statistics, dependency graphs — none of which the standard covers.

Choose the standard surface for anything that must run across engines: a portable schema-diff, a generic ORM's introspection, a CI check. Choose native for operational work, or when the answer simply is not in the standard. Also note the cost gap: standard views are wide, permission-filtered joins and can be seconds slow on databases with many objects, where the equivalent native query is milliseconds.

`INFORMATION_SCHEMA` is defined by the SQL standard: a schema of read-only views (`TABLES`, `COLUMNS`, `TABLE_CONSTRAINTS`, `KEY_COLUMN_USAGE`, …) with stable, portable names. It is a **projection** implemented on top of the engine's real dictionary, and it is the lowest common denominator — it describes standard concepts only, and only in standard vocabulary. The native catalog is the dictionary itself: `pg_class`/`pg_attribute`/`pg_index` style tables, `sys.objects`/`sys.indexes`, Oracle's `ALL_*`/`DBA_*` views. It exposes everything the engine knows — physical size, index usage counters, partitioning, storage options, statistics, dependency graphs — none of which the standard covers. Choose the standard surface for anything that must run across engines: a portable schema-diff, a generic ORM's introspection, a CI check. Choose native for operational work, or when the answer simply is not in the standard. Also note the cost gap: standard views are wide, permission-filtered joins and can be seconds slow on databases with many objects, where the equivalent native query is milliseconds.

code

sql · 13 lines

sql

-- Portable (SQL standard view layer)
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'sales' AND table_name = 'orders';

-- Engine-native (pg_catalog-style): faster, and can also expose
-- storage/statistics columns the standard has no concept of
SELECT a.attname, format_type(a.atttypid, a.atttypmod)
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'sales' AND c.relname = 'orders'
  AND a.attnum > 0 AND NOT a.attisdropped;

go deeper

for a junior

Know that both exist, that INFORMATION_SCHEMA is the portable standard one, and that you can list tables and columns from either.

for a middle

Explain that the standard surface is a view layer over the real dictionary, limited to standard concepts, and that anything physical requires the native catalog.

for a senior

Add the performance gap at scale, privilege-filtering differences, and a policy for which surface tooling versus runbooks should use.

for a principal

Frame it as a coupling decision: portability and upgrade stability from the standard surface versus operational depth from native, and where each belongs in the platform's tooling.

Two views of one dictionary

There is only one source of truth — the engine's internal data dictionary. What differs is the interface you read it through.

INFORMATION_SCHEMA is specified by the SQL standard (ISO/IEC 9075, part 11). It is a set of read-only views with prescribed names and column names: TABLES, COLUMNS, VIEWS, TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS, ROUTINES, SCHEMATA and friends. Because the names are standardized, a query written against it usually runs unchanged on PostgreSQL, MySQL, SQL Server and others.

The native catalog is the engine's own dictionary, exposed directly: pg_catalog tables in PostgreSQL, sys.* catalog views in SQL Server, the DBA_*/ALL_*/USER_* dictionary views in Oracle, mysql.* data-dictionary tables (with SHOW statements and performance_schema alongside) in MySQL 8. Its shape is the engine's own, and it changes between major versions.

What the standard cannot tell you

The standard describes the logical database. Everything physical or engine-specific is absent:

how many pages/bytes a table or index occupies;
whether an index has ever been used, and how many scans it served;
table statistics: row estimates, distinct-value counts, most-common values, null fraction, when they were last gathered;
index method (B-tree, hash, GIN/GiST-style, columnstore), partial/filtered index predicates, included columns;
partitioning layout, tablespaces, fillfactor/storage parameters, compression;
dead-row estimates, last vacuum/analyze time, autovacuum activity;
dependency graphs between objects.

Any operational question — 'which indexes are unused', 'which table grew 40 GB last week', 'are these statistics stale' — must go native. There is no portable answer, because the underlying concepts are not portable.

The standard is also incomplete in the other direction: engines only implement the parts they support, columns can be nullable or approximated, and some engines return types as strings (character varying) that need mapping.

Cost is a real difference

INFORMATION_SCHEMA views are convenience joins across several dictionary tables, plus privilege filters applied per row. On a small schema nobody notices. On a database with 200k objects — a schema-per-tenant system, a warehouse with heavy partitioning — a single SELECT … FROM information_schema.columns WHERE table_name = ? can take seconds, because the view may materialize far more than the rows you asked for before filtering. The native equivalent, hitting an indexed dictionary table by object id, is typically a millisecond.

The practical rule: if the query runs once during a migration, use whichever is clearer. If it runs per request, per connection, or in a monitoring loop every 10 seconds, use the native catalog and cache the result.

Privilege filtering

Both surfaces filter by the caller's rights, but differently. INFORMATION_SCHEMA shows only objects you have some privilege on. Native surfaces vary: PostgreSQL's pg_class is broadly readable (row existence is visible even when the data is not), Oracle splits the same information into USER_* (mine), ALL_* (mine plus granted) and DBA_* (everything, privileged). Comparing a count from one surface against the other without accounting for this produces false 'missing object' alarms.

Choosing between them

Use INFORMATION_SCHEMA when portability is the point: a framework that must support several engines, a generic migration tool, a documentation generator, an assertion in CI like 'every foreign key column is indexed' that should keep working if the team switches engines.

Use the native catalog when you need depth (size, usage, statistics, physical layout), when you need speed, or when you are writing an operations query for one engine you actually run. Most real systems use both: standard views in cross-engine tooling, native views in the runbook.

A third consideration is stability. Native catalog shapes change across major versions — columns get renamed or split — so scripts that read them need review at upgrade time, while standard views are much steadier. That is a genuine maintenance argument for the standard surface where it suffices.

## Two views of one dictionary There is only one source of truth — the engine's internal data dictionary. What differs is the **interface** you read it through. **`INFORMATION_SCHEMA`** is specified by the SQL standard (ISO/IEC 9075, part 11). It is a set of read-only views with prescribed names and column names: `TABLES`, `COLUMNS`, `VIEWS`, `TABLE_CONSTRAINTS`, `KEY_COLUMN_USAGE`, `REFERENTIAL_CONSTRAINTS`, `ROUTINES`, `SCHEMATA` and friends. Because the names are standardized, a query written against it usually runs unchanged on PostgreSQL, MySQL, SQL Server and others. **The native catalog** is the engine's own dictionary, exposed directly: `pg_catalog` tables in PostgreSQL, `sys.*` catalog views in SQL Server, the `DBA_*`/`ALL_*`/`USER_*` dictionary views in Oracle, `mysql.*` data-dictionary tables (with `SHOW` statements and `performance_schema` alongside) in MySQL 8. Its shape is the engine's own, and it changes between major versions. ## What the standard cannot tell you The standard describes the *logical* database. Everything physical or engine-specific is absent: - how many pages/bytes a table or index occupies; - whether an index has ever been used, and how many scans it served; - table statistics: row estimates, distinct-value counts, most-common values, null fraction, when they were last gathered; - index method (B-tree, hash, GIN/GiST-style, columnstore), partial/filtered index predicates, included columns; - partitioning layout, tablespaces, fillfactor/storage parameters, compression; - dead-row estimates, last vacuum/analyze time, autovacuum activity; - dependency graphs between objects. Any operational question — 'which indexes are unused', 'which table grew 40 GB last week', 'are these statistics stale' — must go native. There is no portable answer, because the underlying concepts are not portable. The standard is also incomplete in the other direction: engines only implement the parts they support, columns can be nullable or approximated, and some engines return types as strings (`character varying`) that need mapping. ## Cost is a real difference `INFORMATION_SCHEMA` views are convenience joins across several dictionary tables, plus privilege filters applied per row. On a small schema nobody notices. On a database with 200k objects — a schema-per-tenant system, a warehouse with heavy partitioning — a single `SELECT … FROM information_schema.columns WHERE table_name = ?` can take seconds, because the view may materialize far more than the rows you asked for before filtering. The native equivalent, hitting an indexed dictionary table by object id, is typically a millisecond. The practical rule: if the query runs once during a migration, use whichever is clearer. If it runs per request, per connection, or in a monitoring loop every 10 seconds, use the native catalog and cache the result. ## Privilege filtering Both surfaces filter by the caller's rights, but differently. `INFORMATION_SCHEMA` shows only objects you have some privilege on. Native surfaces vary: PostgreSQL's `pg_class` is broadly readable (row existence is visible even when the data is not), Oracle splits the same information into `USER_*` (mine), `ALL_*` (mine plus granted) and `DBA_*` (everything, privileged). Comparing a count from one surface against the other without accounting for this produces false 'missing object' alarms. ## Choosing between them Use `INFORMATION_SCHEMA` when portability is the point: a framework that must support several engines, a generic migration tool, a documentation generator, an assertion in CI like 'every foreign key column is indexed' that should keep working if the team switches engines. Use the native catalog when you need depth (size, usage, statistics, physical layout), when you need speed, or when you are writing an operations query for one engine you actually run. Most real systems use both: standard views in cross-engine tooling, native views in the runbook. A third consideration is stability. Native catalog shapes change across major versions — columns get renamed or split — so scripts that read them need review at upgrade time, while standard views are much steadier. That is a genuine maintenance argument for the standard surface where it suffices.

You need to assert in CI that every foreign key column has a supporting index. Which surface do you use?
The foreign key definition and its columns are standard concepts, so INFORMATION_SCHEMA.TABLE_CONSTRAINTS plus KEY_COLUMN_USAGE gives you the FK side portably. The index side is not standardized — leading columns, partial predicates and index methods live only in the native catalog — so in practice the check is FK columns from the standard views joined against native index metadata, and it has to be written per engine.
Why can the same INFORMATION_SCHEMA query be instant on one database and take five seconds on another with identical hardware?
Object count. The views are wide joins with per-row privilege filtering, and the optimizer often cannot push a predicate on table_name down into the underlying dictionary scans, so it evaluates far more rows than you asked for. A database with hundreds of thousands of tables, columns and partitions makes that expensive, while the native lookup by object id stays indexed and fast.

saying these in an interview costs you the question

Saying INFORMATION_SCHEMA is just an alias or synonym for the native catalog
Expecting table size, index usage or statistics from the standard views
Assuming standard views cost nothing because 'it is only metadata'
Treating native catalog column names as stable across major versions
Explaining a missing object as corruption when it is privilege filtering

The SQL-standard INFORMATION_SCHEMA views and an engine's native catalog (for example a pg_catalog-style set of system tables, or sys.* / DBA_* dictionary views) both describe the same database. How do they differ, and when would you choose each?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Two views of one dictionary

What the standard cannot tell you

Cost is a real difference

Privilege filtering

Choosing between them

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

Two views of one dictionary

What the standard cannot tell you

Cost is a real difference

Privilege filtering

Choosing between them

answer

go deeper

context