Following PostgreSQL Data Alignment - Introduction, this article walks through optimizing column positions for Data Alignment in practice and checks how storage usage changes as a result.
1. Optimizing Column Positions for Data Alignment
Optimizing column positions for data alignment aims to reduce unnecessary padding, minimizing wasted space and improving search performance. You can determine the optimal column positions using typalign and typlen in the pg_type catalog.
Column Ordering Rules
Columns are ordered by placing fixed-size data types first, followed by variable-size data types.
- Ordering rules for fixed-size data types
A fixed-size data type is one where
pg_type.typlen > 0.- Place columns in order of larger pg_type.typlen, with pg_type.typalign in the order d → i → s → c. A negative pg_type.typlen means variable size, so those types are not part of this step.
- For fixed-size character columns such as the char data type (pg_type.typalign = c), place columns in order of size, largest first.
- Ordering rules for variable-size data types
A variable-size data type is one where
pg_type.typlen = -1.- Place variable-size numeric columns such as numeric and decimal first.
- Then place variable-size character columns such as varchar.
- Then place long variable-size string columns such as text, bytea, jsonb and xml. (Their length varies the most, and they are the most likely to be stored in TOAST)
Summary of column ordering rules
Order | pg_type.typlen condition | pg_type.typalign condition | Detailed condition | Example data types |
1 | Positive | d | Place in order of larger pg.type.typlen | timetz, bigint, timestamp |
2 | Positive | i | Place in order of larger pg.type.typlen | macaddr8, regclass, int |
3 | Positive | s | Place in order of larger pg.type.typlen | tid, smallint |
4 | Positive | c | Place in order of larger pg.type.typlen | name, char(50), char(2), boolean |
5 | -1 | i | Variable-size numeric data types | numeric, decimal |
6 | -1 | i | Variable-size character data types | varchar |
7 | -1 | i | Long variable-size string data types | text, bytea, jsonb, xml |
Variable-Length Data Types and TOAST Tables
Let's look at why variable-size data types are placed after fixed-size data types.
Variable-size string data types have a data header separate from the tuple header. The data header adds 1Byte when the stored data is up to 126 long, and 4Byte from 127 onward.
Also, when a tuple is large (usually 2KB), column data of that data type is compressed depending on
typlen=-1 and typstorage=x, and if the tuple is still large after compression, it is stored in a TOAST table. When data is stored in a TOAST table, the actual table stores information pointing to TOAST.As a result, variable-size data types are likely to be stored in TOAST once they exceed a certain size, so placing them after fixed-size data types lets the layout absorb the variation in length.
2. A Real Example
So far we have seen that tuple size changes depending on column order by data type. In this section, we compare table sizes with a large amount of data, with and without considering column order.
First, create a table structured as follows.
From the CREATE TABLE statement, we can estimate that each tuple needs 36Byte, excluding the 24Byte tuple header and including padding for data alignment. Let's load data and check whether it actually needs 36Byte.
Insert 10 million rows.
To check the size each tuple needs, we use the pageinspect extension to see how the data is actually stored.
The default_col table is stored at 36Byte per tuple, excluding the 24Byte tuple header. The t_data result breaks down as shown below. This matches the tuple size we estimated from the CREATE TABLE statement.
To shrink the table by minimizing unnecessary padding from data alignment, check the alignment information of the default_col table.
Recreate the default_col table as the ordering_col table following the column ordering rules in
1. Optimizing Column Positions for Data Alignment.From the CREATE TABLE statement, we can estimate that each tuple needs 22Byte, excluding the 24Byte tuple header. The ordering_col table, recreated following the column ordering rules, needs no padding. Let's load data and check whether it actually needs 22Byte.
Insert the same 10 million rows that were loaded into the default_col table.
To check the size each tuple needs, we use the pageinspect extension to see how the data is actually stored.
The ordering_col table is stored at 22Byte per tuple, excluding the 24Byte tuple header. The t_data result breaks down as shown below. This matches the tuple size we estimated from the CREATE TABLE statement.
To compare table sizes as tuple size changes, check the sizes of the table without column ordering (default_col) and the table with column ordering (ordering_col).
Comparing table sizes after loading 10 million rows, we can see that the ordering_col table, with its columns reordered, uses 153MB less (about 25% smaller) than the default_col table.
3. Benefits of Changing Column Order
Lower storage usage: Reordering columns by data alignment rules minimizes the unnecessary padding used for alignment, which reduces storage usage. In the example above, a 615MB table dropped to 498MB, about 25% less, after its columns were reordered by data alignment rules.
Better use of TOAST: Placing long variable-size data such as text, jsonb and xml last lets it be stored in TOAST quickly.
Lower I/O cost: For the same reason storage usage drops, each page holds more row data, which lowers I/O cost and improves query performance. In other words, more row data can be cached in fewer pages. In the EXPLAIN output below, the default_col table incurs I/O on 83,334 pages, while the ordering_col table incurs I/O on 63,695 pages.
Related articles
