http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> how to speed up delete from query f...
# questions-and-troubleshooting
c
@Rocky how to speed up delete from query from a big table? can you suggest some speedups? now we experienced 14 mins for deleting 1 year from a fact table
r
Hello! I'm Rocky. Deleting a large volume of data (like a full year) from a big fact table using a standard 
DELETE FROM ... WHERE
statement is slow in StarRocks because it is a "soft delete" operation. It writes "tombstone" markers and relies on background compaction to physically remove the data, which is very CPU and I/O intensive. Here are the most effective ways to speed up this process: 1. Use Partition Management (Fastest) If your fact table is partitioned by time (e.g., by day or month), the fastest way to "delete" a year of data is to drop or truncate the partitions. This is a metadata operation and takes seconds, regardless of data size. * Drop Partitions: (Data is moved to trash; use 
FORCE
to delete immediately)
Copy code
sql
    ALTER TABLE fact_table DROP PARTITION (p202301, p202302, ...);
    -- Or use FORCE to skip the recycle bin and speed up space release:
    ALTER TABLE fact_table DROP PARTITION p202301 FORCE;
* Truncate Partitions: (Keeps the partition structure but clears all data)
Copy code
sql
    TRUNCATE TABLE fact_table PARTITION (p202301, p202302, ...);
2. Use 
INSERT OVERWRITE
 If you need to delete a large amount of data that doesn't align perfectly with partitions, 
INSERT OVERWRITE
is often significantly faster than 
DELETE
. It replaces the data in one atomic operation.
Copy code
sql
-- This effectively "deletes" the year 2023 by overwriting the table 
-- with everything EXCEPT that year.
INSERT OVERWRITE fact_table 
SELECT * FROM fact_table WHERE dt < '2023-01-01' OR dt > '2023-12-31';
Note: You can also use 
INSERT OVERWRITE
on specific partitions to narrow the scope. 3. Optimize "Delete from Query" (Join/Subquery) If your delete relies on a complex condition from another table, use the 
USING
syntax (Multi-table join delete), which is usually more efficient than nested subqueries: * Better (Join Style):
Copy code
sql
    DELETE FROM fact_table
    USING dim_table
    WHERE fact_table.id = dim_table.id AND dim_table.expired = true;
* Avoid (if possible):
Copy code
sql
    DELETE FROM fact_table
    WHERE id IN (SELECT id FROM dim_table WHERE expired = true);
4. Use the Primary Key Model If you are doing frequent deletes and updates, the Primary Key table is specifically designed for this. It uses a "delete-and-insert" strategy with a primary key index in memory, making it much faster than the Duplicate or Unique Key models for these operations. 5. Summary of Quick Tips: * Avoid massive transactions: If you must use 
DELETE
, try batching it (e.g., delete one month at a time) to prevent the transaction from hanging or timing out. * Check `insert_timeout`: If your delete is timing out, you can increase the session variable: 
SET insert_timeout = 3600;
. * Compaction monitoring: After a large delete, the system will be busy with compaction. You can check the status via 
SHOW TABLET STATUS FROM table_name;
. Recommendation: For a 14-minute delete operation involving a year of data, Partition Dropping or INSERT OVERWRITE are your best bets for bringing that time down to seconds or resource usage down to near-zero. References • faq/operation_maintenance_faq.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • knowledge/usage/best-practice.md
c
@Rocky the delete is just like this
Copy code
DELETE FROM fact_table
WHERE utc_created >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
r
The specific query you are running (
DELETE FROM fact_table WHERE utc_created >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
) is slow because it performs a "soft delete" on a massive range of rows. In StarRocks, this generates millions of "delete markers" (tombstones) that the system must store and later merge during background compaction. Since you are deleting a large time range from a fact table, here are the recommended ways to speed this up, ordered from fastest to slowest: 1. The "Metadata" Method (Instant) If your 
fact_table
is partitioned by 
utc_created
(or a similar time column), do not use 
DELETE
. Instead, drop or truncate the partitions. This is a metadata operation and takes less than a second. * To drop the partitions (and free up disk space immediately):
Copy code
sql
    -- First, find which partitions fall into that range
    SHOW PARTITIONS FROM fact_table;

    -- Drop them (use FORCE to skip the recycle bin and speed up space release)
    ALTER TABLE fact_table DROP PARTITION (p2023, p2024) FORCE;
