4

I have static road, country and state datasets in my PostGIS database. Road networks are partitioned by their containing country. Countries, States and other areas reside in their own table and are partitioned by 'version' or 'release'. All spatial data is fully indexed with GiST on all geometries.

The data is used only for look ups (read-only), with updates being performed periodically as a full database refresh.

All nearest-neighbour queries require a minimum of two spatial queries. One to derive the country where the query is located - which allows me to select the correct partition table. Then a second spatial query performs the actual NN search.

Assuming that spatial queries like nearest-neighbour are O(n log n) then partition tables should result in improved performance. That should be true if the spatial query which finds the correct partition table doesn't take longer than the query on an unpartitioned table.

I have been told that partition tables always the way to go when working with global road networks, but to me I think surely it can be just as, or even more efficient to lump all the world's roads into a single PostGIS table.

In what situations should partition tables be used for spatial data?

closed as primarily opinion-based by Vince, John Powell, Mapperz Jun 2 '16 at 13:42

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    The effectiveness of partitioning is highly dependent on the data, the queries, and the platform. As it stands, this is an opionion-based question, since there isn't really and way to answer conclusively. Have to tried bechmarking your system under load, with and without partitions? Have you reviewed the resulting query plans? – Vince Jun 2 '16 at 4:15
  • 1
    How many records are in the full table? Do you have a GiST spatial index? – Mike T Jun 2 '16 at 4:43
  • Please take the Tour where you will see that there should be only one question asked per question. – PolyGeo Jun 2 '16 at 22:42
2

Partitioning is most beneficial when you can couple it with a constraint that will allow the planner to exclude certain child tables entirely from most queries. Unfortunately, this currently isn't possible to do with a spatial constraint, but you might be able to take advantage of it by partitioning roads by country or state, and including the state or country in every query. The PostgreSQL docs describe how to do this.

There is a practical limit on the number of child tables that you'll want to associate with a parent table. I once tried partitioning a nationwide (US) parcel dataset by county and found that the increase in query planning time (from the complexity of ~3000 child tables) caused an decrease in overall query performance, even though query execution time was faster.

  • Thank you. That is useful information. Splitting the USA into States/Territories could be beneficial as I would only have 50-55 child tables, but I suppose splitting all countries into states would result in thousands of child tables increasing query time. Good to know. – jase81 Jun 2 '16 at 21:49
1

One other hint is to look at the logfiles for temporary files as spatial queries are sometimes very large memory wise.

You can log queries that run longer than for example 2seconds (log_min_duration_statement = 2000ms) and check if there are temporary files generated (log_temp_files =0). If you see any temporary files you can set your working memory higher.

You can start with 64MB here(work_mem = 64MB), or you can try to set it 2 times higher than the biggest temporary file you found in the log. But that can fill up your memory fast as this counts per single planner node.

The second tuning can be done to the shared_buffers that you can try and set to 20% of your systems memory.

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