@Rocky if I wanted to copy a big table (3.9tb) in a shared-data arch to a new table, what would be the best way to do that? We once tried to insert into from select * but that blew up pretty quickly

To copy a 3.9TB table in a shared-data (compute-separated) architecture, a single

INSERT INTO ... SELECT *

is almost certain to fail because it creates a massive transaction that exceeds session timeouts (

insert_timeout

), memory limits, and transaction log capacities. Since BACKUP and RESTORE are not supported in shared-data clusters, the best approach is to break the operation down into smaller, manageable chunks. Here are the recommended methods: 1. Partition-by-Partition Copy (Best Practice) If your table is partitioned, copying it one partition at a time is the safest and most efficient method. It keeps transactions small and allows you to resume if a single partition fails. Steps: 1. Create the new empty table with the same schema:

sql
   CREATE TABLE new_table LIKE old_table;

2. Loop through your partitions and run:

sql
   INSERT INTO new_table PARTITION (p202301)
   SELECT * FROM old_table PARTITION (p202301);

Tip: You can script this in Python or Bash to run sequentially or with limited concurrency. ────────── 2. Use

SUBMIT TASK

(Asynchronous) If you must try a single large

INSERT

, do not run it directly from your terminal. Use the

SUBMIT TASK

command to run it as a background process. This avoids client-side timeouts and allows the cluster to manage the job's lifecycle.

sql
SUBMIT /*+set_var(insert_timeout=259200, query_timeout=259200)*/ TASK AS 
INSERT INTO new_table SELECT * FROM old_table;

* Monitoring: Check progress via

SELECT * FROM information_schema.task_runs;

. * Timeout: The hint sets the timeout to 72 hours. ────────── 3. Export and Re-import (Most Robust) For datasets in the multi-terabyte range, using StarRocks' export/import framework is often more stable than SQL-to-SQL copying because it is designed for massive bulk data movement and handles retries better. 1. Export to S3/HDFS: Use

EXPORT

to dump the data into your object storage as Parquet.

sql
    EXPORT TABLE old_table TO "<s3://my-bucket/temp_export/>"
    PROPERTIES ("format" = "parquet");

2. Load via `FILES()`: Once exported, load it into the new table. This uses the internal "Broker Load" engine which is highly parallelized across all CN nodes.

sql
    INSERT INTO new_table
    SELECT * FROM FILES(
        "path" = "<s3://my-bucket/temp_export/*>",
        "format" = "parquet",
        ...
    );

────────── Key Optimizations for Large Copies Before running the copy, set these session variables to maximize performance: *

set insert_timeout = 86400;

: Increases the allowed time for the insert to 24 hours. *

set query_timeout = 86400;

: Prevents the

SELECT

portion from timing out. *

set pipeline_dop = 0;

: (Or set to your CPU core count) to ensure maximum parallelism. *

set load_parallel_instance_num = -1;

: Allows StarRocks to use all available CN nodes for the load. Summary Recommendation: If the table is partitioned, use Method 1. If it is not partitioned, use Method 3 for the highest chance of success on-the-first-try success. References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT.md

@Rocky I already have a nice process I can extend to do ~ 10 million row batches, would that work or could that cause a lot of compaction trouble if writing to multiple prefixes

Using batches of ~10 million rows is a very solid approach for a 3.9TB table. It strikes a balance between keeping transactions manageable and maintaining high throughput. Regarding your concerns about compaction and "multiple prefixes": 1. Compaction & Versioning In StarRocks shared-data (compute-separated) architecture, each

INSERT INTO ... SELECT

creates a new version of the data. * The Risk: If you run thousands of small batches in very quick succession to the same partition, you could accumulate versions faster than the system can compact them. If the "Compaction Score" (number of un-compacted versions) gets too high (typically > 100-200), query performance will degrade. If it hits a hard limit (often 1,000), further writes may fail. * The Solution: Monitor your compaction health. You can check the current compaction score using:

sql
    -- Check Compaction Score (Max_CS) per partition
    SELECT * FROM information_schema.partitions_meta ORDER BY MAX_CS DESC;

If

MAX_CS

stays below 20-30, you are in a healthy range. If it climbs toward 100, slow down your batch frequency. 2. Writing to "Multiple Prefixes" In S3/Object Storage, "prefixes" are used to avoid request rate limits. In StarRocks shared-data architecture, StarRocks automatically manages the prefix structure in your S3 bucket (organizing by DB ID, Table ID, and Partition ID). * Parallelism is Good: Writing to multiple partitions (which map to different prefixes) actually helps compaction. StarRocks schedules compaction tasks at the partition level. By spreading your 10M row batches across different partitions, you allow the cluster to run compaction in parallel across those partitions, preventing any single partition from becoming a bottleneck. * Storage Throttling: If you write too many small files too fast to one prefix, you might hit S3/GCS rate limits. 10M rows per batch is large enough that the resulting files should be substantial (likely hundreds of MBs), which is exactly what object storage prefers. 3. Recommended Settings for Large Batch Moves To ensure your process doesn't "blow up" or fall behind on compaction, consider these optimizations: * Asynchronous Commit: In StarRocks 3.2.3+, enable batch publishing to handle high-concurrency commits more efficiently:

