Column Storage Intervals in Postgres Explained

Postgres uses native CPU alignment to store values in a row. This allows blocks to be copied unchanged from disk into shared buffers and accessed as local variables, as outlined in this presentation.

This can be illustrated by pg_column_size(). First, an empty row size:

SELECT pg_column_size(row());
 pg_column_size
----------------
             24

Predictably, 2-byte, 4-byte, and 8-byte integers increase the length as expected:

SELECT pg_column_size(row(1::int2));
 pg_column_size
----------------
             26

SELECT pg_column_size(row(1::int4));
 pg_column_size
----------------
             28

SELECT pg_column_size(row(1::int8));
 pg_column_size
----------------
             32

You can also see alignment effects — the first query has no alignment padding, while the second query has two-byte padding because the row length is unchanged:

SELECT pg_column_size(row(1::int2, 1::int2, 1::int4));
 pg_column_size
----------------
             32

SELECT pg_column_size(row(1::int2, 1::int4));
 pg_column_size
----------------
             32

You can see the alignment requirements for each data type by querying the system catalogs:

SELECT typalign, typname FROM pg_type ORDER BY 1, 2;
 typalign | typname
----------+---------
 c        | bool
 c        | char
 c        | cstring
 c        | name
 c        | unknown
 c        | uuid
 d        | _box
 d        | _circle
 d        | _float8
…

The Postgres source code file src/include/catalog/pg_type.h documents the meaning of the letters:

'c' = CHAR alignment, ie no alignment needed.
's' = SHORT alignment (2 bytes on most machines).
'i' = INT alignment (4 bytes on most machines).
'd' = DOUBLE alignment (8 bytes on many machines, but by no means all).

It is possible to define table columns in an order that minimizes padding. Someday Postgres might do this automatically.

The 24-byte row header includes an 8-bit mask to record null values.

You can see below that the 8-bit mask is sufficient for eight nulls, but the ninth null requires the null bit mask to be expanded, with additional alignment:

SELECT pg_column_size(row(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL));
 pg_column_size
----------------
             24

SELECT pg_column_size(row(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL));
 pg_column_size
----------------
             32

These things all happen automatically, but sometimes it is interesting to see it working.

This post originally appeared on Bruce's personal blog.