# Update\_by\_query versus terms performance

**URL:** <https://discuss.elastic.co/t/update-by-query-versus-terms-performance/307187>\
**Category:** Elasticsearch\
**Created:** [June 14, 2022, 4:06pm UTC](https://discuss.elastic.co/t/update-by-query-versus-terms-performance/307187 "2022-06-14T16:06:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sumannewton](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sumannewton/32/107024_2.png) [@sumannewton](https://discuss.elastic.co/u/sumannewton)\
**Post date:** [June 14, 2022, 4:06pm UTC](https://discuss.elastic.co/t/update-by-query-versus-terms-performance/307187/1 "2022-06-14T16:06:29Z")

</div>

There is a use case I am working on as described below:

I have documents(transactions) getting saved in below format:

```auto
String date
String versionId // UUID
// OTHER FIELDS

```

Each date has multiple version transactions that goes up to few millions in each version(1-100mil)

Problem statement is to fetch only active transactions(with pagination)

- for a given date
- for a specified date range
- for all dates(no date filter)

I have narrowed down my design to below options:

**Option 1:**  
Add a new field `active` to maintain the latest version.

```auto
String date
String versionId
boolean active // Gives me active docs with value set to true
// OTHER FIELDS

```

For every date, I can have multiple versions with only latest versionId transactions set active to `true`. Whenever a new version is being indexed, I am reverting the older versionId transactions active to `false` using `_update_by_query`.

How update happens?

- \_update\_by\_query(wait\_for\_completion=false) with active = true and set active = false using script.
- Wait for above task to complete.
- Insert new documents(1-100million docs) with next versionId and active = true in _transactions_ index.

How search happens?  
Now, search happens directly on the index(_transactions_) with active = true for all cases:

- for a given date
- for a specified date range
- for all dates(no date filter)

Pagination is through `search_after`.

Questions:

- Is \_update\_by\_query efficient here?

**Option 2:**  
Introduce a new index(_transactions\_meta_) for meta store with below format:

```auto
String date
String versionId
boolean active

```

This meta index will have very less data. Each date and versionId will have one document as opposed to many documents in transactions index.

How update happens?

- \_update\_by\_query with active = true and set active = false using script in _transactions\_meta_ index.
- Insert single document with next versionId and active = true in the _transactions\_meta_ index.
- Insert new documents(1-100million docs) in same versionId(from above) in _transactions_ index.

How search happens?  
Now, search is a two step process:

- Search all the documents with active = true in _transactions\_meta_ index for multiple dates(using `terms` or `range`). Get all documents using `search_after`.
- Then Get unique versionIds from above step.
- Search _transactions_ index using `terms` filter having versionIds from above. Pagination is through `search_after`.

Questions:

- Is terms filter efficient here? There will be more than 200 terms values.

Which option is better? I am inclined towards option 1.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 12, 2022, 4:06pm UTC](https://discuss.elastic.co/t/update-by-query-versus-terms-performance/307187/2 "2022-07-12T16:06:33Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.