@Rocky I have a StarRocks Primary Key table partitioned by a

datetime

column, with a composite PK of

(ordinal, datetime)

where

ordinal

is

AUTO_INCREMENT

. The table stores around 20 columns of user behavioral data. The workload is mostly append-only, but the PK model is required for occasional systemic updates. As the data volume scales from millions to 1B and 10B rows, what is the estimated impact on data ingestion performance and resource cost due to the PK index maintenance and the auto-increment mechanism? At what scale should I expect a significant ingestion bottleneck or performance degradation?

At the scale of 1B to 10B rows, a StarRocks Primary Key (PK) table requires specific configurations to avoid performance "cliffs." Since your workload is mostly append-only with occasional updates, the primary impact will be the growth of the Primary Key Index and the Compaction overhead. 1. Primary Key Index Resource Cost For a composite PK of

(BIGINT, DATETIME)

, your key size is 16 bytes (8 bytes each). * Memory Cost: You must enable the Persistent Index (

enable_persistent_index

=

true

, which is the default since v3.x). * In-Memory (Dangerous at scale): If kept entirely in memory, 10B rows would require ~345 GB of RAM per replica (using the formula

(16+9)_10B_1.5

). * Persistent Index: Only a small portion (controlled by

l0_max_mem_usage

, default 100MB per tablet) is kept in memory. The rest is on disk (SSD). * Disk Cost (Index only): The persistent index occupies disk space. * Formula:

(key_size + 8) _ row_count _ 0.5

(estimated compression). * 1B Rows: ~12 GB per replica. * 10B Rows: ~120 GB per replica. 2. Ingestion Performance Impact While your workload is append-only, StarRocks still performs a PK lookup for every batch to ensure uniqueness. * The "I/O Cliff": As the index grows to 10B rows, it will no longer fit in the OS page cache. If your metadata is on HDD or slow SATA SSDs, ingestion latency will spike because every write requires random I/O to the persistent index on disk. NVMe SSDs are highly recommended at the 10B scale. * Write Amplification: Even for appends, StarRocks must maintain "Delete Vectors." In a purely append-only scenario, this overhead is minimal, but the "occasional systemic updates" will trigger the Delete+Insert mechanism, which is more IO-intensive than the Merge-on-Read approach of other models. 3. AUTO_INCREMENT Scaling The

AUTO_INCREMENT

mechanism is designed to be distributed and does not usually become the bottleneck. * Caching: BE nodes cache IDs in batches (default

100,000

per BE, controlled by

auto_increment_cache_size

). This prevents the FE from becoming a bottleneck during high-concurrency ingestion. * Monotonicity: At 10B rows, note that values will be globally unique but not strictly monotonic (sequential) due to this BE-side caching. 4. When to expect bottlenecks Significant degradation usually occurs at two specific points: 1. Compaction Backlog: If the ingestion rate is high, StarRocks may create too many small versions. * Shared-nothing: You will hit

tablet_max_versions

(default 1000), causing loads to fail. * Shared-data: The

lake_ingest_slowdown_threshold

(default 100) will trigger, artificially slowing down your ingestion to let compaction catch up. 2. Memory Limit for Updates: If many tablets are being written to simultaneously (e.g., very fine-grained partitioning), the cumulative memory used by the

update

tracker might hit the BE

update_memory_limit_percent

(default 60%). Recommendations for 10B Scale * Partitioning: Since you partition by

datetime

, ensure you don't create thousands of empty or tiny partitions. Aim for partitions larger than 10GB. * Monitoring: Track the

update_primary_index_bytes_total

metric and the Compaction Score in

SHOW PROC '/statistic';

. * Batching: For 10B rows, avoid "trickle" loading (small, frequent writes). Use larger batches (100k+ rows per transaction) to reduce the frequency of index updates and compaction pressure. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • table_design/indexes/Bitmap_index.md

