Partitioning and File Layout

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.

# 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.

# 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.