What is the difference between a catalog and a database?

A database stores the application's data, and a database catalog stores the metadata that describes that database. Metadata is data about data. It includes the table names, the columns and their types, the indexes, the views, and the constraints. It also records which users may read or change each object. The catalog is also called the system catalog or the data dictionary. The DBMS, which is the software that manages the database, reads the catalog every time it parses a query. The catalog is therefore part of every database rather than a separate product.

What a Database Catalog Contains

The catalog holds one row for every object the DBMS knows about. For each table there is a row with its name, its schema, its owner, and its storage location. For each column there is a row with its data type, whether it allows null, and its default value. Indexes, views, stored procedures, triggers, sequences, and constraints each have their own catalog tables.

The catalog also holds security data. Users and roles are catalog rows, and so is every GRANT that gives a role access to an object. Most systems add statistics to the catalog as well. Examples are the number of rows in a table and the spread of values in a column. The query planner reads those statistics to choose between an index scan and a full table scan.

How to Query the Catalog

Every major relational DBMS lets you read its catalog with ordinary SQL. The standard route is the information_schema set of views, which PostgreSQL, MySQL, and SQL Server all provide. The query below lists the columns of one table.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Each product also has its own, more detailed catalog. PostgreSQL keeps it in the pg_catalog schema, with tables such as pg_class for relations and pg_attribute for columns. SQL Server exposes it through the sys views, such as sys.tables and sys.columns. MySQL uses information_schema and, for statistics, the mysql and performance_schema databases. Oracle calls it the data dictionary and exposes it through views prefixed USER_, ALL_, and DBA_.

Catalog, Schema, and Database in SQL

The SQL standard defines a hierarchy of catalog, then schema, then table, and vendors apply it differently. In the standard, a catalog is a named collection of schemas. A schema is a named collection of tables and other objects. This is why information_schema.tables has both a table_catalog column and a table_schema column.

PostgreSQL treats each database as one catalog, so table_catalog holds the database name, and schemas such as public are contained in it. MySQL treats "database" and "schema" as the same thing, and its table_catalog column always holds the constant def. SQL Server also equates catalog with database. That is why a connection string accepts either Initial Catalog=Sales or Database=Sales with identical meaning.

A Data Catalog Is a Different Tool

A data catalog is an inventory of the datasets across an organization, not a part of one DBMS. It records which databases, tables, files, and dashboards exist and who owns each. It also records what the columns mean in business terms and where the data came from. Analysts search it to find a dataset before they query it. A data marketplace adds a request-and-approve workflow, so a team can ask for access to a dataset from inside the tool. A data catalog reads table and column names from the database catalogs described above, but the two serve different readers.

Why the Catalog Matters in Design

The catalog is the reason a DBMS can enforce structure. When an application inserts a row, the DBMS checks the catalog for the column list, the types, and the constraints. Anything that does not match is rejected. When a query arrives, the planner reads the catalog statistics to estimate costs. When a migration adds a nullable column, the change is mostly a write to the catalog. That is why the operation is fast on a large table in most systems. A design review that starts by reading the catalog sees the real schema. It includes indexes and constraints that a diagram may have omitted.