Partitioning data on disk: practical tips and trade-offs

The challenge of scale

When you first build a data lake, querying a single large Parquet file feels fine. But once your events table grows to 500 GB or 5 TB, even a simple filter query scans the entire dataset, burning compute and time. Partitioning data on disk: practical tips and trade-offs is one of the most impactful optimizations we implement in production systems, yet it requires thoughtful design to avoid creating more problems than it solves.

The core idea is deceptively simple: organize your files into folders based on a column value, so queries only read the folders they need. But choosing the right partitioning strategy, managing file sizes, and handling growth over time demands careful attention.

partitionBy — Write folders by date or region

Spark's partitionBy method lets you organize data into a folder hierarchy. When you write a DataFrame with a partition column, Spark creates separate folders for each unique value, storing only the files for that partition inside each folder.

Here's a simple example. Imagine you have event data with an event_date column:

df.write \
  .partitionBy("event_date") \
  .mode("overwrite") \
  .parquet("s3://lake/events/")

After this write, your S3 bucket will look like:

s3://lake/events/
  event_date=2025-01-10/
    part-00000.parquet
    part-00001.parquet
  event_date=2025-01-11/
    part-00000.parquet
    part-00001.parquet
  event_date=2025-01-12/
    part-00000.parquet

Spark writes the partition column into the folder name but not into the Parquet file itself. This saves space and makes the partition value immediately obvious from the path.

In a real e-commerce platform, we might partition by both date and region to organize high-volume order events:

orders_df.write \
  .partitionBy("order_date", "region") \
  .mode("overwrite") \
  .parquet("s3://lake/orders/")

This creates a two-level hierarchy: order_date=2025-01-10/region=US/, order_date=2025-01-10/region=EU/, and so on. Multiple partitioning columns let you slice data along multiple dimensions, but they also multiply the number of folders and can fragment files further.

Partition pruning — Queries skip folders that do not match filters

Once data is partitioned, Spark's query planner uses partition pruning to skip entire folders that do not match your filter conditions. This is where partitioning delivers real performance gains.

Consider a query against our partitioned events table:

spark.read.parquet("s3://lake/events/") \
  .filter("event_date == '2025-01-10'") \
  .filter("event_type == 'purchase'") \
  .groupBy("user_id") \
  .count() \
  .show()

Spark's optimizer recognizes the filter on event_date, which matches your partition column. Instead of reading all 500 GB across all dates, it only reads the event_date=2025-01-10/ folder—maybe 5 GB. The event_type == 'purchase' filter still requires scanning the Parquet files, but you have already eliminated 99% of your data.

Partition pruning happens automatically in Spark SQL and DataFrame queries, as long as:

• The filter predicate matches a partition column directly (e.g., event_date == '2025-01-10').
• The filter is not buried inside a complex expression that the optimizer cannot analyze.
• You read from a path with the partition folder structure (Spark must see the folders to prune them).

In a ride-hailing system, we partition trip events by both pickup date and city. A query for trips in San Francisco on a specific day:

trips_df \
  .filter("pickup_date == '2025-01-10'") \
  .filter("city == 'SF'") \
  .show()

Spark prunes both levels: it reads only pickup_date=2025-01-10/city=SF/, cutting query time from minutes to seconds.

Choose low-cardinality keys — Dates are common; user ids are not

Not all columns make good partition keys. The ideal partition column has low cardinality: a small number of unique values that persist across many rows. Dates, regions, status codes, and product categories are classic examples. User IDs, transaction IDs, or IP addresses are not.

Why does cardinality matter? Each unique value creates a new folder. If you partition by user_id on a table with millions of users, you create millions of folders. Each folder might contain just a handful of rows, leading to many tiny files, slow writes, and I/O overhead during reads. The Hadoop filesystem and S3 metadata services struggle with millions of small objects.

Here's a cautionary example from a CRM system:

