ClickHouse gets lazier and faster: Introducing lazy materialization

Post by apwell23 » Wed, Apr 23, 2025, 3:43 AM UTC

memset wrote: post #64

dangoodmanUT wrote: post #46

God clickhouse is such great software, if it only it was as ergonomic as duckdb, and management wasn't doing some questionable things (deleting references to competitors in GH issues, weird legal letters, etc.) The CH contributors are really stellar, from multiple companies (Altinity, Tinybird, Cloudflare, ClickHouse)

chdb and clickhouse-local is nearly as ergonomic as duckdb with all of the features of ch. duckdb has unfortunately been leaning away from pure oss - the ui they released is entirely hosted on motherduck’s servers (which, while an awesome project, makes me feel like the project will be cannibalized by a proprietary extensions.)

Post by codedokode » Wed, Apr 23, 2025, 4:27 AM UTC

ww520 wrote: post #57

simonw wrote: post #2

Unrelated to the new materialization option, this caught my eye: "this query sorts all 150 million values in the helpful_votes column (which isn’t part of the table’s sort key) and returns the top 3, in just 70 milliseconds cold (with the OS filesystem cache cleared beforehand) and a processing throughput of 2.15 billion rows/s" I clearly need to update my mental model of what might be a slow query against modern har…

Let's do a back of the envelope calculation. 150M u32 integers are 600MB. Modern SSD can do 14,000MB/s sequential read [1]. So reading 600MB takes about 600MB / 14,000MB/s = 43ms. Memory like DDR4 can do 25GB/s [2]. It can go over 600MB in 600MB / 25,000MB/s = 24ms. L1/L2 can do 1TB/s [3]. There're 32 CPU's, so it's roughly 32TB/s of L1/L2 bandwidth. 600MB can be processed by 32TB/s in 0.018ms. With 3ms budget, they…

Post by porridgeraisin » Wed, Apr 23, 2025, 5:01 AM UTC

senderista wrote: post #37

eru wrote: post #27

Earlier quoted context omitted.

So quickselect needs multiple passes, and the heap needs O(n log k) time to find the top k elements of n elements total. However, you can find the top k elements in O(n) time and O(k) space in a single pass. One simple way: you keep a buffer of up to 2*k elements. You scan your stream of n items one by one. Whenever your buffer gets full, you pare it back down to k elements with your favourite selection algorithm (li…

How do you efficiently track the "worst element" without something like a max-heap? But yeah, this is a fun algorithm. I think I've seen it before but can't place it, do you remember where you came across it?

  if x > worst then worst = x

Post by kwillets » Wed, Apr 23, 2025, 5:21 AM UTC

Post by beoberha » Wed, Apr 23, 2025, 5:42 AM UTC

vjerancrnjak wrote: post #53

apavlo wrote: post #43

Earlier quoted context omitted.

> It's quite amazing how a db like this shows that all of those row-based dbs are doing something wrong They're not "doing something wrong". They are designed differently for different target workloads. Row-based -> OLTP -> "Fetch the entire records from order table where user_id = XYZ" Column-based -> OLAP -> "Compute the total amount of orders from the order table grouped by month/year"

Filtering by user id would also be trivially fast. It’s transactions mostly that make things slow. Like various isolation levels, failures if stale data was read in a transaction etc. I understand the difference, just a shame there’s nothing close to read or write rate , even on an index structure that has a copy of the columns. I’m aware that similar partitioning is available and that improves write and read rate bu…