System catalogs: how PostgreSQL describes itself

conceptThe concept

The catalogs live in the pg_catalog schema. The ones you will use most:

Rows are identified by OIDs. Casting a name to regclass ('orders'::regclass) resolves it through the search_path to an OID, and casting an OID back prints a readable name. The pg_get_indexdef, pg_get_constraintdef, pg_get_viewdef and pg_get_functiondef functions turn catalog rows back into DDL.

information_schema is the SQL-standard view layer over the catalogs. It is portable but slower, hides PostgreSQL-specific details (index types, partitioning, storage) and only shows objects you have privileges on.

whyWhy it matters

Catalog queries are how you audit a database you did not design: tables without primary keys, foreign keys without indexes, duplicate indexes, oversized columns, objects owned by the wrong role. They are also the basis of every schema-diff and migration tool.

Use caseReal-world scenario

Before a migration, a DBA generates a report of every table with its row estimate, size, primary key and index count straight from the catalogs, then compares it with the same report after the migration.

Try itExamples

Run and edit these in the interactive lesson.

SELECT c.relname, c.relkind, c.reltuples::bigint AS est_rows, c.relpages,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
ORDER BY pg_total_relation_size(c.oid) DESC;

SELECT a.attnum, a.attname, format_type(a.atttypid, a.atttypmod) AS type, a.attnotnull
FROM pg_attribute a
WHERE a.attrelid = 'orders'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

SELECT indexrelid::regclass AS index_name, pg_get_indexdef(indexrelid) AS definition
FROM pg_index WHERE indrelid = 'order_items'::regclass;
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint WHERE conrelid = 'order_items'::regclass ORDER BY conname;

SELECT 'orders'::regclass::oid AS orders_oid,
       'orders'::regclass AS back_to_name,
       'numeric(12,2)'::regtype AS type,
       'pgm.plan'::regproc AS function;

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'customers'
ORDER BY ordinal_position;

internalsUnder the hood

The catalogs are ordinary heap tables with MVCC, so DDL is transactional: a CREATE TABLE inside a rolled-back transaction leaves no catalog rows behind. Each backend caches catalog rows (the relcache and syscache); DDL sends invalidation messages so other sessions reload them. Statistics such as reltuples and relpages in pg_class are estimates updated by VACUUM and ANALYZE, which is why they are cheap to read.

Avoid / preferDo this, not that

SELECT * FROM pg_class WHERE relname = 'orders';

SELECT * FROM pg_class WHERE oid = 'orders'::regclass;

SELECT column_name FROM information_schema.columns
WHERE table_name = 'orders';

SELECT attname FROM pg_attribute
WHERE attrelid = 'public.orders'::regclass
  AND attnum > 0 AND NOT attisdropped;

PitfallsCommon mistakes

• Forgetting NOT attisdropped and listing ghost columns from earlier ALTER TABLE DROP COLUMN.
• Treating reltuples as an exact count. It is the estimate from the last VACUUM or ANALYZE, and is -1 for a table never analyzed.
• Writing catalog queries against relkind r only and missing partitioned tables (p).

LimitsWhen not to use it

Do not modify catalogs directly with UPDATE or DELETE. It bypasses every consistency check and is a classic way to corrupt a cluster. Use DDL, and for statistics use ANALYZE.

SecuritySecurity notes

Most catalogs are readable by every role, so object names, column names and function source are visible to all users of a database even without table privileges. Do not put secrets in function bodies, comments or default values. pg_authid (password hashes) is superuser-only; pg_roles is the safe view.

ChallengeTry it yourself

SELECT c.relname,
       (SELECT count(*) FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped) AS columns,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS indexes,
       EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p') AS has_pk
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind IN ('r', 'p')
ORDER BY c.relname;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• System catalogs
• System information functions
• Object identifier types