< Rocky> I d like to setup a regular backup of my starrocks StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> I'd like to setup a regular backup ...

Guillaume Bienkowski

05/22/2026, 1:28 PM

@Rocky I'd like to setup a regular backup of my starrocks clusters. I have a few of them, in Shared-nothing deployment, in a K8S cluster (so with data disks attached). From the documentation, I can see that I won't be able to use "snapshots" since they are shared-data only. What would be the best way to perform regular backups for the cluster?

Rocky

05/22/2026, 1:28 PM

For a Shared-nothing deployment (even on Kubernetes), the standard and most reliable way to perform regular backups is by using the built-in BACKUP
and RESTORE
SQL commands. Contrary to your finding, these commands are specifically designed for Shared-nothing clusters. The "Cluster Snapshot" feature you saw in the docs is a newer, separate mechanism designed specifically for the Shared-data architecture (v3.5+). Here is the best practice for setting up regular backups in your environment: 1. Create a Remote Repository First, you must define a remote storage location (S3, MinIO, GCS, or HDFS) where StarRocks will upload the data snapshots. Since you are in K8S, using an S3-compatible object store (like MinIO or AWS S3) is recommended.

Copy code

sql
CREATE REPOSITORY `s3_backup_repo`
WITH BROKER 
ON LOCATION "<s3a://my-backup-bucket/starrocks_backups>"
PROPERTIES (
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "us-east-1",
    "aws.s3.endpoint" = "<https://s3.amazonaws.com>"
);

Note: Use
s3a://
protocol for S3/MinIO/GCS. 2. Perform the Backup You can back up an entire database or specific tables. The

BACKUP

command is asynchronous.

Copy code

sql
-- Backup an entire database
BACKUP DATABASE my_db 
SNAPSHOT snapshot_20231027 
TO s3_backup_repo;

-- Or backup specific tables
BACKUP DATABASE my_db 
SNAPSHOT snapshot_tables_20231027 
TO s3_backup_repo 
ON (table1, table2);

3. Automate via Kubernetes CronJob Since StarRocks does not have a built-in backup scheduler, you should use a Kubernetes CronJob. This job runs a container with a MySQL client that connects to your StarRocks FE and executes the backup command. Example CronJob Logic: 1. Generate a unique snapshot name (e.g.,

backup_$(date +%Y%m%d)

). 2. Connect to the FE and run the

BACKUP

command. 3. (Optional) Run a script to check

SHOW BACKUP

until the state is

FINISHED

. Simple CronJob Snippet:

Copy code

yaml
apiVersion: batch/v1
kind: CronJob
metadata:
  name: starrocks-backup
spec:
  schedule: "0 2 * * *" # Every day at 2 AM
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup-client
            image: mysql:8.0
            command:
            - /bin/sh
            - -c
            - |
              SNAPSHOT_NAME="backup_$(date +%Y%m%d)"
              mysql -h starrocks-fe -P 9030 -u root -e "BACKUP DATABASE my_db SNAPSHOT $SNAPSHOT_NAME TO s3_backup_repo;"
          restartPolicy: OnFailure

4. Key Considerations * Consistency: The

BACKUP

command provides an application-level consistent snapshot across all BE nodes. Avoid using disk-level (EBS/PVC) snapshots, as they are difficult to coordinate across a distributed cluster and may lead to data corruption upon restore. * Incremental Backups: StarRocks native

BACKUP

is a full backup. If your tables are partitioned by time, you can simulate incremental backups by only backing up the latest partitions (e.g.,

ON (table1 PARTITION (p20231027))

). * Privileges: Ensure the user running the backup has the

REPOSITORY

and

EXPORT

privileges. * Monitoring: Use

SHOW BACKUP FROM my_db;

to check the progress and status of your jobs. For more complex requirements (like automated rotation or metadata tracking), there is a community-supported tool called

starrocks-backup-and-restore

available on GitHub/PyPI that wraps these SQL commands into a CLI. References • administration/management/Backup_and_restore.md • quick_start/helm.md

