Choosing Partition Keys for an OSM Data Lake

Partitioning is the single largest lever on query cost in a lake, and it is routinely chosen by how the data arrives rather than by how it is read. The two almost never coincide.

Prerequisites

Conceptual minimum

Partition pruning works only when a query’s predicate matches the partition key. Everything else follows from that one fact.

Load date partitions nothing useful. No analyst filters on when the data was ingested, so every query scans every partition. It is chosen because it matches how files arrive, which is a property of the writer rather than of any reader.

Administrative area matches the common predicate. Most OSM analytics is scoped to a country or region, so an area partition prunes hard. Its weakness is skew: some countries are two orders of magnitude larger than others, producing partitions that are both enormous and tiny in the same table.

A spatial cell partitions evenly and serves arbitrary spatial filters, at the cost that every query must translate its region into cells. It works best as a clustering key inside an area partition rather than as the partition key itself.

Feature class partitions very unevenly. Buildings and roads dominate any extract, so a class partition produces two enormous partitions and a long tail of small ones.

The measurement that decides all of this is bytes scanned per query, broken down by predicate. Wall-clock time confounds the partitioning with cluster size and caching; bytes scanned does not.

Four partition keys against the properties that decide between them A grid of four candidate partition keys against how well each prunes, how evenly it divides the data, and what it costs a query. Load date prunes for nothing, divides evenly, and forces every query to scan everything. Administrative area prunes for most queries, divides very unevenly because countries differ enormously in size, and costs nothing extra. A spatial cell prunes for spatial filters, divides evenly, and requires each query to translate its region into cells. Feature class prunes for class filters, divides extremely unevenly, and costs nothing extra. Four keys, and only one matches the common predicate Prunes for Evenness Query cost Load date nothing even scans all Admin area most queries very uneven none Spatial cell spatial filters even translate region Feature class class filters extremely uneven none The first row is the most commonly chosen and the only one that prunes for no query anybody writes.
Area partitioning plus cell clustering combines the second row's pruning with the third row's evenness.

Runnable solution

python 
from __future__ import annotations

import logging
import re
from collections import Counter
from dataclasses import dataclass

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
logger = logging.getLogger("osm.lake.partition")

# Predicates worth counting, as they appear in a query log.
PREDICATE_PATTERNS = {
    "area": re.compile(r"\b(country_code|area_key|admin_\w+)\s*(=|IN)", re.I),
    "class": re.compile(r"\b(class|subclass|feature_class)\s*(=|IN)", re.I),
    "spatial": re.compile(r"\bST_(Intersects|Within|Contains|DWithin)\b", re.I),
    "name": re.compile(r"\bname\s+(=|LIKE|ILIKE)", re.I),
    "date": re.compile(r"\b(load_date|ingested_at|snapshot_date)\s*(=|>|<)", re.I),
}
SKEW_LIMIT = 20.0        # largest / median partition; above this, subdivide


@dataclass(frozen=True)
class PredicateProfile:
    counts: Counter
    total: int

    def share(self, name: str) -> float:
        return self.counts[name] / self.total if self.total else 0.0

    def recommend(self) -> str:
        area = self.share("area")
        spatial = self.share("spatial")
        if area >= 0.5:
            return ("partition by area; cluster by a spatial cell within it"
                    if spatial >= 0.2 else "partition by area")
        if spatial >= 0.5:
            return "partition by a spatial cell"
        if self.share("class") >= 0.5:
            return "partition by class, and expect heavy skew"
        return "no predicate dominates; partition by area as the safe default"


def profile_queries(queries: list[str]) -> PredicateProfile:
    counts: Counter = Counter()
    for query in queries:
        for name, pattern in PREDICATE_PATTERNS.items():
            if pattern.search(query):
                counts[name] += 1
    profile = PredicateProfile(counts, len(queries))
    for name in PREDICATE_PATTERNS:
        logger.info("%-8s appears in %5.1f%% of queries",
                    name, profile.share(name) * 100)
    logger.info("recommendation: %s", profile.recommend())
    return profile


