Apache Pinot #troubleshooting

Badhusha Muhammed

04/15/2026, 9:14 AM

cc: @Mayank, @Xiang Fu Can you please help here .

Richa Kumari

04/15/2026, 1:55 PM

Hi Pinot Team , We are migrating from Elasticsearch to Pinot . We use cursor pagination (POST /query/sql?getCursor=true) on V1 to fetch large result sets including the requestId column (primary key). We originally used V2 (/query) for cursor, but V2's multi-stage engine fails when requestId is in the SELECT clause — the query is cancelled during plan setup (EMPTY_MAILBOX_SEND, error 503, "CANCELLED: client cancelled") because requestId conflicts with V2's internal gRPC stage-tracking identifier. Alias, CAST, and CONCAT wrapping all failed. Per official docs, cursor is a V1 feature, so we switched to /query/sql?getCursor=true. The initial cursor query works correctly and returns the first page. However, subsequent pagination GETs to /responseStore/{requestId}/results fail intermittently with "Query results for {requestId} not found". Out of 12 concurrent Lambda invocations (1 hour split into 5-min windows), some succeed fully, some fail immediately, and some partially succeed (e.g., 600K of 1.4M records fetched before failure). @Mayank @Xiang Fu

Richa Kumari

04/16/2026, 9:55 AM

@Jinesh Parakh @Mayank @Xiang Fu Hi Pinot team, We're running into a blocking issue with the V2 multi-stage engine and need guidance. Our Setup: • Pinot cluster with 18 brokers behind a Network Load Balancer (NLB) • Table: urdmCore with requestId as the primary key column • Query volume: ~1.4M+ records per query, using cursor-based pagination • Client: AWS Lambda (Java) The Problem: When we include requestId in the SELECT clause on the V2 multi-stage engine (/query), the query gets cancelled immediately with EMPTY_MAILBOX_SEND / error 503 CANCELLED errors. Omitting requestId from SELECT works perfectly — cursor, pagination, everything functions as expected. We believe this is a namespace collision between V2's internal gRPC stage-tracking (which uses requestId for inter-stage communication) and our column named requestId. requestId is our primary key — we cannot omit it from SELECT. We need it for record-level validation between data sources. We are thinking of creating new column with changed name since pinot columns are immutable , was considering using transform function to get old data of requestId column in new column. Just needed an opinion / heads-up on this approach.

Soon

04/17/2026, 6:00 PM

Hi Pinot team! I'm currently encountering a weird behavior in text index where regex is not matching rows as expected. For example, my query contains

WHERE TEXT_MATCH(indexedString3, '/.*nav#primary-menu.*/')

and its not matching a row with

nav#primary-menu

. My theory this is due to that special character

and that standard analyzer breaks them up into tokens like

nav

and

primary-menu

and never keep them as a whole. Would be greatly helpful if someone could confirm my theory! Thanks in advance!

Shubham

04/29/2026, 6:18 AM

Hi Team. We are seeing high latency for the following query : SELECT cevalDec, vevalDec, DISTINCTCOUNT(subjectId) AS Count FROM ModelScoreVariableTable WHERE ekey = 'ee801a97-aae1-490d-ac7f-d5556060f758' GROUP BY cevalDec, vevalDec ORDER BY cevalDec, vevalDec LIMIT 1000 Can you let us know if we can optimize it? Attaching other information in thread.

piby

04/30/2026, 9:16 AM

Hi Pinot Community! 👋 We are refining our query standards to ensure we avoid implicit casting and leverage indexes properly. Consider this example of column my_timestamp_col of type TIMESTAMP

Copy code

WHERE my_timestamp_col > '2025-11-02T00:00:00+01:00‘

This syntax works but it takes a very long time. I am assuming either Pinot is implicitly casting my_timestamp_col to string and doing string comparison or indexes are getting ignored due to type mismatch. When we convert our datetime string to TIMESTAMP type exactly, our query performance improves significantly. However, I’m curious which of these three approaches is recommended for filtering TIMESTAMP columns, especially regarding the Multi-Stage Engine: Option A (ANSI SQL CAST):

