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 feature is not available in all editions of SingleStore. For more information, see SingleStore DB Editions.

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.

The cache size is set by the maximum_blob_cache_size_mb variable which is 80% of disk space by default.

Cache item replacement policy is a modified LRU strategy, wherein, one large scan does not flush the whole cache if the scanned data is bigger than the cache.

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. Data is imported into an on-disk rowstore, and then asynchronously exported into unlimited storage on the cloud. This is the key to the high performance as data is ingested into the rowstore, "cold" data is put into long term online storage, keeping the "hot" data on disk where it is faster to access.

  • Data is compressed (lz4) upon insertion into remote storage and decompressed upon retrieval. This seamless process can help reduce network bandwidth and storage costs. The compression ratio is highly data-dependent. For rowstore data, it can be as much as 20x. For columnstores, the data is already compressed, but lz4 can compress it further, often in the range of 20% to up to 2x.

Unlimited storage database support is available in Amazon S3 and Ceph.

Unlimited storage database support is also available in Azure and as a preview feature for Google Cloud Storage (GCS).

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

Notice

You cannot use the REPLICATE DATABASE command to replicate an unlimited storage database. For an alternative, see Replicate an Unlimited Storage Database.