# Bad: high cardinality
customer_interactions_df.write \
  .partitionBy("customer_id") \
  .parquet("s3://lake/interactions/")  # Creates millions of folders

The same data, partitioned wisely:

# Good: low cardinality
customer_interactions_df.write \
  .partitionBy("interaction_date", "interaction_type") \
  .parquet("s3://lake/interactions/")  # Creates hundreds of folders

A practical heuristic: aim for 100 to 10,000 partitions in total. With 10,000 partitions and 1 TB of data, each partition holds roughly 100 MB—a reasonable file size for most queries.

When you need to filter by high-cardinality columns like user ID or email, rely on Parquet's columnar statistics and filtering within files, not on partition structure. Modern Parquet files store min/max values for each column, allowing Spark to skip row groups and blocks within a file even without partitioning.

Avoid tiny files — Too many partitions create small files

Partitioning aggressively can backfire. If you partition by event_date and event_hour, you may end up with 24 partitions per day. On a high-volume system with data arriving in small batches, each partition might contain only 10 MB of data spread across many files. This creates three problems:

1. Write overhead: Spark must open, write to, and close a file handle for each partition, consuming memory and I/O bandwidth.
2. Read latency: Reading 1,000 files of 10 MB each is slower than reading 10 files of 1 GB each, even if the total data volume is identical.
3. Metadata pressure: Listing millions of objects on S3, tracking file handles in memory, and managing partition discovery all become bottlenecks.

We have seen production incidents where aggressive partitioning caused driver out-of-memory errors simply from holding too many file handles.

A practical approach is to use repartition before writing to coalesce data into fewer, larger files within each partition:

df.write \
  .partitionBy("event_date") \
  .option("spark.sql.files.maxRecordsPerFile", 100_000_000) \
  .mode("overwrite") \
  .parquet("s3://lake/events/")

The maxRecordsPerFile option limits the number of rows per file, ensuring that each file is reasonably sized. Alternatively, repartition the data before writing:

df.repartition(col("event_date")) \
  .write \
  .partitionBy("event_date") \
  .mode("overwrite") \
  .parquet("s3://lake/events/")

Repartitioning shuffles data across executors to group all rows of the same partition value together, then writes each group to fewer files. This trades a one-time write cost for cleaner downstream reads.

In a streaming context, where data arrives incrementally, write in micro-batches to specific date partitions and periodically compact older partitions using OPTIMIZE commands (if using Delta Lake) or scheduled rewrite jobs.

Common mistakes

Partitioning by columns that change infrequently: If you partition by a status field that only has two values, you create just two partitions. Partition pruning helps, but you miss the main benefit of splitting data into many folders for targeted reads.

Forgetting to filter on partition columns: If you write df.filter("user_id == 123") without filtering on the partition column first, Spark still reads all partitions. Always filter on partition columns early to trigger pruning.

Over-partitioning during development: A daily partition works fine for a table with 10 GB per day. But when the table scales to 500 GB per day, partition by both date and hour or region to keep each partition small. Retrofitting this partitioning strategy on billions of rows is painful.

Ignoring file sizes after partitioning: Run a quick check after your first few writes: spark.read.parquet("s3://lake/events/").inputFiles().length tells you how many files Spark found. Divide total data volume by file count to estimate average file size. Aim for files in the 100 MB to 1 GB range.

Key takeaways

• Partitioning organizes data into folders based on column values, enabling Spark to skip irrelevant folders during queries.
• Partition pruning is automatic when filters match partition columns; it is one of the highest-ROI optimizations in data warehousing.
• Choose low-cardinality columns for partitioning: dates, regions, and categories work well; user IDs and transaction IDs do not.
• Monitor partition size and file count after writes; tiny files hurt performance and consume memory.
• Repartition before writing or use maxRecordsPerFile to control file sizes within each partition.
• Partition strategy is not static; grow it as your data grows to balance partition count against file size.