How to keep SQL data and Elasticsearch in-sync, and which to search from?

I’ve seen two solutions mentioned, and was wondering what most people do.

1. Use logstash

2. Code your application to make writes to Elasticsearch alongside SQL. For example,

public saveRecord() {
    saveToElasticsearch();
    saveToSQL();
}

Another question is how to handle actually searching the entity? Do you ONLY use Elasticsearch?

If not, I would assume you fetch from Elasticsearch based on keywords and use the IDs returned to filter your SQL query. My question then, is how do you handle pagination? For example let’s say you only want results 50 to 100. First you query Elasticsearch which returns 50-100. Then the SQL query reduces that to 20 results – the other 30 results are in what would’ve been the next Elasticsearch query (100 – 150 for example). Do you keep going back and forth?

Answer

As for your first question check here

As for the second question, if you plan to use elasticsearch as your search layer then better do it for all the searchable/filterable fields. As you’ve described, the alternative will get very messy very soon. Use elasticsearch for all your searches/filters and even aggregations if it suits your needs. Use the sql database as your point of truth and just get the full payload from there.

In general, if you will need to paginate then your search should better be in one place otherwise it will get ugly.