--- title: 'Partitioning and File Layout' source: 'https://academia.sh/en/courses/data-storage-architectures/partitioning-and-file-layout' course: 'Data Storage Architectures' language: en updated: '2026-10-21T05:02:59+00:00' license: 'CC BY-SA 4.0' objectives: - 'Measures on four layouts that a field in the partition key prunes files and a field outside the key does not.' - 'Calculates how the fixed share of the cost grows while the cells read stay constant as the partition gets finer, and defines the small file problem.' - 'Shows that the cheapest layout changes under different weights of the same query mix.' --- # Partitioning and File Layout Counting what the choice of partition key and the fineness of the partition make a query pay: four layouts give exactly the same answer to the same three questions (200, 1122 and 46 rows) but their costs part ways. When the filter falls outside the partition key no file can be pruned, and the cost of 4520 in the unpartitioned layout rises to 11000 in the period plus region layout; the whole of the difference is the per-file constant paid fifty-four times more. Even on a pruning query the fineness carries the cost from 520 to 1000 and to 1600, while the cells read stay at 400 in all three layouts: this is the small file problem, and while the fixed share of the cost rises from 120 to 600 and to 1200, the cell share does not change at all. In three different mixes of the same ten queries the cheapest layout turns out to be three different layouts, so the key is chosen by looking at the query mix and not at the data. All the numbers of the previous lesson were measured **inside a single file**. The partition key was held fixed as `period`, the filter always fell on that key and every query touched exactly one file. For this reason the per-file constant entered no difference at all and the gain of the columnar read looked clean. The key itself was never questioned. This lesson removes the constant. Two questions are answered in turn: what happens when the filter falls on a field **outside** the partition key, and where does splitting the same data into more files move the cost, even if the cells read do not rise at all. The answer to the second question is the clearest example of the second claim of this course. - **MF49.** The source, the consumption rows and the file layout are **fictional** and are produced inside the lesson. No warehouse, lake, file format or query engine is called. The seed is 20260218. - **MF50.** **Partitioning** is distributing rows to separate **partitions** by the value of a key; in this model each partition is **a single file** and the two words are used as synonyms. The partitioning procedure was measured in the Relational Database Administration course in the Databases curriculum; it is **not repeated**. - **MF51.** **Sharding** is a separate concept and is **not confused** with it: sharding distributes rows to independent nodes that have their own storage. It was measured in the Scaling the Data Layer course in the System Design and Distributed Systems curriculum. In this lesson there is a single storage; what is measured is **the file layout**, not the distribution over nodes. - **MF52.** **Partition pruning**: if the field in the filter is in the partition key, the files that do not match are **never touched**. A field outside the key prunes no file and all the files are read from end to end. - **MF53.** Cost is the number of files touched times the **per-file constant of 120**, plus the cells read. The constant is a choice; most of the differences measured in this lesson are **of the same kind as the constant itself**, and every result is written together with the value of the constant. - **MF54.** The query reads two columns (`region` and `m3`); the read is columnar. The effect of column selection was measured in the previous lesson and is held fixed here. - **MF55.** The three questions are fixed: **A** asks for a single period, **B** for a single type, **C** for a single region of a single period. The correct answer to all three is independent of the layout. ## The Partition Key and Pruning The block below writes the 2200 rows coming out of the pipeline in four different layouts and asks the three questions in each layout. Next to the number of files, the row counts of the smallest and the largest file are printed as well. ```python # FICTIONAL. The meter readings of the measurement network , the consumption rows and the file # layout are produced inside the lesson; no data warehouse , data lake , file format or query # engine is called. SEED, M32 = 20260218, 0xFFFFFFFF def rng(t): x = ((t ^ (t >> 16)) * 2246822507) & M32 x = ((x ^ (x >> 13)) * 3266489909) & M32 s = [(x ^ (x >> 16)) & M32] def next_value(): s[0] = (s[0] * 1664525 + 1013904223) & M32 return s[0] / 4294967296 return next_value REGION = ["east", "west", "north", "south", "central"] TYPE = ["residential", "commercial"] PERIODS = [f"2026-{a:02d}" for a in range(1, 13)] FILE_CONSTANT = 120 # the fixed cost of touching a file , in cells SUBSCRIBERS = {} for i in range(200): r = rng(SEED + 37 * i) SUBSCRIBERS[f"A{i + 1:03d}"] = {"region": REGION[int(r() * 5)], "type": TYPE[int(r() * 2)], "start": 10000 + int(r() * 60000)} def rows(): """Consumption rows coming out of the pipeline. In a healthy source the screen and dedupe stages drop no rows , so consumption is computed directly from the source , by period difference.""" raw = [] for i, (sub, a) in enumerate(SUBSCRIBERS.items()): e = a["start"] for p in PERIODS: r = rng(SEED + 101 * i + 7 * PERIODS.index(p)) e += 4 + int(r() * 46) raw.append({"subscriber": sub, "period": p, "region": a["region"], "type": a["type"], "index": e, "unit": "m3"}) table, out = {(k["subscriber"], k["period"]): k for k in raw}, [] for k in raw: i = PERIODS.index(k["period"]) prev = table.get((k["subscriber"], PERIODS[i - 1])) if i else None if prev is not None: out.append(dict(k, m3=k["index"] - prev["index"])) return out def write(records, partitioning=()): """Distributes the rows to files by the partition key; each partition is a file.""" layout = {} for k in records: layout.setdefault(tuple(k[a] for a in partitioning), []).append(k) return layout def query(layout, partitioning, selected, filters=None): """A filter field in the partition key prunes files; a field outside the key prunes no file and has the whole file read.""" filters = {} if filters is None else filters pruning = {a: v for a, v in filters.items() if a in partitioning} remaining = {a: v for a, v in filters.items() if a not in partitioning} files, cells, matching = 0, 0, 0 for key, records in layout.items(): if pruning: pair = dict(zip(partitioning, key)) if any(pair[a] != v for a, v in pruning.items()): continue files += 1 cells += len(records) * len(selected) matching += sum(1 for k in records if all(k[a] == v for a, v in remaining.items())) return {"files": files, "cells": cells, "matching_rows": matching, "cost": files * FILE_CONSTANT + cells} SELECTED = ("region", "m3") LAYOUTS = [("unpartitioned", ()), ("period", ("period",)), ("period+region", ("period", "region")), ("period+region+type", ("period", "region", "type"))] QUESTIONS = [("A period", {"period": "2026-07"}), ("B type", {"type": "commercial"}), ("C period+region", {"period": "2026-07", "region": "north"})] ALL_ROWS = rows() print(f"pipeline rows {len(ALL_ROWS)} , selected columns {len(SELECTED)} ," f" file constant {FILE_CONSTANT} cells") print("A: period filter , B: type filter , C: period+region filter") print("\nlayout files small large" + "".join(f"{a[0] + ' files/cells/cost':>21}" for a, _ in QUESTIONS)) answer = {} for name, part in LAYOUTS: d = write(ALL_ROWS, part) size = [len(x) for x in d.values()] measured = [] for qname, f in QUESTIONS: s = query(d, part, SELECTED, f) answer.setdefault(qname, set()).add(s["matching_rows"]) measured.append(f"{s['files']}/{s['cells']}/{s['cost']}") print(f"{name:<19} {len(d):>5} {min(size):>5} {max(size):>5}" + "".join(f"{x:>21}" for x in measured)) print("\nmatching rows , in all four layouts: " + " , ".join(f"{a[0]} {sorted(answer[a])[0]}" for a, _ in QUESTIONS)) ``` ``` pipeline rows 2200 , selected columns 2 , file constant 120 cells A: period filter , B: type filter , C: period+region filter layout files small large A files/cells/cost B files/cells/cost C files/cells/cost unpartitioned 1 2200 2200 1/4400/4520 1/4400/4520 1/4400/4520 period 11 200 200 1/400/520 11/4400/5720 1/400/520 period+region 55 31 49 5/400/1000 55/4400/11000 1/92/212 period+region+type 110 12 28 10/400/1600 55/2244/8844 2/92/332 matching rows , in all four layouts: A 200 , B 1122 , C 46 ``` The last line is the precondition of the whole table: all four layouts give **exactly the same** answer to all three questions, 200, 1122 and 46 rows. What is measured is not correctness but what the same answer costs. The first row gives the floor. The unpartitioned layout has a single file, every question reads it from end to end and the cost is **4520** on all three questions. This layout is **indifferent** to the question; it neither rewards nor punishes. Question A is the promise of partitioning. In the `period` layout the filter falls on the key, ten files are never opened and the cells read fall **from 4400 to 400**; the cost drops **from 4520 to 520**. Up to here partitioning pays off at every scale. Question B is the limit of the promise and the clearest example of the **second claim** of this course. The filter falls on the `type` field, `type` is not among the first two keys of any layout, so **there is no pruning** and all the files are read. The cells read are **4400** in three layouts; the only thing that changes is the number of files: 1, 11 and 55. The cost becomes **4520**, **5720** and **11000**. The **6480** difference between layouts that read the same cells and return the same 1122 rows is nothing other than the per-file constant being paid **54 times more**. Partitioning has made the work worse here. The break-even point of this row can also be calculated and it is surprisingly low. Without pruning the cells read are 4400 regardless of the number of files, so the cost grows linearly with the number of files and equals the 4520 of the unpartitioned layout only at **a single file**. For a filter outside the key, **more than one partition is always a loss**; not even the size of the loss, but its existence, is open to debate. The fourth row is the first fix that comes to mind: `type` is put in the key as well. Question B now prunes, the files fall to 55 and the cells drop to **2244**. The cost, however, is **8844** and is still nearly twice that of the unpartitioned layout. The 2156 units gained in cells do not cover the 6600 units that are the fixed share of fifty-five files. The price of adding a column to the key for one query is **110 files**, and that price is reflected on all the other queries as well. ## The Small File Problem The second example of the second claim is seen where pruning **works**. Question A prunes in all three partitioned layouts; the block below splits the cost into two parts. ```python # This block continues the rows() , write() , query() , SELECTED , ALL_ROWS , LAYOUTS and # QUESTIONS definitions of the first block. print("Question A (pruning filter): fixed share and cell share of the cost") print("\nlayout touched files fixed share cell share cost") for name, part in LAYOUTS: s = query(write(ALL_ROWS, part), part, SELECTED, {"period": "2026-07"}) print(f"{name:<19} {s['files']:>15} {s['files'] * FILE_CONSTANT:>11}" f" {s['cells']:>11} {s['cost']:>7}") MIXES = [("A-heavy", (8, 0, 2)), ("C-heavy", (2, 0, 8)), ("B-heavy", (1, 8, 1))] print("\ntotal cost of the ten-query mix") print("\nmix (A,B,C) " + "".join(f"{a:>20}" for a, _ in LAYOUTS) + " cheapest") for mname, weight in MIXES: total = [] for name, part in LAYOUTS: d = write(ALL_ROWS, part) total.append(sum(n * query(d, part, SELECTED, f)["cost"] for n, (_, f) in zip(weight, QUESTIONS))) print(f"{mname:<19} " + "".join(f"{t:>20}" for t in total) + f" {LAYOUTS[total.index(min(total))][0]}") ``` ``` Question A (pruning filter): fixed share and cell share of the cost layout touched files fixed share cell share cost unpartitioned 1 120 4400 4520 period 1 120 400 520 period+region 5 600 400 1000 period+region+type 10 1200 400 1600 total cost of the ten-query mix mix (A,B,C) unpartitioned period period+region period+region+type cheapest A-heavy 45200 5200 8424 13464 period C-heavy 45200 5200 3696 5856 period+region B-heavy 45200 46800 89212 72684 unpartitioned ``` The cell share column is the unread finding of the first table: **the cell share is 400 in all three partitioned layouts.** The 200 rows the query needs neither rise nor fall; finer partitioning merely distributes them to more files. With no growth at all in the data read, the cost rises **from 520 to 1600**, that is, triples, and the whole of the increase is in the fixed share: **120, 600, 1200**. **The small file problem is this**: while the data a query reads does not change, the number of files opened to reach that data grows. The dependence of the result on the constant is written openly here. The difference is entirely of the kind of the **per-file constant of 120**; had the constant been 60, the costs of the three layouts would have been 460, 700 and 1000, and had it been 480, they would have been 880, 2800 and 5200. The order does not change, because as long as the constant is positive, more files cost more; what changes is the **magnitude**. The sentence "fine partitioning is expensive" is not measured unless the value of the constant is written. What the constant stands for has to be said here. Touching a file has a cost independent of its row count: finding the file, opening it, reading its header and working out which column sits where. This work is the same whether the file carries twelve rows or two thousand two hundred. All the model does is write that cost **in cells**; the number 120 is not a measurement but a stand-in given to reduce the measure to a single unit. As the number of partitions grows, the files also become **unequal**. The small and large columns of the first table give this: in the 55-file layout the files are between 31 and 49 rows, in the 110-file layout between 12 and 28. Because subscribers are not evenly distributed over regions, the averages of 40 and 20 rows describe no file; the fixed cost, however, is the same for the smallest file and for the largest. The two files at the extremes make this plain: in the 110-file layout the smallest file has two columns read for **24 cells**, and **more than four fifths** of the cost of touching that file is the constant; the single file of the unpartitioned layout has 4400 cells read and the share of the constant stays under three percent. What partitioning makes you pay is dividing the constant over fewer and fewer rows. ## The Key Is Chosen for a Query Mix The second table gives the total cost of three different mixes of ten queries and carries the decision of the lesson here. In the A-heavy mix the cheapest layout is `period`, in the C-heavy mix `period`+`region`, and in the B-heavy mix the **unpartitioned** layout. Three mixes, three winners. The middle row says the opposite of the one before it. Because query C applies a filter to both the period and the region, it goes down to a single file in the `period`+`region` layout and gives **92 cells / a cost of 212**; the same query reads 400 cells in the `period` layout. The small file problem is absent in this query, because the fineness **overlaps** with the filter of the query. The same 55-file layout is a penalty in query A and a reward in query C. The last row is the harshest: in a workload that uses only filters outside the key, not partitioning at all is the cheapest decision, 45200 against 46800, 89212 and 72684. Partitioning is a price that is paid when the query has not been written but for which nothing is received in return. These three rows are the definition of the rule of the course. A layout's number is not the rows it stores but what a query pays to touch it; **a layout whose query is not written down as the one it was chosen for has not been measured.** A partition key cannot be chosen by looking at the data, because all four layouts store the same data and give the same answer. The choice is made only by looking at the query mix, and when the mix changes the decision is measured again. ## Summary - If the field in the filter is in the partition key, the files that do not match are never touched: query A reads 4400 cells in the unpartitioned layout and 400 in the `period` layout, and the cost falls from 4520 to 520. - When the filter falls outside the key there is no pruning, and partitioning makes the work worse: in query B, while the cells read are 4400 in all three layouts, the cost is **4520, 5720 and 11000**; the whole of the 6480 difference is the per-file constant paid 54 times more. Adding `type` to the key pulls the cost to 8844, but at the price of 110 files. - Even on a pruning query, fineness writes a penalty: in query A the cells read are **400** in all three partitioned layouts, the cost becomes **520, 1000, 1600** and the whole of the increase is in the fixed share (120, 600, 1200). That is the small file problem. - The result is of the kind of the per-file constant of 120: at a constant of 60 the three values become 460/700/1000, and at 480 they become 880/2800/5200. The order depends on the sign of the constant, the magnitude on its value. - The four layouts give the same answer to all three questions (200, 1122, 46 rows); in the three mixes of ten queries the cheapest layout comes out as `period`, `period`+`region` and unpartitioned respectively. The key is chosen by looking at the query mix and not at the data. ## Next Step In all the layouts of this lesson, all twelve periods stood in the same place. No row was deleted, no period was moved to another tier; the oldest period could be read at the same price as the newest. Yet the per-file constant is the same for every file, and the number of files grows with the period. The last lesson turns this accumulation into a decision: when the old periods are taken out of the hot tier, how many files and how much cost remain in the hot tier, what does reading from the archive cost, and **which question can no longer be answered** retroactively.