When you create a table in a DBMS, you usually don't think much about column order. But if column order affects storage or performance, it is worth considering. PostgreSQL aligns data and stores it contiguously, so column order can affect storage and performance. Depending on the situation, the impact may be negligible and safe to ignore, but with many columns or a lot of data the impact can be large enough to make column order worth considering.
This document uses tests to explain why you should consider column order in PostgreSQL, how data is aligned depending on column order, and how to optimize tables with this knowledge.
📢 This document includes steps that inspect tuple information with the pageinspect extension. For installing the extension, see PostgreSQL Extension - Introduction.
1. Row Size Check
According to the PostgreSQL documentation, every row includes a fixed-size 24-byte HeapTupleHeaderData layout as described in Table Row Layout, so the smallest possible row is 24Byte.
📢 Table Row Layout (PostgreSQL Document)
In PostgreSQL, a page is the basic unit of data storage. Its size is fixed, and table data is spread across multiple pages. Every page and row has a header that holds metadata about the data. All table rows in PostgreSQL are structured the same way and include a 24Byte HeapTupleHeaderData layout made of a 23Byte fixed-size header and 1Byte of user data. The HeapTupleHeaderData layout contains the following.
Field | Type | Length | Description |
t_xmin | TransactionId | 4 Bytes | insert XID stamp |
t_xmax | TransactionId | 4 Bytes | delete XID stamp |
t_cid | CommandId | 4 Bytes | insert and/or delete CID stamp (overlays with t_xvac) |
t_xvac | TransactionId | 4 Bytes | XID for VACUUM operation moving a row version |
t_ctid | ItemPointerData | 6 Bytes | current TID of this or newer row version |
t_infomask2 | unit16 | 2 Bytes | number of attributes, plus various flag bits |
t_infomask | unit16 | 2 Bytes | various flag bits |
t_hoff | unit8 | 1 Bytes | offset to user data |
Each column added after that takes up as much space as the length of its data type.
As the examples above show, rows with more columns use more disk space.
Example 1has a 24Byte header and a 4Byte int column, so it is 28Byte (24Byte+4Byte).
Example 2has a 24Byte header, an 8Byte bigint column, and a 4Byte int column, so it is 36Byte (24Byte+8Byte+4Byte).
Changing the column positions in Example 2 gives the result in Example 3 below.
Example 2 needs 36Byte, but Example 3, which only swaps the column positions, needs 40Byte. It needs an extra 4Byte. You can see the same result with other data types.In
Example 4, the data types and number of columns are the same; only the column positions change. The row in Example 4-1, made of ( bigint , smallint , boolean ), needs 35Byte; the row in Example 4-2, made of ( boolean , smallint, bigint ), needs 40Byte; and the row in Example 4-3, made of ( boolean , bigint , smallint ), needs 42Byte. The largest, Example 4-3, uses about 20% more disk space than Example 4-1, the smallest, so we can expect column order by data type to affect disk usage in PostgreSQL.In PostgreSQL, columns are aligned according to the alignment requirements of their data types. The goal of this alignment is to optimize storage and access performance by making each column's data start at an appropriate position. To do this, PostgreSQL uses padding, which inserts empty space between column data so that data starts at the right position. In other words, the extra bytes in
Example 3 and Example 4 come from the data alignment PostgreSQL performs for efficient memory access and optimized access performance.2. Data Alignment
In PostgreSQL, you can check the alignment requirements of data types in the
pg_type catalog. As of PostgreSQL 17, the pg_type catalog defines 639 data types.Each data type has
typalign, typlen, and typstorage values.📢 Since this document covers data alignment by data type, it focuses on typalign and typlen. typstorage relates to how TOAST stores data, so we only define the concept.
typalign
typalign is the memory alignment required when storing a given data type. When several data types are stored contiguously, padding may be inserted in front of a value so that it starts on its required boundary. PostgreSQL defines 4 alignment levels, as shown in the table below, which helps optimize performance and speed up data access.
Value | Description | Data Type Example |
c | char alignment, no alignment needed (1byte). | char, boolean |
s | short alignment (2byte). | smallint |
i | int alignment (4byte) | int, real |
d | double alignment (8byte) | bigint, double precision, timestamp |
typlen
typlen defines the size of a data type. For fixed-length data types, it shows the size needed to store a value of that type. Variable-length data types usually show
-1, and null-terminated variable-length data types show -2.typstorage
typstorage is an attribute of variable-length data types whose typlen is -1, such as text and bytea. It defines whether the data type is ready to be TOASTed and which storage strategy applies by default. There are 4 strategies, as shown in the table below. TOAST is a way to manage large data efficiently, and it helps optimize performance without growing the table unnecessarily.
Value | Description |
p (plain) | Data is stored directly in the table. No compression and no TOAST. (Always used for data types that are not variable-length) |
e (external) | No compression; data is stored in TOAST when it exceeds a certain size. |
m (main) | Data is compressed, then stored directly in the table if small, or in TOAST if it exceeds a certain size. (Last resort) |
x (extended) | Data is compressed first, and stored in TOAST if it is still large after compression. (The usual choice for data types that can be TOASTed) |
📢 Compression is decided by toast_compression_threshold, and the threshold for storing data in TOAST is decided by toast_tuple_threshold, which is usually 2KB.
We have described typalign, typlen, and typstorage above. Checking these 3 attributes for commonly used data types gives the following.
- The bool (boolean) and char data types need a fixed length of 1Byte, and since typstorage=p, the data is stored in the table.
- int2 (smallint), int4 (int), and int8 (bigint) each need their fixed length (int2=2Byte, int4=4Byte, int8=8Byte), and since typstorage=p, the data is stored in the table. int2 needs 2Byte for alignment (typalign=s), int4 needs 4Byte (typalign=i), and int8 needs 8Byte (typalign=d).
- The text and varchar data types are variable-length and show typlen=-1. typstorage=x means Extended: when a tuple is 2KB or larger, the variable-length data is compressed and stored in the table or in TOAST depending on the compressed size.
3. Padding from Data Alignment
We checked the alignment requirements of data types in the pg_type catalog. Of those attributes, typalign and typlen deserve close attention.
Example 2 above is a row made of ( bigint , int ) and takes 12Byte excluding the 24Byte header.Checking the bigint and int data types in the pg_type catalog: the
bigint(int8) data type has typlen=8, so its size is 8Byte, and typalign=d, so it needs 8Byte alignment. The int(int4) data type has typlen=4, so its size is 4Byte, and typalign=i, so it needs 4Byte alignment.Based on the pg_type catalog, bigint comes first, and the int that follows needs 4Byte alignment. Because the preceding data type is 8Byte, no extra alignment is needed, and the int data type sits right after the bigint data type as shown below.
Example 3 is a row made of ( int , bigint ) and needs 16Byte excluding the 24Byte header. It has the same data types as Example 2, but because the column positions differ, it needs 4Byte more.int comes first, and the bigint that follows needs 8Byte alignment. But the preceding int is 4Byte, which cannot satisfy bigint's 8Byte alignment, so 4Byte of
padding is added after the int to make alignment possible. As shown below, the bigint data type is placed after the int data type plus 4Byte of padding.
📢 What is padding?
Padding means adding meaningless data (empty space) to increase a column's size for alignment.
Example 4 shows how much padding is added for the ( bigint , smallint, boolean ) data types depending on column order.Checking bigint, int, and boolean in the pg_type catalog: the
bigint(int8) data type has typlen=8, so its size is 8Byte, and typalign=d, so it needs 8Byte alignment. The smallint(int2) data type has typlen=2, so its size is 2Byte, and typalign=s, so it needs 2Byte alignment. The boolean(bool) data type has typlen=1, so its size is 1Byte, and typalign=c, so it needs no alignment.Example 4 looks like this as a diagram.Example 4-1is in ( bigint , smallint , boolean ) order and needs no padding for data alignment.
Example 4-2is in ( boolean , smallint , bigint ) order: 1Byte of padding is added after boolean for smallint, which needs 2Byte alignment, and 4Byte of padding is added after smallint for bigint, which needs 8Byte alignment.
Example 4-3is in ( boolean , bigint , smallint ) order: 7Byte of padding is added after boolean for bigint, which needs 8Byte alignment.
These examples confirm that tuple size changes with column order because of padding. A table with many columns ordered without regard to alignment can have larger tuples due to padding, which makes the table larger. With little data the difference is negligible, but with a lot of data it can become too large to ignore.
4. Checking Padding with the pageinspect Extension
The Example 4 diagrams showed how tuple size changes with column order by data type. With the pageinspect extension, you can see how padding is actually added.
📢 Using the pageinspect extension, we take a heap page with the get_raw_page function and pass it to the heap_page_items function to see the tuple's raw data (t_data) and tuple size (lp_len). t_data is shown in hexadecimal, so every 2 digits count as 1Byte. Padding appears as
00, so the test data below uses the maximum value each data type allows to tell it apart from real data.Example 4-1 needs 11Byte excluding the 24Byte header. In the t_data result, excluding \x,
ffffffffffffff7f is the bigint, ff7f is the smallint, and 01 is the boolean, and no padding is added.Example 4-2 needs 16Byte excluding the 24Byte header. In the t_data result, excluding \x,
01 is the boolean, ff7f is the smallint, and ffffffffffffff7f is the bigint. You can also see 1Byte of padding between boolean and smallint, and 4Byte of padding between smallint and bigint. 0100ff7f00000000ffffffffffffff7fExample 4-3 needs 18Byte excluding the 24Byte header. In the t_data result, excluding \x,
01 is the boolean, ffffffffffffff7f is the bigint, and ff7f is the smallint. You can also see 7Byte of padding between boolean and bigint. 0100000000000000ffffffffffffff7fff7fRecommended articles
