PostGIS ST_AsMVT vs pre-generated PMTiles

Both approaches serve the same bytes to the same renderer. What differs is where the work happens and what you can prove afterwards: ST_AsMVT composes a tile per request from live tables, so there is nothing to invalidate and nothing to archive; PMTiles ships an immutable file with a digest, so there is nothing in the request path and nothing to keep warm. For a zoning layer the decision usually turns on two things a benchmark will not surface — whether you need to serve as-of a past date, and whether a screenshot has to be reproducible next year. This page compares them on those terms, under vector tile publishing for zoning maps.

Diagnosis: which properties actually differ

Property ST_AsMVT per request Pre-generated PMTiles
Invalidation nothing to invalidate; a cache in front needs purging versioned path, so no purge at all
Latency, cold a spatial query per tile an HTTP range request
Latency, warm cache hit cache hit
Cost model database CPU per pan storage plus egress
Freshness immediate — the next tile reflects the last commit as fresh as the last build
Bitemporal as-of query native — parameterise the SQL one pyramid per as-of date
Archival reproducibility none — a query result, not an artifact a file with a digest
Competes with the write path yes, on the same database no
Operational surface a database in the request path object storage

The two rows that decide it for zoning work are bitemporal as-of and archival reproducibility, and they point in opposite directions. If users need “show me the zoning as it stood on 1 March,” ST_AsMVT does it with a parameter while PMTiles needs a pyramid per date. If a screenshot has to be defensible next year, PMTiles is a file with a digest and a query result is not.

The properties that actually separate the two A seven-row by two-column comparison of PostGIS ST_AsMVT against pre-generated PMTiles. Rows are invalidation, freshness, whether an arbitrary as-of date can be served, archival reproducibility, whether tile serving competes with the write path, the cost model, and the operational surface. ST_AsMVT wins on freshness and as-of queries; PMTiles wins on invalidation, archival reproducibility, and staying out of the database. The properties that actually separate the two ST_AsMVT per request pre-generated PMTiles Invalidation cache in front needs purging versioned path, no purge Freshness immediate as of the last build Arbitrary as-of date native — two predicates one pyramid per date Archival reproducibility none — a query result a file with a digest Competes with the write path yes, same database no Cost model database CPU per pan storage + egress Operational surface a database in the request path object storage advantage workable disadvantage
Two rows decide it and they point opposite ways: only ST_AsMVT can serve an arbitrary as-of date, and only PMTiles gives a published view a digest. That is why the practical answer is both — pyramid for live traffic, dynamic for the historical view.

Step-by-step implementation

1. The ST_AsMVT version, with the as-of parameter that is its main advantage 

-- One function, one tile. The as_of parameters are why this approach exists for
-- zoning: they turn the bitemporal history into a map layer for free.
CREATE OR REPLACE FUNCTION zoning_tile(
    z integer, x integer, y integer,
    valid_at timestamptz DEFAULT now(),
    known_at timestamptz DEFAULT now()
) RETURNS bytea AS $$
WITH bounds AS (
    SELECT ST_TileEnvelope(z, x, y) AS geom
),
mvt AS (
    SELECT
        p.feature_id AS id,
        p.parcel_id,
        p.base_code,
        array_to_string(p.overlay_codes, ',') AS overlays,
        ST_AsMVTGeom(
            ST_Transform(p.geom, 3857),
            bounds.geom,
            4096,          -- extent
            64,            -- buffer, so features clipped at the edge still render
            true           -- clip
        ) AS geom
    FROM parcel_version p, bounds
    WHERE p.geom && ST_Transform(bounds.geom, ST_SRID(p.geom))
      AND p.valid_range @> valid_at        -- the as-of query, in two lines
      AND p.txn_range   @> known_at
)
SELECT ST_AsMVT(mvt, 'parcels', 4096, 'geom', 'id') FROM mvt;
$$ LANGUAGE sql STABLE PARALLEL SAFE;

The && bounding-box predicate before ST_AsMVTGeom is what makes this viable: it uses the GiST index to reduce candidates before any expensive clipping, exactly as spatial database indexing & performance describes. Without it every tile scans the table.

