In PostgreSQL, why does a table of narrow rows take far more disk than its column widths add up to, and how can column order reduce it?
answer
- a fixed header on every row
- a line pointer per row too
- MAXALIGN is 8 bytes
- pg_type.typalign per column
- widest fixed-width columns first
basics
~20 sEvery PostgreSQL heap row carries a 23-byte header padded to 24 bytes plus a 4-byte line pointer, and each column is padded to its type's alignment. Ordering fixed-width columns from 8-byte down to 1-byte types removes padding and can shrink narrow rows noticeably.
solid answer
~40 sA heap tuple starts with a 23-byte header (`t_xmin`, `t_xmax`, `t_cid`, `t_ctid`, `t_infomask2`, `t_infomask`, `t_hoff`), an optional null bitmap, and padding so user data starts at a MAXALIGN boundary, 8 bytes on typical 64-bit servers; the page also spends a 4-byte line pointer per row, so each row costs about 28 bytes before any column. Inside the row each value starts at its type's alignment (`pg_type.typalign`): 8 bytes for `bigint`, `timestamptz` and `double precision`, 4 for `integer` and `date`, 2 for `smallint`, 1 for `boolean`. Short variable-length values carry a 1-byte header and need no alignment. So `(boolean, bigint, boolean, bigint)` wastes 14 bytes in padding. Putting 8-byte columns first, then 4-, 2- and 1-byte ones, then variable-length columns, removes most of it; on tall, narrow tables that is often 10 to 20%.
code
sql · 19 lines-- 76 bytes per row on a 64-bit server: 21 bytes of padding
CREATE TABLE article_view_events_padded (
is_bot boolean NOT NULL,
viewed_at timestamptz NOT NULL,
is_mobile boolean NOT NULL,
article_id bigint NOT NULL,
is_logged_in boolean NOT NULL,
session_id bigint NOT NULL
);
-- 60 bytes per row: 8-byte columns first, booleans last
CREATE TABLE article_view_events (
viewed_at timestamptz NOT NULL,
article_id bigint NOT NULL,
session_id bigint NOT NULL,
is_bot boolean NOT NULL,
is_mobile boolean NOT NULL,
is_logged_in boolean NOT NULL
);go deeper
Remember that each row carries a fixed header of about two dozen bytes plus a line pointer, so a million tiny rows are never just their column widths.
Explain MAXALIGN and typalign, compute the padding in a mixed boolean and bigint row, and say why NULLs cost only a bit.
Apply it in schema reviews of tall tables: order columns from 8-byte to 1-byte, estimate rows per page, and know reordering later means a full copy.
Decide when the saving justifies the rule: billions of narrow rows in an event store yes, a wide content table no, and how it compares with partitioning or compression.
## Anatomy of one stored row | Part | Size | Notes | |---|---|---| | Line pointer (`ItemIdData`) | 4 bytes | In the page's item array: offset, length and flags | | Tuple header (`HeapTupleHeaderData`) | 23 bytes | `t_xmin`, `t_xmax`, `t_cid` or `t_xvac`, `t_ctid`, `t_infomask2`, `t_infomask`, `t_hoff` | | Null bitmap | 0, or one bit per column | Present only when the row contains at least one NULL | | Padding up to `t_hoff` | to MAXALIGN | User data starts at a MAXALIGN multiple, 8 bytes on typical 64-bit servers | | Column data | values plus alignment gaps | Each value starts at its type's alignment | | Tail padding | to MAXALIGN | The tuple's length is rounded up so the next one starts aligned | Before a single column is stored, a row therefore costs **28 bytes** on a typical 64-bit server: 24 for the aligned header and 4 for its line pointer. Each 8 kB page also spends 24 bytes on its own header (`PageHeaderData`), which is why at most 291 tuples fit on a default page. What `t_xmin` and `t_xmax` mean for visibility is an MVCC subject; here they are simply bytes. The fixed cost dominates small rows. A table with a single `boolean` column still spends 24 bytes of header, 1 byte of data padded to 32, and a 4-byte line pointer: 36 bytes per row, about 226 rows per page, so a billion such rows need roughly 36 GB before any index is built. ## Alignment: where padding comes from Every type has an alignment, `pg_type.typalign`: - `c`, 1 byte: `boolean` - `s`, 2 bytes: `smallint` - `i`, 4 bytes: `integer`, `real`, `date`, and variable-length values with a 4-byte header - `d`, 8 bytes: `bigint`, `double precision`, `timestamp`, `timestamptz` A value is placed at the next offset that is a multiple of its alignment, and the gap before it is wasted. **Variable-length values** shorter than 127 bytes are stored with a 1-byte header and are not aligned at all, which is why short `text` columns rarely cause padding; longer ones use a 4-byte header aligned like `integer`. ## A worked example An append-only `article_view_events` table declared as `(is_bot boolean, viewed_at timestamptz, is_mobile boolean, article_id bigint, is_logged_in boolean, session_id bigint)` lays out like this: 1. `is_bot` at offset 0, then 7 bytes of padding. 2. `viewed_at` at 8 to 16. 3. `is_mobile` at 16, then 7 bytes of padding. 4. `article_id` at 24 to 32. 5. `is_logged_in` at 32, then 7 bytes of padding. 6. `session_id` at 40 to 48. That is 48 bytes of data, 21 of them padding: a 72-byte tuple plus a 4-byte line pointer, 76 bytes per row, about 107 rows per page. Reordered as `(viewed_at, article_id, session_id, is_bot, is_mobile, is_logged_in)`, the data is 27 bytes, the tuple 51 bytes rounded up to 56, and the row 60 bytes with its line pointer: about 136 rows per page, roughly 21% less space for the same data. ## Null bitmap and other details - A NULL occupies **no data bytes**, only its bit in the null bitmap. - The bitmap exists only in rows that contain a NULL. With up to 8 columns it fits in the byte left after the 23-byte header, so `t_hoff` stays 24; with 9 to 72 columns, a row with any NULL gets a 32-byte header. - Variable-length values that grow past about 2 kB of row width are handled by TOAST, so their size on the page is bounded separately. - To check a real table, the superuser-only `pageinspect` function `heap_page_items(get_raw_page('article_view_events', 0))` shows each tuple's `lp_len` (its length before tail padding) and `t_hoff`, which makes padding and null bitmaps visible without arithmetic. ## When it is worth doing - It pays on **tall, narrow tables**: event logs, metrics, link tables with hundreds of millions of rows, where 10 to 20% means gigabytes and more rows per cached page. - It barely matters on wide rows dominated by text, where the per-row overhead is small next to the data. - **Decide at design time.** PostgreSQL has no `ALTER TABLE` form that reorders columns; changing the order later means building a new table and copying the data. - New columns are always appended at the end, so a schema that grows for years drifts from the ideal order.
- Why can a row of a table with 9 or more columns cost 8 extra bytes as soon as one of its values is NULL?A row with any NULL carries a null bitmap of one bit per column. Up to 8 columns it fits in the spare byte after the 23-byte header, but with 9 or more it needs at least 2 bytes, so the header plus bitmap passes 24 and is padded to the next MAXALIGN boundary, 32 bytes. Rows without NULLs carry no bitmap.
- How do you change the column order of an existing PostgreSQL table?There is no `ALTER TABLE` form for it. You create a new table with the desired order, copy the data, recreate indexes, constraints, defaults and grants, and swap the names, which on a busy table needs a planned migration. That is why the order is worth getting right when the table is first created.
- PostgreSQL stores each row in exactly the bytes its columns declare.
- Column order never affects how much space a PostgreSQL table uses.
- A NULL bigint still occupies 8 bytes in the row.
- You can reorder a table's columns in place with ALTER TABLE.
- Every text value carries a 4-byte header and 4-byte alignment.