solidcodersolidcoder
Explore Courses
solidcodersolidcoder

Practical courses for software engineering interviews — no gatekeeping, no fluff.

High Level Design

  • High Level Design Concepts
  • PostgreSQL Internals

Explore

  • Courses
  • About
  • Privacy Policy
  • Terms

© 2026 solidcoder · Practical courses for software engineering interviews.

Built for the AI era — learn by doing.

Home/High-Level Design/PostgreSQL Internals/Reading a Row in PostgreSQL — From Query to shared_buffers
Chapters — PostgreSQL Internals▾

Reading a Row in PostgreSQL — From Query to shared_buffers

Read Path·Heap Page·B-tree·Sequential Scan·ctid·Buffer Manager11 min read·Sep 18, 2026

Chronological read path: Server → Postgres, heap pages on disk, index vs sequential scan to find the page, then shared_buffers hit/miss and row read.

What happens when we try to read a row

Let's start with a simple query:

SELECT * FROM accounts WHERE id = 7;

From the application's perspective, the operation is straightforward:

flowchart LR
    APP["Application"]:::svc -->|"SELECT * FROM accounts WHERE id = 7;"| DB[(Postgres DB)]:::db

The application sends the SQL query to PostgreSQL over a database connection.

From here, PostgreSQL takes over.

It needs to:

  1. Decide how to find the requested row.
  2. Identify the database page containing the row.
  3. Check whether that page is already in shared_buffers.
  4. If necessary, read the page from storage into memory.
  5. Read the row from the in-memory page.
  6. Return the result to the application.

Let's walk through that process in order.

The DB has pages, sitting on disk

Before PostgreSQL can read a row, we need to understand how the row is stored.

A PostgreSQL table is not stored on disk as a long list of individual rows.

Table data is organized into fixed-size pages.

By default, a PostgreSQL page is 8 KB.

Conceptually, a table's heap storage looks like this:

flowchart LR
    subgraph DISK["Disk — Persistent Storage"]
        direction LR

        subgraph HEAP["Heap File"]
            direction LR
            P0["Page 0<br/>8 KB"]:::pageYellow
            P1["Page 1<br/>8 KB"]:::pageYellow
            P2["Page 2<br/>8 KB"]:::pageYellow
            P3["Page 3<br/>8 KB"]:::pageYellow
        end
    end
    classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;

Each page can contain multiple rows.

For example, Page 2 might contain:

Page 2 — 8 KB

┌─────────────────────────────────┐
│ Row: id=1, name=Alice           │
│ Row: id=2, name=Bob             │
│ Row: id=3, name=Charlie         │
│ Row: id=4, name=David           │
│ Row: id=5, name=Emma            │
│ Row: id=6, name=Frank           │
│ Row: id=7, name=John       ◄────│
│ ...                             │
└─────────────────────────────────┘

So when we execute:

SELECT * FROM accounts WHERE id = 7;

PostgreSQL does not simply ask the disk:

"Give me row 7."

Instead, it ultimately needs to find:

Which heap page contains the row I need?

Once PostgreSQL knows the required page, another important question appears:

Is that page already in memory?

That's where shared_buffers comes in.

How Postgres finds the right page — search strategy

Now that we understand pages and memory, let's return to our query:

SELECT * FROM accounts WHERE id = 7;

PostgreSQL first needs to determine how it should search for the row.

The query planner considers the available access paths and their estimated costs.

Two important possibilities are:

  • Sequential Scan — scan the table's heap pages.
  • Index Scan — use an index to locate matching rows more directly.

An index does not automatically mean PostgreSQL will use it.

PostgreSQL chooses the plan it estimates will be cheaper for the particular query and data distribution.

No index — sequential scan

Suppose there is no useful index for:

SELECT * FROM accounts WHERE id = 7;

PostgreSQL may choose a Sequential Scan.

Conceptually, it walks through the heap pages:

Page 0
  ↓
Page 1
  ↓
Page 2
  ↓
Page 3
  ↓
...

