System Catalog

Every question asked so far in this course has been answered with a number: page count, fill, frame count, hit rate. The source of these numbers is the engine’s own records, and those records do not sit behind a special interface. The engine keeps schema information in ordinary tables and hands it out through ordinary queries. This entire set of tables is called the system catalog.

The catalog being queryable takes administration out of being a documentation task. Which columns which tables carry, which indexes exist, which constraints are defined — none of this sits written down in a hand-maintained list; it is written in the database itself, and that list is always current, because the statement that makes the change also updates it.

The Catalog Is an Ordinary Table

The block below sets up the library schema. The schema is familiar from earlier courses; indexes, a view, and a value check have been added to it here.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (
  branch_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  city    TEXT NOT NULL
);
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  name              TEXT NOT NULL,
  email             TEXT UNIQUE,
  registration_date TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','closed'))
);
CREATE TABLE book (
  book_id    INTEGER PRIMARY KEY,
  title      TEXT NOT NULL,
  author     TEXT NOT NULL,
  isbn       TEXT UNIQUE,
  branch_id  INTEGER NOT NULL REFERENCES branch(branch_id)
);
CREATE TABLE loan (
  loan_id     INTEGER PRIMARY KEY,
  member_id   INTEGER NOT NULL REFERENCES member(member_id),
  book_id     INTEGER NOT NULL REFERENCES book(book_id),
  pickup_date TEXT NOT NULL,
  return_date TEXT
);
CREATE INDEX loan_member ON loan(member_id);
CREATE INDEX loan_book_date ON loan(book_id, pickup_date);
CREATE VIEW open_loan AS SELECT * FROM loan WHERE return_date IS NULL;
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara');
INSERT INTO member (member_id,name,email,registration_date) VALUES (1,'Alice','[email protected]','2023-02-14');
INSERT INTO book VALUES (1,'Handbook of Astronomy','Y. Sterling','978-0201896831',1);
INSERT INTO loan VALUES (1,1,1,'2025-06-01',NULL);
SQL

The list of schema objects can be pulled with a single query.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;
SQL

type   name                       tbl_name 
-----  -------------------------  ---------
index  loan_book_date             loan     
index  loan_member                loan     
index  sqlite_autoindex_book_1    book     
index  sqlite_autoindex_member_1  member   
table  book                       book     
table  branch                     branch   
table  loan                       loan     
table  member                     member   
view   open_loan                  open_loan

Two rows in the list stand out as never having been written: the sqlite_autoindex_ prefixed indexes. No one defining the schema wrote these; the engine created them itself to enforce a uniqueness constraint. The catalog shows not just what was written, but the structure the engine built. This distinction is useful in administration: a column being unique means an index cost in the background, and that cost is visible right here.

The convention of separating internal object names with a prefix is specific to the engine; what does not change is that internal objects also live in the catalog.

Extracting an Inventory with a Query

Because the catalog is queryable, every question about the schema can be turned into a query. The query below produces a per-table column and constraint count.

sqlite3 library.db <<'SQL'
.headers on
.mode column
-- Schema inventory: column and constraint counts per table.
SELECT m.name AS table_name, count(*) AS columns, sum(ti."notnull") AS not_null,
       sum(ti.pk > 0) AS primary_key
FROM sqlite_schema m JOIN pragma_table_info(m.name) ti
WHERE m.type='table' GROUP BY m.name ORDER BY m.name;
SQL

table_name  columns  not_null  primary_key
----------  -------  --------  -----------
book        5        3         1          
branch      3        2         1          
loan        5        3         1          
member      5        3         1          

This table is the starting point of a schema design discussion: how many columns each table carries, how many are required, whether any table lacks a primary key. In a hand-maintained document, these three questions each need their own section, and all three go stale; pulled from the catalog, the query gives a current answer every time it runs.

Audits Written Against the Catalog

The catalog’s real value is that rules can be written as queries. The block below applies, in advance, a rule that will come up in the Indexes and Partitioning topic: every column carrying a foreign key should have its own index, otherwise deletes and updates on the parent table have to scan the child table from end to end.

sqlite3 library.db <<'SQL'
.headers on
.mode column
-- 1. Foreign keys left without an index: raises the cost of deletes and updates.
SELECT m.name AS table_name, f."from" AS column_name, f."table" AS target
FROM sqlite_schema m
JOIN pragma_foreign_key_list(m.name) f
WHERE m.type = 'table'
  AND NOT EXISTS (
    SELECT 1 FROM pragma_index_list(m.name) il
    JOIN pragma_index_info(il.name) ii
    WHERE ii.seqno = 0 AND ii.name = f."from"
  );
