We are thinking about what data should be stored in ElasticSearch.
For example, we have a database with a user table with fields like:
username, age, additional_information.
We are never searching any user by
additional_information but we should retrieve that additional information after the search. For example, let's assume we have the endpoint:
We would like to retrieve
additional_information of the first 5 users who have name
hello and age from
Is it better to store
additional_information in ElasticSearch or retrieve ids of such users and then retrieve
additional_information by user ids from a database?
I believe the former is better as it will avoid having multiple round trips to another database.
Just to share about our experience now from > 5 years using elastic in 5 different projects. When we started we were storing only the fields we need for search and then we were getting the database data with a get_by_id (as we thought that get_by_id in database is always fast)... Problem here is when you need to search on a new fields you need to reindex your data.
Then to avoid round trip (as David said) and improve latency and remove charge on the database server we shadow all our data in elasitc. i.e each time we were saving something in database we were making a full copy in elastic. And finally we were writing in database and always reading in elastic.
After 2~3 Gb in a Mysql table even get by id is slow. This approach had some merit at the beginning but was making the code more hard to maintain, melting with database object from the orm and json dictionary from elasticsearch.
Now we use only elasticsearch and removed the database, it took time for us to think flat data not relational and so far we don't have problem. We also removed all the overhead problem of database relation problem (like deployment, foreign key deadlock etc...). It also depend a lot on your needs, but for us so far we don't regret the move.
Hope it can help.
Hello @dadoonet and @gabriel_tessier,
Thank you for the suggestions! It makes sense. I think we also will store all fields in the ElasticSearch.
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.