Partitioning and Partition Evolution

## The problem partitioning solves A table on object storage is ultimately a set of immutable data files — Parquet, ORC or Avro — sitting under some prefix. Without any organising scheme, a query such as `SELECT count(*) FROM events WHERE dt = '2024-05-01'` has no way to know which files could possibly contain that day, so it must open all of them. On a table with years of history that is an enormous amount of I/O to answer a question about one day. Partitioning is the coarsest and oldest fix: physically group the files so that the value of a chosen column determines *where* a file lives. ## The Hive-style convention The convention that came out of Apache Hive, and which nearly every engine still understands, encodes the partition column and its value directly in the directory name: ``` s3://warehouse/events/ dt=2024-05-01/country=DE/part-0000.parquet dt=2024-05-01/country=US/part-0000.parquet dt=2024-05-02/country=DE/part-0000.parquet ``` Two properties matter: 1. **The partition column's value lives in the path, not in the file.** Writers usually strip it from the data itself — every row under `dt=2024-05-01` has the same value, so storing it a million times is waste. Readers reconstruct the column by parsing the directory name. 2. **The layout is a physical commitment.** The directory tree *is* the partitioning. Changing it means moving or rewriting files. ## How pruning actually happens Given `WHERE dt = '2024-05-01'`, the engine evaluates that predicate against partition values first, selects the surviving directories, and only then lists and reads files inside them. This is fundamentally cheaper than a row filter: a row filter still costs you the read. Two consequences follow directly: - A filter on a **non**-partition column prunes nothing at the partition level. It may still be helped by file-level statistics, but that is a different, finer mechanism. - Once a partition is selected, partitioning does nothing more. All of that partition's data is a candidate. ## Who knows which partitions exist In the classic Hive setup, a metastore holds the list of partitions. A directory that appears in storage but was never registered is invisible to queries until someone runs `ALTER TABLE ... ADD PARTITION` or a repair command. This is a frequent source of "the data is there but the query returns nothing". Modern table formats change this: the table's own metadata records, per data file, which partition it belongs to. Pruning becomes a filter over that metadata rather than a directory listing, no registration step is needed, and the on-disk path layout becomes a convention rather than the source of truth. ## Choosing partition columns Good partition columns are: **low cardinality** relative to the table, **present in most queries' filters**, and **evenly distributed**. Time (day, sometimes month or hour) is the archetype, because analytical queries almost always bound a time range and because old data ages out as whole partitions. The sizing rule of thumb is the one that catches people out: each partition should hold enough data to justify at least one full-sized data file. Partitioning by something with millions of distinct values produces millions of directories with a few kilobytes each, and the cost of enumerating and planning over them swamps any pruning benefit. ## What partitioning is not - It is **not an index**. Nothing is built or maintained; the layout *is* the mechanism. - It is **not sorting**. Rows within a partition are in whatever order the writer produced. - It does **not** speed up filters on other columns. - It does **not** reduce the data read once a partition is selected. ## The two failure modes to remember First, filtering on a column the partition value was *derived* from (filtering a raw timestamp when the table is partitioned by a date string) prunes nothing, because the engine does not know the two are related. Second, over-partitioning trades a scan problem for a planning-and-tiny-files problem. Both are the reason table formats moved partition tracking into metadata and made the partition layout something you can change later.