PolarDB:Cache prefetching

How it works

PolarDB-X uses a compute-storage separation architecture. By default, compute nodes check the local cache first. On a cache miss, the columnar engine fetches data from OSS and backfills it into the cache hierarchy — this is passive mode.

Cache prefetching adds an active mode: the WARMUP statement pushes specified columnar data into the cache on a schedule you control. The two modes work together:

Instance support

• Cache prefetching is supported on columnar read-only instances and on primary instances that have an attached columnar read-only instance.

• When you run WARMUP on the primary instance, it is forwarded to the columnar read-only instance. The primary instance acts as a proxy only.

Usage notes

-- Efficient: loads only data for the specified date
SELECT * FROM orders WHERE date = '2023-10-01';

-- Inefficient: pulls the entire table
SELECT * FROM orders;

Use cases

Cache prefetching suits stable, predictable query patterns. It is not suitable for ad hoc queries, which vary too much to benefit from a fixed prefetching schedule.

Syntax

WARMUP [cron_expression]
SELECT
    select_expr [, select_expr] ...
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ...]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

cron_expression:
    ('cron_string') | <empty>

select_expr:
    column_name
  | aggregate_function(column_name)
  | column_name AS alias_name
  | expression

To run multiple SELECT statements in a single WARMUP call, enclose each in braces ({ }):

WARMUP [cron_expression]
{SELECT ...}
{SELECT ...}
{SELECT ...};

Examples

When WARMUP has no CRON expression, the prefetch runs immediately. When a CRON expression is included, the prefetch runs on the defined schedule.

The following examples use the TPC-H 100 GB test dataset. For details, see TPC-H tests.

WARMUP SELECT * FROM lineitem;

WARMUP('*/3 * * * *')
SELECT *
FROM lineitem
WHERE l_shipdate > '2024-09-01';

WARMUP('0 8 * * *')
SELECT *
FROM lineitem, part
WHERE l_partkey = p_partkey;

WARMUP('*/5 1-5 * * *')
SELECT *
FROM lineitem, part
WHERE l_partkey = p_partkey;

Manage prefetching tasks

-- List all scheduled tasks in the database named database1
SELECT * FROM information_schema.columnar_warmup WHERE schema_name = 'database1';

-- Show executions from the past 2 days
SELECT * FROM information_schema.warmup_execution_logs
WHERE start_time >= NOW() - INTERVAL 2 DAY;

-- Delete a specific task
WARMUP DELETE <TASK_ID>

-- Delete all tasks
WARMUP DELETE ALL;

-- Pause a specific task
WARMUP SUSPEND <TASK_ID>

-- Pause all tasks
WARMUP SUSPEND ALL;

-- Resume a specific task
WARMUP RESUME <TASK_ID>

-- Resume all tasks
WARMUP RESUME ALL;

Best practices

Choose a prefetching strategy based on your query pattern:

High-write workloads with low-latency requirements — Schedule prefetching on commonly used columns of the target table every 2 minutes to keep the cache fresh after writes:

WARMUP('*/2 * * * *')
SELECT col1, col2, col3 FROM <table_name>;

Scheduled analytical reports — If your analytics job runs at 22:00 every day, schedule a prefetch 30 minutes earlier to warm the cache:

WARMUP('30 21 * * *')
SELECT col1, col2, col3
FROM <table_name1>
LEFT JOIN <table_name2> ON <table_name1>.id1 = <table_name2>.id2
LEFT JOIN <table_name3> ON <table_name1>.id1 = <table_name3>.id3
WHERE <table_name1>.col_date > '2024-01-01';

Performance benchmarks (TPC-H, ClickBench) — Run a one-time prefetch across all tables before testing to eliminate cold-start variance from results:

WARMUP SELECT * FROM lineitem;
WARMUP SELECT * FROM orders;
WARMUP SELECT * FROM customer;
WARMUP SELECT * FROM part;
WARMUP SELECT * FROM partsupp;
WARMUP SELECT * FROM supplier;
WARMUP SELECT * FROM region;
WARMUP SELECT * FROM nation;

Maintenance windows — Use a time-bounded CRON expression to confine prefetching to off-peak hours:

WARMUP('*/5 1-5 * * *')
SELECT * FROM lineitem;