System Catalogs

PostgreSQL stores its own schema in system catalogs (pg_catalog). The SQL-standard information_schema views wrap a subset for portability. Operators and automation should know both: catalogs are complete; information_schema is familiar.

Recipe

-- Tables in current database (catalog style)
SELECT n.nspname, c.relname, pg_total_relation_size(c.oid) AS bytes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','m')
  AND n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY bytes DESC
LIMIT 20;

When to reach for this: You need to introspect schemas, columns, constraints, or sizes without external tools.

Working Example

-- Column detail with types and nullability
SELECT table_schema, table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'customers'
ORDER BY ordinal_position;
 
-- Constraints on a table
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'app.customers'::regclass;

What this demonstrates:

• information_schema.columns lists user-facing column metadata
• pg_constraint stores CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY definitions
• pg_total_relation_size includes indexes and toast for capacity planning

Deep Dive

Key Catalogs

information_schema vs pg_catalog

• information_schema filters to SQL-standard objects and hides implementation details.
• pg_catalog is authoritative for partitions, policies, triggers, and storage parameters.
• Join catalogs when you need OIDs, relfilenode, or amname (access method).

Discovery Tips

• Regclass casts ('app.customers'::regclass) resolve names using search_path.
• psql \d+ tablename is a thin wrapper over catalog queries.

Gotchas

• OID wraps on long-lived clusters - Very old clusters rarely hit this; still use names in app code.. Fix: Prefer regclass/regtype casts in admin scripts.
• information_schema omits partitions detail - Declarative partition children may look like ordinary tables.. Fix: Query pg_inherits and pg_partitioned_table.
• pg_attribute.attnum gaps - Dropped columns leave holes; filter attnum > 0 and not dropped.. Fix: Use information_schema for portable column lists.
• Stats views are not catalogs - pg_stat_* are views over shared memory snapshots.. Fix: Reset stats with pg_stat_reset() cautiously in prod.
• search_path affects regclass - Same unqualified name can resolve differently per role.. Fix: Schema-qualify in tooling and migrations.

Alternatives

FAQs

Related

• PostgreSQL Basics - first catalog queries
• Schema Design Basics - naming and keys
• DDL Transactions - catalog updates during migrations

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.