@Rocky Our current ingestion pipeline is a real-time stream (individual rows arriving continuously, not in batches). However, we also need to systemically update specific rows every 30 minutes based on filtering criteria. Given that trickle-loading is heavily discouraged for the Primary Key model at a 10B scale, what architecture do you recommend? Should we switch to a

DUPLICATE KEY

model? If so, how should we efficiently handle the 30-minute periodic updates, and what are the exact performance and resource advantages of your recommended approach for this scenario?

For a 10B-row scale with frequent systemic updates, I do not recommend switching to a

DUPLICATE KEY

model. Doing so would solve the ingestion bottleneck but create a much larger problem for your 30-minute updates. Instead, the recommended architecture is to retain the Primary Key (PK) model but change your ingestion pattern from "trickle" to "micro-batch." 1. Why

DUPLICATE KEY

is a trap for your scenario * No Native Updates:

DUPLICATE KEY

tables are append-only. To update "systemic" rows every 30 minutes, you would have to use

INSERT OVERWRITE

, which rewrites entire partitions. At a 10B row scale, rewriting partitions every 30 minutes is extremely resource-intensive and will likely cause significant I/O contention. * Storage Bloat: Without the PK model's automatic deduplication, your table will store every historical version of a row until you manually clean it up, leading to massive storage overhead at 10B rows. * Query Performance:

DUPLICATE KEY

requires you to handle deduplication manually in your SQL (e.g.,

GROUP BY

or

WINDOW

functions), which is significantly slower than the PK model’s native performance (often 3-10x faster for ad-hoc queries). 2. Recommended Architecture: The "Buffered Ingestion" Pattern The bottleneck you are worried about is not the PK model itself, but the IOPS/Commit overhead of individual row writes. At 10B rows, the PK index is large; looking up a single row's location for every incoming record is inefficient. The Fix: Batching at the Ingestion Layer Instead of sending individual rows, you should buffer data for 10–60 seconds (or until you reach ~100k+ rows) and then commit. * Option A: Routine Load (Kafka): This is the easiest solution. StarRocks will consume from Kafka and automatically batch the data based on

max_batch_interval

(seconds) and

max_batch_rows

. * Option B: Flink StarRocks Connector: If you use Flink, the connector buffers data in memory and uses the

Stream Load

interface to send larger batches to StarRocks. * Option C: Shared-Data Batch Commit (v3.2.3+): If you are on StarRocks shared-data architecture, enable

lake_enable_batch_publish_version = true

. This allows the cluster to combine multiple small commits into a single version, significantly reducing the "version explosion" problem of trickle loading. 3. Resource & Performance Advantages of PK + Batching Metric | PK Model + Batching | Duplicate Key Model Ingestion Efficiency | High (with batching) | Very High (append-only) Update Efficiency | Row-level (Optimized) | Partition-level (Poor) Disk Storage | Optimized (only latest rows) | High (stores all duplicates) Query Speed | Fast (3-10x faster) | Slower (needs manual deduplication) Index Cost | Requires Persistent Index (Disk/SSD) | No Index cost 4. Critical Configurations for 10B Scale To ensure this scale works without a bottleneck: 1. Persistent Index: Ensure

enable_persistent_index = true

(default). For shared-data, use

persistent_index_type = "CLOUD_NATIVE"

to store metadata on object storage and avoid local disk limits. 2. SSD/NVMe Storage: At 10B rows, the persistent index lookup requires random I/O. Using NVMe SSDs is the single most effective way to prevent ingestion lag as the table grows. 3. Compaction Tuning: If you still see a "Too many versions" error, increase

cumulative_compaction_num_threads_per_disk

in

be.conf

to let the system merge rowsets faster. Summary: Stick with the Primary Key table. Use Routine Load or Flink to turn your continuous stream into micro-batches of at least 100,000 rows. This preserves your ability to perform efficient systemic updates while keeping ingestion performance stable at the 10B scale. References • table_design/table_types/primary_key_table.md

@Rocky My 10B-row PK table uses a composite key of