For each page, PostgreSQL checks the rows inside it to see whether they satisfy:

id = 7

Of course, PostgreSQL still uses its buffer manager while doing this.

For every page it needs, PostgreSQL checks whether that page is already available in its buffer pool before obtaining it from storage.

A sequential scan therefore does not mean:

"Read every row directly from disk."

It means PostgreSQL processes the table's pages sequentially, with the normal buffering mechanism underneath.

With an index — B-tree lookup

Now suppose we have an index:

CREATE INDEX ON accounts (id);

The default index type for this statement is a B-tree.

For:

SELECT * FROM accounts WHERE id = 7;

PostgreSQL may choose an Index Scan.

The index has its own pages.

Conceptually, the B-tree looks like:

flowchart TD

    subgraph BTREE["B-tree Index"]
        direction TB

        subgraph ROOT["Root Page"]
            direction LR
            R1["10"]:::svc
            R2["20"]:::svc
            R3["30"]:::svc
        end

        subgraph PAGE1["Page 1"]
            direction LR
            P1A["5"]:::svc
            P1B["7"]:::svc
            P1C["9"]:::svc
        end

        subgraph PAGE2["Page 2"]
            direction LR
            P2A["15"]:::svc
            P2B["25"]:::svc
            P2C["35"]:::svc
        end

        subgraph PAGE3["Page 3"]
            direction LR
            P3A["1"]:::svc
            P3B["3"]:::svc
            P3C["4"]:::svc
        end

        subgraph PAGE4["Page 4"]
            direction LR
            P4A["5"]:::svc
            P4B["7"]:::pageYellow
            P4C["9"]:::svc
        end

        subgraph PAGE5["Page 5"]
            direction LR
            P5A["10"]:::svc
            P5B["12"]:::svc
            P5C["14"]:::svc
        end

        subgraph PAGE6["Page 6"]
            direction LR
            P6A["20"]:::svc
            P6B["25"]:::svc
            P6C["35"]:::svc
        end

        ROOT -->|"keys < 10"| PAGE1
        ROOT -->|"10 ≤ keys < 20"| PAGE2

        PAGE1 -->|"keys < 5"| PAGE3
        PAGE1 -->|"5 ≤ keys < 9"| PAGE4
        PAGE1 -->|"keys ≥ 9"| PAGE5

        PAGE2 -->|"keys ≥ 20"| PAGE6

        PAGE4 -->|"id=7 → ctid (2,3)"| TID["TID<br/>Heap Page 2, Offset 3"]:::pageYellow
    end

    TID --> HEAPPAGE

    subgraph HEAPPAGE["Heap Page 2"]
        direction LR
        H1["Tuple 1"]:::svc
        H2["Tuple 2"]:::svc
        H3["Tuple 3<br/>id = 7"]:::pageYellow
        H4["Tuple 4"]:::svc
    end

    H3 -->|"actual tuple"| ROW["Actual Table Row<br/>id = 7<br/>name = John<br/>balance = 100"]:::down

    classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;

The index allows PostgreSQL to narrow down where matching rows are located instead of checking every heap page.

The index points toward the heap tuple

An index entry does not normally contain the complete table row.

For a regular B-tree index scan, the index entry contains a TID (ctid) that identifies the corresponding heap tuple.

The index helps answer:

Where should PostgreSQL look for the matching heap tuple?

It does not mean PostgreSQL has already loaded the table row.

PostgreSQL may still need to access the corresponding heap page.

Note: PostgreSQL can sometimes use an Index Only Scan, where the required columns can be obtained from the index itself. That is a separate optimization; for this article, we're following the normal heap-access path.

The index has pages too

There is one more important detail: the B-tree index is also stored as pages.

We already saw that PostgreSQL stores the actual table data in heap pages. The B-tree index is separate from the table, but PostgreSQL also stores the index structure in pages.

Those index pages also participate in PostgreSQL's buffer management.

Conceptually:

