{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1dad87fb-fd19-460e-a741-39193459d301",
   "metadata": {},
   "source": [
    "## Lecture 19: Hard disk - The Final Frontier \n",
    "\n",
    "### Indexing\n",
    "\n",
    "### Announcements\n",
    "\n",
    "- The comprehensive **Final Exam** is going to be on\n",
    "  - December 16th, at 11:30AM in Sage 3303 (this room)\n",
    "  \n",
    "- Hw#6 is out, due on November 19th.\n",
    "  - Please read carefully and test extensively before submitting.\n",
    "\n",
    "- No lecture exercise today! Lecture exercises will resume next week.      \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01d4cc54-53a5-4a37-b92a-2f54fffab0fa",
   "metadata": {},
   "source": [
    "- Data is stored on hard disk, read to memory for any operation.\n",
    "\n",
    "- Hard disk is permanent storage, while memory is volatile.               \n",
    "- Types of hard disk:\n",
    "  - Magnetic disks: slower but cheaper\n",
    "  - Solid state: faster but more expensive\n",
    "\n",
    "\n",
    "Magnetic disk read:\n",
    "- To read a single page: seek time (to move the read head) + rotational latency (wait for the disk to spin to the correct track) + transfer time\n",
    "\n",
    "-> **RANDOM IO** to read K random pages (in different parts of the disk):\n",
    "   K * (seek + rl + tt)\n",
    "\n",
    "-> **SEQUENTIAL IO** to read K pages on the same track \n",
    "   seek + rl + K*tt\n",
    "\n",
    "**Table: Performance Characteristics of Fast Magnetic Disks**\n",
    "\n",
    "| Disk Drive | Seek Time (ms) | Average Rotational Latency (ms) | Transfer Rate (MB/s) |\n",
    "|---|---:|---:|---:|\n",
    "| Seagate Exos 32TB | 4–9 | 4.16 | 285 |\n",
    "| Seagate Exos X16 16TB | 4–9 | 4.16 | 261 |\n",
    "| WD Ultrastar 7K6000 | 7.6–8 | 4.16 | Varies |\n",
    "| HPE 26TB Enterprise HDD | 4–9 | 4.16 | 278 |\n",
    "                                                                                 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d49d11ca-482f-45d6-a2a3-475dfb8242c3",
   "metadata": {},
   "source": [
    "- Solid state hard drives do not have moving parts, hence reads are much faster.\n",
    "- Virtual machines can be much slower due to the additional overhead of the virtualization.\n",
    "\n",
    "Example:\n",
    "\n",
    "My laptop (solid state drive): \n",
    "\n",
    "min/avg/max/mdev = 29 us / 32.6 us / 35 us / 2.58 us\n",
    "\n",
    "Magnetic drive\n",
    "\n",
    "min/avg/max/mdev = 157.2 us / 166.9 us / 183.5 us / 8.78 us\n",
    "\n",
    "Virtual machine:\n",
    "\n",
    "min/avg/max/mdev = 481.5 us / 586.4 us / 652.8 us / 56.5 us\n",
    "\n",
    "\n",
    "### Table: Approximate Time to Read a 4 KB Disk Page\n",
    "\n",
    "| Performance Measure | HDD (Random Access) | HDD (Sequential Access) | SATA SSD |\n",
    "|---|---:|---:|---:|\n",
    "| Average seek time | 5 ms | ~0 ms* | Negligible |\n",
    "| Average rotational latency | 4.17 ms | ~0 ms* | None |\n",
    "| 4 KB data transfer time | 0.015 ms | 0.015 ms | 0.03 ms |\n",
    "| **Total read time** | **~9.2 ms** | **~0.015 ms** | **~0.05–0.15 ms** |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dcdf9d3-65ac-4a1b-a5c6-ae1ad30bf251",
   "metadata": {},
   "source": [
    "### Cost to storage capacity comparison\n",
    "\n",
    "| Storage Capacity | Magnetic HDD Price | SATA SSD Price | NVMe SSD Price |\n",
    "|---:|---:|---:|---:|\n",
    "| 500 GB | $30–$50 | $50–$100 | — |\n",
    "| 1 TB | $40–$80 | $100–$180 | $120–$190 |\n",
    "| 2 TB | $60–$120 | $200–$300 | $240–$350 |\n",
    "| 4 TB | $100–$200 | $400–$700 | $450–$700 |\n",
    "| 8 TB | $200–$350 | $1,000+   | $1,000+ |\n",
    "| 12–24 TB | $300–$700+ | Limited availability | Limited availability |\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99dad985-8458-467c-a44e-1c84b60f6ee9",
   "metadata": {},
   "source": [
    "```\n",
    "select * from bakers order by baker;\n",
    "```\n",
    "\n",
    "Assume:\n",
    "- The whole relation is stored in a single page:   10 ms\n",
    "- Assume that there are 4,000 values to sort: \n",
    "\n",
    "Sorting 4,000 integers\n",
    "\n",
    "Quicksort / Introsort\t5–30 micro seconds  \n",
    "Mergesort\t5–40 micro seconds  \n",
    "Heapsort\t10–50 micro seconds  \n",
    "Bubble sort\t1–5 ms  \n",
    "\n",
    "Reading the page is generally much slower than sorting. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a32493a6-6ac6-400b-92dc-40ecb181d8f1",
   "metadata": {},
   "source": [
    "**Reliability of different storage technologies**\n",
    "\n",
    "Magnetic hard disk (HDD)\n",
    "- Contains moving mechanical parts.\n",
    "- Subject to mechanical wear, head crashes, and motor failures.\n",
    "- Can be affected by vibration and physical shock.\n",
    "- Suitable for long-term, high-capacity storage.\n",
    "\n",
    "\n",
    "SATA SSD\n",
    "- Has no moving mechanical parts.\n",
    "- Resistant to mechanical shock and vibration.\n",
    "- Flash memory has finite write endurance.\n",
    "- Controller or electronic failures can occur.\n",
    "\n",
    "NVMe SSD\n",
    "- Also uses flash memory, like SATA SSDs.\n",
    "- No moving mechanical components.\n",
    "- Can experience high temperatures under heavy workloads.\n",
    "- Reliability depends on NAND quality, controller, firmware, and write endurance.\n",
    "\n",
    "\n",
    "RAID Array: Redundant Array of Inexpensive Disks  \n",
    "    \n",
    "![RAID array examples](lecture19_figures/RAID.png)    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dc6aeda-c4ad-4bb3-b175-e707185c06d4",
   "metadata": {},
   "source": [
    "A disk page in the database stores multiple tuples.\n",
    "\n",
    "![Postgresql disk page](lecture19_figures/postgresql_heap_page_layout.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebeac0b3-09da-42be-8f7d-e3ef7f74272d",
   "metadata": {},
   "source": [
    "**Cost of most operations in a database are measured in terms of the number of disk pages read/written.** \n",
    "\n",
    "- A disk page is the smallest unit of data that can be read/written to disk. (4K or 8K)\n",
    "\n",
    "- Whenever a disk page is read into memory, it is put into a memory block. Assume for now a block is the same size as a page.\n",
    "\n",
    "- We will use block and page interchangeably.\n",
    "\n",
    "- To READ (select): you must read data to memory.\n",
    "\n",
    "- To UPDATE/Change: you must read data to memory, change it in memory and write back to disk.\n",
    "\n",
    "- So, the cost a query:\n",
    "\n",
    "```select * from tableX; ```\n",
    "\n",
    "- is the cost of reading all the disk pages for the relation tableX: Sequential scan. \n",
    "\n",
    "- A single page stores many tuples (depending on the size of the attributes we store)\n",
    "\n",
    "- Number of pages depend on the size of a tuple and the number of tuples. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5ec90db-ad47-43c6-8a73-236cc54a0c5a",
   "metadata": {},
   "source": [
    "## Indexing as a way to speed up queries\n",
    "\n",
    "```\n",
    "select \n",
    "    actorid\n",
    "from\n",
    "    actors\n",
    "where \n",
    "    name = 'PS' ;\n",
    "```\n",
    "\n",
    "- Create a look up structure for the name attribute, to find where a tuple\n",
    "is stored for a specific name\n",
    "\n",
    "- Index on a  key value (name) and a pointer to the tuple (page number for that tuple)\n",
    "                                                           "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7c6fa00-1557-40f3-9589-685b31155c0e",
   "metadata": {},
   "source": [
    "Actors relation has 149K tuples and 1088 pages.\n",
    "\n",
    "Create an index on actorid, integer = 4 bytes\n",
    "\n",
    "Create an entry for each tuple: integer(actorid) a tuple address (12 bytes)  = 16 bytes per tuple\n",
    "\n",
    "- Each index page stored in an 8K disk page can index up to  8K/(16) = 512 tuples \n",
    "\n",
    "- So the leaf node of my index takes up (149000/512): 292 pages\n",
    "\n",
    "- The next level points to the leaf nodes, still can index 512 tuples at most.\n",
    "\n",
    "B-trees - stores B entries in each node/disk page"
   ]
  }
 ],
 "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
}
