Partitioning

What is partitioning?

Partitioning splits a large table into smaller physical parts based on the value of a column. A sales table might be partitioned by month. A multi-tenant system might partition by tenant. A logistics table might partition by region.

The benefit is query skipping. If a report asks only for March 2026, the query engine can read the March partition and skip the rest. That skipping is called partition pruning.

Partitioning is common in data warehouses, lakehouses, big SQL databases, and file-based tables on object storage. The details differ, but the goal is the same: read less data for the queries people actually run.

How partitioning looks on storage

In file-based lakehouse tables, partitioning often appears as folders. A table partitioned by month may create paths such as:

• sales/month=2026-01/

• sales/month=2026-02/

• sales/month=2026-03/

Formats and engines such as Parquet, Hive-style tables, Spark, Delta Lake, and many lakehouse tools can use those folder values to skip files. Delta Lake and similar table formats also track files and partitions in their transaction log or metadata.

In database systems, partitioning may be implemented inside the table and index structures rather than as visible folders. SQL Server, PostgreSQL, Oracle, BigQuery, Snowflake, and others each have their own partitioning features.

What partitioning gives you

Faster queries. If the query filters on the partition column, the engine reads fewer files, blocks, or partitions.

Lower scan cost. In cloud systems that charge by bytes scanned, pruning partitions can reduce the bill.

Simpler retention. Dropping old data can be much easier when each month or year is its own partition.

Operational control. Large loads, rebuilds, and maintenance jobs can sometimes run per partition instead of touching the whole table.

Partitioning helps only when it matches the way the data is queried or managed. A partition column that nobody filters on adds overhead without much gain.

Choosing a partition column

Start from query behaviour, not from what looks tidy in the source system. Ask which filters appear in most important queries.

Date is the usual answer for fact tables: order date, invoice month, event date, booking day. It supports common filters and makes retention easier.

Region, country, tenant, or business unit can work when users nearly always filter that way and the number of values stays manageable.

Avoid high-cardinality columns such as customer id, order id, exact timestamp, email address, or transaction id. They create too many tiny partitions. Opening thousands of small files or partitions can be slower than scanning a larger, simpler layout.

Also avoid partitioning tables that are too small. The overhead may be larger than the saving. Many modern lakehouse platforms now prefer automatic clustering, liquid clustering, or file statistics for medium-sized tables instead of manual partitioning everywhere.

Partitioning, clustering, and Z-ordering

Partitioning is a coarse layout choice. It splits data into separate parts by one or more columns.

Clustering sorts or groups data inside files or storage blocks so related values sit close together. The engine can then use file statistics, min and max values, or indexes to skip chunks without creating a folder for every value.

Z-ordering is a multi-column clustering technique used in some lakehouse systems. It tries to keep records close together across several columns that are often filtered together.

Databricks now recommends liquid clustering for many Delta tables because it can adapt data layout without the same manual partition decisions. BigQuery clustering, Snowflake clustering, and SQL Server indexes solve similar problems in their own ecosystems.

The design question is practical: which layout lets your engine skip the most irrelevant data with the least maintenance?

Example: sales by month

Suppose a sales table has five years of daily transactions and most reports filter on a month or recent quarter. Partitioning by sales month means the report for March can skip the other fifty-nine months.

If another report asks for total revenue by product category across all years, month partitioning does not help. The query needs every month. A cluster or index on product category may be a better fit for that access pattern.

This is why teams often combine layouts: partition by date for retention and recent-period filters, then cluster or index by the columns used inside those periods.

What to watch out for with partitioning

Too many small partitions. A folder per customer or second can create thousands of tiny files. Metadata and file-opening overhead then dominate the query.

Wrong partition column. If dashboards never filter by region, region partitioning will not speed them up.

Changing the design is expensive. Repartitioning a large table usually means rewriting the data.

Partitioning is not a modelling fix. It improves physical layout. It does not replace a good star schema, clean filters, or sensible aggregation tables.