Build & Automate Xscale Columnstore Snapshots - PolarDB

A columnstore snapshot lets you query a point-in-time view of your data using the columnstore engine without affecting row-store writes. Use it when you need to run periodic analytical queries on a large table—such as daily reporting or batch aggregations—while keeping transactional workloads isolated.

Key concepts

Columnstore index (CCI): The underlying storage structure that organizes table data in columnar format for analytical queries. A columnstore snapshot is built on top of a CCI.
Columnstore snapshot: A time-stamped, queryable snapshot of the CCI data. The system can generate snapshots automatically at a configurable interval and retain each snapshot for a configurable number of days.
Snapshot retention: After the retention period expires, the snapshot may no longer be queryable. The retention period is independent of time zone settings.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for Xscale instance running version 5.4.19-20250305 or later (version 5.4.20-20250328 or later is recommended for optimized snapshot performance)
A table with MySQL 5.7 or MySQL 8.0 as the data node database engine

For version naming rules, see Release notes. To check your instance version, see View and update the engine version of a PolarDB for Xscale instance.

Supported versions

Three-digit version	Compute node version	Recommended version	Data node engine
5.4.20	All versions	5.4.20-20250328 or later	MySQL 5.7 or MySQL 8.0
5.4.19	5.4.19-20250305 or later	—	MySQL 5.7 or MySQL 8.0

Create a columnstore snapshot

A columnstore snapshot is created as part of a clustered columnar index (CCI). Use the CREATE CLUSTERED COLUMNAR INDEX statement and set "type":"snapshot" in the columnar_options clause.

For background on CCIs, see Create and use a clustered columnar index.

Syntax

CREATE
    CLUSTERED COLUMNAR INDEX snapshot_name
    ON tbl_name (index_sort_key_name,...)
    [partition_options]
    columnar_options='{
      "k1":"v1",
      "k2":"v2"
    }'

The columnar_options clause accepts the following parameters:

Parameter	Required	Description
`type`	Yes	Whether to create a columnstore snapshot. Set to `snapshot` to create one. The default value `default` does not create a snapshot.
`snapshot_retention_days`	No	Retention period in days. Valid values: 1–366. Default: `7` (the global default takes precedence if one is set). After the retention period ends, the snapshot may no longer be queryable. This parameter is independent of time zone settings. For example, if `snapshot_retention_days` is set to `7`, a snapshot created at time T0 is retained for at least T0+7 days. After T0+7 days, the snapshot may no longer be queryable.
`auto_gen_columnar_snapshot_interval`	No	Interval in minutes between automatic snapshot generations. Default: `-1` (automatic generation disabled). All non-default values must be ≥ 5. After you set this parameter, the system calls `CALL polardbx.columnar_flush(schema_name, table_name, index_name)` at each interval. The first call may be delayed by up to 1 minute. To generate snapshots manually, see Generate a snapshot.

Creation methods

Choose the method that matches your situation:

Option 1 — Create a table with a CCI and snapshot in one statement (recommended for new tables)

CREATE TABLE t_order (
  `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
  `order_id` VARCHAR(20) DEFAULT NULL,
  `buyer_id` VARCHAR(20) DEFAULT NULL,
  `seller_id` VARCHAR(20) DEFAULT NULL,
  `order_snapshot` LONGTEXT DEFAULT NULL,
  `order_detail` LONGTEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`),
  CLUSTERED COLUMNAR INDEX `seller_snapshot` (`seller_id`)
    PARTITION BY HASH(`order_id`)
    PARTITIONS 16
    COLUMNAR_OPTIONS='{
      "type":"snapshot",
      "snapshot_retention_days":"7",
      "auto_gen_columnar_snapshot_interval":"30"
    }'
) ENGINE=INNODB DEFAULT CHARSET=UTF8 PARTITION BY HASH(`order_id`) PARTITIONS 16;

Option 2 — Add a CCI and snapshot to an existing table using `CREATE CLUSTERED COLUMNAR INDEX`

CREATE CLUSTERED COLUMNAR INDEX `seller_snapshot` ON t_order (`seller_id`)
PARTITION BY HASH(`order_id`)
PARTITIONS 16
COLUMNAR_OPTIONS='{
  "type":"snapshot",
  "snapshot_retention_days":"7",
  "auto_gen_columnar_snapshot_interval":"30"
}';

Option 3 — Add a CCI and snapshot to an existing table using `ALTER TABLE`

ALTER TABLE t_order ADD CLUSTERED COLUMNAR INDEX `seller_snapshot` (`seller_id`)
PARTITION BY HASH(`order_id`)
PARTITIONS 16
COLUMNAR_OPTIONS='{
  "type":"snapshot",
  "snapshot_retention_days":"10",
  "auto_gen_columnar_snapshot_interval":"30"
}';

Option 4 — Enable snapshot mode on an existing CCI

If the table already has a CCI, convert it to a snapshot by calling polardbx.columnar_set_config:

CALL polardbx.columnar_set_config(<cci_id>, 'type', 'snapshot');

cci_id is the ID of the columnstore index. Retrieve it with SHOW COLUMNAR STATUS. For more information, see SHOW COLUMNAR STATUS.

Modify columnstore snapshot properties

Modify settings for a specific snapshot

Replace <cci_id> with the ID of the target columnstore index:

-- Set the retention period to 10 days
CALL polardbx.columnar_set_config(<cci_id>, 'snapshot_retention_days', 10);

-- Generate a snapshot every 10 minutes
CALL polardbx.columnar_set_config(<cci_id>, 'auto_gen_columnar_snapshot_interval', 10);

-- Disable automatic snapshot generation
CALL polardbx.columnar_set_config(<cci_id>, 'auto_gen_columnar_snapshot_interval', -1);

Set global defaults

Global defaults apply to new columnstore snapshots only—existing snapshots are not affected.

-- Set the default automatic snapshot interval to 20 minutes
CALL polardbx.columnar_set_config('auto_gen_columnar_snapshot_interval', 20);

-- Set the default retention period to 7 days
CALL polardbx.columnar_set_config('snapshot_retention_days', 7);

When you create a columnstore snapshot without specifying snapshot_retention_days or auto_gen_columnar_snapshot_interval, the system uses the global defaults for those parameters.

View a columnstore snapshot definition

SHOW FULL CREATE TABLE t_order;

Delete a columnstore snapshot

Deleting the CCI or the table removes the associated snapshot:

-- Delete only the columnstore index and its snapshot
DROP INDEX `seller_snapshot` ON t_order;

-- Delete the table and all associated indexes and snapshots
DROP TABLE t_order;

What's next

Generate a snapshot — manually trigger snapshot generation using polardbx.columnar_flush
SHOW COLUMNAR STATUS — retrieve CCI IDs and check columnstore status
Create and use a clustered columnar index — learn the full CCI creation and usage workflow