Guillaume Bienkowski

05/22/2026, 1:29 PM

Thanks @Rocky, do you know if Azure Blobstore is supported for these REPOSITORIES?

Rocky

05/22/2026, 1:30 PM

Yes, Azure Blob Storage is supported for StarRocks

BACKUP

and

RESTORE

repositories (from version 3.0 onwards). While the primary

CREATE REPOSITORY

documentation often emphasizes S3 and HDFS, the Azure integration is fully supported. For Shared-nothing clusters, StarRocks typically uses the Hadoop-compatible protocols (

wasbs://

for Blob Storage or

abfss://

for ADLS Gen2) to communicate with Azure. Example: Create a Repository on Azure Blob Storage You can create the repository using the following syntax. Note that the

WITH BROKER

keyword is required by the syntax for cloud storage repositories, even if you are not using a separate StarRocks Broker process.

Copy code

sql
CREATE REPOSITORY `azure_backup_repo`
WITH BROKER 
ON LOCATION "wasbs://<container_name>@<storage_account_name>.<http://blob.core.windows.net/backups|blob.core.windows.net/backups>"
PROPERTIES (
    "azure.blob.storage_account" = "your_storage_account_name",
    "azure.blob.shared_key" = "your_storage_account_shared_key",
    "azure.blob.endpoint" = "<https://your_storage_account_name.blob.core.windows.net>"
);

Key Parameters for Azure * Protocol: Use

wasbs://

for Azure Blob Storage (HTTPS) or

abfss://

for Azure Data Lake Storage Gen2 (HTTPS). * Authentication: * For Shared Key: Use

azure.blob.storage_account

and

azure.blob.shared_key

. * For SAS Token: Use

azure.blob.storage_account

azure.blob.container

, and

azure.blob.sas_token

. * Endpoint: The

azure.blob.endpoint

is generally required to point the request to the correct Azure region/account (e.g.,

https://<account>.<http://blob.core.windows.net|blob.core.windows.net>

). Important Notes for K8S Deployments * Network Access: Ensure your Kubernetes BE (Backend) nodes have outbound network access to the Azure Storage endpoint. * Hierarchical Namespace: If you are using ADLS Gen2, the StarRocks documentation notes that for certain backup/restore operations, the hierarchical namespace should ideally be disabled on the storage account for best compatibility with the

wasb

driver, though

abfs

is supported for newer versions. * Check Status: After creating the repository, you can verify it exists with

SHOW REPOSITORIES;

. If you encounter errors during the first backup, check the BE log (

<http://be.INFO|be.INFO>

) for detailed credential or connection errors from the underlying Hadoop Azure connector. For the actual backup automation, you would still use the Kubernetes CronJob approach mentioned previously, simply referencing this Azure-backed repository. References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • data_source/catalog/unified_catalog.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md

Rocky

05/22/2026, 2:20 PM

In StarRocks, using a partial update (specifying every column in the header) is more expensive than a standard full row UPSERT (Insert/Stream Load without the partial update flag). If you have the data for all columns, you should always use a standard load rather than forcing a partial update. Here is why: 1. Internal Mechanism Difference * Standard UPSERT (Full Row): StarRocks Primary Key tables use a delete-and-insert mechanism. When you perform a standard load, StarRocks uses the Primary Key index to find the old row's location, marks it as deleted, and writes the new row data. This is a write-optimized path. * Partial Update (Row Mode): When you set

partial_update=true

, StarRocks enters a read-on-write flow. Even if you provide all columns in your CSV/JSON, the engine’s internal state machine is configured to "merge" data. It must verify the existence of the row and potentially perform additional checks or row reconstructions that the standard insert path skips. 2. Performance Overhead Specifying all columns in a partial update header introduces unnecessary costs: * CPU/Memory: The partial update logic invokes a specialized merge process in the Backend (BE). Forcing this logic for a full row adds computation cycles that the native "full insert" path doesn't require. * Optimization Bypass: StarRocks has highly optimized paths for standard loading (like vectorized writes). Partial updates, especially in older versions or specific configurations, may not hit the same level of optimization as a standard full-row write. 3. Summary of Best Practices Scenario | Recommended Method | Performance Note You have data for every column | Standard

