hi team. by right at the moment we managed to work on a POC to “roll/dedup” our data on realtime table by querying historical data using python for a fixed time range like last 1 week, grouped it, flushed to json and push segments to our historical offline table using ingestion job spec. managed to reduce the segment size from 130GB++ on realtime table to 13GB++ for segment size on the offline table. by right i guess this is an unconventional ways of doing things since its kinda hard for us to use upsert table because its pretty memory consuming and been taking down our server for few times last time, are there anyone that do this kind of workaround or something similar to support your needs / use case ? our server spec : EC2 m7a.xlarge 4VCPU 16GB RAM running all components: ZK, kafka , 1 controller, 1 broker, 1 server, 1 minion we are targeting not that huge volume of query, most likely 10 - 15 QPS but not that frequent since this data is a historical data and rarely used. only used during debug and some handful of use cases for our application. plus we are resorting to this because there are too many duplicates. and the difference between this duplicates is 2 column, timestamp and log ID column ( we refers this log ID to our main Postgres DB). so i grouped it to this query and flushed the response to json for each of this

profile

, each JSON will have around 5M rows so it will have consistent JSON and segment size:

SELECT shop, svid, spid, type, profile, "key", message, product,
                   CAST(MAX(created_at) AS TIMESTAMP) AS created_at,
                   ARRAY_AGG(product_log, 'STRING', TRUE) AS product_log
            FROM   product_tracking
            WHERE  profile = {profile}
              AND  created_at >= CAST(DATE_TRUNC('DAY', timestampAdd(DAY,{-lookback_days},NOW()), 'MILLISECONDS','GMT-04:00') AS TIMESTAMP)
              AND  created_at <  CAST(DATE_TRUNC('DAY', timestampAdd(DAY,0,NOW()), 'MILLISECONDS','GMT-04:00') AS TIMESTAMP)
            GROUP BY shop, svid, spid, type, profile, "key", message, product
            LIMIT 999999999

need help for any insights/feedback from other Pinot OSS users, thanks.