@Rocky When I do a ALTER DATABASE <db_name> SET ("storage_volume" = "<new_storage_volume_name>"); will it move the data? Is the data available while the move is occuring? (readonly ? R/W?

When you execute

ALTER DATABASE <db_name> SET ("storage_volume" = "<new_storage_volume_name>");

in StarRocks, it does not move existing data. Here is how this command behaves: 1. No Data Migration * Existing Tables: Changing the

storage_volume

property at the database level only updates the default setting for that database. Existing tables within the database will continue to use the storage volume they were originally created with. Their data stays exactly where it is. * New Tables: Any new tables created in that database after the command is executed (without explicitly specifying a

storage_volume

in their own

CREATE TABLE

statement) will use the new storage volume by default. 2. Availability (R/W Status) Because no data is being moved, there is no impact on availability: * Read/Write Access: Existing tables remain fully available for both reads and writes. * No Read-Only Period: Since no background migration process is triggered, the database does not enter a read-only state. How to actually move data If your goal is to move existing data to a different storage volume, you cannot do it via an

ALTER DATABASE

or

ALTER TABLE

command. Instead, you must: 1. Manual Migration: Create a new table (which will inherit the new database-level storage volume) and move the data using:

sql
    INSERT INTO new_table SELECT * FROM old_table;

2. Migration Tool: Use the StarRocks Cross-cluster Data Migration Tool, which supports specifying a

target_cluster_storage_volume

during a migration process. Summary Table: Feature | Behavior Moves existing data? | No Affects existing tables? | No Affects new tables? | Yes (updates the default) Data availability? | Normal R/W access (no downtime) For more details on storage volumes, see the CREATE STORAGE VOLUME and ALTER DATABASE documentation. References • sql-reference/sql-statements/cluster-management/storage_volume/ALTER_STORAGE_VOLUME.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/Database/ALTER_DATABASE.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • quick_start/shared-data.md

@Rocky for large DBs, the select * has crashed our CNs on occasions, can we batch the select to limit memory usage?

Yes, for large data migrations, using a single

INSERT INTO ... SELECT *

can indeed exhaust memory on your Compute Nodes (CNs) or Backends (BEs). There are several ways to "batch" this process or reduce its memory footprint. 1. Manual Batching (Recommended) The most reliable way to batch is to split the migration into multiple statements using

WHERE

clauses or by moving data partition by partition. * By Partition: If your table is partitioned, move one partition at a time.

sql
    INSERT INTO target_table
    SELECT * FROM source_table PARTITION (p202301);

* By ID Range: If you have an auto-incrementing or sequential ID.

sql
    INSERT INTO target_table
    SELECT * FROM source_table WHERE id BETWEEN 0 AND 1000000;

2. Enable "Spill to Disk" (v3.0+) If you are on StarRocks v3.0 or later, you can enable the Spill to Disk feature. This allows the system to use disk space when memory limits are reached, preventing the CN from crashing. 1. Configuration: Ensure a spill directory is set in your

cn.conf

(or

be.conf

):

spill_local_storage_dir=/path/to/spill

2. Session Variable: Enable it before running your insert:

sql
    SET enable_spill = true;
    SET spill_mode = 'auto'; -- Triggers spilling when memory is low

3. Adjust Memory Limits You can explicitly limit how much memory the

INSERT

operation is allowed to use. This will cause the query to fail with a "Memory Limit Exceeded" error rather than crashing the whole node, or it will trigger the spilling mentioned above. *

load_mem_limit

: Specifically limits the memory for the "load" part of the insert. *

query_mem_limit

: Limits the memory for the "select" part.

sql
SET load_mem_limit = 8589934592; -- Limit to 8GB
SET query_mem_limit = 8589934592;
INSERT INTO target_table SELECT * FROM source_table;

4. Use Asynchronous Tasks For very large moves, use

SUBMIT TASK

(available since v2.5). This runs the insert in the background, making it more resilient to session timeouts and allowing you to set specific timeouts for the task.

sql
SUBMIT /*+set_var(insert_timeout=28800)*/ TASK AS 
INSERT INTO target_table SELECT * FROM source_table;

You can monitor the progress via:

sql
SELECT * FROM information_schema.task_runs WHERE task_name = '...';

5. Increase Timeout Large inserts often take longer than the default 4-hour timeout. Increase it to avoid "Execute timeout" errors:

sql
SET insert_timeout = 28800; -- 8 hours

Summary of Strategy: For the safest migration, manually batch by partition and enable spilling if your version supports it. This ensures that even if one batch is large, the node remains stable. References • administration/management/resource_management/spill_to_disk.md • loading/InsertInto.md • faq/loading/Insert_into_faq.md • sql-reference/System_variable.md • administration/management/resource_management/Memory_management.md References • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md