Hologres versions 3.1 and later support the query cache feature, which caches query results in memory. When the system receives an identical or similar query, it returns the matching results from the cache. This process avoids data reads and computation overhead, which improves query performance. The query cache is most effective for high-concurrency, repetitive query scenarios, such as stress testing and BI reporting, because it significantly improves query efficiency and reduces resource waste.
Important notes
The query cache is supported only in Hologres versions 3.1 and later.
NoteIf your instance runs an earlier version, you can perform an instance upgrade or join the Hologres DingTalk group to request an upgrade. For instructions on how to join the online support DingTalk group, see How do I get more online support?.
The query cache is disabled by default. You must enable it before use.
-- Enable query cache. Requires superuser privileges. Takes effect for new connections after enabling. ALTER DATABASE <database_name> SET hg_experimental_enable_query_cache=on;Query cache results are weakly consistent. If data is written during the cache retention period, the results may be incorrect.
Queries that contain the RANDOM function are not cached.
Use Query Cache
Hologres memory is divided into three main categories: query execution, cache, and metadata. The cache can use up to 30% of the total instance memory. For example, if a worker has a 64 GB memory limit, its cache can use up to 20 GB. The query cache uses a portion of this cache memory.
query_cache_percentage: Set memory allocated to Query Cache
You must allocate memory for the query cache to function effectively.
Memory per worker = 20 GB × query_cache_percentage × 0.01
Total memory across all workers = Memory per worker × Number of workers
-- Example: query_cache_percentage=2 (2%). Allocated memory = 20 GB × 2 × 0.01 = 400 MB
SELECT hg_admin_command('set_global_flag', 'query_cache_percentage=2');Set maximum memory per cached result
The default maximum memory for a single cached result is 10 MB. You can change this value using the following parameter. The maximum value is 20 GB.
-- Adjust maximum memory per cached result (default: 10 MB)
ALTER DATABASE <database_name> SET hg_experimental_query_cache_max_kb_per_item=10240;Adjust Query Cache time-to-live (TTL)
The Hologres cache uses eviction policies that are based on factors such as memory limits and the retention period. For more information, see Storage technology deep dive. You can use the following parameter to adjust the query cache TTL.
The default TTL is 60 seconds.
The TTL is specified in seconds.
-- Adjust Query Cache TTL in seconds (default: 60 seconds)
ALTER DATABASE <database_name> SET hg_experimental_query_cache_expire_time_sec=60;