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.
ClickHouse gets lazier and faster: Introducing lazy materialization
21–30 of 130 posts
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#22Unrelated 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…
> I guess sorting 150 million integers in 70ms shouldn't be surprising. I find sorting 150M integers at all to be surprising. The query asks for finding the top 3 elements and returning those elements, sorted. This can be done trivially by keeping the best three found so far and scanning the list. This should operate at nearly the speed of memory and use effectively zero additional storage. I don’t know whether Click…
Maybe they do have that optimization and that explains the 3.59 MiB peak memory usage for ~600MB of integers.
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#23Maybe I'm too inexperienced in this field but reading the mechanism I think this would be an obvious optimisation. Is it not? But credit where it is due, obviously clickhouse is an industry leader.
Obvious solutions are often hard to do right. I bet the code that was needed to pull this off is either very complex or took a long time to write (and test). Or both.
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#24Wonder how well this propagates down to subqueries/CTE's
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#25Unrelated 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…
Slow VMs on overprovisioned cloud hosts which cost as much per month as a dedicated box per year have broken a generation of engineers. You could host so much from your macbook. The average HN startup could be hosted on a $200 minipc from a closet for the first couple of years if not more - and I'm talking expensive here for the extra RAM you want to not restart every hour when you have a memory leak.
Raw compute wise, you're almost right (almost because real cloud hosts aren't overprovisioned, you get the full CPU/memory/disk reserved for you).
But you actually need more than compute. You might need a database, cache, message broker, scheduler, to send emails, and a million other things you can always DIY with FOSS software, but take time. If you have more money than time, get off the shelf services that provide those with guarantees and maintenance; if not, the DIY route is also great for learning.
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#26Earlier 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...
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.
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#27Earlier quoted context omitted.
To be fair to quickselect, I can imagine a lazy data processing framework having a concept of a lazily sorted data column where the actual data has been materialized but it’s not in sorted order yet. Then someone does “LIMIT k” to it, and the framework can go to town with quickselect. As noted a couple times in this thread, there are all kinds of tradeoffs here, and I can’t imagine quickselect being even close to com…
Yeah, obviously I wouldn't bother with a heap for k=3. A heap has good compactness but poor locality, so I guess it wouldn't perform well out of (some level of) cache.
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 (like quickselect).
As a minor optimisation, you can only add items to your buffer, if they improve on the worst element in your buffer (or when you haven't hit k elements in your buffer, yet).
As an empirical question, you can also experiment with the size of the buffer. Theoretically any multiple of k will do (even 1.1*k or so), but in practice they give you different constant factors for space and time.
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#28[deleted]
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#29I 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.
Same here. I come from a strong Postgres and Microsoft SQL Server background and I was able to get up to speed with it, ingesting real data from text files, in an afternoon. I was really impressed with the docs as well as the performance of the software.
Re: ClickHouse gets lazier and faster: Introducing lazy materialization
#30can we take the "packing your luggage" analogy and only pack the things we actually use in the trip and apply that to clickhouse?