Data Organization

Physical Structure

12

Copyright

© Postgres Professional, 2017–2021

Authors: Egor Rogov, Pavel Luzanov

Contact Us

Please send your feedback to: edu@postgrespro.ru

Disclaimer

In no event shall Postgres Professional company be liable for any damages

or loss, including loss of profits, that arise from direct or indirect, special or

incidental 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

3

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

data, while another tablespace located on fast disks will be used for the data

that is being actively updated.

During cluster initialization, two tablespaces are created: pg_default and

pg_global.

The pg_global tablespace is special: it stores those system catalog

objects that are common to the whole cluster.

4

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

pre-defined pg_global and pg_default tablespaces are always located

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.

Actual database objects are stored in files within these directories: each

object has its own files.

Each file, which is called a segment, takes no more than 1 GB of disk space

(this value can be altered at build time). That’s why each object can have

6

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

only the current tuples visible in all data snapshots. In other words, these

are the pages that have not been modified in a while and have no outdated

tuples left.

Visibility map is used to optimize vacuum (the pages marked in the visibility

map do not have to be vacuumed) and to speed up index access. The point

is that versioning information is stored only for tables, but not for indexes

(that’s why indexes have no visibility maps). Once a reference to a tuple is

found in index, the corresponding table page has to be read to check the

visibility status of this tuple. But this step can be skipped if the index itself

already contains all the required columns, and the page is present in the

8

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

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

TOAST tables are accessed only if the oversized value has to be returned.