@Rocky How performant is starrocks primary key tables at executing delete from table where id in (xxx) statements

StarRocks Primary Key tables are highly performant for

DELETE FROM table WHERE id IN (xxx)

statements because they leverage a specialized storage and indexing mechanism designed for real-time updates and deletions. 1. Performance Mechanism: Index Lookups vs. Scans Unlike other table models (like Duplicate or Unique Key), the Primary Key table uses a Delete+Insert strategy: * Index-Based Lookup: When you execute a

DELETE

using the primary key (e.g.,

WHERE id IN (...)

), StarRocks uses its Primary Key Index to find the exact location (data file and row number) of each record. * No Full Table Scan: If the

WHERE

clause specifies the primary key, StarRocks skips the full table scan. It directly marks these rows as deleted in a "Delete Vector" (DelVector). * Read-Time Efficiency: During queries, the system simply checks the DelVector and ignores the deleted rows. This is significantly faster than the "Merge-on-Read" strategy used in other models. 2. Configuration Limits By default, there is a safety limit on how many items you can put in an

IN

clause for a

DELETE

statement: * Parameter:

max_allowed_in_element_num_of_delete

(FE configuration). * Default Value: 10,000. * If you need to delete more than 10,000 IDs in a single statement, you can increase this parameter, but it is generally recommended to use subqueries or temporary tables for much larger batches. 3. Comparison with Other Deletion Methods Method | Performance on PK Table | Best Use Case

WHERE id = xxx

| Fastest | Deleting single specific records.

WHERE id IN (1, 2, ...)

| Very Fast | Deleting batches up to ~10,000 records.

WHERE id IN (SELECT id FROM...)

| Efficient | Deleting millions of rows based on logic in another table.

TRUNCATE PARTITION

| Instant | Deleting entire time-ranges/partitions (metadata operation). 4. Best Practices * Batching: For very large deletions (millions of rows), it is better to perform them in batches or use a subquery/join delete (

DELETE FROM table USING other_table ...

) rather than one massive

IN

clause with 100k+ literals. * Memory Considerations: The Primary Key index is typically kept in memory (unless

enable_persistent_index

is true). Ensure your BE nodes have enough memory if your table has billions of unique keys. * Avoid Frequent Small Deletes: While performant, sending thousands of individual

DELETE

statements per second can increase compaction pressure. Group them into larger

IN

clauses when possible. Summary: If your

id

is the primary key,

DELETE ... WHERE id IN (...)

is the most efficient way to remove specific rows in StarRocks, often completing in milliseconds for typical batch sizes. References • administration/management/FE_configuration.md • knowledge/usage/best-practice.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • faq/Sql_faq.md