Storage Internals — Pages, Buffers & B+ Trees
Storage internals — page, buffer, B+ tree
1. Below SQL — Where Does the Data Actually Live?
Until now we have treated the database as a black box: we throw SELECT at it, rows come back.
But behind that one line of SQL there is a small operating system in itself —
a storage manager that fights physics every day. Disks are slow. Memory is fast but small.
CPU caches are tiny. Every database in the world — SQLite, PostgreSQL, MySQL, Oracle, MongoDB — is in the end
a clever stack of data structures wrapped around this one painful fact: moving bytes from disk costs a million
times more than moving them inside RAM.
SELECT দিলে সাথে সাথেই row চলে আসে। কিন্তু সেই এক লাইন SQL-এর পেছনে আসলে একটা ছোটখাটো operating system লুকিয়ে আছে — storage manager। এই layer-এর কাজই হচ্ছে disk-এর ধীরগতি, RAM-এর সীমিত আকার এবং CPU cache-এর ছোট size — এই তিনটির সাথে রোজ যুদ্ধ করা। মূল সত্য একটাই: disk থেকে এক byte আনা মানে RAM-এর তুলনায় প্রায় দশ লাখ গুণ বেশি সময়।
In this module you will learn the four pillars of every classical disk-based DBMS: (1) how rows are packed into pages; (2) how the buffer pool caches those pages in RAM; (3) how B+ trees let us find a single row among billions in milliseconds; (4) why a newer family of structures — the LSM-tree — has taken over the write-heavy workloads at companies like Pathao, Daraz and Cassandra-shops worldwide.
2. Pages and Tuples — The Atom of Storage
A disk does not understand the concept of a "row". It understands blocks — fixed-size chunks (typically 4 KB or 8 KB on modern SSDs). To match this reality, every DBMS organizes its data as a sequence of equally-sized pages. SQLite's default page size is 4096 bytes; PostgreSQL uses 8192; Oracle traditionally 8 KB. Inside one page, the DBMS packs as many tuples (rows) as will fit and keeps a small directory at the top called the slot array (sometimes "line pointers").
Each tuple inside a page is identified by its RID — Row Identifier — typically the pair
(page_number, slot_number). When an index says "row 7 lives in page 412 slot 3", the engine can
jump directly to that tuple in O(1).
-- SQLite exposes its page size and total page count.
-- Run this and notice: even an "empty" database has 1 or 2 pages.
PRAGMA page_size;
PRAGMA page_count;
CREATE TABLE student(
id INTEGER PRIMARY KEY,
name TEXT,
city TEXT
);
INSERT INTO student VALUES
(1,'Rahim','Dhaka'),
(2,'Karim','Khulna'),
(3,'Sumi','Sylhet');
PRAGMA page_count; -- now larger
SELECT rowid, name FROM student;3. Row-Store vs Column-Store — Two Ways to Slice the Same Table
The traditional layout we just saw is called a row-store: tuple n sits as a single
contiguous blob inside its page. That is wonderful for OLTP queries like
SELECT * FROM student WHERE id = 7 — one disk read brings the entire row.
But it is terrible for analytics: SELECT AVG(age) FROM customer drags every column of every row
into memory just to read one column.
A column-store turns the layout 90 degrees. Each column is stored in its own file (or page set),
so AVG(age) reads only the age column — sometimes 50× less data.
ClickHouse, Apache Parquet, BigQuery, Snowflake, Amazon Redshift and MonetDB all use column layouts.
Bangladesh examples: any analytics dashboard at bKash, Grameenphone or Pathao that crunches billions of rows
is almost certainly running on a column-store.
city column has only ~64 distinct values nationwide.
| Property | Row-store | Column-store |
|---|---|---|
| Best for | OLTP — point lookups, single-row updates | OLAP — aggregations across millions of rows |
| Compression | Modest — values mixed | Excellent — same dtype together (10–30× typical) |
| Update cost | Cheap (one row in one place) | Expensive — touches every column file |
| Real systems | SQLite, MySQL InnoDB, PostgreSQL heap | ClickHouse, Parquet, Druid, Vertica, BigQuery |
| Bangladesh use | bKash core ledger, eCab booking table | Daraz analytics warehouse, Grameenphone CDR vault |
4. The Buffer Pool — RAM as a Cache for the Disk
The DBMS never operates on disk pages directly. It reserves a slab of RAM called the buffer pool (or "page cache" or "shared buffers" in PostgreSQL) and treats it like a giant in-memory cache of disk pages. When a query needs page 412, the buffer manager checks: is page 412 already in the pool? If yes — a cache hit, microseconds. If no — a cache miss: evict some other page (using LRU, CLOCK, or LRU-K policy), read 412 from disk (milliseconds), pin it in RAM, hand it over.
Pinning is the rule that keeps a page in memory while a query is reading or writing it — you cannot evict a page that someone is currently using. Once all references release it, the page is unpinned and becomes a candidate for eviction. Pages that have been modified are marked dirty and must be written back to disk before they can be evicted, to satisfy durability.
A modern NVMe SSD does ~100 µs per random read. RAM does ~100 ns. That is a 1000× ratio. If 1000 queries hit RAM and only 1 hits disk, total time is dominated by that one query — but if hit rate falls from 99% to 90%, throughput drops by an order of magnitude.
একটা NVMe SSD-তে random read ~100 microsecond, RAM-এ ~100 nanosecond — তফাৎ 1000 গুণ। তাই buffer pool-এর hit rate 99%+ রাখতে পারলেই production database "fast" থাকে।
5. Heap Files vs Clustered (Index-Organized) Tables
How are pages of a table connected? Two big traditions exist.
- Heap-organized (PostgreSQL, MySQL MyISAM, Oracle default): The table is a pile (heap) of pages in arbitrary insertion order. Indexes are separate structures whose leaves carry RIDs pointing into the heap. To read row 7 you first descend the index, then jump into the heap — two structures, two reads.
- Index-organized / Clustered (SQLite, MySQL InnoDB, Oracle IOT, MS SQL clustered index): The table itself is a B+ tree on the primary key. Leaves of the tree hold the actual rows. Reading by primary key means a single descent — no second hop.
-- In SQLite, a table is index-organized by default. The "INTEGER PRIMARY KEY"
-- column is an alias for ROWID and IS the clustering key — rows live in
-- ROWID order on disk. WITHOUT ROWID changes that.
CREATE TABLE heap_like(id INTEGER, val TEXT);
CREATE TABLE clustered(id INTEGER PRIMARY KEY, val TEXT);
INSERT INTO clustered VALUES
(300,'three'),(100,'one'),(200,'two');
-- rows come back in id order — they are physically sorted on disk
SELECT rowid, id, val FROM clustered;
-- EXPLAIN QUERY PLAN proves it: PK lookup uses the clustering tree directly,
-- not a secondary index.
EXPLAIN QUERY PLAN
SELECT * FROM clustered WHERE id = 200;6. The B+ Tree — How a Database Finds 1 Row Among 10 Billion
A B+ tree is a balanced multi-way search tree designed specifically for disk. The differences from a binary search tree are crucial: each node holds hundreds of keys, not one — that way the tree depth stays at 3 or 4 even for tables with billions of rows. And a B+ tree comes in two node flavours:
- Internal nodes — only carry keys + child pointers; route searches downward.
- Leaf nodes — hold the actual data (clustered) or RIDs (non-clustered) and are linked left-to-right in a doubly linked list, so range scans are sequential and brutally fast.
6.1 Insert — split when a leaf overflows
Inserting a key walks the tree to the correct leaf, then places the new key there. If the leaf is full, the engine splits it into two halves and pushes the median key up to the parent. If the parent is full, that propagates upward — and if it reaches the root, the root itself splits and the tree grows taller by exactly one level. This is how a B+ tree stays perfectly balanced through millions of inserts without any explicit rebalancing pass.
6.2 Delete — borrow or merge
When a deletion empties a leaf below the minimum fill ratio, the engine first tries to borrow a key from a sibling. If the sibling is also at the minimum, the two siblings merge into one and the corresponding key is removed from the parent. This too can propagate upward and may shrink the tree.
-- Watch SQLite use a B+ tree index. EXPLAIN QUERY PLAN tells us
-- whether the engine SCANs (full table) or SEARCHes (B+ tree descent).
CREATE TABLE customer(
id INTEGER PRIMARY KEY,
phone TEXT,
name TEXT
);
CREATE INDEX idx_phone ON customer(phone);
WITH RECURSIVE g(i) AS (
SELECT 1 UNION ALL SELECT i+1 FROM g WHERE i < 5000
)
INSERT INTO customer(id,phone,name)
SELECT i, '+8801'||printf('%09d',i), 'User-'||i FROM g;
EXPLAIN QUERY PLAN
SELECT name FROM customer WHERE phone = '+880100000247';
-- → SEARCH customer USING INDEX idx_phone (a B+ tree descent)
EXPLAIN QUERY PLAN
SELECT name FROM customer WHERE name = 'User-247';
-- → SCAN customer (no index → full B+ tree leaf scan)7. LSM-Trees — The Modern Alternative for Write-Heavy Workloads
B+ trees are excellent for read-mostly OLTP. But what about a system that ingests millions of writes per second — a logging pipeline at Pathao, an IoT sensor stream from a smart-meter rollout in Sylhet, a chat backend? Every B+ tree insert may dirty a random page; with billions of writes you saturate disk with random I/O. The Log-Structured Merge-tree (LSM-tree) flips the strategy.
- All writes first go to an in-memory sorted structure called a memtable (often a skiplist).
- When the memtable fills, it is flushed to disk as an immutable, sorted file — an SSTable (Sorted String Table).
- Background threads compact small SSTables into bigger ones, merging duplicates and discarding tombstones.
- Reads check the memtable first, then SSTables newest-to-oldest, helped by Bloom filters to skip files that definitely do not contain the key.
| Trade-off | B+ tree | LSM-tree |
|---|---|---|
| Write amplification | ~1× (one page rewrite per insert) | 10–30× (every key is rewritten during compactions) |
| Read amplification | 1 descent (~3-4 reads) | Possibly multiple SSTables + Bloom filter |
| Space amplification | ~33% wasted (half-empty pages) | Variable, depends on compaction |
| Sequential writes? | No — random | Yes — append-only |
| Used by | SQLite, PostgreSQL, MySQL InnoDB, Oracle | RocksDB, Cassandra, LevelDB, ScyllaDB, HBase |
Note: SQLite4 (an experimental successor that never shipped) explored an LSM-style storage engine, and RocksDB — the LSM library extracted from Facebook's modified LevelDB — is now embedded inside MySQL's MyRocks engine, MongoDB-with-RocksDB, and many startups' homegrown databases.
8. Putting It All Together — Tracing One SELECT
Let us trace a single query — SELECT name FROM customer WHERE id = 247 — from SQL to the disk
and back, on a clustered B+ tree table.
- SQL parser → logical plan: scan
customerforid=247. - Optimizer notices
idis the primary key (clustering key) → chooses an index seek. - Executor asks the access method: "find leaf containing key 247".
- B+ tree descent: read root page (probably already pinned in RAM), choose the right child, read internal page, choose leaf page.
- Buffer manager satisfies each request: hit if cached, miss → 4 KB read from SSD.
- Leaf is decoded into tuples; the slot for
id=247is located in O(log k) using a binary search inside the page. - Tuple is returned up the operator tree, projected (only the
namecolumn), and shipped back to the client.
The whole thing — for a row found in a warm buffer pool — finishes in under 10 microseconds. The same query against a cold disk costs ~3 reads × 100 µs ≈ 300 µs, still very fast. That gap is exactly why DBAs obsess over buffer-pool hit rate.
SELECT name FROM customer WHERE id = 247 — কিভাবে disk-এ পৌঁছে আবার ফেরত আসে: parser → optimizer → access method → B+ tree descent (root → internal → leaf) → buffer pool hit/miss → tuple decode → projection। গরম buffer pool-এ এটা ~10 microsecond, ঠান্ডা disk-এ ~300 microsecond — তাই DBA-রা hit rate নিয়ে এত মাথা ঘামান।
9. Practice Problems
Each problem comes with a Show Answer button containing the explanation and, where useful, runnable SQLite code. Try them yourself first.
-
What is the default page size of an SQLite database, and how would you find it for a given file?SQLite-এ default page size কত? কোনো নির্দিষ্ট database file-এর page size কীভাবে দেখবেন?
✨ Show Answer (উত্তর দেখুন)
Answer: Default is 4096 bytes since SQLite 3.12 (2016). Use
PRAGMA page_size;.ans1.sqlPRAGMA page_size; PRAGMA page_count; -
A query
SELECT SUM(amount) FROM transactionsruns over 100 million rows. Which storage layout — row or column — will be faster, and why?১০ কোটি row-এর উপরSELECT SUM(amount)চালালে row-store নাকি column-store দ্রুত হবে এবং কেন?✨ Show Answer
Answer: A column-store. It reads only the
amountcolumn from disk, while a row-store must drag every column of every row through I/O and CPU cache. With ~10 columns per row that is roughly a 10× I/O reduction — and column-stores compress that one column extremely well, often another 5–10× saving. -
Use
EXPLAIN QUERY PLANto prove that a primary-key lookup uses the clustered tree, while a non-indexed column lookup does a full scan.EXPLAIN QUERY PLANদিয়ে দেখান যে PK lookup index ব্যবহার করে কিন্তু non-indexed column-এ full scan হয়।✨ Show Answer
ans3.sqlEXPLAIN QUERY PLAN SELECT * FROM u WHERE id = 2; EXPLAIN QUERY PLAN SELECT * FROM u WHERE email = 'b@x';The first plan shows
SEARCH … USING INTEGER PRIMARY KEY— clustered tree. The second showsSCAN— full table walk. -
Suppose a B+ tree node holds 200 keys. How tall does the tree need to be to index 1 billion rows?একটি B+ tree node-এ যদি 200 key থাকে, ১০০ কোটি row index করতে গাছ কতটা গভীর হবে?
✨ Show Answer
Answer: log200(109) ≈ 4 levels. So even a billion-row table takes 4 page reads at most for any point lookup — that is the magic of high-fanout trees.
-
Why are leaf nodes in a B+ tree linked left-to-right? Give a concrete query example.B+ tree-এর leaf node-গুলো বাঁ থেকে ডানে linked কেন থাকে? একটা concrete query উদাহরণ দিন।
✨ Show Answer
Answer: So that range scans become sequential. Example:
SELECT * FROM order_log WHERE created_at BETWEEN '2025-05-01' AND '2025-05-31'— the engine descends to the first leaf, then walks right via the sibling pointers without ever returning to the root. That is O(K + log N) instead of O(K log N). -
Define dirty page and explain why the buffer manager must not evict a dirty page without writing it out first.Dirty page কী? buffer manager কেন একটি dirty page disk-এ না লিখে evict করতে পারে না?
✨ Show Answer
Answer: A dirty page is one whose in-memory copy has been modified by a transaction but whose on-disk copy is still the old version. If the buffer manager evicted it without flushing, the change would be silently lost when the page is later re-read from disk — directly violating durability, the D in ACID.
-
Insert these keys in order into an empty B+ tree of order 3 (max 3 keys per node): 10, 20, 5, 6, 12, 30, 7, 17. Sketch the final tree.খালি B+ tree (order 3)-এ ক্রমে এই key-গুলো insert করুন: 10, 20, 5, 6, 12, 30, 7, 17। চূড়ান্ত tree-টি draw করুন।
✨ Show Answer
Answer (one valid shape — splits depend on tie-breaking):
[ 10 | 20 ] / | \ [5,6,7] [10,12,17] [20,30]Three leaves, sibling-linked. Internal root holds the separator keys 10 and 20. Note that the separator
10is duplicated in the leaf because in B+ trees all real data lives in the leaves. -
A workload writes 200,000 events per second to a single table. Would you choose a B+ tree-based storage engine or an LSM-tree-based one? Justify in 3 sentences.এক table-এ প্রতি সেকেন্ডে ২ লাখ event write হয়। B+ tree না LSM-tree বেছে নেবেন? তিন বাক্যে যুক্তি দিন।
✨ Show Answer
Answer: Choose LSM. (1) LSM turns those 200k random writes into sequential append-only writes via the memtable + SSTable flow, which an SSD can sustain easily. (2) B+ trees would dirty hundreds of random pages per second, saturating buffer-pool flushes and causing write stalls. (3) The trade-off is read amplification, but a Bloom filter per SSTable keeps point reads at ~1 SSTable seek on average.
-
What is a slot array, and why does putting it at the top of the page (with tuples growing from the bottom) make variable-length tuples easier to manage?Slot array কী? এটিকে page-এর উপরে রেখে tuple-গুলোকে নিচ থেকে বাড়তে দেওয়া হয় কেন?
✨ Show Answer
Answer: The slot array is a small directory of (offset, length) pointers — one per tuple. Putting it at the top growing downward, while tuples grow upward from the bottom, means the free space is one contiguous gap in the middle. Adding a new tuple is O(1) (consume from both ends), and variable-length tuples never need to be packed because their location is found via the slot pointer, not arithmetic on a fixed row width.
-
Name three production databases that use B+ trees and three that use LSM-trees, and one example each of when you would pick each in Bangladesh.তিনটি B+ tree-ভিত্তিক ও তিনটি LSM-ভিত্তিক production database-এর নাম বলুন; বাংলাদেশের context-এ কোনটিতে কোনটি লাগবে — একটি করে উদাহরণ দিন।
✨ Show Answer
Answer:
- B+ tree: SQLite, PostgreSQL, MySQL InnoDB. Use case: bKash transaction ledger — needs strong consistency and read-mostly point lookups by transaction-id.
- LSM-tree: Cassandra, RocksDB, ScyllaDB. Use case: Pathao ride-event stream — millions of events per minute, mostly time-ordered writes, range scans by driver-id over a recent window.
Summary — Module 36
Beneath every SQL query lies a stack of physical structures. Tables live as fixed-size pages; tuples are placed using a slot directory; pages are cached in a buffer pool whose hit rate dictates throughput. Tables are organized either as heaps with side indexes, or clustered as a single B+ tree. The B+ tree — high fanout, balanced, leaf-linked — is the default index of every classical RDBMS, while the LSM-tree dominates write-heavy and distributed systems. Knowing this layer lets you read execution plans, predict performance, and explain why two seemingly identical queries can differ by a thousand-fold.