Logical structure

For DBAs and operators

Shared versus per-database catalogs

Most system catalogs (pg_class, pg_attribute, pg_namespace, pg_proc …) exist separately in each database. A handful are shared across the cluster and stored in global/: pg_database, pg_authid, pg_auth_members, pg_tablespace, pg_shdepend, pg_db_role_setting, pg_shdescription, pg_shseclabel, pg_parameter_acl, pg_subscription and pg_replication_origin. That is why roles are visible from every database while privileges on tables are recorded per database.

Templates

CREATE DATABASE copies template1 by default (since PostgreSQL 15 using WAL-logged block copies, STRATEGY = wal_log). template0 is a pristine copy you use when you need a different encoding or locale, or to restore a dump. Objects you add to template1 appear in every new database.

Crossing databases

There is no cross-database query. Trying it gives:

Use postgres_fdw or dblink, or put the data in different schemas of the same database.

Schemas and search_path

Unqualified names are resolved through search_path (default "$user", public). pg_catalog is always searched first unless listed explicitly, and the session's temporary schema (pg_temp_N) comes before everything for tables. Since PostgreSQL 15, ordinary users can no longer create objects in public by default (it is owned by pg_database_owner). Functions with SECURITY DEFINER should set their own search_path.

Relations and relkind

Tables, indexes, sequences, views, materialized views, composite types, TOAST tables, foreign tables and partitioned tables are all rows in pg_class, distinguished by relkind (r i S v m c t f p I). Each relation has an oid as its catalog identity. Storage-bearing relations such as ordinary tables, indexes, sequences, materialized views and TOAST tables normally have a relfilenode that identifies their current physical storage. Views, foreign tables and partitioned parents do not have their own local relation file, and mapped system catalogs use PostgreSQL's relation-mapping mechanism instead of storing the physical file number directly in pg_class.relfilenode. A rewrite can change the physical file number while the relation OID remains the same.

SELECT c.oid, n.nspname, c.relname, c.relkind, c.relfilenode,
       coalesce(t.spcname, 'pg_default') AS tablespace
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
 WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
   AND n.nspname NOT LIKE 'pg_toast%'
 ORDER BY 2, 3;

SELECT oid, datname, pg_size_pretty(pg_database_size(oid)) FROM pg_database;
SELECT 'orders'::regclass::oid;   -- resolve a name through search_path

OIDs are 32-bit and wrap around, but uniqueness is enforced per catalog, so when the OID counter wraps around, OIDs already in use in that catalog are skipped instead of causing errors. User tables no longer get a hidden oid column (removed in PostgreSQL 12).