sql
    ADMIN SET FRONTEND CONFIG ("lake_enable_batch_publish_version" = "true");

* CN Compaction Threads: Ensure your Compute Nodes (CNs) have enough threads dedicated to merging these new batches:

sql
    -- Check current config on CNs
    ADMIN SHOW FRONTEND CONFIG LIKE 'compact_threads';
    -- Increase if necessary (usually 25% of CPU cores)

* Insert Overwrite (If applicable): If you are replacing data in the new table rather than appending, use

INSERT OVERWRITE

for specific partitions. It’s more efficient as it creates a brand new version and avoids the overhead of merging with existing data. Summary Your 10M row batch process is safe. To avoid "compaction trouble": 1. Parallelize by Partition: If possible, run batches for different partitions concurrently rather than all for one partition at once. 2. Monitor `MAX_CS`: Keep an eye on

information_schema.partitions_meta

. 3. Check File Sizes: Ensure each 10M row batch is producing files in the 100MB+ range (StarRocks usually handles this via

flush_thread_num_per_store

). References • faq/shared_data_faq.md • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky sorry multiple partitions

Writing to multiple partitions with 10 million row batches is actually more efficient for StarRocks' shared-data (compute-separated) architecture than writing to just one. It helps distribute the I/O and compaction work across more compute resources. Here is how to manage the "compaction" and "prefix" concerns: 1. The "Prefix" Advantage In StarRocks shared-data mode, the system automatically manages the S3/object storage pathing. The structure typically looks like:

<s3://bucket/cluster_id/db_id/table_id/partition_id/tablet_id/>

Because each partition (and even each tablet) has its own path, your writes are naturally distributed across different S3 prefixes. This prevents you from hitting the S3 request rate limits (typically 3,500 PUTs/sec per prefix) that often plague flat-file architectures. Writing to multiple partitions simultaneously is a "win" for storage throughput. 2. Compaction: Versions vs. Partitions The main thing to watch is the version count per partition: * The Rule: Every

INSERT

statement creates 1 new version for every partition it touches. * The Risk: If a single 10M row batch touches 100 different partitions, each of those 100 partitions gets +1 to its "Compaction Score." * The Threshold: You want to keep the "Max Compaction Score" (

MaxCS

) below 100 (ideally below 30). If you write to the same 100 partitions every 30 seconds, you might outpace the compaction engine. Recommendation: If your 10M row batches are spread across different partitions every time (e.g., loading historical data by day), you will have zero compaction issues. If you are constantly hitting the same set of partitions, just ensure your batch interval is at least 30–60 seconds to allow the background threads to keep up. 3. Monitoring for "Trouble" Before you scale up the process, run this query to see your baseline:

sql
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    MAX_CS,      -- Max Compaction Score
    DATA_SIZE, 
    VISIBLE_VERSION 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_new_table'
ORDER BY MAX_CS DESC;

If

MAX_CS

starts climbing toward 100, increase your batch size (e.g., 20M rows) or slow down the frequency. 4. Pro-Tips for 3.9TB Moves * Increase Compaction Parallelism: If you see

MAX_CS

rising, you can allow your CN nodes to work harder on compaction:

sql
    -- On CN nodes (be.conf) or via ADMIN:
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "-1"); -- Auto-scale
    -- Ensure compact_threads is roughly 25% of your CN CPU cores.

* Use

INSERT INTO ... SELECT

with a Partition Filter: Instead of one giant

INSERT

, script it like this:

sql
    INSERT INTO new_table
    SELECT * FROM old_table
    WHERE event_date >= '2023-01-01' AND event_date < '2023-02-01';

This ensures that each "transaction" only touches a specific set of S3 prefixes, making it much easier for the system to commit and compact. Summary: 10M row batches across multiple partitions is the "sweet spot." It avoids the S3 prefix bottleneck and keeps the compaction engine healthy by distributing the work. References • knowledge/admin_and_operation/shared_data_garbage_cleanup.md • knowledge/admin_and_operation/shared_data_compaction.md

When we drop a partition on a primary key table and re-create it, do we need to add the Primary key back to those dropped partitions or do anything to them? We are getting duplicate records that violate our primary key constraints.