Data Organization

Physical Structure

15

Copyright

© Postgres Professional, 2023

Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov

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

template1

postgres

Tablespaces are used to manage physical storage of data; they define the

layout of files in the file system.

For example, one tablespace can be created on slow disks to store archive

Besides, each database has a so-called “default tablespace,” in which all

database objects are created unless another location is specified. This

tablespace also stores system catalog objects. Initially, the pg_default

tablespace is used as the default one, but you can change this behavior.

The pg_global tablespace is special: it stores those system catalog

objects that are common to the whole cluster.

Basically, a tablespace is a reference to the directoty that stores data. The

pre-defined pg_global and pg_default tablespaces are always located

in the PGDATA/global/ and PGDATA/base/ directories, respectively. When

creating a custom tablespace, you can specify an arbitrary directory; the

server always uses relative links, so it creates an additional symlink in

PGDATA/pg_tblspc/ that points to this custom directory.

Within the PGDATA/base/ directory, all data is distributed between

subdirectories of different databases (PGDATA/global/ has no such

subdirectories because it contains the data related to the cluster as a

whole).

Custom tablespaces have one more nesting level to reflect the PostgreSQL

version. It is done to facilitate server upgrades.

Each object is usually represented by several forks. Each fork is a set of

segments (i.e., a file or several files). All segments are split into separate

pages. The page size is usually 8 kB; it can be configured for the whole

The main fork is the data itself: index rows or different versions of table

rows.

The vm fork is a visibility map (a bitmap). It marks the pages that contain

only the current tuples visible in all data snapshots. Visibility map is used to

optimize vacuum and to speed up index access.

The fsm fork is a free space map. It tracks free space within pages, which

can appear after vacuum. This map is used to quickly find a page that is

suitable for inserting a new tuple.

In PostgreSQL, any row version must fit a single page. For “oversized”

tuples, the TOAST mechanism is used, which stands for “The Oversized

Attributes Storage Technique”. It implies several strategies. Some oversized

approaches can also be combined.

If required, for each main table, PostgreSQL creates a separate TOAST

table (with a special index). Such tables and indexes are located in a

separate schema called pg_toast, so they are usually invisible.

Tuples in TOAST tables must also fit a single page, so to store oversized

values, PostgreSQL splits them into chunks. When required by an

application, these chunks are seamlessly put together to produce a full

value.

Besides, TOAST tables have their own versioning: if an update does not

affect the oversized value, the new tuple refers to the same value in the

TOAST table. This approach allows us to save some space.