Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns.

Users will query subsets of this table for download.

I am rather familiar with the PostgreSQL 9.1 database, and my plan is:

  • The webapp will help the user to create queries like "WHERE a=3 AND b=true AND c>300"
  • The DB will have proper indexes for the most-used columns.

NOW.. I read here: http://stackoverflow.com/questions/10053050/why-is-solr-so-much-faster-than-postgres:

I recently switched from Postgres to Solr and saw a ~50x speed up in our queries. The queries we run involve multiple ranges, and our data is vehicle listings. For example: "Find all vehicles with mileage < 50,000, $5,000 < price < $10,000, make=Mazda..."

So now I wonder: Will/can Solr, Lucene, ElasticSearch, Amazon Cloud Search searches be faster than PostgreSQL even if no full-text search is involved?

share|improve this question

1 Answer

up vote 5 down vote

Will/can Solr/Lucene searches be faster than PostgreSQL even if no full-text search is involved?

Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really.

Solr is a search engine. PostgreSQL is a relational database engine.

Solr is built from the ground up to do one thing well, search. It cannot do the work of a transactional database system. Solr cannot guarantee ACID compliance, it does not efficiently manage concurrency, it is not a viable candidate for OLTP workloads.

PostgreSQL is built from the ground up to do one thing well, transactional processing. It can also do a reasonable job of providing search type facilities but that is not its primary role. It does guarantee ACID compliance, it does manage concurrency, it is not a search engine.

The 4m rows only change once a year.

In which case you probably don't need the key features of a relational database engine.

You need a search engine. Could be Solr, Lucene, ElasticSearch, Amazon Cloud Search or one of a dozens others but first and foremost it will be a search platform, not a relational database.

share|improve this answer
Thanks! I just found stackoverflow.com/questions/5814050/solr-or-database with a similar answer: Use each for what it is best for. – alfonx Feb 3 at 14:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.