Data models

PostgreSQL's "data models" are its system catalogs — the tables that describe everything about the database. Every CREATE / ALTER / DROP statement is fundamentally an UPDATE on a system catalog. This page is a quick reference to the most-used catalogs; for the full list and per-column documentation see doc/src/sgml/catalogs.sgml and the pg_*.h headers.

Per-database catalogs

These catalogs live inside each database; you only see your own database's rows.

pg_class — relations

Every "relation" — table, view, materialized view, index, sequence, partitioned table, foreign table, composite type — is one row. Source: src/include/catalog/pg_class.h.

Key columns:

• oid — relation OID. Used in relfilenode for the on-disk file.
• relname — name.
• relnamespace — schema OID (pg_namespace.oid).
• reltype — corresponding row type (pg_type.oid).
• relkind — r=ordinary table, i=index, S=sequence, t=TOAST, v=view, m=matview, c=composite, f=foreign, p=partitioned, I=partitioned index.
• relpages, reltuples, relallvisible — physical statistics, updated by ANALYZE/VACUUM.
• relfilenode — physical file ID. Usually equals oid but diverges after CLUSTER, TRUNCATE, etc.
• reltablespace — tablespace OID, or 0 for default.
• relfrozenxid, relminmxid — wraparound bookkeeping.

pg_attribute — columns

One row per column per relation. Source: src/include/catalog/pg_attribute.h.

Key columns:

• attrelid, attname, attnum (1-based), atttypid, atttypmod, attlen, attnotnull, atthasdef, attisdropped, attidentity, attgenerated.

pg_type — types

Built-in and user-defined types. Source: src/include/catalog/pg_type.h.

Highlights:

• oid, typname, typnamespace.
• typtype — b=base, c=composite, d=domain, e=enum, r=range, m=multirange, p=pseudo.
• typlen, typbyval, typalign, typstorage — physical layout, governing TOAST eligibility and array packing.
• typinput, typoutput, typreceive, typsend — I/O functions (text and binary).
• typdelim, typelem, typarray — array support.

pg_proc — functions and procedures

Source: src/include/catalog/pg_proc.h.

Each function has: proname, pronamespace, prolang, provolatile (immutable, stable, volatile), proparallel (safe, restricted, unsafe), proargtypes, proallargtypes, proargmodes, prorettype, proretset, prosrc, probin. The proacl column holds privilege grants.

pg_operator — operators

Pairs of (oprleft, oprright, oprname, oprcode) plus selectivity hooks. Source: src/include/catalog/pg_operator.h. Operators are mostly "syntactic sugar" — every operator references a function in pg_proc.

pg_index — indexes

One row per index, in addition to its pg_class entry.

• indrelid — relation indexed.
• indkey — column attnums.
• indclass — operator classes (per column).
• indisprimary, indisunique, indisexclusion, indisvalid, indisready.

pg_constraint — constraints

check, foreign-key, primary-key, unique, trigger, xexclusion. Includes the constraint's relation, the involved attnums, and the check expression for CHECK constraints.

pg_namespace — schemas

Schemas (also called namespaces). nspname, nspowner, nspacl. Special schemas: pg_catalog, public, information_schema, pg_toast, pg_temp_*.

pg_depend, pg_shdepend — dependencies

Edges in the catalog object graph. Tells the engine to refuse a DROP of an object that's still referenced (or to cascade the drop). Read by pg_dump to topologically sort the dump.

Statistics catalogs

• pg_statistic — per-column planner statistics: ndistinct, MCV list and frequencies, histogram bounds, correlation. Hidden from non-superusers; the public-facing view is pg_stats.
• pg_statistic_ext and pg_statistic_ext_data — multi-column extended statistics (ndistinct, dependencies, MCV).

Replication

• pg_publication, pg_publication_rel, pg_publication_namespace — what to replicate (publisher side).
• pg_subscription, pg_subscription_rel — what to subscribe to (subscriber side).
• pg_replication_origin — track origin of changes for loop avoidance.

Other notable per-database catalogs

• pg_aggregate — aggregate function metadata.
• pg_amop, pg_amproc — operator-class strategy and support functions per index AM.
• pg_am — registered access methods.
• pg_cast — explicit/implicit/assignment casts.
• pg_collation — collations.
• pg_conversion — encoding conversions.
• pg_default_acl — default privilege grants.
• pg_event_trigger — DDL event triggers.
• pg_extension — installed extensions.
• pg_foreign_data_wrapper, pg_foreign_server, pg_user_mapping, pg_foreign_table — FDW objects.
• pg_inherits — table inheritance edges.
• pg_largeobject, pg_largeobject_metadata — large objects.
• pg_partitioned_table — partition-key info for partitioned tables.
• pg_policy — row-level security policies.
• pg_rewrite — view rules.
• pg_seclabel — security labels (used by sepgsql).
• pg_sequence — sequence parameters.
• pg_trigger — triggers.
• pg_ts_* — text search configurations, parsers, dictionaries.

Cluster-wide (shared) catalogs

These live outside any single database; their content is the same when queried from any DB.

• pg_database — list of databases. datname, datdba, encoding, datcollate, datctype, datfrozenxid.
• pg_authid — roles (with passwords; superuser-only). The view pg_roles is the public-friendly version.
• pg_auth_members — role membership.
• pg_tablespace — tablespaces.
• pg_pltemplate — historical, mostly empty.
• pg_replication_origin (also exists per-database) — origin-tracking entries.
• pg_subscription, pg_db_role_setting, pg_shdescription, pg_shseclabel, pg_shdepend.

Statistics views (pg_stat_*)

Cumulative runtime statistics, populated from shared memory:

• pg_stat_activity — one row per backend; includes the current/last query, state, wait event.
• pg_stat_replication — one row per walsender.
• pg_stat_replication_slots, pg_stat_subscription, pg_stat_subscription_stats.
• pg_stat_database, pg_stat_database_conflicts.
• pg_stat_all_tables, pg_stat_user_tables — per-table counters.
• pg_stat_all_indexes, pg_stat_user_indexes, pg_statio_*.
• pg_stat_bgwriter, pg_stat_checkpointer, pg_stat_archiver, pg_stat_wal, pg_stat_io, pg_stat_slru.
• pg_stat_progress_* — long-running operations (vacuum, copy, base backup, cluster).

These are views over functions defined in src/backend/utils/adt/pgstatfuncs.c that pull from the shared-memory stat collector.

Locks

pg_locks lists every currently held or waited heavy lock, with relation/database/transaction info. Joined with pg_stat_activity, it is the standard "why is X blocked?" diagnostic query.

Backwards compatibility

The catalog row structures are stable across minor releases but change at major releases. Each new major version bumps CATALOG_VERSION_NO, forcing existing data dirs to fail to start with the new server (you must pg_dump + restore, or use pg_upgrade).

User code that queries pg_catalog directly should be aware that:

• New columns may be added at major version boundaries.
• pg_class.relreplident, pg_class.relrowsecurity, pg_class.relispartition, pg_class.relhassubclass are examples of columns added over the years.
• Always consult the docs for the version you support.

For higher-level, SQL-standard-mandated views, query information_schema instead. Source: src/backend/catalog/information_schema.sql.