SQL

table_name  column_name  target
----------  -----------  ------
book        branch_id    branch

The audit produced a single finding: the book table’s reference to branch is unindexed. The loan table’s two references are covered — one by its own index, the other because it is the first column of a composite index. That a composite index’s first column, and only that column, does this job is the counterpart here of the rule established in the SQL Performance topic.

This audit’s advantage over a hand-maintained list is threefold. When a new table is added, the audit covers it automatically. When an index is dropped, the finding comes back automatically. And the audit reads what the engine actually knows, not what a person wrote — when the two diverge, the divergence becomes visible.

Other audits can be written with the same pattern: tables without a primary key, indexes that are never used, duplicate indexes sharing the same column list, status columns with no value check. Each one is an administrative rule in the form of a query.

Measuring Schema Drift

Because the catalog is queryable, two databases’ schemas can be compared. This is the operation most often needed during version upgrades and cross-environment consistency checks.

# The difference between two schemas: are the development and production copies the same?
sqlite3 production.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE,
                  registration_date TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    isbn TEXT UNIQUE, branch_id INTEGER NOT NULL REFERENCES branch(branch_id));
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES member(member_id),
                    book_id INTEGER NOT NULL REFERENCES book(book_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
CREATE INDEX loan_member ON loan(member_id);
SQL

compare() {
  sqlite3 "$1" "SELECT type||' '||name||' ('||coalesce((SELECT group_concat(ti.name||':'||ti.type, ', ')
                  FROM pragma_table_info(m.name) ti), '')||')'
                FROM sqlite_schema m WHERE name NOT LIKE 'sqlite_%' ORDER BY type, name;"
}
compare library.db    > development.list
compare production.db > production.list
echo "--- only in development ---"
comm -23 development.list production.list
echo "--- only in production ---"
comm -13 development.list production.list

--- only in development ---
index loan_book_date ()
table member (member_id:INTEGER, name:TEXT, email:TEXT, registration_date:TEXT, status:TEXT)
view open_loan (loan_id:INTEGER, member_id:INTEGER, book_id:INTEGER, pickup_date:TEXT, return_date:TEXT)
--- only in production ---
table member (member_id:INTEGER, name:TEXT, email:TEXT, registration_date:TEXT)

The diff shows three items. An index and a view exist only in the development copy. The member table exists on both sides, but it is not the same: a status column has been added in development and is missing in production. Including the column list in the comparison makes this difference visible; if only object names had been compared, the two tables would have counted as equal.

The direction of the drift is also information. A column present in development but absent in production means a migration that has not yet been applied. An object present in production but absent in development is more concerning: it points to a change written directly to production, with no counterpart in version control.

Writing to the Catalog

The catalog is read; it is not written by hand. Schema changes are made with data definition statements, and the engine takes on updating the catalog. Writing directly to catalog tables — even where it is technically possible in some engines — breaks the engine’s internal consistency: the physical layout in the data files and the definition in the catalog diverge, and that divergence usually shows up later as a hard-to-diagnose error on some subsequent read.

The practical form of the rule is this: migrations are written as statements and kept in version control; the catalog is read only to verify whether those migrations have been applied.

Summary

• The system catalog is the set of tables that hold the schema’s definitions and can be read with ordinary queries; because the statement that makes a change also updates it, the catalog is always current.
• The catalog shows not just what was written but the objects the engine builds on its own: the index behind a uniqueness constraint shows up in the list.
• Every administrative rule about the schema can be turned into a query; the unindexed foreign key audit produced a single finding in the model and covers new tables automatically.
• Comparing two copies’ schemas together with their column lists makes drift visible; comparing only object names would count a changed table as equal.
• The catalog is read, not written by hand: schema changes are made with statements, and the catalog is queried to verify that change was applied.

Next Step

The engine’s internals close out with this lesson: processes, pages, the log, checkpoints, version chains, cleanup, and the catalog that records all of it. One finding was left open in this section — the unindexed foreign key. The next topic opens the area that finding belongs to: how index types differ from each other, why column order changes the outcome in a composite index, when partial and covering indexes pay off, and which problem splitting a table into partitions solves and which it creates.