[OSM-dev] Patch to osm2pgsql for faster updates
erik at erikburrows.com
Sun Jan 23 16:24:06 GMT 2011
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
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.
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 3092 bytes
Desc: not available
More information about the dev