Software Engineering
database relational-database postgres elasticsearch
Updated Fri, 19 Aug 2022 13:41:29 GMT

Elasticsearch and PostgreSQL combination


I have a marketplace application and I stored data in a PostgreSQL. I have performance problem for product search, I know I can improve search performance if I use Elasticsearch instead of PostgreSQL but I'm confused for is Elasticsearch strong as PostgreSQL (RDBMS) in create, update or delete operations. which makes sense; Using Elasticsearch for searches only, in other cases, using PostgreSQL, such as fetching by id. or using Elasticsearch for all operations




Solution

Think of Elasticsearch as an eventually inconsistent index on top of your DBMS. The index is built once for a specific state of the database, but when the content of the DB changes, one will have to update this index afterwards (for example, in regular intervals, or somehow coupled to these changes). Depending on how one implements the update strategy, there can be some temporary inconsistencies between what Elasticsearch finds and what is really in the DB.

This is just the same situation you see everyday at search engines like Google: when the content of a website changes, and you look for a newly added keyword on a certain page, there is always a time lag until Google finds the changed page. And when a keyword or a whole page was deleted, there is also a time lag until Google does not present the page any more among its search results.

So an application has to take care for searches which may sometimes find something which is not really in the DB anymore, or does not find something immediately which was added shortly. Nevertheless it can make sense to let the search engine handle complex full text queries and deliver a resultset of product IDs, which are then queried in a relational DB by a simple SELECT ... FROM ... WHERE ID IN (...) statement.

You will have to analyse for yourself if the additional overhead is worth it, and if Elasticsearch really solves your issues. Alternatively (and definitely simpler to start with), you could look at the full text index capabilities of PostgreSQL and check if they can help you to optimize the queries of your specific application. See also this tutorial, starting with the words

.... but you're not sure you're ready to implement a full-blown search application like [...] Elasticsearch

which seems precisely to be your situation here.





Comments (1)

  • +0 – Thank you for your answer and kindly answer. I read tutorials you sent and i dont know its good/true way but i decided to use Elasticsearch for only search in my application and i will use logstash for sync PostgreSQL and Elasticsearch — Jan 12, 2020 at 16:20