PostgreSQL/Page Layout

From Wikibooks, open books for an open world
Jump to navigation Jump to search

PostgreSQL organizes data and associated indexes in separate files. Such files consist of 8k blocks. Currently used blocks are mirrored 1:1 in the Shared Memory of the Instance. All pages of data files are logically equivalent, whereas index files use different page types depending on the needs of the index type (root page, internal tree page, leaf page, ...). Nevertheless, the physical layout is identical for all of them.

Each page consists of 5 major components

  1. Page Header: General information about the page.
  2. ItemIds: Array of pointers to Items. It grows or shrinks over time.
  3. Free Space: The unused space of the page.
  4. Items: A set of actual data respectively rows or a set of index entries. It grows or shrinks over time.
  5. Special Space: Data files don't use if. Index files structure it depending on the needs of the index type.

Every additional row creates a new ItemId at the end of the 2. component plus a new Item at the beginning of the 4. component. As a result, the free space shrinks from both its left and right side.

PostgreSQL pageLayout.svg

The Page Header consists of 24 bytes and contains information like a page checksum, offset to start and end of free space, information for WAL handling, the layout version number, and some more flags.

Every ItemId consists of 4 bytes and contains information about the offset and the length of the Item.

In the case of data files, every Item (= row) consists of:

  1. Item Header: 23 bytes containing various transaction IDs, current or newer tuple ID, offset to data, and various flags.
  2. Null Bit Map: Marker for such columns which are currently NULL. The map is optional: if a table contains only no-nullable columns, it is superfluous.
  3. Data: The value of every attribute of the row - if not NULL. The sequence and types of attributes are stored in the system schema. In the case of variable-length data types, the currently used length is stored at the beginning of the attribute.

External links[edit | edit source]

PostgreSQL Documentation concerning page layout