Hi Everyone, We have enabled partial upserts in realtime table for pinot, but due to the nature of business we had to setup

metadataTTL

expiry on upserts to be 12hrs We are seeing that due to that we are now getting duplicate entries created in Pinot due to update of the event arriving after 12 hr window This is causing our reports which utilised count(*) to show wrong results and distinct is not working as the data set has grown too huge To solve this issue we are planning to leverage the Segment compaction job for this using a minion Have anyone implemented such segment compaction task in Java which we can take reference of Then please help

we have the same issue but with a lot bigger expiration window i have created a proposal in github - https://github.com/apache/pinot/issues/15802

@Utsav Jain the metadataTTL feature requires that deleted keys do mot reappear post TTL. I think compaction job won’t work because it relies on metadata to be present (it would be TTL’d). What’s the worst case scenario after which a key can reappear?

@Mayank we have setup the metadataTTL to 12hrs and we can still expect for 20% events out of 70M updates can come after 12hrs

Compaction is to remove obsolete records, and it relies on metadata. If the metadata is TTL’d it won’t be able to identify what records are obsolete, right?

So do you have some better reference how to get accurate results on count(*) for a bigger dataset then if not through compaction ?

Well, you need to have TTL > PK life

@Mayank one doubt here if the key which has TTLd cannot be considered for compaction then how that older version of record is being considered while querying.

Metadata tells out of N versions of the record which one is the correct one and which ones are obsolete. Without Metadata, they are all treated as not obsolete.

So if we have metadata enabled on upserts and if we are qyuerying at any point jn time then once the expiry has reached for an older version then while querying for that version will not be included right ?

The way it works is as follows: • There is a PK map stored on heap that tells for each PK value, where is the latest record. This is the one on which TTL applies. • Per segment there is a validDocIDSet that says for a segment which docIds are valid. If a PK is TTL'd and it re-appears, for closed segments the validDocIDSet will ensure we don't count those required. If a new event with same PK comes, it is treated as new one. Can you elaborate the issue you are seeing with this?