Local and Unlimited Database Storage Concepts

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

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.

The blobs for high availability (HA) are kept in the blob cache. The size configured for the maximum_blob_cache_size_mb includes the local storage used for HA. This is done so that on any failover the blob cache is at least partially warmed up and ready to run queries. The hottest 40% of blobs on master partitions are stored on replica partitions in a steady state.

The views mv_bottomless_status_extended, mv_cached_blobs and mv_blob_cache_events can be used to monitor the cache behavior.

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.

• Columnstore data can be committed locally, so you do not have to wait for it to get uploaded, which helps keep the latency as low as possible.

• 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.

• Data is always stored in an object store but may only have a subset in local storage in your cluster. Querying data that is in object store but not in local storage is comparatively slower the first time as it has to bring the data into the local storage.  Hence it is useful to know how much data and what data is in the cache. 

• When you resume a cluster the data does not automatically fault back into the local storage. You will have to run queries to "warm the cache" on a resume.

SELECT SUM(mv_columnstore_files.size)/(1024*1024*1024) AS dataSizeGB
FROM mv_columnstore_files
WHERE mv_columnstore_files.database_name = 'database_name';

SELECT COUNT(*) AS numSegments,SUM(mv_cached_blobs.size)/(1024*1024*1024) AS blobdataSizeGB
FROM mv_cached_blobs
WHERE mv_cached_blobs.database_name = 's2_dataset_tpch'
AND type = 'primary';