How to Serve Vector Tiles from PostGIS with ST_AsMVT
Problem statement
A web map needs tiles, the data is already in PostGIS, and cutting a static pyramid means an overnight job and a stale copy. ST_AsMVT generates a vector tile inside the database, from live data, in one query.
The appeal is obvious. The failure modes are less so:
- the tile takes 800 ms at low zoom because it is encoding a hundred thousand features nobody can see
- the geometry is not simplified for the zoom, so a coastline is drawn at full precision at zoom 4
- the tile is regenerated on every request, because nothing cached it
- the coordinates are wrong, because the geometry was not transformed to Web Mercator
Each has a specific fix, and together they are the difference between a tile endpoint that works and one that falls over the first time somebody pans.
Quick answer
One query, one round trip, one binary response:
-- :z, :x, :y are the tile coordinates
with bounds as (
select st_tileenvelope(:z, :x, :y) as geom
),
mvtgeom as (
select st_asmvtgeom(
st_transform(t.geom, 3857),
bounds.geom,
extent => 4096,
buffer => 64,
clip_geom => true) as geom,
t.id, t.name, t.category
from features t, bounds
where t.geom && st_transform(bounds.geom, 4326)
)
select st_asmvt(mvtgeom.*, 'features', 4096, 'geom') from mvtgeom;@app.get("/tiles/{z}/{x}/{y}.mvt")
def tile(z: int, x: int, y: int):
with pool.connection() as con:
body = con.execute(TILE_SQL, {"z": z, "x": x, "y": y}).fetchone()[0]
return Response(bytes(body), media_type="application/vnd.mapbox-vector-tile",
headers={"Cache-Control": "public, max-age=3600"})ST_TileEnvelope produces the tile's bounds in EPSG:3857, which is what the whole tile scheme is defined in.
Step-by-step solution
1. Understand what the two functions do
ST_AsMVTGeom prepares one geometry for a tile: transforms it into tile-local integer coordinates, clips it to the tile plus a buffer, and removes anything that collapses to nothing at that resolution. It returns NULL for geometry that disappears, which is why the outer query should filter nulls.
ST_AsMVT is an aggregate that packs the prepared rows into the tile's binary encoding, taking the layer name and the geometry column.
Both work in the tile's coordinate space, so the input must be in EPSG:3857.
2. Filter with the index before preparing geometry
The && bounding-box operator uses the GiST index and eliminates almost everything before any expensive work:
where t.geom && st_transform(bounds.geom, 4326)Doing it the other way — transforming every row and then intersecting — makes the index useless and the query slow at every zoom.
3. Simplify for the zoom level
At zoom 4 a tile covers thousands of kilometres, and the screen resolves about 0.15° per pixel. Sending full-resolution coastline is pure waste, and it is the commonest reason a low-zoom tile is enormous.
st_asmvtgeom(
st_transform(
case when :z < 10
then st_simplify(t.geom, greatest(0.0001, 0.05 / power(2, :z)))
else t.geom end,
3857),
bounds.geom, 4096, 64, true)Better still, keep pre-simplified geometry columns for zoom bands and choose between them — the simplification then happens once rather than per tile.
4. Limit what appears at low zoom
Generalising geometry is not enough if the tile still contains a hundred thousand features. Filter by importance:
where t.geom && st_transform(bounds.geom, 4326)
and (:z >= 10 or t.rank <= 100) -- only the significant ones when zoomed outThis is a cartographic decision, and making it explicitly in the query is better than shipping a tile the client has to thin.
5. Set the buffer and the extent deliberately
extent is the tile's internal coordinate grid, conventionally 4,096. buffer is how far outside the tile geometry is retained, in those units — 64 is a common default and exists so that a label or a wide line straddling the tile edge does not get cut in half visually.
A larger buffer means larger tiles; too small and features clip visibly at tile boundaries.
6. Cache, or none of the above matters
A dynamic tile endpoint without a cache regenerates identical tiles indefinitely. Measured on an HTTP service, answering a conditional request with 304 ran at 1,191 requests per second and 6.2 ms, against 100 requests per second and 94.6 ms for generating the full body.
Add an ETag, a Cache-Control, and — if the map will be public — a CDN.
Code examples
Example 1 — the endpoint, with pooling, caching and bounds checks
import hashlib
from fastapi import FastAPI, HTTPException, Request, Response
from psycopg_pool import ConnectionPool
app = FastAPI()
pool = ConnectionPool("postgresql://reader@db/gis", min_size=2, max_size=10)
MAX_ZOOM = 16
TILE_SQL = """
with bounds as (select st_tileenvelope(%(z)s, %(x)s, %(y)s) as geom),
mvtgeom as (
select st_asmvtgeom(st_transform(t.geom, 3857), bounds.geom, 4096, 64, true)
as geom,
t.id, t.name, t.category
from features t, bounds
where t.geom && st_transform(bounds.geom, 4326)
and (%(z)s >= 10 or t.rank <= 100)
)
select st_asmvt(mvtgeom.*, 'features', 4096, 'geom')
from mvtgeom where geom is not null;
"""
@app.get("/tiles/{version}/{z}/{x}/{y}.mvt")
def tile(version: str, z: int, x: int, y: int, request: Request):
if not 0 <= z <= MAX_ZOOM:
raise HTTPException(404, f"zoom {z} is outside 0–{MAX_ZOOM}")
limit = 2 ** z
if not (0 <= x < limit and 0 <= y < limit):
raise HTTPException(404, f"tile {z}/{x}/{y} is outside the pyramid")
with pool.connection() as con:
row = con.execute(TILE_SQL, {"z": z, "x": x, "y": y}).fetchone()
body = bytes(row[0]) if row and row[0] else b""
etag = '"' + hashlib.sha256(body).hexdigest()[:16] + '"'
if request.headers.get("if-none-match") == etag:
return Response(status_code=304, headers={"ETag": etag})
return Response(body, media_type="application/vnd.mapbox-vector-tile",
headers={"ETag": etag,
"Cache-Control": "public, max-age=31536000, immutable"
if version != "live"
else "public, max-age=60"})The bounds check is not pedantry: without it, a client requesting /tiles/v1/3/99/99 runs a query that scans and returns nothing, once per bad request.
Example 2 — measuring tile cost across the pyramid
import statistics
import time
def profile_tiles(pool, sql, samples):
"""samples: [(z, x, y), ...] spread across zoom levels."""
by_zoom = {}
for z, x, y in samples:
with pool.connection() as con:
started = time.perf_counter()
row = con.execute(sql, {"z": z, "x": x, "y": y}).fetchone()
elapsed = time.perf_counter() - started
size = len(bytes(row[0])) if row and row[0] else 0
by_zoom.setdefault(z, []).append((elapsed, size))
print(f"{'zoom':>5} {'n':>4} {'median ms':>10} {'p95 ms':>8} {'median kB':>10}")
for z in sorted(by_zoom):
times = sorted(t for t, _ in by_zoom[z])
sizes = sorted(s for _, s in by_zoom[z])
print(f"{z:5} {len(times):4} {statistics.median(times) * 1000:10.1f} "
f"{times[int(0.95 * len(times)) - 1] * 1000:8.1f} "
f"{statistics.median(sizes) / 1024:10.1f}")Low zoom levels are where the problems are. A median of 800 ms at zoom 4 and 12 ms at zoom 14 is the classic signature of missing generalisation and no feature filter.
Example 3 — pre-simplified geometry columns per zoom band
alter table features
add column geom_z0_7 geometry(Geometry, 4326),
add column geom_z8_11 geometry(Geometry, 4326);
update features set
geom_z0_7 = st_simplifypreservetopology(geom, 0.01),
geom_z8_11 = st_simplifypreservetopology(geom, 0.001);
create index on features using gist (geom_z0_7);
create index on features using gist (geom_z8_11);-- and in the tile query
case when :z <= 7 then t.geom_z0_7
when :z <= 11 then t.geom_z8_11
else t.geom endSimplifying once at load time rather than per tile is the single largest improvement available for a low-zoom tile, and it is exactly what a static tiling pipeline would do.
Explanation
Why the tile query must filter before it transforms
t.geom && st_transform(bounds.geom, 4326) compares the table's geometry against a constant box in the table's own CRS, which the GiST index can answer. st_transform(t.geom, 3857) && bounds.geom transforms every row first, which no index can help with.
The two look interchangeable and differ by orders of magnitude at scale. Transform the bounds, not the table.
Why low zoom is where tile servers fail
A zoom 4 tile covers a quarter of a continent. Every feature in that area is a candidate, and at full resolution their combined geometry is enormous — while the screen shows the tile at 256 or 512 pixels, resolving perhaps a hundredth of it.
So the expensive tiles are the ones showing the least detail. Generalisation and a feature filter attack exactly that, and without them a tile server is fast at the zooms nobody complains about and slow at the ones everybody sees first.
Why ST_AsMVTGeom returns NULL
Geometry that vanishes at the tile's resolution — a polygon smaller than one of the 4,096 units, a line reduced to a point — cannot be represented, so the function returns NULL rather than an empty geometry.
Those rows must be filtered out before ST_AsMVT, or the tile contains features with no geometry. The where geom is not null in the outer query is not optional.
Why the cache is part of the design
A dynamic tile server generates the same bytes for the same tile until the data changes. Without caching it does that work on every request, including for the tiles that receive the overwhelming majority of traffic.
The measured comparison — 1,191 requests per second on the cached path against 100 on the generating path — is why "dynamic tiles" and "cached tiles" should be treated as one decision. A versioned URL prefix plus immutable is the cleanest form: no revalidation at all, and a data update becomes a new prefix.
Edge cases or notes
ST_TileEnvelope(z, x, y)returns EPSG:3857 bounds — transform it back to filter a 4326 table.extentis conventionally 4096; clients assume it unless told otherwise.bufferin tile units — 64 is common, and too small clips features at tile edges.- Filter out NULL geometry before
ST_AsMVT. - An empty tile should be a 204 or an empty body, not an error.
- Bounds-check
xandyagainst2^z, or bad requests run real queries. - Use a connection pool. One connection per tile request exhausts Postgres quickly.
- Pre-simplified columns per zoom band are worth more than any query tuning.
Internal links
- Dynamic tile server or static tiles: what to serve — whether to generate at all
- Vector tiles explained — what is inside the response
- How to build vector tiles in Python — the static route
- How to write spatial SQL queries with PostGIS — the query layer
- PostGIS spatial indexes explained — why the filter order matters
- How to set cache headers on a spatial API and tile service — the caching
- Fixing a tile endpoint that returns 404 or blank tiles — when tiles do not appear
- Zoom and generalisation explained — how much detail each zoom can hold
FAQ
What does ST_AsMVT do?
It aggregates rows into a Mapbox Vector Tile. ST_AsMVTGeom prepares each geometry first — transforming it to tile-local coordinates, clipping it, and returning NULL for anything that vanishes at that resolution.
Why is my tile query slow at low zoom?
Because a zoom 4 tile covers a quarter of a continent and the query is encoding every feature in it at full precision. Simplify for the zoom and filter by importance.
How do I filter efficiently?
Compare the table's geometry against the transformed bounds — t.geom && st_transform(bounds.geom, 4326) — so the GiST index applies. Transforming every row defeats the index.
What buffer and extent should I use?
4096 for the extent, which clients assume, and 64 for the buffer. A smaller buffer clips features visibly at tile boundaries.
Do I need to cache dynamic tiles?
Yes — it is the design, not an optimisation. Measured, a cached conditional response ran at 1,191 requests per second against 100 for generating the body.
Why do some tiles come back empty?
Either no features intersect, or ST_AsMVTGeom returned NULL for everything because the geometry vanished at that resolution. Filter nulls, and return an empty body rather than an error.