@Rocky what is the consequence of deleting data periodically through a task, the deletion strategy is not partition wise My scenario

I have bucketd my data based on orderID 
and every order id has a order created date 

and i want to run delete based on booking created date (delete current-last15 th day's data)

Deleting data periodically using a

DELETE

statement based on a non-partition column (like

order_created_date

when you have bucketed by

orderID

but not partitioned by date) has several significant consequences in StarRocks. Because StarRocks is optimized for append-heavy analytical workloads, a standard

DELETE

is a "heavy" operation compared to metadata-level operations like dropping a partition. 1. Query Performance Degradation In StarRocks (specifically for Duplicate, Aggregate, and Unique models), a

DELETE

statement does not immediately erase data. Instead, it creates a Delete Condition (a tombstone) that is attached to the data version. * Read-time Filtering: Every time you query the table, StarRocks must pull these delete conditions and filter out the "deleted" rows on the fly during the read process. * Slowdown: As you accumulate more periodic delete tasks, the number of delete conditions grows, leading to visible latency in your queries until a "Base Compaction" merges the data and physically removes the rows. 2. High Resource Consumption (Compaction) To physically remove the data and reclaim space, the system must perform Compaction. * CPU & I/O Spike: Compaction is an intensive process that reads old data files, removes the deleted rows, and writes new files. Performing non-partition-wise deletes forces the system to re-process many "tablets" (data shards) across the entire cluster. * Version Explosion: Frequent small deletes create many small data versions. If the version count exceeds the system limit (default

max_tablet_version_num

is often 1000), ingestion or queries on that table may fail until compaction catches up. 3. Delayed Disk Space Reclamation Unlike

TRUNCATE

or

DROP PARTITION

(which free up space immediately), a

DELETE

command keeps the data on disk until it is merged. If your deletion volume is high, you may see your disk usage continue to rise even though you are "deleting" data, because the tombstones themselves take up metadata space and the old data hasn't been compacted away yet. 4. Primary Key Model Specifics If you are using the Primary Key model, the impact is slightly different: * Delete Vectors: Primary Key tables use "delete vectors" which are more efficient for individual lookups but still require memory and I/O to manage. * Full Table Scans: If your

WHERE

clause is on a non-primary key column (

order_created_date

), StarRocks must perform a full scan to identify which Primary Keys to mark as deleted, which is resource-intensive. Better Alternatives If you need to delete data older than 15 days periodically, the best practice is to Partition by Date. 1. Partitioning: Change your table schema to be

PARTITION BY RANGE(order_created_date)

. 2. Metadata Deletion: Use

ALTER TABLE ... DROP PARTITION ...

or

TRUNCATE TABLE ... PARTITION ...

. This is a metadata-only operation that takes milliseconds, consumes zero CPU/IO for the deletion itself, and reclaims space immediately. 3. Automatic TTL (Primary Key Tables): If using the Primary Key model, you can set a

storage_ttl

property or use dynamic partitioning to automatically drop old data. 4. Insert Overwrite: For large-scale cleanup where partitioning isn't possible,

INSERT OVERWRITE ... SELECT

(selecting only the data you want to keep) is often more efficient than a massive

DELETE

because it replaces the data in bulk. Summary Recommendation: If your table is already large and you cannot re-partition, try to run your

DELETE

tasks during off-peak hours and avoid running them too frequently (e.g., once a day is better than once every hour) to allow the system time to compact the data versions. References • knowledge/usage/best-practice.md