[OSM-dev] Patch to osm2pgsql for faster updates

Erik Burrows erik at erikburrows.com
Sun Jan 23 16:24:06 GMT 2011

Hi All,
I was having great difficulty with applying the daily diffs to my
PostgreSQL database (8.4 and 9.0). I would get good performance right
after a vacuum, but after a few hours of updates, it would be running at
less than real-time. After a great deal of experimentation, I found that
the GIN indexes on the ways and rels tables were the cause. 

In PostgreSQL 8.4, the "fastupdate" feature was introduced for GIN


This feature is on by default and delays updates of GIN indexes until
the next vacuum, speeding individual index updates by adding them to a
temporary space. 

I found that this causes slowdowns after a few hours of diffs. This is
likely due to each subsequent index reads/updates having to read through
the entire temporary update space.

If the index is created with "with (fastupdate=off)", applying diffs is
much faster overall, and stable in terms of performance from one diff
application to another.

Attached is a patch to osm2pgsql's middle-pgsql.c that adds the above
term to the GIN index creation statements. 

This command WILL FAIL if used on PostgreSQL databases earlier than 8.4.


-Erik Burrows
-------------- next part --------------
A non-text attachment was scrubbed...
Name: osm2pgsql-nofastupdate.patch
Type: text/x-patch
Size: 3092 bytes
Desc: not available
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20110123/2a1ad8ae/attachment.bin>

More information about the dev mailing list