Having introduced the basic extensibility concepts, we can now
take a look at how the catalogs are actually laid out. You can
skip this section for now, but some later sections will be
incomprehensible without the information given here, so mark this
page for later reference. All system catalogs have names that
begin with pg_. The following tables
contain information that may be useful to the end user. (There
are many other system catalogs, but there should rarely be a
reason to query them directly.)

Table 8-1. PostgreSQL System Catalogs

Catalog Name

Description

pg_database

databases

pg_class

tables

pg_attribute

table columns

pg_index

indexes

pg_proc

procedures/functions

pg_type

data types (both base and complex)

pg_operator

operators

pg_aggregate

aggregate functions

pg_am

access methods

pg_amop

access method operators

pg_amproc

access method support functions

pg_opclass

access method operator classes

Figure 8-1. The major PostgreSQL system catalogs

The Developer's Guide gives a more
detailed explanation of these catalogs and their columns.
However, Figure
8-1 shows the major entities and their relationships in the
system catalogs. (Columns that do not refer to other entities are
not shown unless they are part of a primary key.) This diagram is
more or less incomprehensible until you actually start looking at
the contents of the catalogs and see how they relate to each
other. For now, the main things to take away from this diagram
are as follows:

In several of the sections that follow, we will present
various join queries on the system catalogs that display
information we need to extend the system. Looking at this
diagram should make some of these join queries (which are
often three- or four-way joins) more understandable, because
you will be able to see that the columns used in the queries
form foreign keys in other tables.

Many different features (tables, columns, functions,
types, access methods, etc.) are tightly integrated in this
schema. A simple create command may modify many of these
catalogs.

Types and procedures are central to the schema.

Note: We use the words procedure and function more or less
interchangeably.

Nearly every catalog contains some reference to rows in
one or both of these tables. For example, PostgreSQL frequently uses type
signatures (e.g., of functions and operators) to identify
unique rows of other catalogs.

There are many columns and relationships that have obvious
meanings, but there are many (particularly those that have to
do with access methods) that do not.