Everything is a page

Here are two tables with the same six columns, holding the same million rows:

 relname | size  | pages
---------+-------+-------
 padded  | 73 MB |  9346
 packed  | 57 MB |  7353

The only difference is the order the columns were declared in. One is 27% bigger than the other: 27% more pages to scan, cache, back up, and replicate, forever.

Postgres doesn't really store tables. It stores pages, and a table is whatever fits in them. Picture the page and you can say how many bytes any row takes, and how to make it take fewer. We'll go from the outside in, mostly through pageinspect, an extension that reads raw pages from SQL.

A table is a file of pages

Every table and index is one or more ordinary files in the server's data directory. You can ask which:

select pg_relation_filepath('events');

 pg_relation_filepath
----------------------
 base/16764/16644

16764 is the database's ID and 16644 the table's relfilenode, the name of its file (yours will differ). From a shell in the container (docker compose exec postgres bash, then cd /var/lib/postgresql/18/docker):

-rw------- 1 postgres postgres 288137216 Oct  6 04:07 base/16764/16644
-rw------- 1 postgres postgres     90112 Oct  6 04:07 base/16764/16644_fsm
-rw------- 1 postgres postgres     16384 Oct  6 04:07 base/16764/16644_vm

As Know the nouns first showed, 288,137,216 bytes divided by 8,192 is exactly 35,173. The file is nothing but pages, one after another, numbered from 0. (Past 1 GB, a table continues in 16644.1, 16644.2, and so on.)

select lab.prove(
  'the events file is nothing but 8 KB pages: 288,137,216 bytes is exactly 35,173 pages',
  pg_relation_size('events') = 288137216
  and pg_relation_size('events') % 8192 = 0
  and pg_relation_size('events') / 8192 = 35173);

Anything that rewrites a whole table (truncate, vacuum full, some alter tables) writes a fresh file under a new name, so never assume the name is stable.

A table is more than its main file

events had three files, not one. Each is a fork with its own job: main holds the rows, _fsm is the free space map, and _vm is the visibility map; both maps get sections below. Unlogged tables add an empty _init fork that's copied over them after a crash.

That's why the honest answer to "how big is this table?" is rarely the main file alone:

select pg_size_pretty(pg_relation_size('events'))       as main,
       pg_size_pretty(pg_total_relation_size('events')) as total;

  main  | total
--------+--------
 275 MB | 318 MB

pg_relation_size is the main fork only. pg_table_size adds the other forks and the table's TOAST storage (below), and pg_total_relation_size adds every index. Use the total.

Every page has the same layout

Every one of those 35,173 pages is laid out like this. It's the most important picture in this book:

It works like a notebook page with a table of contents written from the top and the entries written from the bottom up.

A 24-byte page header records the page's last change, a checksum, and where the free space starts and ends. Line pointers, 4 bytes each, grow from the front; each points at a tuple. Tuples grow from the back; a tuple is one version of a row, as stored on the page. The page is full when the two meet. (Indexes keep bookkeeping in the special space at the end; table pages leave it empty.)

select lsn, checksum, flags, lower, upper, special, pagesize
from page_header(get_raw_page('events', 0));

    lsn     | checksum | flags | lower | upper | special | pagesize
------------+----------+-------+-------+-------+---------+----------
 0/AF5BFA50 |    27634 |     4 |   248 |   384 |    8192 |     8192

select lab.prove(
  'lower = 24-byte header + 56 line pointers x 4 bytes = 248; upper = 384; 136 bytes free',
  lower = 24 + 4 * 56 and lower = 248 and upper = 384 and upper - lower = 136)
from page_header(get_raw_page('events', 0));
 
select lab.prove(
  '136 free bytes is too few for another events row (136 to 144 bytes, plus a line pointer)',
  (select upper - lower from page_header(get_raw_page('events', 0)))
    < (select min(lp_len) + 4 from heap_page_items(get_raw_page('events', 0))));

A line pointer lets a tuple move without changing its address

The line pointer lets Postgres move a tuple within its page, to compact free space, without changing its address. That address is the row's ctid, a page number and a line pointer number, and every table has it as a hidden column:

select ctid, id, kind from events order by id limit 3;

 ctid  | id |  kind
-------+----+---------
 (0,1) |  1 | build
 (0,2) |  2 | deploy
 (0,3) |  3 | comment

(0,1) means page 0, line pointer 1. Indexes store ctids: when an index lookup finds id = 2, what it finds is "page 0, slot 2". Following a ctid to its table page is the fourth read in the four-page lookup from A query is pages and round trips.

