Good practices between SQL and elasticsearch Good practices between SQL and elasticsearch elasticsearch elasticsearch

Good practices between SQL and elasticsearch


The most common setup for critical business data is having e.g. a SQL database as your primary datastore and Elasticsearch as additional search index. (= your solution 3).

An alternative for non business-critical data like logs etc. is having Elasticsearch standalone.

Solution 2 seems wired, is not an option for me.


Because you may have a lot of business rules mixed into you database and application using it, I would be conservative and keep the DB. And use ES to index the user attributes I want to search on. ES would return scored results. When a result select I would switch to DB to retrieve all information and relations.

So I would choose 2b : keep DB and store PK in ES, not ID in DB).

Keep in mind you can force the ID en ES. It could be "user_PK" or something alike.