What is Partitioning?

How it works

Storage partitioning lays a table out by a column’s value: one folder per day in a data lake (dt=2026-10-05/), or one declared partition per range in PostgreSQL, BigQuery or a Hive-style table. When a query filters on the partition column, the engine prunes the partitions that cannot match and never opens their files. For a table queried mostly by recent dates, that turns a full scan into a read of a few days.

Choosing the key is the important decision:

• Pick a column that most queries filter on, usually event date.
• Keep cardinality low to moderate. Partitioning by a high-cardinality column such as user_id creates millions of tiny partitions; that is over-partitioning, and it makes every query slower.
• Aim for partitions large enough to hold reasonably sized files (hundreds of megabytes or more); otherwise you create a small-files problem.
• Use clustering, bucketing or Z-ordering, not partitioning, for high-cardinality columns.

In Spark the word has a second meaning. An in-memory partition is a chunk of a dataset that one task processes; their number sets a stage’s maximum parallelism and is controlled by file splits, spark.sql.shuffle.partitions and repartition. df.write.partitionBy("dt") controls folders on disk, not in-memory partitions, and each writing task writes one file per partition value it holds. That is why jobs often repartition("dt") before a partitioned write.

Example

Writing by date creates one folder per value, and a filter on the column becomes a partition filter, so only one folder is read (PySpark 4.2.0).

import os, re, tempfile
from pyspark.sql import SparkSession

spark = SparkSession.builder.master("local[2]").appName("glossary-partitioning").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
path = os.path.join(tempfile.mkdtemp(), "events")

df = spark.createDataFrame(
    [(1, "2026-10-04"), (2, "2026-10-05"), (3, "2026-10-05")], "event_id INT, dt STRING")
df.repartition("dt").write.partitionBy("dt").parquet(path)
print(sorted(d for d in os.listdir(path) if d.startswith("dt=")))

q = spark.read.parquet(path).where("dt = '2026-10-05'")
plan = q._jdf.queryExecution().executedPlan().toString()
print(re.sub(r"#\d+", "", re.search(r"PartitionFilters: \[[^\]]*\]", plan).group(0)))
print(q.count(), "rows")
spark.stop()

['dt=2026-10-04', 'dt=2026-10-05']
PartitionFilters: [isnotnull(dt), (dt = 2026-10-05)]
2 rows

Common mistakes

• Partitioning by a high-cardinality column, creating thousands of folders with tiny files.
• Filtering on an expression of the partition column, such as to_date(dt), which some engines cannot use for pruning.
• Confusing Spark’s in-memory partitions with on-disk partitioning.
• Writing a partitioned table without repartitioning by the partition column first, so every task writes a file into every folder.