List Partitioning Spatial Data by Region

Problem Statement

A property-listings platform operates in fourteen national markets from one PostGIS database. Every query in the product is scoped to a market — a user in Portugal never sees Norwegian listings — but the single GiST index spans all fourteen, so every search descends a tree fourteen times larger than it needs to be. This page applies spatial partitioning on a region key rather than a time key, and shows how to keep the key honest when it has to be derived from geometry.

Why the Naive Approach Fails

The obvious attempt is PARTITION BY RANGE (geom), which PostgreSQL rejects outright: geometry has no default btree operator class, so it cannot be a partition key. The next attempt — a region text column filled in by the application — works mechanically but fails in a subtler way: nothing stops a Norwegian polygon being labelled PT, and once one exists, every query for Portugal returns a listing in the Arctic and pruning has actively hidden the bug.

Partition keys must agree with the geometry Two partitions holding listings for Portugal and Norway. One Norwegian listing carries the region code PT, so it sits in the Portuguese partition. A query for Portugal returns it and a query for Norway does not, because pruning skipped the partition it should have been in. A check constraint comparing the geometry against the region boundary rejects the row at insert. A wrong key is worse than no partitioning listings_pt in Tromsø region = 'PT' on all five rows listings_no the Tromsø listing is missing from here The fix is a constraint, not a convention CHECK (region = 'PT' AND geom && ST_MakeEnvelope(-9.6, 36.9, -6.1, 42.2, 4326))

Production-Ready Implementation

Start with the parent and a partition per market, each carrying a bounding-box constraint that ties the key to the geography:

