Support query result caching

[osscm]

Query Result Caching involves the temporary storage of previously executed query results to expedite future data retrieval requests. When a query is executed against a dataset, the result set is cached in memory or disk storage. Subsequent identical or similar queries can then be served directly from the cache rather than re-executing the query against the underlying data source.

This caching mechanism significantly improves query performance and reduces computational overhead, especially for frequently accessed or computationally intensive queries. Query Result Caching is a critical optimization technique employed in big data analytics platforms to enhance query response times, improve system scalability, and optimize resource utilization.

Trino users seeking this functionality often implement it within their application layer. Different commercial solutions offer variations of this feature to cater to diverse needs.

BigQuery: https://cloud.google.com/bigquery/docs/cached-results
Snowflake: https://docs.snowflake.com/en/user-guide/querying-persisted-results
Athena: https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-athena-query-result-reuse-accelerate-queries/
SB: https://docs.starburst.io/latest/admin/cache-service.html

[osscm]

I think we can collaborate to define the scope and consider gradually implementing it, taking into account various use cases and the time/resources teams are willing to allocate. Since there may be multiple implementation approaches with varying limitations, certain options might be suitable for specific use cases.

We can add more subtasks to scope the individual tasks.

[YuriyGavrilov]

Talk about query caching https://youtu.be/t7ndqYUhKSA?si=1wg1TWIVG7FQZzBR at Microsoft

[shezis]

@YuriyGavrilov Sadly no PR for their caching service 😢

[sopel39]

@YuriyGavrilov @shezis are you still interested in this? I think an interesting approach would be to use something like a DuckDB to act as a result cache on coordinator. This way cached data would also be queryable (e.g. if subsequent queries would be running slice and dice queries on subset of data).

[shezis]

This is a promising way of achieving it. Will be easier to implement.

[masayyed]

This is a highly valuable feature request, especially for environments integrating Trino with BI tools like Tableau.

These tools frequently generate identical queries repeatedly, for instance, when users refresh dashboards or interact with visual elements. A native Query Result Caching mechanism would significantly reduce the redundant computational load on the Trino cluster by serving these repeated requests from cache.

[YuriyGavrilov]

This is a highly valuable feature request, especially for environments integrating Trino with BI tools like Tableau.

These tools frequently generate identical queries repeatedly, for instance, when users refresh dashboards or interact with visual elements. A native Query Result Caching mechanism would significantly reduce the redundant computational load on the Trino cluster by serving these repeated requests from cache.

Yep, you are right. But same way tableau uses cache, you can't set it up by hours number on the server to save cache 1,2 or 8 hours. Also you can refresh it manually by adding some parameter in the url link.

For the Trino I think should be different approach. Commercial trino like cedrusdata uses cache on iceberg row level groups on different nodes and save this cache on local drive. It nicely works with joins and etc. it didn't works with external catalogs.

External trino catalogs are not always can maintain analytical workloads so this is a pain point for caches. One Postgres are large second are not. So caches it this level could be useful with some ttl because analytics want to make dashboard rapidly but they have no time today to make dbt etl to iceberg ( with normal speed ) so they ask to connect production Postgres through trino directly but Postgres owner will don't like. May be to have to caches ttl on specially this Postgres will make some benefit.

[YuriyGavrilov]

Simple way are also could be, to save results but need to take care of one select * with limits 200 are not the same results like another same query. Some ttl cache in minutes could be applied here.

( we have different trino clusters one for Bi one for etl and another for bi and services - so if such cache will appear we can turn it on only on bi trino cluster. But need to have option how to remove or update this cache in the query like tableau do in url.

Exp: set refresh = now or select * from ... // -- refresh

[sopel39]

For the Trino I think should be different approach. Commercial trino like cedrusdata uses cache on iceberg row level groups on different nodes and save this cache on local drive. It nicely works with joins and etc. it didn't works with external catalogs.

There are many levels of caching really. One could describe them as more front-end caching and back-end caching. Font-end caching (as described here) would usually require some TTL as the data might be combined across different tables which can be frequently modified. Backend caching (like #22827) often doesn't require TTL as it caches per file (split), which are usually immutable in lakehouses.