Hello all, One of the main problems I’m dealing with is an upsert

sessions

table associated with our

events

table, which receives around 100M events/day. This results in roughly 100M rows/day in the sessions table as well. Real versions of the row are only a fraction of the data (~5%). Conventionally, we would keep only a few weeks of session data, but our requirement is to retain around a year or more of historical data. The challenge is that, with upsert tables, the primary keys remain in heap memory, so retaining a large number of documents eventually puts significant pressure on heap. I’m considering the following approach: 1. Keep only a small number of days of data in the realtime upsert table for recent data, while retaining roughly a year of historical data in an offline table. 2. Query the Pinot realtime table itself to extract the data. Since it is an upsert table, the query should return the deduplicated/latest version of each primary key. 3. Build and push this cleaned/deduplicated data into the offline table. 4. Verify the counts and data consistency between the realtime query results and the offline table. 5. Only after successful verification, delete the realtime segments that are completely covered by the offline data. 6. The final setup would have clean historical data in offline storage + recent data in realtime, with a hybrid table serving both. The goal is to effectively get much longer retention from the upsert table without keeping all historical primary-key metadata in heap. I’d really appreciate your thoughts on whether this is a viable approach in Pinot, or if there is a more native/recommended way to handle this use case.

Upsert feature works only with Realtime table, as in, a PK has to be contained with a single server. In order for your proposal to work, there has to be a guarantee that a key that is moved to offline table will never see any updates. If that breaks, it will break upsert.

Yeah, that is already guaranteed. I'm more concerned about the data coming from Pinot through the broker or controller. Will it be overwhelming to handle for pinot?

You should not use query path for that. You need something like this, but with dedup.

thanks i will read this and update you what i choose.