Tiered Storage

Time to live (TTL) is a mechanism used to limit the lifespan of data. Once TTL is reached, the database system marks the data as expired and deletes it.

In DolphinDB, data retention period can be set for a database partitioned by a DATE or DATEHOUR column. The retention policy is set by specifying the retentionHours parameter of function setRetentionPolicy. DolphinDB compares the machine time with the temporal partitioning column, retaining data within the retention range, and bdeleting data that falls within the interval [current time - retentionHours - 10 days, current time - retentionHours).

In DolphinDB, the tiered storage strategy is also implemented on a database basis. Set the parameter hoursToColdVolume of function setRetentionPolicy to specify the retention time of hot data. DolphinDB compares the machine time with the temporal partitioning column, retaining old data in volumes, and transferring data that falls within the interval [current time - hoursToColdVolumes - 10 days, current time - hoursToColdVolumes) to the cold volumes.

coldVolumes=file:/{your_local_path},s3://{your_bucket_name}/{s3_path_prefix}

Tiered storage manages data by migrating partition on each data node to object storage. The migration process is similar for both automatic and manual triggering, with the main difference being the trigger mechanism.

(1) Initiate Migration Tasks

• Automatic Triggering

  Once the retention policies are set, background workers identify data eligible for migration based on partitions and initiate corresponding migration tasks.

  Migration occurs database by database, with only a portion of a single database's data migrated each hour to reduce system pressure and maximize data availability. For example, for databases dfs://db1 and dfs://db2 partitioned by date range, if hoursToColdVolume is set to 120 (i.e., 5 days), the background worker would complete migrating the partition [2023.02.05, 2023.02.15) of db1 and db2 by the end of 2023.02.20.

• Manual Triggering

  Alternatively, users can force trigger the data migration with a specified time range using the moveHotDataToColdVolume function.

Differences between automatic and manual migrations are:

Note: When migrating a large amount of historical data for the first time, it is recommended to use moveHotDataToColdVolume first and then configure a reasonable policy with function setRetentionPolicy for subsequent automatic data migration.

(2) Execute Migration Tasks

The migration tasks copy corresponding data files to local cold volumes or use the AWS S3 plugin to upload data to S3 paths in multiple threads.

Note:

• If multiple coldVolumes are configured, partitions are randomly migrated to different directories.
• Migration speed to S3 coldVolumes may be slower compared to local paths.
• During migration, the affected partition is temporarily unavailable.

(3) Update Metadata

Once a migartion task is complete, the system modifies the metadata: a) Partition paths are updated. b) Partition permission is changed to READ_ONLY. These partitions can be queried using select statements, but cannot be updated, deleted, or written to using statements like update, delete or append!. Note that executing drop DDL operations (e.g. dropTable, dropPartition, dropDatabase) will remove the data in all storages.

(4) Check Migration Tasks

DolphinDB uses the recovery mechanism to implement data migration, so migration task status can be viewed with getClusterChunksStatus. READ_ONLY permission indicates that the corresponding partition has been migrated.

Supposing coldVolumes is already configured.coldVolumes=file://home/dolphindb/tiered_store/<ALIAS>

(1) Create a VALUE-partitioned DFS database, and write data of the last 15 days:

// Create a database partitioned by date
db = database(directory="dfs://db1", partitionType=VALUE, partitionScheme=(date(now()) - 14)..date(now()))
// Create a table and write data of the last 15 days
data = table((date(now()) - 14)..date(now()) as cdate, 1..15 as val)
tbl = db.createPartitionedTable(data, "table1", `cdate)
tbl.append!(data)

(2) Set the storage policies with setRetentionPolicy:

• Data over 5 days (hoursToColdVolume=120) is transferred to the cold volumes.
• Data over 30 days (retentionHours=720) is deleted.

setRetentionPolicy(dbHandle=db, retentionHours=720, retentionDimension=0, hoursToColdVolume=120)

(3) Force trigger migration with moveHotDataToColdVolume for demonstration:

pnodeRun(moveHotDataToColdVolume)

DolphinDB initiates migration tasks for partitions containing data from the last 15 days to the last 7 days.

(4) Check the execution status of migration tasks:

rpc(getControllerAlias(), getRecoveryTaskStatus)

Sample output (some columns are omitted):

(5) After the migration task ends, the permission of the migrated partitions becomes READ_ONLY.

// Query migrated data
select * from tbl where cdate = date(now()) - 10

// Update is denied
update tbl set val = 0 where cdate = date(now()) - 10
// Error: Writing to chunk {ChunkID} is not allowed.

Check partition status with getClusterChunksStatus:

rpc(getControllerAlias(), getClusterChunksStatus)

Sample output (some columns are omitted):