2. The PMTiles version, with the digest that is its main advantage 

import hashlib
import json
import subprocess


def build_pyramid(source_geojson, out_dir, as_of, taxonomy_version):
    """One immutable artifact, addressed by content. The digest is the property a
    query result cannot have."""
    version = as_of.strftime("%Y%m%dT%H%M%SZ")
    out = out_dir / f"parcels-{version}.pmtiles"

    subprocess.run([
        "tippecanoe", "--output", str(out), "--layer", "parcels",
        "--minimum-zoom", "13", "--maximum-zoom", "16",
        "--use-attribute-for-id", "feature_id",
        "--no-feature-limit", "--no-tile-size-limit", "--no-tiny-polygon-reduction",
        "--detect-shared-borders", "--no-simplification-of-shared-nodes",
        str(source_geojson),
    ], check=True)

    digest = hashlib.sha256(out.read_bytes()).hexdigest()
    (out_dir / f"manifest-{version}.json").write_text(json.dumps({
        "pyramid_version": version,
        "as_of": as_of.isoformat(),
        "taxonomy_version": taxonomy_version,
        "pmtiles_sha256": digest,          # a screenshot can be traced to this
    }, indent=2))
    return out, digest

3. Run both, for different jobs

The productive arrangement is not a choice. Serve the live map from PMTiles, because it is cheap, cacheable and out of the database’s way; keep ST_AsMVT behind an as-of parameter for the historical view, which is used rarely and cannot be pre-generated for every possible date.

Where tile requests actually go once both paths exist Stacked bar chart of tile request volume across four weeks, split between the pre-generated pyramid and the dynamic as-of endpoint. The pyramid serves between 840 000 and 1 020 000 requests a week; the dynamic endpoint serves between 900 and 1 600. Historical as-of queries are a fraction of a per cent of traffic, which is why putting them on a database query is affordable and putting live panning there is not. Where tile requests actually go once both paths exist 0 250000 500000 750000 1000000 1250000 912000 week 1 840000 week 2 1020000 week 3 968000 week 4 Tile requests dynamic as-of endpoint pre-generated pyramid Both paths must agree for the current date; the CI test asserts it.
Historical queries are well under 1% of traffic. That asymmetry is what makes the split work: the rare, expensive capability goes on the database, and the overwhelming majority of panning goes to object storage where it costs nothing but egress.
def tile_url(z, x, y, *, as_of=None, current_version=None):
    """Live traffic hits object storage; a historical query hits the database."""
    if as_of is None:
        return f"/tiles/parcels-{current_version}/{z}/{x}/{y}.pbf"
    # Rare path: a specific past date. Rate-limited and cached briefly.
    return f"/api/zoning_tile/{z}/{x}/{y}?valid_at={as_of.isoformat()}"

That split gives the live map an immutable, digest-addressed pyramid, and gives the historical view a capability no reasonable number of pre-built pyramids could cover — while keeping database load proportional to how rarely anyone asks a historical question.

4. Cache the dynamic path deliberately 

CACHE_SECONDS = {"current": 31_536_000,     # versioned path: a year
                 "as_of_past": 86_400,      # a past date never changes… almost
                 "as_of_recent": 60}        # within the last week, corrections still land


def cache_control(as_of, now):
    if as_of is None:
        return f"public, max-age={CACHE_SECONDS['current']}, immutable"
    age_days = (now - as_of).days
    if age_days > 7:
        # A bitemporal correction CAN still change a past valid-time answer, which is
        # why this is a day rather than a year.
        return f"public, max-age={CACHE_SECONDS['as_of_past']}"
    return f"public, max-age={CACHE_SECONDS['as_of_recent']}"

The comment matters: it is tempting to cache a historical tile forever because “the past does not change,” and in a bitemporal model it does — a late-arriving correction changes what was valid on a past date. A day is a defensible compromise; immutable is not.

