StarRocks Temporary Partitions: Complete Guide to Viewing & Safe Deletion

Why Temporary Partitions Block DDL Operations

StarRocks temporary partitions are designed as temporary data containers for bulk partition replacement or updates. Their core characteristics create three fundamental conflicts with DDL operations:

Data Consistency Constraints : Temporary partitions share table metadata (column structure, partition keys, bucketing rules) with formal partitions. DDL operations (column changes, partition key modifications) alter table metadata. If temporary partitions exist during DDL, metadata inconsistency arises between temporary and formal partitions, causing data corruption during subsequent replacement or merge operations.

Lock Mechanism Restrictions : StarRocks acquires an exclusive table-level lock for DDL execution. Temporary partition creation, writing, and replacement also require metadata locks. These locks are mutually exclusive; if a temporary partition exists, DDL requests are blocked by the lock and return an immediate "cannot execute" error.

Operation Atomicity Guarantee : The temporary partition lifecycle — create → write → validate → replace formal partition → delete — must remain atomic. DDL operations interrupt this flow, preventing temporary partition data from merging correctly into formal partitions.

Practical Record: DDL Failure Due to Temporary Partition

Background: After a data development team submitted a StarRocks DDL approval, execution failed with an error stating a temporary partition existed, preventing DDL execution. The issue was resolved by locating and deleting the temporary partition.

Core Functions of Temporary Partitions

Data Validation : After syncing data (e.g., incremental data in lakehouse migration), store it first in a temporary partition for verification, avoiding direct impact on formal partition data.

Transitional Storage : During incremental migration, temporarily hold intermediate data awaiting merge.

Risk Isolation : Temporary partitions do not participate in normal queries; even if data is abnormal, business queries remain unaffected.

Note : Temporary partitions only take effect after replacement into formal partitions. After validation, clean them promptly to avoid excessive storage consumption.

How to View Temporary Partitions

1. Precise View: Single Table Temporary Partitions (Most Common)

Use SHOW TEMPORARY PARTITIONS to view details (partition name, data range, status) for a specific table. Syntax is concise and precise:

-- Basic syntax: view temporary partitions of a table in current database
SHOW TEMPORARY PARTITIONS FROM table_name;
-- Example: view temporary partitions of ads_chatops_df
SHOW TEMPORARY PARTITIONS FROM ads_chatops_df;
-- Cross-database scenario: specify database name
SHOW TEMPORARY PARTITIONS FROM {dbname}.ads_chatops_df;

Result Field Interpretation

Execution returns core fields to quickly grasp temporary partition status (illustrated in the article's screenshot).

2. Batch Investigation: All Temporary Partitions in a Database/Multiple Tables

For batch investigation across all tables in a database (e.g., comprehensive cleanup after lakehouse migration), query the StarRocks system metadata table information_schema.partitions_meta to generate organized results:

-- Syntax: query all temporary partitions in specified database
select DB_NAME AS "Database",
TABLE_NAME AS "Table",
IS_TEMP AS "IsTemporaryPartition(1=yes:0=no)"
from information_schema.partitions_meta
WHERE DB_NAME = 'bigdata'  -- replace with target database name
  AND IS_TEMP = '1';  -- filter only temporary partitions

3. Indirect View: Confirm via Table Structure Statement

If a temporary partition is bound to a table, SHOW CREATE TABLE reveals the complete temporary partition configuration in the table structure:

SHOW CREATE TABLE ads_chatops_df;

Safe Operations: How to Delete Temporary Partitions

After data validation, delete temporary partitions promptly to free storage. StarRocks supports single partition deletion, multi-partition batch deletion, and full-database batch cleanup with flexible, controlled operations.

1. Basic Operation: Delete Single/Multiple Temporary Partitions

Use ALTER TABLE ... DROP TEMPORARY PARTITION, supporting single or multiple partitions (comma-separated):

-- 1. Delete single temporary partition (most common)
ALTER TABLE ads_chatops_df DROP TEMPORARY PARTITION tmp_part_20251217;
-- Cross-database scenario
ALTER TABLE bigdata_dev.ads_chatops_df DROP TEMPORARY PARTITION tmp_part_20251217;
-- 2. Batch delete multiple temporary partitions
ALTER TABLE ads_chatops_df 
DROP TEMPORARY PARTITION tmp_part_20251217, tmp_part_20251218, tmp_part_20251219;

2. Operation Verification: Confirm Deletion Success

Verify cleanup results after deletion to avoid failures due to syntax errors or permission issues:

-- Verify single table: no result means deletion succeeded
SHOW TEMPORARY PARTITIONS FROM ads_chatops_df;

Critical Precautions: Avoid Mistakes!

Temporary partition deletion is irreversible; data cannot be recovered once deleted. Key points:

Permission Check : Deletion requires ALTER permission on the table; read-only users cannot execute.

Partition Name Case Sensitivity : StarRocks partition names are case-sensitive by default; must match exactly (e.g., tmp_part_20251217 ≠ Tmp_Part_20251217).

Dependency Check : If a temporary partition is executing REPLACE PARTITION (replacing formal partition), wait for completion before deletion.

Data Confirmation : Before deletion, ensure temporary partition data has been validated and is no longer needed (e.g., lakehouse migration temporary data already synced to formal partition).

Version Compatibility : Only StarRocks 2.0+ supports temporary partitions; versions below 2.0 do not require these operations.

Summary

StarRocks temporary partition management follows the core principle: view first, validate, then delete :

View temporary partitions: single table with SHOW TEMPORARY PARTITIONS, full database via metadata table batch query.

Delete temporary partitions: single/multiple partitions directly with ALTER TABLE ... DROP, full database via metadata table generating batch cleanup statements.

Core principles: confirm data validity before operation, watch permissions and dependencies during operation, verify results after operation to avoid accidental or ineffective cleanup.

Core Conclusion : StarRocks prohibits DDL execution when temporary partitions exist to prevent table metadata inconsistency and guarantee temporary partition operation atomicity — a design-level safety constraint.

Prevention Key : Standardize temporary partition lifecycle; check temporary partition status before DDL to avoid overlapping operations.

In practice, incorporate temporary partition view-validate-delete into data operations workflows to ensure data safety and improve storage resource utilization.