Copy code

WHERE my_timestamp_col > CAST('2025-11-02T00:00:00+01:00' AS TIMESTAMP)

Option B (Explicit Functions):

Copy code

WHERE my_timestamp_col > toTimestamp(fromIso8601('2025-11-02T00:00:00+01:00'))

Option C (Standard Literal - This does not support timezones - UTC only ):

Copy code

WHERE my_timestamp_col > TIMESTAMP '2025-11-02 00:00'

Our specific questions: Performance: Is any one of these more "index-friendly" than the others? Timezones: Does CAST (Option A) reliably handle the +01:00 offset, or should we stick to fromIso8601 (Option B) for anything non-UTC? Engine Preference: With the move toward the Multi-Stage Engine, is there a preferred syntax for long-term compatibility? (we only use multi stage engine) Would love to hear your thoughts on the "cleanest" way to handle this in production. Thanks!

piby

04/30/2026, 3:06 PM

Hey, We have issues with realtime/hybrid tables whenever we perform rolling restarts on servers. We are using Pinot 1.4.0. How can we make it more reliable? We do have 3 replicas per segment. So if one server is down, we do expect it to take the data from replicas. Furthermore, no query works on the table if only one segment is down. If we just want to query the latest data and some old segment is down, we do get the error.

Copy code

When multistage is enabled

query > SELECT * FROM my_table LIMIT 10;

useMultiStage=true
usePhysicalOptimizer=true

DatabaseError: {'errorCode': 150,
 'message': 'SQLParsingError: Error composing query plan: No enum constant '
            'org.apache.pinot.spi.config.table.TableType.my_table. No '
            'enum constant '
            'org.apache.pinot.spi.config.table.TableType.my_table'}

useMultistageEngine=false
usePhysicalOptimizer=false

DatabaseError: {'errorCode': 305,
 'message': '3 segments unavailable '
            '[my_table__2__3373__20260430T0524Z, '
            'my_table__2__3380__20260430T1225Z, '
            'my_table__2__3180__20260422T0357Z, '], with routing policy: '
            'balanced [realtime], balanced [offline]'}

Rajat

05/25/2026, 11:03 AM

Hey Pinot folks 👋 I'm trying to validate a PARTIAL upsert use case for our shipmentMerged table and ran into behavior I can't reconcile with the docs. Would love a quick sanity check on what's actually intended. Setup: mode: PARTIAL, nullHandlingEnabled: true, comparison column s_updated_at. Use case: dual-writer on the same table. One Flink pipeline emits full shipment records (all ~80 fields populated). A second pipeline emits partial records carrying only 2 NDR fields (is_ndr, no_of_attempts) — the other 78 columns are Avro-null. Goal: partial records update only the 2 NDR columns; full records update everything; intentional NULLs from source MySQL propagate to Pinot. What I observed on the test cluster:

Copy code

Strategy   | Non-null incoming  | Null incoming (nullHandlingEnabled:        true)
-----------+--------------------+-----------------------------------------
IGNORE     | Keeps existing     | Keeps existing
           | (never updates     |
           |  after first       |
           |  insert)    
-----------+--------------------+-----------------------------------------       
OVERWRITE  | Takes new          | Framework preserves existing
           |                    | (null skipped before merger)