* To clear the data but keep the partition structure:
Copy code
sql
    TRUNCATE TABLE fact_table PARTITION (p2023, p2024);
2. The 
INSERT OVERWRITE
Method (Fast & Clean) If your table is not partitioned by 
utc_created
, or you only want to delete some data within a partition, use 
INSERT OVERWRITE
. This replaces the data in one atomic transaction and is much more efficient than 
DELETE
for large volumes.
Copy code
sql
-- This will overwrite the table with only the data you want to KEEP.
-- (i.e., data older than 1 year)
INSERT OVERWRITE fact_table 
SELECT * FROM fact_table 
WHERE utc_created < DATE_SUB(NOW(), INTERVAL 1 YEAR);
Note: This creates new data files and ignores the old ones, avoiding the "delete marker" overhead entirely. 3. Optimization if you MUST use 
DELETE
 If you cannot use the methods above, try these optimizations for the 
DELETE
statement: * Batching: Instead of one massive 14-minute transaction, delete in smaller chunks (e.g., one month at a time). This prevents the transaction from hitting memory limits or blocking compaction for too long. * Avoid 
NOW()
in the query: Calculate the actual timestamp in your application and pass a fixed string (e.g., 
'2023-01-01 00:00:00'
). While StarRocks supports 
NOW()
, using a literal string allows for better partition pruning and query planning.
Copy code
sql
    DELETE FROM fact_table WHERE utc_created >= '2023-04-29 00:00:00';
