Serving Vector Tiles from PostGIS with ST_AsMVT

ST_AsMVT aggregates rows into a single MVT tile, and ST_AsMVTGeom clips and transforms each geometry into the tile’s local grid. Together they turn a spatial table into a tile endpoint with no build step — which is the right architecture only when the data genuinely changes faster than a build can run.

When to Use This

When tiles must reflect the database within seconds: incident feeds, vehicle positions, live editing sessions, anything with a “last updated” that readers care about. Below that cadence, a built archive serves the same bytes without a database on the request path.

The honest test is whether a reader would notice a five-minute-old tile. If not, generate ahead of time.

Specification Detail

Production Command

CREATE OR REPLACE FUNCTION public.incidents_tile(z integer, x integer, y integer)
RETURNS bytea
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
AS $$
DECLARE
  bounds geometry := ST_TileEnvelope(z, x, y);
  mvt bytea;
BEGIN
  SELECT INTO mvt ST_AsMVT(t, 'incidents', 4096, 'geom')
  FROM (
    SELECT
      ST_AsMVTGeom(
        ST_Transform(i.geom, 3857),   -- source is EPSG:4326
        bounds, 4096, 64, true
      ) AS geom,
      i.id,
      i.category,
      i.severity,
      to_char(i.reported_at, 'YYYY-MM-DD"T"HH24:MI:SSZ') AS reported_at
    FROM incidents i
    WHERE i.geom && ST_Transform(bounds, 4326)   -- index-usable, on the source SRID
      AND i.reported_at > now() - interval '7 days'
      AND (z >= 12 OR i.severity >= 3)           -- thin low zooms deliberately
    LIMIT 20000
  ) AS t;

  RETURN COALESCE(mvt, ''::bytea);
END;
$$;

Four details in that function are what separate a working endpoint from a slow one.

The && predicate is written against the source SRID so the GiST index on i.geom can serve it. Transforming the column instead of the envelope — ST_Transform(i.geom, 3857) && bounds — makes the index unusable and turns every tile into a sequential scan.

The zoom-dependent WHERE clause is how a live endpoint stays inside the tile budget without a dropping strategy. There is no --drop-densest-as-needed here; thinning is something the query has to do.

The LIMIT is a safety valve. Without it, one dense tile can return a million rows and produce a tile no client will render.

And COALESCE turns a null aggregate into an empty tile, because ST_AsMVT over zero rows returns NULL and a handler that passes that through will emit a 500 for every empty area.

Indexing and Simplification

-- Required: without this every tile is a sequential scan
CREATE INDEX incidents_geom_gist ON incidents USING GIST (geom);

-- Usually worth it: filter columns used in the tile query
CREATE INDEX incidents_reported_at ON incidents (reported_at DESC);

-- For polygon layers, simplify per zoom rather than shipping full geometry
ST_AsMVTGeom(
  ST_Simplify(ST_Transform(g.geom, 3857), 4 * (2 ^ (14 - z))),
  bounds, 4096, 64, true
)

The simplification tolerance halves with each zoom because the tile’s ground extent does — the same scaling relationship Tippecanoe applies automatically, which a live endpoint has to implement itself.

Interaction Effects

With the edge cache. A live endpoint still belongs behind a CDN, with a short deliberate TTL — thirty to sixty seconds is usually indistinguishable from live and removes almost all database load. Without it, every reader’s every pan is a query, and the database becomes the capacity limit for the whole map.

With connection pooling. Tile requests are short and numerous, which is the worst shape for PostgreSQL connections. A pooler in transaction mode is effectively mandatory; without one, pool exhaustion appears as latency rather than as errors, which makes it hard to diagnose.

With Martin. Martin calls exactly this kind of function and handles the HTTP layer, headers and TileJSON. Writing the function and letting Martin serve it is the usual arrangement — see tile server selection.

With gzip. ST_AsMVT returns uncompressed bytes. The serving layer must compress and set Content-Encoding, and must not do so twice — the double-gzip failure is common here because the compression is added by hand.

Performance Impact

The point of that table is that the query latency does not change — the number of queries does, by two orders of magnitude. Tuning the query matters, and putting a cache in front of it matters more.

Common Mistakes

Transforming the geometry column in the predicate. Disables the index. This is the single most common cause of a slow tile endpoint.

Returning NULL for an empty tile. Produces a 500 for every tile over water.

No LIMIT. One dense tile can return enough rows to exhaust memory and produce an unusable tile.

No cache in front. The database becomes the scaling limit for a workload that is 99% cacheable even at a thirty-second TTL.

Forgetting per-zoom thinning. Live endpoints have no dropping strategy; low zooms will return every row unless the query excludes them.

FAQ

Is ST_AsMVT fast enough for production?

Yes, with the right index and a cache in front. Sub-hundred-millisecond p95 for a typical urban tile is achievable, and the cache means the database sees a small fraction of reader traffic.

Can I serve several layers from one query?

Yes — ST_AsMVT produces one layer, and concatenating several calls’ output produces a multi-layer tile. Martin does this when a function returns several layers, and it is cheaper than several requests.

Should the function be marked IMMUTABLE?

It is stable rather than truly immutable, since the table changes. Marking it IMMUTABLE allows better planning and is conventional for tile functions; STABLE is the strictly correct choice if the distinction matters to your deployment.

How do I know when to switch to pre-built tiles?

When the cache TTL you can accept exceeds the time a rebuild takes. At that point the build produces the same freshness with no database on the request path.

Related

• Tile Server Selection — the parent topic, and which servers call functions like this.
• Martin vs tileserver-gl for Production Delivery — choosing the process that fronts the database.
• Geometry Simplification Algorithms — the tolerance scaling a live query has to implement itself.
• Diagnosing Double-Gzipped Vector Tiles — the compression mistake this architecture invites.