[OSM-dev] osm2pgsql slow on update import

Jukka Rahkonen jukka.rahkonen at latuviitta.fi
Tue May 3 14:28:20 BST 2011

> Sven Geggus <lists at fuchsschwanzdomain.de> wrote:
>> The initial import is very fast (with pbf) and still fast (with
>> .osm.bz2).
>> It is just updates which are very slow here.
> Hm, I just verified this. The import is fast enough (about 10 hours) but I
> did expect it to be even faster.
> What are the expected values here?

I am pretty lazy nowadays with updates and when I update once per month or
so I am running full import of finland.osm.bz2 into another database and
when it is finalized I am changing the database connection strings of my
clients. It works for me but smaller and smaller percentage of the
database contents is changing monthly and most part of the 135 Mb import
is unnecessary.

I have been thinking that when I have the old and new imported database
versions available, perhapsa I could somehow generate diff files for
helping other people to keep their databases up-to-date. I would imagine
that the system should compare osm_point, osm_line and osm_polygon tables
and create a reasonable set of inserts, updates and deletes.

Does this sound reasonable at all or would it still be faster to
syncronise with the regular OSM diff files? I believe that we have lots of
users who are using the Simple Feature version of OSM database created by
osm2pgsql and do not need the routability that is lost with the osm2pgsql
import. All the tags can be preserved in the hstore column so not much
data loss should appear. Except some relations; has anybody tried to
convert OSM relations into Simple Feature GeometryCollections which can
hold a mixture of points, lines and polygons?

-Jukka Rahkonen-

More information about the dev mailing list