Data Compression
  1. Home
  2. Data Conversion & Migration Pipelines for Cloud-Native Geospatial Storage
  3. Incremental Conversion and Change Data Capture
  4. Partition Overwrite vs Append for Spatial Tables
Section: Data Conversion & Migration Pipelines for Cloud-Native Geospatial Storage 13 min read

Partition Overwrite vs Append for Spatial Tables

Append when features are immutable; overwrite partitions when they are not. That single test decides it, and the reason it is not obvious is that append is so much cheaper that teams reach for it first and then discover, months later, that the table has three rows for the same address. This page sets out the trade and its edge cases, extending incremental conversion and change data capture.

Quick Reference

Property Append Partition overwrite Primary Use Case
Expresses an edit No Yes Editable feature estates
Expresses a delete No Yes Datasets with retirements
Bytes written per run Only the new rows Whole affected partitions Cost per run
Reader cache impact None — old files untouched Affected partitions invalidated CDN and query caches
Cross-partition move Not applicable Two partitions rewritten Spatial partition keys
Reader complexity Supersession logic if edits exist None Where the complexity lands

The Test and Its Consequences

An append-only table is a log. Every write adds rows; nothing existing changes. That makes it the cheapest possible incremental write — bytes written equal bytes of new data, no file is invalidated, and every reader’s cache stays warm. It also makes it structurally incapable of representing an edit or a delete, because there is no mechanism for saying “that earlier row no longer applies.”

Partition overwrite is the opposite bargain. A partition is the unit of replacement: to change one feature you rewrite every file in its partition. That expresses edits and deletes exactly, at the cost of rewriting bytes that did not change and invalidating readers’ caches for the whole partition.

The quantity that decides the economics is the change density within a partition: the fraction of a partition’s features that changed in a run. If one feature in a 200 MB partition changed, overwrite writes 200 MB to express 400 bytes of change. If half the partition changed, overwrite is close to optimal.

Bytes written against change density, for both write modes Bytes written per run plotted against the fraction of a partition's features that changed. The append line rises linearly from zero, because it writes exactly the changed rows. The partition overwrite line is flat and high across the whole range, because it rewrites the entire partition regardless of how little changed. The two converge only when nearly everything in the partition changed. An annotation marks that append is not available at all once features can be edited, so the comparison only applies where both are correct. Bytes written per run, one 200 MB partition partition overwrite — always 200 MB append — proportional to what changed 0.4% changed: append 0.8 MB, overwrite 200 MB 90% changed: the two converge 0.4% 45% 90% fraction of the partition that changed → bytes written → This comparison only applies where both modes are correct — append cannot express an edit at any density.

Implementation

python
# Requires: pyarrow>=16, geopandas>=1.0, boto3>=1.34  (Python 3.10+)
from __future__ import annotations

import uuid
from dataclasses import dataclass
from pathlib import Path

import geopandas as gpd
import pyarrow as pa
import pyarrow.parquet as pq


@dataclass(frozen=True)
class WriteResult:
    mode: str
    partitions_touched: int
    files_written: int
    bytes_written: int


def append_partition(
    frame: gpd.GeoDataFrame, root: Path, partition: str
) -> WriteResult:
    """Add a new file to a partition; nothing existing is touched.

    Only correct when features are immutable — an appended correction leaves
    the superseded row in place for every reader to trip over.
    """
    if frame.empty:
        raise ValueError("nothing to append")
    target_dir = root / partition
    target_dir.mkdir(parents=True, exist_ok=True)

    target = target_dir / f"part-{uuid.uuid4().hex}.parquet"
    frame.to_parquet(target, compression="zstd", compression_level=3)
    return WriteResult("append", 1, 1, target.stat().st_size)


def overwrite_partitions(
    frame: gpd.GeoDataFrame,
    root: Path,
    partition_column: str,
    partitions: set[str],
) -> WriteResult:
    """Replace whole partitions, writing beside and swapping at the end.

    Both the partition a feature left and the one it arrived in must appear in
    `partitions`, or the feature is duplicated across the two.
    """
    if partition_column not in frame.columns:
        raise KeyError(f"{partition_column!r} is not in the frame")
    present = set(frame[partition_column].astype(str).unique())
    missing_from_frame = partitions - present
    if missing_from_frame:
        # A partition being rewritten with no surviving rows is legitimate
        # (everything in it was deleted) but must be explicit, not accidental.
        raise ValueError(
            f"partitions {sorted(missing_from_frame)} were scheduled for rewrite "
            f"but the frame has no rows for them — pass them explicitly as empty"
        )

    written = staged = 0
    for partition in sorted(partitions):
        subset = frame[frame[partition_column].astype(str) == partition]
        staging = root / f".{partition}.staging"
        staging.mkdir(parents=True, exist_ok=True)
        target_file = staging / "part-0000.parquet"
        subset.to_parquet(target_file, compression="zstd", compression_level=3)
        written += target_file.stat().st_size
        staged += 1

    # Swap only after every staging write succeeded, so a failure leaves the
    # previous complete state in place.
    for partition in sorted(partitions):
        live = root / partition
        staging = root / f".{partition}.staging"
        if live.exists():
            for old in live.glob("*.parquet"):
                old.unlink()
            live.rmdir()
        staging.rename(live)

    return WriteResult("overwrite", len(partitions), staged, written)