8 KB is a compile-time choice, and you should keep it

Eight kilobytes isn't a law of nature. It's a constant, BLCKSZ, fixed when Postgres is compiled (./configure --with-blocksize, a power of two from 1 to 32 KB), and every file in the data directory is written in it. A server built for 16 KB pages can't open a data directory made with 8 KB ones, and pg_upgrade won't cross between them. A running server tells you what it was built with, and no setting changes it:

show block_size;

 block_size
------------
 8192

Bigger pages sound like fewer pages to count, and for some work they are. A sequential scan makes fewer, larger reads; an index fits more keys per page, so the tree is a little shallower; and wide rows stay inline longer, because the threshold for compressing and moving big values (TOAST, below) scales with the page.

But Postgres reads and caches whole pages, and sometimes logs them whole, so everything else gets worse. At 32 KB, a lookup that wants one row pulls four times the bytes into memory. The same shared_buffers holds a quarter as many pages, so your hot rows share the cache with four times as many cold neighbors. And the first change to a page after a checkpoint logs the whole page, so each of those full-page images can be up to four times the size.

So we keep 8 KB, and so should you. Managed services don't let you choose it anyway. Every lever in this book changes how many rows fit on a page without a custom build: column order, narrower types, fillfactor, TOAST, and the right index. Larger pages pay off mainly for scan-heavy analytics on a server you compile yourself, and that's the workload where a column store wins anyway (Where Postgres loses).

select lab.prove('the page size is fixed when Postgres is built: block_size is 8192, and no setting can change it',
  current_setting('block_size') = '8192'
  and (select context from pg_settings where name = 'block_size') = 'internal');

select lab.prove('WAL blocks are a separate compile-time constant, also 8 KB here',
  current_setting('wal_block_size') = '8192'
  and (select context from pg_settings where name = 'wal_block_size') = 'internal');

A row costs more than its columns

Open a page and count what each row actually stores. heap_page_items decodes every tuple on a page:

select lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid, t_hoff
from heap_page_items(get_raw_page('events', 0))
limit 3;

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid | t_hoff
----+--------+--------+--------+--------+--------+--------
  1 |   8048 |    144 |    766 |      0 | (0,1)  |     24
  2 |   7904 |    144 |    766 |      0 | (0,2)  |     24
  3 |   7760 |    144 |    766 |      0 | (0,3)  |     24

Tuple 1 is 144 bytes at offset 8048, ending exactly at the end of the page; tuple 2 sits right below it. Each starts with a 23-byte tuple header.

The header's two most important fields are t_xmin, the transaction that inserted this version, and t_xmax, the one that deleted or locked it (0 if none). They're the heart of Postgres's concurrency control and get a chapter of their own (Updates are inserts). Here, every row was inserted by transaction 766, the seed, and none has been deleted. t_ctid is the tuple's own address, or its newer version's. t_hoff says the column values start at byte 24, after the header and a byte of alignment, in declaration order, end to end.

Twenty-four bytes of header and a 4-byte line pointer, on every row. A row of two integers is mostly overhead.

