@Rocky Are there any side effects when adding a column using an ALTER statement while the table is being used live?

Adding a column using

ALTER TABLE

in StarRocks is designed to be an online and non-blocking operation. The table remains fully available for both reads and writes while the schema change is in progress. However, there are a few technical "side effects" and behaviors you should be aware of: 1. Asynchronous Execution (Standard Behavior) By default, most column-related

ALTER

operations are asynchronous. • When you execute the command, StarRocks returns a "Query OK" message almost immediately, but the actual schema update happens in the background. • Side Effect: The new column won't be visible to queries or

DESC

commands immediately. You must track the progress using:

sql
  SHOW ALTER TABLE COLUMN;

• Constraint: You can only run one schema change operation at a time on a single table. If you try to add another column while one is still "RUNNING," the second command will fail. 2. Performance & Resource Usage StarRocks uses Fast Schema Evolution (available since v3.2 for shared-nothing and v3.3 for shared-data) to make

ADD COLUMN

a metadata-only operation. • Impact: This is extremely lightweight and consumes very few resources because it avoids rewriting existing data files. • Legacy Behavior: If Fast Schema Evolution is disabled or you are on an older version, StarRocks performs a "Linked Schema Change," which is still relatively fast but involves more tablet-level metadata updates in the background. 3. Fast Schema Evolution v2 (StarRocks v4.1+) If you are using StarRocks v4.1 or later with a cloud-native (shared-data) table: • Schema changes become synchronous. The statement returns only after the change is effective. • It is nearly instantaneous (second-level latency) regardless of the table size because it only modifies Frontend (FE) metadata. 4. Visibility and Metadata •

information_schema

Delay: You might notice a short delay before the new column appears in

information_schema.COLUMNS

. • Default Values: For existing rows, the new column will automatically be populated with the

DEFAULT

value specified in your

ALTER

statement (or

NULL

if no default is provided and the column is nullable). 5. Interaction with Data Ingestion • Concurrent Loads: You can continue to load data into the table via Stream Load, Broker Load, or Routine Load while the column is being added. • New Schema: Once the schema change job is finished, subsequent load jobs must include data for the new column (unless it has a default value). Summary Checklist * Check progress: Use

SHOW ALTER TABLE COLUMN

. * One at a time: Don't try to batch multiple separate

ALTER

statements; wait for each to finish or combine them if the syntax allows (though StarRocks typically requires separate jobs for different types of changes). * Aggregate Tables: If you are adding a value column to an Aggregate Key table, you must specify an aggregate function (e.g.,

SUM

,

REPLACE

). References • faq/operation_maintenance_faq.md • best_practices/query_tuning/schema_tuning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md

@Rocky Is there any issue when backfilling data into a live table that uses the Primary Key table engine?

Backfilling data into a live Primary Key (PK) table in StarRocks is a common operation, but there are several critical considerations to ensure data consistency and system stability. The most common issues and their solutions are: 1. Data Versioning (Overwriting Newer Data) The most significant risk during backfilling is unintentional data overwrites. By default, StarRocks treats every incoming row as an "UPSERT." If you backfill historical data that contains primary keys already present in the table, the backfilled rows will overwrite the existing (newer) rows because the backfill job has a later commit time. * Issue: Historical data (older business logic) overwriting current data (newer business logic). * Solution: Use Conditional Updates (the

merge_condition

property). Specify a version or timestamp column (e.g.,

updated_at

) as the condition. This ensures that the backfill only updates a row if the source data's version is greater than or equal to the existing record.

sql
    -- Example for Stream Load
    curl -H "merge_condition: updated_at" -T data.csv ...

2. Primary Key Index Memory Pressure The Primary Key table requires an index to map each primary key to its physical location. * Issue: If you use the In-Memory Index (

enable_persistent_index = false

), backfilling a large volume of new unique keys can consume significant BE memory, potentially leading to "Primary-key index exceeds the limit" errors or OOM. * Solution: Ensure Persistent Index is enabled (

"enable_persistent_index" = "true"

), which is the default in modern StarRocks versions. This stores the index on disk (SSD recommended) and only caches hot data in memory, allowing you to handle billions of keys without exhausting RAM. 3. Compaction Backlog and Write Throttling Backfilling usually involves large batches of data, which creates many new data versions (rowsets). * Issue: If the backfill is too aggressive, it can cause the compaction score to spike. Since PK tables use a "Delete+Insert" strategy, high compaction scores can lead to: * Increased query latency. _ Ingestion slowdown or "too many versions" errors for your _live* real-time streams. * Solution: * Batching: Avoid many small backfill commits. Use larger batch sizes (e.g., 100MB–1GB per load). * Monitoring: Monitor the compaction status using

SHOW PROC '/transactions/<db_id>/running';

. If you see "Partition's compaction score is larger than 100", slow down the backfill. * Throttling: If necessary, increase compaction threads in

be.conf

(e.g.,

update_compaction_num_threads_per_disk

). 4. Write Amplification Because the PK engine must generate Delete Vectors for every update/delete: * Issue: Backfilling data that updates existing rows is more resource-intensive than inserting brand-new keys. This causes write amplification as StarRocks must mark old rows as deleted and write new ones. * Solution: If you are backfilling an entire partition's worth of data and it doesn't need to be atomic with the live table, consider backfilling into a temporary table first and then using

ALTER TABLE ... SWAP WITH

for a metadata-only swap. 5. Interaction with Partial Updates If your live table uses Partial Updates (e.g., streaming different columns from different sources): * Issue: A backfill might inadvertently overwrite columns it didn't intend to if not configured correctly. * Solution: If the backfill only contains a subset of columns, you must enable partial update mode for that load job (e.g.,

-H "partial_update: true"

). Note that in older versions (pre-3.1), you couldn't use Partial Update and Conditional Update simultaneously. In v3.1+, they can be combined. Summary Checklist for Backfilling: 1. Is

enable_persistent_index

true? (To protect memory) 2. Are you using

merge_condition

? (To protect data freshness)