An index page is a block of memory/disk storage where part of the index data is stored.

More precisely:

• A page is a fixed-size block of data — typically 8 KB (in systems like SQL Server, PostgreSQL, MySQL with InnoDB).
• When a database creates an index (often a B-tree structure), it stores the index entries inside pages.
• The database doesn’t read individual rows — it reads pages.
• Pages are the units of read/write operations in a database.

How It Works

In a typical B-tree or B+ tree index:

• The root page holds the top level of the index (entry points).
• The intermediate pages hold pointers to child pages.
• The leaf pages contain the actual key values (and often a pointer to the data row — or sometimes the row itself, depending on the database).

🗂️ Visual Sketch:

           [ Root Page ]
               /    \
       [ Page 1 ]  [ Page 2 ]
        /     \       /     \
 [Leaf1] [Leaf2] [Leaf3] [Leaf4]

Each [Page] and [Leaf] here is a page — usually 8 KB.

• Leaf pages are where the actual sorted index entries are.
• Internal pages guide the search, kind of like a phonebook index.

Inside an Index Page

Each page contains:

• Keys (the column values you are indexing).
• Pointers to the next level (for non-leaf pages) or to the row (for leaf pages).
• Metadata (page header, free space info, etc.).

📝 Example inside a leaf page:

[Page Header][ Key1 | Row Pointer ][ Key2 | Row Pointer ][ Key3 | Row Pointer ] ... [Free Space]

Why Are Pages Important?

1. Efficiency:
   • Reading 8 KB of data at once is faster than reading small bits many times.
   • Disk and memory operations are optimized for block (page) reads.
2. I/O Cost:
   • Database performance is often measured by the number of pages it must read (logical reads).
   • More fragmented → more scattered pages → more I/O → slower queries.
3. Index Size:
   • Larger indexes = more pages.
   • Smaller indexes = fewer pages = faster navigation.
4. Page Splits:
   • When a page is full and you insert a new key, the database may have to split the page into two pages — this can cause fragmentation.
   • Many page splits mean the index becomes less efficient.

---

Page Splitting Example

Imagine this:

• Page can hold 4 keys.
• It’s full: [1, 2, 3, 4]
• You insert 5 — no space.

Result:

• Split into two pages: [1, 2] and [3, 4, 5]
• This keeps the tree balanced but creates extra work — and over time causes fragmentation if splits happen often.

---

In Short

Concept	Meaning
Page	A fixed-size block (e.g., 8 KB) for storing index data.
Index Page	A page that holds part of the index (keys + pointers).
Root/Leaf Pages	Root = entry point; Leaf = actual key entries.
Page Split	When a page is full, it splits to keep the tree balanced — but causes fragmentation over time.

---

Simple Analogy

Think of a phone book:

• One page holds a chunk of names and phone numbers.
• If you add too many new names, you have to add more pages and reorganize the index.