PostgreSQL describes all database objects using the meta information stored in database relations. These relations hold information about tables, views, functions, indexes, foreign data wrappers (FDWs), triggers, constraints, rules, users, groups, and so on. This information is stored in the pg_catalog schema, and to make it more readable by humans, PostgreSQL also provides the information_schema schema, in which the meta information is wrapped and organized into views.

In the psql client, one can see exactly what is happening behind the scenes when a certain meta command is executed, such as z, by enabling ECHO_HIDDEN. The ECHO_HIDDEN or -E switch allows users to study the system catalog tables of PostgreSQL. You need to run the following command:

postgres=# set ECHO_HIDDEN
postgres=# d 
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
Did not find any relations.

As seen in the preceding example, when the d meta command is used, the query is sent to the database server backend. In addition to ECHO_HIDDEN, one can have a peek at the information_schema and pg_catalog views, as follows:

 SELECT * FROM information_schema.views where table_schema IN ('pg_catlog', 'information_schema');

The pg_catalog and information_schema views contain hundreds of views, tables, and administration functions; for this reason, only some of the common and heavily used catalog tables will be described.

The pg_class table is one of the main tables in pg_catalog; it stores information about various relation types, including tables, indexes, views, sequences, and , composite types. The relkind attribute in pg_class specifies the relation type. The following characters are used to identify relations: 

• r: Ordinary table
• i: Index
• S: Sequence
• t: TOAST table
• v: View
• m: Materialized view
• c: Composite type
• f: Foreign table
• p: Partitioned table

As this table is used to describe all relations, some columns are meaningless for some relation types.

The relations are identified by object identifiers. These identifiers are used as primary keys in the pg_catalog schema, so it is important to know how to convert object identifiers (OID) into text to get the relation name. Also, note that the OIDs have types; for example, the regcalss type is used to identify all relations stored in pg_class, while the regprocedure type is used to identify functions. The following example shows how to convert a table name to OID and vice versa:

postgres=# SELECT 'pg_catalog.pg_class'::regclass::oid;
 oid 
------
 1259
(1 row)

postgres=# SELECT 1259::regclass::text;
   text 
----------
 pg_class
(1 row)

Another approach is to use pg_class and pg_namespace to get the oid as follows:

SELECT c.oid FROM pg_class c join pg_namespace n ON (c.relnamespace = n.oid) WHERE relname ='pg_class' AND nspname ='pg_catalog';
 oid 
------
 1259
(1 row)

Another important table is pg_attribute; this table stores information about tables and other pg_class object columns. The pg_index table, as the name suggests, stores information about indexes. In addition, pg_depend and pg_rewrite are used to store information about dependent objects and rewrite rules for tables and views.

Another important set of tables and views are pg_stat<*> relations; these tables and views provide statistics about tables, indexes, columns, sequences, and so on. Information stored in these tables is highly valuable for debugging performance issues and usage patterns. The following query shows some of the static relations:

SELECT relname, case relkind when 'r' then 'table' WHEN 'v' THEN 'VIEW' END as type FROM pg_class WHERE relname like 'pg_sta%' AND relkind IN ('r','v') LIMIT 5 ;
           relname | type 
-----------------------------+-------
 pg_statistic | table
 pg_stat_user_tables | VIEW
 pg_stat_xact_user_tables | VIEW
 pg_statio_all_tables | VIEW
 pg_statio_sys_tables | VIEW