Cache lifetime by what is being served Lollipop chart of the cache lifetime appropriate to four kinds of tile request. A versioned current pyramid can be cached for a year. An as-of date more than a week old can be cached for a day. An as-of date within the last week can be cached for 60 seconds. A tile served from a stable, unversioned path should not be cached at all. A dashed rule marks a day, above which the answer must be immutable by construction. Cache lifetime by what is being served 0 10000000 20000000 30000000 40000000 a day — beyond this it must be immutable by construction versioned current pyramid 31536000 s as-of older than a week 86400 s as-of within the last week 60 s unversioned path 0 s Cache lifetime (seconds)
The second row is the counter-intuitive one: a past date is not immutable in a bitemporal model, because a late-arriving correction changes what was valid then. A day is defensible; immutable would serve a superseded answer forever.

Verification & testing 

def test_both_paths_produce_the_same_tile_for_today():
    a = fetch_pmtile(CURRENT_VERSION, 14, 3423, 6192)
    b = fetch_dynamic(14, 3423, 6192, as_of=None)
    assert mvt_feature_ids(a) == mvt_feature_ids(b)
    assert mvt_geometry_digest(a) == mvt_geometry_digest(b)


def test_as_of_returns_the_historical_state():
    now = fetch_dynamic(14, 3423, 6192, as_of=date(2026, 8, 11))
    then = fetch_dynamic(14, 3423, 6192, as_of=date(2026, 3, 1))
    assert feature_class(now, PARCEL_ID) == "MIXED-USE"
    assert feature_class(then, PARCEL_ID) == "RES-LOW-1"     # before the rezone


def test_pmtiles_digest_matches_the_manifest():
    m = read_manifest(CURRENT_VERSION)
    assert sha256_of(pmtiles_path(CURRENT_VERSION)) == m["pmtiles_sha256"]


def test_dynamic_tile_uses_the_spatial_index():
    plan = explain(f"SELECT zoning_tile(14, 3423, 6192)")
    assert "Index Scan" in plan or "Bitmap Index Scan" in plan
    assert "Seq Scan on parcel_version" not in plan


def test_historical_tiles_are_not_marked_immutable():
    headers = fetch_dynamic_headers(14, 3423, 6192, as_of=date(2020, 1, 1))
    assert "immutable" not in headers["cache-control"]      # corrections still land

The first test is the one worth keeping in CI: when both paths exist, they must agree for the current date, or users see different maps depending on which URL they happened to hit.

Failure recovery

Database CPU spiking during map traffic. Dynamic tiles are competing with the write path. Move live traffic to the pyramid immediately — it is a style-source change — and keep the dynamic path for the as-of view only.

A screenshot nobody can date. A query-result tile has no manifest. If the live map is served dynamically, this is unfixable retrospectively; go forward by serving the current view from a versioned pyramid whose manifest records the source digest and effective date.

A historical tile that changed. Correct, and often surprising: a bitemporal correction revised what was valid on that past date. Check the cache headers are not immutable, and treat the change as the audit trail working rather than as corruption.

Both paths disagreeing for today. Usually the pyramid is older than the last commit. Check the manifest’s as_of against the database’s latest transaction time; if the pyramid lags, that is the freshness cost of pre-generation, and the fix is either a more frequent build or accepting a stated lag.

Frequently asked questions

Which should serve the live map?

Pre-generated PMTiles, for most zoning layers. It keeps a database out of the request path, caches at a versioned URL for a year, and gives every published view a digest. The freshness cost is real and bounded — the map is as current as the last build — which is why the build runs after the publication gate rather than on a timer of its own.

What can ST_AsMVT do that a pyramid cannot?

Serve an arbitrary as-of date. A bitemporal history can answer “what was the zoning on any past day,” and covering that with pre-built pyramids means one pyramid per date, which is not feasible. Parameterising the SQL with valid_at and known_at turns the whole history into a map layer for the cost of two range predicates.

Is it wasteful to run both?

No, because they carry very different traffic. Live map panning is the overwhelming majority and goes to object storage; historical as-of queries are rare, deliberate and worth a database query. Running both also gives a cross-check: the two paths must agree for the current date, which catches a stale pyramid.

Can historical tiles be cached forever?

No, and this is the counter-intuitive part of a bitemporal model. A late-arriving correction changes what was valid on a past date, so a tile for 1 March can legitimately differ today from what it was last week. Cache it for a day, not with immutable — the alternative is serving a superseded answer indefinitely.