Saturday, June 23, 2018

Search vs Database : Do I need a search engine ?

Since the beginning of time, applications have been developed with a database at backend to store application data.

Relational databases like Oracle, Mysql etc took databases to the next level with the relation model, transaction, SQL. These are hugely successful for the last 30+ years.

In the last 10+ years, Big data databases like HBase, Cassandra, MongoDb etc arrived to solve data at scale issues which was not handled by the relational databases. These databases handled scale, high availability and replication better than relational database.

In the last 10 years, also available are search engines like Apache Solr and ElasticSearch that also store your data like a database, but offer much better search and analytics than a traditional database.

So when do you use a database and when to use a search engine ? This is what is discussed in this blog. Or do you need both ?

Some differences between a database and search engine are :

1.0 Indexes

In a database, to search efficiently, you define indexes. But then you are required to search based on index key. If you search with some other fields, the index cannot be used and the search is inefficient.

A search engine by default will index by all fields. This gives tremendous flexibility. If you add a new type of search to your application, you do not need a new index.

2.0 Full text search

A search engine excels at full text search.

Say you have document one with line "Hello from england".
And another document with line "Hello from england and europe".

A search for the term "england" will return 2 documents. A search for term "europe" will  return second document.

Databases on the other hand are more convenient for exact value search.

3.0 Flexible document format

Databases are limited in the structure of data - such row and columns or key/value pairs.

Search engines generally consume a wider variety of documents. While json is the most popular format for documents that a search engine consumes, third party libraries are available to parse word docs, pdfs etc for consumption by search engines.

4.0 Analysis and Mapping

Every document stored in a search engine goes through a process of analysis and mapping.

So if you store a document "the Hello 21 from England on 2018-06-15 *", it make get tokenized based on space, certain tokens like * or "the" could get discarded, all the other tokens made lowercase, 21 recognized as a integer, 2018-06-15 recognized as a date.

When you search, the search query goes through a similar process.

The benefit of this process is that whether you search for Hello or hello or hElLo, the document is found. Whether you search for england or UK or Britain, the document is still found. Whether you search for 2018-06-15 or 15 July 2018, the document is still found.

5.0 Write once read many times

As mentioned above, search engine is very efficient for search and or in other words better for reading.

However, the analysis and indexing and storage process for a search engine can be expensive. Update to a document could lead to reindexing.

For this reason, search engines are better suited when your documents are written once, updated rarely, but need to be searched and read many times.

6.0 Database better at OLTP

For reason mentioned above, Search engines become inefficient if the documents they store are updated frequently as would done in an online transaction processing system.

A traditional database is more suited for such usage scenarios.

Another place where a traditional database is better where ACID or even less transactional integrity is important.

7.0 Analytics

The popular open source search engines ElasticSearch and Apache Solr have done a great job making it easy do analytics - from basic counting, aggregation, summarization, faceting etc.

Analytics on data is much easier and powerful in a search engine than a database

8.0 Summary


your queries change frequently
your need to search on fields that change
you need to search on a large variety of fields
you have variety of document formats
you need full text search
you need analytics
your data access pattern is write/update few times but read many many times

then, a search engine in your architecture will certainly help.

Note that it does not have to be one or the other. Most modern architectures use both a database and search engine. Depending on the use case you may choose to store some data in database and other data in a search engine. Or you may choose for store your data in both a search engine for better querying  and a database for transactional integrity.

No comments:

Post a Comment