flowchart TD
    subgraph SB["shared_buffers"]
        direction TB
        ROOT["B-tree Root Page"]:::svc
        INTERNAL["B-tree Internal Page"]:::svc
        LEAF["B-tree Leaf Page"]:::svc
        HEAP["Heap Page<br/>containing the row"]:::pageYellow
    end
    classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;

So an index does not bypass shared_buffers.

It simply helps PostgreSQL find the relevant heap location with fewer page accesses.

How Postgres keeps pages in RAM

PostgreSQL has a memory area called shared_buffers.

It is a pool of RAM used by PostgreSQL to cache database pages.

The important distinction is:

  • Disk contains the persistent database files.
  • shared_buffers contains in-memory copies of database pages that PostgreSQL is using.
  • Rows live inside those pages.

Conceptually:

flowchart LR

    subgraph PG["PostgreSQL Server"]
        direction LR

        subgraph RAM["RAM — shared_buffers"]
            direction LR
            B0["Page 2"]:::pageYellow
            B1["..."]
        end
    end

    subgraph DISK["Disk"]
        direction LR
        D0["Page 0"]
        D1["Page 1"]
        D2["Page 2"]:::pageYellow
        D3["Page 3"]
    end

    D2 -->|"loaded into RAM<br/>on first access"| B0
    classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;

Notice that the disk is outside the PostgreSQL Server box.

The PostgreSQL server manages memory such as shared_buffers, while the database files are persistent storage.

If Page 2 is needed, PostgreSQL can read the persistent Page 2 from storage and place a copy of it in shared_buffers.

The disk copy remains on disk.

The RAM copy is what PostgreSQL can work with efficiently.

PostgreSQL now knows which heap page it needs

At this point, whether PostgreSQL used:

  • a Sequential Scan, or
  • an Index Scan,

it eventually needs one or more heap pages containing the rows it wants.

Let's assume our target row is on:

Heap Page 2

Now the next question is:

Is Heap Page 2 already in shared_buffers?

This is where the actual buffer lookup happens.

PostgreSQL checks shared_buffers

PostgreSQL's backend process handling the query asks the buffer manager for the required page.

Conceptually:

sequenceDiagram
    participant Q as Query
    participant SB as shared_buffers
    participant DISK as Disk

    Q->>SB: Need Heap Page 2
    SB->>SB: Check shared_buffers
    alt Page 2 exists
        SB-->>Q: Page 2 found
        Q->>SB: Read the row
    else Page 2 does not exist
        SB-->>Q: Page 2 not found
        Q->>DISK: read Page 2
        DISK-->>Q: Page 2
        Q->>SB: copy to shared_buffers
        Q->>SB: Read the row
    end

There are two possible outcomes.

Cache hit

If Page 2 is already in shared_buffers, PostgreSQL can use the existing in-memory copy.

This is a buffer hit.

No new read from the storage device is needed for that page.

The backend can inspect the page already present in memory.

Cache miss

If Page 2 is not currently in shared_buffers, PostgreSQL needs to obtain it from storage.

This is a buffer miss.

The required page is read into PostgreSQL's buffer pool, and the backend can then work with the in-memory page.

The page is now in RAM

Suppose Page 2 was a cache miss.

After PostgreSQL obtains it, the relevant state is:

flowchart LR

    subgraph PG["PostgreSQL Server"]
        direction LR

        subgraph RAM["RAM — shared_buffers"]
            direction LR
            B0["Page 2<br/>id=7 is here"]:::pageYellow
            B1["..."]
        end
    end

    subgraph DISK["Disk"]
        direction LR
        D2["Page 2<br/>8 KB"]:::pageYellow
    end

    D2 -->|"read page"| B0
    classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;

PostgreSQL can now inspect Page 2 directly in memory.

Remember:

PostgreSQL loaded the page, not just row id = 7.

The page may contain many other rows as well.

Step 2 — Postgres reads the row from the buffered page

Now Page 2 is available in memory.

PostgreSQL can locate the required tuple inside that page and evaluate the query conditions.

Conceptually:

