Time Travel and Flashback Recovery

Time Travel provides SynxDB Elastic with a powerful suite of data protection and historical data analysis capabilities. It allows users to query the state of data at a specific point in the past (that is, historical query) and to quickly recover database objects that were accidentally deleted (that is, flashback recovery).

This feature is a valuable tool for data auditing, recovering from operational errors, and analyzing data evolution trends.

User value

Introducing the Time Travel feature brings the following core values to users:

Instant data recovery: When operational errors occur (such as an UPDATE without a WHERE clause or a DELETE that removes too much data), there’s no need to restore from complex backups. You can quickly retrieve the correct data simply by querying its state before the erroneous operation.
Historical data auditing and analysis: Easily query and analyze data at any point in the past for troubleshooting, data auditing, or analyzing trends over time.
Simplified database operations: Quickly recover an accidentally dropped table with a simple UNDROP command, significantly reducing operational complexity and recovery time objective (RTO).
Flexible query capabilities: Support joining and analyzing different tables at various historical points in time within a single query.

Typical application scenarios

The following are some typical application scenarios for the Time Travel feature.

Scenario 1: Recover data from operational errors

Problem: A developer forgets to add a WHERE clause when executing an UPDATE statement in the production environment, causing the entire table’s data to be updated incorrectly.
Solution: Use the Time Travel feature to query the table’s state before the incorrect operation and restore the correct data, quickly fixing the issue.

Query the state of table t1 from 10 minutes ago and table t2 from 30 minutes ago:

SELECT * FROM t1 TRAVELON (now() - interval '10 min'), t2 TRAVELON (now() - interval '30 min');

Scenario 2: Verify historical report data

Problem: This month’s sales report data differs significantly from last month’s. It is necessary to verify if the source data at that time was accurate.
Solution: Set the state of multiple source tables back to the time when last month’s report was generated. Then, perform a join query to compare and identify the cause of the discrepancy.

Query the state of tables t1 and t2 at different specific date and time:

SELECT * FROM t1 TRAVELON (timestamp '2024-10-11 10:24:00'), t2 TRAVELON (timestamp '2024-10-11 11:24:00');

Scenario 3: Quickly recover an accidentally dropped table

Problem: While cleaning up test tables, a database administrator accidentally drops an important production table, t1.
Solution: Instead of a time-consuming and complex data recovery process, simply execute the UNDROP command to restore the table and its data in seconds.
Restore the dropped table t1:
```
UNDROP TABLE t1;
```

How it works

The core of this feature is based on the database’s multi-version concurrency control (MVCC) mechanism, which ensures that all historical versions of data are retained in the data tables. The system records a precise commit timestamp for each successfully committed transaction. When a user initiates a historical query, the database uses this sequence of timestamps to compute the data snapshot at the target moment, thus presenting the data state at that time.

To ensure the validity of historical queries, the system must control the cleanup of historical data. For DML operations (like UPDATE, DELETE), this means managing the progress of VACUUM to prevent premature reclamation of historical data versions. For DDL operations (like DROP TABLE), it relies on a recycle bin mechanism to enable the recovery of dropped objects.

Core concepts

Before using Time Travel, it is important to understand the following core concepts.

Data retention period

When data in a table is modified (UPDATE, DELETE) or the table itself is dropped (DROP), SynxDB Elastic does not immediately and permanently remove the data. Instead, it retains these historical data versions or the dropped objects for a period of time, known as the “data retention period”.

Feature availability: You can only query historical data or restore dropped objects using Time Travel within the data retention period.
Storage costs: Note that a longer data retention period means more historical data needs to be stored, which will consume additional storage space and increase storage costs. You need to balance your business’s recovery time objective (RTO) and cost budget to set a reasonable retention period.

Recycle bin

For DDL operations like DROP, information about the dropped object (e.g., a table) is stored in a system-level recycle bin (the pg_recycle_bin system table). The UNDROP command locates and restores the object by querying information from this recycle bin. The retention time for objects in the recycle bin is also governed by the data retention period.

Configuration and management

To use the Time Travel feature, you need to perform the following configurations.

Enable transaction timestamp tracking

First, you must ensure that the system’s track_commit_timestamp parameter is enabled. This parameter is used to record the commit timestamp of each transaction and is fundamental to the Time Travel feature. You can set it at the session level or by modifying the configuration file.

-- Enable in the current session
SET track_commit_timestamp = on;

It is recommended to set track_commit_timestamp to on in the database configuration file (postgresql.conf) to ensure the feature is enabled by default for all sessions.

Set the data retention period

The data retention period determines how long historical data versions and dropped objects are kept before being permanently purged. This period is set globally using the GUC parameter time_travel_minutes, with the unit in minutes.

For example, to set the data retention period to 1 hour (60 minutes):

SET time_travel_minutes = 60;

Similarly, you can persist this session-level setting into the configuration file to make it permanent.

Usage: Query historical data

Time Travel allows you to query historical data using the TRAVELON clause in your SELECT statement. You can specify different historical points in time for different tables within the same query.

Syntax

SELECT ...
FROM table_name [ AS alias ] TRAVELON ( timestamp_expression )
[ ... ]

The TRAVELON clause follows the table name and takes a timestamp expression as its argument, such as now() - interval '5 seconds' or timestamp '2024-01-01 10:00:00'.

Query a data snapshot at a precise point in time.

-- Query the state of t1 at a specific time and t2 at another specific time
SELECT * FROM t1 TRAVELON (timestamp '2024-10-11 10:24:00'), t2 TRAVELON (timestamp '2024-10-11 11:24:00');

Query data from a period before the current time.

-- Query the state of t1 from 1 day ago and t2 from 30 minutes ago
SELECT * FROM t1 TRAVELON (now() - interval '1 day'), t2 TRAVELON (now() - interval '30 min');

Usage: Restore dropped objects (Flashback)

The flashback feature allows you to quickly recover objects that have been dropped.

Restore a table (`UNDROP TABLE`)

When a table is dropped, you can restore it within the data retention period using the UNDROP TABLE command. Before restoring, you must first query the system table pg_recycle_bin to find the unique name of the table in the recycle bin.

Syntax:

UNDROP TABLE <new_table_name> AS <recycle_bin_table_name>;

<new_table_name>: The new name for the restored table.
<recycle_bin_table_name>: The name of the table to be restored, as it exists in pg_recycle_bin.

Example:

-- Assume table t1 was accidentally dropped.
DROP TABLE t1;

-- 1. Query the recycle bin for the unique name of the dropped table.
SELECT relname FROM pg_recycle_bin WHERE orig_relname = 't1';

-- Assume the query result is 't1_1678886400'.

-- 2. Use the retrieved name to restore the table and name it t1.
UNDROP TABLE t1 AS "t1_1678886400";

Limitations and considerations

Supported objects: Time Travel and flashback generally support permanent tables.
Unsupported objects: Non-persistent objects such as temporary tables and external tables do not typically support Time Travel.
Data retention period is key: All Time Travel operations are strictly limited by the configured data retention period. Once this period is exceeded, historical data may be permanently deleted.
Impact of VACUUM: The background VACUUM process is responsible for cleaning up expired data versions. The data retention period setting directly affects VACUUM’s behavior.
Storage costs: Enabling Time Travel and setting a long retention period will increase your storage costs. Be sure to monitor and plan accordingly.