@Rocky I am trying to run a pipe for a month of data that mostly already exists in a pk table and the process gets jammed up and i think it's due to many load jobs on the same table, is there any way I can get ahead of this?

Running a monthly backfill into a Primary Key (PK) table can often lead to performance bottlenecks (or "jamming") because PK tables are particularly sensitive to high-frequency, small-batch write operations. When you load a month's worth of data through a Pipe, StarRocks might be creating hundreds or thousands of individual transactions. Each transaction on a PK table requires: 1. Index Updates: Loading the PK index into memory (or checking the persistent index). 2. Delete+Insert Logic: Identifying which existing rows need to be marked as deleted in the

DelVector

. 3. Compaction Pressure: Each commit creates a new rowset version. If versions accumulate faster than the BE can compact them, you hit a "Too many versions" or "Apply" queue bottleneck. Here is how you can get ahead of this: 1. Increase Pipe Batching By default, a Pipe might be committing too frequently. You can force the Pipe to group more files into a single transaction by adjusting the

BATCH_SIZE

and

BATCH_FILES

properties.

sql
ALTER PIPE <your_pipe_name> 
SET ("BATCH_SIZE" = "2GB", "BATCH_FILES" = "1000");

* BATCH_SIZE: Default is 1GB. Increasing this helps if your individual files are large. * BATCH_FILES: Default is 256. Increasing this is crucial if you have many small files (e.g., thousands of small Parquet/CSV files for the month). 2. Tuning the Primary Key "Apply" Parallelism If your BE nodes are getting stuck during the "Commit" or "Apply" phase, you can increase the number of threads dedicated to applying these updates in

be.conf

. * For Shared-nothing clusters:

conf
    transaction_apply_worker_count = <increase_this_value>
    # Default is the number of CPU cores. Try increasing it if you have high IO/CPU headroom.

* For Shared-data (Cloud Native) clusters:

conf
    transaction_publish_version_worker_count = <increase_this_value>

3. Ensure Persistent Index is Enabled If your PK table is large (millions/billions of rows), ensure you are using the Persistent Index. This offloads the PK index from memory to disk (SSD recommended), preventing OOM issues and reducing the "warm-up" time for the index during loads. Check your table properties:

sql
SHOW CREATE TABLE <your_table>;
-- Look for 'enable_persistent_index' = 'true'

If it's false, you can enable it via:

ALTER TABLE <name> SET ("enable_persistent_index" = "true");

4. Alternative: Single Bulk Load for Backfills If you are doing a one-time backfill of a month's data, using a Pipe might be overkill. A single

INSERT INTO ... SELECT FROM FILES()

statement is processed as a single transaction. This is often much more efficient for massive data sets because it only triggers the PK index update/compaction cycle once.

sql
INSERT INTO <your_pk_table>
SELECT * FROM FILES(
    "path" = "<s3://bucket/path/to/month_data/*>",
    "format" = "parquet",
    ...
);

5. Monitor for Backlogs To confirm if the "jam" is due to versioning, run:

sql
SHOW BACKENDS\G

Check the

TabletMaxRunningMs

and if any backends have a very high

VisibleVersion

compared to others. If

SHOW PIPES

shows many files in a

LOADING

or

ERROR

state, it's a sign the transaction queue is full. References • sql-reference/sql-statements/loading_unloading/pipe/ALTER_PIPE.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/information_schema/pipe_files.md • sql-reference/System_variable.md

@Rocky removing the pk help here?

Removing whether or not a Primary Key (PK) will help depends entirely on the specific issue you are facing (e.g., high memory usage, slow loading, or query performance). Here are the scenarios where removing the PK (switching to a Duplicate Key table) or modifying it might help: 1. If you are facing High Memory Usage PK tables in StarRocks maintain a Primary Key Index to track the location of each row for fast updates. * The Problem: By default, this index can consume significant RAM (roughly 20-30 bytes per row). If you have billions of rows, this can lead to OOM (Out of Memory) issues. * The Solution: * If you don't need to update or delete data (append-only), switching to a Duplicate Key table will remove the memory overhead entirely. * If you do need updates but want to save memory, you can keep the PK table but enable the Persistent Index (

