Partitioning Strategy in Data Lakes: When It Hurts Performance

>GLB_ // tools · Partitioning

“Partition by date” is the default answer in most data lake designs. Then someone adds country, then customer_id, and six months later the table has 400,000 directories, most holding a single 3 MB file, and queries are slower than if the table weren’t partitioned at all.

Partitioning is a physical layout decision with a narrow sweet spot. This post covers when it helps, when it hurts, and how to check.

What partitioning actually buys you

Hive-style partitioning writes data into directories by column value:

s3://lake/events/event_date=2026-10-07/country=AR/part-0000.parquet

The benefit is partition pruning: a query with WHERE event_date = '2026-10-07' never lists or opens files outside that directory. That’s it. One mechanism, and it only fires when:

1. Queries filter on the partition column, and
2. The filter is expressed in a way the engine can push down (no function wrapping the column, e.g. WHERE year(event_ts) = 2026 on a table partitioned by event_date does not prune unless the engine derives it).

Everything else about partitioning is cost.

How it hurts

1. Small files multiply

Each write produces at least one file per partition it touches. Partitioning by date × country × event_type with 30 countries and 10 event types means a single daily batch writes up to 300 files. Streaming every minute: 432,000 files per day.

Small files then cost you in object-store requests, task overhead and metadata size (see the small-files post in this series).

2. Planning and listing overhead

On plain Parquet/Hive tables, the engine lists directories to find files. Listing 100,000 prefixes on S3 is slow and adds request cost. Table formats like Delta and Iceberg avoid listing by reading the file list from metadata, but a huge number of partitions still inflates the metadata the planner has to process.

3. Skew

Partitioning by country when 70% of traffic is US gives you one enormous partition and many tiny ones. Parallelism collapses on the big one; the small ones add overhead.

4. Queries that don’t filter on the partition key

If 80% of your queries filter by customer_id and the table is partitioned by event_date, those queries scan everything — now spread across more, smaller files than an unpartitioned table would have.

5. Rigidity

Changing a partition scheme on Hive-style tables means rewriting the whole table. Iceberg supports partition evolution in metadata; Delta does not support changing partition columns in place (liquid clustering is the escape hatch there).

Rules of thumb that hold up

From the Delta Lake and Databricks guidance, plus what I’ve seen in practice:

• Don’t partition small tables. Databricks recommends not partitioning tables under ~1 TB.
• Each partition should hold ~1 GB or more. If a partition is a few MB, the column is too granular.
• Low cardinality only. date (or month for smaller tables), region. Never user_id, order_id, timestamps.
• Partition column = most common filter. Check query history, not intuition.
• One partition column is usually enough. A second level multiplies file count; justify it with data.

Quick sanity math before choosing:

partition_size ≈ daily_volume_GB × retention_days / num_partitions

20 GB/day × 365 days partitioned by date → ~20 GB per partition. Fine. Add country (40 values) → ~500 MB, skewed. Borderline. Add event_type (12 values) → ~40 MB average, many much smaller. Too granular.

Check an existing table

Delta:

DESCRIBE DETAIL my_catalog.analytics.events;  -- numFiles, sizeInBytes, partitionColumns

File count and size per partition with delta-rs:

from deltalake import DeltaTable
import polars as pl

adds = pl.from_arrow(DeltaTable("s3://lake/events").get_add_actions(flatten=True))

(adds.group_by("partition.event_date")
     .agg(pl.len().alias("files"),
          (pl.col("size_bytes").sum() / 1e9).alias("gb"))
     .sort("gb"))

Red flags: thousands of partitions under 100 MB, or a long tail of partitions with one tiny file each.

Then confirm pruning actually happens:

EXPLAIN FORMATTED
SELECT count(*) FROM my_catalog.analytics.events
WHERE event_date = DATE'2026-10-07';

Look for PartitionFilters in the scan node. If it’s empty, the query isn’t pruning and partitioning is pure overhead for it.

What to use instead (or in addition)

My default for a new Delta table today: no partitioning, liquid clustering on the 1–2 most common filter columns, and revisit only if query history says otherwise.

Note on S3 request rates

An old argument for partitioning on S3 was spreading load across key prefixes. S3 supports at least 3,500 PUT/COPY/POST/DELETE and 5,500 GET/HEAD requests per second per prefix, and scales by prefix. That’s a reason to have distinct prefixes under very high request rates, not a reason to partition by high-cardinality columns. Most lakehouse workloads never hit those limits; small-file request volume is a bigger issue.

References

• Delta Lake — Best practices (choosing partition columns): https://docs.delta.io/latest/best-practices.html
• Databricks — When to partition tables: https://docs.databricks.com/en/tables/partitions.html
• Databricks — Liquid clustering: https://docs.databricks.com/en/delta/clustering.html
• Delta Lake — Data skipping and Z-ordering: https://docs.delta.io/latest/optimizations-oss.html
• Apache Iceberg — Partitioning and hidden partitioning: https://iceberg.apache.org/docs/latest/partitioning/
• Apache Iceberg — Partition evolution: https://iceberg.apache.org/docs/latest/evolution/#partition-evolution
• Spark SQL — EXPLAIN: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-explain.html
• Amazon S3 — Performance guidelines (request rates per prefix): https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimizing-performance.html