Design point for product search and auto-complete using Elastic search?

Question

I need to implement product search using Elastic search similar to it is done on any scalable ecommerce site.I am planning below algorithm for this

1. Whenever product is added to system, First add it in DB and then in ES server. ES will create the index on document provided to it and then keep both index and document in memory .So it will kind of write through cache(where with Cache here I meant indexes and document in memory. Not to be confused with key value cache like redis/memcache).
2. Say,my document in DB has 30 fields, but I need to search only on three fields(name,description and type) . So ES will have 4(3 + 1) fields . 3 are searchable fields and fourth field is primary key id representing unique key in DB
3. ES will create the index on 3 fields internally while adding and keep it in memory.
4. While search, search query will go the ES server for multimatch and fetch the relevant result with order according to boosting factor in query.
5. Search result will contain the field id representing the DB primary key with which we can further fetch the product details from DB based on primary key id.
6. We need to reindex the 3 fields again while ES server startup. For this I will fetch the data from DB during startup and give it to ES.

With this design all the searchable field will be in cached in memory under system at any point of time and there won't be cache miss and system does have to hit DB . Does this design looks good or I am missing anything here ?

Auto-complete Same design we can use for auto complete when user starts typing. For example when user starts typing say 3 letters, query will go to back end and above design will work there too. The only difference will be it will return only limited result say top 10 results instead of returning all results.

Will post a separate question about my question/thoughts how to make it scalable.

Answer 1

As you've written this question, it describes the standard usage for a search engine: you index the fields that you want to search by, then run queries against those fields.

A search engine, however, is not a cache, even if it happens to store its indexes in-memory. If you want to have fast ID-based lookup for data, you should use an actual cache such as Redis or Memcached.

Along those lines, point #6 only applies to initialization of your ES cluster (or re-initialization if you choose to make major database changes). In normal operation, an ES cluster writes its indexes to disk.

The one thing that you do need to think about is when you will update your ES server due to normal database changes. Some options:

• Perform the ES update in the same transaction as the database update. This will increase the time taken for the database update (thus increasing the possibility for lock contention), as well as causing it to fail if there's a problem with the ES server.
• Perform the ES update after the database transaction but within the same user request. Breaks the coupling between database and search engine, but means that database updates may not be indexed. Delays in search update will be visible to user.
• Perform the ES update asynchronously to the database update. Allows clustering of ES updates, which improves its behavior, but introduces a lag between the time the database is updated and the time those updates appear are searchable.

For the second two options, I would add an indexed_at field to the database row, alongside updated_at (you do use updated_at and updated_by, right?), so that you can catch discrepancies between database transactions and search updates.