(ordinal AUTO_INCREMENT, datetime)

. Because every client event gets a completely new auto-incremented ID, the ingestion is inherently append-only from the user's perspective. I am not using the PK model to prevent or resolve user-side duplicates (deduplication is never triggered during client ingestion). I strictly chose the PK model so my backend system can update specific rows every 30 minutes. However, my ingestion volume is highly volatile (20 to 20,000 rows/min). If I buffer the stream and commit exactly once per minute (regardless of whether the batch has 20 or 20,000 rows), will this 1-minute micro-batching be sufficient to prevent the 'Too many versions' error and I/O cliffs at a 10B scale? Or will committing small batches (e.g., just 20 rows) every minute still cause severe compaction overhead

For a 10B-row Primary Key table, a 1-minute commit interval is a good baseline, but it is not a complete "safety net" if your batch sizes are as small as 20 rows. At your scale, you are essentially balancing the Version Limit and the I/O Cost of Index Maintenance. 1. The "Too Many Versions" Math The default

tablet_max_versions

is 1000. * The Risk: 1 commit per minute = 1,440 versions per day. * The Reality: If your compaction cannot merge these versions faster than 1 per minute, you will hit the 1,000-version limit in less than 17 hours. * The "Small Batch" Trap: Compacting a 20-row rowset takes nearly as much metadata overhead as compacting a 100,000-row rowset. High-frequency small batches force the compaction threads to work constantly on "tiny" merges, often failing to keep up with the overall version growth. 2. Is 20 rows "Too Small"? Yes. For a 10B-row table, committing 20 rows is highly inefficient. Every commit, no matter how small, triggers: 1. Index Lookup: Even though you are appending, StarRocks must verify the PK doesn't exist. At 10B rows, if that part of the index isn't in cache, it's a disk I/O. 2. Metadata Update: Writing a new version to the FE/BE metadata. 3. Compaction Pressure: Creating a tiny "rowset" that must eventually be merged with 10B rows of data. Recommendation: Switch from a "Strict Time" buffer to a "Hybrid" buffer. Buffer data until whichever comes first: * 5 Minutes have passed. * 100,000 Rows have been collected. Even at your lowest volume (20 rows/min), a 5-minute window still only yields 100 rows, which is small but reduces your daily version count from 1,440 to 288—well within the safety margin for compaction. 3. The Advantage of

AUTO_INCREMENT

in this PK Your choice of

(ordinal AUTO_INCREMENT, datetime)

is actually very beneficial for performance: * Index Locality: Since

ordinal

is auto-incrementing, your new keys are always "at the end" of the ID space. This means the Persistent Index lookups will almost always hit the L0 (latest) level of the index, which is much more likely to be in memory or the OS page cache. * Reduced Random I/O: Unlike a UUID or random string, a sequential PK prevents the BE from having to jump all over a 120GB+ index on disk to verify uniqueness for every batch. 4. Critical Settings for 10B Row Stability To prevent the bottlenecks you're worried about, ensure these are configured: *

enable_pk_size_tiered_compaction_strategy = true

: (Default in v3.2.5+). This is vital. It allows StarRocks to merge smaller rowsets together before trying to merge them into the massive 10B-row base data, preventing "Compaction Score" explosion. * Increase Version Limit (If Needed): If you must stay at 1-minute intervals, you can safely increase

tablet_max_versions

to 2000 or 3000 in

be.conf

, provided you have fast NVMe storage to handle the extra files. * Batch Publish (Shared-Data Only): If you are on the StarRocks Shared-Data (Lake) architecture, set

lake_enable_batch_publish_version = true

. This will combine your small 1-minute commits into a single version on the backend, effectively solving the version limit problem. Summary: 1 minute is fine for your 20,000-row peaks, but it is too aggressive for your 20-row troughs. Increase your time buffer to 5 minutes during low-traffic periods to give the compaction engine breathing room at the 10B-row scale. References • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • table_design/table_types/primary_key_table.md

