Postgres Data Types, Sizes and Alignment

How much storage does a single row in a Postgres table take?

Page Format

A page in a default Postgres installation is 8 KiB in size. Each page begins with 24 bytes of PageHeaderData (metainformation). This metainformation is followed by ItemIds, each containing the offset (from the start of this page) and the length of the row it points to. An ItemId is 4 bytes. ItemIds are put after the PageHeaderData, the items (rows) themselves are stored from the end of the page backwards.

Page Layout in Postgres

Row Sizes

Each row in a table has a fixed-size, 23-byte header, followed by an optional “null bitmap”. When NULLable types are used in a table definition, the bitmap contains a bit per field: 0 for NON NULLable, 1 for NULLable. When the definition has 9+ fields (so more bits than can be contained in a single byte), it grows to 2 bytes. The user data, however, only starts at a fixed MAXALIGN boundary (8 bytes on a 64-bit machine, 4 on 32-bit).

=# 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

(The row() constructor creates a row on an anonymous record type.)

If we have 8 NULLable values, the fields fit into a single-byte null bitmap. If we have more than 8, more bytes are required. In this case, 2 bytes. On a 64-bit machine, user data starts at an 8-byte boundary. So the user data starts at 23 + 2 + 7 = 32 bytes.

Data Sizes

There are two types of type lengths: fixed-length and variable-length types. A smallint (= int2), for example, is a fixed-length type taking 2 bytes of storage. A text type is a variable-length type. Since rows cannot span pages, when the data of a variable-length type exceeds 2 KiB, it can be stored out-of-line (in another table). In that case, instead of the value itself, a pointer to that data is stored. (See the example later on.)

Data Alignment

Each data type has alignment requirements. You can view these requirements:

=# select typname, typlen, typalign from pg_type where typname = 'int2';
 typname | typlen | typalign 
---------+--------+----------
 int2    |      2 | s

For a smallint/int2, we see that typalign is s. This indicates alignment on a short boundary, which equals 2 bytes on most machines. Consequently, if an int2 field is defined after a field that takes an odd amount of storage, a padding byte is inserted:

=# select pg_column_size(row(true));
 pg_column_size
----------------
             25

=# select pg_column_size(row(true, 2::int2));
 pg_column_size
----------------
             28

In the first case: 23 bytes of fixed-size header + 1 byte padding to align to MAXALIGN + 1 byte for the boolean.

If we throw an int2 in the mix after this boolean, we see that the total size increases by 3: 1 padding byte and 2 bytes to store the int2 value.

TOAST

Types with flexible-storage requirements use the internal varlena type. Depending on the length of the data they store, they have a 1- or 4-byte header. Their actual values may be stored in-line (meaning in the row itself) or out-of-line (meaning in another table). Wherever the actual data may live, it can also be compressed. The technique for dealing with arbitrary-length fields is called TOAST.

Values with a single-byte header are not aligned on any particular boundary:

=# select pg_column_size(row(true, 'foo'::text));
 pg_column_size
----------------
             29

After the 24-byte row header, we see 1 byte for the boolean, and then 1

3 = 4 bytes for the text type.

In the 1-byte header, when the most-significant is set, the remaining 7 bits indicate the length of the data in bytes (including this header). It therefore can indicate up to 126 bytes of data (2^7 - 1 header byte).

=> select pg_column_size(row(true, repeat('b', 127)));
 pg_column_size
----------------
            159

Here we see that once the text type exceeds 126 bytes 2 things happen:

the type now requires a header of 4 bytes; and
it now needs to align on a integer (4-byte) boundary.

When we calculate ourselves: 24 bytes fixed size header, 1 byte to store the boolean, 3 padding bytes to make sure the 4-byte text header starts on a 4-byte boundary, 4 bytes for the text header and finally 127 bytes to store the actual string.

TOAST Storage Options

TOAST is only used when the data-to-be-stored exceeds TOAST_TYPLE_THRESHOLD (2 KiB on default installations).

You can set storage for a TOASTable column with ALTER TABLE ... SET STORAGE.

There are 4 strategies for TOASTable columns:

PLAIN (no compression or out-of-line storage is done)
EXTENDED (attempt compression, then out-of-line storage)
EXTERNAL (attempt only out-of-line storage)
MAIN (attempt only compression)

The default storage strategies for types can be seen in the pg_type system catalog:

=# select typname, typlen, typalign, typstorage from pg_type where typname = 'int8';
 typname | typlen | typalign | typstorage
---------+--------+----------+------------
 int8    |      8 | d        | p

=# select typname, typlen, typalign, typstorage from pg_type where typname = 'text';
 typname | typlen | typalign | typstorage
---------+--------+----------+------------
 text    |     -1 | i        | x

An int8 takes a fixed 8 bytes and is not TOASTable as indicated by both the typlen and the typstorage (p stands for PLAIN).

A text field is a variable-length type. This is indicated by the negative typlen of -1. The storage strategy is x, which stands for EXTENDED. If you have a value that is > 2 KiB, it will either be compressed or moved to a another table for storage:

=# create table t (t text);
CREATE TABLE
=# insert into t values (repeat('a', 1024));
INSERT 0 1
=# insert into t values (repeat('a', 2048));
INSERT 0 1
=# select pg_column_size(t) from t t;
 pg_column_size
----------------
           1028
             35

A text value of length 1024 is stored in-line after a 4-byte header. A text value whose storage (including header) exceeds 2 KiB is stored in a TOAST table. In its place, a pointer to the data is inserted in our table. The documentation says the pointer data is 18 bytes long (storing the toast table OID, chuck OID, logical size, physical size and the compression method used, if any), but here we see that only 11 (35 - 24) bytes were used. That is something to figure out for me :)

Find Total Size On Disk of a Table

To find the total size of a table, including any indexes and TOAST tables (where oversized data of variable-length types are stored):

=# select pg_total_relation_size('t');
 pg_total_relation_size
------------------------
                  16384

The same can be achieved when using the psql CLI client:

=# \dt+ t
                                   List of relations
 Schema | Name | Type  | Owner | Persistence | Access method | Size  | Description
--------+------+-------+-------+-------------+---------------+-------+-------------
 public | t    | table | owner | permanent   | heap          | 16 kB |

There are more handy functions to calculate different types of objects available.