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)

Primary vs. Secondary Storage

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.

Searching B-tree

  • 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

Insertion in a B-tree

  • DELETION:

    • Search for the value to be inserted (where it would go)

    • Delete the entry, and if the node is less than full

Deletion in B-trees

Indexing key values that are not unique

Indexing multiple attributes