ClickHouse gets lazier and faster: Introducing lazy materialization

This optimization <i>should</i> provide dramatic speed-ups when taking random samples from massive data sets, especially when the wanted columns can contain large values. That&#x27;s because the basic SQL recipe relies on a LIMIT clause to determine which rows are in the sample (see query below), and this new optimization promises to defer reading the big columns until the LIMIT clause has filtered the data set down to a tiny number of lucky rows.<p><pre><code> SELECT * FROM Population WHERE weight &gt; 0 ORDER BY -LN(1.0 - RANDOM()) &#x2F; weight LIMIT 100 -- Sample size. </code></pre> Can anyone from ClickHouse verify that the lazy-materialization optimization speeds up queries like this one? (I want to make sure the randomization in the ORDER BY clause doesn&#x27;t prevent the optimization.)

I really like Clickhouse. Discovered it recently, and man, it&#x27;s such a breath of fresh air compared to suboptimal solutions I used for analytics. It&#x27;s so fast and the CLI is also a joy to work with.

Unrelated to the new materialization option, this caught my eye:<p>&quot;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&#x2F;s&quot;<p>I clearly need to update my mental model of what might be a slow query against modern hardware and software. Looks like that&#x27;s so fast because in a columnar database it only has to load that 150 million value column. I guess sorting 150 million integers in 70ms shouldn&#x27;t be surprising.<p>(Also &quot;Peak memory usage: 3.59 MiB&quot; for that? Nice.)<p>This is a really great article - very clearly explained, good diagrams, I learned a bunch from it.

Late Materialization, 19 years later.<p><a href="https:&#x2F;&#x2F;dspace.mit.edu&#x2F;bitstream&#x2F;handle&#x2F;1721.1&#x2F;34929&#x2F;MIT-CSAIL-TR-2006-078.pdf;sequence=1" rel="nofollow">https:&#x2F;&#x2F;dspace.mit.edu&#x2F;bitstream&#x2F;handle&#x2F;1721.1&#x2F;34929&#x2F;MIT-CSA...</a>

IMHO if ClickHouse had Windows native release that does not need WSL or a Linux virtual machine it would be more popular than DuckDB. I remember for years MySQL being way more popular than PostgreSQL. One of the reasons being MySQL had a Windows installer.

Reminder clickhouse can be optionally embedded, you don&#x27;t need to reach for Duck just because of hype (it&#x27;s buggy as hell everytime I tried it).<p><a href="https:&#x2F;&#x2F;clickhouse.com&#x2F;blog&#x2F;chdb-embedded-clickhouse-rocket-engine-on-a-bicycle" rel="nofollow">https:&#x2F;&#x2F;clickhouse.com&#x2F;blog&#x2F;chdb-embedded-clickhouse-rocket-...</a>

Clickhouse is a masterpiece of modern engineering with absolute attention to performance.

&gt;Despite the airport drama, I’m still set on that beach holiday, and that means loading my eReader with only the best.<p>What a nice touch. Technical information and diagrams in this were top notch, but the fact there was also some kind of narrative threaded in really put it over the top for me.

Has anyone compared ClickHouse and StarRocks[0]? Join performance seems a lot better on StarRocks a few months ago but I&#x27;m not sure if that still holds true.<p>[0] <a href="https:&#x2F;&#x2F;www.starrocks.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.starrocks.io&#x2F;</a>

It&#x27;s quite amazing how a db like this shows that all of those row-based dbs are doing something wrong, they can&#x27;t even approach these speeds with btree index structures. I know they like transactions more than Clickhouse, but it&#x27;s just amazing to see how fast modern machines are, billions of rows per second.<p>I&#x27;m pretty sure they did not even bother to properly compress the dataset, with some tweaking, could have probably been much smaller than 30GBs. The speed shows that reading the data is slower than decompressing it.<p>Reminds me of that Cloudflare article where they had a similar idea about encryption being free (slower to read than to decrypt) and finding a bug, that when fixed, materialized this behavior.<p>The compute engine (chdb) is a wonder to use.

Maybe I&#x27;m too inexperienced in this field but reading the mechanism I think this would be an obvious optimisation. Is it not?<p>But credit where it is due, obviously clickhouse is an industry leader.

Wonder how well this propagates down to subqueries&#x2F;CTE&#x27;s

Whats up with these unscrollable websites? i dont get it. i scroll down a bit and it jumps up making it impossible to use.

is apache druid still a player in this space ? Never seem to hear about it anymore. why would someone choose it over clickhouse?

That’s an awesome change. Will that also work for limit offset queries?

can we take the &quot;packing your luggage&quot; analogy and only pack the things we actually use in the trip and apply that to clickhouse?

Thought this was Clickhole.com and was waiting for the payoff to the joke

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

We adopted ClickHouse ~4 years ago. We COULD have stayed on just Postgres. With a lot of bells, whistles, aggregation, denormalisation, aggressive retention limits and job queues etc. we could have gotten acceptable response times for our interactive dashboard.<p>But we chose ClickHouse and now we just pump in data with little to no optimization.