@Rocky Should I try to limit ETL jobs to a single or few partitions?

Yes, it is generally recommended to limit ETL jobs to a single or few partitions per transaction whenever possible. While StarRocks can handle loading into multiple partitions simultaneously, doing so at a high frequency or with too many partitions in a single job can lead to several performance and stability issues. Why you should limit partitions per ETL job: 1. Small File Problem & Compaction Pressure: Every load job creates a new "version" (data file) for every tablet it touches. If an ETL job touches hundreds of partitions, StarRocks must create a new file for every tablet in those partitions. This results in many small files, which increases background compaction pressure and can eventually lead to the "too many versions" error. 2. Memory Consumption: Each partition/tablet being written to requires a memory buffer (controlled by

write_buffer_size

) on the Backend (BE). Writing to many partitions simultaneously increases the memory footprint of the load job. If memory is exceeded, the load may fail or cause frequent flushes to disk, slowing down the process. 3. Transaction Overhead: The Frontend (FE) must manage the metadata for every tablet involved in a transaction. A single load touching thousands of tablets across many partitions puts more stress on the FE's metadata management and can lead to longer commit times or RPC timeouts. Best Practices & Guardrails: * Automatic Partitioning Limit: StarRocks has a safety guardrail for expression/automatic partitioning. The FE parameter

auto_partition_max_creation_number_per_load

defaults to 4096. If your load tries to create more partitions than this in one go, it will fail to prevent accidental "partition explosions." * Historical Backfills: If you are performing a historical backfill (e.g., loading 3 years of daily data), do not do it in one single

INSERT INTO

or

Stream Load

. Instead, batch the load by month or year. * Routine/Stream Load: For real-time streaming, it is common to touch 1-2 partitions (e.g., today's and yesterday's for late-arriving data). This is the ideal pattern for StarRocks. * Write Buffer Tuning: If you must load into many partitions, you may need to monitor the

write_buffer_size

(default 100MB) in

be.conf

. If it's too small, you'll get many small files; if it's too large, you might hit memory limits when many partitions are touched. Summary: For the best performance, try to keep your ETL jobs focused on the "hot" partitions. If you find yourself touching more than a few dozen partitions in a frequent load job, consider re-evaluating your batching strategy! References • faq/loading/Routine_load_faq.md • loading/loading_introduction/loading_considerations.md • best_practices/partitioning.md • loading/Spark-connector-starrocks.md