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?