👋 Hi all, I have a use case that I wanted to see if Pinot would potentially be a good fit for. Note that I haven't yet used Pinot, I'm merely testing the waters before I dive in deep 🙂 Context: • Very high cardinality: ◦ 100's of millions / hour ◦ Billions / day ◦ Expected to be 10s to 100s of billions / month ◦ The cardinality eventually becomes asymptotic ◦ These big numbers are the "simple" case and it's expected that a single field could be one or two orders of magnitude larger than this case • Low dimensionality: <10 fields • The primary query pattern is as follows: ◦ Perform aggregations (count, count distinct, sum, etc...) across one or more fields ◦ Exact matching filtering on aggregated fields, e.g.

where foo = 'something'

• The secondary query pattern is the same as the primary, but inexact matching which could potentially included regex • Data will be treated as immutable (exceptions for data deletions for GDPR) • Data will be bulk loaded and already pre-aggregated Questions: 1. Can Pinot efficiently handle data with cardinalities of this magnitude? 2. Are sub-second response times feasible for exact matching? 3. Can Pinot efficiently handle aggregates across multiple fields with high cardinality or is it better to split them into smaller subsets? 4. Any gut feeling estimate as to how large of a cluster I would need? Even if Pinot can work, the next question is how much will it cost 🙂

@User Thanks for posting these details. Pinot definitely is the right choice for your case:

1. Yes high cardinality is not an issue.
2. Yes, with indexing, you can do a lot better than sub second for matching equality predicates.
3. Is it just aggregates or group-by too? Both are fine, will need to know more concrete info to suggest optimizations.
4. For cluster size, will need more info like data size, read/write qps, latency requirements query selectivity on average et.

Thanks! (3) both - here are some examples: A: Simple summation

-- lowest-level granularity
SELECT
   a
   , b
   , c
   , SUM(num_events) AS num_events
FROM ...
WHERE event_date BETWEEN ... AND ...
   AND a = ... AND b = ... AND c = ...
GROUP BY 1, 2, 3

B: Count distinct

SELECT
  a
  , b
  , COUNT(DISTINCT c) AS num_uq_c
  , SUM(num_events) AS num_events
FROM ...
WHERE event_date BETWEEN ... AND ...
   AND a = ... AND b = ... 
GROUP BY 1, 2

C: Complex rollup, producing summarized view with multiple levels of aggregations

WITH data AS (
    -- Reduce dataset
    SELECT
        a
        , b
        , c
        , SUM(num_events) AS num_events
    FROM ...
    WHERE event_date BETWEEN ... AND ...
        AND c = ...
    GROUP BY 1, 2, 3
), low_level AS (
    -- Find specific low-level result
    SELECT
        a
        , b
        , c
        , num_events
    FROM data
    WHERE a = ... AND b = ... AND c = ...
    LIMIT 1
), mid_level AS (
    -- Do the next-level rollup
    SELECT
        a
        , c
        , COUNT(1) AS num_uq_b
        , SUM(num_events) AS num_events_r1
    FROM data
    WHERE a = ...
    GROUP BY 1, 2
), top_level AS (
    -- Top-level rollup
    SELECT
        c
        , COUNT(DISTINCT a) AS num_uq_a
        , SUM(num_events) AS num_tot_events
        , COUNT(1) AS num_uq_ab
    FROM data
    GROUP BY 1
)
SELECT
    ll.*
    , ml.*
    , tl.*
FROM low_level ll
JOIN mid_level ml ON ll.a = ml.a AND ll.c = ml.c
JOIN top_level tl ON ml.c = tl.c

(4) The expectation is to load data once / hr. So let's say 100M records / hr (in reality there'd be multiple loads, but I can do math from here). Data size is small, let's say 1KiB / record, resulting in <100 GiB / hr. Writes will only be on the hourly bulk load cadence. Reads are low volume - a few thousand / day, with most (let's say ~90%) doing exact searches. edit - forgot to add the

group by

in some of the queries 🤦‍♂️

Pinot doens’t do complex joins. For that one option is to use Presto or Trino connectors for Pinot.

I wanted to follow-up on this and post some early results. I loaded in a day's worth of data, which was about 5.5B documents (this is already pre-aggregated data to the hour-level). I'm starting simple and using bloom indexes, since I'm primarily doing exact string matching. For hardware, I'm using a single m5.16xl (64 cores, 256GiB RAM) with a 2TiB attached EBS volume. I'm running one server, broker, and controller. I have 123 segments, each having around 50M records. The data in S3 is ~42GB and on the box is ~165GB. The results are a bit mixed: A simple

SELECT DISTINCTCOUNTHLL(...) FROM ...

fails to run. It seems that a single core gets pegged and a 427 error code is returned. A more representative query is much better. It takes ~3s for a

DISTINCTCOUNT

and ~300ms for a

DISTINCTCOUNTHLL

where the

where

clause matches on ~3M documents (again exact string matching on a column with a bloom index). The box seems to be asleep. I'm not sure why the first query fails or if there are settings to tweak it to make things faster, but overall the results are promising. It looks like the bounding isn't by cardinality but by documents matching the predicate.

@User for exact string match, you want to use inverted index, not bloom index.

Thanks @User, I'll try inverted and start tree and see how it goes. I can share the query, I'm not sure how to get the metadata (is that available in the query window within Pinot?). Right now I'm primarily interested in forecasting how Pinot will scale. Resource-wise, it seems to be doing great. What I'm trying to figure out is if there's a way to introduce more parallelism (I think server + broker is analogous to scatter + gather?). I just have one server, but my box is asleep. My understanding was that a single thread would process a segment and I assumed that in this scenario multiple servers vs one shouldn't matter, since the server could spin up as many threads as needed? An upper bound on matched predicates could be 10M / hour, so for one month (the needed time range for this use case), I'm looking at potentially billions of matches. Assuming I can control the degree of parallelism (e.g. one server per day or something), then I should be able to get near constant time performance. Does this make sense? It's possible I'm understanding Pinot incorrectly?

For query execution, the parallelism on server side is based on number of segments. Number of threads is fixed to 2x number of cores by default. When you say scale, do you want to optimize for latency or throughput (what’s the rough requirements for those)? Do really low selectivity, startree will definitely help

Latency is the primary driver. I can tolerate <5s (for the full month), but was hoping to make it faster for a smooth UX (<1s). Throughput will be low -- at most <100 simultaneous queries. So from what you said, it sounds like one server should be fine. I think the pain point here is that all segments have to be read. Looking at this doc, it seems like throughput can get addressed more easily. Aside from that, startree seems most promising. Partitioning could work, but then I'd only be able to optimize for some of the query patterns, so instead I'd probably opt to pre-aggregate further. Another thought I had was to reduce the number of records in a segment to push up the parallelism. (I'm looking at this). Assuming the

psf

files are the segments, those are ~1.3 GiB.