[OSM-dev] Too many slow queries in db

Brett Henderson brett at bretth.com
Tue Sep 4 09:08:15 BST 2007

I like the idea of trialling alternative databases.  But we need a way 
of assessing their suitability.

Can we build a list of possible improvement areas over current 
database?  This would less any alternatives be measured quantitively.  
For example:
* The current top 10 slow queries.  Assuming an alternative schema still 
follows the nodes, segments, ways model this would let us manually run 
queries against alternative implementations without porting tools.
* Speed to build indexes.
* Ability to avoid or recover from corruption.
* Ability to partition data.  The physical ways the data can be split 
(across disks or servers) and logically how the data is partitioned 
(chronological time, primary key range, or geo-spatial location) are of 

For example, Oracle (most of my past experience is using Oracle) has a 
number of features that make it desirable for systems like this.  
Features such as clustering, partitioned tables (and indexes) and online 
index rebuilds would all be very useful.

Hopefully open source databases can provided some or all of these 
features as well but this requires research and testing.  For example, 
MySQL supports table partitioning but doesn't appear to support online 
index rebuilds (perhaps I'm wrong).  I know very little about postgresql 
or other alternatives.

I would love to see somebody create some alternate schemas for different 
database engines and test some of this out.  If nobody else does it I'll 
do it one day but I won't get there for a while, I have a long list of 
feature requests already with osmosis.  However I am happy to help out 
with data load processing if required.

Raphael Jacquot wrote:
> Stefan de Konink wrote:
>> About the TIGER import: you are completely right the import is no problem.
>> Like the Dutch/AND import it will add more to the database in a time with
>> no time to grow.
>> If what you say is right you are not really reaching hardware limits, just
>> software that isn't smart enough there is ofcourse Oracle and PostgreSQL
>> maybe if someone really wants to try something big: http://monetdb.cwi.nl/
>> (although my suggestion on this one would be: test before production).
> been saying that for a while

More information about the dev mailing list