@Rocky is there a way to schedule compaction for previous-day partitions?

While StarRocks does not have a native "compaction scheduler" that targets specific partitions based on date logic (like "yesterday"), you can achieve this by combining StarRocks' manual compaction commands with an external scheduler. 1. Manual Compaction Command Starting from v3.1 (and v3.3 for shared-data clusters), you can manually trigger compaction for a specific partition using the following SQL:

sql
ALTER TABLE <table_name> COMPACT <partition_name>;

You can also specify the type of compaction: * `CUMULATIVE`: Merges several small rowsets into a larger rowset. * `BASE`: Merges all rowsets into a single rowset (more resource-intensive). 2. Scheduling for "Previous Day" Since there is no internal "partition-aware" scheduler, the standard practice is to use an external tool (such as Airflow, Jenkins, or cron) to run a script that: 1. Calculates yesterday's partition name (e.g.,

p20231026

). 2. Executes the

ALTER TABLE ... COMPACT

command via a MySQL client. Example (Shell/Cron):

bash
# Calculate yesterday's partition name (assuming pYYYYMMDD format)
YESTERDAY=$(date -d "yesterday" +%Y%m%d)
PARTITION="p$YESTERDAY"

# Execute compaction via MySQL client
mysql -u<user> -p<password> -h<fe_host> -P9030 -e "ALTER TABLE my_table COMPACT $PARTITION;"

3. Alternative: Control Automatic Compaction If your goal is simply to ensure compaction happens during off-peak hours rather than targeting a specific partition, you can use the

base_compaction_forbidden_time_ranges

property (supported from v3.2.13). This allows you to define a "blackout" period when the system is not allowed to perform base compaction.

sql
ALTER TABLE <table_name> SET ("base_compaction_forbidden_time_ranges" = "* 8-20 * * *"); -- Forbidden 8 AM to 9 PM

Key Considerations * Automatic Compaction: StarRocks usually handles compaction automatically based on "Compaction Scores" (viewable via

SHOW PROC '/dbs/<db>/<table>/partitions'

). Manual compaction is typically only needed if the background process isn't keeping up or if you want to optimize a partition immediately after a large batch load. * Internal Tasks: The StarRocks

CREATE TASK

/

SUBMIT TASK

framework (v3.3+) currently officially supports

INSERT

,

CTAS

, and

CACHE SELECT

, but does not explicitly support

ALTER TABLE ... COMPACT

. External scheduling remains the recommended approach for now the most reliable method for administrative method. References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT.md

@Rocky explain CUMULATIVE vs BASE, what are the benefits of BASE / single-rowset compaction

In StarRocks, compaction is the process of merging multiple versions of data (called Rowsets) into fewer, larger files. This is essential for maintaining query performance and reclaiming storage space. Cumulative vs. Base Compaction Feature | Cumulative Compaction | Base Compaction Target | Merges small, newly loaded "delta" rowsets into larger ones. | Merges cumulative rowsets with the "Base" (the largest/oldest) rowset. Frequency | High frequency; triggered quickly after data is loaded. | Low frequency; typically triggered when cumulative rowsets reach a certain size or age. Scope | Only a subset of rowsets (the "tail" versions). | Usually targets all rowsets in a tablet to create a single version. Cost | Low resource consumption (IO/CPU). | High resource consumption; essentially a full rewrite of the tablet. Goal | Prevents "too many versions" errors and handles small file bloat. | Maximizes query performance and cleans up stale data. ────────── Benefits of BASE / Single-Rowset Compaction "Single-rowset compaction" refers to the state where a tablet's data is consolidated into a single large rowset (Version 0 to X). Achieving this state provides several critical benefits: 1. Reduced Read Amplification When a query runs, StarRocks must read and merge all active rowsets for a tablet. * Without Base Compaction: If you have 50 rowsets, the system must perform a 50-way merge at query time. * With Base Compaction: The system reads 1 rowset. This drastically reduces the I/O overhead and CPU cycles spent on merging data during every query. 2. Enhanced Indexing & Pruning StarRocks uses metadata like Zone Maps (Min/Max values) and Short Key Indexes to skip data. * In many small rowsets, the Min/Max ranges often overlap significantly, making it harder for the engine to "prune" (skip) files. * In a single, large sorted rowset, these indexes are far more effective at pinpointing exactly which data blocks need to be read, leading to sub-second query responses. 3. Space Reclamation & Cleanup For specific table models, Base Compaction is the only way to physically "clean" the data: * Primary Key Model: It physically removes rows that were marked for deletion and collapses multiple updates to the same row into a single final state. * Aggregate/Unique Models: It performs the actual aggregation or value replacement, reducing the total storage footprint. 4. Faster Cold Data Access For shared-data (compute-storage separation) clusters, having fewer, larger files is much more efficient for object storage (S3/OSS). It reduces the number of metadata requests and improves the throughput of data caching. When to use

ALTER TABLE ... BASE COMPACT

