Partitioning Data in S3 for Athena Performance

S3 has no indexes. When Athena runs a query it reads files, and unless something tells it which files are irrelevant, it reads all of them. Partitioning is that something: the data is split into folders by the value of one or more columns, and a query that filters on those columns reads only the matching folders. The Athena guide puts the benefit in one line: by partitioning you restrict the amount of data scanned by each query, improving performance and reducing cost (Athena docs: partition your data).

Done well, a partitioning scheme cuts most queries to a small part of the table. Done badly, it produces thousands of tiny files and slower queries than no partitioning at all. This article covers both, with a measured example.

What a partition is

A partition is a prefix in the bucket that holds the rows sharing a value. In the Hive style, which most tools write, the path carries both the key and the value:

s3://amzn-s3-demo-bucket/trips/pickup_date=2024-01-15/part-0.parquet
s3://amzn-s3-demo-bucket/trips/pickup_date=2024-01-16/part-0.parquet

The partition column is not stored inside the files; it is read from the path and behaves as a column in queries. A filter such as WHERE pickup_date = '2024-01-15' lets Athena skip every other prefix without opening a file.

A measured example

We took one month of New York yellow taxi trips (NYC TLC: trip record data), 2,964,624 rows, and wrote it as Hive-style Parquet twice on a local disk: partitioned by pickup date, and by pickup date and hour. Then we asked which files a query for a single day has to open.

import os, statistics
import pyarrow as pa
import pyarrow.compute as pc
import pyarrow.dataset as ds
import pyarrow.parquet as pq

table = pq.read_table("yellow_tripdata_2024-01.parquet")
pickup = table["tpep_pickup_datetime"]
table = table.append_column("pickup_date", pc.strftime(pickup, format="%Y-%m-%d"))
table = table.append_column("pickup_hour", pc.strftime(pickup, format="%H"))


def write(root, keys):
    data = table.drop_columns([c for c in ("pickup_date", "pickup_hour") if c not in keys])
    ds.write_dataset(data, root, format="parquet", max_partitions=5000,
                     partitioning=ds.partitioning(pa.schema([data.schema.field(k) for k in keys]), flavor="hive"))
    sizes = [os.path.getsize(os.path.join(d, f)) for d, _, fs in os.walk(root) for f in fs]
    print(f"{'/'.join(keys):24} {len(sizes):5,} files  total {sum(sizes) / 2**20:6.1f} MB  "
          f"median {statistics.median(sizes) / 1024:8.1f} KB")


write("by_date", ["pickup_date"])
write("by_date_hour", ["pickup_date", "pickup_hour"])

print()
parts = sorted(os.listdir("by_date"))
print(len(parts), "partitions; first:", parts[:4], "last:", parts[-2:])

# What one day's query has to open
dataset = ds.dataset("by_date", format="parquet", partitioning="hive")
all_files = list(dataset.get_fragments())
one_day = list(dataset.get_fragments(filter=ds.field("pickup_date") == "2024-01-15"))
day_bytes = sum(os.path.getsize(f.path) for f in one_day)
all_bytes = sum(os.path.getsize(f.path) for f in all_files)
print()
print(f"filter on the partition key : {len(one_day)} of {len(all_files)} files, "
      f"{day_bytes / 2**20:.2f} of {all_bytes / 2**20:.1f} MB ({day_bytes / all_bytes:.1%})")

# The same day asked for through the timestamp column instead
by_ts = list(dataset.get_fragments(
    filter=(ds.field("tpep_pickup_datetime") >= pa.scalar(pa.scalar("2024-01-15").cast(pa.timestamp("us"))))
    & (ds.field("tpep_pickup_datetime") < pa.scalar(pa.scalar("2024-01-16").cast(pa.timestamp("us"))))))
print(f"filter on the timestamp only: {len(by_ts)} of {len(all_files)} files listed for reading")
rows = dataset.to_table(filter=ds.field("pickup_date") == "2024-01-15", columns=["fare_amount"]).num_rows
print(f"rows on 2024-01-15: {rows:,}")

pickup_date                 35 files  total   60.5 MB  median   1996.7 KB
pickup_date/pickup_hour    749 files  total   71.7 MB  median    111.8 KB

35 partitions; first: ['pickup_date=2002-12-31', 'pickup_date=2009-01-01', 'pickup_date=2023-12-31', 'pickup_date=2024-01-01'] last: ['pickup_date=2024-01-31', 'pickup_date=2024-02-01']