shared_buffers
┌──────────────────────────────┐
│ Page 2 — 8 KB                │
│                              │
│ id = 1                       │
│ id = 2                       │
│ id = 3                       │
│ id = 4                       │
│ id = 5                       │
│ id = 6                       │
│ id = 7  ◄──── requested row  │
│ id = 8                       │
│ ...                          │
└──────────────────────────────┘

The backend reads the tuple and produces the result for the query.

For:

SELECT * FROM accounts WHERE id = 7;

the result might be:

id | name | balance
---|------|--------
7  | John | 100

That result is then sent back to the application.

Diagram — the full read path

We can now put everything together.

sequenceDiagram
    participant APP as Application
    participant PG as PostgreSQL
    participant PLAN as Query Planner
    participant SB as shared_buffers
    participant DISK as Disk

    APP->>PG: SELECT * FROM accounts WHERE id = 7;
    PG->>PLAN: Choose access path

    alt Sequential Scan
        PLAN-->>PG: Scan heap pages
    else Index Scan
        PLAN-->>PG: Use B-tree to find matching heap tuple
    end

    PG->>SB: Request required heap page

    alt Cache hit
        SB-->>PG: Page already in RAM
    else Cache miss
        SB-->>PG: Page not cached
        PG->>DISK: Read heap page
        DISK-->>PG: Page data
        PG->>SB: Place page in shared_buffers
    end

    PG->>SB: Read requested row from page
    SB-->>PG: Row
    PG-->>APP: Query result

One important mental model

The easiest way to remember the entire read path is:

PostgreSQL finds the page first. Then it makes sure the page is in memory. Then it reads the row from that page.

Think of it as three layers:

1. FIND
   Find the page that contains the required row.
          │
          ▼
2. CHECK
   Is that page already in shared_buffers?
          │
          ├── Yes → Use the page already in memory
          │
          └── No  → Load the page from storage
                       into shared_buffers
          │
          ▼
3. READ
   Read the required row from the page in memory.

An index primarily helps with FIND.

shared_buffers is involved in LOAD.

The actual tuple access happens in READ.

Key takeaways

Key Takeaways
#
Takeaway
Pages
Table data lives in fixed-size 8 KB pages; each page holds multiple rows.
Application
Application sends SQL to PostgreSQL; it never reads database files directly.
Access path
Planner chooses Sequential Scan (walk heap) vs B-tree Index Scan (narrow to ctid) by cost.
B-tree
B-tree entry points to heap tuple via TID/ctid; index does not contain the full row.
Index pages
B-tree pages are also buffer-managed pages, not a bypass of shared_buffers.
shared_buffers
All page access goes through shared_buffers; check RAM first before disk.
Cache hit
Hit = page already in shared_buffers, no I/O.
Cache miss
Miss = read page from disk, copy into shared_buffers, then read.
Read
PostgreSQL reads the requested tuple from the in-memory page.
Index role
Index helps FIND the heap location faster; LOAD/READ mechanism is unchanged.

The core mental model: Find the page → check shared_buffers → load the page if needed → read the row.

Next: what happens when PostgreSQL needs to modify a row that is already sitting inside a buffered page — and why that involves MVCC and WAL.

Up next1/1
Part 1 · Fundamentals
←
← Prev Chapter
Change Data Capture (CDC)
15 min
Next Chapter →
Writing a Row in PostgreSQL
15 min · continue reading
→
Part of a free guide

PostgreSQL Internals

From reading a row to writing with WAL and MVCC — how Postgres really works.

Browse All Guides →
On this page
  • What happens when we try to read a row
  • The DB has pages, sitting on disk
  • How Postgres finds the right page — search strategy
    • No index — sequential scan
    • With an index — B-tree lookup
    • The index points toward the heap tuple
    • The index has pages too
  • How Postgres keeps pages in RAM
  • PostgreSQL now knows which heap page it needs
  • PostgreSQL checks `shared_buffers`
    • Cache hit
    • Cache miss
  • The page is now in RAM
  • Step 2 — Postgres reads the row from the buffered page
  • Diagram — the full read path
  • One important mental model
  • Key takeaways