Partitioning trajectory Parquet by time and space

Partition on time in the directory tree and on space in the sort order. That single asymmetry follows from how the two dimensions behave: time is unbounded and monotone, so it makes good directories; space is bounded and revisited daily, so a spatial directory tree produces a fixed set of paths that grow forever and prune nothing after the first year.

Why this happens

A partition column becomes a directory, and directories are cheap to skip and expensive to have too many of. Time partitions grow in number and each one is written once and then read; a query bounded to a week opens seven of thousands. That is the ideal shape.

A spatial partition is the opposite. A city has a fixed number of cells, so the directory count stops growing while each directory grows without bound — and after a year every spatial directory contains every day, so a time-bounded query has to open all of them. Worse, the two combined produce cells × days directories, which at H3 resolution 8 over a city is tens of thousands of tiny files per day and a metadata problem that dominates the query. The format-level trade-offs are in spatial storage formats.

Core pipeline

  1. Choose the time granularity from the target file size, not from a calendar preference.
  2. Sort within the partition by a space-filling-curve or cell key, so row-group statistics prune spatially.
  3. Write row groups small enough to skip and large enough to compress.
  4. Handle late data by compaction, not by rewriting the partition on every arrival.

Production-ready Python implementation

PYTHON 
import numpy as np
import pandas as pd
import pyarrow as pa
import pyarrow.dataset as ds
import pyarrow.parquet as pq


def choose_time_granularity(rows_per_day: int, bytes_per_row: int = 64,
                            target_bytes: int = 400_000_000) -> str:
    """
    Pick hour, day or month so partitions land near the target size.

    Too small and metadata dominates; too large and pruning is coarse. The
    target is a file-size decision, and the calendar unit follows from it.
    """
    daily = rows_per_day * bytes_per_row
    if daily > target_bytes * 4:
        return "hour"
    if daily * 28 < target_bytes:
        return "month"
    return "day"


def write_partitioned_trajectory(
    df: pd.DataFrame,
    out_dir: str,
    time_col: str = "t",
    cell_col: str = "h3_r8",
    entity_col: str = "entity_id",
    granularity: str = "day",
    row_group_rows: int = 300_000,
) -> dict:
    """
    Write day-partitioned, spatially sorted Parquet.

    Parameters
    ----------
    cell_col : str
        Spatial key used for the SORT ORDER, not for the directory tree.
        Sorting is what makes row-group min/max statistics able to skip
        blocks; partitioning on it instead explodes the file count.
    row_group_rows : int
        Smaller groups skip more finely and compress slightly worse.
        200k-500k is the usual band for trajectory rows.

    Raises
    ------
    ValueError
        On missing columns, an empty frame, or a naive timestamp.
    """
    required = {time_col, cell_col, entity_col}
    missing = required - set(df.columns)
    if missing:
        raise ValueError(f"Missing required columns: {missing}")
    if df.empty:
        raise ValueError("Input DataFrame is empty.")
    if df[time_col].dt.tz is None:
        raise ValueError(
            "Timestamps must be tz-aware. A local-time partition column "
            "produces partitions that overlap twice a year."
        )

    fmt = {"hour": "%Y-%m-%d-%H", "day": "%Y-%m-%d", "month": "%Y-%m"}[granularity]
    out = df.copy()
    out["part"] = out[time_col].dt.strftime(fmt)

    # Sort by the spatial key WITHIN the partition, then by time. This is the
    # single line that makes statistics-based skipping work; without it every
    # row group's bbox covers the whole city and nothing can be skipped.
    out = out.sort_values(["part", cell_col, time_col])

    table = pa.Table.from_pandas(out, preserve_index=False)
    pq.write_to_dataset(
        table,
        root_path=out_dir,
        partition_cols=["part"],
        row_group_size=row_group_rows,
        compression="zstd",
        use_dictionary=[entity_col, cell_col],
        write_statistics=True,
        existing_data_behavior="overwrite_or_ignore",
    )
    return {"partitions": int(out["part"].nunique()), "rows": len(out)}


