I'm developing a web application where PostGIS will be used as a vector tile server backend to serve mvt tiles. For each user of the application at least one vector layer will be created. So, potentially there are going to be thousands of layers. Since these layers will be used to serve tiles, read-queries performance is rather critical. My question is, whether I should store all geometries in a feature table with a foreign key referencing layer table, or should I have a separate table for each layer, eg. layer_1, layer_2, etc.

The second approach feels more natural and common for relational databases. However, I have arranged a little experiment, importing 5000 layers to a single table and saw that there is a significant fall in performance.

What is the general approach to this problem? Searched the web but couldn't find the answer.

  • 1
    In your single table experiment, have you indexed some fields, for example those used as filters in queries ? Apr 5 '19 at 15:37
  • Yep, this is what I did: CREATE INDEX geometry_gix ON feature USING GIST (geometry); CREATE INDEX layer_id_fkey ON feature (layer_id); VACUUM ANALYZE feature; Apr 5 '19 at 16:01
  • 1
    Here some other hints for tuning a PostGIS database : postgis.net/workshops/postgis-intro/tuning.html Apr 5 '19 at 17:24
  • It depends what kind of queries you are going to run, ie, but in general I would put each geometry and its attributes in one table, indexed by some primary key and a spatial index. This will be easier to maintain and you won't take any performance hit from spatial joins. Ultimately, it is hard to tell without knowing what kind of queries you might be running, in terms of joins across tables, but as a general rule of thumb, separate tables for separate entities is the way to go. Apr 5 '19 at 18:21
  • I guess that clustering by user layers could suit very well for your use case postgresql.org/docs/9.1/sql-cluster.html.
    – user30184
    Apr 5 '19 at 18:31

Your Answer

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

Browse other questions tagged or ask your own question.