hi all -- wanted to ask a basic performance questi...
# generalm
Mike Beyer
03/28/2024, 8:52 PMhi all -- wanted to ask a basic performance question:
1. I have a 1B row table of data
2. I kick off a process that will need to update 10M of these rows
3. I will want to query the updated table and produce some aggregated metrics
Right now we do this with AzureSQL. Not the best setup and we see high latencies due to the large transactions.
Was wondering how well pinot can handle this use case? With appropriate scaling, is it possible to see sub second performance to complete (1) -> (3) [end to end]?
m
Mayank
04/01/2024, 9:15 PM
Apologies for missing your message, Pinot should be able to handle this use case. Would need a bit more info to help with the details. What kind of queries are you trying to run?
m
Mike Beyer
04/01/2024, 9:17 PM@Mayank -- Bulk upsert of 1M rows foloowed by JOIN and GROUP BY query to get updated metrics
Mike Beyer
04/01/2024, 9:17 PMUPSERT is mostly UPDATE
Mike Beyer
04/01/2024, 9:18 PMright now round trip can be very log due to database latency/bad query plan - wondering if moving to a streaming model will enable better UX as several people kick off updates to teh system iun -parallel
m
Mayank
04/01/2024, 9:21 PM
Pinot supports upserts in streaming model only (atm). So you can have 10M rows upserted via stream, while querying in parallel. Given joins, the latency would depend on the complexity of joins
Mayank
04/01/2024, 9:21 PM
Is there a sample query you can share, and/or an idea of the size of left and right tables to be joined (post applying predicates)
m
Mike Beyer
04/01/2024, 9:23 PMSELECT sum(sales) as total_sales FROM sales_tbl GROUP BY region, month HAVING region in ('north', 'south')Mike Beyer
04/01/2024, 9:24 PMso we have a very large "facts" table (~1B rows) that we filter using WHERE clauses to get sales by region, month, category, etc
m
Mayank
04/01/2024, 9:25 PM
If you move the having to where, the query can be super fast.
Mayank
04/01/2024, 9:26 PM
And assuming low cardinality for regions, and no other filters (ie very low selectivity query), you can use StarTree index. Otherwise, sorted/inv index will work well
m
Mike Beyer
04/01/2024, 9:27 PMyeah, cardinality of the main categories is low
Mike Beyer
04/01/2024, 9:27 PMmain issue is slowness of the DB response to updating 1M records across a 1B row table
Mike Beyer
04/01/2024, 9:28 PMI think Pinot would be very good at this while still keeping good latency on the SELECT query after update