SingleStore DB

Local and Unlimited Database Storage Concepts

You can store data locally in a SingleStore cluster or remotely in an object store. When you create a database, you specify where to store the data.

Local Storage Databases

Local storage databases are stored in your SingleStore cluster. To create a local storage database, use the CREATE DATABASE command.

To backup or restore a local storage database, use the BACKUP DATABASE and RESTORE DATABASE commands, respectively.

Unlimited Storage Databases

Note

This section does not apply to the SingleStore DB Free Edition.

Unlimited storage databases (also known as "bottomless" databases) are kept on remote object storage. The use of remote storage separates where data is stored (in an object store external to the SingleStore cluster) from where the data is processed (in a SingleStore cluster). Because unlimited storage databases are stored remotely, their size is not limited by the size of local cluster storage, but rather only by available external object storage. On public cloud object stores, this is for all practical purposes unlimited.

In an unlimited storage database:

  • All columnstore data is stored externally in an object store. Recently accessed columnstore data objects are also cached locally on the cluster's local storage.

  • All rowstore data is stored locally in cluster memory and externally in an object store.

  • Data updates made on the cluster are flushed to the object store asynchronously. Typically, the object store will be no more than one minute behind the latest update on the cluster.

Currently, unlimited storage databases support the Amazon S3 object store and the Ceph object store.

An unlimited storage database can be created using the CREATE DATABASE command. Prior to creating an unlimited storage database, the enable_bottomlessengine variable must be set to ON (the default value).

Point-in-Time Recovery (PITR)

Note

PITR is currently a beta feature.

Using PITR, you can restore (attach) an unlimited storage database to any point in time, provided all data for the restore point has been flushed to the object store.

Attaching an unlimited storage database can be faster than restoring an equivalent local storage database. This is because an attach of an unlimited storage database does not copy all data to the cluster, as is the case with the restore of a local storage database. Note that after an unlimited storage database is attached, queries may be slower for some time until remote data is cached locally in the cluster.

To enable PITR, set the allow_point_in_time_attachengine variable to ON. Using PITR also requires that the enable_bottomless engine variable is set to ON (the default value).

To work with PITR, use the following commands:

For a basic walkthrough of creating milestones and restoring a database to milestone, see Attach an Unlimited Storage Database Using Point-in-Time Recovery (PITR).