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 human readable, PostgreSQL also provides the information_schema schema, where the meta information is wrapped and organized in views.

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

car_portal=# set ECHO_HIDDEN
car_portal=# z car_portal_app.car
********* 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 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'
') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':
  ' || pg_catalog.array_to_string(attacl, E'
  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'
') AS "Column access privileges"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
  AND c.relname ~ '^(car)$'
  AND n.nspname ~ '^(car_portal_app)$'
ORDER BY 1, 2;
**************************

                              Access privileges
     Schema     | Name | Type  | Access privileges | Column access privileges
----------------+------+-------+-------------------+--------------------------
 car_portal_app | car  | table |                   |
(1 row)

As seen in the preceding example, when the z 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_cataolg; it stores information about various relation types, as seen in the following list:

The relkind attribute in pg_class specifies the relation type. The following characters are used to identify relations:

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:

car_portal=# SELECT 'car_portal_app.car'::regclass::oid;
  oid
-------
 24807
(1 row)

car_portal=# SELECT 24807::regclass::text;
        text
--------------------
 car_portal_app.car
(1 row)

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

car_portal=# SELECT c.oid FROM pg_class c join pg_namespace n ON (c.relnamespace = n.oid) WHERE relname ='car' AND nspname ='car_portal_app';
  oid
-------
 24807
(1 row)

Another important table is pg_attribute; this table stores information about the table and other pg_class object columns. The pg_index table, as the name suggests, stores information about indexes. In addition to these, pg_depend and pg_rewrite are used to store information about dependent objects and rewrite rules for tables and views. The complete list of catalog tables can be found in the PostgreSQL online documentation.

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

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');

The following sections show some recipes that are often used in PostgreSQL. Some of these recipes might be used on a daily basis: such as SELECT pg_reload_conf(), which is used to reload the database cluster after amending pg_hba.conf or postgresql.conf, and SELECT pg_terminate_backend(pid), which is used to kill a certain process.