4.4 System Catalogs & Information Schema

4.4 System Catalogs & Information Schema

[!NOTE] Self-Describing Database Engine: In PostgreSQL, metadata is treated as first-class relational data. The database describes itself using regular tables and views that can be queried with standard SELECT statements. Understanding the physical system catalogs (pg_catalog) and the ANSI SQL standard views (information_schema) provides the foundation for deep diagnostics and administrative mastery.

A central topic on the PostgreSQL Associate certification is navigating these system catalogs, interpreting key metadata columns, and understanding why and when to query pg_catalog versus information_schema.

---

System Catalog Architecture: The pg_catalog Schema

Every PostgreSQL database automatically provisions the pg_catalog schema. This schema houses the core system catalog tables, built-in data types, built-in functions, operators, and aggregate definitions.

Special Namespace Rules

• Precedence on search_path: Even if an administrator sets search_path = public, custom, PostgreSQL implicitly searches pg_catalog first before any user-defined schema, unless pg_catalog is explicitly placed later in the search_path string. This guarantees that built-in system functions and operators are always resolvable.
• Catalog Mutability: System catalogs are standard tables, but direct DML (INSERT, UPDATE, DELETE) against them is strictly disabled for normal users and strongly discouraged even for superusers. Modifying catalog tables directly bypasses integrity checks and can cause immediate cluster corruption. DDL statements (CREATE TABLE, ALTER ROLE, DROP INDEX) safely manage catalog mutations on the user's behalf.

---

Foundational Catalog Tables in pg_catalog

While PostgreSQL contains dozens of catalog tables, certification candidates must thoroughly understand the foundational catalogs that manage relations, databases, namespaces, roles, and columns:

                                +-------------------+
                                |   pg_database     |
                                | (Cluster DB list) |
                                +-------------------+
                                          │ (datname)
                                          ▼
+-------------------+           +-------------------+
|  pg_tablespace    |           |   pg_namespace    |
|  (Storage paths)  |           | (Schema/Namespace)|
+-------------------+           +-------------------+
          │                               │ (oid = relnamespace)
          └───────────────┬───────────────┘
                          ▼
                +-------------------+
                |     pg_class      | <─── Core relation catalog
                | (Tables, Indexes) |      (relkind, reltuples, relpages)
                +-------------------+
                   │             │
   (attrelid = oid)│             │ (indrelid / indexrelid)
                   ▼             ▼
          +----------------+  +----------------+
          |  pg_attribute  |  |    pg_index    |
          | (Table Columns)|  | (Index Details)|
          +----------------+  +----------------+

1. pg_class: The Relation Catalog

pg_class is the central catalog relation. Every database entity that has columns or disk storage—including regular tables, indexes, sequences, views, materialized views, composite types, and TOAST tables—has a corresponding row in pg_class.

Key Columns in pg_class

• oid: The unique Object Identifier assigned to the relation.
• relname: Name of the relation (table, index, view, sequence).
• relnamespace: OID referencing pg_namespace (the schema containing the relation).
• reltype: OID referencing pg_type (the composite data type representing rows of this table).
• relowner: OID referencing pg_authid (the owning role).
• relkind: A single character identifying the specific relation type:
  • 'r': Ordinary heap table (relation)
  • 'i': Index
  • 'S': Sequence
  • 'v': View
  • 'm': Materialized view
  • 'c': Composite type
  • 't': TOAST table (oversized attribute storage table)
  • 'f': Foreign table
  • 'p': Partitioned table
  • 'I': Partitioned index
• reltuples: Estimated number of rows in the relation (updated by VACUUM and ANALYZE). Used directly by the query planner.
• relpages: Size of the relation's on-disk image measured in 8KB pages.
• relfilenode: Physical filename on the filesystem under $PGDATA/base/DB_OID/.

-- Query the largest tables and their disk footprints from pg_class
SELECT relname, relkind, reltuples::bigint, relpages, 
       pg_size_pretty(relpages::bigint * 8192) AS total_size
FROM pg_catalog.pg_class
WHERE relkind = 'r'
ORDER BY relpages DESC
LIMIT 5;

2. pg_database: Database Cluster Catalog

Located in the pg_global shared tablespace, pg_database stores metadata for all databases provisioned across the cluster:

• datname: Database name.
• datdba: OID of the database owner role.
• encoding: Character encoding integer ID (e.g., UTF8 = 6).
• datcollate: Collation rules used for sorting text.
• datctype: Character classification rules (upper/lowercase conversion).
• dattablespace: Default tablespace OID used to store this database's files.

3. pg_tablespace: Physical Tablespaces

Also stored globally across the cluster, pg_tablespace records storage locations:

• spcname: Tablespace name (e.g., pg_default, pg_global, fast_nvme).
• spcowner: OID of the owning role.
• spcoptions: Storage parameters (such as seq_page_cost and random_page_cost overrides).

4. pg_namespace: Schema Namespaces

pg_namespace stores schemas (namespaces) within the active database:

