Historical queries

Vertica can execute historical queries, which execute on a snapshot of the database taken at a specific timestamp or epoch.

Vertica can execute historical queries, which execute on a snapshot of the database taken at a specific timestamp or epoch. Historical queries can be used to evaluate and possibly recover data that was deleted but has not yet been purged.

You specify a historical query by qualifying the SELECT statement with an AT epoch clause, where epoch is one of the following:

EPOCH LATEST: Return data up to but not including the current epoch. The result set includes data from the latest committed DML transaction.
EPOCH integer: Return data up to and including the integer-specified epoch.
TIME 'timestamp': Return data from the timestamp-specified epoch.

Note

These options are ignored if used to query temporary or external tables.

See Epochs for additional information about how Vertica uses epochs.

Historical queries return data only from the specified epoch. Because they do not return the latest data, historical queries hold no locks or blocking write operations.

Query results are private to the transaction and valid only for the length of the transaction. Query execution is the same regardless of the transaction isolation level.

Restrictions

The specified epoch, or epoch of the specified timestamp, cannot be less than the Ancient History Mark epoch.
Vertica does not support running historical queries on temporary tables.

Important

Any changes to a table schema are reflected across all epochs. For example, if you add a column to a table and specify a default value for it, all historical queries on that table display the new column and its default value.