STREAM LOAD

INSERT

| Fastest. Uses the native PK delete-and-insert path. You only have 1-2 columns out of many |

STREAM LOAD

with

partial_update=true

| Efficient because it avoids sending/processing massive amounts of unchanged data. Batch updating 10% of columns across 1M+ rows |

partial_update_mode=column

| Best for mass updates. It only writes to the specific column files being changed. The Verdict: If you have the data for the whole row, do not use the

partial_update

flag. It adds "under the hood" overhead for a merge operation that isn't actually necessary. References • faq/Sql_faq.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Load_to_Primary_Key_tables.md • faq/Others.md • knowledge/usage/best-practice.md • loading/Spark-connector-starrocks.md

Rocky

05/22/2026, 2:40 PM

Yes, StarRocks does default omitted columns to their specified default values, but the exact behavior depends on whether you are doing a standard load or a "Partial Update" on a Primary Key table. 1. Standard
INSERT
(SQL) When using the standard

INSERT INTO

statement, if you omit a column from the column list, StarRocks will automatically use the

DEFAULT

value defined in the schema.

Copy code

sql
-- Schema: age INT DEFAULT '18'
INSERT INTO users (name) VALUES ('Rocky'); 
-- Result: name='Rocky', age=18

2. Standard
STREAM LOAD
/
ROUTINE LOAD
For standard loading (where you are replacing or inserting entire rows): * Columns omitted from the
COLUMNS
header: If you don't list a column in the

columns

property of your load command, StarRocks fills that column with its default value defined in the

CREATE TABLE

statement. * Missing fields in data: If a field is present in your column mapping but missing in the actual data row (e.g., a CSV line has fewer columns than specified), StarRocks will attempt to fill it with

NULL

. If the column is

NOT NULL

and has a

DEFAULT

, it will use the default. 3. Primary Key Tables (The "Upsert" exception) If you are using a Primary Key table, the behavior depends on whether the row already exists: * If the row is NEW (Insert): Omitted columns will be populated with their default values (e.g.,

NULL

, or your custom string). * If the row EXISTS (Update): * Standard Load: If you perform a standard stream load (no partial update flag), the omitted columns will be overwritten with their default values. * Partial Update (
partial_update: true
): Omitted columns will retain their existing values in the database. They are not reset to defaults. 4. Special Case:
REPLACE_IF_NOT_NULL
(Aggregate Tables) In Aggregate Key tables, you can define a column with the

REPLACE_IF_NOT_NULL

aggregation type. * If the incoming data for that column is

NULL

(or omitted during certain load types), StarRocks will keep the original value already stored in the cluster instead of overwriting it with

NULL

or a default. Summary Table Operation Type | Omitted Column Behavior SQL
INSERT
| Uses

DEFAULT

value. Standard Stream Load | Uses

DEFAULT

value (overwrites existing data if PK matches). PK Partial Update | Retains existing value (does not use default). Auto-increment | Generates a new ID if omitted. Important Note: If a column is defined as

NOT NULL

and has no

DEFAULT

value, omitting it during a load will cause the row to be filtered out as a "quality error"quality fail (unless it is a Partial Update on-conflict update scenario). References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/loading_unloading/INSERT.md • loading/InsertInto.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md

Rocky

05/22/2026, 2:54 PM

Yes, within a single Stream Load transaction, StarRocks follows a "last row wins" behavior for rows with the same Primary Key. If your data source (e.g., a CSV or JSON file) contains multiple objects with the same Primary Key, they are processed in the order they appear in the file. The final state of the row in the database will match the last occurrence of that key in the load batch. Key Behaviors to Note: 1. Sequential Processing: Inside a single load, StarRocks applies updates sequentially. If your file has: * Row 1:

id=101, status='A'

* Row 100:

id=101, status='B'

