The system catalog

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...