If this is correct, then: 1. There's no strategy combination that lets a single PARTIAL table support our dual-writer pattern AND propagate intentional NULLs from source. The two requirements collide on how "incoming null" is interpreted. 2. IGNORE is unusable for any column that ever needs to update — feels less like "ignore" and more like "freeze after first insert." 3. OVERWRITE + nullHandlingEnabled: true is effectively "OVERWRITE_NON_NULL" — useful, but it means partial-upsert can't intentionally clear a column to null. Questions: 1. Is the table above the documented intended behavior, or am I missing a config knob? 2. Is there a known pattern in Pinot for "update on non-null AND clear to null when source genuinely cleared"? (Other than nullHandlingEnabled: false, which loses Avro null semantics globally.) 3. Any plan for a null-aware variant strategy in upcoming releases? We've reverted to FULL upsert for our prod path. Just want to make sure I'm not under-using PARTIAL. Thanks 🙏 C.C: @Mayank @Xiang Fu @Jackie

Hassan Ait Brik

05/27/2026, 12:37 PM

Hi Pinot community, I’m trying to clarify the expected behavior of

UpsertCompactMergeTask

with

validDocIdsType=SNAPSHOT

IN_MEMORY

. We have a REALTIME upsert table on Pinot 1.4.0 with:

Copy code

"upsertConfig": {
  "mode": "FULL",
  "snapshot": "ENABLE",
  "enableSnapshot": true
}

At the beginning of May, we updated this table config to add

UpsertCompactMergeTask

. Since then, the task has worked partially: we reduced the table from around 20K segments to around 3K segments. However, a lot of old completed segments still cannot be merged. From what we see, the task is not able to retrieve their

validDocIds

using

SNAPSHOT

. When triggering the task manually, the controller logs show something like:

Copy code

Retrieved validDocIds metadata for 15 segments from 96 server requests
Finished generating 0 tasks configs

But we still have many old completed segments from 2023 to 2025 that look like they should be compactable. I tested the

validDocIdsMetadata

endpoint on one old segment:

Copy code

validDocIdsType=SNAPSHOT -> []
validDocIdsType=IN_MEMORY -> returns metadata correctly
validDocIdsType=IN_MEMORY_WITH_DELETE -> []

Example with `IN_MEMORY`:

Copy code

{
  "segmentName": "daily_activity__2__0__20230427T1137Z",
  "totalValidDocs": 4,
  "totalInvalidDocs": 0,
  "totalDocs": 4,
  "segmentCrc": "2205091866",
  "validDocIdsType": "IN_MEMORY",
  "serverStatus": "GOOD"
}

So it looks like the upsert state exists in memory on the realtime servers, but many historical segments do not have

validDocIds

snapshots. From the docs, I understand that: •

SNAPSHOT

is the default

validDocIdsType

for

UpsertCompactMergeTask

•

SNAPSHOT

requires

enableSnapshot=true

• snapshots are created/refreshed during the normal upsert / segment commit lifecycle • there does not seem to be a documented command/API to backfill

validDocIds

snapshots for all already-committed historical segments My questions are: 1. Is this expected behavior for old completed segments that existed before we added the compaction task / before snapshots were generated for them? 2. Is there any supported way to force Pinot to generate

validDocIds

snapshots for all existing completed segments? 3. If not, is the recommended approach to run UpsertCompactMergeTask temporarily with:"validDocIdsType": "IN_MEMORY" to compact the remaining historical backlog, and then switch back to

SNAPSHOT

for steady state? 4. Are there any risks or caveats with using

IN_MEMORY

for this remaining backlog, assuming the servers are stable and the table is not being restarted/rebalanced? Thanks!

Badhusha Muhammed

05/27/2026, 6:06 PM

I know its after a long time, But just wanted to update one thing . > On this specific Pinot server, all tables are upsert-enabled with a combined disk utilization of 612GB. What could be causing the heap to spike to 700GB? During Pinot server startup, the heap fills up rapidly, leading to continuous Full GC pauses and OOM failures. Upon debugging, we got to know that the

validDocIds

