Fast Dynamic MVT generation

Question

I am trying to migrate from serving static MVT to generated on the backend by PostGIS with ST_AsMVT(). Or find a way of minimizing the time of building MVT from the database.

I have a dataset of political, historical maps, and I am serving them as prerendered Mapbox Vector Tiles. Dataset itself is not massive. It's 2300 entities for 200 years ~ 1Gb of data. As a result, distant zoom tile (/1/0/0) has ~ 600 geometries. They are filtered on client side with filter option by year in MVT style. We want to introduce an ability for the end-user to upload their maps.

Pipeline to produce an MVT tileset that we are using right now:

1. Loop over geometry in the database and dump it in a file as a GeoJSON feature (feature collection does not fit into memory)
2. Process output with tippecanoe and zoom up to 10
3. Serve file.mbtiles with mbtiles-server

It took around 30 minutes and full CPU load for the whole cycle.

As one way of optimization here - we could rebuild only the part that was changed and update tileset with tile-join

---

I've tried to serve MVT directly from PostGIS with ST_AsMVT(), Geometry is in SRID:4326 queries were executed with similar simplification level and against the same bounding box /1/0/0

Full queries and explanation are here

• The straight-forward approach of MVT generation took 130-177 seconds
• Simplification after transforming to SRID:3857 (WebMercator) took 30 seconds
• Simplification before processing to SRID:3857 took 3 seconds

-- three seconds to execute
SELECT ST_AsMVT(a, 'stv')
FROM (
  SELECT
      api_spacetimevolume.id
      , api_spacetimevolume.start_date::INTEGER
      , api_spacetimevolume.end_date::INTEGER
      , api_spacetimevolume.references
      , ST_AsMVTGeom(
          ST_SnapToGrid(
            ST_Transform(
              ST_Simplify(
                  api_spacetimevolume.territory
                , 0.44 -- Dynamic based on zoom level
              )
              , 3857
            )
            , 1
          )
          , TileBBox(1, 0, 0)
      ) as territory
      , api_spacetimevolume.entity_id
      , api_territorialentity.wikidata_id
      , api_territorialentity.color
      , api_territorialentity.admin_level
  FROM api_spacetimevolume
  JOIN api_territorialentity
  ON api_spacetimevolume.entity_id = api_territorialentity.id
  WHERE territory && TileBBox(1, 0, 0, 4326)
) as a
;

Even with 3 seconds per tile, it is obvious that it should be cached on disk or in the database. Temporary caching (nginx or redis) will not solve the problem completely.

In this article Michal Zimmerman is generating tiles in advance by PostGis and storing them on disk.

---

Well, the questions

1. What do you recommend to speed up the tile delivery?
2. What is the best way to keep the tile generation flexible?

Answer 1

I can only hint at question one, but according to this arctile https://carto.com/blog/MVT-mapnik-vs-postgis/

"We may also consider removing the ST_Simplify and ST_SnapToGrid as they aim to facilitate raster rendering and it’s done by the MVT encoding mechanism."