I'm storing timestamped change data coming off a SQL database into Elasticsearch and would like some advice. It is over 100 tables and most will be fairly small (small number of bytes + not many changes over course of the day) with a few tables being much larger and more active.
I'm curious if you all would recommend one index per table with an alias or one mono-index.
- Every table will have similar context information (timestamp, user id, org id, etc)
- Queries will usually be over all tables by timestamp. I'll likely be sorting by timestamp and aggregating a lot across tables.
- I'll like be rolling over the index/indices every week or month.
- There will be about 1000 columns (so 1000 fields about total).
- Very rarely add columns (and therefore fields).
- Quite a few of the tables are small enough that they wouldn't even necessarily need their own shard space-wise(quite a few would be less than 10GB per month).
I'll be writing into it with bulk action, from one process atm. I'm mostly focused on query speed.
I realize there isn't always a straightforward answer here, but I'd love any thoughts you could provide.