# Partitioning

> Partitioning splits a table into parts by the value of a column, most often a date, so queries and writes touch only the parts they need.

Partitioning splits a table into parts by the value of a column, most often a date, so queries and writes can touch only the parts they need. It makes [backfills](https://emolysis.com/glossary/backfill) and late-data fixes cheaper, because a single day can be replaced without rewriting the table. Too many small partitions slow things down, so the column must suit the data.

For example, a `page_views` table partitioned by `view_date` stores each day's rows in its own partition. A query for last week reads seven partitions and skips the other three years. A late fix to March 3 deletes and rewrites only that partition. Without partitioning, both operations would scan or rewrite the whole table, which for a large table can take hours and a lot of compute.

The usual guidance is to partition only large tables, by a column with a moderate number of values that both queries and loads filter on, and to aim for partitions of about a gigabyte or more. Partitioning a small table, or partitioning by a column such as a customer id with millions of values, creates many tiny files, and the time spent listing and opening them outweighs any skipping. On Databricks, liquid [clustering](https://emolysis.com/glossary/clustering) is now often recommended instead of partitioning for new tables, and Snowflake manages micro-partitions automatically, so explicit partitioning matters most for large date-based tables and for file-based formats.

Partitioning also defines a unit of work: [backfills](https://emolysis.com/glossary/backfill), retention rules that drop old data and fixes for [late-arriving records](https://emolysis.com/glossary/late-arriving-data) all operate one partition at a time. See also [Apache Iceberg](https://emolysis.com/glossary/iceberg), whose hidden partitioning lets queries filter on the original column.

A partition column should be chosen once and rarely changed, because changing it means rewriting the whole table in most formats. When in doubt for a new table, start without partitions and add clustering, then partition only if the table grows large and every query and load filters by date. See also [incremental load](https://emolysis.com/glossary/incremental-load).

Emolysis's [Backfill](https://emolysis.com/shapes/backfill) and [Late-arriving](https://emolysis.com/shapes/late-arriving) shapes replace exactly the partitions in their range. Compare [clustering](https://emolysis.com/glossary/clustering). Partition by a column that both queries and loads filter on; a date is usually that column.

---

Page: https://emolysis.com/glossary/partitioning. Emolysis is a product of Bandpey GmbH, Munich, Germany. It is in private development, and nothing is released yet.