On this page
Takes a snapshot of the given database and truncates the transaction log.
SNAPSHOT [DATABASE] <database_name>;
database_is the name of the database to snapshot.
The engine variable, snapshot_
trigger_ size, sets a threshold for the size, in bytes, that the transaction log must reach before the next snapshot is taken.
A user must have either the
BACKUPprivilege to run this command.
SNAPSHOT DATABASEforces a manual snapshot, and can only be run on the master aggregator.
This command causes implicit commits.
See COMMIT for more information.
Refer to the Permission Matrix for the required permission.
This command forces data to the object store if you are using bottomless.
During normal operation, SingleStoreDB logs writes and DDL commands to transaction log files.
When the transaction log reached the threshold set by
After large ingests to columnstore tables, during which new columnstore segments are written or merged during ingest, and old (immutable) segments need to be cleared out.
The snapshot effectively accomplishes this, since they are stored as long as the transaction log references them.
SNAPSHOT DATABASE command allows you to force a snapshot operation on a given database across all the partitions in a cluster.
SNAPSHOT DATABASE is primarily useful to compact the logs to enable faster recovery if the SingleStoreDB service is restarted.
SNAPSHOT on a database:
After changing the schema of a large table using
ALTER TABLEto avoid the cost of replaying the
ALTERoperation if a leaf were ever to restart.
In practice, this situation comes up most often during prototyping and development of your application when schemas are frequently changed.
Prior to performing maintenance operations (such as SingleStoreDB upgrades) that require you to take leaves offline, especially if your workload is highly transactional.
Consider a database where DDL queries being run frequently cancel each other out, such as
CREATE TABLE and
DROP TABLE for the same table.
Note: For workloads with ETL transactions, some users choose to take a snapshot at the end of each transaction in order to keep the cluster ready for a restart.
snapshot_ (sometimes more than once), or enough data is ingested to trigger a snapshot.
Although taking frequent snapshots is beneficial in many cases, users who utilize DR cluster replication should exercise caution when configuring
snapshot_ or triggering manual snapshots with
For more information about replication, see Replicating Data Across Clusters.
SNAPSHOT DATABASE database_name;
Query OK, 0 rows affected (45.36 sec)
Last modified: January 11, 2023