snapshot was missing from many segments. Assumption 1. Dataset: We are running a 10 TB Upsert table containing millions of rows with a high-cardinality primary key. 2. Restart Behavior: When a server hosting an Upsert table restarts, it must rebuild the in-memory Upsert Metadata Manager (the hash map tracking

PrimaryKey -> RecordLocation

). 3. Segments with Snapshots: The server can efficiently skip entire segments if their maximum timestamp falls outside the metadata TTL, keeping heap usage low. 4. Segments without Snapshots: The server is forced to load the entire segment into the heap to determine which records are valid. A background TTL filter job (running , lets say every 10 minutes) is responsible for cleaning up expired keys. Now i feel like the 4th point is causing this issue of high heap , If yes ( 4th point is correct ) Is there any way we can create snapshot for segments thats are missing them..? If no ( 4th point is wrong ) Can you please let me know, what other reason can be there ..? @Xiang Fu, @Mayank

Senthil Maruthaiappan

05/28/2026, 7:54 PM

Hi, I am trying optimize a query which brings data from

30 different segments

of a table. I use

timestamp

for the segment partitioning and also setup

rangeindexcolumn

on the same column

'report_date'

. for example select name, total_sales from product_sales where

report_date > '2024-01-20'

and

report_date < '2024-01-27'.

I see that no index has been used for this because

numEntriesScannedInFilter

is not zero. Lets say, I have

100k rows

per segment for

30days

worth of data then takes about `504ms f`or the query. Anyone knows how to optimize or tune any of the stuff?

dstawiarz

05/29/2026, 12:15 PM

Hi Team, I am new to Pinot (1.2.0 version) and facing following challenge: currently we have one realtime table on our Pinot cluster. We would like to remove it and recreate with the same name on a daily basis. We often see that although table was successfully deleted, external view is present long after table removal. Only once server is restarted EV seems to be removed. My question would be: • Is there any direct way to remove EV or force Pinot to refresh EV status, • Is it safe to create the table even EV is still visible after deletion or we must wait until EV is no longer present? Thanks!

piby

06/02/2026, 8:02 AM

Hi Team, We had an incident where heap memory of one of our pinot servers was above 90%. This caused downtime in production. How can we prevent it in the future? We are using pinot 1.4.0 currently on k8s and value.yaml is in the comments.

Claudia Asanza

06/03/2026, 9:58 PM

Hi! I tested MergeRollupTask on an OFFLINE APPEND table using: 1month.mergeType = concat 1month.bucketTimePeriod = 1M Expected: daily segments -> one monthly segment Observed: one daily segment with hourly bucket_start_ts values -> 24 hourly merged segments The task completes successfully, but increases the number of segments. Is this expected behavior for a DATE_TIME column with granularity 1:HOURS?

Raghavendra M

06/15/2026, 12:45 PM

Hi @Mayank @Xiang Fu while loading json data to OFFLINE table , am getting below error and segment state showing as ERROR.

Copy code

java.lang.reflect.InvocationTargetException
        at jdk.internal.reflect.GeneratedMethodAccessor11.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at org.apache.helix.messaging.handling.HelixStateTransitionHandler.invoke(HelixStateTransitionHandler.java:350)
        at org.apache.helix.messaging.handling.HelixStateTransitionHandler.handleMessage(HelixStateTransitionHandler.java:278)
        at org.apache.helix.messaging.handling.HelixTask.call(HelixTask.java:97)
        at org.apache.helix.messaging.handling.HelixTask.call(HelixTask.java:49)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.lang.OutOfMemoryError: Cannot reserve 132127 bytes of direct buffer memory (allocated: 64424466414, limit: 64424509440)

am actually using spark3 to push data to pinot OFFLINE table. any help here?

Badhusha Muhammed

06/17/2026, 3:32 AM

Hi Team We have an Upset Table in Pinot 1.2.0/1.3.0 version, which has TIMESTAMP dType as timeColumn , But after Pinot upgrade to 1.4.0 , timeColumn does not support TIMESTAMP datatype if TTL is enabled , Which is restricting us to do schema evolutions . Is there any known workaround for this , other than re-creating the table..? @Mayank, @Xiang Fu

samirun shaik

06/25/2026, 8:07 AM

