# Ranged and recurring events **URL:** \ **Category:** Elasticsearch\ **Created:** [November 14, 2010, 6:47am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567 "2010-11-14T06:47:10Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![mjarecki](https://avatars.discourse-cdn.com/v4/letter/m/8edcca/32.png) [@mjarecki](https://discuss.elastic.co/u/mjarecki)\ **Post date:** [November 14, 2010, 6:47am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/1 "2010-11-14T06:47:10Z")
Hi, We are looking at using ES in an upcoming project and are fleshing out data models for it - and are hoping for some suggestions on our approach. Our database consists of ranged and recurring events. We want to be able to query the events index to find, for example, what events are occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I want all events active between 3.45pm - 3.59pm). Our proposed approach is such: each event with its various attributes (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", "finish" if not recurring) is initially stored as a document in Riak. Using a post-commit-hook, the event is added to the events index in ES. A field called "time\_periods" is included that contains a series of 15 minute time period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" attributes. A time period "2010-11-11T14:00" is a block of time that ranges from "2010-11-11T14:00" until "2010-11-11T14:14". For example: EVENT 1 (truncated). A ranged event =\> { "event": { "start": "2010-11-11T14:00", "finish": "2010-11-11T15:00" …. } } EVENT 1 (truncated). Sent to ES =\> { "event": { "time\_periods": ["2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] …. } } EVENT 2 (truncated and with a dodgy example rrule). A recurring event =\> { "event": { "rrule": "daily 14:00 until 14:30", "repeat\_start": "2010-11-10", "repeat\_until": "2010-12-10" …. } } EVENT 2 (truncated). Sent to ES =\> { "event": { "time\_periods": ["2010-11-10T14:00", "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"….] …. } } At scheduled intervals, a recurring event's time periods are updated - old ones removed, new ones added. The time periods are a floating set of values that might typically span 2 months. Our concern is that some recurring events are continuous (24 hours/day everyday), and others recur daily at multiple times during the same day. This leads to a large number of time periods for some events - 5952 time periods in 2 months (24/7 15 minute periods). The database will have about 10000 such frequently recurring events. We worry that our proposed approach might bring ES to a crawl - especially during indexing. Is it reasonable to use ES in this way? Can ES handle a field with thousands of values? Does anyone have suggestions on how better to efficiently index and query ranged/recurring events? Thanks in advance, Mark ---
**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\ **Post date:** [November 14, 2010, 9:12am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/2 "2010-11-14T09:12:13Z")
Hey Mark, I think you may be better off use a date range per doc approach where each event has multiple ranges and each of those ranges is a document. So, using your example: EVENT 1 (truncated). A ranged event =\> { "event": { "start": "2010-11-11T14:00", "finish": "2010-11-11T15:00" …. } } EVENT 1 (truncated). Sent to ES =\> { "event": { "eventid": "1", "start": "2010-11-11T14:00", "finish": "2010-11-11T15:00" …. } } In this case the range query is easy: start:[\* TO] AND finish:[TO \*] EVENT 2 A recurring event =\> { "event": { "rrule": "daily 14:00 until 14:30", "repeat\_start": "2010-11-10", "repeat\_until": "2010-12-10" …. } } EVENT 2 Sent to ES =\> { "event": { "eventid": "2", "start": "2010-11-10T14:00", "finish": "2010-11-10T14:30" } } { "event": { "eventid": "2", "start": "2010-11-11T14:00", "finish": "2010-11-11T14:30" } } and repeat for each occurrence. The same range query as above should work to find the events. This may feel like a slightly SQL-esque approach, but that's because it's a SQL-esque problem 🙂 There may be better solutions, as I am not familiar with all of the ES search options. Hope this helps, Paul On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > Hi, > > We are looking at using ES in an upcoming project and are fleshing out data models for it - and are hoping for some suggestions on our approach. > > Our database consists of ranged and recurring events. We want to be able to query the events index to find, for example, what events are occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I want all events active between 3.45pm - 3.59pm). > > Our proposed approach is such: each event with its various attributes (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", "finish" if not recurring) is initially stored as a document in Riak. Using a post-commit-hook, the event is added to the events index in ES. A field called "time\_periods" is included that contains a series of 15 minute time period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" attributes. A time period "2010-11-11T14:00" is a block of time that ranges from "2010-11-11T14:00" until "2010-11-11T14:14". > > For example: > > EVENT 1 (truncated). A ranged event =\> > { > "event": { > "start": "2010-11-11T14:00", > "finish": "2010-11-11T15:00" > …. > } > > } > > EVENT 1 (truncated). Sent to ES =\> > { > "event": { > "time\_periods": ["2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > …. > } > > } > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event =\> > { > "event": { > "rrule": "daily 14:00 until 14:30", > "repeat\_start": "2010-11-10", > "repeat\_until": "2010-12-10" > …. > } > > } > > EVENT 2 (truncated). Sent to ES =\> > { > "event": { > "time\_periods": ["2010-11-10T14:00", "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"….] > …. > } > > } > > At scheduled intervals, a recurring event's time periods are updated - old ones removed, new ones added. The time periods are a floating set of values that might typically span 2 months. > > Our concern is that some recurring events are continuous (24 hours/day everyday), and others recur daily at multiple times during the same day. This leads to a large number of time periods for some events - 5952 time periods in 2 months (24/7 15 minute periods). The database will have about 10000 such frequently recurring events. We worry that our proposed approach might bring ES to a crawl - especially during indexing. > > Is it reasonable to use ES in this way? Can ES handle a field with thousands of values? Does anyone have suggestions on how better to efficiently index and query ranged/recurring events? > > Thanks in advance, > > Mark ---
**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\ **Post date:** [November 14, 2010, 9:15am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/3 "2010-11-14T09:15:16Z")
Also, the approach I mention should give your events a 1 second granularity if you use the date field type. On Nov 14, 2:12 am, Paul [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > Hey Mark, > I think you may be better off use a date range per doc approach > where each event has multiple ranges and each of those ranges is a > document. So, using your example: > > EVENT 1 (truncated). A ranged event =\> > { > "event": { > "start": "2010-11-11T14:00", > "finish": "2010-11-11T15:00" > …. > } > > } > > EVENT 1 (truncated). Sent to ES =\> > { > "event": { > "eventid": "1", > "start": "2010-11-11T14:00", > "finish": "2010-11-11T15:00" > …. > } > > } > > In this case the range query is easy: > start:[\* TO] AND finish:[TO \*] > > EVENT 2 A recurring event =\> > { > "event": { > "rrule": "daily 14:00 until 14:30", > "repeat\_start": "2010-11-10", > "repeat\_until": "2010-12-10" > …. > } > > } > > EVENT 2 Sent to ES =\> > { > "event": { > "eventid": "2", > "start": "2010-11-10T14:00", > "finish": "2010-11-10T14:30" > } > > } > > { > "event": { > "eventid": "2", > "start": "2010-11-11T14:00", > "finish": "2010-11-11T14:30" > } > > } > > and repeat for each occurrence. The same range query as above should > work to find the events. > > This may feel like a slightly SQL-esque approach, but that's because > it's a SQL-esque problem 🙂 > > There may be better solutions, as I am not familiar with all of the ES > search options. > > Hope this helps, > Paul > > On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > > > Hi, > > > We are looking at using ES in an upcoming project and are fleshing out data models for it - and are hoping for some suggestions on our approach. > > > Our database consists of ranged and recurring events. We want to be able to query the events index to find, for example, what events are occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I want all events active between 3.45pm - 3.59pm). > > > Our proposed approach is such: each event with its various attributes (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", "finish" if not recurring) is initially stored as a document in Riak. Using a post-commit-hook, the event is added to the events index in ES. A field called "time\_periods" is included that contains a series of 15 minute time period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" attributes. A time period "2010-11-11T14:00" is a block of time that ranges from "2010-11-11T14:00" until "2010-11-11T14:14". > > > For example: > > > EVENT 1 (truncated). A ranged event =\> > > { > > "event": { > > "start": "2010-11-11T14:00", > > "finish": "2010-11-11T15:00" > > …. > > } > > > } > > > EVENT 1 (truncated). Sent to ES =\> > > { > > "event": { > > "time\_periods": ["2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > > …. > > } > > > } > > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event =\> > > { > > "event": { > > "rrule": "daily 14:00 until 14:30", > > "repeat\_start": "2010-11-10", > > "repeat\_until": "2010-12-10" > > …. > > } > > > } > > > EVENT 2 (truncated). Sent to ES =\> > > { > > "event": { > > "time\_periods": ["2010-11-10T14:00", "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"….] > > …. > > } > > > } > > > At scheduled intervals, a recurring event's time periods are updated - old ones removed, new ones added. The time periods are a floating set of values that might typically span 2 months. > > > Our concern is that some recurring events are continuous (24 hours/day everyday), and others recur daily at multiple times during the same day. This leads to a large number of time periods for some events - 5952 time periods in 2 months (24/7 15 minute periods). The database will have about 10000 such frequently recurring events. We worry that our proposed approach might bring ES to a crawl - especially during indexing. > > > Is it reasonable to use ES in this way? Can ES handle a field with thousands of values? Does anyone have suggestions on how better to efficiently index and query ranged/recurring events? > > > Thanks in advance, > > > Mark ---
**Author:** ![mjarecki](https://avatars.discourse-cdn.com/v4/letter/m/8edcca/32.png) [@mjarecki](https://discuss.elastic.co/u/mjarecki)\ **Post date:** [November 14, 2010, 9:30am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/4 "2010-11-14T09:30:32Z")
Thanks Paul. On 14/11/2010, at 8:15 PM, Paul wrote: > Also, the approach I mention should give your events a 1 second > granularity if you use the date field type. > > On Nov 14, 2:12 am, Paul [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > > > Hey Mark, > > I think you may be better off use a date range per doc approach > > where each event has multiple ranges and each of those ranges is a > > document. So, using your example: > > > > EVENT 1 (truncated). A ranged event =\> > > { > > "event": { > > "start": "2010-11-11T14:00", > > "finish": "2010-11-11T15:00" > > …. > > } > > > > } > > > > EVENT 1 (truncated). Sent to ES =\> > > { > > "event": { > > "eventid": "1", > > "start": "2010-11-11T14:00", > > "finish": "2010-11-11T15:00" > > …. > > } > > > > } > > > > In this case the range query is easy: > > start:[\* TO] AND finish:[TO \*] > > > > EVENT 2 A recurring event =\> > > { > > "event": { > > "rrule": "daily 14:00 until 14:30", > > "repeat\_start": "2010-11-10", > > "repeat\_until": "2010-12-10" > > …. > > } > > > > } > > > > EVENT 2 Sent to ES =\> > > { > > "event": { > > "eventid": "2", > > "start": "2010-11-10T14:00", > > "finish": "2010-11-10T14:30" > > } > > > > } > > > > { > > "event": { > > "eventid": "2", > > "start": "2010-11-11T14:00", > > "finish": "2010-11-11T14:30" > > } > > > > } > > > > and repeat for each occurrence. The same range query as above should > > work to find the events. > > > > This may feel like a slightly SQL-esque approach, but that's because > > it's a SQL-esque problem 🙂 > > > > There may be better solutions, as I am not familiar with all of the ES > > search options. > > > > Hope this helps, > > Paul > > > > On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > > > > > Hi, > > > > > We are looking at using ES in an upcoming project and are fleshing out data models for it - and are hoping for some suggestions on our approach. > > > > > Our database consists of ranged and recurring events. We want to be able to query the events index to find, for example, what events are occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I want all events active between 3.45pm - 3.59pm). > > > > > Our proposed approach is such: each event with its various attributes (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", "finish" if not recurring) is initially stored as a document in Riak. Using a post-commit-hook, the event is added to the events index in ES. A field called "time\_periods" is included that contains a series of 15 minute time period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" attributes. A time period "2010-11-11T14:00" is a block of time that ranges from "2010-11-11T14:00" until "2010-11-11T14:14". > > > > > For example: > > > > > EVENT 1 (truncated). A ranged event =\> > > > { > > > "event": { > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T15:00" > > > …. > > > } > > > > > } > > > > > EVENT 1 (truncated). Sent to ES =\> > > > { > > > "event": { > > > "time\_periods": ["2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > > > …. > > > } > > > > > } > > > > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event =\> > > > { > > > "event": { > > > "rrule": "daily 14:00 until 14:30", > > > "repeat\_start": "2010-11-10", > > > "repeat\_until": "2010-12-10" > > > …. > > > } > > > > > } > > > > > EVENT 2 (truncated). Sent to ES =\> > > > { > > > "event": { > > > "time\_periods": ["2010-11-10T14:00", "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"….] > > > …. > > > } > > > > > } > > > > > At scheduled intervals, a recurring event's time periods are updated - old ones removed, new ones added. The time periods are a floating set of values that might typically span 2 months. > > > > > Our concern is that some recurring events are continuous (24 hours/day everyday), and others recur daily at multiple times during the same day. This leads to a large number of time periods for some events - 5952 time periods in 2 months (24/7 15 minute periods). The database will have about 10000 such frequently recurring events. We worry that our proposed approach might bring ES to a crawl - especially during indexing. > > > > > Is it reasonable to use ES in this way? Can ES handle a field with thousands of values? Does anyone have suggestions on how better to efficiently index and query ranged/recurring events? > > > > > Thanks in advance, > > > > > Mark ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [November 14, 2010, 1:58pm UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/5 "2010-11-14T13:58:19Z")
I agree with Paul, it sounds like a better solution and simpler to manage. Note that date type supports up to millisecond resolution, not second level resolution (it is stored internally as milliseconds since the epoch). On Sun, Nov 14, 2010 at 11:30 AM, Mark Jarecki [mjarecki@bigpond.net.au](mailto:mjarecki@bigpond.net.au)wrote: > Thanks Paul. > > On 14/11/2010, at 8:15 PM, Paul wrote: > > > Also, the approach I mention should give your events a 1 second > > granularity if you use the date field type. > > > > On Nov 14, 2:12 am, Paul [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > > > > > Hey Mark, > > > I think you may be better off use a date range per doc approach > > > where each event has multiple ranges and each of those ranges is a > > > document. So, using your example: > > > > > > EVENT 1 (truncated). A ranged event =\> > > > { > > > "event": { > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T15:00" > > > …. > > > } > > > > > > } > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > { > > > "event": { > > > "eventid": "1", > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T15:00" > > > …. > > > } > > > > > > } > > > > > > In this case the range query is easy: > > > start:[\* TO] AND finish:[TO \*] > > > > > > EVENT 2 A recurring event =\> > > > { > > > "event": { > > > "rrule": "daily 14:00 until 14:30", > > > "repeat\_start": "2010-11-10", > > > "repeat\_until": "2010-12-10" > > > …. > > > } > > > > > > } > > > > > > EVENT 2 Sent to ES =\> > > > { > > > "event": { > > > "eventid": "2", > > > "start": "2010-11-10T14:00", > > > "finish": "2010-11-10T14:30" > > > } > > > > > > } > > > > > > { > > > "event": { > > > "eventid": "2", > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T14:30" > > > } > > > > > > } > > > > > > and repeat for each occurrence. The same range query as above should > > > work to find the events. > > > > > > This may feel like a slightly SQL-esque approach, but that's because > > > it's a SQL-esque problem 🙂 > > > > > > There may be better solutions, as I am not familiar with all of the ES > > > search options. > > > > > > Hope this helps, > > > Paul > > > > > > On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > > > > > > > Hi, > > > > > > > We are looking at using ES in an upcoming project and are fleshing out > > > > data models for it - and are hoping for some suggestions on our approach. > > > > > > > Our database consists of ranged and recurring events. We want to be > > > > able to query the events index to find, for example, what events are > > > > occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I > > > > want all events active between 3.45pm - 3.59pm). > > > > > > > Our proposed approach is such: each event with its various attributes > > > > (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", > > > > "finish" if not recurring) is initially stored as a document in Riak. Using > > > > a post-commit-hook, the event is added to the events index in ES. A field > > > > called "time\_periods" is included that contains a series of 15 minute time > > > > period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" > > > > attributes. A time period "2010-11-11T14:00" is a block of time that ranges > > > > from "2010-11-11T14:00" until "2010-11-11T14:14". > > > > > > > For example: > > > > > > > EVENT 1 (truncated). A ranged event =\> > > > > { > > > > "event": { > > > > "start": "2010-11-11T14:00", > > > > "finish": "2010-11-11T15:00" > > > > …. > > > > } > > > > > > > } > > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > > { > > > > "event": { > > > > "time\_periods": ["2010-11-11T14:00", > > > > "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > > > > …. > > > > } > > > > > > > } > > > > > > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event > > > > =\> > > > > { > > > > "event": { > > > > "rrule": "daily 14:00 until 14:30", > > > > "repeat\_start": "2010-11-10", > > > > "repeat\_until": "2010-12-10" > > > > …. > > > > } > > > > > > > } > > > > > > > EVENT 2 (truncated). Sent to ES =\> > > > > { > > > > "event": { > > > > "time\_periods": ["2010-11-10T14:00", > > > > "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", > > > > "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", > > > > "2010-11-11T14:45"….] > > > > …. > > > > } > > > > > > > } > > > > > > > At scheduled intervals, a recurring event's time periods are updated - > > > > old ones removed, new ones added. The time periods are a floating set of > > > > values that might typically span 2 months. > > > > > > > Our concern is that some recurring events are continuous (24 hours/day > > > > everyday), and others recur daily at multiple times during the same day. > > > > This leads to a large number of time periods for some events - 5952 time > > > > periods in 2 months (24/7 15 minute periods). The database will have about > > > > 10000 such frequently recurring events. We worry that our proposed approach > > > > might bring ES to a crawl - especially during indexing. > > > > > > > Is it reasonable to use ES in this way? Can ES handle a field with > > > > thousands of values? Does anyone have suggestions on how better to > > > > efficiently index and query ranged/recurring events? > > > > > > > Thanks in advance, > > > > > > > Mark ---
**Author:** ![mjarecki](https://avatars.discourse-cdn.com/v4/letter/m/8edcca/32.png) [@mjarecki](https://discuss.elastic.co/u/mjarecki)\ **Post date:** [November 15, 2010, 1:09am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/6 "2010-11-15T01:09:36Z")
Just to clarify, would you duplicate the entire event document in ES (including other searchable and non-searchable fields)? Or would you only index the searchable fields, and then query the index to generate a list of ids and do a key lookup in Riak to fetch the full event documents? Thanks again, Mark On 15/11/2010, at 12:58 AM, Shay Banon wrote: > I agree with Paul, it sounds like a better solution and simpler to manage. Note that date type supports up to millisecond resolution, not second level resolution (it is stored internally as milliseconds since the epoch). > > On Sun, Nov 14, 2010 at 11:30 AM, Mark Jarecki [mjarecki@bigpond.net.au](mailto:mjarecki@bigpond.net.au) wrote: > Thanks Paul. > > On 14/11/2010, at 8:15 PM, Paul wrote: > > > Also, the approach I mention should give your events a 1 second > > granularity if you use the date field type. > > > > On Nov 14, 2:12 am, Paul [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > > > > > Hey Mark, > > > I think you may be better off use a date range per doc approach > > > where each event has multiple ranges and each of those ranges is a > > > document. So, using your example: > > > > > > EVENT 1 (truncated). A ranged event =\> > > > { > > > "event": { > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T15:00" > > > …. > > > } > > > > > > } > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > { > > > "event": { > > > "eventid": "1", > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T15:00" > > > …. > > > } > > > > > > } > > > > > > In this case the range query is easy: > > > start:[\* TO] AND finish:[TO \*] > > > > > > EVENT 2 A recurring event =\> > > > { > > > "event": { > > > "rrule": "daily 14:00 until 14:30", > > > "repeat\_start": "2010-11-10", > > > "repeat\_until": "2010-12-10" > > > …. > > > } > > > > > > } > > > > > > EVENT 2 Sent to ES =\> > > > { > > > "event": { > > > "eventid": "2", > > > "start": "2010-11-10T14:00", > > > "finish": "2010-11-10T14:30" > > > } > > > > > > } > > > > > > { > > > "event": { > > > "eventid": "2", > > > "start": "2010-11-11T14:00", > > > "finish": "2010-11-11T14:30" > > > } > > > > > > } > > > > > > and repeat for each occurrence. The same range query as above should > > > work to find the events. > > > > > > This may feel like a slightly SQL-esque approach, but that's because > > > it's a SQL-esque problem 🙂 > > > > > > There may be better solutions, as I am not familiar with all of the ES > > > search options. > > > > > > Hope this helps, > > > Paul > > > > > > On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > > > > > > > Hi, > > > > > > > We are looking at using ES in an upcoming project and are fleshing out data models for it - and are hoping for some suggestions on our approach. > > > > > > > Our database consists of ranged and recurring events. We want to be able to query the events index to find, for example, what events are occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I want all events active between 3.45pm - 3.59pm). > > > > > > > Our proposed approach is such: each event with its various attributes (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", "finish" if not recurring) is initially stored as a document in Riak. Using a post-commit-hook, the event is added to the events index in ES. A field called "time\_periods" is included that contains a series of 15 minute time period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" attributes. A time period "2010-11-11T14:00" is a block of time that ranges from "2010-11-11T14:00" until "2010-11-11T14:14". > > > > > > > For example: > > > > > > > EVENT 1 (truncated). A ranged event =\> > > > > { > > > > "event": { > > > > "start": "2010-11-11T14:00", > > > > "finish": "2010-11-11T15:00" > > > > …. > > > > } > > > > > > > } > > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > > { > > > > "event": { > > > > "time\_periods": ["2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > > > > …. > > > > } > > > > > > > } > > > > > > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event =\> > > > > { > > > > "event": { > > > > "rrule": "daily 14:00 until 14:30", > > > > "repeat\_start": "2010-11-10", > > > > "repeat\_until": "2010-12-10" > > > > …. > > > > } > > > > > > > } > > > > > > > EVENT 2 (truncated). Sent to ES =\> > > > > { > > > > "event": { > > > > "time\_periods": ["2010-11-10T14:00", "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"….] > > > > …. > > > > } > > > > > > > } > > > > > > > At scheduled intervals, a recurring event's time periods are updated - old ones removed, new ones added. The time periods are a floating set of values that might typically span 2 months. > > > > > > > Our concern is that some recurring events are continuous (24 hours/day everyday), and others recur daily at multiple times during the same day. This leads to a large number of time periods for some events - 5952 time periods in 2 months (24/7 15 minute periods). The database will have about 10000 such frequently recurring events. We worry that our proposed approach might bring ES to a crawl - especially during indexing. > > > > > > > Is it reasonable to use ES in this way? Can ES handle a field with thousands of values? Does anyone have suggestions on how better to efficiently index and query ranged/recurring events? > > > > > > > Thanks in advance, > > > > > > > Mark ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [November 15, 2010, 9:38am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/7 "2010-11-15T09:38:04Z")
Its really up to you. I, most times, prefer to index more data, so it can be fetched as part of the search request without needing to go to another system. Going to another system (riak, db, ...) will mean higher latency and more load in general when searching. -shay.banon On Mon, Nov 15, 2010 at 3:09 AM, Mark Jarecki [mjarecki@bigpond.net.au](mailto:mjarecki@bigpond.net.au)wrote: > Just to clarify, would you duplicate the entire event document in ES > (including other searchable and non-searchable fields)? > Or would you only index the searchable fields, and then query the index to > generate a list of ids and do a key lookup in Riak to fetch the full event > documents? > > Thanks again, > > Mark > > On 15/11/2010, at 12:58 AM, Shay Banon wrote: > > I agree with Paul, it sounds like a better solution and simpler to manage. > Note that date type supports up to millisecond resolution, not second level > resolution (it is stored internally as milliseconds since the epoch). > > On Sun, Nov 14, 2010 at 11:30 AM, Mark Jarecki [mjarecki@bigpond.net.au](mailto:mjarecki@bigpond.net.au)wrote: > > > Thanks Paul. > > > > On 14/11/2010, at 8:15 PM, Paul wrote: > > > > > Also, the approach I mention should give your events a 1 second > > > granularity if you use the date field type. > > > > > > On Nov 14, 2:12 am, Paul [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > > > > > > > Hey Mark, > > > > I think you may be better off use a date range per doc approach > > > > where each event has multiple ranges and each of those ranges is a > > > > document. So, using your example: > > > > > > > > EVENT 1 (truncated). A ranged event =\> > > > > { > > > > "event": { > > > > "start": "2010-11-11T14:00", > > > > "finish": "2010-11-11T15:00" > > > > …. > > > > } > > > > > > > > } > > > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > > { > > > > "event": { > > > > "eventid": "1", > > > > "start": "2010-11-11T14:00", > > > > "finish": "2010-11-11T15:00" > > > > …. > > > > } > > > > > > > > } > > > > > > > > In this case the range query is easy: > > > > start:[\* TO] AND finish:[TO \*] > > > > > > > > EVENT 2 A recurring event =\> > > > > { > > > > "event": { > > > > "rrule": "daily 14:00 until 14:30", > > > > "repeat\_start": "2010-11-10", > > > > "repeat\_until": "2010-12-10" > > > > …. > > > > } > > > > > > > > } > > > > > > > > EVENT 2 Sent to ES =\> > > > > { > > > > "event": { > > > > "eventid": "2", > > > > "start": "2010-11-10T14:00", > > > > "finish": "2010-11-10T14:30" > > > > } > > > > > > > > } > > > > > > > > { > > > > "event": { > > > > "eventid": "2", > > > > "start": "2010-11-11T14:00", > > > > "finish": "2010-11-11T14:30" > > > > } > > > > > > > > } > > > > > > > > and repeat for each occurrence. The same range query as above should > > > > work to find the events. > > > > > > > > This may feel like a slightly SQL-esque approach, but that's because > > > > it's a SQL-esque problem 🙂 > > > > > > > > There may be better solutions, as I am not familiar with all of the ES > > > > search options. > > > > > > > > Hope this helps, > > > > Paul > > > > > > > > On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > > > > > > > > > Hi, > > > > > > > > > We are looking at using ES in an upcoming project and are fleshing out > > > > > data models for it - and are hoping for some suggestions on our approach. > > > > > > > > > Our database consists of ranged and recurring events. We want to be > > > > > able to query the events index to find, for example, what events are > > > > > occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I > > > > > want all events active between 3.45pm - 3.59pm). > > > > > > > > > Our proposed approach is such: each event with its various attributes > > > > > (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", > > > > > "finish" if not recurring) is initially stored as a document in Riak. Using > > > > > a post-commit-hook, the event is added to the events index in ES. A field > > > > > called "time\_periods" is included that contains a series of 15 minute time > > > > > period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" > > > > > attributes. A time period "2010-11-11T14:00" is a block of time that ranges > > > > > from "2010-11-11T14:00" until "2010-11-11T14:14". > > > > > > > > > For example: > > > > > > > > > EVENT 1 (truncated). A ranged event =\> > > > > > { > > > > > "event": { > > > > > "start": "2010-11-11T14:00", > > > > > "finish": "2010-11-11T15:00" > > > > > …. > > > > > } > > > > > > > > > } > > > > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > > > { > > > > > "event": { > > > > > "time\_periods": ["2010-11-11T14:00", > > > > > "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > > > > > …. > > > > > } > > > > > > > > > } > > > > > > > > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event > > > > > =\> > > > > > { > > > > > "event": { > > > > > "rrule": "daily 14:00 until 14:30", > > > > > "repeat\_start": "2010-11-10", > > > > > "repeat\_until": "2010-12-10" > > > > > …. > > > > > } > > > > > > > > > } > > > > > > > > > EVENT 2 (truncated). Sent to ES =\> > > > > > { > > > > > "event": { > > > > > "time\_periods": ["2010-11-10T14:00", > > > > > "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", > > > > > "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", > > > > > "2010-11-11T14:45"….] > > > > > …. > > > > > } > > > > > > > > > } > > > > > > > > > At scheduled intervals, a recurring event's time periods are updated - > > > > > old ones removed, new ones added. The time periods are a floating set of > > > > > values that might typically span 2 months. > > > > > > > > > Our concern is that some recurring events are continuous (24 hours/day > > > > > everyday), and others recur daily at multiple times during the same day. > > > > > This leads to a large number of time periods for some events - 5952 time > > > > > periods in 2 months (24/7 15 minute periods). The database will have about > > > > > 10000 such frequently recurring events. We worry that our proposed approach > > > > > might bring ES to a crawl - especially during indexing. > > > > > > > > > Is it reasonable to use ES in this way? Can ES handle a field with > > > > > thousands of values? Does anyone have suggestions on how better to > > > > > efficiently index and query ranged/recurring events? > > > > > > > > > Thanks in advance, > > > > > > > > > Mark ---
**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\ **Post date:** [November 15, 2010, 5:16pm UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/8 "2010-11-15T17:16:30Z")
You also want to make sure you understand your long term search requirements, as well. For example, if you want to search for an event happening at a certain time combined with some search input, you'd need all the data pushed down to each doc. There are various trade-offs in terms of complexity and performance. I prefer to keep things as simple as possible until the performance suffers. For your case, this would mean taking figuring out how much content you are going to be searching and making trade-offs based on that. Kimchy, thanks for the clarification regarding the date fields milliseconds granularity. Good to know. Best Regards, Paul On Nov 15, 2:38 am, Shay Banon [shay.ba...@elasticsearch.com](mailto:shay.ba...@elasticsearch.com) wrote: > Its really up to you. I, most times, prefer to index more data, so it can be > fetched as part of the search request without needing to go to another > system. Going to another system (riak, db, ...) will mean higher latency and > more load in general when searching. > > -shay.banon > > On Mon, Nov 15, 2010 at 3:09 AM, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au)wrote: > > > Just to clarify, would you duplicate the entire event document in ES > > (including other searchable and non-searchable fields)? > > Or would you only index the searchable fields, and then query the index to > > generate a list of ids and do a key lookup in Riak to fetch the full event > > documents? > > > Thanks again, > > > Mark > > > On 15/11/2010, at 12:58 AM, Shay Banon wrote: > > > I agree with Paul, it sounds like a better solution and simpler to manage. > > Note that date type supports up to millisecond resolution, not second level > > resolution (it is stored internally as milliseconds since the epoch). > > > On Sun, Nov 14, 2010 at 11:30 AM, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au)wrote: > > > > Thanks Paul. > > > > On 14/11/2010, at 8:15 PM, Paul wrote: > > > > > Also, the approach I mention should give your events a 1 second > > > > granularity if you use the date field type. > > > > > On Nov 14, 2:12 am, Paul [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > > > > > > > > > Hey Mark, > > > > > I think you may be better off use a date range per doc approach > > > > > where each event has multiple ranges and each of those ranges is a > > > > > document. So, using your example: > > > > > > EVENT 1 (truncated). A ranged event =\> > > > > > { > > > > > "event": { > > > > > "start": "2010-11-11T14:00", > > > > > "finish": "2010-11-11T15:00" > > > > > …. > > > > > } > > > > > > } > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > > > { > > > > > "event": { > > > > > "eventid": "1", > > > > > "start": "2010-11-11T14:00", > > > > > "finish": "2010-11-11T15:00" > > > > > …. > > > > > } > > > > > > } > > > > > > In this case the range query is easy: > > > > > start:[\* TO] AND finish:[TO \*] > > > > > > EVENT 2 A recurring event =\> > > > > > { > > > > > "event": { > > > > > "rrule": "daily 14:00 until 14:30", > > > > > "repeat\_start": "2010-11-10", > > > > > "repeat\_until": "2010-12-10" > > > > > …. > > > > > } > > > > > > } > > > > > > EVENT 2 Sent to ES =\> > > > > > { > > > > > "event": { > > > > > "eventid": "2", > > > > > "start": "2010-11-10T14:00", > > > > > "finish": "2010-11-10T14:30" > > > > > } > > > > > > } > > > > > > { > > > > > "event": { > > > > > "eventid": "2", > > > > > "start": "2010-11-11T14:00", > > > > > "finish": "2010-11-11T14:30" > > > > > } > > > > > > } > > > > > > and repeat for each occurrence. The same range query as above should > > > > > work to find the events. > > > > > > This may feel like a slightly SQL-esque approach, but that's because > > > > > it's a SQL-esque problem 🙂 > > > > > > There may be better solutions, as I am not familiar with all of the ES > > > > > search options. > > > > > > Hope this helps, > > > > > Paul > > > > > > On Nov 13, 11:47 pm, Mark Jarecki [mjare...@bigpond.net.au](mailto:mjare...@bigpond.net.au) wrote: > > > > > > > Hi, > > > > > > > We are looking at using ES in an upcoming project and are fleshing out > > > > > > data models for it - and are hoping for some suggestions on our approach. > > > > > > > Our database consists of ranged and recurring events. We want to be > > > > > > able to query the events index to find, for example, what events are > > > > > > occurring in the current 15 minute time period (e.g. if it's 3.43pm now, I > > > > > > want all events active between 3.45pm - 3.59pm). > > > > > > > Our proposed approach is such: each event with its various attributes > > > > > > (e.g. "rrule", "repeat\_start", "repeat\_until" if recurring; "start", > > > > > > "finish" if not recurring) is initially stored as a document in Riak. Using > > > > > > a post-commit-hook, the event is added to the events index in ES. A field > > > > > > called "time\_periods" is included that contains a series of 15 minute time > > > > > > period instances derived from the "rrule"/"repeat\_until" or "start"/"finish" > > > > > > attributes. A time period "2010-11-11T14:00" is a block of time that ranges > > > > > > from "2010-11-11T14:00" until "2010-11-11T14:14". > > > > > > > For example: > > > > > > > EVENT 1 (truncated). A ranged event =\> > > > > > > { > > > > > > "event": { > > > > > > "start": "2010-11-11T14:00", > > > > > > "finish": "2010-11-11T15:00" > > > > > > …. > > > > > > } > > > > > > > } > > > > > > > EVENT 1 (truncated). Sent to ES =\> > > > > > > { > > > > > > "event": { > > > > > > "time\_periods": ["2010-11-11T14:00", > > > > > > "2010-11-11T14:15", "2010-11-11T14:30", "2010-11-11T14:45"] > > > > > > …. > > > > > > } > > > > > > > } > > > > > > > EVENT 2 (truncated and with a dodgy example rrule). A recurring event > > > > > > =\> > > > > > > { > > > > > > "event": { > > > > > > "rrule": "daily 14:00 until 14:30", > > > > > > "repeat\_start": "2010-11-10", > > > > > > "repeat\_until": "2010-12-10" > > > > > > …. > > > > > > } > > > > > > > } > > > > > > > EVENT 2 (truncated). Sent to ES =\> > > > > > > { > > > > > > "event": { > > > > > > "time\_periods": ["2010-11-10T14:00", > > > > > > "2010-11-10T14:15", "2010-11-10T14:30", "2010-11-10T14:45", > > > > > > "2010-11-11T14:00", "2010-11-11T14:15", "2010-11-11T14:30", > > > > > > "2010-11-11T14:45"….] > > > > > > …. > > > > > > } > > > > > > > } > > > > > > > At scheduled intervals, a recurring event's time periods are updated - > > > > > > old ones removed, new ones added. The time periods are a floating set of > > > > > > values that might typically span 2 months. > > > > > > > Our concern is that some recurring events are continuous (24 hours/day > > > > > > everyday), and others recur daily at multiple times during the same day. > > > > > > This leads to a large number of time periods for some events - 5952 time > > > > > > periods in 2 months (24/7 15 minute periods). The database will have about > > > > > > 10000 such frequently recurring events. We worry that our proposed approach > > > > > > might bring ES to a crawl - especially during indexing. > > > > > > > Is it reasonable to use ES in this way? Can ES handle a field with > > > > > > thousands of values? Does anyone have suggestions on how better to > > > > > > efficiently index and query ranged/recurring events? > > > > > > > Thanks in advance, > > > > > > > Mark ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 4:16am UTC](https://discuss.elastic.co/t/ranged-and-recurring-events/3567/9 "2017-07-06T04:16:31Z")