Database Indexing

What Is a Page in a Database?

A page is the smallest unit of data a database reads or writes to disk.

Think of it like this:

• Disk -> very slow
• RAM -> fast
• DB does not read one row at a time from disk
• DB reads a chunk -> that chunk is a page

Typical page sizes:

• PostgreSQL: 8 KB
• MySQL InnoDB: 16 KB
• Orace: 8 KB (configurable)
• SQL Server: 8 KB

Even if you request 1 row, the DB loads the entire page containing that row.

Why Pages Exist?

Disk Reality

Disk I/O is expensive

• Seek time
• Rotation time
• Latency

Reading 1 byte vs 8KB:

Almost the same cost

So DBs optimize by:

Reading data in fixed-size pages

How Tables Are Stored Using Pages

A table is not a continuous file of rows.

Instead:

Table
 ├── Page 1 → rows 1–50
 ├── Page 2 → rows 51–100
 ├── Page 3 → rows 101–150

Each page contains:

• Page header (metadata)
• Row slots
• Free space

Rows inside a page are packed, not necessarily ordered.

What Happens During a Query

Example:

SELECT * FROM users WHERE id = 7;

Steps:

1. DB finds which page contains row 7
2. Loads that page into buffer cache
3. Reads the row from memory
4. Return result