1

I need to increase the rendering speed of pg_tileserv for a leaflet map.

I am running pg_tileserv on a digital ocean server with

2cpus, 4GB memory, 25 GB SSD and 4TB transfer

I am serving fairly dense parcel data county and state wide.

I know I can set a min zoom level on the front end in leaflet or in pg_tileserv config files but I need the parcels to be seen at least to zoom 14 on the map.

To serve up the larger parcel sets right now I generating the mbtiles with tippecanoe then serving them with tileserver-gl...its working fairly good but this parcel updates frequently and it takes time to export, generate the tiles and spin up the tleserver-gl

I'd like to avoid using geoserver, had a load of trouble trying to figure out how to setup .pbf on geoserver and I gave up.

Is there anything I can do in the postgres settings to increase rendering speed? and tips to tune pg_tileserv? or increase server transfer speed?

3

Building off what @robin loche and @Paul Ramsey mentioned I threw together a crude example of a function that assesses the map zoom level and weeds out features that based on square footage. For the function below if the map zoom Z level is 14 or greater (zoomed in) then any geometry with a square footage of 100 or greater will be displayed. if its zoom levels 1-13 then anything under 10000 square feet will not be displayed. One can add different Z level square footage logic depending on their use case and data. So far it works fairly quick and I'm happy

CREATE OR REPLACE
FUNCTION public.zoom_removal(z integer, x integer, y integer)
RETURNS bytea
AS $$
DECLARE
    result bytea;
BEGIN
    WITH
    bounds AS (
      SELECT ST_TileEnvelope(z, x, y) AS geom,
     (CASE 
        when z >= 14 then 100           
        ELSE 10000 END
       ) as min_area
    ),
    mvtgeom AS (
      SELECT 
        ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom,
        t.parcelid,t.sqft
      FROM oh.open_parcels t join bounds
      on t.geom && st_transform(bounds.geom,3735)
      where t.sqft > bounds.min_area
    )
    SELECT ST_AsMVT(mvtgeom, 'public.zoom_removal')
    INTO result
    FROM mvtgeom;
    RETURN result;
END;
$$
LANGUAGE 'plpgsql'
STABLE
PARALLEL SAFE;
2

The number one thing you can do is ensure your version of PostGIS is as recent as possible, since pg_tileserv doesn't actually do any rendering at all, it just converts HTTP requests into SQL. It may just be that slamming N-thousand things into a tile takes a while.

The number two thing you can do is place a caching proxy in front of your tile server, so that those huge renders where almost every parcel goes into one tile are only run one time per caching period. This means one user gets a slow render and all the other users get the cached version. With reasonable caching periods, this can be a nice compromise between "live" and "pre-rendered" set-ups. It's also just a best practice in general, as it avoids a slug of web traffic bringing your database server to its knees. Going whole hog and using a CDN can also be a big win, particularly for public-facing web sites.

1
  • updated postgis and looking into proxy servers. thanks for the advice
    – ziggy
    Nov 16 at 15:51
1

I fully agree with the caching suggested, it's almost always necessary for a production environment.

As another improvment idea, maybe you can try to make a function to generate your tiles. Pg_tileserv is good and easy for serving small table, but for big tables I think it's often better to make it serve a function instead of the table directly.

For example you can filter what you want to display depending of the zoom level (filtering on the client side does not help if the tile is still generated) or even use simplified version of your geometries, preferably pre-generated on another table, and select the most adequate table depending on the zoom level.

8
  • interesting idea..so you're saying in postgres have pre-created tables for different zoom levels? and a function that will select those tables based on zooms from the frontend?
    – ziggy
    Nov 16 at 16:38
  • Yes, a zoom-level-aware function is a great way to avoid slugging 50K parcels into a tile. If you have something that "makes sense" at higher scales, having your function swap between different raw sources is a great trick. Nov 16 at 18:15
  • 1
    @ziggy yes, if you didn't know pg_tileserv can serve tables or functions if they are in the good format (look at the end of the github readme for an exemple). You can use the z parameter to select the most adequate of your pre-generated tables. Nov 17 at 10:43
  • Also, little trick with cache, you can add another parameter (let's say 'version') that doesn't do anything inside your function, but that you can change on your client side in case you have a new version of your postgis table(s). This will make the request different, so there will be no cache yet... It's sometimes more easy than resetting the cache server manually. Nov 17 at 10:43
  • @robinloche so with the z parameter..how do i correlate that to any area? any examples of what you suggested
    – ziggy
    Nov 27 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.