def check_skew(partition_rows: dict[str, int]) -> dict[str, float]:
    """OSM partitions by area are extremely uneven; measure before committing."""
    if not partition_rows:
        return {}
    sizes = sorted(partition_rows.values())
    median = sizes[len(sizes) // 2] or 1
    largest = sizes[-1]
    skew = largest / median
    logger.info("%d partition(s): median %d row(s), largest %d, skew %.1fx",
                len(sizes), median, largest, skew)

    if skew > SKEW_LIMIT:
        offenders = [k for k, v in partition_rows.items()
                     if v > median * SKEW_LIMIT]
        logger.warning("subdivide these partition(s) by a spatial cell: %s",
                       sorted(offenders)[:5])
    return {"median": float(median), "largest": float(largest), "skew": skew}


def partition_path(country_code: str, cell: str | None) -> str:
    """Hive-style path. Cell subdivision only where the skew demands it."""
    base = f"country_code={country_code}"
    return f"{base}/cell={cell}" if cell else base


if __name__ == "__main__":
    sample = [
        "SELECT count(*) FROM fact_feature WHERE country_code = 'PL'",
        "SELECT * FROM fact_feature WHERE country_code IN ('DE','FR') "
        "AND class = 'poi'",
        "SELECT * FROM fact_feature WHERE ST_Intersects(geom, ?)",
    ]
    profile_queries(sample)
    check_skew({"PL": 42_000_000, "LU": 900_000, "DE": 310_000_000})

Step-by-step walkthrough

  1. Profile the queries, do not guess. A week of logs settles an argument that otherwise runs on intuition, and the answer is frequently not what the team expected.
  2. Count predicates, not queries. One query can filter on several things, and each filter is a candidate partition key independently.
  3. Recommend a combination, not a single key. Area partitioning plus spatial clustering serves both the common scoped query and the occasional arbitrary one; either alone serves half.
  4. Measure skew before committing. OSM’s area partitions differ by orders of magnitude, and a partition scheme whose largest member is a hundred times the median has not solved the scan problem for the queries that matter most.
  5. Subdivide only the offenders. Adding a second partition level uniformly multiplies the file count everywhere; adding it only to the oversized partitions keeps the small ones simple.
  6. Use a stable path convention. Hive-style key-value directories are readable by every engine and self-documenting, which matters when somebody inspects the lake directly.
  7. Re-profile periodically. Query patterns change as consumers arrive, and a partitioning chosen two years ago for a workload nobody runs any more is a common finding.
Bytes scanned for one typical query under four partitioning schemes Four schemes measured on the same country-scoped query against a continental table. Partitioning by load date scans the whole table, because no predicate matches the key. Partitioning by feature class scans a large fraction, since the class filter prunes only two enormous partitions. Partitioning by administrative area scans a small fraction. Partitioning by area with spatial clustering inside it scans less still, because the spatial predicate prunes within the chosen partition. Bytes scanned for one country-scoped query By load date the whole table By feature class about half By admin area a few percent Area plus cell clustering under two percent Wall-clock time confounds partitioning with cluster size and caching; bytes scanned isolates the effect and transfers between environments.
The gap between the first and third bars is the whole argument, and it is decided before a single query is written.
From a query log to a physical layout Four steps. The profile step counts which predicates appear across a week of real queries, which settles the choice on evidence rather than intuition. The choose step picks the partition key matching the dominant predicate, with a clustering key for the second most common. The measure step compares the largest partition against the median to expose the skew that administrative partitioning always produces on OSM. The subdivide step adds a second level only to the partitions exceeding the skew limit, leaving the rest untouched. Profile, choose, measure, subdivide profile a week of real queries evidence, not intuition choose dominant predicate plus a clustering key measure largest over median skew is guaranteed subdivide only the offenders small ones stay simple The fourth step is where selective subdivision beats a uniform second level, which multiplies file counts across the whole lake.
Every step produces a number, which is what makes the resulting layout defensible to whoever inherits it.

Verification

  • Pruning actually happens. Check the query plan or the engine’s scanned-bytes metric; a partition filter that does not reduce it is not pruning.
  • The skew ratio is bounded. Largest over median should be within roughly an order of magnitude after subdivision.
  • File counts are sane. Thousands of tiny files per partition is its own problem; target files in the hundreds of megabytes.
  • The profile matches reality. Re-run the predicate profiling on a fresh log and confirm the recommendation is unchanged.
  • Small partitions stay simple. Confirm subdivision was applied only where the skew demanded it.

Common errors and fixes

Symptom Root cause One-line fix
Every query scans everything Partitioned by load date Partition on the predicate queries actually use
One partition dominates runtime Extreme area skew Subdivide the oversized partitions by a spatial cell
Tiny files everywhere Subdivision applied uniformly Subdivide only partitions above the skew limit
Spatial queries still scan widely No clustering within partitions Sort or cluster by a spatial cell inside each partition
Pruning silently absent Predicate does not match the key type Compare the filter’s type against the partition column
Layout no longer fits the workload Chosen once and never revisited Re-profile the query log periodically
Time measurements inconclusive Wall clock used as the metric Measure bytes scanned instead

Specification reference

Partition pruning allows a query engine to skip files whose partition values cannot satisfy the query’s predicates, so it applies only when a predicate references the partition column directly. Hive-style partitioning encodes key-value pairs in directory names, which every common engine recognises. See Partitioning a GeoParquet OSM Lake by H3 Cell for the cell-based layout referenced here.

Frequently Asked Questions

Why is partitioning by load date so common and so wrong?

Because it matches how data arrives: a daily job writes a day’s files, and a date directory is the obvious place to put them. The problem is that no analyst filters on ingestion date, so the partition key matches no predicate and every query scans the whole table. It is a writer’s convenience that costs every reader, which is exactly the trade partitioning exists to avoid.

How do I handle the skew between large and small countries?

Subdivide only the oversized partitions. Adding a second level uniformly multiplies the file count across the whole lake, which creates a small-files problem in every small partition to solve a large-files problem in a few. Measuring the ratio of the largest partition to the median, and subdividing anything above roughly an order of magnitude, keeps both ends healthy.

Should I partition or cluster by a spatial cell?

Cluster, in most cases. Partitioning by cell divides evenly but requires every query to translate its region into cells, and it loses the pruning that an area predicate would have given. Sorting or clustering by cell inside an area partition keeps the area pruning and adds intra-partition skipping for spatial predicates, which is the combination that serves both query shapes.

Why measure bytes scanned rather than query time?

Because wall-clock time mixes the effect of partitioning with cluster size, caching, concurrency and the weather. Bytes scanned isolates what the layout actually changed, transfers between environments, and in most cloud engines is also what the query costs. A partitioning change that halves bytes scanned has definitely helped; one that halves wall-clock time on a quiet cluster may have measured nothing.

Up one level: Modelling OSM for Analytics Warehouses.