@Mayank @Xiang Fu we recently introduced deepstore in our cluster but we see only newly compelted segments are only reaching deepstore . we did repair and reload segments , and force commit but doesnt seem to get the old segments into deepstore. are we missing anything? i dont see documentations around these details aswell apart form what we have already tried

piby

06/26/2026, 10:56 AM

Hey all, Since migrating 1.4 → 1.5 and pinot-kafka-2.0 → pinot-kafka-3.0, realtime tables consuming from transactional Kafka topics (AWS MSK) get stuck. With 3 partitions × 3 replicas, one consuming segment per table wedges in an internal re-seek loop and never advances. - Reset segment: does not help - Pause / resume: does not help - Adding

stream.kafka.isolation.level: read_committed

(and confirming the table picks it up): does not help — segment still gets stuck - Only workaround: delete table, set ``auto.offset.reset=smallest``, backfill. Works until the next segment rolls — then a new segment gets stuck again at a commit boundary. It only affects transactional topics. Non-transactional topics are fine. Do you have any suggestions for me?

Mannoj

07/10/2026, 7:57 AM

Hi Team, Issue: High Off-Heap Memory Usage ("Other" section in NMT) causing OutOfMemoryError and Segments dropping to OFFLINE/ERROR state Detailed Description: We are facing a severe memory issue on our Pinot servers where they climb to maximum memory limits within minutes of a restart. The overhead is entirely off-heap. To investigate, we enabled Native Memory Tracking (NMT) with

detail

mode (

-XX:NativeMemoryTracking=detail

). Looking at the

VM.native_memory.detail

