Storage and Pages

A database does not read single rows from disk. It reads and writes fixed-size pages (also called blocks). In PostgreSQL a page is 8 KB by default. Every table, index, and buffer cache entry is built from pages, so page behaviour explains most performance and storage effects.

Example 1: row storage vs column storage

A row store keeps all columns of one row together. A column store keeps all values of one column together.

flowchart LR
    subgraph Row store
      R1["page 1: (1, Meera, 250000) (2, Ravi, 180000)"]
      R2["page 2: (3, Kiran, 150000) (4, Neha, 150000)"]
    end
    subgraph Column store
      C1["id: 1, 2, 3, 4"]
      C2["name: Meera, Ravi, Kiran, Neha"]
      C3["salary: 250000, 180000, 150000, 150000"]
    end

Plain PostgreSQL tables are always row stores. Extensions (Citus columnar, TimescaleDB compression) add columnar storage.

Example 2: pages and the heap file

A table is stored as a heap file: a sequence of 8 KB pages with no ordering. New rows go into any page that has free space. Each page holds a header, an array of item pointers, free space, and the tuples.

flowchart TB
    subgraph Page["8 KB page"]
      H["Page header (24 bytes)"]
      P["Item pointers: 1 -> 2 -> 3 ... (grow down)"]
      F["Free space"]
      T["Tuples (grow up from the end)"]
      H --> P --> F --> T
    end

The page size is a compile-time setting and can be read from SQL.

SHOW block_size;

 block_size 
------------
 8192
(1 row)

Item pointers give each tuple a stable slot number inside the page, so indexes can point to a slot even when the tuple moves within the page.

Example 3: ctid, the physical row address

ctid is a hidden column (page number, slot number) that gives the physical location of a row version. Page numbers start at 0, slot numbers at 1.

SELECT ctid, id, name FROM departments ORDER BY id;

 ctid  | id |    name     
-------+----+-------------
 (0,1) |  1 | Engineering
 (0,2) |  2 | Sales
 (0,3) |  3 | Marketing
 (0,4) |  4 | HR
 (0,5) |  5 | Legal
(5 rows)

Indexes store a ctid for every entry. An index lookup reads the index, then fetches the heap page named by the ctid.

Example 4: rows per page

Grouping by the page part of the ctid shows how many rows share a page. A table is created with 1000 narrow rows.

CREATE TABLE narrow AS
SELECT g AS id, 'row ' || g AS label FROM generate_series(1, 1000) g;

SELECT (ctid::text::point)[0]::int AS page, count(*) AS rows_in_page
FROM narrow
GROUP BY 1
ORDER BY 1;

 page | rows_in_page 
------+--------------
    0 |          185
    1 |          185
    2 |          185
    3 |          185
    4 |          185
    5 |           75
(6 rows)

185 of these rows fit in each 8 KB page, so 1000 rows need 6 pages. The row width decides how many rows fit. Wider rows mean fewer rows per page and more pages to read for a full scan.

Example 5: UPDATE writes a new tuple version

PostgreSQL never overwrites a row in place. UPDATE inserts a new tuple and marks the old one dead, so the ctid changes.

SELECT ctid, id, salary FROM employees WHERE id = 1;
UPDATE employees SET salary = salary + 1 WHERE id = 1;
SELECT ctid, id, salary FROM employees WHERE id = 1;

 ctid  | id | salary 
-------+----+--------
 (0,1) |  1 | 250000
(1 row)

  ctid  | id | salary 
--------+----+--------
 (0,13) |  1 | 250001
(1 row)

The old version remains in the page until VACUUM reclaims it. MySQL InnoDB updates in place and keeps old versions in an undo log; Oracle and SQL Server also update in place.

Example 6: page fill and fillfactor

fillfactor (default 100 for tables, 90 for B-tree indexes) is the percentage of each page that inserts may fill. The remaining space is kept for updated row versions, which lets them stay on the same page.

CREATE TABLE ff100 (id int, pad text) WITH (fillfactor = 100);
CREATE TABLE ff50  (id int, pad text) WITH (fillfactor = 50);
INSERT INTO ff100 SELECT g, 'x' FROM generate_series(1, 10000) g;
INSERT INTO ff50  SELECT g, 'x' FROM generate_series(1, 10000) g;