* Primary Key Model: If your table uses the Duplicate Key or Unique Key model, 
DELETE
is expensive. If you frequently perform large deletes, consider the Primary Key model, which uses a specialized primary key index to make deletes significantly faster. Summary Recommendation * If Partitioned: Use 
ALTER TABLE ... DROP PARTITION
. * If Not Partitioned: Use 
INSERT OVERWRITE
. * If deleting regularly: Check if your table is a Primary Key table; if not, switching to one will drastically improve delete performance. Check your logs: If the query is timing out, you can increase the timeout for your session: 
SET insert_timeout = 3600;
(This gives it 60 minutes, though with the methods above, you shouldn't need it). References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • knowledge/usage/best-practice.md • table_design/table_types/duplicate_key_table.md
c
@Rocky take note that we are using shared-data architecture
r
In a shared-data architecture (Cloud Native), the performance of a 
DELETE
operation is heavily influenced by the fact that your data resides on object storage (like S3, GCS, or Azure Blob Storage). Because object storage has higher latency for small metadata updates and "tombstone" (delete marker) management compared to local disks, a standard 
DELETE FROM ... WHERE
for a huge range is particularly expensive. Here are the specific speedups and best practices for your architecture: 1. Partition Management (The "Gold Standard") In shared-data clusters, dropping or truncating partitions is a metadata-only operation. It doesn't matter if you have 10 rows or 10 billion; it will complete in seconds. * If you are deleting the most recent year:
Copy code
sql
    -- Find the partitions for the last year
    SHOW PARTITIONS FROM fact_table;

    -- Drop them (this is effectively instant)
    ALTER TABLE fact_table DROP PARTITION (p2023, p2024) FORCE;
Note: 
FORCE
bypasses the recycle bin, reclaiming object storage space faster. 2. Use 
INSERT OVERWRITE
(Recommended for Shared-Data) If your data isn't perfectly partitioned, 
INSERT OVERWRITE
is much faster than 
DELETE
. In shared-data architecture, a 
DELETE
creates "delete markers" that force the system to perform heavy background compaction (reading from S3, merging, and writing back). 
INSERT OVERWRITE
creates clean, new files and ignores the old ones entirely.
Copy code
sql
-- To "delete" the last year, overwrite the table with only the data you want to KEEP
INSERT OVERWRITE fact_table 
SELECT * FROM fact_table 
WHERE utc_created < DATE_SUB(NOW(), INTERVAL 1 YEAR);
3. Shared-Data Compaction Tuning In shared-data architecture, the Frontend (FE) manages compaction tasks and dispatches them to Compute Nodes (CN). If your delete is slow, compaction might be lagging. You can check the "Compaction Score" to see if the system is struggling to keep up: * Check Score: 
SELECT * FROM information_schema.partitions_meta WHERE TABLE_NAME = 'fact_table';
* Tune FE/CN (Admin only): If the 
MaxCS
(Compaction Score) is high, you can increase the concurrency of compaction: * 
admin set frontend config("lake_compaction_max_tasks" = "16");
(Increase FE task dispatching) * On CN nodes, increase 
compact_threads
in the 
cn.conf
(default is usually 4). 4. Primary Key Model Advantage If your table is a Duplicate Key or Unique Key model, deletes are very heavy. In the Primary Key model, StarRocks uses a "delete-and-insert" strategy with a primary key index. In shared-data mode, this is significantly more efficient for large DML operations. If you plan to do this often, consider migrating the table to a Primary Key model. 5. Managing Object Storage Space (Vacuum) Even after a successful delete, you won't see your S3/GCS storage usage drop immediately. Shared-data clusters use an Auto-Vacuum process to physically delete files from object storage. * The default grace period is 24 hours (
lake_autovacuum_grace_period_minutes = 1440
). * If you need space back immediately, you can temporarily lower this setting in the FE config, but be careful as it affects query consistency for long-running queries. Summary of Recommendations: 1. Fastest: Use 
ALTER TABLE ... DROP PARTITION
if the table is partitioned by time. 2. Next Best: Use 
INSERT OVERWRITE
to replace the data set. 3. Optimization: If you must use 
DELETE
, increase the 
insert_timeout
(e.g., 
SET insert_timeout = 3600;
) to prevent the 14-minute job from failing. References • administration/management/compaction.md • faq/shared_data_faq.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md
c
PartitionId PartitionName CompactVersion VisibleVersion NextVersion State PartitionKey Range DistributionKey Buckets DataSize StorageSize RowCount EnableDataCache AsyncWrite AvgCS P50CS MaxCS DataVersion VersionEpoch VersionTxnType MetaSwitchVersion 112566 fact_table 13858 13861 13862 NORMAL id 16 2.6GB 2.6GB 13619449 true false 1.88 2.00 2.00 13259 418190487258660864 TXN_NORMAL 0
@Rocky here are some outputs show partitions PartitionId PartitionName CompactVersion VisibleVersion NextVersion State PartitionKey Range DistributionKey Buckets DataSize StorageSize RowCount EnableDataCache AsyncWrite AvgCS P50CS MaxCS DataVersion VersionEpoch VersionTxnType MetaSwitchVersion 112566 fact_table 13858 13861 13862 NORMAL id 16 2.6GB 2.6GB 13619449 true false 1.88 2.00 2.00 13259 418190487258660864 TXN_NORMAL 0 partitions meta DB_NAME TABLE_NAME PARTITION_NAME PARTITION_ID COMPACT_VERSION VISIBLE_VERSION VISIBLE_VERSION_TIME NEXT_VERSION DATA_VERSION VERSION_EPOCH VERSION_TXN_TYPE PARTITION_KEY PARTITION_VALUE DISTRIBUTION_KEY BUCKETS REPLICATION_NUM STORAGE_MEDIUM COOLDOWN_TIME LAST_CONSISTENCY_CHECK_TIME IS_IN_MEMORY IS_TEMP DATA_SIZE ROW_COUNT ENABLE_DATACACHE AVG_CS P50_CS MAX_CS STORAGE_PATH STORAGE_SIZE TABLET_BALANCED METADATA_SWITCH_VERSION dbfact_table fact_table 112,566 13,858 13,861 2026-04-28 031933.000 13,862 13,259 418,190,487,258,660,864 TXN_NORMAL id 16 1 HDD 9999-12-31 155959.000 [NULL] 0 0 2,859,263,411 13,619,449 1 1.875 2 2 2,859,263,411 1 0
r
Based on your 
SHOW PARTITIONS
output, I have identified the root cause: Your table is unpartitioned (the 
PartitionKey
and 
Range
columns are empty). In StarRocks, especially in shared-data architecture, performing a 
DELETE
on a large, unpartitioned table is a "soft delete" that creates millions of "tombstone" markers. These markers must be stored in object storage and merged during every query until background compaction physically removes them. This is why a relatively small table (2.6GB, 13M rows) is taking 14 minutes. Here are the solutions, ranked by effectiveness: 1. Immediate Workaround: 
INSERT OVERWRITE
(Much Faster) Instead of deleting, you should overwrite the table with the data you want to keep. This creates clean new files and bypasses the delete-marker logic entirely. For a 2.6GB table, this should take 1-2 minutes instead of 14.
Copy code
sql
-- This "deletes" the last year by overwriting the table with 
-- only data older than 1 year.
INSERT OVERWRITE fact_table 
SELECT * FROM fact_table 
WHERE utc_created < DATE_SUB(NOW(), INTERVAL 1 YEAR);
────────── 2. The Long-Term Fix: Implement Partitioning Fact tables in StarRocks should almost always be partitioned by time. If your table were partitioned by 
utc_created
, you could delete a year of data in less than 1 second using a metadata operation. Since you cannot "convert" an existing table to a partitioned one, you should migrate it: 1. Create a new partitioned table:
Copy code
sql
    CREATE TABLE fact_table_new (
        id INT,
        utc_created DATETIME,
        ...
    )
    DUPLICATE KEY(id)
    PARTITION BY RANGE(utc_created) (
        START ("2023-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
    )
    DISTRIBUTED BY HASH(id) BUCKETS 16;
2. Migrate the data:
Copy code
sql
    INSERT INTO fact_table_new SELECT * FROM fact_table;
3. Swap them:
Copy code
sql
    ALTER TABLE fact_table RENAME fact_table_old;
    ALTER TABLE fact_table_new RENAME fact_table;
Now, to delete data in the future, you just run: 
ALTER TABLE fact_table DROP PARTITION p202301 FORCE;
(Instant result). ────────── 3. Architecture-Specific Tuning (Shared-Data) Since you are on shared-data, keep these two things in mind: * Compaction: Your 
MaxCS
(Compaction Score) is currently 
2
, which is very healthy. However, a massive 
DELETE
will cause this score to spike. If you see it go above 10-20, your queries will slow down. * Primary Key Model: If your use case requires frequent deletions of specific rows (rather than big time-ranges), consider using the Primary Key table model. It handles deletes much more efficiently than the Duplicate Key model you are likely using now. Summary of Recommendation: Use Method 1 (
INSERT OVERWRITE
) for your immediate need today, but plan to re-create the table with Partitions (Method 2) to ensure this doesn't happen again as your data grows beyond 2.6GB. References • knowledge/usage/best-practice.md • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md • knowledge/admin_and_operation/shared_data_compaction.md
c
@Rocky now that we know it deleted for 14 mins why I still the data when running select *?
r
Running a 
DELETE
for 14 minutes and still seeing the data in a 
SELECT *
query usually happens for one of three reasons in StarRocks (especially in shared-data architecture): 1. The Delete Operation Failed or Timed Out This is the most likely cause. A 14-minute run time suggests the system was struggling to generate the "delete markers" across all tablets. If the session timed out or hit the 
insert_timeout
limit, the transaction would roll back completely, and no data would be deleted. How to verify: Run this command to check the status of your delete job:
Copy code
sql
SHOW DELETE FROM db_name;
* If the State is 
FINISHED
, the delete was successful. * If it is 
CANCELLED
or not listed, the operation failed. * Check your FE logs or the client output for errors like 
timeout by txn manager
or 
RPC timeout
. 2. Expressions in predicates (The 
NOW()
issue) In many versions of StarRocks (especially for Duplicate/Unique/Aggregate key models), the 
DELETE
statement requires the right side of the predicate to be a literal value, not a dynamic expression or function. While StarRocks has improved support for expressions, using 
DATE_SUB(NOW(), INTERVAL 1 YEAR)
in a 
DELETE
can sometimes cause the operation to either fail at the start or behave non-deterministically. * Fix: Calculate the date first and use a hardcoded string:
Copy code
sql
    -- If today is 2024-04-29, use:
    DELETE FROM fact_table WHERE utc_created >= '2023-04-29 00:00:00';
3. Row Count vs. Data Visibility In the 
SHOW PARTITIONS
output you provided, you see: * 
RowCount: 13619449
* 
VisibleVersion: 13861
In Duplicate Key tables (the default for fact tables), 
DELETE
does not physically remove data immediately. It adds a "delete marker" to the metadata. * The 
RowCount
in metadata will NOT change immediately after a delete. It only decreases after background compaction physically merges the files and removes the rows. _ However, a 
SELECT _
or 
SELECT count(*)
should filter those rows out immediately if the delete was successful. 4. You are in an uncommitted transaction If you (or your IDE) started a transaction block:
Copy code
sql
BEGIN;
DELETE FROM fact_table WHERE ...;
-- 14 minutes pass --
SELECT * FROM fact_table; -- You still see the data here because of READ COMMITTED isolation
You must run 
COMMIT;
for the changes to become visible to other sessions (and your own, depending on the isolation level). Recommendations 1. Check Success: Run 
SHOW DELETE
to see if it actually finished. 2. Use a Literal: Replace 
DATE_SUB(NOW(), ...)
with a fixed timestamp string. 3. Use `INSERT OVERWRITE`: As mentioned before, for a 2.6GB table, 
INSERT OVERWRITE
is much more reliable and often faster than a complex 
DELETE
because it creates a clean new version of the data without relying on markers.
Copy code
sql
    INSERT OVERWRITE fact_table
    SELECT * FROM fact_table
    WHERE utc_created < '2023-04-29 00:00:00';
References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_DELETE.md • faq/Sql_faq.md • table_design/table_types/duplicate_key_table.md References • faq/Sql_faq.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/duplicate_key_table.md
c
@Rocky show delete is empty. im not the one who run the delete
r
The fact that 
SHOW DELETE
is empty while your data remains exactly as it was after a 14-minute run points to one of two specific scenarios in StarRocks: Scenario 1: The Table is a "Primary Key" Model In StarRocks, 
SHOW DELETE
only records history for Duplicate, Unique, and Aggregate key tables. * Behavior: If your table uses the Primary Key engine, 
DELETE
operations are treated as internal transactions (similar to an 
INSERT
). These will never show up in 
SHOW DELETE
. * Why data is still there: If it's a Primary Key table and it ran for 14 minutes without deleting anything, the transaction likely timed out or was killed, causing a full rollback. * How to check: Run 
SHOW CREATE TABLE fact_table;
. If you see 
PRIMARY KEY(...)
, this is why 
SHOW DELETE
is empty. Scenario 2: The Delete Task Failed (Rollback) The 
SHOW DELETE
command only displays successfully completed delete operations. * Behavior: If a 
DELETE
statement runs for 14 minutes and then encounters an error (like an RPC timeout, S3 connection issue, or a 
SET insert_timeout
limit), the entire operation is rolled back to ensure data consistency. * Why it took 14 minutes: In shared-data architecture, a 
DELETE
on an unpartitioned table requires scanning metadata and writing many small delete-marker files to object storage. If this process is too slow, the FE (Frontend) will eventually kill the transaction. * Evidence: Since 
SHOW DELETE
is empty, it confirms that no delete was ever successfully committed to the table metadata. ────────── How to find out what actually happened Since you didn't run the command yourself, you can "forensically" find the failed query: 1. Check the Query History Run this to see the list of recent queries and their status:
Copy code
sql
SHOW PROFILELIST LIMIT 20;
Look for a 
DELETE
statement in the 
Statement
column. * If the 
State
is 
Error
, look at the error message. It will likely say 
Timeout
or 
Cancelled
. * Check the 
Time
column to see if it matches the 14-minute duration. 2. Check the Load/Transaction History (If PK Table) If your table is a Primary Key model, the delete was a transaction. Check the load history:
Copy code
sql
-- Replace 'db_name' with your database name
SHOW LOAD FROM db_name ORDER BY CreateTime DESC LIMIT 10;
Look for entries where the 
URL
or 
Label
indicates a DML/Delete operation. Why did it take 14 minutes? Based on your earlier 
SHOW PARTITIONS
output, your table is unpartitioned and has 13.6 Million rows spread across only 16 buckets. For a 
DELETE
operation: 1. StarRocks must find every row matching your 
WHERE
clause. 2. In shared-data mode, it has to write these "delete markers" back to S3/GCS. 3. If your object storage latency is high or the 
WHERE
clause is complex (e.g., using 
NOW()
), the transaction will hang. Recommended Fix 1. Verify the Table Model: Run 
SHOW CREATE TABLE
. 2. Use `INSERT OVERWRITE`: As suggested before, this is the most reliable way to delete large chunks of data in shared-data architecture because it doesn't rely on the "delete marker" system that likely timed out here. 3. Check Timeouts: If you must use 
DELETE
, tell the person running it to set a higher timeout:
Copy code
sql
    SET insert_timeout = 3600; -- Set to 1 hour
    DELETE FROM fact_table WHERE ...;
References • sql-reference/sql-statements/table_bucket_part_index/SHOW_DELETE.md • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • administration/management/Transaction_atomic.md
c
@Rocky can you please give me the ways to optimize a primary key tables that is in shared-data architecture?
r
Optimizing a Primary Key (PK) table in a shared-data architecture (StarRocks 3.x+) requires a different approach than the standard shared-nothing setup. Since data and metadata are stored in object storage (S3/GCS), minimizing network I/O and managing memory for the primary key index are the most critical factors. Here are the ways to optimize your Primary Key tables: 1. Index Persistence (Crucial for Shared-Data) In shared-data mode, the primary key index (which maps keys to row locations) can consume significant memory. * Persistent Index: Ensure 
enable_persistent_index
is set to 
true
(default). This keeps the index on the BE's local disk (SSD recommended) instead of just in memory. * Cloud-Native Index (v3.3.2+): For extreme scale where local disk is too small, use 
persistent_index_type = CLOUD_NATIVE
. This allows the index itself to be stored in object storage, using the local disk only as a cache.
Copy code
sql
    PROPERTIES (
        "enable_persistent_index" = "true",
        "persistent_index_type" = "CLOUD_NATIVE"
    )
2. Table Design: Partitioning & Bucketing Your previous issue was likely caused by an unpartitioned table. * Range Partitioning: Always partition large fact tables by time (e.g., 
PARTITION BY RANGE(utc_created)
). This allows "Partition Pruning," where StarRocks ignores entire years of data during a query. * Bucket Count: Aim for 100MB to 1GB of compressed data per bucket. * Too many buckets = excessive metadata and small files in S3. * Too few buckets = poor parallelism. * In shared-data, since compute and storage are decoupled, you can generally afford a slightly higher bucket count than in shared-nothing to increase parallelism across your Compute Nodes. 3. Column Mode for Partial Updates If your performance issue involves 
UPDATE
or 
DELETE
on specific columns in wide tables (100+ columns), use Column Mode. * Row Mode (Default): Reads the entire row, updates it, and writes it back. High I/O for wide tables. * Column Mode: Only reads and updates the specific columns involved.
Copy code
sql
    PROPERTIES (
        "partial_update_mode" = "column"
    )
4. Memory & Performance Tuning (BE/FE Config) You can tune how StarRocks handles the "Apply" phase (where updates/deletes are merged): * Concurrency: In 
be.conf
, adjust 
transaction_publish_version_worker_count
. If you have high-core machines, increasing this allows the system to process more PK updates/deletes in parallel. * Memory Limit: 
l0_max_mem_usage
(default 100MB) controls how much memory each tablet uses for the persistent index. If you have many tablets, you may need to lower this to avoid OOM; if you have very large tablets, increase it to reduce disk I/O. 5. Compaction Tuning (Shared-Data Specific) In shared-data, compaction is managed differently. If you see high "Compaction Scores" in 
SHOW PARTITIONS
, it means there are too many "delete markers" or small files. * Slowdown Thresholds: Tune these in 
fe.conf
to prevent the system from grinding to a halt during heavy write/delete loads: * 
lake_ingest_slowdown_threshold
(Default 100): Increase this if you can tolerate slightly slower queries in exchange for higher ingestion throughput. * 
lake_compaction_score_upper_bound
(Default 2000): Increase if you have massive tables and need to allow more versions before stopping writes. 6. Data Cache Optimization Since data lives in S3, the Data Cache is your best friend. * Ensure your Compute Nodes have enough local SSD space for the cache. * Set 
enable_datacache = true
on your table properties. * Monitor your cache hit rate using 
SHOW BACKENDS
. If the hit rate is low, your queries will always be bottlenecked by object storage latency. Summary Checklist 1. Partition by time (always). 2. Enable Persistent Index (use 
CLOUD_NATIVE
for massive datasets). 3. Optimize Buckets (100MB-1GB per bucket).
4. Use Column Mode for partial updates on wide tables. 5. Monitor via 
mem_tracker
 to ensure the PK index isn't starving the rest of the system for RAM. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • deployment/shared_data/feature-support-shared-data.md • best_practices/bucketing.md References • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md
c
@Rocky is it possible to see all the config or params for a table?
r
Yes, there are several ways to see the configurations and parameters of a StarRocks table, ranging from the high-level schema to internal storage details. 1. High-Level: 
SHOW CREATE TABLE
 This is the most common way to see the "DNA" of your table. It displays the exact SQL used to create it, including the column types, key model (Primary, Duplicate, etc.), partitioning/bucketing strategy, and all 
PROPERTIES
.
Copy code
sql
SHOW CREATE TABLE your_table_name;
* What it shows: Keys, 
replication_num
, 
enable_persistent_index
, 
compression
type, and any other specific properties set at creation or via 
ALTER TABLE
. 2. Column Metadata: 
DESCRIBE
(or 
DESC
) If you just need to see column types and which columns are part of the Sort/Primary Key.
Copy code
sql
DESCRIBE your_table_name;
3. System Config View: 
information_schema.tables_config
 For a more structured or "queryable" look at table properties (useful if you want to find all tables with a specific config), you can query this system table.
Copy code
sql
SELECT * FROM information_schema.tables_config 
WHERE TABLE_NAME = 'your_table_name';
* What it shows: 
TABLE_ENGINE
, 
TABLE_MODEL
, 
PARTITION_KEY
, 
DISTRIBUTE_KEY
, and a JSON-like string of all 
PROPERTIES
. 4. Partition & Storage Config: 
SHOW PARTITIONS
 This is the best way to see the "live" configuration of your partitions, such as the actual bucket count, storage medium, and replication status.
Copy code
sql
SHOW PARTITIONS FROM your_table_name;
* What it shows: 
Buckets
, 
ReplicationNum
, 
StorageMedium
, 
DataSize
, and 
VisibleVersion
. 5. Detailed Tablet Info: 
SHOW TABLET
 If you need to go even deeper to see how the data is physically distributed across your backends (BEs).
Copy code
sql
SHOW TABLET FROM your_table_name;
6. Internal FE Explorer: 
SHOW PROC
 StarRocks has an internal process filesystem. You can navigate it like a directory to see very granular metadata.
Copy code
sql
-- First, get the database ID
SHOW PROC '/DBS';

-- Then navigate to the table's partitions (replace {db_id} and {table_id})
SHOW PROC '/DBS/{db_id}/{table_id}/partitions';
Rocky's Pro-Tip: For troubleshooting performance or "where did my data go" issues (like your previous delete question), 
SHOW CREATE TABLE
 and 
SHOW PARTITIONS
 are almost always the first two commands you should run. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_CREATE_TABLE.md • faq/Others.md • table_design/StarRocks_table_design.md • knowledge/admin_and_operation/shared_data_compaction.md
Open in Slack
PreviousNext
Powered by