? Manual Base Compaction is typically used as a "maintenance" task during off-peak hours for partitions that have finished their heavy ingestion phase (like "yesterday's" data). It ensures that the partition is in its most optimized state for long-term analytical queries. References • faq/Sql_faq.md • faq/operation_maintenance_faq.md • knowledge/usage/best-practice.md • sql-reference/System_variable.md • administration/management/monitoring/alert.md • deployment/environment_configurations.md

@Rocky am I correct in understanding that for DUPLICATE KEY table multiple rowsets do not impede performance same way they do for PRIMARY KEY (with overwritten data) or AGGREGATE table?

You are largely correct. The performance penalty for multiple rowsets is significantly lower for a

DUPLICATE KEY

table than for

AGGREGATE

or

UNIQUE KEY

tables, but it is not zero. Here is the breakdown of why and how they differ: 1.

AGGREGATE

&

UNIQUE KEY

(Highest Impact) These models use a Merge-On-Read strategy. * The Penalty: When you have 100 rowsets, the system must literally "merge" them during the query (comparisons to find the latest version or aggregate values). This is CPU-intensive and prevents the engine from skipping data effectively. * Compaction Benefit: Reducing rowsets here directly removes the need for this expensive "online merge" logic. 2.

PRIMARY KEY

(Medium Impact) This model uses a Delete+Insert strategy with a Delete Vector (

DelVector

). * The Penalty: It does not do a complex merge-on-read like the Unique model, but it must check the

DelVector

to see if a row has been marked as "deleted" by a newer rowset. * Compaction Benefit: Compaction physically removes the "deleted" rows and merges the

DelVector

data, making lookups faster and reclaiming disk space. 3.

DUPLICATE KEY

(Lowest Impact) This is an Append-Only model. Every row is valid; there is no deduplication or aggregation required. * Why you are correct: There is no "merge logic" at query time. The system simply reads all relevant rows from all rowsets. You won't see the massive CPU spikes caused by row-version comparisons that you'd see in the other models. * Why compaction still matters: * I/O Efficiency: Reading 100 small files (rowsets) involves many random I/O operations and metadata overhead. Reading 1 large file allows for high-throughput sequential I/O. * Sort Key Effectiveness: StarRocks uses Sort Keys (specified in

ORDER BY

or

DUPLICATE KEY

clauses) to skip data. Sorting is only guaranteed within a rowset. If your data is spread across 100 rowsets, the "Min/Max" ranges of those files will overlap, making it harder for the engine to prune (skip) files. Compacting them into a single rowset creates a globally sorted file where pruning is extremely efficient. * Indexing: Bloom Filters and Bitmap indexes are also rowset-local. Fewer rowsets mean fewer index structures to load and check. Summary Table Table Model | Primary Rowset Penalty | Compaction Priority Aggregate / Unique | Merge Overhead: Extreme CPU/latency hit. | Critical Primary Key | Lookup Overhead: Checking delete vectors. | High Duplicate Key | I/O & Pruning: Small files and overlapping ranges. | Medium Conclusion: For a

DUPLICATE KEY

table, you don't need to be as aggressive with manual compaction as you would be with an

AGGREGATE

table, but you should still aim for a "single-rowset" state for large historical partitions to maximize scan speed and storage efficiency. References • table_design/table_types/duplicate_key_table.md • table_design/table_types/primary_key_table.md • using_starrocks/caching/query_cache.md

@Rocky if I run ALTER TABLE COMPACT on just compacted table - will it ‘know’ that tablets are already fully compacted quickly or will it go through the process of reading it?

The short answer is yes, it will "know" and finish almost instantly. When you trigger

ALTER TABLE ... COMPACT

, StarRocks performs a metadata check before starting any actual data processing. If the partition/tablet is already in an optimal state, the task will skip the heavy lifting. How it works: 1. Rowset Count Check: The system looks at the number of rowsets (data versions) for each tablet. If a tablet already consists of a single rowset (or is below the minimum compaction threshold), the BE (or CN) will mark the task for that tablet as SKIPPED. 2. Compaction Score: StarRocks uses a "Compaction Score" to evaluate if work is needed. If the score is already at its minimum (typically 0 or 1 for a single rowset), the system won't waste I/O or CPU resources re-reading and re-writing the same data. 3. Metadata-Only Task: In this scenario, the command will appear to "run," but if you check the compaction status, you will see that no actual data was processed. How to verify: You can see exactly what happened by querying the system metadata tables. For Shared-Nothing (Standard BEs):

sql
SELECT * FROM information_schema.be_compactions 
WHERE TABLE_NAME = 'your_table_name' 
ORDER BY START_TIME DESC;

For Shared-Data (Compute-Storage Separation):

sql
SELECT * FROM information_schema.be_cloud_native_compactions 
ORDER BY START_TIME DESC;

*Look for the

SKIPPED

column. If it shows

1

(or true), it means the system identified that no work was necessary and did not read the data.* Summary You don't need to worry about "double-compacting" or wasting resources. If you run the command on an already optimized partition, StarRocks will recognize the state at the metadata level and exit the task without performing any disk I/O. References • administration/management/compaction.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/monitoring/alert.md • deployment/environment_configurations.md • faq/Sql_faq.md • faq/operation_maintenance_faq.md • knowledge/usage/best-practice.md • sql-reference/System_variable.md References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md