Hi team, I need your help with a very complex scenario.
We have a user management system, which has a user index, which stores various attributes of user, and a user action log index, which records what the user has done at what time, the user action log index has a userId field.
Our requirement is to be able to filter out specific users based on multiple attributes and aggregate their actions over a time horizon: for example, how many emails and how many phone calls they made in last month.
Considering our time horizon can be one day, one month, or one year, the index of our operation records is cut by month like [user-log-202201, user-log-202202, ...].
The problem is that the number of users we filter out based on specified attributes can be very, very large (sometimes even hundreds of thousands), and it would be very slow to query the records of these userId using term Query in the user action log index.
We have a solution: we merge these two indices, so we only need to query log records by multi user attributes, but this poses another problem: we could update, delete or add a attribute for user at any time. after merge, updates a single attribute for one user, is equivalent to update all log records of this user, this also seems to have an impact on performance.
Is there a better solution?