Good practices between SQL and elasticsearch

Question

Imagine you have a SQL database like mysql or postgresql. You have two tables : user and car. One user can drive N cars, a car can be driven by N users, so you have a third "drive" table with two foreign key.

Now, you want that your table user goes on elasticsearch, because you want search users by name, email... etc... Maybe you also need to do some search on the car table.

I see three way to achieve this, I d'like to know what is the best way :

1) Abandon the sql database. All your tables are now on elasticsearch. You can do search on whatever you want, but you must treat all your constraints manually.

2) Keep the structure on the sql database, you keep your three tables, the primary keys and the foreign keys. But your tables contains only elasticsearch ID of the associated row in elasticsearch. For exemple in table user, you keep user_id and add a user_elasticsearch_id that point on the elasticsearch row where you found the name, the email... etc... So you have your sql constraints, you can do search, but you must maintain two tables.

3) Duplicate. You don't touch your sql database, you duplicate all the rows on the elasticsearch database. You have your constrains, you can search, but again you must maintain two tables and you have twice the data and twice the storage.

Now, brave fellow of stackoverflow, what would you do in this case ?

Thank you.

This answer should help: stackoverflow.com/questions/36915428/… and this one also: stackoverflow.com/questions/40410920/… — Val
– Val, Commented Dec 22, 2016 at 10:49

Philip · Accepted Answer · 2016-12-28 12:56:31Z

6

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.

answered Dec 28, 2016 at 12:56

Philip

3,0981 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Setop · Accepted Answer · 2016-12-22 10:30:18Z

1

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.

answered Dec 22, 2016 at 10:30

Setop

2,57017 silver badges35 bronze badges

Collectives™ on Stack Overflow

Good practices between SQL and elasticsearch

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related