I'm currently thinking about our database structure and how we modify it for scale. Specifically, we're thinking about using ElasticSearch to provide our search functionality.

One common pattern with ElasticSearch seems to be the 'user-routing' pattern; that is, using routing to ensure that any one user's data resides on the same shard. This is great for client-specific search e.g. Gmail.

Our application has a constraint such that any user will have a maximum of a few thousand documents, so this pattern seems like a good candidate. However, our search needs to work across all users, as well as targeting a specific user (so I might search my content, Alice's content, or all content). Similarly, we need to provide full-text search across any timeframe; recent months to several years ago.

I'm thinking of combining the 'user-routing' and 'index-per-time-interval' patterns:

• I create an index for each month
• By default, searches are aliased against the most recent X months
• If no results are found, we can search against previous X months
• As we grow, we can reduce the interval X
• Each document is routed by the user ID

So, this should let us do the following:

• search by user. This will search all indeces across 1 shard
• search by time. This will search ~2 indeces (by default) across all shards

Is this a reasonable approach, considering we may scale to multi-million+ documents? Or should I be denormalizing the data somehow, so that user searches are performed on a totally seperate index from date searches?

Thanks for any pros-cons of the above scenario.