[OSM-dev] PostgreSQL 9.4 benchmarking results

Paul Norman penorman at mac.com
Thu Feb 5 08:16:27 UTC 2015

I recently compared the osm2pgsql performance of PostgreSQL 9.3 and 9.4 
with my standard import workload and consuming updates and found 9.4 
offered a solid performance improvement.

The 130904 planet PBF was imported on a SSD-based i7-4700 machine with 
32GB RAM with PostgreSQL 9.3.5 and 9.40. PostgreSQL was tuned in 
accordance with 
except full_page_writes was not set. osm2pgsql options were 
--number-processes 8 --flat-nodes --slim --cache 20000. Hourly diffs 
from 000008555 to 000008854 were consumed in blocks of 100 with a 10 
second sleep between each diff. osm2pgsql options were 
--number-processes 8 --flat-nodes --slim --cache 2000.

Initial import time decreased by 2.2% from 7 hours 31 minutes to 7 hours 
21 minutes. The time to create the large ways nodes GIN index decreased 
by 6% from 3 hours 31 minutes to 3 hours 18 minutes. The time to perform 
the other parts of the import did not change significantly.

Total update time for 100 hours of diffs decreased by 2.5% from 4 hours 
8 minutes to 4 hours 1 minute. It was not possible to separate by 
osm2pgsql stage.

The size of the ways nodes GIN index after importing and updating 
remained at 100GB, which was not the expected result. GIST geometry 
index sizes differed slightly, but GIST index creation is 
non-deterministic and the variations were well within the bounds of what 
is observed by simply reindexing repeatedly.

The latest (150202) planet has increased in size by 25%, so import times 
would be expected to increase by the same for both 9.3 and 9.4. The size 
of 28 daily diffs has increased in size by 33% from 1133 MiB to 1512 
MiB, so update times would be expected to similarly increase.

The system used for benchmarking has significantly faster random IO than 
many. Most systems, particularly those with mechanical HDDs, would have 
a greater relative and absolute speed increase than this. It would have 
saved 1 to 3 hours on one of my servers.

When not updating --drop should be used for increased speed. These 
imports do not generate the GIN index and would be no faster with 9.4.

Although a rendering stack can run on versions as old as PostgreSQL 8.4 
and 1.5, a minimum of 9.1 + 2.0 is suggested, with gains seen from 
9.3+2.1.1, and this combination is successfully run on many production 
servers. I will be recommending 9.4+2.1 for new installs.

For Debian based OSes, including Ubuntu, 9.4 is packaged at 
apt.postgresql.org with packages that are drop-in replacements for the 
system ones.

More information about the dev mailing list