Skip to content

PATTERN Cited by 1 source

Database as tile server (middleware replacement)

Problem

Web maps over large geospatial datasets cannot ship the full dataset to the browser — "over 100,000 polygons… would slow down map navigation a lot" (Source: sources/2021-12-01-zalando-maps-with-postgresql-and-postgis). The industry answer is to tile: split the data into a grid of fixed-size tiles indexed by (zoom, x, y), and have the client fetch only the tiles currently in the viewport. At different zoom levels the geometry should be generalised — coarser lines, fewer features — so the detail shown matches the display scale.

Historically this required a bespoke tile-pipeline middleware service: a component that built the tile grid, generalised geometry per zoom level, encoded tiles (MVT / PBF / raster), optionally cached them, and served them over HTTP. Teams were responsible for the tile grid invariants, the encoding, the zoom-level generalisation, the tile-regeneration pipeline when underlying data changed, and the deployment topology — all as a separate service alongside the spatial database.

Pattern

Put tile production into the database itself via an extension, and run only a thin HTTP shim in front of it to translate (z, x, y) tile-coordinate requests into database calls.

The shim doesn't render geometry, doesn't cache (or delegates caching to a CDN in front), doesn't know the schema beyond the URL contract:

/{schema}.{object}/{z}/{x}/{y}.pbf

where {object} is either a spatial table (the shim auto-generates a tile query from its geometry column and returns the MVT blob) or a user-defined function in the database with the signature (z integer, x integer, y integer [, ...]) RETURNS bytea.

Function layers let the database compute dynamic, derived tiles — grids, heatmaps, spatial aggregations — whose geometry adapts per-tile because the tile envelope is a parameter of the query.

Zalando's instantiation

The canonical instance of this pattern on the wiki:

  • Database: PostgreSQL 14 with the PostGIS extension. Tile-encoding functions (ST_AsMVT, ST_AsMVTGeom, ST_TileEnvelope) and grid-generation helpers (ST_HexagonGrid) live inside the database.
  • Thin HTTP shim: systems/pg-tileserv from Crunchy Data. TOML-configured, runs as a single Kubernetes Deployment behind Zalando's oauth2-proxy; no tile logic of its own.
  • Database deploy: Managed by the Postgres Operator; PostGIS pre-compiled into the Spilo Docker image; declaratively installed into a schema via preparedDatabases.<db>.extensions.postgis: <schema> on the Postgresql custom resource.
  • Client: Leaflet's VectorGrid over an OpenStreetMap basemap.

Two worked tile layers in the post:

  1. Table layer — geo.boundaries_europe: ~100k NUTS administrative polygons served as MVT directly from the table.
  2. Function layer — geodata.population_hexagons(z, x, y, step): a PL/SQL function that takes the step parameter as a heatmap-granularity knob, computes (ST_XMax(envelope) − ST_XMin(envelope)) / pow(2, step) as the hex size, generates the grid via ST_HexagonGrid, LATERAL-joins against a Eurostat 1 km² population grid (reprojected via ST_Transform from WGS84 to Web-Mercator), aggregates popcount per hex, and encodes the result as MVT — so the heatmap adapts to zoom level because the hex size scales with the tile envelope size.

"these days PostGIS can take over most of the middleware's job and produce map tiles for you. You only need a lightweight server between the frontend that takes in requests from the map and sends queries to your spatial database to produce the tiles you want." (Source: sources/2021-12-01-zalando-maps-with-postgresql-and-postgis.)

Forces and trade-offs

Benefits:

  • One fewer service to operate: tile pipeline lives in the database, shim is ~1 container.
  • Live tiles: no pre-render / invalidation pipeline — tiles reflect the current database state. Function layers let interactive aggregations (dynamic hexagon heatmaps, on-the-fly spatial joins) ship as tiles without separate materialisation.
  • Operational reuse: database HA, backups, upgrades, authZ apply to the tile pipeline too.

Costs:

  • Database CPU pays for tile encoding and per-tile geometry computation. Function layers over large datasets can become expensive at small zoom levels (large envelopes → more hexes, larger joins). Needs tile caching in front (CDN) and careful indexing.
  • Shared-fate: tile traffic shares the database with OLTP workload. A spike in map panning can affect transactional latency if not isolated (read replica, QoS).
  • Schema/function changes are user-visible through the URL contract — function layer signatures are part of the API.
  • Authorisation is coarse by default (tile shim exposes all configured schema objects); fine-grained per-layer authZ needs extra work.

Generalisation

Not Postgres-specific: any spatial database with per-tile geometry-generation functions can host this pattern. The key primitives are:

  1. Tile-envelope helper (ST_TileEnvelope on PostGIS).
  2. Tile-geometry clipper (ST_AsMVTGeom).
  3. MVT encoder (ST_AsMVT) returning an opaque blob.
  4. A user-defined-function mechanism so derived tiles (grids, heatmaps, aggregations) can be authored without adding pipeline code.

This is itself a specialisation of the broader patterns/postgres-extension-over-fork pattern — the database gains a new domain-specific capability via its extension API rather than via a companion service. systems/postgis is a canonical third-party instance; the tile-serving sub-pattern shows the capability is deep enough to displace a category of middleware.

Seen in

  • sources/2021-12-01-zalando-maps-with-postgresql-and-postgis — first wiki canonical instance. Zalando Postgres Operator team (Nikolai Averkiev, 2021-12-01) documents the architectural collapse: bespoke tile middleware → PostGIS + pg_tileserv. Both table-layer and function-layer tiles shown; zoom-dependent hex-grid heatmap as the canonical derived-tile example.
Last updated · 501 distilled / 1,218 read