@Rocky When we drop a partition on a primary key table and re-create it, do we need to add the Primary key back to those dropped partitions or do anything to them? We are getting duplicate records that violate our primary key constraints when we re-load the data.

When you drop and re-create a partition in a StarRocks Primary Key table, you do not need to manually add the primary key back or perform any schema-level actions. The Primary Key is a table-wide schema property, and all partitions (including newly created ones) automatically inherit this constraint. However, the "duplicate records" issue you are experiencing is a common point of confusion with Primary Key tables and partitioning. Here is why it happens and how to fix it: 1. The Partition Column is Part of the Primary Key In StarRocks Primary Key tables, the partitioning column must be included in the Primary Key. * If your table is partitioned by a date column (e.g.,

dt

), your Primary Key must look like

PRIMARY KEY (id, dt)

. * Because

dt

is part of the key, StarRocks considers

(id: 1, dt: '2023-01-01')

and

(id: 1, dt: '2023-01-02')

to be two different, unique records. * If you drop the partition for Jan 1st and load that same ID into Jan 2nd, the record from Jan 2nd will not overwrite any record in other partitions. If you query the whole table by

id

, you will see "duplicates" because the unique constraint is actually on the combination of

(id, dt)

. 2. Why Duplicates Appear After Re-loading If you are seeing duplicates within the same partition after re-creating it, check the following: * Persistent Index: If you have

enable_persistent_index

set to

true

, StarRocks stores the PK index on disk. When you drop a partition, its associated index entries are deleted. When you re-create it, it starts with a fresh index. If your source data itself contains duplicate keys for that partition, StarRocks will automatically deduplicate them during the load (keeping the last row), unless the load itself is failing to apply the "upsert" logic. * Load Type: Ensure you are using a load method that supports the Primary Key's "upsert" behavior (like Stream Load, Broker Load, or Routine Load). If you are using an older version or specific settings that treat loads as "append-only," you might see duplicates. 3. Recommended Approach:

INSERT OVERWRITE

Instead of manually dropping and adding partitions, the best practice in StarRocks is to use

INSERT OVERWRITE

:

sql
INSERT OVERWRITE my_table PARTITION (p20230101)
SELECT * FROM source_table WHERE dt = '2023-01-01';

* Atomic: It replaces the data in the partition in one atomic step. * Clean: It automatically handles the clearing of old data and the re-indexing of new data. * Consistency: It prevents the "duplicate" issue often caused by manual partition management where data might accidentally be loaded into the wrong partition or overlap with existing data. Summary Checklist: 1. Check your

CREATE TABLE

statement: Verify if your Primary Key includes the partition column. If it does, your "duplicates" are logically different keys to StarRocks. 2. Verify the query: If you only want one record per

id

, you must query with a filter on the partition column, or use a window function (

ROW_NUMBER()

) to pick the latest one if you have cross-partition duplicates. 3. Use

TRUNCATE

or `OVERWRITE`: If your goal is to refresh data, use

TRUNCATE PARTITION

or

INSERT OVERWRITE

instead of

DROP/ADD

to ensure metadata stays within-partition state. References • table_design/table_types/primary_key_table.md • faq/operation_maintenance_faq.md • table_design/data_distribution/expression_partitioning.md

@Rocky We were using pipe loads into a table that is partitioned by date, where duplicate records were in the source when looking at the Primary Key and in some cases where we had dropped the partition and it was re-created it allowed duplicate records to be created in those same exact partitions. This should be impossible.

When you drop and re-create a partition in a StarRocks Primary Key table, you do not need to manually add the primary key back. The Primary Key is a table-level schema definition; any newly created partition automatically enforces it. However, the fact that you are seeing "duplicate records" that violate your constraints after re-loading indicates one of the following scenarios is occurring: 1. The "Duplicate" IDs have different Partition Keys In StarRocks Primary Key tables, the partitioning column must be part of the Primary Key. * If your table is partitioned by a

date

column, your Primary Key is likely

(id, date)

. * Because the date is part of the key, StarRocks treats

(ID: 101, Date: 2023-10-01)

and

(ID: 101, Date: 2023-10-02)

as two unique records. * If you dropped a partition and re-loaded data with a different date than what was originally there, you will see what looks like "duplicate" IDs, but they are technically unique keys to the database. 2. Verify Table Type (Duplicate Key vs. Primary Key) Check if the table was accidentally created as a Duplicate Key table. This is the default table type in StarRocks if you don't explicitly specify a model. * Run

SHOW CREATE TABLE <table_name>;

* If the output contains

DUPLICATE KEY(...)

instead of

PRIMARY KEY(...)

, the table will allow multiple rows with the same ID. 3. Persistent Index Reset When you

DROP

and

ADD

a partition, the Primary Key Index for that specific partition is completely removed. * When you start a new

PIPE

load into the re-created partition, StarRocks begins building a fresh index for those tablets. * While StarRocks should still deduplicate keys within the same load batch, if your source files have duplicates and are loaded via

PIPE

, StarRocks will handle them as "Upserts." It will keep the latest version of the record based on the order it was processed. * If you are seeing two identical rows in the same partition after the load finishes, this is a violation of the Primary Key engine's core logic and usually points to an issue with the partition column not being in the PK or a query-side misunderstanding (like Case Sensitivity:

User1

vs

user1

). 4. Why

DROP

and

ADD

is discouraged Manually dropping and adding partitions can lead to data gaps or metadata inconsistencies if loads are running concurrently. * Recommended approach: Use

INSERT OVERWRITE

.

sql
    INSERT OVERWRITE my_table PARTITION (p20231001)
    SELECT * FROM FILES(...) WHERE date = '2023-10-01';

* This is an atomic operation. It creates a temporary partition, loads the data (deduplicating keys in the process), and swaps it with the existing one. It ensures that the Primary Key index is correctly maintained without leaving the partition "open" or "empty" during the process. Summary Checklist: 1. Run

SHOW CREATE TABLE

: Ensure it is a

PRIMARY KEY

table and check which columns make up the key. 2. Check for Cross-Partition Duplicates: Are the "duplicates" actually in different partitions? If so, you must include the partition column in your deduplication logic or window functions. 3. Check for Case/Whitespace: Primary Keys are case-sensitive strings.

101

(with a space) and

101

are different keys. 4. Use

INSERT OVERWRITE

: Replace your

DROP/ADD

logic with

OVERWRITE

to ensure a cleaner metadata transition. References • loading/Load_to_Primary_Key_tables.md • loading/Flink-connector-starrocks.md • loading/loading_introduction/troubleshooting_loading.md