sql
CREATE TABLE listings (
    id          bigserial,
    region      char(2)  NOT NULL,
    title       text     NOT NULL,
    price_cents bigint   NOT NULL,
    geom        geometry(Point, 4326) NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE listings_pt PARTITION OF listings
    FOR VALUES IN ('PT');
ALTER TABLE listings_pt
    ADD CONSTRAINT listings_pt_extent
    CHECK (geom && ST_MakeEnvelope(-9.6, 36.9, -6.1, 42.2, 4326));

CREATE TABLE listings_no PARTITION OF listings
    FOR VALUES IN ('NO');
ALTER TABLE listings_no
    ADD CONSTRAINT listings_no_extent
    CHECK (geom && ST_MakeEnvelope(4.0, 57.9, 31.6, 71.4, 4326));

CREATE TABLE listings_default PARTITION OF listings DEFAULT;

The extent constraint is cheap — a bounding-box test against a constant — and it turns a data-integrity problem into an insert error at the point of the mistake. It also gives the planner an extra pruning opportunity: a query with a bounding box entirely outside Portugal can skip listings_pt even without the region code, because the constraint proves no row there can match.

Each partition then gets its own spatial index, attached to a parent template:

sql
CREATE INDEX CONCURRENTLY listings_pt_geom_idx ON listings_pt USING gist (geom);
CREATE INDEX CONCURRENTLY listings_no_geom_idx ON listings_no USING gist (geom);

CREATE INDEX listings_geom_idx ON ONLY listings USING gist (geom);
ALTER INDEX listings_geom_idx ATTACH PARTITION listings_pt_geom_idx;
ALTER INDEX listings_geom_idx ATTACH PARTITION listings_no_geom_idx;

Deriving the key from the geometry

Where the region can be computed from coordinates alone, a generated column removes the possibility of disagreement entirely:

sql
-- A coarse grid cell as the partition key: immutable, so a generated column works.
ALTER TABLE observations
    ADD COLUMN grid_cell text
    GENERATED ALWAYS AS (ST_GeoHash(geom, 3)) STORED;

ST_GeoHash(geom, 3) yields a cell roughly 150 km across, which makes a natural partition granularity for continental data. Because the column is generated, it cannot drift from the geometry: updating geom updates the key, and PostgreSQL moves the row to the correct partition automatically.

A lookup-based key — “which administrative region contains this point?” — cannot be a generated column, because the answer depends on another table. In that case compute it in the insert path and let the extent constraint catch mistakes:

python
from sqlalchemy import select, func

def region_for(session, point_wkt: str, srid: int = 4326) -> str:
    """Resolve the market a listing belongs to, once, at insert time."""
    stmt = (
        select(Region.code)
        .where(func.ST_Contains(
            Region.geom, func.ST_GeomFromText(point_wkt, srid)))
        .order_by(Region.priority)
        .limit(1)
    )
    code = session.scalar(stmt)
    if code is None:
        raise ValueError(f"no region contains {point_wkt}")
    return code
Three sources for the partition key Three options with their guarantees. An application-supplied code is fast but can disagree with the geometry. A generated column derived from a geohash can never disagree but gives arbitrary cell boundaries. A lookup against a regions table gives meaningful boundaries but costs a spatial join on every insert and needs a constraint to stay honest. Where the region code comes from supplied by the app insert cost: none can disagree with geom needs the extent check constraint to stay safe good when markets are a generated column insert cost: microseconds cannot disagree, ever cells are arbitrary, not administrative ST_GeoHash(geom, 3) lookup at insert insert cost: a point-in-poly needs the extent check too boundaries mean something to the business the usual right answer Whichever you pick, the extent constraint is what makes the choice safe rather than merely conventional.

Configuration and Tuning Knobs

Partition count follows market count, which keeps it naturally small — fourteen partitions is nothing to the planner. If you partition on geohash cells instead, choose the precision deliberately: precision 2 gives about thirty cells over Europe, precision 3 about a thousand. Prefer the coarser one.

Uneven partitions are fine and expected. A German partition holding twelve million listings and a Luxembourgish one holding nine thousand coexist happily; each gets an index sized to its own contents, which is the entire point.

The DEFAULT partition should be monitored, not relied on. Rows landing there mean a new market went live without a partition, and until one is created every query for that market scans the default.

Cross-region queries still work — they just cost more

A list-partitioned table has not become several databases. A query without a region predicate still returns the right answer; it simply searches every partition. That is exactly right for the rare admin report that spans markets, and exactly wrong for a per-request code path, so the distinction worth enforcing is which layer is allowed to omit the key.

The practical pattern is a repository method that requires the region and a separate, explicitly named one that does not:

python
def search_in_market(session, region: str, bbox) -> list[Listing]:
    """The normal path. The region key makes pruning possible."""
    return session.scalars(
        select(Listing)
        .where(Listing.region == region)
        .where(Listing.geom.op("&&")(bbox))
    ).all()


def search_all_markets(session, bbox) -> list[Listing]:
    """Admin and reporting only — scans every partition by design."""
    return session.scalars(
        select(Listing).where(Listing.geom.op("&&")(bbox))
    ).all()

Two functions rather than an optional argument, because an optional argument left unset is invisible in a code review while a call to search_all_markets from a request handler is not.

Verification Steps

sql
-- pruning works for a market-scoped query
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM listings
WHERE region = 'PT'
  AND geom && ST_MakeEnvelope(-9.2, 38.6, -9.0, 38.8, 4326);
-- expect a single Index Scan on listings_pt_geom_idx

-- nothing has drifted into the wrong partition
SELECT 'PT' AS region, count(*) AS outside_extent
FROM listings_pt
WHERE NOT (geom && ST_MakeEnvelope(-9.6, 36.9, -6.1, 42.2, 4326))
UNION ALL
SELECT 'NO', count(*)
FROM listings_no
WHERE NOT (geom && ST_MakeEnvelope(4.0, 57.9, 31.6, 71.4, 4326));

-- is the default partition collecting anything?
SELECT region, count(*) FROM listings_default GROUP BY region;
Uneven partitions are the normal case GiST index size per market: Germany 1.4 gigabytes, Spain 620 megabytes, Portugal 210, Norway 96, Luxembourg 3. Each index is sized to its own contents, which is exactly the benefit — a Luxembourg query never touches a German index. One index per market, sized to that market listings_de 1.4 GB listings_es 620 MB listings_pt 210 MB listings_lu 3 MB — and a query there touches only this Balance is not a goal here. Isolation is.

Gotchas Checklist

  • Adding a partition for a new market takes a lock on the parent. Brief, but set lock_timeout and retry rather than blocking every insert during a launch.
  • ST_GeoHash requires geographic coordinates. On a projected SRID it returns nonsense without erroring. Cast or transform first, or use ST_SnapToGrid on projected data instead.
  • Updating the region column moves the row. That is a delete plus insert internally, so it churns both partitions’ indexes. If listings genuinely relocate across markets, reconsider the key.
  • A generated column cannot reference another table. The error message mentions immutability rather than tables, which sends people down the wrong path — the fix is to compute the value in the insert path.
  • Extent constraints must be maintained if borders change. They are constants; an administrative boundary change means updating them, and the update needs a full validation scan of that partition.