Data Organization
System Catalog
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
use of course materials. Postgres Professional company specifically disclaims
any warranties on course materials. Course materials are provided “as is,”
and Postgres Professional company has no obligations to provide
maintenance, support, updates, enhancements, or modifications.
2
Topics
What Is the System Catalog and how to Access It
System Catalog Objects and their Location
Object Naming Rules
Special Data Types
3
System Catalog
A set of tables and views
describing all objects in a database cluster
Schemas
primary schema: pg_catalog
alternative representation: information_schema (SQL standard)
SQL access
view: SELECT
change: CREATE, ALTER, DROP
psql access
commands for convenient data visualization
The system catalog is a collection of tables and views that describe all
database objects. It is metadata for the contents of the cluster:
Starting from version 14 of PostgreSQL, primary keys and unique
constraints have been added for most system catalog tables.
You can access this metadata using regular SQL queries. SELECT
commands can give you a description of an object, and DDL (Data Definition
Language) commands let you add and modify objects.
All system catalog tables and views are located in the pg_catalog schema.
There is another schema, as defined by the SQL standard:
information_schema. It is more stable and portable than pg_catalog, but
does not reflect specific features of PostgreSQL.
Client programs can read the contents of the system catalog and display it to
the user in a convenient way. For example, GUI-based development and
management environments usually come with a hierarchical object
navigation tool.
The psql client also offers a number of convenient built-in commands
specifically designed for working with the system catalog. Most of these
commands start with \d (as in “describe”). For the full list of commands and
their descriptions, see:
We will look at the most commonly used ones in the demo.
The course materials also include the catalogs.pdf file that features a
diagram of the main system catalog tables and related psql commands.
4
Cluster-Level Objects
appdb
pg_catalog
table
table
object
public
table
table
object
postgres
pg_catalog public
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
objects in
this database
shared
cluster objects
schema
table
table
object
table
table
object
In a database cluster, each database has its own set of system catalog
tables. However, there are several system catalog objects that are shared
between all cluster databases. The most obvious example is the list of the
databases themselves.
These tables are stored outside of any single database, but at the same time
they are accessible from any database within the cluster.
5
Naming Rules
Object (table, view) and column name prefixes
Object names are always lowercase
pg_database.datname
common prefix
for all objects
column prefix
(usually derived from
the object name)
All system catalog tables and views begin with the prefix pg_. In order to
avoid potential conflicts, it is not recommended to create your own objects
starting with pg_.
Column names have a three-letter prefix, which is usually derived from the
name of the table. There is no underscore after the prefix. There are some
exceptions to this rule, such as the oid column and others.
Object names are always stored in lowercase.
7
Special Data Types
oid type – object identifier
oid column ensures object uniqueness in system catalog tables
integer with an auto increment
reg* types
oid aliases for some system catalog tables
(regclass for pg_class, etc.)
converting the text name of an object to the oid type and vice versa
Most system catalog tables use a column with oid name and data type of
the same name as a primary key.
The oid (Object Identifier) type is a 32 bit integer (about 4 billion possible
values) with an auto increment.
There are several special data types (in fact, oid aliases) starting with reg
that are used to convert object names to oids and back.
9
Takeaways
The system catalog contains cluster metadata stored within the
cluster itself
SQL access and additional psql commands
Some system catalog tables are stored in databases,
some are shared across the entire cluster
The system catalog uses special data types
10
Practice
1. Get a description of the pg_class table.
2. Get a detailed description of the pg_tables view.
3. Create a database and a temporary table in it.
Get a complete list of schemas in the database, including system
schemas.
4. Get a list of views in the information_schema.
5. What queries does the following psql command perform?
\d+ pg_views
