Question on using ST_AsMVT for huge dataset

[yilin-ang]

Hi, I am building a vector tile server using ST_AsMVT and ST_AsMVTGeom to generate MVTs on the fly performantly. I am working with datasets of ~2 million polygon features tightly clustered on the map (high feature density). I am struggling to optimise the MVT generation for the front-end to (1) render the tiles and (2) render within a reasonable time.

I have already created RTREE index on the table and this is my current SQL to generate the MVT. Appreciate any advice and ideas for this!

WITH filtered_data AS (
      SELECT
          *
      FROM table
      WHERE ST_Intersects(
          geometry,
          ST_Transform(ST_TileEnvelope({z}, {x}, {y}), 'EPSG:3857', 'EPSG:4326', true)
      )
  )
  SELECT ST_AsMVT(tile_data, 'mvt-layer')
  FROM (
      SELECT
          ST_AsMVTGeom(
              ST_Transform(geometry, 'EPSG:4326', 'EPSG:3857', true),
              ST_Extent(ST_TileEnvelope({z}, {x}, {y})),
              4096,
              256,
              false
          ) AS geometry,
          CAST(COLUMNS(c -> c NOT IN ('geometry', 'geometry_3857', 'extent_3857')) AS VARCHAR)
      FROM filtered_data
  ) AS tile_data
  WHERE NOT ST_isEmpty(geometry)