Postgres pg_class and pg_attribute: Reading the System Catalogs Directly

Every Postgres operator eventually learns that information_schema is the official portable way to ask the database about itself, and that it is also slow, incomplete, and occasionally lies about what is actually in the cluster. The catalogs underneath are the system of record. They are also stable enough that production scripts can rely on them, in the sense that the Postgres project considers compatibility important and breaking changes within a major version essentially never happen.

The two catalogs that account for most introspection queries are pg_class and pg_attribute. The first contains one row per relation, where a relation is a generalization including tables, indexes, views, sequences, materialized views, foreign tables, partitioned tables, and TOAST tables. The second contains one row per column of every relation. Joined together, they describe the shape of essentially every queryable object in the database.

What pg_class actually contains

The columns that matter most often are oid (the object identifier used to join against other catalogs), relname (the unqualified name), relnamespace (the schema OID, joined against pg_namespace for the name), relkind (a single character describing what kind of relation this is), relpages and reltuples (planner statistics about size), and relhasindex and a handful of related booleans.

The relkind values worth memorizing are r for ordinary table, i for index, S for sequence, t for TOAST table, v for view, m for materialized view, c for composite type, f for foreign table, and p for partitioned table. The pattern is that a single letter conveys what kind of relation you are looking at, and a query for "all tables" actually has to specify which letters count.

The everyday query for "list all user tables and their approximate row counts" is:

SELECT n.nspname AS schema, c.relname AS table, c.reltuples::bigint AS approx_rows,
       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 c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

The information_schema equivalent is roughly five times slower, because the views compute permission checks that the catalogs leave to you. For interactive use during an incident, the catalog query is the difference between an answer that arrives instantly and an answer that takes a noticeable wait.

What pg_attribute actually contains

Every column of every relation has a row in pg_attribute. The columns to know are attrelid (joined against pg_class.oid to identify which relation the column belongs to), attname, atttypid (the type OID, joined against pg_type for the name), attnotnull, atthasdef, attnum (the column position), and attisdropped.

The attisdropped field is the column most people forget exists. When you drop a column from a table, Postgres does not actually remove the column from pg_attribute. Instead it marks the column dropped, renames it to a deterministic placeholder, and removes it from query visibility. The row stays in the catalog because removing it would require rewriting the table to compact the physical layout. Almost every catalog query that lists columns needs a WHERE NOT attisdropped clause to avoid seeing ghost columns.

The everyday query for "describe this table" using catalogs directly is:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS type,
       a.attnotnull AS not_null, pg_get_expr(d.adbin, d.adrelid) AS default_value
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'my_table'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

The 'my_table'::regclass cast is the canonical Postgres trick for resolving a name to an OID using the current search_path. The attnum > 0 filter excludes system columns like ctid and xmin, which have negative attnum values.

Why information_schema is not enough

The information_schema views are defined by the SQL standard, which means they expose the subset of catalog information that every SQL database is required to support. Postgres has substantially more information in its catalogs than the standard contemplates, including statistics, storage parameters, partition information, index expressions, and many features that have no standard analog.

The catalogs also expose information accurately for the current session and database, while information_schema sometimes filters based on permissions in ways that produce empty results when you have permission to query the underlying catalogs but not the standardized view. The combination of speed, completeness, and consistency makes the catalogs the right tool for operational scripts even though the documentation typically points to information_schema as the polite first answer.

The pg_index complement

Index introspection is similar but uses pg_index as the join partner. The everyday query for "show indexes on this table with their definitions" is:

SELECT i.relname AS index_name, pg_get_indexdef(idx.indexrelid) AS definition,
       idx.indisunique AS is_unique, idx.indisprimary AS is_primary
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
WHERE idx.indrelid = 'my_table'::regclass
ORDER BY i.relname;

The pg_get_indexdef function returns the CREATE INDEX statement that would recreate the index, which is the right thing to log before any DDL operation that might need to be reversed. The function is part of the small family of pg_get_* helpers that turn catalog rows into the SQL text that produced them: pg_get_viewdef, pg_get_functiondef, pg_get_constraintdef, and pg_get_triggerdef are the others worth knowing.

The catalog joins that turn introspection into operations

The combination of these catalogs supports queries that are awkward or impossible in information_schema. The "tables that have grown unexpectedly since last week" query joins pg_class against a saved snapshot. The "columns that are NOT NULL but have all-null values in the current data" query joins against the table contents using dynamic SQL. The "indexes that have never been used" query joins pg_index against pg_stat_user_indexes.

The pattern is that operational questions are mostly catalog queries, and the catalogs are designed to support exactly the kind of joins you need to answer them. The reason the queries do not appear in standard documentation is that the documentation focuses on portable SQL, and operational questions are almost always database-specific.

What the catalogs do not tell you

The catalogs describe the structure of the database, not its content. Row-level questions require querying the underlying tables. The catalogs also lag slightly behind reality: reltuples is updated by VACUUM and ANALYZE rather than after every modification, so a table that has just received a million inserts will report its old row count until the next statistics update.

The catalogs also do not show the cluster level. pg_class is per-database, and a query against it shows only relations in the current database. Cluster-wide introspection requires pg_database plus a connection to each database, which is awkward enough that most monitoring tools just iterate over pg_database and connect repeatedly.

What this lets us do at Anethoth

Our four products run on SQLite, which has its own catalog story via sqlite_master and the PRAGMA family. The catalog content is much thinner because SQLite intentionally keeps less metadata about itself. The migration plan to Postgres includes building an introspection layer that uses pg_class and pg_attribute directly for the operational queries we currently run by parsing .schema output, because the catalog approach is both faster and more reliable.

The deeper observation is that databases that consider themselves part of the SQL standard tradition expose a polite portable interface plus a richer native one, and the native interface is almost always the right tool for production operations. The portable interface exists for compatibility; the native interface exists for the work that the database actually does.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.