filter on the partition key : 1 of 35 files, 1.65 of 60.5 MB (2.7%)
filter on the timestamp only: 35 of 35 files listed for reading
rows on 2024-01-15: 77,033

This ran with pyarrow on local folders, not on Athena; the layout is the one Athena reads, and the files selected stand in for the partitions it would scan.

Pruning works. Filtering on the partition key selects 1 file of 35 and 1.65 MB of 60.5 MB, under three percent of the table.

A filter on another column prunes nothing. Asking for the same day through the timestamp column leaves all 35 files to be read. The engine may skip parts of each file using Parquet statistics, but it cannot skip partitions, because it does not know the two columns are related. Queries have to filter on the partition key itself. This is the most common reason a partitioned table still scans everything.

Bad data creates stray partitions. A month has 31 days, yet there are 35 partitions: the file contains a few trips stamped 2002, 2009, 31 December 2023 and 1 February 2024. Partitioning by a date taken from the data inherits the data's faults. Either filter such rows out first, or partition by the load date, which you control.

Finer is not better. Adding the hour gives 749 files with a median size of 111.8 KB, and the total grows from 60.5 MB to 71.7 MB, because every file carries its own metadata and compresses less well.

Choosing partition keys

The Athena guide's advice is to optimise for the most common queries, not for rare ones (Athena docs: optimize data). In practice:

• Partition on what queries filter by, which for most analytical tables is a date.
• Keep cardinality low. A key with thousands of values per day, such as a customer ID, shatters the table. The guide suggests bucketing for lookups on high-cardinality keys.
• Stop at the grain queries need. If queries cover days, do not partition by hour. The guide recommends sorting records by timestamp within each file instead, which is almost as efficient for short windows.
• Use string partition keys. Athena needs string keys to push partition filters down to the Glue Data Catalog.
• Aim for reasonably large files. The guide warns that many small files give poor performance, and that for small files the overhead of a columnar format outweighs its benefits. Even the daily files here, about 2 MB each, are small; at this volume, partitioning by month would be the better production choice.

Telling Athena about partitions

Writing a folder to S3 does not make it queryable. Athena must learn that the partition exists, in one of three ways:

The guide advises against MSCK REPAIR TABLE for regular maintenance: it can take a long time, only adds partitions and never removes old ones. A Glue crawler can also register partitions.

Partition projection removes the bookkeeping. Athena calculates partition values and locations from table properties instead of looking them up in the catalog (Athena docs: partition projection). A definition written from the documentation, not run:

CREATE EXTERNAL TABLE trips (
  vendorid int,
  tpep_pickup_datetime timestamp,
  payment_type bigint,
  fare_amount double
)
PARTITIONED BY (pickup_date string)
STORED AS PARQUET
LOCATION 's3://amzn-s3-demo-bucket/trips/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.pickup_date.type' = 'date',
  'projection.pickup_date.format' = 'yyyy-MM-dd',
  'projection.pickup_date.range' = '2024-01-01,NOW',
  'storage.location.template' = 's3://amzn-s3-demo-bucket/trips/pickup_date=${pickup_date}/'
);

New days become queryable as soon as their files land. Two cautions from the guide: enabling projection makes Athena ignore any partitions registered in the catalog, and a query with no filter on the key makes Athena list every value in the range, so always filter.

Checking that pruning happens

Do not assume. Prefix the query with EXPLAIN and look for the partition key in the output; the guide shows that it lists the partition values the query will scan. After running, compare "Data scanned" in the query statistics with the table size. If the two are close, the filter is not reaching the partition key.

Limits worth knowing

The guide notes that Athena can query Glue tables with 10 million partitions but cannot read more than 1 million in a single scan, and that queries on tables with tens of thousands of partitions slow down unless every key is filtered by equality. If a design heads towards those numbers, the grain is too fine.

The same reasoning applies in Spark, where partitioning and bucketing decide how files are written. For the other half of Athena cost, see Parquet and compression for cheaper Athena queries, and for the service itself, Amazon Athena: querying S3 with SQL.

Data lake design on S3, Glue and partitioning for query engines are covered in the Data Engineering course.

Sources

• Partition your data (Amazon Athena User Guide) — AWS Documentation
• Optimize data (Amazon Athena User Guide) — AWS Documentation
• Use partition projection with Amazon Athena (Amazon Athena User Guide) — AWS Documentation
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission