[OSM-dev] Patch to osm2pgsql for faster updates

[Tom Hughes]

On 23/01/11 16:24, Erik Burrows wrote:

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

Note that using Postgres 8.4 for mapnik is not recommended as there are 
known to be severe performance issues.

That's why the main tile server is still using 8.3 although as far as we 
know the problem (which was never really identified) has been solved in 
9.0 judging by some limited testing.

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

I wonder if in fact this is the performance problem that we saw using an 
8.4 database. The general consensus was that it was somehow related to 
the GIN indexes but we never managed to work out exactly what it was.

Tom

-- 
Tom Hughes (tom at compton.nu)
http://compton.nu/