PolarDB:Row-store snapshots

Supported versions

How it works

Each point in time within the retention period is a valid snapshot point. PolarDB assigns each snapshot point a Timestamp Oracle (TSO) value. To query data at a specific point, convert a human-readable timestamp to a TSO value using TIME_TO_TSO(), then reference that TSO in your SELECT or INSERT INTO ... SELECT statement.

Because snapshots are backed by undo logs, a limited storage size is allocated to undo logs and the retention period is relatively short.

For long-term snapshot retention (up to one year), use column-store snapshots instead. The two features can be used at the same time.

Billing

Row-store snapshots are free of charge. The feature increases undo log volume and storage usage, so plan your storage capacity accordingly.

Limitations

• After running a DDL statement on a table, historical snapshots created before the DDL cannot be queried.

• Snapshots outside the retention period cannot be queried.

Prerequisites

Before you begin, ensure that you have:

• The storage layer parameter loose_opt_flashback_area set to true in the PolarDB console — see Parameter settings

• The storage layer parameter loose_innodb_txn_retention set to your target retention period in seconds

Retention period guidance

Query historical data

Use the AS OF TSO clause to target a specific snapshot. The examples below show the two supported usage patterns.

Usage patterns at a glance

-- Convert a specific timestamp (uses the current session time zone)
SELECT TIME_TO_TSO("2024-07-24 10:00:00");

-- Convert the current time
SELECT TIME_TO_TSO(NOW());

-- Specify a time zone explicitly
SELECT TIME_TO_TSO("2024-07-24 10:00:00", "+8:00");

-- Replace 7206138458723582016 with the TSO value returned by TIME_TO_TSO()
SELECT * FROM tb1 AS OF TSO 7206138458723582016;

-- Create a temporary table with the same schema as the original
CREATE TABLE tmp (
  -- same columns as the source table
);

-- Copy the snapshot data into the temporary table
INSERT INTO tmp SELECT * FROM tb1 AS OF TSO 7206138458723582016;

End-to-end example

This example creates a table, inserts data at different points in time, captures TSO values at each point, and then queries and restores historical data.

Step 1: Set parameters

Configure loose_opt_flashback_area and loose_innodb_txn_retention as described in Prerequisites.

Step 2: Create the table

CREATE TABLE tb1 (
  id INT PRIMARY KEY,
  a INT,
  gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) PARTITION BY KEY(id);

Step 3: Insert data and capture TSO values

INSERT INTO tb1 (id, a) VALUES (0, 0);

-- Wait 1 second, then capture TSO1
SELECT TIME_TO_TSO(NOW());  -- record this value as TSO1

INSERT INTO tb1 (id, a) VALUES (1, 1);

-- Wait 1 second, then capture TSO2
SELECT TIME_TO_TSO(NOW());  -- record this value as TSO2

INSERT INTO tb1 (id, a) VALUES (2, 2);

Step 4: Query historical snapshots

-- Returns: (0, 0)
SELECT * FROM tb1 AS OF TSO {TSO1} ORDER BY id;

-- Returns: (0, 0), (1, 1)
SELECT * FROM tb1 AS OF TSO {TSO2} ORDER BY id;

-- Returns: (0, 0), (1, 1), (2, 2)
SELECT * FROM tb1 ORDER BY id;

Step 5: Restore data from a snapshot

CREATE TABLE tb1_tmp (
  id INT PRIMARY KEY,
  a INT,
  gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) PARTITION BY KEY(id);

-- Restore data from the snapshot at TSO into the temporary table
INSERT INTO tb1_tmp SELECT * FROM tb1 AS OF TSO {TSO} FORCE INDEX(PRIMARY);

What's next

• Column-store snapshots — for long-term retention (up to one year)

• Parameter settings — to configure storage layer parameters

• View and update the version of an instance