SELECT relname, pg_relation_size(oid) / 8192 AS pages
FROM pg_class
WHERE relname IN ('ff100', 'ff50')
ORDER BY relname;

 relname | pages 
---------+-------
 ff100   |    45
 ff50    |    89
(2 rows)

A lower fillfactor uses more pages now, in exchange for cheaper updates later (see HOT updates in MVCC and Vacuum).

Example 7: TOAST for large values

A row must fit in one page, so values larger than about 2 KB are moved to a side table called TOAST (The Oversized-Attribute Storage Technique). PostgreSQL first compresses the value, then stores it out of line in chunks of about 2 KB. pg_column_size shows the stored size of a value.

CREATE TABLE docs (id int, body text);
INSERT INTO docs VALUES
  (1, 'short'),
  (2, repeat('a', 100000)),
  (3, (SELECT string_agg(md5(i::text), '') FROM generate_series(1, 3000) i));

SELECT id, length(body) AS chars, pg_column_size(body) AS stored_bytes
FROM docs ORDER BY id;

 id | chars  | stored_bytes 
----+--------+--------------
  1 |      5 |            6
  2 | 100000 |         1156
  3 |  96000 |        96000
(3 rows)

Row 2 is repetitive: 100000 characters are stored in 1156 bytes after compression. Row 3 (96000 hex characters) does not compress and is stored at full size. Queries that do not read body never touch the TOAST table.

Example 8: measuring table size

Three size functions answer different questions.

SELECT pg_relation_size('docs')       AS heap_bytes,
       pg_table_size('docs')          AS with_toast,
       pg_total_relation_size('docs') AS total;

 heap_bytes | with_toast | total  
------------+------------+--------
       8192 |     155648 | 155648
(1 row)

The heap of docs is one page; nearly all bytes sit in the TOAST table. Monitoring that reads only pg_relation_size under-reports tables with large values.

Example 9: free space and the buffer pool

Each table has a free space map (FSM) that records free space per page, so inserts find a page quickly. Deleted or updated space returns to the FSM only after vacuum.

Reads go through a cache:

flowchart LR
    Q[Query] --> SB{"shared_buffers<br/>hit?"}
    SB -- yes --> R[Return page]
    SB -- no --> OS{"OS page cache<br/>hit?"}
    OS -- yes --> SB
    OS -- no --> D[(Disk)]
    D --> OS

PostgreSQL relies on the OS page cache as a second layer, so a page can be cached twice. EXPLAIN (ANALYZE, BUFFERS) reports shared hit (found in shared_buffers) and read (requested from the OS). Oracle and SQL Server manage their own buffer pool; InnoDB does the same with innodb_buffer_pool_size, commonly set to 50-75% of RAM.

Example 10: sequential vs random I/O and B-tree pages

Disks read a run of adjacent pages much faster than the same number of scattered pages. This is why the planner has separate costs.

SELECT name, setting
FROM pg_settings
WHERE name IN ('seq_page_cost', 'random_page_cost')
ORDER BY name;

       name       | setting 
------------------+---------
 random_page_cost | 4
 seq_page_cost    | 1
(2 rows)

A sequential scan reads the heap page by page (cost 1.0 per page). An index scan reads random heap pages (cost 4.0 by default), so it wins only when few rows match.

B-tree nodes are also 8 KB pages. One page holds hundreds of keys, so the tree is shallow: three or four levels reach hundreds of millions of rows, and each level costs one page read. Matching the node size to the disk page size means one I/O loads a whole node. See How Indexes Work.

Summary

• PostgreSQL stores tables as heap files of 8 KB pages; a row version is addressed by ctid = (page, slot).
• UPDATE writes a new tuple version and changes the ctid; the old version stays until vacuum.
• Values over about 2 KB are compressed and moved to a TOAST table; use pg_total_relation_size for the full footprint.
• Reads pass through shared_buffers and the OS page cache; sequential I/O is cheaper than random I/O.
• Row stores suit OLTP, column stores suit OLAP scans of few columns.