{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c4a8c63b-9aff-49c0-bd34-f852e351ea1b",
   "metadata": {},
   "source": [
    "\n",
    "## B-trees - Secondary Storage\n",
    "\n",
    "\n",
    "### Announcements\n",
    "\n",
    "- Hw#6 is due this thursday\n",
    "  - There are partial answers to test with\n",
    "  - Make sure your code runs correctly and can be tested as illustrated\n",
    "\n",
    "- Lecture Exercise 21 is available after class, due on Thursday at 2pm\n",
    "\n",
    "### Primary storage\n",
    "\n",
    "- The way the relation is stored on disk\n",
    "\n",
    "  - Heap/no actual order, stored in the way it is inserted\n",
    "  - Each disk page contains a number of tuples.\n",
    "\n",
    "- Alternative methods?\n",
    "\n",
    "  - Sorted by some attribute\n",
    "  - Hashed on some attribute\n",
    "\n",
    "\n",
    "- Assume no order\n",
    "\n",
    "- Suppose a disk page is 8KB, suppose 8000K bytes of addressable space\n",
    "\n",
    "- Suppose I want to store movies relation\n",
    "\n",
    "  Each tuple:    int -> 4 bytes,    string -> variable (avg?),   etc.\n",
    "\n",
    "-- Query average tuple size\n",
    "\n",
    "```\n",
    "\n",
    "SELECT\n",
    "    AVG(pg_column_size(t)) AS avg_tuple_size_bytes\n",
    "FROM your_table t;\n",
    "\n",
    "\n",
    "SELECT\n",
    "    AVG(pg_column_size(t)) AS avg_tuple_size_bytes\n",
    "FROM movies t;\n",
    "\n",
    "```\n",
    "\n",
    "- For movies, we find it is 112 bytes\n",
    "\n",
    "- In each page can store up to 8000/112 = 70 tuples\n",
    "\n",
    "- How many pages does this relation take?\n",
    "\n",
    "  - 4708 tuples, 4708/70 = 68 pages long.\n",
    "\n",
    "\n",
    "--- Actual storage:\n",
    "\n",
    "```\n",
    "\n",
    "SELECT\n",
    "    relname,\n",
    "    relpages AS number_of_pages,\n",
    "    reltuples AS estimated_number_of_rows\n",
    "FROM pg_class\n",
    "WHERE relname = 'your_table';\n",
    "\n",
    "\n",
    "SELECT\n",
    "    relname,\n",
    "    relpages AS number_of_pages,\n",
    "    reltuples AS estimated_number_of_rows\n",
    "FROM pg_class\n",
    "WHERE relname = 'movies';\n",
    "\n",
    "```\n",
    "\n",
    "-- Actual: 70 pages.\n",
    "\n",
    "- Now back to primary storage, suppose:\n",
    "\n",
    "```\n",
    "select * from movies\n",
    "where movieid = 12345;\n",
    "\n",
    "```\n",
    "\n",
    "- Primary access method: Read every single tuple and check if\n",
    "movieid = 12345, and if so, output. (sequential scan)\n",
    "\n",
    "  - Cost: read 70 pages\n",
    "  - Suppose a disk read takes 10 ms, so I will spend 700 ms\n",
    "    to answer this query.\n",
    "  \n",
    "### Secondary access method\n",
    "\n",
    "- Create an index on a search key (movieid)\n",
    "\n",
    "\n",
    "- A b-tree node can store for each tuple:\n",
    "\n",
    "```\n",
    "\n",
    "Index tuple header\t8 bytes\n",
    "Key value: (movieid Integer:\t4 bytes)\n",
    "Tuple pointer\t4 bytes\n",
    "            +--------\n",
    "\t       16 bytes\n",
    "\n",
    "```\n",
    "\n",
    "- An index node (disk page) can store , 8000/16 = 500 tuples\n",
    "\n",
    "- Relation has: 4708 tuples/500 = 10 pages at the leaf level\n",
    "\n",
    "- One root level\n",
    "\n",
    "- To search for:\n",
    "\n",
    "\n",
    "```\n",
    "select * from movies\n",
    "where movieid = 12345;\n",
    "\n",
    "```\n",
    "\n",
    "- using the index on movieid as a secondary access method:\n",
    "\n",
    "  - Search the index: read root node, and one leaf node to find the\n",
    "    node containing link to tuple with movieid=1245;\n",
    "\n",
    "  - Read the tuple from disk\n",
    "\n",
    "  - Total pages to read: 3 ( so 30 ms)\n",
    "\n",
    "![Primary vs. Secondary Storage](lecture21_figures/1.png)\n",
    "\n",
    "## B-trees\n",
    "\n",
    "- Each index node is stored in a disk page\n",
    "\n",
    "- A B-tree is of order n, meaning maximum number of entries you\n",
    "  can store in a node\n",
    "\n",
    "- Each node (except for root) should have between (n+1)/2 and n entries\n",
    "\n",
    "  - Each node is between full and half full\n",
    "\n",
    "- Root can have 2 to n entries\n",
    "\n",
    "- More accurately: \n",
    "   - An internal node can contain at most n+1 pointers and n key values.\n",
    "\n",
    "   - An internal node must contain at least floor((n+1)/2) pointers\n",
    "  (and one less key value), except the root which can contain a single\n",
    "  key value and 2 pointers.\n",
    "\n",
    "- An entry: a key value and a pointer a disk page\n",
    "\n",
    "- Leaf nodes: contain an entry for each tuple, key value for that\n",
    "  tuple and a pointer to where the tuple is stored. (leaf nodes are\n",
    "  sorted by the key value)\n",
    "\n",
    "  - Each leaf node contains a pointer to the next node in the leaf\n",
    "    (sibling pointer)\n",
    "\n",
    "- Internal nodes: contain key values that are decision points for the\n",
    "  index nodes below and pointers to the nodes below.\n",
    "\n",
    "- SEARCH:\n",
    "\n",
    "  - Equality search: key = value\n",
    "    - Start from leaf, and scan one node for each level\n",
    "\n",
    "  - Range search: c1 <= key <= c2\n",
    "    - Find the first leaf node with key >=c1\n",
    "    - Scan leaf nodes to the left using sibling pointers until we reach a vlue greater than c2.\n",
    "\n",
    "![Searching B-tree](lecture21_figures/2.png)\n",
    "\n",
    "- INSERTION:\n",
    "\n",
    "  - Search for the value to be inserted (where it would go)\n",
    "\n",
    "  - Insert and if the node is full, split and insert the key value for\n",
    "    the new node the internal node above recursively\n",
    "\n",
    "![Insertion in a B-tree](lecture21_figures/3.png)\n",
    "\n",
    "- DELETION:\n",
    "\n",
    "  - Search for the value to be inserted (where it would go)\n",
    "\n",
    "  - Delete the entry, and if the node is less than full\n",
    "\n",
    "![Deletion in B-trees](lecture21_figures/4.png)\n",
    "\n",
    "\n",
    "![Indexing key values that are not unique](lecture21_figures/5.png)\n",
    "\n",
    "![Indexing multiple attributes](lecture21_figures/6.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94c8ad30-ccaa-4c98-9a3d-647e84d26947",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
