Athena wasn't slow. We just gave it four million files.

Market tick data arrives continuously and in small pieces. If you write it to S3 the obvious way — one file per micro-batch, partitioned by date and instrument — you end up, after not very long, with millions of tiny Parquet objects.

Then the queries get slow, and someone proposes a bigger cluster.

Where the time actually goes

Athena does not mind large tables. It minds scattered ones.

Scanning a 512MB Parquet file is close to free per byte: the reader opens one object, reads the footer, and can skip whole row groups using column statistics. Scanning the same 512MB spread across four thousand 128KB objects means four thousand LIST and GET round trips, four thousand footers, and column statistics too granular to prune anything useful. The bytes are identical. The work is not.

Cost follows the same shape. You are paying for request volume and for metadata operations as much as for storage, and both scale with object count rather than with data volume.

So the fix is not more compute. The fix is fewer, larger files.

The awkward constraint

You cannot solve this by batching harder at write time. The ingest path has its own latency budget, and the whole point of a streaming pipeline is that data is queryable soon after it arrives. Holding records back to build bigger files trades the property you built the pipeline for.

Which means compaction has to happen out of band: read a partition after the fact, merge, sort, deduplicate, and write it back under a compacted prefix. The hot path stays optimised for write latency; a separate job optimises for read.

The job, and the one parameter that mattered

The compaction script does four things per partition:

1. Read every small .zstd.parquet object in the partition.
2. Deduplicate on a configured key set — for tick data, (timestamp, qualified_name).
3. Sort on the same columns, so that row-group statistics can actually prune at query time.
4. Write out files targeting a configured uncompressed size.

Point four is the one that earned its keep, and specifically the word configured.

The instinct is to hardcode a target — 128MB is the number everyone quotes — and move on. But the right size is a function of the dataset: how selective the typical query is, how compressible the columns are, how much memory the writer has. Making it a parameter meant the number could be argued about with evidence rather than inherited from a blog post. For this dataset the answer landed at 512MB uncompressed, which is well above the folk wisdom and comfortably correct for the access pattern.

The sort in step three is the quiet second win. Parquet stores min/max statistics per row group; if the data is unsorted those ranges overlap and the reader cannot skip anything. Sorted, the same predicate prunes most of the file before a byte is decompressed. Sorting is not free, but it happens once in a background job and pays out on every subsequent query.

The safety property

Compaction is destructive if you let it be. Ours is not, on the first pass: the original objects are lifecycle-tagged and retained for a recovery window rather than deleted inline. If a compaction run produces something wrong, the source data is still there. The lifecycle policy cleans up later, once the compacted output has been queried in anger.

This costs a little storage during the retention window and removes an entire class of incident. Easy trade.

The general shape

The end result was a 40% reduction in storage cost and query times that stopped being a topic of conversation. No new infrastructure, no bigger cluster — one out-of-band job and a parameter someone was willing to think about.

I have now seen the same pattern in three different systems, and it always presents the same way: performance degrades gradually, everyone assumes volume, and the actual problem is that the data is laid out for the convenience of the writer rather than the reader. It is worth checking the object count before you check the instance type.