Why Sequential Reads Beat Random Reads in Postgres at Scale

We had a large table - hundreds of gigabytes - that needed to be fully exported and processed in batches. The pipeline fetched rows from Postgres in batches of 2000, processed them, and pushed results downstream.

It was slow. Much slower than expected. The bottleneck wasn't the processing. It was Postgres. Specifically, how we were reading from it.

How Postgres Stores Data

Before getting to the fix, you need to understand three things: pages, slots, and sequence columns.

Pages are fixed 8KB chunks on disk. Everything Postgres stores lives inside pages.

disk: [page 0][page 1][page 2][page 3]...

       8KB     8KB     8KB     8KB

Slots are positions of rows within a page. Each page holds however many rows fit in 8KB - typically 10-20 rows depending on row size.

page 1 (8KB):

  slot 1: [row data - ~500B]

  slot 2: [row data - ~500B]

  slot 3: [row data - ~500B]

  ...

A row's physical address is (page, slot). Postgres calls this a ctid.

A sequence column (like an id or created_at) is just a business column in your table - a regular value like any other field. It has no relationship to where the row lives on disk.

page 1:

  slot 1: { id: 892, name: "foo", status: "active" }

  slot 2: { id: 3,   name: "bar", status: "pending" }

  slot 3: { id: 445, name: "baz", status: "active" }

Rows are written to the heap in insert order, not id order. Deletes, updates, and bulk loads all shift things around. So id 1, 2, 3 can be on completely different pages scattered across the table.

The Index Makes It Worse at Scale

Postgres builds a B-tree index on id that maps each value to a ctid:

Index (B-tree on id):            Heap (actual rows on disk):



id → (page, slot)                page 0: [id=892][id=3  ][id=445]

  1 → (5, 2)                     page 1: [id=7  ][id=1  ][id=22 ]

  2 → (2, 3)                     page 2: [id=2  ][id=567][id=88 ]

  3 → (0, 2)                     ...

When you run WHERE id > X LIMIT 2000:

1. Postgres walks the B-tree to find matching ids in order


2. For each one, jumps to a random heap page to fetch the row


3. 2000 rows = up to 2000 random page reads, each potentially on a different part of disk

At small scale this is fine. At hundreds of gigabytes it kills you - the disk head is constantly seeking to new locations.

The Sequential Scan Fix

A cursor tells Postgres to scan the heap directly, page by page, from start to finish:

BEGIN;



DECLARE rows_cursor CURSOR FOR

  SELECT * FROM events

  WHERE id > 0

  ORDER BY id;



FETCH 2000 FROM rows_cursor;  -- reads ~130 pages sequentially

FETCH 2000 FROM rows_cursor;  -- continues from where it left off

FETCH 2000 FROM rows_cursor;  -- no seeking, no index lookups

-- ...



COMMIT;

FETCH 2000 doesn't mean "read 2000 pages". Each page holds N (e.g. 16) rows, so fetching 2000 rows reads ~130 pages. The cursor remembers where it stopped and the next fetch continues from there.

Across the entire export, every 8KB page is read exactly once. No page is visited twice. No index lookups. The OS can prefetch pages ahead because the read pattern is completely predictable.

Sequential vs Random - Side by Side

Random reads (index scan):

  query 1 → index lookup → jump to page 450 → read 16 rows

  query 2 → index lookup → jump to page 23  → read 16 rows

  query 3 → index lookup → jump to page 891 → read 16 rows

  disk head: seeks constantly



Sequential scan (cursor):

  fetch 1 → page 0  → read 16 rows

  fetch 2 → page 1  → read 16 rows

  fetch 3 → page 2  → read 16 rows

  disk head: moves forward, never back

Same data. Same Postgres. Just read in a straight line instead of jumping around.

The Trade-off

The cursor holds an open transaction for the duration of the scan. For a large table that takes hours or days to process, this blocks vacuum, bloats pg_wal, and holds locks.

In practice, keyset pagination gets most of the benefit without a long-lived transaction:

-- track last seen id, restart cleanly if interrupted

SELECT * FROM events

WHERE id > :last_seen_id

ORDER BY id

LIMIT 2000;



-- update :last_seen_id after each batch and repeat

This isn't a pure sequential scan but it's close enough - as long as id correlates with physical insert order, most reads will be forward-moving on disk.

Result

When a full table export is slow, the first thing to check isn't hardware or downstream processing. It's whether you're doing thousands of random index lookups when you could be reading the table in a straight line.

The index is great for finding a few rows. For reading everything, it's just overhead.