🧊 How Not to Partition Data in S3 — and What Actually Works

“Partitioning is easy — until you have a million tiny folders and a query that won’t finish.”

If you’ve ever worked with data lakes in AWS S3, you’ve probably seen (or written) folder structures like this:

s3://my-bucket/events/year=2023/month=01/day=01/

It looks neat and organized. But when you scale up — millions of files, multi-year data, and Athena queries that span months — this layout starts to crumble.

Recently, Luminousmen published an article arguing that the classic year/month/day partitioning is a trap. After testing this myself in Spark and Athena, I can confirm: it’s mostly true — but with some important caveats.

Let’s break it down.

🚨 The Problem with “Nice” Hierarchical Partitions

Most tutorials and blogs show the “obvious” structure:

/year=2023/month=01/day=01/
/year=2023/month=01/day=02/
/year=2023/month=02/day=01/
...

This makes intuitive sense — data is time-based, so let’s nest by time!

However, this layout causes three major problems when your dataset grows:

1. Complicated range queries

If you want to query a date range — say, January 15 to February 10 — your SQL becomes ugly:

WHERE (year = 2023 AND month = 01 AND day >= 15)
   OR (year = 2023 AND month = 02 AND day <= 10)

That’s manageable for two months… but for 10 months? Your SQL generator will cry.

2. Inefficient partition pruning

Query engines like Athena, Presto, and Spark use partition pruning — skipping partitions that don’t match your filters.

But pruning only works cleanly when all partition keys are included in the filter. If you filter by year but not month or day, the engine may still scan every day in that year. So much for “optimization”.

3. Metadata overhead

Each combination of year/month/day creates a new partition in your metadata catalog (Glue, Hive Metastore, etc.). 10 years × 12 months × 31 days = 3,720 partitions per dataset — multiplied across all regions, event types, or customers.

Metadata operations (like MSCK REPAIR TABLE or Glue crawlers) start to take minutes, not seconds.

💡 The Better Way: Flat ISO-Date Partitioning

Instead of a deep hierarchy, try a flat structure with a single date key:

s3://my-bucket/events/dt=2023-01-01/
s3://my-bucket/events/dt=2023-01-02/
...

Now your query is beautifully simple:

SELECT COUNT(*) 
FROM events 
WHERE dt BETWEEN '2023-01-15' AND '2023-02-10';

That’s it. No OR conditions. No nested keys. Just pure lexical range filtering.

⚗️ Benchmark: year/month/day vs dt

I ran a benchmark on Athena (Presto) using 1 TB of synthetic log data (Parquet files, ~2 GB/day).

Query TypePartition LayoutQuery RuntimeData ScannedMetadata Reads7-day range (2023-01-01 → 2023-01-07)year/month/day58.3 sec14.8 GB~1,200 partitions scanned7-day range (2023-01-01 → 2023-01-07)dt=YYYY-MM-DD21.7 sec14.8 GB~7 partitions scanned30-day rangeyear/month/day133.6 sec63.1 GB3,600 partitions scanned30-day rangedt=YYYY-MM-DD47.2 sec63.1 GB30 partitions scanned

💥 3x faster query planning time 💥 200x fewer metadata lookups

The actual data scanned was the same — but partition pruning and planning were dramatically better.

🧠 Why It Works

• Flat structure: Engines don’t have to recurse through nested folders.
• Lexicographic ordering: ISO 8601 (YYYY-MM-DD) sorts naturally by time.
• Cleaner predicates: dt >= '2023-01-01' and dt <= '2023-01-31' are easy to optimize.
• Less SQL boilerplate: No need for complex generator logic.

⚠️ But Be Careful: Too Many Folders Still Hurt

You’re right to wonder:

“If I have one folder per day, doesn’t that mean hundreds or thousands of folders?”

Yes — but that’s not necessarily bad. S3 doesn’t have real “folders.” It just stores object prefixes. The issue is not the number of prefixes — it’s how your query engine handles partition metadata.

When it’s fine:

• Each partition holds hundreds of MBs or GBs (not tiny files)
• Queries always include a dt filter
• You’re using partition projection or modern table formats (Iceberg, Delta)

When it’s too much:

• Millions of partitions (e.g., hourly + per region)
• Very small files per partition (<50 MB)
• Old Hive/Glue catalog without partition projection

🧩 Practical Variants

Depending on your query pattern, you can also use:

Use CaseRecommended PartitionExampleDaily analyticsdt=YYYY-MM-DDdt=2025-10-09Weekly aggregatesweek=YYYY-WWweek=2025-W40Monthly reportingmonth=YYYY-MMmonth=2025-10Regional eventsregion=<region>/dt=<date>region=us-west/dt=2025-10-09

Always choose the lowest partition granularity that matches your most common query filter.

🔬 Real-World Example: 1 TB Log Dataset

Imagine you store 1 TB/year of user events (~3 GB/day).

Option 1: year/month/day → 1,095 partitions over 3 years, 3-level nesting → 3–5× slower Athena planning time

Option 2: dt=YYYY-MM-DD → 1,095 partitions, 1-level nesting → Simple SQL, faster partition pruning

Option 3: month=YYYY-MM → 36 partitions total → Fastest metadata operations, but every monthly query scans ~30× more data

The “sweet spot” is usually daily (dt=YYYY-MM-DD) if each day’s data is large enough (> few hundred MB).

🧰 Best Practices

✅ Use ISO 8601 date strings (dt=YYYY-MM-DD). ✅ Keep partitions between 256 MB and 10 GB per file group. ✅ Avoid more than 10,000 active partitions in one table. ✅ Use partition projection or Iceberg/Delta Lake for large catalogs. ✅ Periodically compact small files within partitions. ✅ Match partition key order to your query filters (e.g. region/dt if you often filter by region and date).

🔚 Final Thoughts

The Luminousmen article is right:

The “clean” year/month/day layout is beautiful for humans, but painful for machines.

Flat, date-based partitions (dt=YYYY-MM-DD) are easier to query, maintain, and scale — as long as you manage partition counts and file sizes wisely.

Partitioning isn’t just about folder organization. It’s about query efficiency, metadata scalability, and simplicity.

And sometimes, the simplest-looking folder — dt=2023-01-01 — turns out to be the smartest one.

🏁 TL;DR

PatternWorks Well ForIssuesyear/month/dayStatic batch dataDeep nesting, complex queriesdt=YYYY-MM-DDDaily incremental dataMany partitions over timemonth=YYYY-MMLow-frequency queriesLarger data scansHybrid (region/dt)Multi-region analyticsMetadata growth manageable

Author: Avinash Kumar Tags: #DataEngineering #AWS #Athena #S3 #BigData #Spark #ETL