Posted by ntur1337 5 days ago
SELECT item_id, COUNT(*) AS c
FROM orders
GROUP BY item_id
ORDER BY c DESC
LIMIT 10;
They are already summing these counts _before applying their optimization_.
From what I see this is the "base" algorithm:
1. Get the data from disk, which will be partitioned by storage key.
2. Parallelizing by storage key, aggregate each partition according to the grouping key. The result are many partitions by grouping key holding partially aggregated data.
3. Parallelizing by grouping key, merge the partially aggregated results. The results are fully aggregated partitions (each item is present in one and only one partition)
4. Merge these partitions into a global list of aggregated results
5. Pick the top K of this list
1. Get the data from disk, which will be partitioned by storage key
2. Partition the data by grouping key (without aggregating it)
3. In parallel, aggregate each partition
4. Take the top K from each partition
5. Take the top K overall
https://benchmark.clickhouse.com/#system=+liH|Hoaus|DcD|ucqs...
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, run a single query across multiple nodes, 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
(And I'm unclear what will happen given the Amazon situation)
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.
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.
I'm not sure ergonomics matter that much these days, when your agent can write the tedious bits?
It is not just ergonomics but the point of using rust is to have solid abstraction and a lot of high quality libraries to use.
If you are doing static allocation + arenas then you can't use nice abstraction, you can't use libraries, you have to write a ton of unsafe code because of these reasons. Then using rust is actually working against you so there is no point in using it.
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.
Can't we really keep the IT without breaking news/we are all gonna die/sensationalist way of passing news?
[1] Even latency changes are best described in terms of throughput.
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.
It still works with speed as the measurement (not as "zero point nine" but just "nine"), you just have to inverse the relationship. Going from 10s to 1s can still be described as "9 times faster" but it's because, less intuitively as you say, "faster" is comparing the 1s to the 10s (as in, "9(1s) plus 1s is 10s") rather than the other way around ("9(10s) plus 10s is 1s" is obviously false). This works because we know that 1s is less time than 10s; "faster" inverses the numerical relationship for time because increasing it inherently means "slower". Indeed, the difference between them is 9x where x = 1. And 10 to 0.1 is 99 times faster because 99x where x = 0.1 is 9.9 (measured by throughput, assuming only the rate changed, it's also 99x more things and 100x as many things).
(Not that I think you don't know all this, just nitpicking, really.)
It’s not sensationalist, it’s just a bit sloppy with the word “faster”.