* Result: The final value in the table will be

status='B'

. 2. Partial Updates: If you are using

partial_update: true

and have multiple rows for the same PK in one load: * The rows will be merged one after another onto the base row. * This is useful for "accumulating" updates in a single batch, where each subsequent row in the file updates specific fields of the same PK. 3. Handling "Out-of-Order" Data (Conditional Update): If you cannot guarantee that the "last" row in your file is the "newest" (e.g., due to upstream system offsets), you should use the Conditional Update feature. By specifying a version or timestamp column (e.g.,

-H "merge_condition: version_col"

), StarRocks will only apply an update if the incoming row's version is greater than or equal to the version currently stored. 4. Performance Tip: While "last row wins" works, having a very large number of duplicate keys within a single load batch can slightly increase processing overhead (as the index is updated multiple times for that key). If possible, deduplicating your data before the Stream Load is a best practice, though not required for correctness. Summary: For a single stream load, the order of rows in the input data determines the final state, effectively making the last write the "write" (the last row in the file) the winner. References • loading/Load_to_Primary_Key_tables.md • faq/Sql_faq.md • loading/StreamLoad.md • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Flink-connector-starrocks.md • loading/Loading_intro.md • administration/Query_management.md • sql-reference/sql-statements/loading_unloading/INSERT.md • unloading/Export_queries_to_files.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • loading/InsertInto.md • sql-reference/sql-statements/data-manipulation/DELETE.md • loading/RoutineLoad.md • loading/Load_to_Aggregate_tables.md • introduction/StarRocks_intro.md • loading/Spark-connector-starrocks.md • loading/ETL_in_StarRocks.md • loading/HDFS_load.md

Rocky

05/22/2026, 2:54 PM

• sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • sql-reference/sql-statements/data-definition/CREATE_VIEW.md • sql-reference/sql-statements/loading_unloading/SUBMIT_TASK.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/sql-statements/loading_unloading/EXPORT.md • administration/Management_commands.md • administration/configuration/FE_configuration_items.md • loading/SparkLoad.md • using_starrocks/Primary_key_table.md • administration/Replica_Management.md • administration/Cluster_administration.md • loading/Data_compression_for_loading.md • using_starrocks/Async_MV.md • table_design/Sort_key.md • loading/Loading_Troubleshooting.md • faq/Environment_FAQ.md • using_starrocks/query_acceleration_with_auto_refresh_mv/Sync_MV.md • table_design/data_types/Array.md • loading/Stream_Load_transaction_interface.md • loading/BrokerLoad.md • unloading/Unload_to_S3.md • sql-reference/sql-statements/loading_unloading/PAUSE_ROUTINE_LOAD.md • sql-reference/sql-statements/data-definition/TRUNCATE_TABLE.md • loading/Json_loading.md • unloading/Unload_to_HDFS.md • administration/Resource_group.md • sql-reference/sql-statements/loading_unloading/CANCEL_LOAD.md • sql-reference/sql-statements/loading_unloading/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/account-management/SET_PASSWORD.md • sql-reference/sql-statements/backup_restore/RESTORE.md • loading/Mysql_load.md

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

Rocky

05/22/2026, 2:54 PM

For Primary Key (PK) and Unique Key tables in StarRocks, the behavior for multiple records with the same primary key within a single Stream Load is "Last Write Wins" based on the order of the records in your source file or stream. 1. Behavior by Table Type * Primary Key / Unique Key Tables: StarRocks processes the data sequentially as it appears in the stream or file. If two records in the same batch have the same Primary Key, the one that appears later in the file will effectively "overwrite" the previous one. Only the most recently loaded record for that key is retained. * Duplicate Key Tables: Every record is kept. If you have three objects with the same PK in one load, you will end up with three separate rows in the table. * Aggregate Tables: The rows are merged according to their aggregation functions (e.g.,

SUM

MAX

, or

REPLACE

). If the column is defined as

REPLACE

