Partitions and partition indexes

Lesson 3 of 4 in Cataloging and Discovery.

A partition is a catalog entry, not a piece of data. It binds one combination of partition-column values to one S3 location: dt=2026-03-14 lives at s3://lake/events/dt=2026-03-14/. Two consequences fall out, and they pull in opposite directions — which is why partitioning has an optimum rather than a direction.

Partition columns cost nothing to read. Their values are not stored in the data files; they come from the catalog entry or from the key itself. So a predicate on a partition column is evaluated entirely in metadata, before a single object is opened — that is partition pruning, and it is the single biggest lever on lake query cost, because Athena and Redshift Spectrum bill by bytes scanned and a pruned partition contributes nothing.

The register has weight of its own. Partitioned by day for ten years is about 3,650 entries. By hour, 87,600. By hour and customer across five thousand customers, more than four hundred million. Every query begins by asking the catalog which partitions it needs, and that call is on the critical path. This is the mechanism behind the complaint "the query got slower as the table grew" even when the bytes scanned did not change at all — and it is why over-partitioning is a real failure mode rather than a style preference.

The practical test for a grain is the size of a partition's data. If one partition holds a few kilobytes, the grain is too fine: you have traded a small saving in bytes scanned for a large cost in planning and in per-file overhead. The rule of thumb is to partition on the column your queries actually filter on, at the coarsest grain that still prunes usefully, and to let columnar statistics handle the finer filtering inside the files.

A partition index changes what the GetPartitions call does, and the mechanism is worth stating exactly because the exam tests the condition rather than the feature.

Without an index, the Data Catalog loads every partition of the table and then applies the request's filter expression. Planning time therefore rises with the total partition count no matter how selective the query is. With an index, it tries to fetch only the matching partitions.

The condition is the part people miss: the first key of the index must appear in the expression. For an index on [country, category, year]:

Expression	Index used?
`country = 'US'`	yes
`country = 'US' AND category = 'Shoes'`	yes
`category = 'Shoes'`	no — `country` is absent, so all partitions load
`country = 'US' AND category = 'Shoes' AND year > '2018'`	yes
`country = 'US' AND category = 'Shoes' OR year > '2018'`	no — an `OR` is present
`country IN ('US','UK') AND category = 'Shoes'`	no — the operator is unsupported

Index optimisation is best-effort: a missing index, an unsupported operator or an OR anywhere in the expression makes the catalog fall back to loading everything, silently. A table can carry up to three indexes for different query shapes, and adding one to an existing table runs a backfill which reports per-partition errors if some partitions cannot be indexed.

Partition projection is the other answer, and it works differently: you declare the ranges of partition values in table properties, and the engine computes the partition values and locations instead of retrieving them. There is no catalog round trip for partitions at all, so planning time stops scaling with partition count, and new partitions need no registration. The cost is in the two words "declare the ranges": it suits a regular, dense key space — a date range, a numeric id range — and not a sparse or irregular one.

Four ways to make a partition known, and the one each situation calls for
Mechanism	How it learns	Scales how	The catch
The writing job	the job that created the partition registers it, in the same run	perfectly — one call per partition created, ever	none worth the name; this is the default for a pipeline you own
A crawler	lists the prefix and discovers what is new	runtime grows with the table, because it lists everything each run	it is also a writer of the table definition, with the authority to replace it
MSCK REPAIR TABLE	scans the location for `column=value/` folders and adds the missing ones	badly — a full prefix listing every time, which can time out on a large table	it only ever adds; entries whose data is gone are never removed
Partition projection	computes values and locations from ranges declared in table properties	flatly — planning stops scaling with partition count at all	Athena ignores registered partition metadata, so a sparse key space breaks

Two pruning failures that look like something else

A function on the column defeats pruning. WHERE dt = '2026-03-14' prunes. WHERE date(event_time) = DATE '2026-03-14' does not, because event_time lives inside the files and the planner cannot evaluate the expression before opening them. Even WHERE year(dt) = 2026 on a string partition column loses pruning, because the planner is handed a function rather than a comparison against the stored value. Compare the partition column to a literal, in the form it is stored.

Projection plus registered partitions is not additive. The documentation states it plainly: enabling partition projection makes Athena ignore any partition metadata registered to the table. Teams enable it on a table that already had partitions registered and conclude it is broken, when in fact it is doing exactly what it says — and projecting a dense range over data that is actually sparse means most of the projected partitions resolve to prefixes holding nothing, while anything outside the declared range becomes invisible.

Key terms: Partition (catalog), Partition index, Partition pruning, Over-partitioning, Hive-style partitioning, MSCK REPAIR TABLE

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.