• nspname: Name of the schema (e.g., public, pg_catalog, information_schema, sales).
• nspowner: OID of the schema owner.
• nspacl: Access control list (privileges granted via GRANT).

5. pg_authid vs. pg_roles: Role Metadata & Password Protection

PostgreSQL manages user accounts and security groups through roles. However, it strictly separates physical storage from unprivileged access:

• pg_authid: The underlying physical catalog table. Stores rolname, rolsuper (superuser), rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, and rolpassword (the encrypted SCRAM-SHA-256 or MD5 password hash).

  [!CAUTION] Access Restriction: Read access to pg_authid is strictly restricted to superusers. Unprivileged database users cannot read pg_authid to prevent offline brute-force attacks against password hashes.

• pg_roles: A public system view defined on top of pg_authid. For non-superusers, pg_roles masks the rolpassword column with ******** (or null) while exposing non-sensitive attributes (rolname, rolsuper, rolcreatedb, rolcanlogin). This allows regular users to inspect role privileges safely.

6. pg_attribute: Table Columns

Defines every column across every relation in pg_class:

• attrelid: OID of the relation in pg_class to which this column belongs.
• attname: Column name.
• atttypid: OID referencing pg_type (data type).
• attnum: Column position number (1 for the first user column, 2 for the second). Note: Negative numbers denote PostgreSQL system columns (e.g., -1 for ctid, -3 for xmin, -4 for cmin, -5 for xmax).
• attnotnull: Boolean flag indicating if a NOT NULL constraint applies.

7. pg_index: Index Specifications

Records index mechanics:

• indexrelid: OID of the index in pg_class.
• indrelid: OID of the indexed table in pg_class.
• indisunique: Boolean flag indicating if the index enforces a UNIQUE constraint.
• indisprimary: Boolean flag indicating if the index enforces a PRIMARY KEY.
• indkey: An array of column numbers (attnum values) included in the index key.

---

The ANSI SQL information_schema

While pg_catalog is PostgreSQL-specific, the information_schema is an ANSI SQL standard metadata schema (defined by ISO/IEC 9075). It provides standardized, portable views that present database metadata in a vendor-neutral structure identical across PostgreSQL, MySQL, Microsoft SQL Server, and Oracle.

Standard Views in information_schema

• information_schema.tables: Lists tables and views with standard columns: table_catalog, table_schema, table_name, table_type (BASE TABLE, VIEW, LOCAL TEMPORARY).
• information_schema.columns: Column metadata: table_name, column_name, ordinal_position, column_default, is_nullable, data_type.
• information_schema.schemata: Schemas: catalog_name, schema_name, schema_owner.
• information_schema.table_constraints: Constraints: constraint_name, constraint_type (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK).
• information_schema.views: Text definitions of views (view_definition).

-- ANSI-compliant query to find all nullable columns in the customers table
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' 
  AND table_name = 'customers';

---

Deep Comparison: pg_catalog vs. information_schema

Understanding the architectural tradeoffs between these two metadata systems is a common certification topic:

---

Reverse-Engineering psql: The psql -E Flag

How does psql implement its informational backslash commands? It executes standard SQL queries against pg_catalog.

Administrators can inspect these underlying queries by launching psql with the -E (or --echo-hidden) flag:

psql -E -U postgres -d sales

When -E is active, typing any meta-command (such as \dt) prints the exact SQL query executed by psql before printing the formatted output:

sales=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind 
    WHEN 'r' THEN 'table' 
    WHEN 'v' THEN 'view' 
    WHEN 'm' THEN 'materialized view' 
    WHEN 'i' THEN 'index' 
    WHEN 'S' THEN 'sequence' 
    WHEN 's' THEN 'special' 
    WHEN 'f' THEN 'foreign table' 
    WHEN 'p' THEN 'partitioned table' 
  END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

              List of relations
 Schema |     Name      |     Type     |  Owner   
--------+---------------+--------------+----------
 public | customers     | table        | postgres
 public | orders        | table        | postgres
(2 rows)

Using psql -E is a proven technique for mastering PostgreSQL catalogs, as it reveals production-tested joins across pg_class, pg_namespace, and internal utility functions like pg_table_is_visible().

---

Exam Tips and Common Pitfalls

• Exam Trap: Relkind Identifiers: Know the single-character codes in pg_class.relkind: 'r' = regular table, 'i' = index, 'S' = sequence, 'v' = view, 'm' = materialized view, 'p' = partitioned table. A question asking for all base tables and partitioned tables requires WHERE relkind IN ('r', 'p').
• Exam Trap: pg_authid vs. pg_roles: Remember that pg_authid contains the actual hashed password (rolpassword) and can only be accessed by superusers. Non-superusers querying pg_roles see a masked placeholder.
• Exam Trap: pg_catalog vs. information_schema Speed: If an exam question asks why an administrative tool queries pg_catalog rather than information_schema, the reasons are comprehensive coverage of PostgreSQL-specific engine features and faster query execution.