#Partitioning GeoParquet for Athena Cost Control

For Athena to be cheap, partition GeoParquet on the columns your queries actually filter by — in practice a coarse spatial key (admin region or tile) plus a date — and serve those partitions with partition projection rather than registered Hive partitions once the count grows. Athena bills on bytes scanned, so every partition a query can skip is money saved. The spatial predicate never lowers the bill; only pruning does. This page sharpens the layout guidance from AWS Athena spatial queries on GeoParquet into a concrete key-selection and sizing decision, and it flags the small-file trap that quietly cancels out good partitioning.

#Quick-Reference: Partition Key Choices

#Choosing Partition Keys

The rule is unglamorous: partition on the columns that appear in your WHERE clause almost every time. A partition column only earns its keep if queries reference it, because pruning happens by matching the predicate against partition values before any file opens. A perfectly balanced tiling scheme that no query filters on prunes nothing and just adds path overhead.

Admin-region keys suit teams whose questions are framed jurisdictionally — parcels in one county, hydrology within one basin. They are cheap to reason about and align with how results are reported. Their weakness is skew: a metropolitan county may hold a hundred times the geometry of a rural one, so a single region partition becomes a hot, oversized path that dominates scan time. Spatial tiles fix skew by cutting space on a regular grid or a fixed-length geohash prefix, so an arbitrary bounding-box query prunes to just the tiles it overlaps. The trade is that dense urban tiles over-split into tiny files while sparse ocean tiles waste directory entries. The technique here is the same co-location idea behind spatial partitioning with quadtree indexes: put features that are queried together into the same physical path.

Date keys are almost always worth adding as a second dimension for datasets that grow over time, because incremental loads land in fresh date paths and time-bounded queries prune the rest. Date alone, though, prunes nothing spatially — pairing it with a region or tile key is what gives you two independent levers. Whichever keys you pick, prefer partition projection over registered Hive partitions once the count climbs: projection computes the target prefixes from the predicate, sidestepping both slow Glue listing and MSCK REPAIR TABLE.

#Focused Implementation

The snippet writes a GeoParquet dataset partitioned by admin region and a geohash tile prefix, then registers it for Athena partition projection. It derives the tile key from geometry centroids so spatial locality is baked into the path layout.

# python>=3.10, geopandas>=0.14, pyarrow>=14.0, awswrangler>=3.5
from __future__ import annotations

import geopandas as gpd
import awswrangler as wr


def add_partition_keys(gdf: gpd.GeoDataFrame, precision: int = 3) -> gpd.GeoDataFrame:
    """Attach an admin region and a geohash tile prefix as partition columns.

    Args:
        gdf: Input features with a valid geometry column and a 'region' attribute.
        precision: Geohash length; 3 gives ~156 km cells, 4 ~39 km. Higher
                   precision prunes finer but risks small files in dense areas.
    """
    if "region" not in gdf.columns:
        raise KeyError("expected a 'region' attribute to use as the top partition")

    centroids = gdf.geometry.representative_point()
    gdf = gdf.copy()
    # Encode centroid to a geohash prefix; keeps spatially near rows in one path.
    gdf["tile"] = [
        _geohash(pt.y, pt.x, precision) for pt in centroids
    ]
    return gdf


def write_partitioned(gdf: gpd.GeoDataFrame, s3_path: str, database: str, table: str) -> None:
    """Write partitioned GeoParquet and enable Athena partition projection."""
    wr.s3.to_parquet(
        df=gdf.to_wkb(),                 # geometry -> WKB binary column
        path=s3_path,
        dataset=True,
        partition_cols=["region", "tile"],
        compression="zstd",
        max_rows_by_file=2_000_000,      # guards against tiny files
        database=database,
        table=table,
        athena_partition_projection=True,
    )

Note max_rows_by_file: it caps how many files each partition produces, the direct defence against the small-file trap discussed below. The to_wkb() call ensures Athena receives a binary geometry column it can pass to ST_GeomFromBinary.

#Validation

After writing, run a representative bounding-box query and read the scanned bytes back. Pruning is working when the scan is proportional to the queried extent, not the table.

-- Region + tile predicate must precede the spatial refine
SELECT count(*) FROM parcels
WHERE region = 'us-west'
  AND tile IN ('9q8', '9q9', '9qb')          -- tiles covering the bbox
  AND ST_Intersects(ST_GeomFromBinary(geometry),
                    ST_GeomFromText('POLYGON((...))'));

Then inspect DataScannedInBytes from GetQueryExecution. Expected ranges for a 1 TB dataset: an unpartitioned scan reads 900 GB or more; region-plus-tile pruning on a metro-scale bbox should read low tens of gigabytes or less. If the scan barely drops, your query is not carrying the partition predicate, or the tiles listed are far larger than the query extent.

#Edge Cases and Caveats

The small-file trap. Fine tiling in dense areas can shatter a partition into hundreds of sub-megabyte files. Athena opens each file’s footer separately and pays per-object request overhead, so the pruning win is eaten by planning and request costs, and Parquet row-group pushdown weakens because tiny files hold tiny row groups. Cap files per partition (as max_rows_by_file does) and, if needed, compact with a CTAS pass. Aligning file size to your row group sizing strategy — 128 to 512 MB — keeps both pruning and pushdown healthy.

Partition skew from admin regions. A single dense region can hold most of the dataset, so a query touching it gains little from partitioning. Add a tile sub-level beneath the region, or switch the top key to tiles entirely, so no single path dominates.

Over-projection. Partition projection with an overly wide range (for example a date range starting years before any data exists) makes Athena consider prefixes that hold nothing. Empty prefixes are cheap but not free at scale; keep the projection bounds tight to the real data window.

#Frequently Asked Questions

Should I partition GeoParquet by spatial tile or by admin region?

Partition by whichever column your queries filter on most. Admin region works when analysts think in states, countries, or basins and queries carry that predicate. Spatial tiles (a fixed grid or geohash prefix) work when queries are arbitrary bounding boxes, because a tile key prunes to the cells covering the box. Many teams use a coarse admin region as the top level and a tile prefix below it, giving two independent pruning dimensions.

How many partitions is too many for Athena?

With registered Hive partitions, planning slows noticeably past a few tens of thousands and Glue enforces hard per-table limits. With partition projection there is no registered-count limit because partitions are computed from the predicate, but you still pay if each partition holds only a few small files. The practical ceiling is set by file size, not partition count: aim for 128 to 512 MB of data per partition path.

Does partition pruning reduce the ST_ predicate cost?

Pruning reduces bytes scanned, which is what Athena bills; the ST_ predicate itself is always free because it runs in compute after decompression. So partitioning does not make ST_Intersects cheaper per row, but it dramatically reduces how many rows reach it. The whole point is to shrink the data the spatial function must run over, which lowers both cost and latency.

#Related

• AWS Athena Spatial Queries on GeoParquet — parent guide: table registration, WKB decoding, and CTAS
• Trino & Presto GeoParquet Connector — the same partition pruning on self-managed Trino
• Row Group Sizing Strategies for Parquet — sizing files so pruning is not lost to small-file overhead
• Spatial Partitioning with Quadtree Indexes — co-locating features that are queried together

← Back to AWS Athena Spatial Queries on GeoParquet