Hi team,
My current primary key count is around 10...
# generals
Shubham Kumar
10/10/2025, 9:59 AMHi team,
My current primary key count is around 100 million. Whenever I restart the server, the primary key count increases to around 260 million and then drops back to 100 million. Could you please help me understand why this behavior occurs?
m
Mayank
10/10/2025, 1:32 PM
Are you using upsert deletes?
Mayank
10/10/2025, 1:32 PM
Also cc @Kartik Khare
s
Shubham Kumar
10/10/2025, 3:05 PMYes, we are deleting the key using the delete key TTL
m
Mayank
10/10/2025, 3:11 PM
Right, that explains it.
s
Shubham Kumar
10/13/2025, 4:49 AMWhat does it explain? I didn’t get it. Could you please explain it?
m
Mayank
10/13/2025, 4:51 AM
When server restarts, it will start consuming from previous checkpoint, so the keys will come back, but then TTL will delete them again.
s
Shubham Kumar
10/13/2025, 4:55 AMPrevious checkpoint means snapshot or anything else?
m
Mayank
10/13/2025, 4:55 AM
snapshot
s
Shubham Kumar
10/13/2025, 4:57 AMOk, thanks!
Shubham Kumar
10/13/2025, 5:11 AMBased on this, the max PKs count should be around 130–140 million. However, in this case, the PKs count grows up to 270 million. The PK count in snapshot is shown in the attached screenshot.
Shubham Kumar
10/13/2025, 11:00 AMadded indexes to my table and reloaded the segment, even in this case, the primary key count increased exponentially, leading to server instance failure.
m
Mayank
10/13/2025, 1:25 PM
You are saying just adding index + calling reload api (no server restart), and still the thr PK count goes up and comes down? What index did you add? This should not happen.
cc @Kartik Khare
🤔 1
s
Shubham Kumar
10/13/2025, 1:33 PMAdded these indexes
"invertedIndexColumns": ["customer_id", "msisdn"],
"bloomFilterColumns": ["msisdn"],
"rangeIndexColumns": ["created_on"],
m
Mayank
10/13/2025, 1:34 PM
I think it might be because reloading will re-read all rows of the segment, so increase PK first during loading, and then TTL
s
Shubham Kumar
10/13/2025, 1:39 PMThis could become a bottleneck. If we create the realtime table with upsert and modify the table configuration midway, it may cause issues
m
Mayank
10/13/2025, 2:01 PM
Yes, you probably need off heap upsert and snapshots that avoid these problems. Unfortunately, not available in OSS
🥲 1
s
Shubham Kumar
10/13/2025, 2:09 PMWhen S3 as tiered storage becomes available? Once it’s available, we’ll remove the key-based approach and insert the data directly, handling everything at query time
m
Mayank
10/13/2025, 2:11 PM
I am not aware of a plan for that. Also I don’t see how that helps eliminate upsert
k
Kartik Khare
10/13/2025, 3:04 PM@Shubham Kumar mayank is right, meanwhile you can use https://docs.pinot.apache.org/manage-data/data-import/upsert-and-dedup/segment-compaction-on-upserts to remove the already upserted/deleted primary keys on periodic basis
s
Shubham Kumar
10/14/2025, 2:36 PMAfter compaction, the PK isn’t growing much now after segment reload. Thanks, @Kartik Khare and @Mayank.
m
Mayank
10/14/2025, 2:38 PM
Cool