Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

ClickHouse gets lazier and faster: Introducing lazy materialization

clickhouse.com

Post reply on HN
Print view View original

81–90 of 130 posts

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#81

Post by rastignack »

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.)

ClickHouse too. Their sharedmergetree is not open source at all. It makes ClickHouse OSS obsolete design-wise. Shame.

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#82

Post by eru »

senderista wrote: post #15
Akronymus wrote: post #14

Earlier quoted context omitted.

My failure was misreading it as most common k rather than max k.

Most common k is super-interesting because it can't be solved in one pass in constant space! https://en.wikipedia.org/wiki/Streaming_algorithm#Frequent_e...

What you are quoting solves a very different problem. It doesn't give you the most common k (in general).

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#83

Post by eru »

3np wrote: post #26
senderista wrote: post #15

Earlier quoted context omitted.

Most common k is super-interesting because it can't be solved in one pass in constant space! https://en.wikipedia.org/wiki/Streaming_algorithm#Frequent_e...

Why is that interesting? Intuitively a worst-case could be a stream of n-1 unique elements out of n with the duplicate at the end, so there is no way around O(n) space. Any element could be the most common so you must keep them all.

The algorithms on the Wikipedia page quoted actually solve a different problem. And they can do that in constant space.

So if someone tells you that one item in the stream is repeated so often that it occurs at least p% of the time (say 10%), then these algorithms can find such an element. But eg if they are multiple elements that occur more than p% of the time, they are not guaranteed to give you the one that occurs the most often. Nor are they guaranteed to give you any meaningful output, if the assumption is violated and no element occurs at least p% of the time.

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#84

Post by codedokode »

mmsimanga wrote: post #45

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.

I was under impression that servers and databases generally run on Linux though.

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#85

Post by codedokode »

simonw wrote: post #51
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)

They have an interesting version that's packaged a bit like DuckDB - you can even "pip install" it: https://github.com/chdb-io/chdb

What is the use case for an embedded terabyte-scale database, by the way?

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#86

Post by geysersam »

tschreiber wrote: post #5
tmoertel wrote: post #3

This optimization should provide dramatic speed-ups when taking random samples from massive data sets, especially when the wanted columns can contain large values. That'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 nu…

Verified: EXPLAIN plan actions = 1 SELECT * FROM amazon.amazon_reviews WHERE helpful_votes > 0 ORDER BY -log(1 - (rand() / 4294967296.0)) / helpful_votes LIMIT 3 Lazily read columns: review_body, review_headline, verified_purchase, vine, total_votes, marketplace, star_rating, product_category, customer_id, product_title, product_id, product_parent, review_date, review_id Note that there is a setting query_plan_max_li…

Sorry if this question exposes my naivety, why such a low default limit? What drawback does lazy materialization have that makes it good to have such a low limit?

Do you know any example query where lazy materialization is detrimental to performance?

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#87

Post by AYBABTME »

auxten wrote: post #63
AYBABTME wrote: post #52

Earlier quoted context omitted.

They don't do static builds AFAICT, which would make it a real competitor to DuckDB.

chDB author here, You are right, we have not made a static libchDB. BTW, I guess you are a golang developer?

Correct! Would love to have the Go package come as a single dependency without having to distribute `.so` files. That's what's stopping me from using `chDB` now instead of DuckDB. Being able to use chDB in a static manner would also help deepen my usage of the Clickhouse server. Right now the Clickhouse side of my project is lagging behind the DuckDB one because of this.

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#89

Post by nasretdinov »

geysersam wrote: post #86
tschreiber wrote: post #5

Earlier quoted context omitted.

Verified: EXPLAIN plan actions = 1 SELECT * FROM amazon.amazon_reviews WHERE helpful_votes > 0 ORDER BY -log(1 - (rand() / 4294967296.0)) / helpful_votes LIMIT 3 Lazily read columns: review_body, review_headline, verified_purchase, vine, total_votes, marketplace, star_rating, product_category, customer_id, product_title, product_id, product_parent, review_date, review_id Note that there is a setting query_plan_max_li…

Sorry if this question exposes my naivety, why such a low default limit? What drawback does lazy materialization have that makes it good to have such a low limit? Do you know any example query where lazy materialization is detrimental to performance?

My understanding is that with higher limit values you may end up doing lots of random I/O (for each granule the order in which you read it would be much less predictable than when ClickHouse normally reads it sequentially), essentially one I/O operation per LIMIT value. So larger default values would only be beneficial in pathological examples given in the article, but much less so in "real world".

Re: ClickHouse gets lazier and faster: Introducing lazy materialization

#90

Post by nasretdinov »

lukaslalinsky wrote: post #80
jurgenkesker wrote: post #21

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

I always dismissed ClickHouse, because it's all super low level. Building a reliable system out of it, requires a lot of internal knowledge. This is the only DB I know, where you will have to deal with actual files on disk, in case of problems. However, I managed to look besides that, and oh-my-god it is so fast. It's like the tool is optimized for raw speed and whatever you do with it is up for you.

Yeah ClickHouse does feel like adult LEGO to me too: it lets you design your data structures and data storage layout, but doesn't force you to implement everything else. If you work on a large enough scale that's exactly what you want from a system usually
Post reply on HN