S3 Small Files Problem: Fix Slow Queries and Rising Bills

Nothing changed. That is what makes this one maddening. Same queries, same dashboards, same nightly job, and yet the report that used to load in nine seconds now takes fifty, and the S3 line on the bill has grown a request-charge component nobody remembers approving.

Nine times out of ten the cause is file count, not data volume. This is the S3 small files problem, and it never arrives on a particular day. It accumulates until someone asks why a lake holding the same data as last quarter costs more to query.

warning How it shows up before anyone names it

Teams rarely report "we have too many small files". They report symptoms, and the symptoms are specific enough to recognise:

• Planning time grows while scan volume stays flat. In Athena the "data scanned" figure is unchanged but total runtime doubles. That gap is listing and file-open overhead, not I/O.
• SlowDown: Please reduce your request rate appears in query status. AWS names this explicitly as a small-files symptom.
• Spark and Glue jobs sit idle at the start. The driver spends minutes enumerating objects before a single executor does any work.
• A request-charge line becomes visible on the S3 bill. Storage is flat. Requests are not.
• It got worse gradually and nobody deployed anything. A pipeline adding 400 files a day adds 146,000 a year without changing its behaviour at all.

That last one is the tell that separates this from a regression. Nobody broke it. The pipeline has been doing the same slightly wrong thing since the day it shipped.

receipt_long Why file count costs more than file size

Object storage has no directories. A "partition" is a key prefix, and finding out what lives under it means asking S3 to list it. AWS's Athena performance guidance is blunt about the consequence:

Datasets that consist of many small files result in poor overall query performance. When Athena plans a query, it lists all partition locations, which takes time. Handling and requesting each file also has a computational overhead. Therefore, loading a single bigger file from Amazon S3 is faster than loading the same records from many smaller files.

Three separate costs stack up behind that sentence.

Listing is sequential and capped. A list call returns at most 1,000 keys. The same AWS page notes that "multiple sequential list operations are required if you have more than 1000 files in a partition because Amazon S3 returns only 1000 objects per list operation". A partition holding 40,000 objects therefore needs at least 40 round trips before the engine knows what to read.

Every file is a separate request, and requests have a ceiling. S3 delivers "at least 3,500 PUT/COPY/POST/DELETE or 5,500 GET/HEAD requests per second per partitioned Amazon S3 prefix", per the S3 performance design patterns documentation. A single query fanning out over millions of tiny objects can exceed that on its own, which is where the throttling errors come from.

Columnar formats get worse, not better, at small sizes. Parquet stores per-file metadata that a reader must parse before it can skip anything, and AWS notes that the default Parquet row-group size is 128 MB. A 2 MB Parquet file cannot fill one row group, so the statistics that make column pruning fast have almost nothing to prune. In AWS's words: "For small files, the overhead of the columnar file format outweighs the benefits."

Then the arithmetic. In us-east-1, S3 request pricing runs about $0.005 per 1,000 LIST requests and $0.0004 per 1,000 GET requests. Reading a table as 5,000,000 objects costs roughly $2.00 in GET charges per full pass. Reading the identical data as 5,000 right-sized objects costs about $0.002. Run that scan 200 times a month and the request line alone is $400 against $0.40. Those are published list rates and they move, but the ratio is the durable part, and the ratio is a thousand to one.

search Find out in one query whether this is your problem

Do not guess. Athena exposes a "$path" pseudo-column giving the S3 object each row came from, so you can count files per partition directly:

SELECT
  regexp_extract("$path", '^(.*)/[^/]+$', 1) AS partition_prefix,
  count(DISTINCT "$path")                    AS files,
  count(*)                                   AS rows_in_partition
FROM events
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-28'
GROUP BY 1
ORDER BY files DESC
LIMIT 20;

That query scans the partitions it touches, so keep the date range tight. For a free answer on a single prefix, ask S3 instead:

aws s3 ls s3://my-lake/events/event_date=2026-08-27/ \
  --recursive --summarize | tail -3
# Total Objects: 41822
# Total Size: 3187446512      <- ~3 GB spread across 41,822 objects

Divide size by object count. That single number tells you almost everything:

settings Where the small files actually come from

Four writers produce nearly all of them, and each has a different fix.

One file per micro-batch. A streaming consumer or delivery buffer flushing every 60 seconds writes 1,440 objects a day per shard, however little data arrived. Overnight, when volume drops, the files get smaller but the count does not.

Over-partitioning. Partitioning by hour a table that receives a few megabytes an hour guarantees thin partitions. AWS's guidance is direct: "if your queries look at time spans of days, don't partition by hour, even if some queries filter to that level."

Spark's default write parallelism. A job with 200 shuffle partitions writing to a date-partitioned table produces up to 200 files per date, per run. Run it hourly and one day's partition ends up holding thousands of files that together amount to a few hundred megabytes.

Frequent incremental and change-data loads. Every upsert cycle appends a new delta. On the three-tier Glue pipeline I built for an environmental-analytics provider, the incremental and slowly-changing-dimension loads run far more often than the full loads, and that is precisely the write pattern that fragments a partition over months.

straighten The target is 128 MB to 1 GB, and the vendors agree

This is one of the few numbers in data engineering with real consensus behind it, which makes it easy to defend in a design review:

AWS's managed compaction also encodes a trigger rule you can copy even if you never switch the feature on. In the Data Catalog, "the compaction process starts when a table or any of its partitions have more than 100 files. Each file must be smaller than 75% of the target file size."