"enable_persistent_index" = "true"

), which stores the index on disk (SSD recommended) instead of RAM. 2. If you have Slow Loading Speeds * The Problem: When loading data into a PK table, StarRocks must check the index to see if the record already exists (to perform an update) and then write a "Delete Vector." This index lookup adds overhead. * The Solution: A Duplicate Key table is much faster for ingestion because it simply appends data without any "existence check" or index maintenance. 3. If your Primary Key is a large String (VARCHAR) * The Problem: Large strings as primary keys consume more memory and slow down comparisons. * The Solution: Use an

INT

or

BIGINT

as the primary key if possible. If you can't change the data type and don't strictly need the uniqueness guarantee at the database level, removing the PK will help. 4. When NOT to remove the PK Do not remove the PK if: * You need real-time updates/deletes: PK tables are specifically optimized for this. * Query Performance is your priority: PK tables are significantly faster (3-10x) than Unique Key tables for queries because they use a "Delete+Insert" strategy rather than "Merge-on-Read." To give you a more specific recommendation, could you share: 1. Are you seeing a specific error (like OOM) or just slow performance? 2. Do you actually need to update or delete rows in this table? 3. How many rows are in the table and what are the PK columns? References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md

hey @Rocky I'm getting this error from a transaction created by a pipe that is inserting into a large pk table that is mostly already populated meaning these are upserts Fail to publish partition 1173582 error Fail to publish version for tablets [1173589]: delvec inconsistent tablet:1173589 rssid:21 #old:1101841 #add:669 #new:1102254 old_v:14 v:15, host: <myhost>

The error message you're seeing,

delvec inconsistent ... #old:X #add:Y #new:Z

, indicates a metadata inconsistency within the Primary Key (PK) table's deletion vector (DelVec) mechanism. In StarRocks, when you perform an upsert into a Primary Key table, the system uses a deletion vector to mark old versions of rows as "deleted" so that only the newest version is read. The error occurs during the

publish version

phase because the math for these markers doesn't add up:

old + add

(what the transaction expected) does not equal

new

(what the backend actually found). Common Causes 1. Concurrency Race Conditions: High-frequency writes (like those from a Pipe) or a conflict between a background compaction task and a write task can sometimes cause metadata to get out of sync in older StarRocks versions. 2. Persistent Index Corruption: If the table has

enable_persistent_index = true

, the index on disk might have become inconsistent with the actual data rowsets. 3. Known Bugs: There are known issues related to DelVec inconsistencies that were specifically fixed in later patch releases (e.g., v3.2.x and v3.3.x). Recommended Steps to Resolve 1. Check Tablet Consistency First, identify which replicas are affected. Run the following command for the tablet ID mentioned in your error (e.g.,

1173589

):

sql
ADMIN CHECK TABLET (1173589) PROPERTIES("type" = "consistency");

After running this, check the results in:

sql
SHOW PROC "/statistic";

If

InconsistentTabletNum

is greater than 0, StarRocks has detected the mismatch. 2. Verify Replica Status Check if any specific replica is marked as "Bad" or has a version mismatch:

sql
ADMIN SHOW REPLICA STATUS FROM <your_table_name> WHERE TabletId = 1173589;

Look at the

IsBad

column. If a replica is bad but not being automatically repaired, you can manually mark it as bad to force StarRocks to rebuild it from a healthy replica:

sql
-- Replace <backend_id> with the ID of the backend containing the inconsistent replica
ADMIN SET REPLICA STATUS PROPERTIES("tablet_id" = "1173589", "backend_id" = "<backend_id>", "status" = "bad");

3. Force Compaction Sometimes triggering a manual compaction can help reconcile the deletion vectors:

sql
ALTER TABLE <your_table_name> COMPACT;

