Query cache stores the results of frequently repeated queries and returns them directly on subsequent identical requests—bypassing full query execution. This reduces query latency for read-heavy workloads where the same queries run repeatedly within a short time window.
Prerequisites
Before enabling query cache, make sure that:
-
Your instance runs V6.3.7.0 or later. To check your version, see View the minor version of an instance. To upgrade, see Update the minor version of an instance.
-
Query cache is enabled for the instance (disabled by default). To enable it, Submit a ticket.
Limitations
Configuration requirements
| Requirement | Detail |
|---|---|
| Transaction isolation level | Must be set to READ COMMITTED |
GUC parameter rds_uppercase_colname |
Must be set to off |
GUC parameter gp_select_invisible |
Must be set to off |
| libpq library frontend protocol | Must be version 3.0 or later |
| Table-level enablement | Query cache must be enabled on every table involved in the query |
Unsupported query types
Query cache does not apply to:
-
Queries on temporary tables, views, materialized views, system tables, unlogged tables, or external tables
-
Queries that call volatile or immutable functions
-
Queries on partitions
-
Queries involving cursors in extended queries
-
Queries executed after a data modification within the same transaction block
-
Queries with result sets larger than 7.5 KB
-
Queries that join more than 32 tables
Unsupported configurations
-
Instances with multiple coordinator nodes
Enable query cache for an instance
Query cache is disabled by default because it is only effective for workloads with high query repetition. To enable it, Submit a ticket.
After enabling query cache, restart the instance for the change to take effect. Schedule the restart during off-peak hours to minimize disruption.
Enable or disable query cache for a session
Use the rds_session_use_query_cache parameter to control query cache at the session level.
Enable query cache for the current session:
SET rds_session_use_query_cache = on;Disable query cache for the current session:
SET rds_session_use_query_cache = off;Enable or disable query cache for a table
Use the querycache_enabled storage parameter to control query cache at the table level.
Enable query cache when creating a table:
CREATE TABLE table_name (c1 int, c2 int) WITH (querycache_enabled=on);Enable query cache for an existing table:
ALTER TABLE table_name SET (querycache_enabled=on);Disable query cache for a table:
ALTER TABLE table_name SET (querycache_enabled=off);Query cache only applies to a query when it is enabled on all tables involved in that query.
Manage cache validity
By default, cached results expire after 10 minutes. If the same query runs after the 10-minute window, the system re-executes the query instead of returning cached results.
Cache invalidation on writes: Any DDL or DML statement on a table invalidates that table's cached results, preventing stale data from being returned after writes.
AnalyticDB for PostgreSQL uses multiversion concurrency control (MVCC) and stores only the most recent query results per cache entry. In high-concurrency environments where read and write transactions are committed simultaneously, the cache may not reflect the very latest committed data within the validity window.
To change the default 10-minute validity period, Submit a ticket.
Performance
Query cache delivers significant performance gains when cache hit rates are high, with minimal overhead when they are not.
The TPC-H and TPC-DS tests below are based on the TPC-H and TPC-DS benchmark methodologies but do not meet all requirements of the official benchmarks. Results cannot be compared with published TPC-H and TPC-DS benchmark results.
OLTP (online transaction processing)
At 100% cache hit rate, query cache improves point query performance by approximately 6.5 times—from 1,718 TPS (transactions per second) to 11,219 TPS.
When the cache hit rate is 0%, the overhead is minimal: query latency stays under 20 ms even with cache replacement, compared to the baseline of approximately 10 ms.
| Scenario | Cache disabled | Cache enabled |
|---|---|---|
| 0% hit rate, 1 statement | 1,718 TPS | Without replacement: 1,399 TPS; with replacement: 915 TPS |
| 50% hit rate, 2 statements | 807 TPS | Without replacement: 1,367 TPS; with replacement: 877 TPS |
| 100% hit rate, 1 statement | 1,718 TPS | 11,219 TPS |
OLAP (online analytical processing)
For analytical workloads on 10 GB of data, query cache cuts total query time when repeated queries hit the cache:
| Benchmark | Cache disabled | Cache enabled |
|---|---|---|
| TPC-H 10 GB | 1,255s | 522s |
| TPC-DS 10 GB | 2,813s | 1,956s |
Individual queries that match cached results can see extreme speedups—TPC-H Q1, for example, improves by more than 1,000 times on a cache hit. Queries returning result sets larger than 7.5 KB are not cached, which limits the overall improvement across a full benchmark run.
When to use query cache
Query cache is most effective when:
| Workload characteristic | Recommendation |
|---|---|
| Same queries run repeatedly within minutes (high temporal locality) | Enable cache; set validity period to match your query repetition window |
| Read-heavy with infrequent writes to cached tables | Enable cache; benefit is maximized when DML is rare |
| Result sets are small (under 7.5 KB) | Enable cache; large result sets bypass the cache automatically |
| BI dashboards with fixed report queries | Enable cache; standardize query text in the BI tool to ensure exact matching |
Query cache provides limited benefit for:
-
Ad-hoc or unique analytical queries where cache hits are rare
-
Tables with frequent writes, as DML operations invalidate cached results
-
Queries that return large result sets exceeding the 7.5 KB cache limit