Query cache in AnalyticDB for PostgreSQL V7.0

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for PostgreSQL V7.0 instance with minor engine version v7.0.6.9 or later

To check your minor engine version, see View the minor engine version. If your version does not meet this requirement, see Upgrade the minor engine version.

Use cases

The query cache is most effective in read-heavy workloads with high query repetition, such as:

• Analytical dashboards: Multiple users run the same aggregate queries against large tables throughout the day.

• Reporting workloads: Scheduled reports repeatedly query the same dataset within a short time window.

• Batch analytics on static data: Data is loaded periodically (for example, nightly), and the same analytical queries run repeatedly between loads.

The query cache provides less benefit when tables are updated frequently, because cache entries are invalidated on every Data Definition Language (DDL) or Data Manipulation Language (DML) operation.

Enable the query cache

The query cache requires high temporal locality (frequent repetition of the same queries) to be effective, so it is disabled by default. To enable it for your instance, contact technical support.

Configure query cache at the table level

AnalyticDB for PostgreSQL V7.0 provides the querycache_enabled table-level parameter to control which tables participate in the query cache. This lets you exclude frequently updated tables from caching while keeping it active for stable tables.

For a query to use the cache, both conditions must be true:

• The query cache feature is enabled at the instance level.

• The querycache_enabled parameter is set to on for every table referenced in the query.

Enable the cache for a new table:

CREATE TABLE table_name (c1 int, c2 int) WITH (querycache_enabled=on);

Enable the cache for an existing table:

ALTER TABLE table_name SET (querycache_enabled=on);

Disable the cache for a table:

ALTER TABLE table_name SET (querycache_enabled=off);

Cache invalidation

Cache entries for a table are invalidated whenever a DDL or DML operation is performed on that table. The next identical query re-executes and stores a fresh result.

CREATE TABLE test1 (c1 int, c2 int) WITH (querycache_enabled=on);
1: BEGIN;
1: SELECT * FROM test1;
2: BEGIN;
2: INSERT INTO test1 values (3, 4);
2: COMMIT;
1: COMMIT;
-- This query returns an empty result instead of (3,4) because it uses the previous cache.
1: SELECT * FROM test1;

Performance benchmarks

The query cache does not significantly improve performance for transactional processing (TP) workloads, nor does it have a negative impact. For analytical processing (AP) workloads, the following results were measured using 10 GB TPC-H and TPC-DS benchmarks:

For TPC-H, a cache hit on a query such as Q1 completes in approximately 10 milliseconds — a performance improvement of more than 1,000 times compared to a full execution. In theory, any cache hit should complete in under 1 second.

The total benchmark runtime was approximately 10 seconds for both workloads rather than the theoretical minimum, because some result sets exceeded the default 1 MB per-query cache size limit and were not cached.

Limitations

The query cache does not apply in the following situations:

• Direct access to partition child tables.

• The libpq frontend protocol version is earlier than 3.0.

• A single query references more than 128 tables.

• Cursors are used in an Extended Query.

• The transaction isolation level is set to READ UNCOMMITTED or SERIALIZABLE.

• The querycache_enabled parameter is not set to on for one or more tables in the query.

• The Grand Unified Configuration (GUC) parameter gp_select_invisible is set to on.

• A modification operation occurs within a transaction block before the query executes.

• The query targets temporary tables, views, materialized views, system tables, unlogged tables, foreign tables, or calls volatile or immutable functions.

• The result set exceeds the per-query cache size limit (1 MB by default). To increase this limit, contact technical support. The change requires an instance restart.

What's next

• View the minor engine version

• Upgrade the minor engine version

• Manage transactions