performance - Full-text search in Postgres or CouchDB?

Question

I took geonames.org and imported all their data of German cities with all districts.

If I enter "Hamburg", it lists "Hamburg Center, Hamburg Airport" and so on. The application is in a closed network with no access to the internet, so I can't access the geonames.org web services and have to import the data. :( The city with all of its districts works as an auto complete. So each key hit results in an XHR request and so on.

Now my customer asked whether it is possible to have all data of the world in it. Finally, about 5.000.000 rows with 45.000.000 alternative names etc.

Postgres needs about 3 seconds per query which makes the auto complete unusable.

Now I thought of CouchDb, have already worked with it. My question:

I would like to post "Ham" and I want CouchDB to get all documents starting with "Ham". If I enter "Hamburg" I want it to return Hamburg and so forth.

Is CouchDB the right database for it? Which other DBs can you recommend that respond with low latency (may be in-memory) and millions of datasets? The dataset doesn't change regularly, it's rather static!

Answer 1

If I understand your problem right, probably all you need is already built in the CouchDB.

1. To get a range of documents with names beginning with e.g. "Ham". You may use a request with a string range: startkey="Ham"&endkey="Hamufff0"
2. If you need a more comprehensive search, you may create a view containing names of other places as keys. So you again can query ranges using the technique above.

Here is a view function to make this:

function(doc) {
    for (var name in doc.places) {
        emit(name, doc._id);
    }
}

Also see the CouchOne blog post about CouchDB typeahead and autocomplete search and this discussion on the mailing list about CouchDB autocomplete.