The PostgreSQL system catalog and system administration functions can aid both developers and administrators to keep the database clean and performant. System catalogs can be used to automate several tasks, such as finding tables without indexes, finding dependencies between database objects, and extracting information about the database through health checks, such as table bloats, database size, locks, and so on. Information extracted from the system catalog can be employed in monitoring solutions such as Nagios and in dynamic SQL. This chapter will be formatted a little bit differently and follow a cookbook approach.
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:
Characters |
Tables |
---|---|
|
Relations |
|
Views |
|
Materialized views |
|
Foreign tables |
|
Toast tables |
|
Indexes |
|
Composite type |
As this table is used to describe all relations, some columns are meaningless for some relation types.
One could think of The Oversized-Attribute Storage Technique (TOAST) as a vertical partitioning strategy. PostgreSQL does not allow tuples to span multiple pages where the page size is often 8 KB; therefore, PostgreSQL stores, breaks, and compresses large objects into several chunks and stores them in other tables called TOAST tables.
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.
18.191.222.5