B-trees - Secondary Storage#
Announcements#
Hw#6 is due this thursday
There are partial answers to test with
Make sure your code runs correctly and can be tested as illustrated
Lecture Exercise 21 is available after class, due on Thursday at 2pm
Primary storage#
The way the relation is stored on disk
Heap/no actual order, stored in the way it is inserted
Each disk page contains a number of tuples.
Alternative methods?
Sorted by some attribute
Hashed on some attribute
Assume no order
Suppose a disk page is 8KB, suppose 8000K bytes of addressable space
Suppose I want to store movies relation
Each tuple: int -> 4 bytes, string -> variable (avg?), etc.
– Query average tuple size
SELECT
AVG(pg_column_size(t)) AS avg_tuple_size_bytes
FROM your_table t;
SELECT
AVG(pg_column_size(t)) AS avg_tuple_size_bytes
FROM movies t;
For movies, we find it is 112 bytes
In each page can store up to 8000/112 = 70 tuples
How many pages does this relation take?
4708 tuples, 4708/70 = 68 pages long.
— Actual storage:
SELECT
relname,
relpages AS number_of_pages,
reltuples AS estimated_number_of_rows
FROM pg_class
WHERE relname = 'your_table';
SELECT
relname,
relpages AS number_of_pages,
reltuples AS estimated_number_of_rows
FROM pg_class
WHERE relname = 'movies';
– Actual: 70 pages.
Now back to primary storage, suppose:
select * from movies
where movieid = 12345;
Primary access method: Read every single tuple and check if movieid = 12345, and if so, output. (sequential scan)
Cost: read 70 pages
Suppose a disk read takes 10 ms, so I will spend 700 ms to answer this query.
Secondary access method#
Create an index on a search key (movieid)
A b-tree node can store for each tuple:
Index tuple header 8 bytes
Key value: (movieid Integer: 4 bytes)
Tuple pointer 4 bytes
+--------
16 bytes
An index node (disk page) can store , 8000/16 = 500 tuples
Relation has: 4708 tuples/500 = 10 pages at the leaf level
One root level
To search for:
select * from movies
where movieid = 12345;
using the index on movieid as a secondary access method:
Search the index: read root node, and one leaf node to find the node containing link to tuple with movieid=1245;
Read the tuple from disk
Total pages to read: 3 ( so 30 ms)

B-trees#
Each index node is stored in a disk page
A B-tree is of order n, meaning maximum number of entries you can store in a node
Each node (except for root) should have between (n+1)/2 and n entries
Each node is between full and half full
Root can have 2 to n entries
More accurately:
An internal node can contain at most n+1 pointers and n key values.
An internal node must contain at least floor((n+1)/2) pointers (and one less key value), except the root which can contain a single key value and 2 pointers.
An entry: a key value and a pointer a disk page
Leaf nodes: contain an entry for each tuple, key value for that tuple and a pointer to where the tuple is stored. (leaf nodes are sorted by the key value)
Each leaf node contains a pointer to the next node in the leaf (sibling pointer)
Internal nodes: contain key values that are decision points for the index nodes below and pointers to the nodes below.
SEARCH:
Equality search: key = value
Start from leaf, and scan one node for each level
Range search: c1 <= key <= c2
Find the first leaf node with key >=c1
Scan leaf nodes to the left using sibling pointers until we reach a vlue greater than c2.

INSERTION:
Search for the value to be inserted (where it would go)
Insert and if the node is full, split and insert the key value for the new node the internal node above recursively

DELETION:
Search for the value to be inserted (where it would go)
Delete the entry, and if the node is less than full


