Data Organization
System catalog
13
Copyright
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
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:
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 locations
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 view: information_schema (the SQL standard)
SQL access
view: SELECT
update: CREATE, ALTER, DROP
psql access
quality-of-life commands
The system catalog is a collection of tables and views that describe all
database objects. It is metadata for the contents of the cluster.
You can access this metadata using regular SQL queries. SELECT
commands can give 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 a number of 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
Shared cluster 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.
Object names are always stored in lowercase.
Example of a query for tablespaces:
SELECT * FROM pg_tablespace WHERE spcname='pg_global';
All pg_tablespace columns start with "spc", and the name of the tablespace
under the WHERE clause must be in lowercase.
7
Special data types
OID — object identifier
column, ensures object distinction 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 the identifier.
The PRIMARY KEY constraint is not used for system catalog tables (it was
added in PostgreSQL 14), but unique indexes are created on the oid
columns.
Prior to PostgreSQL 12, the oid columns were hidden and the regular
“SELECT *” command did not show them.
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 oid’s and back.
9
Takeaways
The system catalog contains metadata about the cluster.
It is stored within the cluster itself
SQL access and additional psql commands
Some system catalog tables are stored in databases, some are
shared by 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 schema.
5. What queries does the following psql command perform?
\d+ pg_views