@Rocky Okay, I’m considering a different architecture: instead of loading data directly, I process incremental updates in a 5-minute buffer, apply all required transformations there, and only then load the results into a table that could scale to ~10B rows. Also, I no longer have 30-minute update cycles. With this architecture change, which design would you recommend and why: (1) keep a primary key using an auto-increment original column plus datetime as the partition key (both acting together as the primary key), or (2) switch to a duplicate-key model and avoid using a primary key entirely? Also, how should deletes be handled in duplicate-key tables in this scenario, and what trade-offs should I consider?

Given your move to a 5-minute buffering architecture and the removal of the 30-minute systemic update cycles, your decision boils down to whether you still need row-level mutability within StarRocks or if you can treat the database as a purely append-only store. At the 10B-row scale, here is the recommended direction based on your two options: Recommendation: Stick with the Primary Key (PK) Model Even though you are now processing updates in a 5-minute buffer before loading, I still recommend the Primary Key model over the Duplicate Key model for this scenario. Why? 1. Deduplication Safety: Even with a 5-minute buffer, upstream retries or overlaps can introduce duplicates. The PK model ensures your 10B-row table remains idempotent and clean without manual logic. 2. Query Performance: The PK model uses a "Delete+Insert" strategy. This means it doesn't have to "merge" data during queries like the Unique Key model, and it doesn't require complex

ROW_NUMBER()

or

GROUP BY

logic to find the "latest" row like the Duplicate Key model would if you were simulating updates. 3. Efficient Deletes: Since you asked about deletes, the PK model is objectively superior at scale. It uses Delete Vectors (bitmaps) to mark rows as deleted via the index. A delete in a PK table is a metadata operation + index lookup, whereas a delete in a Duplicate Key table can be a heavy background process. ────────── Analysis of Your Options Option 1: Primary Key

(ordinal, datetime)

* Ingestion: With a 5-minute buffer, you are now committing only ~288 times per day. This completely eliminates the "Too many versions" risk. * Index Cost: At 10B rows, your Persistent Index will take roughly 120GB–150GB of SSD space. This is a fixed cost, but it provides predictable, sub-second lookups for any row by ID. * Deletes: You can run

DELETE FROM table WHERE ordinal = X

and it will be extremely fast because it uses the PK index to find the exact row. Option 2: Duplicate Key Model * Ingestion: The fastest possible ingestion. No index maintenance. * The "Delete" Problem: Duplicate Key tables do not have an index to find specific rows. When you run a

DELETE

statement: * StarRocks creates a delete predicate (a "tombstone"). * Every query on the 10B-row table must now evaluate this predicate against every row to see if it should be filtered out. * Performance Trap: If you accumulate many small

DELETE

commands, query performance will degrade significantly because the system has to check a long list of "tombstones" for every scan. * Handling Deletes (The Workarounds): 1. Soft Deletes: Add a

is_deleted

column. To delete, you

INSERT

a new row with

is_deleted = true

and change your queries to

WHERE is_deleted = false

. At 10B rows, this means your table grows forever and queries get slower. 2. Drop Partition: The only truly efficient delete in a Duplicate Key table is

ALTER TABLE ... DROP PARTITION

. This is instantaneous and recovers disk space immediately, but it only works if you want to delete an entire time range (e.g., "Delete all data from 2023"). Summary Comparison Feature | Primary Key Model (Recommended) | Duplicate Key Model Best For | Fact tables needing row-level deletes/upserts | Raw, immutable logs Delete Performance | High (Index-based Delete Vector) | Low (Tombstone-based filtering) Query Speed | Fastest (Direct scan) | Variable (Filters needed for deletes/dupes) Storage Cost | Lower (Stores only 1 version of a row) | Higher (Stores every version/tombstone) Index Overhead | ~120GB SSD for 10B rows | Zero Final Advice If your "deletes" are infrequent and always target an entire partition (e.g., deleting data older than 90 days), the Duplicate Key model is the most resource-efficient.