PolarDB:Column-store snapshots

Supported versions

Benefits

The column-store snapshot feature allows you to query historical data within the snapshot retention period (up to one year). You can use the column-store snapshot feature to:

• Restore data from an earlier point in time.

• Back up data at key points in time.

• Perform computational analysis on data at an earlier point in time.

Differences from row-store snapshots

• The retention period of row-store snapshots is short (up to three days are recommended). Column-store snapshots can be retained for a longer period (up to one year). For more information, see Row-store snapshots.

• Column-store snapshots are stored in OSS in the form of clustered columnar indexes (CCIs), while row-store snapshots are stored in disks of data nodes in the form of undo logs.

• Column-store snapshots can be used on PolarDB-X instances which run MySQL 5.7 and MySQL 8.0. Row-store snapshots are currently supported only on PolarDB-X instances which run MySQL 8.0.

• Due to the latency in data synchronization between the primary instance and the read-only columnar instance, column-store snapshots may have latency when you query historical data. However, row-store snapshots do not have latency.

Features

Create a column-store snapshot

Column-store snapshots are implemented based on CCIs. You can create a column-store snapshot for an existing table or create a column-store snapshot when you create a table. The following figure shows how to create a column-store snapshot for an existing table.

Generate a snapshot point

The CALL polardbx.columnar_flush() function generates a snapshot point and returns a value of the unsigned long type which represents the version of the snapshot point (a timestamp independent of the time-zone). The following figure shows how to generate a snapshot point.

Query column-store snapshots

Query the data of a snapshot point by using the SELECT AS OF TSO statement. The following figure shows how to query column-store snapshots.