def compact_partition(part_dir: str, target_bytes: int = 400_000_000) -> dict:
    """
    Merge the small files late data leaves behind.

    Late arrivals append new files to an existing partition. Rewriting the
    whole partition on every arrival is expensive; letting the fragments
    accumulate is worse, because the file count is what queries pay for.
    Compact on a schedule instead.
    """
    dataset = ds.dataset(part_dir, format="parquet")
    files = dataset.files
    if len(files) <= 1:
        return {"compacted": False, "files": len(files)}

    table = dataset.to_table()
    tmp = part_dir.rstrip("/") + ".compacting"
    pq.write_table(
        table, tmp + "/part-0.parquet" if False else tmp,
        compression="zstd", write_statistics=True,
        row_group_size=300_000,
    )
    return {"compacted": True, "files_before": len(files), "rows": table.num_rows}

Validation block

PYTHON 
def validate_layout(root: str, sample_query_days: int = 7) -> dict:
    """Assert the layout will still perform in a year."""
    dataset = ds.dataset(root, format="parquet", partitioning="hive")
    files = dataset.files
    sizes = []
    import os
    for f in files:
        try:
            sizes.append(os.path.getsize(f))
        except OSError:
            pass
    sizes = np.array(sizes, dtype=float)

    # 1. File size band. Small files are a metadata tax paid on every query.
    median_mb = float(np.median(sizes)) / 1e6
    assert median_mb > 20, (
        f"median file is {median_mb:.0f} MB — the partition is too fine and "
        "queries will be metadata-bound"
    )
    assert median_mb < 2000, f"median file is {median_mb:.0f} MB — pruning is too coarse"

    # 2. File count. Beyond a few tens of thousands, listing dominates.
    assert len(files) < 50_000, (
        f"{len(files)} files — consider a coarser partition or compaction"
    )

    # 3. Statistics must exist, or row-group skipping cannot happen.
    md = pq.ParquetFile(files[0]).metadata
    rg = md.row_group(0)
    has_stats = any(rg.column(i).statistics is not None for i in range(rg.num_columns))
    assert has_stats, "no row-group statistics — the writer disabled them"

    return {"files": len(files), "median_file_mb": median_mb,
            "row_groups_per_file": md.num_row_groups}

Common mistakes and gotchas

  • Partitioning on the spatial cell. The directory count stops growing while each directory accumulates every day, so time-bounded queries lose all pruning after the first year.

  • Partitioning on both. Cells × days directories produces millions of tiny files and a query that spends its time listing rather than reading.

  • Not sorting within the partition. Row-group statistics then cover the whole extent and skip nothing; the sort is what makes the spatial predicate useful without a spatial directory.

  • A local-time partition column. Partitions overlap by an hour twice a year, and a query on the boundary either double-counts or misses.

  • Letting late data fragment the partition. Each late arrival appends a file. Without scheduled compaction the file count grows until the metadata dominates.

  • Disabling statistics to save space. They are a few kilobytes per file and they are the entire mechanism for skipping row groups.

FAQ

Should I ever partition spatially?

At national or continental scale, yes — as a coarse second level, such as a country or a large region, so that a query for one city never opens another country’s files. The rule is that a spatial partition must have few values and be highly selective; H3 resolution 8 has neither property.

Hive-style partitioning or a manifest?

Hive-style directories are simpler and work everywhere. A table format such as Iceberg or Delta adds a manifest that tracks file-level statistics, which removes the listing cost entirely and makes compaction and late-data handling transactional. For an archive that is written once and read often, directories are usually enough; for one being continuously updated, the manifest earns its complexity.

How do I change the partition key later?

By rewriting, which is why the decision deserves an afternoon at the start. The rewrite is mechanical — read the dataset, re-sort, write with the new key — but it costs a full pass over the archive and coordination with every consumer that reads paths directly. Consumers that query through a catalogue rather than by path make this much easier.

Back to Spatial Storage Formats

Related in this section