[OSM-dev] Advantages PostgreSQL 10.0 + PostGIS 2.4

Paul Norman penorman at mac.com
Mon Oct 9 22:17:44 UTC 2017


On 10/8/2017 11:03 AM, Sven Geggus wrote:
> Hello,
>
> Debian stable 9.x comes with PostgreSQL 9.6 and Postgis 2.3.
>
> However the now released PostgreSQL 10.0 and Postgis 2.4 are an easy
> backport.
>
> Will the newer version provide any advantage for a osm2pgsql database use case?

Yes, but nothing game-changing for a osm2pgsql database and a standard 
production workload according to the release notes. There's a lot of 
improvements related to replication. The following are likely to help

- Faster GiST index inserts and updates
- Faster GIN index vacuuming
- General performance improvements

The parallel b-tree index usage could help updates, but this is seldom 
an issue, and may even be undesirable under heavy rendering load. I've 
also seen no data on if it helps or not for the particular queries 
osm2pgsql runs.

In short, I'd recommend the upgrade for any new deployments, but it's 
not likely to be worth an out of schedule reload.

If you're doing analysis with an osm2pgsql database, it's likely to be 
more useful. Query-level parallelism is more useful there because 
there's generally not so many connections running in parallel at the 
application level. This makes the parallelism improvements useful, if 
they apply to the types of queries that are being run.

The multi-column statistics have the potential to be interesting for 
analysis queries.

If upgrading to 10.x, make sure to set the parallel configuration 
options if you want to see those performance gains.

PostGIS 2.4 is a similar situation. Stylesheets are unlikely to require 
it yet, so it won't enable anything new for rendering servers for some 
time. If a new function helps with your analysis use-case, it will be 
useful.





More information about the dev mailing list