[OSM-dev] Patch to osm2pgsql for faster updates

Erik Burrows erik at erikburrows.com
Sun Jan 23 16:28:11 GMT 2011


Corrected patch attached (without my custom change to default.style).

> 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
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev

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


More information about the dev mailing list