select raw_flags
from heap_page_items(get_raw_page('events', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

                        raw_flags
----------------------------------------------------------
 {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}

select substr(t_data, 1, 16)
from heap_page_items(get_raw_page('events', 0))
where lp = 1;

               substr
------------------------------------
 \x0100000000000000a903000000000000

select lab.prove(
  'pageinspect decodes t_infomask 2306 as HASVARWIDTH, XMIN_COMMITTED, XMAX_INVALID',
  t_infomask = 2306
  and raw_flags = '{HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}')
from heap_page_items(get_raw_page('events', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;
 
select lab.prove(
  'the next 8 bytes are account_id: 0x03a9 = 937',
  substr(t_data, 9, 8) = '\xa903000000000000'::bytea
  and (select account_id from events where id = 1) = 937
  and x'03a9'::int = 937)
from heap_page_items(get_raw_page('events', 0)) where lp = 1;

NULLs cost one bit

A NULL takes no space in the data area. A row with any NULL carries a null bitmap in its header instead: one bit per column, 0 for NULL. In projects, an unarchived project has a NULL archived_at:

 lp |  t_bits  | lp_len
----+----------+--------
  1 | 11110100 |     72
  2 |          |     80
  3 | 11110100 |     72

Rows 1 and 3 aren't archived: their fifth bit is 0, and they're 8 bytes shorter than row 2, which stores its timestamp. One bit instead of eight bytes. (Up to 8 columns, the bitmap fits in a spare byte of the header. Past 8, a row with any NULL needs a longer bitmap, which can push the start of its data from byte 24 to byte 32.)

select lp, t_bits, lp_len
from heap_page_items(get_raw_page('projects', 0))
limit 3;

select lab.prove(
  'projects rows 1 and 3 (not archived) carry a null bitmap 11110100; row 2 (archived) has none',
  array_agg(coalesce(t_bits, '') order by lp) = '{11110100,"",11110100}')
from heap_page_items(get_raw_page('projects', 0)) where lp <= 3;
 
select lab.prove(
  'row 2 is archived and 8 bytes longer, because it stores the timestamp',
  (select lp_len from heap_page_items(get_raw_page('projects', 0)) where lp = 2)
    = (select lp_len from heap_page_items(get_raw_page('projects', 0)) where lp = 1) + 8
  and (select archived_at is not null from projects where ctid = '(0,2)')
  and (select archived_at is null from projects where ctid = '(0,1)'));

Column order is free space

Now the mystery from the start of the chapter. CPUs read a value fastest at an address that's a multiple of its size, so Postgres lines values up that way: a bigint or timestamptz starts at a multiple of 8 bytes, an integer or date at a multiple of 4, a smallint at a multiple of 2, and a boolean anywhere. When an 8-byte column follows one that ended at an odd offset, Postgres pads the gap. Picture a parking lot where trucks may only park at every eighth line: let a motorbike take the first space, and the next seven go empty.

Here are the two tables from the start, column by column. pg_column_size says a padded row is 72 bytes and a packed one 52:

padded (72 bytes)              packed (52 bytes, 56 on the page)
offset  contents               offset  contents
   0    header (24)               0    header (24)
  24    is_active (1)            24    id (8)
  25    padding (7)              32    created_at (8)
  32    id (8)                   40    score (8)
  40    is_admin (1)             48    flags (2)
  41    padding (7)              50    is_active (1)
  48    created_at (8)           51    is_admin (1)
  56    flags (2)                52    end
  58    padding (6)
  64    score (8)
  72    end

It compounds: each tuple also starts at a multiple of 8 on the page (so packed's 52 bytes occupy 56), and each needs a line pointer. That's 107 rows per page against 136, and 9,346 pages against 7,353.

The rule that falls out:

1. Fixed-width 8-byte columns first: bigint, timestamptz, double precision.
2. Then 4-byte ones: integer, real, date.
3. Then 2-byte, then 1-byte: smallint, boolean.
4. Variable-length columns (text, jsonb, numeric) last. Short values need no alignment, so they pack tightly at the end.

create table padded (
  is_active  boolean,
  id         bigint,
  is_admin   boolean,
  created_at timestamptz,
  flags      smallint,
  score      bigint
);
 
create table packed (
  id         bigint,
  created_at timestamptz,
  score      bigint,
  flags      smallint,
  is_active  boolean,
  is_admin   boolean
);

insert into padded
  select true, g, false, now(), 1, g from generate_series(1, 1000000) g;
insert into packed
  select g, now(), g, 1, true, false from generate_series(1, 1000000) g;

padded: 8168 usable bytes / (72 + 4) per row  =  107 rows per page
packed: 8168 usable bytes / (56 + 4) per row  =  136 rows per page

select lab.prove(
  'a padded row is 72 bytes; the same columns packed take 52',
  (select pg_column_size(p.*) from padded p limit 1) = 72
  and (select pg_column_size(p.*) from packed p limit 1) = 52);
 
select lab.prove(
  'page 0 of padded holds 107 tuples, page 0 of packed holds 136',
  (select count(*) from heap_page_items(get_raw_page('padded', 0))) = 107
  and (select count(*) from heap_page_items(get_raw_page('packed', 0))) = 136
  and 8168 / (72 + 4) = 107 and 8168 / (56 + 4) = 136);
 
select lab.prove(
  '9,346 pages versus 7,353: padded is about 27% bigger',
  pg_relation_size('padded') / 8192 = 9346
  and pg_relation_size('packed') / 8192 = 7353
  and pg_relation_size('padded')::numeric / pg_relation_size('packed') between 1.26 and 1.28);

Large values are compressed, then moved out of the row

A row must fit in an 8 KB page, yet a text value can be up to 1 GB. TOAST (The Oversized-Attribute Storage Technique) squares the two. When a row about to be stored is larger than about 2 KB (2,032 bytes with 8 KB pages, so at least four rows fit on a page), Postgres works through its variable-length columns, largest first:

1. Compress the value in place.
2. If the row is still too big, move the value into the table's own TOAST table, cut into chunks of just under 2 KB, leaving an 18-byte pointer behind.

In a test table, 100,000 repeated characters compressed to 1,156 bytes and never left the row. 32,000 characters of random hex didn't compress, so they moved out in 17 chunks, leaving a 50-byte row behind. Reading that value back means an index lookup in the TOAST table and 17 more tuple fetches, invisible in your SQL.

What that means for your queries:

• select * isn't free on wide tables. A list view that doesn't show a document's body shouldn't fetch it, and most ORMs fetch every column unless told otherwise.
• Changing a toasted value rewrites all of it. Change one key in a big jsonb document and Postgres stores the whole document again. Keep a hot counter out of the document; better still, give it a narrow table of its own (Move hot counters to a narrow table).
• Medium values are the awkward case. A 1 to 2 KB value isn't toasted and makes every row large, so only a handful fit per page.

create table docs (
  id   bigint generated always as identity primary key,
  body text
);
insert into docs (body) values (repeat('a', 1000));
insert into docs (body) values (repeat('a', 3000));
insert into docs (body) values (repeat('a', 100000));
insert into docs (body)
  select string_agg(md5(random()::text), '') from generate_series(1, 100);
insert into docs (body)
  select string_agg(md5(random()::text), '') from generate_series(1, 1000);
 
select id, length(body) as chars, pg_column_size(body) as stored,
       pg_column_compression(body) as compression
from docs order by id;

 id | chars  | stored | compression
----+--------+--------+-------------
  1 |   1000 |   1004 |
  2 |   3000 |     44 | pglz
  3 | 100000 |   1156 | pglz
  4 |   3200 |   3200 |
  5 |  32000 |  32000 |

 chunk_id | chunks | bytes | max_chunk
----------+--------+-------+-----------
    17446 |      2 |  3200 |      1996
    17447 |     17 | 32000 |      1996

create table toast_edge (body text);
insert into toast_edge values (repeat('a', 2000)), (repeat('a', 2010));
select lab.prove(
  'a 2,028-byte row is stored as is; a 2,038-byte one crosses 2,032 bytes and is compressed',
  (array_agg(lp_len order by lp))[1] = 2028
  and (array_agg(lp_len order by lp))[2] < 100
  and (select array_agg(pg_column_compression(body) is not null order by length(body))
       from toast_edge) = '{f,t}')
from heap_page_items(get_raw_page('toast_edge', 0));

select lab.prove(
  'random hex doesn''t compress, so rows 4 and 5 move out of line',
  bool_and(pg_column_compression(body) is null)
  and bool_and(pg_column_toast_chunk_id(body) is not null)
  and array_agg(pg_column_size(body) order by id) = '{3200,32000}')
from docs where id in (4, 5);
 
select reltoastrelid::regclass as toast_table from pg_class where relname = 'docs' \gset
select lab.prove(
  'out-of-line values are cut into chunks of at most 1,996 bytes: 2 chunks for row 4, 17 for row 5',
  (select array_agg(n order by n) from
     (select count(*) as n from :toast_table group by chunk_id) c) = '{2,17}'
  and (select max(length(chunk_data)) from :toast_table) = 1996);

alter table docs add column title text;
select pg_column_toast_chunk_id(body) as chunk_before from docs where id = 5 \gset
update docs set title = 'renamed' where id = 5;
select lab.prove(
  'an update that doesn''t change a toasted value keeps the same TOAST pointer',
  pg_column_toast_chunk_id(body) = :chunk_before)
from docs where id = 5;
 
update docs set body = body || 'x' where id = 5;
select lab.prove(
  'an update that changes a toasted value stores the whole new value, every chunk',
  pg_column_toast_chunk_id(body) <> :chunk_before
  and (select count(*) from :toast_table t
       where t.chunk_id = pg_column_toast_chunk_id(d.body)) = 17)
from docs d where id = 5;

alter table docs alter column body set compression lz4;
insert into docs (body) values (repeat('a', 100000));
select id, pg_column_size(body), pg_column_compression(body)
from docs where id = 6;

 id | pg_column_size | pg_column_compression
----+----------------+-----------------------
  6 |            411 | lz4

create table ttt_256 (a text, b text) with (toast_tuple_target = 256);
insert into ttt_256 values (repeat('a', 1500), null);
select lab.prove(
  'toast_tuple_target = 256 does not touch a 1.5 KB row: toasting still starts at about 2 KB',
  (select pg_column_compression(a) from ttt_256) is null
  and (select pg_column_size(a) from ttt_256) = 1504);

Fillfactor leaves room for updates

By default Postgres fills table pages completely; the fillfactor storage parameter tells inserts to stop earlier and leave room on each page. That room is for updates, which write a new version of the row and are much cheaper when it fits on the same page, so choosing the value is part of Leave room on the page, or every update moves.

Inserts find room without scanning the table

How does an insert find a page with room? Not by scanning the table. The _fsm fork records roughly how much each page has free, one byte per page in steps of 32 bytes: page 0 of events, with 136 bytes free, is recorded as 128 (after setting aside 4 for a line pointer). Finding a page with room takes a few reads. Vacuum updates the map, which is when space from deleted rows becomes reusable. When no page has room, the table grows at the end.

select lab.prove(
  'the FSM stores one byte per page in 32-byte steps: events page 0 (136 free) is recorded as 128',
  avail = 128 and avail % 32 = 0)
from pg_freespace('events') where blkno = 0;

Two bits per page let scans and vacuum skip work

Two bits per page are why an index-only scan can skip the table, and why vacuuming a big, mostly static table is cheap. The last fork, _vm, is the visibility map: two bits for each page of the table.

• all-visible: every row on the page is visible to every transaction, now and in the future.
• all-frozen: every row on the page is frozen, safe from transaction ID wraparound forever (Old rows must be frozen before the XID counter wraps).

The pg_visibility extension summarizes it:

create extension pg_visibility;
select * from pg_visibility_map_summary('events');

 all_visible | all_frozen
-------------+------------
       35173 |          0

Every page is all-visible, because the seed ended with vacuum analyze. Two parts of Postgres lean on this map. Index-only scans skip the table page when it's all-visible and answer from the index alone (The B-tree does the heavy lifting). Vacuum skips all-visible pages (Vacuum is not optional). Any change to a page clears its bit; vacuum sets it again once every transaction can see the change.

update accounts set name = name where id = 1;
select lab.prove(
  'any change to a page clears its all-visible bit',
  (select all_visible from pg_visibility_map('accounts', 0)) = false
  and (select flags & 4 from page_header(get_raw_page('accounts', 0))) = 0);
 
vacuum accounts;
select lab.prove(
  'vacuum sets it again once every transaction can see the change',
  (select all_visible from pg_visibility_map('accounts', 0)) = true
  and (select flags & 4 from page_header(get_raw_page('accounts', 0))) = 4);

Count pages before you create the table

Put the pieces together and you can predict a table's size before it exists:

row size      = 24 (header) + column data + padding, rounded up to 8
bytes per row = row size + 4 (line pointer)
rows per page = floor(8168 x fillfactor / 100 / bytes per row)
pages         = rows / rows per page

8,168 is the page's 8,192 bytes minus its 24-byte header. events rows average about 139 bytes, so about 57 fit on a page, and 2,000,000 rows need about 35,100 pages. The real table has 35,173. Do that in your head the next time someone proposes a table that will take 50 million rows a month: how many pages, how many hot, and does it fit in memory?

Page count drives everything that follows, starting with which of those pages get to stay in memory. And every update and delete changes it, in ways that surprise most people the first time they look.

The short version

• A table is a file of 8 KB pages, plus forks for the free space map and visibility map. Ask pg_total_relation_size how big it really is.
• 8 KB is fixed when Postgres is built (block_size, read-only). Change how many rows fit on a page with the schema, not a custom build.
• Every row carries a 24-byte header and a 4-byte line pointer. Small rows are mostly overhead.
• A ctid is a physical address (page, slot) that changes on every update. Never store it.
• NULLs cost one bit, not the width of the column.
• Order columns from 8-byte fixed-width types down to 1-byte types, variable-length last. The wrong order cost 27% more space here.
• Values over about 2 KB are compressed, then moved to a TOAST table. Don't select * wide columns you don't need.
• Fillfactor leaves room on each page for updates; the HOT chapter covers choosing it.

Further reading

• Database page layout, PGDG. The 8 KB page, line pointers, and every tuple header field, from the source of truth.
• TOAST, PGDG. Thresholds, storage strategies, and compression options.
• Free space map and visibility map, PGDG. The two small forks that make inserts and vacuum fast.
• pageinspect, PGDG. Every function used in this chapter, and more.
• Heap table structure, Hironobu Suzuki, The Internals of PostgreSQL. A diagram-first companion explanation of the same layout.