Is it better to store all fields from a database in ElasticSearch or only indexed fields?

[porunov (Oleksandr Porunov)]

Hello,

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:
example.com/user/search
We would like to retrieve additional_information of the first 5 users who have name hello and age from 123 to 456.

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?

Best regards,
Alexandr

[dadoonet (David Pilato)]

I believe the former is better as it will avoid having multiple round trips to another database.

[gabriel_tessier]

Hi,

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.

[porunov (Oleksandr Porunov)]

Hello @dadoonet and @gabriel_tessier,

Thank you for the suggestions! It makes sense. I think we also will store all fields in the ElasticSearch.

Best regards,
Alexandr