Validation

The assertion that matters is that no feature key appears twice. It is cheap, it catches the cross-partition move bug, and it is the one check that append-with-supersession pipelines habitually skip.

python
# Requires: duckdb>=1.0 — no feature may appear in two partitions
import duckdb

con = duckdb.connect()
duplicates = con.execute("""
    SELECT feature_key, count(*) AS n, list(DISTINCT region) AS partitions
    FROM read_parquet('s3://bucket/parcels/**/*.parquet', hive_partitioning => true)
    GROUP BY feature_key
    HAVING count(*) > 1
    LIMIT 20
""").fetchall()

assert not duplicates, (
    f"{len(duplicates)} feature(s) exist in more than one partition — a "
    f"cross-partition move rewrote only the destination: {duplicates[:3]}"
)

Expected result: zero rows. A non-empty result with two distinct partitions per key is the cross-partition move bug; a non-empty result with one partition per key is an append that should have been an overwrite.

The cross-partition move, done wrongly and done correctly A feature whose geometry was edited so that it now falls in partition B rather than partition A. In the incorrect handling, only partition B is rewritten: the feature now exists in both partitions and a spatial query covering the boundary returns it twice. In the correct handling, both partition A and partition B are rewritten, so the feature disappears from A and appears in B exactly once. The diagnostic is a group-by on the feature key. Wrong — only the destination rewritten partition A …, feature 8814 stale, never removed partition B …, feature 8814 newly written a boundary query returns feature 8814 twice no error, no warning — just a wrong count Right — both partitions rewritten partition A … (8814 removed) rewritten without it partition B …, feature 8814 rewritten with it exactly one row for feature 8814 two partitions rewritten to express one edit The diff must compare each feature's current partition against the one recorded last run — comparing content alone marks this feature unchanged and rewrites nothing at all. Which is the third and worst outcome: the feature stays in the wrong partition indefinitely. What append does to file count over time File count in one partition plotted across a year of hourly appends. Without compaction the count rises linearly to nearly nine thousand files, and query planning time rises with it long before storage becomes a concern. With a nightly compaction that merges the day\u2019s appends into properly sized row groups, the count oscillates around a small number and planning time stays flat. Files in one partition over a year of hourly appends no compaction — 8,760 files nightly compaction — 24 files at worst Query planning degrades long before storage does, and it degrades on every query.

Edge Cases and Caveats

Append into an over-partitioned layout. Appending one small file per run per partition produces thousands of tiny files within months, and query planning degrades long before storage does. Schedule a compaction that merges small files into properly sized row groups, and treat it as part of the pipeline rather than as maintenance.

Overwrite without atomicity. A partition rewritten by deleting then writing has a window in which readers see missing data. Write to a staging path and swap, as the implementation does, or use a table format that swaps a file list transactionally — the argument for Iceberg tables.

Spatial partition keys and moving geometry. Any partition key derived from geometry — a grid cell, a Hilbert bucket, an administrative area — means an edit can move a feature between partitions. Administrative or temporal keys are more stable; if you must partition spatially, the change-detection diff has to track the previous partition explicitly.

Frequently Asked Questions

When can I use append-only writes for a spatial dataset?

When features are immutable once written: sensor readings, GPS traces, transaction events, observation records. Each new record is genuinely new, nothing already written ever changes, and the natural partition key is time. Append is dramatically cheaper because it never rewrites existing bytes, and it never invalidates a cached file. The moment a feature can be edited, append alone stops being correct.

Why can’t I just append a corrected version of a feature?

You can, but then the table holds two rows for one feature and every reader must know to keep only the latest. That works if you control every consumer and they all apply the same rule, and it fails the moment a naive SELECT counts the feature twice. Append-with-supersession is a real pattern, but it moves complexity from the writer to every reader, which is usually the wrong direction.

How does a feature moving between partitions affect the write mode?

It forces both the old and new partitions to be rewritten, because the feature must disappear from one and appear in the other. Writing only the new partition leaves a duplicate behind that spatial queries will return twice. This is the case that catches pipelines using a spatial partition key: an edit that nudges a geometry across a partition boundary is a two-partition write, not a one-partition write.

← Back to Incremental Conversion and Change Data Capture