Partitioning

Partitioning divides a table into separate physical segments based on the value of one or more columns — most commonly a date, so all of a given day's or month's rows are stored together as one partition. A query that filters on the partitioning column can then skip entire partitions that cannot contain matching rows, without scanning them at all.

This is distinct from indexing: an index points to specific rows within otherwise unchanged storage, while partitioning physically separates data into distinct files or directories, which also makes it easier to load, delete, or expire a whole partition, such as dropping a month of old data, as a single fast operation instead of a row-by-row delete. In massively parallel processing systems, partitioning can also determine which compute node holds which data.

Good partitioning is one of the most effective query optimization levers available, because skipping unneeded data is cheaper than reading and filtering it; the common pitfall is over-partitioning — choosing a column with too many distinct values, which creates a huge number of tiny partitions and slows queries down instead of speeding them up. Modern open table formats like Apache Iceberg support partition evolution, letting a table's partitioning strategy change over time without rewriting historical data.