Turn that into your own alarm: more than 100 files in a partition, each under 75% of target, means compact it. It gives monitoring an unambiguous threshold instead of a vibe.

Hudi is worth calling out because it solves the problem at the write rather than after it. As the project documents it, "a critical design decision in the Hudi architecture is to avoid small file creation", routing new inserts into existing file groups still under the small-file limit. That is why the S3 and Athena lake I built for a sales-engagement platform, which ingests millions of records a day through incremental upserts, uses Hudi for the merge layer rather than plain appended Parquet.

edit Fix the writers first, because it is cheaper than compaction

Compaction is rework. You pay to read and rewrite data you already paid to write once. Whenever the fix can move upstream, move it. AWS says the same thing in one line: "The best time to combine small files is before they are written."

In Spark or Glue, stop accepting the default writer count and derive one from the output size:

import math
 
TARGET_BYTES = 256 * 1024 * 1024          # 256 MB: mid-band and safe
 
approx_bytes = estimated_row_count * AVG_ROW_BYTES
writers = max(1, math.ceil(approx_bytes / TARGET_BYTES))
 
(df
   .repartition(writers, "event_date")    # at most `writers` files per date
   .sortWithinPartitions("event_ts")      # tighter Parquet min/max stats
   .write
   .mode("append")
   .partitionBy("event_date")
   .parquet("s3://my-lake/events/"))

repartition costs a shuffle. If the data is already reasonably distributed, coalesce(writers) does the same job without one, at the price of less even file sizes. For streaming or micro-batch writers, widen the buffer instead: flushing every 15 minutes rather than every minute cuts object count by 15x and changes nothing a daily report can detect.

On the read side, Glue can group tiny objects into one in-memory partition so they stop spawning one task each:

frame = glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    connection_options={
        "paths": ["s3://my-lake/raw/events/"],
        "recurse": True,
        "groupFiles": "inPartition",
        "groupSize": "134217728",   # 128 * 1024 * 1024
    },
    format="json",
)

compress Compacting what is already there

Compaction is bin-packing: read a partition's files, write the rows back out in objects near the target size, then swap. The safe shape never varies. Stage, verify row counts, swap, delete last.

-- 1. Rewrite one day into a staging prefix, as Parquet.
CREATE TABLE tmp_events_20260827
WITH (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  external_location = 's3://my-lake/staging/events/event_date=2026-08-27/'
) AS
SELECT user_id, action, payload, event_ts
FROM events
WHERE event_date = '2026-08-27';
 
-- 2. Verify before touching anything.
SELECT
  (SELECT count(*) FROM events WHERE event_date = '2026-08-27') AS before_rows,
  (SELECT count(*) FROM tmp_events_20260827)                    AS after_rows;
 
-- 3. Swap the prefix, then DROP TABLE tmp_events_20260827.
--    Dropping an Athena CTAS table leaves its S3 objects in place, so the
--    data survives the drop and you delete the old prefix yourself.

Two caveats before you schedule this.

CTAS in Athena exposes no target-file-size knob. It writes files according to how the query parallelised, so you get fewer and larger objects but not a precise size. When the size has to be guaranteed, run the rewrite in Glue or Spark with an explicit writer count.

And compaction rewrites objects underneath any query already running. On a plain Hive-style table, a reader that listed the prefix before the swap can fail when those keys disappear. That is the operational reason open table formats ship their own compaction with snapshot isolation. If your table already sits on one, use the built-in path: managed compaction in the Glue Data Catalog is designed to run "without interfering with concurrent queries".

account_tree Partition granularity is the other half of the problem

Compaction with the wrong partition scheme is a treadmill. If the layout guarantees thin partitions, they refill with fragments as fast as you pack them.

The rule is one line: partition to the grain your queries filter on, and no finer. Sizing it is arithmetic rather than taste. Take daily volume, divide by a 256 MB target, and see what falls out.

If a partition grain cannot fill a single target-sized file, it is too fine. Recover the narrow time filtering another way: keep records sorted by timestamp inside the file so Parquet's min/max statistics skip row groups. AWS recommends exactly this trade, noting that "for queries on shorter time windows, sorting by timestamp is almost as efficient as partitioning by hour".

Two smaller layout rules are worth enforcing at the same time. Keep files flat inside a partition prefix, because AWS warns that nested directories force "multiple rounds of listings" during planning. And delete catalog entries for partitions whose data has aged out, since empty partitions still get listed at query time.

Partition design and file sizing are the same decision seen from two ends, which is why my Athena query optimisation guide and this post keep pointing at each other. Get both right and SQL tuning rarely matters. Get them wrong and no amount of trimming Glue DPU-hours hides it.

quiz Frequently asked questions

flag Where to start on Monday

Run the object count on your three most-queried tables. If any partition holds more than a hundred objects averaging a couple of megabytes, you have found where the query time and the request charges went.

Then work cheapest first: fix the writers so fragments stop arriving, compact the worst partitions into the target band, and revisit partition grain only if the layout guarantees thin partitions however often you pack them. Fragmentation cuts both ways. Left alone it compounds. Fixed at the writer, it stops the same day.

If you would rather not spend a quarter working out which of the four writers is responsible, look at how I have built and repaired S3 lakes for other teams, or tell me what your numbers look like.