4. Upgrade (Long-term Fix) If you are on an older version (e.g., below v3.2.10 or v3.3.8), this is likely a bug that has been addressed. For instance, v3.3.8 includes a fix specifically for: "BE crashes/errors caused by inconsistencies in the DelVec of Primary Key tables". Upgrading to the latest patch of your current minor version is highly recommended to prevent this from recurring during Pipe operations. Summary of your specific error numbers: In your case:

#old:1101841

+

#add:669

=

1102510

. However, the system found

#new:1102254

. The difference is exactly 256, which often suggests a specific internal block-level update was missed or miscounted. This strongly points toward a version management bug that is solved by patching the cluster. References • release_notes/release-3.2.md • table_design/indexes/vector_index.md • release_notes/release-3.3.md

@Rocky arch: shared data daily partition size: ~50m rows table: primary key say i have january already mostly popluated in the table but not sure if i missed something, I want to run a pipe to ingest all of that month again, would you suggest dropping the partitions before running the pipe or something else?

For your scenario on a Primary Key table in Shared-data architecture, I would recommend using

INSERT OVERWRITE

instead of manually dropping partitions or just running a standard Pipe. Here is why and how to handle this: 1. Why

INSERT OVERWRITE

is better than dropping If you manually drop or truncate partitions before re-ingesting: * Downtime: The data is physically gone until the new ingestion finishes. Queries during that window will return incomplete results. * Risk: If the ingestion fails halfway through, you are left with empty partitions. * "Ghost" Data: If you just "re-run" a normal ingestion (upsert), StarRocks will update matching keys, but it will not remove rows that exist in the table but are no longer in your source files.

INSERT OVERWRITE

is atomic. It loads the data into temporary structures and only swaps them with the active partitions once the load is successful. This ensures zero downtime and a "clean slate" for those specific partitions. 2. Handling the "Pipe" behavior If you are using the StarRocks Pipe (introduced in v3.2), there is a catch: Pipes are idempotent. Pipe tracks every file it has already loaded (using file name and ETag) in

information_schema.pipe_files

. If you simply point a Pipe at the same folder for January, it will see that those files were already processed and skip them, even if you cleared the table. To re-ingest using a Pipe-like flow for a specific month, you have two main options: Option A: The "One-Shot" Overwrite (Recommended) Since you want to ensure you "didn't miss something," you can run a bulk

INSERT OVERWRITE

statement using the

FILES()

table function. This bypasses the Pipe's file tracking and replaces the month's data entirely.

sql
INSERT OVERWRITE your_table 
PARTITION (p20240101, p20240102, ... p20240131) -- List your January partitions
SELECT * FROM FILES(
    "path" = "<s3://your-bucket/data/january/*>",
    "format" = "parquet",
    ... -- your S3 credentials/config
);

_Note: If 1.5 billion rows (50m/day _ 31 days) is too large for a single transaction in your cluster, you can run this daily (one partition at a time).* Option B: Resetting the Pipe If you prefer to use the background Pipe mechanism (e.g., to let it handle the heavy lifting in micro-batches), you must "reset" its memory: 1. Truncate/Drop the January partitions (Note: This causes downtime). 2. Drop the existing Pipe:

DROP PIPE jan_ingest_pipe;

(This clears the file tracking history for that pipe). 3. Re-create the Pipe: Use a new name or the same name. It will see the files as "new" and start ingesting them into the empty partitions. Summary of Recommendation * For Data Integrity: Use

INSERT OVERWRITE

. It is the only way to guarantee the table perfectly matches your source files (removing any stray data) without downtime. * For Management: If you have 50m rows per day, I suggest running the

INSERT OVERWRITE

one day at a time in a small script. This keeps transactions manageable and ensures that if one day fails, the others are already safely committed. Pro-tip for Shared-data: Since you are on Shared-data,

INSERT OVERWRITE

is highly efficient because it performs the metadata swap on the object store, making it very effective for large-scale data correction. References • loading/s3.md • loading/minio.md