How to Serve Vector Tiles from PostGIS with ST_AsMVT
On this page
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 seconds at low zoom because it is encoding thousands of detailed polygons 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.
What you get — from The data used on this page
(4596, 1295319) features and vertices
Filter first, simplify by zoom, set the buffer
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 a 256-pixel tile resolves 360 / (2⁴ × 256) = 0.088° of longitude 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 against the endpoint in Example 1, answering a conditional request with 304 ran at 188 requests per second against 184 for sending the tile — no faster, because that endpoint's ETag is a hash of the tile, so the server builds the whole tile before it can say "not modified". A 304 saves the transfer. Saving the work takes a cache in front of the endpoint, or an ETag the server can compute without building the tile, such as the data's version.
Add an ETag, a Cache-Control, and — if the map will be public — a CDN.
A pooled, cached tile endpoint over PostGIS
The data used on this page
The features are Natural Earth's 1:10m admin-1 states and provinces — 4,596 polygons with detailed coastlines, which is exactly what makes low-zoom tiles expensive — loaded into a throwaway PostGIS database in EPSG:4326 as features, with the province name, its type as category, and a rank by area (1 is the largest), which stands in for the importance column the low-zoom filter needs.
import io
import subprocess
import time
import zipfile
import geopandas as gpd
import psycopg
import requests
from sqlalchemy import create_engine
if subprocess.run(["docker", "inspect", "guide-postgis"], capture_output=True).returncode != 0:
subprocess.run(["docker", "run", "-d", "--name", "guide-postgis", "-e", "POSTGRES_USER=user",
"-e", "POSTGRES_PASSWORD=guide", "-e", "POSTGRES_DB=gis", "-p", "127.0.0.1:55433:5432",
"postgis/postgis:16-3.4"], check=True, capture_output=True)
for _ in range(120):
try:
with psycopg.connect("postgresql://user:guide@127.0.0.1:55433/gis", autocommit=True) as con:
if not con.execute("select 1 from pg_database where datname = 'ne'").fetchone():
con.execute("create database ne")
break
except psycopg.OperationalError:
time.sleep(1)
DSN = "postgresql://user:guide@127.0.0.1:55433/ne"
with psycopg.connect(DSN, autocommit=True) as con:
con.execute("create extension if not exists postgis")
loaded = con.execute("select to_regclass('features') is not null").fetchone()[0]
if not loaded:
archive = requests.get("https://naciscdn.org/naturalearth/10m/cultural/"
"ne_10m_admin_1_states_provinces.zip", timeout=300)
with zipfile.ZipFile(io.BytesIO(archive.content)) as z:
for ext in (".shp", ".shx", ".dbf", ".prj", ".cpg"):
open(f"ne_provinces{ext}", "wb").write(z.read(f"ne_10m_admin_1_states_provinces{ext}"))
provinces = gpd.read_file("ne_provinces.shp")
area_rank = provinces.to_crs(6933).area.rank(ascending=False, method="first").astype(int)
features = gpd.GeoDataFrame({"id": range(1, len(provinces) + 1), "name": provinces["name"],
"category": provinces["type_en"], "rank": area_rank},
geometry=provinces.geometry, crs=4326).rename_geometry("geom")
features.to_postgis("features", create_engine(DSN.replace("postgresql://", "postgresql+psycopg://")))
with psycopg.connect(DSN) as con:
con.execute("create index on features using gist (geom)")
with psycopg.connect(DSN) as con:
print(con.execute("select count(*), sum(st_npoints(geom)) from features").fetchone(), "features and vertices")(4596, 1295319) features and vertices
Example 1 — the endpoint, with pooling, caching and bounds checks
%%writefile vt_service.py
import hashlib
from fastapi import FastAPI, HTTPException, Request, Response
from psycopg_pool import ConnectionPool
app = FastAPI()
pool = ConnectionPool("postgresql://user:guide@127.0.0.1:55433/ne", 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.
The ETag here is a hash of the tile — which is correct, and has a cost the measurement below shows. Started under uvicorn, the endpoint can be asked for one tile repeatedly — generating it each time, then answering with 304 once the client sends the ETag back:
import socket
import sys
import httpx
with socket.socket() as probe:
probe.bind(("127.0.0.1", 0))
port = probe.getsockname()[1]
server = subprocess.Popen([sys.executable, "-m", "uvicorn", "vt_service:app", "--port", str(port),
"--log-level", "warning"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
BASE = f"http://127.0.0.1:{port}"
for _ in range(200):
try:
httpx.get(BASE + "/docs", timeout=1); break
except httpx.TransportError:
time.sleep(0.1)
try:
url = BASE + "/tiles/v1/10/511/340.mvt" # London
etag = httpx.get(url).headers["etag"]
for label, headers in (("generating the tile", {}), ("304 not modified", {"If-None-Match": etag})):
with httpx.Client() as session:
n, started = 0, time.perf_counter()
while time.perf_counter() - started < 3:
response = session.get(url, headers=headers)
n += 1
seconds = time.perf_counter() - started
print(f"{label:20} {n / seconds:7.1f} req/s, {seconds / n * 1000:6.1f} ms, {len(response.content):7,} bytes")
print("out of range:", httpx.get(BASE + "/tiles/v1/3/99/99.mvt").status_code)
finally:
server.terminate(); server.wait()generating the tile 184.1 req/s, 5.4 ms, 3,995 bytes
304 not modified 188.2 req/s, 5.3 ms, 0 bytes
out of range: 404
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}")Profiled on tiles over London, Berlin, Madrid and Rome at each zoom, first with no simplification and no feature filter, then with Example 1's rank filter:
import mercantile
from psycopg_pool import ConnectionPool
pool = ConnectionPool(DSN, min_size=1, max_size=2, open=True)
PLACES = [(-0.13, 51.51), (13.40, 52.52), (-3.70, 40.42), (12.50, 41.90)]
SAMPLES = [(t.z, t.x, t.y) for z in (4, 6, 8, 10, 12, 14)
for t in {mercantile.tile(lon, lat, z) for lon, lat in PLACES}]
PLAIN = """
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))
select st_asmvt(mvtgeom.*, 'features', 4096, 'geom') from mvtgeom where geom is not null"""
RANKED = PLAIN.replace("where t.geom && st_transform(bounds.geom, 4326))",
"where t.geom && st_transform(bounds.geom, 4326)\n and (%(z)s >= 10 or t.rank <= 100))")
for label, sql in (("no simplification, no filter", PLAIN), ("rank filter below zoom 10", RANKED)):
print(label)
profile_tiles(pool, sql, SAMPLES)no simplification, no filter
zoom n median ms p95 ms median kB
4 3 43.5 43.5 70.6
6 4 8.7 8.8 13.6
8 4 3.1 4.6 1.3
10 4 2.3 4.0 0.3
12 4 2.0 3.6 0.1
14 4 1.9 3.4 0.1
rank filter below zoom 10
zoom n median ms p95 ms median kB
4 3 3.8 3.8 0.0
6 4 1.8 3.5 0.0
8 4 1.9 3.5 0.0
10 4 2.3 4.1 0.3
12 4 2.1 3.8 0.1
14 4 2.0 3.7 0.1
Low zoom levels are where the cost is: the zoom-4 tiles took twenty times as long as the zoom-14 ones and were hundreds of times the size. The rank filter "fixed" that by emptying them — none of the hundred largest provinces lies in the sampled tiles over London, Berlin, Madrid or Rome, so western Europe vanished below zoom 10. An importance column has to mean importance for the map being drawn; area rank is the wrong one for a map of Europe.
Example 3 — pre-simplified geometry columns per zoom band
PRESIMPLIFY = """
alter table features
add column if not exists geom_z0_7 geometry(Geometry, 4326),
add column if not exists 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 if not exists features_z0_7 on features using gist (geom_z0_7);
create index if not exists features_z8_11 on features using gist (geom_z8_11);
"""
with psycopg.connect(DSN) as con:
con.execute(PRESIMPLIFY)
print(con.execute("select sum(st_npoints(geom)), sum(st_npoints(geom_z8_11)), sum(st_npoints(geom_z0_7)) "
"from features").fetchone(), "vertices: full, z8-11, z0-7")(1295319, 1058347, 383858) vertices: full, z8-11, z0-7
The zoom 0–7 total moves by a vertex between runs on this PostGIS 3.4 build (GEOS 3.9): with parallel query workers the same st_simplifypreservetopology call returned 383,858 and 383,859 on one table, and with max_parallel_workers_per_gather = 0 it returned 383,858 every time.
-- 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 endBANDED = PLAIN.replace("st_asmvtgeom(st_transform(t.geom, 3857)",
"st_asmvtgeom(st_transform(case when %(z)s <= 7 then t.geom_z0_7 "
"when %(z)s <= 11 then t.geom_z8_11 else t.geom end, 3857)")
print("pre-simplified columns, no feature filter")
profile_tiles(pool, BANDED, SAMPLES)pre-simplified columns, no feature filter
zoom n median ms p95 ms median kB
4 3 12.7 12.7 32.5
6 4 3.0 3.2 5.8
8 4 2.5 4.0 1.1
10 4 2.0 3.5 0.2
12 4 2.0 3.6 0.1
14 4 1.9 3.5 0.1
Simplifying 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.
Why low zoom is where tile servers fail
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.
FILTERS = {"transform the bounds": "t.geom && st_transform(bounds.geom, 4326)",
"transform every row": "st_transform(t.geom, 3857) && bounds.geom"}
with psycopg.connect(DSN, autocommit=True) as con:
for label, where in FILTERS.items():
try:
started = time.perf_counter()
for _ in range(20):
n = con.execute(f"with bounds as (select st_tileenvelope(10, 548, 335) as geom) "
f"select count(*) from features t, bounds where {where}").fetchone()[0]
print(f"{label:22} {n} rows, {(time.perf_counter() - started) / 20 * 1000:7.2f} ms")
except psycopg.Error as exc:
print(f"{label:22} failed: {str(exc).splitlines()[0]}")
pool.close()transform the bounds 3 rows, 0.56 ms
transform every row failed: transform: tolerance condition error (-20)
Transforming every row did not just lose the index — it failed. Natural Earth's Antarctic provinces reach latitude −90°, which Web Mercator cannot represent, and the query transforms every row whether or not it is anywhere near the tile. Transforming the bounds touches only the rows the index returns. 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 — 188 requests per second for a 304 against 184 for a full response, because the ETag still required building the tile — 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.
Tile envelopes, extents and empty tiles
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
xandyagainst, 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 304 from an endpoint that hashes the tile to make its ETag ran no faster than sending the tile — 188 against 184 requests per second — because the tile was still built. Cache in front, or derive the ETag from the data version.
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.