# Refresh\_interval for denormalized views with high indexing load

**URL:** <https://discuss.elastic.co/t/refresh-interval-for-denormalized-views-with-high-indexing-load/130985>\
**Category:** Elasticsearch\
**Created:** [May 8, 2018, 11:01am UTC](https://discuss.elastic.co/t/refresh-interval-for-denormalized-views-with-high-indexing-load/130985 "2018-05-08T11:01:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pahomovda](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pahomovda/32/30870_2.png) [@Pahomovda](https://discuss.elastic.co/u/Pahomovda)\
**Post date:** [May 8, 2018, 11:01am UTC](https://discuss.elastic.co/t/refresh-interval-for-denormalized-views-with-high-indexing-load/130985/1 "2018-05-08T11:01:30Z")

</div>

Hi!  
I am trying to create materialized view of relational data in Elasticsearch. Lets say i have `child` table and multiple `parent` tables (all backed by separated microservices with event queue). I need to find relevant childs by parent properties mostly.  
So i have my model denormalized like:

```auto
PUT matview/foo/1
{
  "foo": "bar",
  "parent1": { "id": "1", ... },
  "parent2": { "id": "2", ... }
}

```

When `parent1` update event accepted i perform `_update_by_query` to update parent1 objects by id

```auto
POST matview/_update_by_query
{
  "query": { "term": { "parent1.id": "1" } },
  "script": { "source": "ctx._source.parent1.name='foo'" }
}

```

**Problem is** I am constantly getting `version conflict` errors on `update_by_query` and `delete_by_query`. Preferable way to avoid those is using `refresh=wait_for` and client-side retries as discussed here  
[github issue](https://github.com/elastic/elasticsearch/issues/22723)  
and here  
[?refresh doc](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-refresh.html)  
So i think i should **decrease** `refresh_interval` for this case to reduce probability of version conflicts and following retries

On the other hand my index is under havy indexing load and as described here  
[tune for indexing speed doc](https://www.elastic.co/guide/en/elasticsearch/reference/master/tune-for-indexing-speed.html)  
i should **increase** `refresh_interval` to increase indexing speed.  
So i cant use `refresh=wait_for` because it could wait too long for big `refresh_interval` value

**Question is:** what to do increase or decrease? -\_- or maybe there is some other ways to overcome this problem?  
for example maybe some hack to disable versioning (i know can use external versioning in update by id but it seems no way to use external versioning in `update_by_query`).  
As i said before i use queues to populate elasticsearch so i have full control of index(shard) update order.

---

<div class="post-metadata">

**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\
**Post date:** [May 8, 2018, 2:29pm UTC](https://discuss.elastic.co/t/refresh-interval-for-denormalized-views-with-high-indexing-load/130985/2 "2018-05-08T14:29:27Z")

</div>

I don't think any combination of `refresh_interval` or `refresh` is going to address your problem, and even if it did, you would have another problem with performance because you'll be creating lots of tiny Lucene segments with all those updates to the same document.

Consider collecting all your `parent.id`: _name_ updates in memory (e.g., `{"1": "foo", ...}`) and then applying them all in a single bulk operation after all your `PUT matview/foo/..`'s are done (ideally via `_bulk`) and the index has been refreshed.

Since you are using queues, you can change the unit of work from one parent to a group of say 100 parents to make bulk operations more efficient (i.e. reduce small segment creation). Hope this helps!

---

<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:** [June 5, 2018, 2:34pm UTC](https://discuss.elastic.co/t/refresh-interval-for-denormalized-views-with-high-indexing-load/130985/3 "2018-06-05T14:34:41Z")

</div>

Closed on June 5, 2018, 2:34pm UTC

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