output, the memory is heavily concentrated in the Other section, while the heap looks completely normal: > Other (malloc=41940616KB #435) (~40 GB) For comparison, we checked our Elasticsearch servers under similar loads, and their off-heap/buffer usage remains minimal (only a few MBs out of 30GB available): JSON

Copy code

{
  "count": 243,
  "used_in_bytes": 23122953,
  "total_capacity_in_bytes": 23122951
}

When checking the Pinot Server logs during these crashes, we see

java.lang.OutOfMemoryError

exceptions being thrown during segment completion protocols: Plaintext

Copy code

INFO [ServerSegmentCompletionProtocolHandler] [table_name__partition__sequence__timestamp] Controller response {"streamPartitionMsgOffset":null,"buildTimeSec":-1,"isSplitCommitType":true,"status":"PROCESSED"} for http://<controller-host>:9000/segmentStoppedConsuming?reason=java.lang.OutOfMemoryError&streamPartitionMsgOffset=29329220173&instance=Server_<server-host>_8098&name=table_name__partition__sequence__timestamp

Right after these OOM errors occur, the affected segments transition into an OFFLINE or ERROR state. It feels like

mmap

is either not being effectively utilized, or there is a major native memory leak associated with the "Other" category during data consumption/segment allocation. A few questions for the community: 1. Has anyone seen Apache Pinot allocations balloon specifically in the NMT "Other" category (which usually points to direct

malloc

calls from JNI/native code or specific system allocations)? 2. Could this be related to a misconfiguration in how Pinot utilizes memory-mapped files (

mmap

) versus direct memory allocation for real-time segments? 3. We are using Pinot 1.4. Considering that it uses below setting by default.

Copy code

# Use off-heap for consuming segments (keep true)
pinot.server.instance.realtime.alloc.offheap=true

# Use MMAP (not direct) for that off-heap allocation (key setting)
pinot.server.instance.realtime.alloc.offheap.direct=false

4. Its not MMAP is not in use at all. It is but major workload is going to direct mem.

Copy code

PID="`pgrep -f "pinot-server.conf"`" ; grep -E "VmRSS|RssAnon|RssFile" /proc/$PID/status

VmRSS: 81,275,588 kB ≈ 81.28 GB (≈ 77.51 GiB)
RssAnon: 67,669,292 kB ≈ 67.67 GB (≈ 64.53 GiB)
RssFile: 13,605,948 kB ≈ 13.61 GB (≈ 12.98 GiB)

5. What are the best triage steps or configurations to tune to prevent segment consumption from blowing up native memory within minutes? Any guidance or pointers would be greatly appreciated!

Priyanshu Soni

07/22/2026, 2:03 PM

hi does pinot have existing recovery runbooks to recover data from hdfs in case there is data loss from pinot server disks? working out how to recover data in case of cluster wide data loss issues

prasanna

07/28/2026, 9:27 AM

Hi Team, just wanted to ask a very basic question in Pinot 1.5 version do we have a way to manage table retention on a dimension/categorical field other than time based retention that we use today in our pinot 1.3 deployments. example - we have event data coming into our tables via kafka and we have Hybrid tables. with 7 Days RT and 30 Days OF line table. we have a lifecycle for each event at end of which status is Clear State we would like to drop all events that are in cleared state as this table we want to maintain only active events.

Rishabh Sharma

07/30/2026, 5:57 AM

Hi all — a couple of security questions :

1. What tools are people using to scan the Pinot libraries/dependencies for vulnerabilities?

2. Are there any known open/unresolved critical vulnerabilities in Pinot 1.5.1 specifically? If so, are there recommended mitigations or a target release for fixes?

Trying to assess 1.5.1 for a production deployment. Any pointers to a security advisory page or process for reporting/tracking CVEs would also be appreciated. Thanks!

@Xiang Fu, @Mayank

Priyanshu Soni

07/31/2026, 9:34 AM

As a rule of thumb is it discouraged to perform any egress from Pinot servers apart from deep store commits? since it may interfere with ongoing ingestion

Syed Meraj Ashraf

08/26/2026, 4:28 AM

Hi we have implemented an api using cursor but in our current deployment we have two brokers running, now if the cursor response is stored in broker 1 and the api lands on broker 1 we get the response but if the api lands on broker 0 we are getting 404 any quick soultion to tackle this

Priyanshu Soni

09/04/2026, 5:43 AM

hey, is it recommended that a segment reload op be carried out for a realtime table that has around 40k segments? its distributed over 40 or so servers. the reload is needed due to a schema update

Rishabh Sharma

09/04/2026, 1:44 PM

How to reliably confirm Kafka consumers are fully stopped before deleting Kafka topics after a Pinot table deletion in 1.5.1? We are performing a clean-slate reset of our Pinot setup by deleting all tables (schema + config) and then deleting the corresponding Kafka topics. However, we observed that some Kafka topics were being recreated automatically because the Kafka consumers for those tables had not fully stopped by the time we deleted the topics. We tried polling

GET /tables/{tableName}/consumingSegmentsInfo

repeatedly until it returned no consuming segments, but this did not reliably prevent the issue. Questions: 1. Is there a definitive API or mechanism to confirm that all Kafka consumers for a table have fully stopped after the table is deleted? 2. Is there a recommended sequence or waiting strategy to ensure consumers are fully torn down before proceeding to delete Kafka topics? 3. Are there any known race conditions in the table deletion flow where consumers can briefly restart or reconnect and recreate a deleted Kafka topic?

Dhanesh Dhakrey

09/08/2026, 6:46 AM

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.

Badhusha Muhammed

09/10/2026, 8:17 AM

Using Pinot 1.4.0 Since Apache Pinot real-time Upsert tables do not support storage tiering, what is the recommended production approach to handle storage growth instead of simply adding larger disks? Specifically, can we use LVM or ZFS pools to combine multiple disks? If so, will introducing these changes impact ingestion performance or query processing latency ..? Any noticeable increase cc: @Mayank, @Xiang Fu

phani madani

09/14/2026, 4:15 AM

Hi Pinot community, We are evaluating Apache Pinot as a low-latency serving layer as part of our migration from Snowflake to an open lakehouse architecture, and I would appreciate some guidance on whether our proposed architecture aligns with Pinot best practices. Our current stack is: Trino + Apache Iceberg + Azure ADLS Gen2/HNS + Snowflake Open Catalog/Polaris + Spark + Flink + Red panda + Azure AKS*.* The use case is building/sensor analytics exposed through an application dashboard. We need to serve both recent/raw sensor telemetry and aggregated KPIs. Spark/Flink perform transformations and aggregations, with Iceberg on ADLS Gen2 acting as the durable analytical/system-of-record layer. One of our current challenges is KPI latency through the Trino → Iceberg → ADLS path. We also have an Iceberg small-file accumulation issue, so we want to avoid introducing Pinot in a way that creates additional heavy reads against the same ADLS storage. The architecture we are considering is: 1. Redpanda → Pinot for recent/realtime telemetry 2. Redpanda → Spark/Flink → Iceberg/ADLS for the durable lakehouse 3. Spark/Flink computes aggregates once and publishes the result to: ◦ Iceberg Gold for durable/historical storage ◦ Redpanda aggregate topic → Pinot for application KPI serving The application would then route: KPI / recent telemetry → Pinot Historical scans / large exports / ad-hoc SQL → Trino → Iceberg → ADLS We would use Iceberg/ADLS → Spark → Pinot primarily for initial historical loads, backfills or corrections, rather than continuously scanning Iceberg to refresh Pinot. Could the community please help us validate the following? 1. Is this a recommended pattern for using Pinot as a dedicated application-facing serving layer while Iceberg/ADLS remains the system of record? 2. For realtime ingestion, are there any known considerations when using Redpanda through its Kafka-compatible API as the Pinot stream source, particularly with SASL/TLS and consumer/offset behavior? 3. For Spark/Flink-produced aggregates, is publishing the aggregate once to Iceberg and an aggregate Kafka/Redpanda topic → Pinot preferable to having another Spark job re-read Iceberg to populate Pinot? 4. Is Azure ADLS Gen2 with HNS a well-supported production option for Pinot deep storage? Are there any specific HNS/ABFS/authentication limitations or configuration recommendations we should be aware of? 5. For Pinot on Azure AKS, would the recommended pattern be persistent/block storage such as Azure Disk for Pinot Server serving storage, with ADLS used as deep storage? 6. Does Pinot need any direct integration with Iceberg REST catalogs such as Snowflake Open Catalog/Polaris, or is it preferable for Pinot to remain completely independent of the Iceberg catalog/metadata path? 7. Given that we currently have an Iceberg small-file problem, is our assumption correct that Pinot can isolate the application KPI SLA from the Iceberg/ADLS path, while the underlying Iceberg compaction/file-layout issue still needs to be resolved independently? 8. For sensor dashboards containing recent raw telemetry plus hourly/daily/monthly aggregates, would you recommend purpose-built Pinot tables with different retention/indexing strategies rather than replicating the complete Iceberg lake into Pinot? Our main objective is to prevent customer-facing KPI latency from being dependent on Iceberg file layout, ADLS latency and Trino concurrency, without introducing additional contention for Spark/Flink/ADLS. Any recommendations or experiences with similar Pinot + Azure + Kafka/Red panda + Spark/Flink + Iceberg architecture would be greatly appreciated. Thanks!

Dhanesh Dhakrey

09/16/2026, 10:19 AM

Hello everyone, I’m trying to understand how we should approach this kind of analytical query in Pinot. Whenever I run a query like the one below, it eventually times out. For context, my

events

table has around 3*B events per month*, so I’m wondering whether this type of multi-stage query is realistically expected to work at this scale, and if so, what the recommended approach would be. The query is essentially doing the following: • Finding each user’s first

APP_INSTALL

event. • Looking at their sessions in the following 7 days. • Deduplicating sessions using

ROW_NUMBER()

because, for a given

(session_id, platform)

, there can be multiple rows and we need to select the row with the highest

event_count

. • Identifying users who were active on exactly 3 distinct days and had exactly 3 sessions during those 7 days. • Finally, scanning their sessions again to find their last usage timestamp. I’m using the multi-stage engine:

Copy code

SET useMultistageEngine = true;

and the query currently times out even with:

Copy code

OPTION(timeoutMs=90000);

My main questions are: 1. Can Pinot efficiently handle this type of query at this scale, or am I approaching it in the wrong way? 2. Would adding more/heavier indexes help here, or are the expensive parts mainly caused by the

GROUP BY

, window function, joins,

COUNT(DISTINCT ...)

, and repeated scans of

sessions

? 3. What is the recommended way to plan indexing for a query like this? Should I first use something like

EXPLAIN

/ query plan mode to understand which operators are expensive and then decide which columns/indexes to optimize? 4. Are there specific indexes or table/data-model changes that would make these joins and aggregations more efficient in Pinot? 5. Would it be better to pre-aggregate or maintain some of these metrics rather than calculating them at query time? I’m particularly interested in understanding the right process for optimizing Pinot queries: query plan → identify the expensive operators/scans → determine whether indexing can help → benchmark the change, rather than blindly adding indexes. Here is the full query for reference:

Copy code

SET useMultistageEngine = true;

WITH first_install AS (
    SELECT
        user_id,
        MIN(event_timestamp) AS first_install_timestamp,
        MIN(event_date_ist) AS first_install_date
    FROM events
    WHERE event_type = 'APP_INSTALL'
      AND user_id IS NOT NULL
      AND event_year = 2026
      AND event_month = 9
    GROUP BY user_id
),

ranked_sessions AS (
    SELECT
        s.*,
        ROW_NUMBER() OVER (
            PARTITION BY s.session_id, s.platform
            ORDER BY s.event_count DESC
        ) AS rn
    FROM sessions s
    WHERE s.session_id IS NOT NULL
),

dedup_sessions AS (
    SELECT
        session_id,
        platform,
        user_id,
        property_id,
        event_count,
        page_view_count,
        min_timestamp,
        max_timestamp,
        min_date_ist
    FROM ranked_sessions
    WHERE rn = 1
),

sessions_7d AS (
    SELECT
        fi.user_id,
        fi.first_install_timestamp,
        fi.first_install_date,
        s.session_id,
        s.platform,
        s.min_timestamp,
        s.max_timestamp,
        s.min_date_ist
    FROM first_install fi
    JOIN dedup_sessions s
        ON fi.user_id = s.user_id
    WHERE s.min_timestamp >= fi.first_install_timestamp
      AND s.min_timestamp < fi.first_install_timestamp + 604800000
),

qualified_users AS (
    SELECT
        user_id,
        MIN(first_install_timestamp) AS first_install_timestamp,
        MIN(first_install_date) AS first_install_date,
        COUNT(DISTINCT min_date_ist) AS active_days_7d,
        COUNT(DISTINCT session_id) AS session_count_7d
    FROM sessions_7d
    GROUP BY user_id
    HAVING COUNT(DISTINCT min_date_ist) = 3
       AND COUNT(DISTINCT session_id) = 3
),

last_usage AS (
    SELECT
        s.user_id,
        MAX(s.max_timestamp) AS last_used_timestamp
    FROM dedup_sessions s
    JOIN first_install fi
        ON s.user_id = fi.user_id
    GROUP BY s.user_id
)

SELECT
    q.user_id,
    q.first_install_date,

    DATETIMECONVERT(
        lu.last_used_timestamp,
        '1:MILLISECONDS:EPOCH',
        '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(Asia/Kolkata)',
        '1:DAYS'
    ) AS last_used_date,

    q.active_days_7d,
    q.session_count_7d

FROM qualified_users q
LEFT JOIN last_usage lu
    ON q.user_id = lu.user_id

ORDER BY q.first_install_date

OPTION(timeoutMs=90000);