, it behaves like a Unique Key table (last record wins). 2. Controlling Order (Conditional Updates) If your data might arrive out of order within a stream or across multiple loads, you shouldn't rely solely on the "last write" position. StarRocks provides a Conditional Update feature (for Primary Key tables) to handle this: * `merge_condition`: You can specify a column (like a

version

updated_at

timestamp) as the condition. * Effect: Even if a "later" record appears in the stream, StarRocks will only update the row if the new record's version is greater than or equal to the version currently in the database (or the one previously processed in the same batch). 3. Summary of Intra-Batch Processing * No `merge_condition`: The engine processes rows in the physical order of the input file. The physically last record for a given PK in that batch is what will be committed. * With `merge_condition`: The engine compares the specified column values. The record with the "highest" value (based on your condition) for that PK in the batch wins, regardless of its position in the file. Recommendation: If you are streaming data where duplicates can occur and order isn't guaranteed (e.g., from Kafka), always use a

merge_condition

with a sequence number or timestamp to ensure data consistency. References • table_design/table_types/table_types.md • loading/Load_to_Primary_Key_tables.md • loading/Flink-connector-starrocks.md • table_design/table_types/primary_key_table.md • table_design/table_types/unique_key_table.md • table_design/table_types/duplicate_key_table.md

Rocky

05/22/2026, 3:38 PM

Using a

merge_condition

(Conditional Update) in StarRocks does introduce a performance overhead, but it is likely not the overhead you expect. Contrary to the assumption that StarRocks must "fetch the row from disk" to compare values, StarRocks is designed to handle this check extremely efficiently in memory for Primary Key tables. 1. The Internal Mechanism: Memory over Disk For Primary Key tables, StarRocks maintains a Primary Key Index. * Normal PK Index: Usually stores a mapping of

Hash(PK) -> {RowLocation}

. * With Merge Condition: If you specify a sequence column (via

merge_condition

or the

SEQUENCE COLUMN

table property), StarRocks expands the Primary Key Index to store

Hash(PK) -> {RowLocation, VersionValue}

. Because the Version Value is stored directly inside the in-memory index, StarRocks can perform the comparison ("Is the incoming version $\ge$ the existing version?") without reading the data row from disk. This prevents the "read-before-write" I/O bottleneck that would otherwise kill ingestion performance. 2. Where the Actual "Overhead" Occurs The performance cost of using a

merge_condition

is primarily found in three areas: 1. Memory Consumption (Most Significant): * Since the version value (e.g., a 8-byte

BIGINT

timestamp or

DATETIME

) is added to every entry in the Primary Key Index, the memory footprint of your index will grow. * If you have billions of rows, adding an extra 8 bytes per row to the index can result in several additional gigabytes of RAM usage on your Backend (BE) nodes. 2. Index Persistence (I/O): * If you use the

Persistent Index

feature (storing the PK index on disk/SSD), the index files will be larger, leading to slightly more I/O when the index is loaded or flushed. 3. CPU Cycles: * The BE must perform a comparison for every row in the stream. While very fast (nanoseconds), it is technically more "expensive" than a blind overwrite where the engine just marks the old row as deleted and writes the new one. 3. Comparison: Merge Condition vs. Partial Update It is important to distinguish the overhead of a

merge_condition

from the overhead of a Partial Update: * `merge_condition`: Generally cheap (Memory-based comparison). It just decides if a write should happen. * Partial Update: More expensive (Read-on-Write). It requires the engine to reconstruct the full row. If you are doing a partial update and a merge condition, the engine first checks the index (cheap) and then, if the condition passes, performs the row reconstruction (expensive). Summary Recommendation * If you have a version/timestamp: Always use it. The memory overhead is a fair trade-off for ensuring data consistency and avoiding out-of-order data issues. * If you don't need versioning: Don't specify a

merge_condition

. This keeps your Primary Key Index as lean as possible, maximizing the number of rows you can fit in memory. References • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md • best_practices/primarykey_table.md • faq/Sql_faq.md References • faq/Sql_faq.md • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • best_practices/table_clustering.md

Open in Slack

Previous Next