Data Cache-AnalyticDB(AnalyticDB)-阿里云帮助中心

The Beam storage engine of AnalyticDB for PostgreSQL provides the Data Cache feature to cache decompressed columnar data blocks, significantly improving query performance in scenarios such as index point queries and nested loop joins.

Overview

The Beam storage engine of AnalyticDB for PostgreSQL provides the Data Cache feature to cache decompressed columnar data blocks. In scenarios such as index point queries and nested loop joins where the same data blocks are read repeatedly, Data Cache avoids repeated decompression and significantly improves query performance.

Limitations

Data Cache currently takes effect only for index scan scenarios. Other scan methods such as full table scans (SeqScan) are not supported.
Data Cache is stable in v7.3.0.x and later. The default cache size is 128 MB. To adjust the cache size, submit a ticket to contact technical support.

Working Principle

The Beam storage engine stores data in columnar format. When the same data block is accessed multiple times (such as index point query lookups), repeated decompression consumes significant CPU time. Data Cache allocates a region in shared memory to cache decompressed data blocks. Subsequent accesses to the same data block are read directly from the cache, skipping the decompression step.

Session-Level Parameter

Parameter	Default Value	Description
`beam_session_enable_decompressed_cache`	off	Session-level switch. When set to on, index scan queries in the session use the Data Cache path. Decompressed data blocks are cached and subsequent accesses are read directly from the cache.

Usage

Enable at Session Level

Enable Data Cache in the current session:

-- Enable Data Cache in the current session
SET beam_session_enable_decompressed_cache TO on;

-- Execute queries. Data blocks repeatedly accessed by index scans are cached.
SELECT * FROM orders WHERE order_id IN (1001, 1002, 1003);

-- Disable after use to avoid affecting other queries.
SET beam_session_enable_decompressed_cache TO off;

Disable at Session Level

SET beam_session_enable_decompressed_cache TO off;

Scenarios

Important

Prerequisite: The following scenarios require data access through index scans (Index Scan / Bitmap Scan) for Data Cache to take effect. Full table scan scenarios do not apply.

Scenario 1: Nested Loop Join

Characteristics: The inner table is repeatedly scanned by each row of the outer table through an index. The same data block is decompressed multiple times, resulting in high CPU overhead.

Recommendation: Ensure the inner table uses an index scan and enable Data Cache to avoid repeated decompression.

SET beam_session_enable_decompressed_cache TO on;

-- Small table drives large table. Inner table uses index scan.
SELECT /*+ NestedLoop(t1 t2) */ *
FROM small_table t1
JOIN big_table t2 ON t1.key = t2.key;
-- Ensure t2.key has an index for Index Scan.

Scenario 2: Index Point Query

Characteristics: Frequently accesses different rows in the same data block through an index. Each lookup requires decompression.

Recommendation: Enable Data Cache for the point query target table to cache hot data blocks.

SET beam_session_enable_decompressed_cache TO on;

-- Batch index point queries
SELECT * FROM orders WHERE order_id IN (1001, 1002, 1003, 1004, 1005);
-- Ensure order_id has an index for Index Scan.

Notes

Index scan only: Data Cache currently takes effect only for index scan (Index Scan / Bitmap Scan) scenarios. Full table scans (SeqScan) and other scan methods are not supported.
Disabled by default: This parameter is off by default. Enable it only in index repeated-read scenarios with clear benefits.
Session-level: SET takes effect only for the current session and automatically reverts to the default value after disconnection.
Not suitable for full table scans: Cache hit rate is low in large-range scan scenarios, which are not currently supported. Not recommended.