This looks good. Having one is always better than not. From my perspective, pre-aggregation/standing query suits this scenario (which refers to repeated queries) better.

For the context of result cache, I came up with these points on the design level that we may need to figure out before starting implementation:

• Where to put this cache? On frontend or datanode? This also relates to the next point
• How to invalidate the cache
• How to combine the cached result and uncached result
• What's the granularity of this cache? Like from your example, the cache needs to be parted by hour so 17:00 ~ 18:00 can fit into two queries.

Could you give a more detailed design or draft an RFC? This is a relatively huge topic. And appreciate your enthusiasm ❤️

If you want to speed up your aggregation queries, especially those from the dashboard, then pre-aggregation would be a better solution. This is similar to Prometheus' recording rules.

whereas the rollupResultCache has a broader design scope and is intended for caching for any queries

I guess you're looking for a more general result cache, not just accelerating aggregation queries. However, even in observable scenarios, the latest data may not have been fully written when queried, so we still need to implement a more refined cache strategy.

I currently don't have enough bandwidth to design or implement this feature, but if you're interested in implementing it, feel free to submit an RFC.

but I'm uncertain about the optimal location for its placement. frontend? datanode or a standardalone service 🤔?

A good cache eviction mechanism can be implemented on either the frontend or datanode. Implementing it on the datanode allows requests from multiple frontends to share the same cache. We can also invalidate the cache based on ingested data.
In frontend, we can get the final output record batches but we might not always send queries (same as write requests which may reset the cache) to the same frontend.

I guess you're looking for a more general result cache, not just accelerating aggregation queries. However, even in observable scenarios, the latest data may not have been fully written when queried, so we still need to implement a more refined cache strategy.

The cache is specifically designed with the following circumstance:

Picture a user with a Grafana dashboard that features a run chart, querying the time-series data of the last three hours. This user clicks the refresh button every one minute to retrieve the most recent data.

The cache can store the data range spanning between the last three hours and ending at the last cacheTimestampOffset (which is, by default, set to 5 minutes in VictoriaMetrics). This proposed cache can significantly reduce the retrieval time, as it only necessitates fetching data from the past cacheTimestampOffset period from the storage system.

It seems that my version of Grafana is outdated and lacks this particular feature🤔. This revelation has addressed my issue, and I would now like to conclude this discussion. Thank you~

It's a feature that is disabled by default. Doc is here (search "incremental")

You can set it on data-source page (the "greptimedb" is only the name of my data source):

And here is an example for 10mins delta query