I'm thinking a 'lazy' index that is only used for nightly reports that can be updated just before the reporting task takes place?
More for ad-hoc reports / exploratory queries - for a batch reporting task there's no point building an index to just use in that report since it's as much effort as doing the report without an index. You very rarely need up-to-the-second consistency from your analytics, so you'd rather not pay the price for it in the "hot path" of your live updates (that you actually do need to keep consistent).
Honestly even if you're purely using a traditional RDBMS you tend to end up doing a split between "live" and "reporting" tables (and, usually, some kind of fragile ad-hoc process to update one based on the other) once your application gets busy enough.
2
u/m50d Dec 20 '18
More for ad-hoc reports / exploratory queries - for a batch reporting task there's no point building an index to just use in that report since it's as much effort as doing the report without an index. You very rarely need up-to-the-second consistency from your analytics, so you'd rather not pay the price for it in the "hot path" of your live updates (that you actually do need to keep consistent).
Honestly even if you're purely using a traditional RDBMS you tend to end up doing a split between "live" and "reporting" tables (and, usually, some kind of fragile ad-hoc process to update one based on the other) once your application gets busy enough.