4 — Databases, Storage & the Data Layer

Databases and the Data Layer 1 D A T A L A Y E R M A S T E R C L A S SDatabases and the Data Layer: Relational, Document and Vector Supplementary study note. Modelling,indexing, transactions, engines, vector search and the operational layeraround them. Md Ahbab Hamid Khan https://ahbab.dev/ This note does not repeat the talk. You already have the slides. What followsis the material that does not fit on a slide: the arithmetic, the mechanisms,the failure modes and the trade off tables. Read it with the slides beside you.Every technical term is explained the first time it appears, in one clause.All numbers in the charts are illustrative orders of magnitude, not measuredbenchmarks, and the captions repeat that warning where it matters. 1 Five numbers that decide most of your design Every decision in this note is really a decision about where a byte sits and howfar the processor must travel to reach it. Figure 1 puts thosedistances on one scale. Learn it once and reuse it forever.A 1 ns 100 ns 10 us 1 ms 100 ms 10 s cross region hop spinning disk seek NVMe page read main memory read L1 cache hit about 100 ms about 10 ms about 100 us about 100 ns about 1 ns time for one access, log scal

Databases and the Data Layer 2 inthe query text. 2 What the planner is actually pricing The planner does not know your data. It knows a sample of it. From that sampleit estimates selectivity, the fraction of rows a condition keeps, then itturns that fraction into a price using a few constants. PostgreSQL ships withseq_page_cost 1.0, random_page_cost 4.0 andcpu_tuple_cost 0.01. Those numbers say that a random page fetch is worthfour sequential ones, and that touching a row in memory is worth one hundredthof a page read.Work the example. The table has 1,000,000 rows in 25,000 pages. • Sequential scan: 25,000 × 1.0 + 1,000,000 × 0.01 = 35,000 cost units. This price is flat. It does not care what you filter on. • Index scan returning a fraction f of the table, with the rows scattered: about f × 1,000,000 × (4.0 + 0.01) cost units, plus three or four page reads to walk down the tree. Set the two equal and you get f ≈0.0087. So at roughly nine rows inevery thousand, the sequential scan wins, and it keeps winning for anythinglarger. Figure 2 shows the crossing. This is why a perfectlygood index sits unused when your WHERE clause matches 5 percent of thetable. The planner is not ignoring t

Databases and the Data Layer 3 alignmentand slot overhead. Divide: about 500 entries per page. That number is thefan-out.Now count what each level can address, as shown in Figure 3. Oneroot page points at 500 pages. Those point at 250,000 pages. Those point at125,000,000 entries. So a table of 100 million rows needs three index levels,and a lookup costs three page reads to walk down plus one more to fetch the rowfrom the heap, the file where the table itself lives. Four page reads.The payoff is in how slowly that grows. Multiply the table by 500 and you addone level, so one page read. Depth is log500 N. In practice the root andmost of the second level live in memory permanently, because together they areonly about 4 MB, so the real cost of a point lookup on a warm database is oftenone or two storage reads, not four.The dashed line between leaves matters as much as the tree above it. Leaf pagesare linked in root internal internal internal leaf leaf leaf leaf leaf heap page with the row 1 page 500 pages 250,000 pages up to 125 million rows addressed fan-out 500 Figure 3: Depth grows like log500 N, so multiplying the row count by fivehundred adds only one page read. The dashed line is