What's the difference between pivot and duckdb? They seem rather similar and this implementation of group by may be copied by other to achieve similar bench results
While DuckDB is a query engine that can indeed also query Iceberg and other open table formats, Pivot aims to bring the experience of a âfull databaseâ like ClickHouse to the world of open data formats - with the ability to spin up a cluster, handle ingestion and compaction, run in server mode to serve multiple clients, etc.. in a fast and efficient way
We love DuckDB, but we see that many companies still choose ClickHouse over DuckDB, despite its proprietary storage format, because it offers a complete end-to-end solution that DuckDB doesnât
I wonder why you didn't opt to perform a Top-K aggregation in each partition in parallel. That would give you both a reduction of elements in each partition and also the correct maximum of each partition, which can be used for further pruning.
SELECT item_id, COUNT(*) AS c
FROM orders
GROUP BY item_id
ORDER BY c DESC
LIMIT 10;
They are getting the top 10 most frequent values of item_id. It's possible for one item_id to be the 11th most popular item across every partition and not the 10th. Then when you sum the counts across all partitions, it becomes the 10th most common.
Neat! For others, the data structure they are describing is a count-min sketch. It looks like they're using it to set a bound on elements that can enter the group-by and using it to skip a bunch of extra work
It's 60% faster (0.6x faster, 1.6x as fast). Throughput (âhow much stuff you can do per unit of timeâ) is the only reasonable way to describe performance[1], otherwise large speedups are basically impossible to describe in an intuitive way. (If something that took 10s now takes 1s, that's âten times as fastâ, not âzero point nine times fasterâ.)
[1] Even latency changes are best described in terms of throughput.
how can lantency changes be described in terms of throughput? you can have completely different latencies with the same throughput due to different choices of pipelining, no?
The same way: If you change latency from 10s to 1s, it's ten times as fast in terms of latency (or 900% faster, if you will); earlier, you could do 0.1 things/second, and now you can do 1.0 thing/second. The only real difference between latency and throughput is whether you can overlap work instances or not, you can use the same language to talk about their changes.
You obviously cannot _calculate_ latency from throughput or vice versa (except of course that one bounds the other); they are different values. But both can use the (very simple, very intuitive) framework of ânumber of stuffs per time unitâ.
Now where things become ugly is when you talk about âN% _slower_â, which is also useful but gets very quickly ambiguous and problematic when N is not close to zero.
The problem is that âfasterâ and âslowerâ compare speed, which is x units per y time. But here, weâre only comparing time. The semi-legit interpretation is âif one unit takes 22.2ms, how many units can I complete in 35.6msâ, and actually the answer to that is indeed the 1.6 number they mention.
Itâs not sensationalist, itâs just a bit sloppy with the word âfasterâ.
MySQL GROUP BY is not broken by default (ONLY_FULL_GROUP_BY, the standards-conforming choice, is default for a decade or more). But there is basically no team left on the MySQL optimizer, after a long series of layoffs; it's gone from something like 40 to 3, give or take. Try Postgres :-)
You know, I have a hard time seeing how it can be cheaper to run a dataset across a wire rather than just send its grouped results. That's really the problem. I mean, who cares if it takes 100ns to group by on a box when it will take 10 times much for the data to get to it and back.
The fastest possible group by is in sequential memory on that box, fetched from an nvme.
So, when we do group by partitioning, we're really saying, put the groups themselves on different boxes, and group the group by. If it were some utter megaset, the place to put it is on a mainframe, which is basically created for purposes of group by on ridiculous datasets.
At least they have useful technical information in it. Personally really like reading these blog posts.
Measurements and context is so situational that it would be useless anyway imo.
It is not like any DB company is writing blog posts with benchmarks compared to hardware limits. DuckDB had blog posts that has sentences like "You are doing it wrong if you are not using HTTP api on your database in 2026", last time I saw their blog.
I also think object storage is bad in this case. And rust is no a no-no for a database project like this, because it is not ergonomic to do static allocation and arena allocators in rust. And parquet is trash for performance because of many reasons like supported compression formats etc.
You're not wrong: if you know how much memory you need for a single query and can keep the data in attached SSD (so ephemeral SSD on AWS), scaling vertically will give you the fastest execution time.
However horizontal scaling has advantages in that it's easier to scale up and down incrementally. It gives you more aggregate network bandwidth for accessing disaggregated data. Disaggregation allows for independent scaling of compute and storage. Losing a single machine has a marginal impact on the cluster, which can enable you to use cheaper spot resources.
Basically, it's often easier to operate a system designed around horizontal scaling if you need resiliency.
What's the difference between pivot and duckdb? They seem rather similar and this implementation of group by may be copied by other to achieve similar bench results
Hi! One of Pivotâs authors here :)
While DuckDB is a query engine that can indeed also query Iceberg and other open table formats, Pivot aims to bring the experience of a âfull databaseâ like ClickHouse to the world of open data formats - with the ability to spin up a cluster, handle ingestion and compaction, run in server mode to serve multiple clients, etc.. in a fast and efficient way
We love DuckDB, but we see that many companies still choose ClickHouse over DuckDB, despite its proprietary storage format, because it offers a complete end-to-end solution that DuckDB doesnât
I wonder why you didn't opt to perform a Top-K aggregation in each partition in parallel. That would give you both a reduction of elements in each partition and also the correct maximum of each partition, which can be used for further pruning.
The query is:
They are getting the top 10 most frequent values of item_id. It's possible for one item_id to be the 11th most popular item across every partition and not the 10th. Then when you sum the counts across all partitions, it becomes the 10th most common.Neat! For others, the data structure they are describing is a count-min sketch. It looks like they're using it to set a bound on elements that can enter the group-by and using it to skip a bunch of extra work
Their clickbench results look really good.
https://benchmark.clickhouse.com/#system=+liH|Hoaus|DcD|ucqs...
I wonder if with a LLM you could better analyse and make a strategy for querying. Maybe even live restructuring of data models based on its usage.
22.2ms vs 35.6ms is 1.6x faster! It's 0.4x faster and even doing worst math possible it's 0.62x faster.
Can't we really keep the IT without breaking news/we are all gonna die/sensationalist way of passing news?
It's 60% faster (0.6x faster, 1.6x as fast). Throughput (âhow much stuff you can do per unit of timeâ) is the only reasonable way to describe performance[1], otherwise large speedups are basically impossible to describe in an intuitive way. (If something that took 10s now takes 1s, that's âten times as fastâ, not âzero point nine times fasterâ.)
[1] Even latency changes are best described in terms of throughput.
how can lantency changes be described in terms of throughput? you can have completely different latencies with the same throughput due to different choices of pipelining, no?
The same way: If you change latency from 10s to 1s, it's ten times as fast in terms of latency (or 900% faster, if you will); earlier, you could do 0.1 things/second, and now you can do 1.0 thing/second. The only real difference between latency and throughput is whether you can overlap work instances or not, you can use the same language to talk about their changes.
You obviously cannot _calculate_ latency from throughput or vice versa (except of course that one bounds the other); they are different values. But both can use the (very simple, very intuitive) framework of ânumber of stuffs per time unitâ.
Now where things become ugly is when you talk about âN% _slower_â, which is also useful but gets very quickly ambiguous and problematic when N is not close to zero.
The problem is that âfasterâ and âslowerâ compare speed, which is x units per y time. But here, weâre only comparing time. The semi-legit interpretation is âif one unit takes 22.2ms, how many units can I complete in 35.6msâ, and actually the answer to that is indeed the 1.6 number they mention.
Itâs not sensationalist, itâs just a bit sloppy with the word âfasterâ.
1.6x faster is completely acceptable in natural language.
acceptable if you don't care about misunderstandings
More importantly, WHY is mysql GROUP BY still broken as default? MySQL is the PHP of databases, but it seems they have no plan to fix bugs.
MySQL GROUP BY is not broken by default (ONLY_FULL_GROUP_BY, the standards-conforming choice, is default for a decade or more). But there is basically no team left on the MySQL optimizer, after a long series of layoffs; it's gone from something like 40 to 3, give or take. Try Postgres :-)
You know, I have a hard time seeing how it can be cheaper to run a dataset across a wire rather than just send its grouped results. That's really the problem. I mean, who cares if it takes 100ns to group by on a box when it will take 10 times much for the data to get to it and back.
The fastest possible group by is in sequential memory on that box, fetched from an nvme.
So, when we do group by partitioning, we're really saying, put the groups themselves on different boxes, and group the group by. If it were some utter megaset, the place to put it is on a mainframe, which is basically created for purposes of group by on ridiculous datasets.
At least they have useful technical information in it. Personally really like reading these blog posts.
Measurements and context is so situational that it would be useless anyway imo.
It is not like any DB company is writing blog posts with benchmarks compared to hardware limits. DuckDB had blog posts that has sentences like "You are doing it wrong if you are not using HTTP api on your database in 2026", last time I saw their blog.
I also think object storage is bad in this case. And rust is no a no-no for a database project like this, because it is not ergonomic to do static allocation and arena allocators in rust. And parquet is trash for performance because of many reasons like supported compression formats etc.
> And rust is no a no-no for a database project like this, because it is not ergonomic to do static allocation and arena allocators in rust.
I'm not sure ergonomics matter that much these days, when your agent can write the tedious bits?
You're not wrong: if you know how much memory you need for a single query and can keep the data in attached SSD (so ephemeral SSD on AWS), scaling vertically will give you the fastest execution time.
However horizontal scaling has advantages in that it's easier to scale up and down incrementally. It gives you more aggregate network bandwidth for accessing disaggregated data. Disaggregation allows for independent scaling of compute and storage. Losing a single machine has a marginal impact on the cluster, which can enable you to use cheaper spot resources.
Basically, it's often easier to operate a system designed around horizontal scaling if you need resiliency.
group by is useful with other aggregates like sum, max, and so on.