[OSM-dev] Patch to osm2pgsql for faster updates

[Erik Burrows]

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
indexes:

http://www.postgresql.org/docs/8.4/static/release-8-4.html

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.

Thanks!

-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>