8 ms·

ClickHouse gets lazier and faster: Introducing lazy materialization

366 points by tbragin 1y ago

simonw 1y ago
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 hardware and software. Looks like that'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't be surprising. (Also "Peak memory usage: 3.59 MiB" for that? Nice.) This is a really great article - very clearly explained, good diagrams, I learned a bunch from it.
- amluto 1y ago
  > 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 Clickhouse does this optimization, but I didn’t see it mentioned. Generically, one can find the kth best of n elements in time O(n): https://en.m.wikipedia.org/wiki/Selection_algorithm https://en.m.wikipedia.org/wiki/Selection_algorithm And one can scan again to find the top k, plus some extra if the kth best wasn’t unique, but that issue is manageable and, I think, adds at most a factor of 2 overhead if one is careful (collect up to k elements that compare equal to the kth best and collect up to k that are better than it). Total complexity is O(n) if you don’t need the result sorted or O(n + k log k) if you do. If you’re not allowed to mutate the input (which probably applies to Clickhouse-style massive streaming reads), you can collect the top k in a separate data structure, and straightforward implementations are O(n log k). I wouldn’t be surprised if using a fancy heap or taking advantage of the data being integers with smallish numbers of bits does better, but I haven’t tried to find a solution or disprove the existence of one.
  - Akronymus 1y ago
    > This can be done trivially by keeping the best three found so far and scanning the list. That doesnt seem to guarantee correctness. If you dont track all of the unique values, at least, you could be throwing away one of the most common values. The wiki entry seems to be specifically about the smallest, rather than largest values.
    - recursive 1y ago
      What? The algorithm is completely symmetrical with respect to smallest or largest, and fully correct and general. I don't understand the problem with unique values. Could you provide a minimal input demonstrating the issue?
      - Akronymus 1y ago
        I cant because I completely misread the wiki article before commenting and have now read it more carefully and realized I was wrong. Specifically I went in thinking about top 3 most common value.
    - datadrivenangel 1y ago
      With an equality that returns true/false, this guarantees correctness. If there can be 3 best/biggest/smallest values, this technique works.
    - senderista 1y ago
      The max-heap algorithm alluded to above is correct. You fill it with the first k values scanned, then peek at the max element for each subsequent value. If the current value is smaller than the max element, you evict the max element and insert the new element. This streaming top-k algorithm is ubiquitous in both leetcode interviews and applications. (The standard quickselect top-k algorithm is not useful in the streaming context because it requires random access and in-place mutation.)
      - Akronymus 1y ago
- deleted 1y ago
  [deleted]
- baq 1y ago
  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.
  - sofixa 1y ago
    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.
    - baq 1y ago
      My point is all of this can be hosted on a single bare metal box, a small one at that! We used to do just that back in mid naughts and computers only got faster. Half of those cloud services are preconfigured FOSS derivatives behind the scenes anyway (probably…)
  - rfoo 1y ago
    > so much from your macbook At least on cloud I can actually have hundreds of GiBs of RAM. If I want this on my Macbook it's even more expensive than my cloud bill.
    - baq 1y ago
      You can, but if you need it you’re not searching for a product market fit anymore.
    - nasretdinov 1y ago
      Strangely I've found inverse to be true: many backend technologies are actually quite good with memory management and often require as little as a few GiB of RAM or even less to serve production traffic. Often a single IDE consumes more RAM than a production Go binary that serves thousands of requests per second for example
- ww520 1y ago
  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 can process the 600MB data 166 times. The rank selection algorithms like QuickSelect and Floyd-Rivest have O(N) complexity. It's entirely possible to process 600MB in 70ms. [1] https://www.tomshardware.com/features/ssd-benchmarks-hierarchy https://www.tomshardware.com/features/ssd-benchmarks-hierarc... [2] https://www.transcend-info.com/Support/FAQ-292 https://www.transcend-info.com/Support/FAQ-292 [3] https://www.intel.com/content/www/us/en/developer/articles/technical/memory-performance-in-a-nutshell.html https://www.intel.com/content/www/us/en/developer/articles/t...
  - codedokode 1y ago
    They mentioned that they use 125 MiB/s SSD. However, one can notice that the column seems to contain only about 47500 unique values. Probably there are many reviews with zero or one votes. This column is probably stored compressed so it can be loaded much faster.
    - ww520 1y ago
      That’s true. With such a small data domain, there would be a lot repeated numbers in the 160M values, leading to highly compressible data.
      - codedokode 1y ago
        I found in the article that the column uses 70 Mb of storage. if it was sorted (i.e. if it was an index) it would take even much less space. I don't understand though how they loaded 70 Mb of data with 125 MiB/s SSD in 70 ms.
        valyala 1y ago
        There is no need in loading data block, which has no rows with column values, which might be included into the final set of rows. If every column in every granule has a header containing the minimum and the maximum value seen in the granule, then ClickHouse can read and check only the column header per every granule, without the need to read the column data.
- skeptrune 1y ago
  Strong and up to date intuition on "slow vs. fast" queries is an underrated software engineering skill. Reading blogs like this one is worth it just for that alone.
tmoertel 1y ago
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 number of lucky rows. SELECT * FROM Population WHERE weight > 0 ORDER BY -LN(1.0 - RANDOM()) / weight LIMIT 100 -- Sample size. 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't prevent the optimization.)
- tschreiber 1y ago
  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_limit_for_lazy_materialization (default value 10) that controls the max n for which lm kicks in for LIMIT n.
  - tmoertel 1y ago
    Awesome! Thanks for checking :-)
  - geysersam 1y ago
    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?
    - nasretdinov 1y ago
      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".
simianwords 1y ago
Maybe 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.
- ahofmann 1y ago
  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.
- ryanworl 1y ago
  This is a well-known class of optimization and the literature term is “late materialization”. It is a large set of strategies including this one. Late materialization is about as old as column stores themselves.
jurgenkesker 1y ago
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.
- EvanAnderson 1y ago
  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.
  - osigurdson 1y ago
    Having a SQL like syntax where everything feels like a normal DB helps a lot I think. Of course, it works very differently behind the scenes but not having to learn a bunch of new things just to use a new data model is a good approach. I get why some create new dialects and languages as that way there is less ambiguity and therefore harder to use incorrectly but I think ClickHouse made the right tradeoffs here.
- theLiminator 1y ago
  How does it compare to duckdb and/or polars?
  - thenaturalist 1y ago
    This is very much an active space, so the half-life of in depth analyses is limited, but one of the best write ups from about 1.5 years ago is this one: https://bicortex.com/duckdb-vs-clickhouse-performance-comparison-for-structured-data-serialization-and-in-memory-tpc-ds-queries-execution/ https://bicortex.com/duckdb-vs-clickhouse-performance-compar...
  - nasretdinov 1y ago
    In my understanding DuckDB doesn't have its own optimised storage that can accept writes (in a sense that ClickHouse does, where it's native storage format gives you best performance), and instead relies on e.g. reading data from Parquet and other formats. That makes sense for an embedded analytics engine on top of existing files, but might be a problem if you wanted to use DuckDB e.g. for real-time analytics where the inserted data needs to be available for querying in a few seconds after it's been inserted. ClickHouse was designed for the latter use case, but at a cost of being a full-fledged standalone service by design. There are embedded versions of ClickHouse, but they are much bulkier and generally less ergonomic to use (although that's a personal preference)
ohnoesjmr 1y ago
Wonder how well this propagates down to subqueries/CTE's
deleted 1y ago
[deleted]
meta_ai_x 1y ago
can we take the "packing your luggage" analogy and only pack the things we actually use in the trip and apply that to clickhouse?
- nasretdinov 1y ago
  Are you implying that ClickHouse is too large? You can build ClickHouse with most features disabled, it must be much smaller if you do that.
Onavo 1y ago
Reminder clickhouse can be optionally embedded, you don't need to reach for Duck just because of hype (it's buggy as hell everytime I tried it). https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-engine-on-a-bicycle https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-...
- sirfz 1y ago
  Chdb is awesome but so is duckdb
justmarc 1y ago
Clickhouse is a masterpiece of modern engineering with absolute attention to performance.
vjerancrnjak 1y ago
It's quite amazing how a db like this shows that all of those row-based dbs are doing something wrong, they can't even approach these speeds with btree index structures. I know they like transactions more than Clickhouse, but it's just amazing to see how fast modern machines are, billions of rows per second. I'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. 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. The compute engine (chdb) is a wonder to use.
- apavlo 1y ago
  > 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"
  - vjerancrnjak 1y ago
    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 but not to these magnitudes .
    - FridgeSeal 1y ago
      Some of the “new SQL” hybrid (HTAP, hybrid transaction-analytical processing) databases might be of interest to you. TiDB is the main example off the top of my head.
    - beoberha 1y ago
mmsimanga 1y ago
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.
- skeptrune 1y ago
  Is Clickhouse not already more popular than DuckDB?
  - nasretdinov 1y ago
    28k stars on GitHub for DuckDB vs 40k for ClickHouse - pretty close. But, anecdotally, here on HN DuckDB gets mentioned much more often
- codedokode 1y ago
  I was under impression that servers and databases generally run on Linux though.
  - mmsimanga 1y ago
    Windows still runs on 71% of the desktop and laptops [1]. In my experience a good number of applications start life on simple desktops and then graduate to servers if they are successful. I work in the field of analytics. I have a locked down Windows desktop and I have been able to try out all the other databases such as MySQL, MariaDB, PostgreSQL and DuckDB because they have windows installers or portable apps. I haven't been able to try out ClickHouse. This is my experience and YMMV. [1]https://en.wikipedia.org/wiki/Usage_share_of_operating_systems https://en.wikipedia.org/wiki/Usage_share_of_operating_syste...
    - anentropic 1y ago
      surely you have Docker though?
    - codedokode 1y ago
dangoodmanUT 1y ago
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)
- simonw 1y ago
  They have an interesting version that's packaged a bit like DuckDB - you can even "pip install" it: https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb
  - AYBABTME 1y ago
    They don't do static builds AFAICT, which would make it a real competitor to DuckDB.
    - auxten 1y ago
      chDB author here, You are right, we have not made a static libchDB. BTW, I guess you are a golang developer?
      - AYBABTME 1y ago
        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.
        ryadh 1y ago
        That's great feedback, thank you! I just added your comment to the GH issue: https://github.com/chdb-io/chdb/issues/101#issuecomment-2824107674 https://github.com/chdb-io/chdb/issues/101#issuecomment-2824... Ps. I work for ClickHouse
skeptrune 1y ago
>Despite the airport drama, I’m still set on that beach holiday, and that means loading my eReader with only the best. 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.
apwell23 1y ago
is apache druid still a player in this space ? Never seem to hear about it anymore. why would someone choose it over clickhouse?
- anentropic 1y ago
  or Apache Doris ...I'm also curious
higeorge13 1y ago
That’s an awesome change. Will that also work for limit offset queries?
- devcrafter 1y ago
  Yes, it does work with limit offset as well
kwillets 1y ago
Late Materialization, 19 years later. https://dspace.mit.edu/bitstream/handle/1721.1/34929/MIT-CSAIL-TR-2006-078.pdf;sequence=1 https://dspace.mit.edu/bitstream/handle/1721.1/34929/MIT-CSA...
- ignoreusernames 1y ago
  Same thing with columnar/vectorized execution. It has been known for a long time that's the "correct" way to process data for olap workflows, but only became "mainstream" in the last few years(mostly due to arrow). It's awesome that clickhouse is adopting it now, but a shame that it's not standard on anything that does analytics processing.
  - kwillets 1y ago
    Nothing in C-store seems to have sunk in. In clickhouse's case I can forgive them since it was an open source, bootstrap type of project, and their cash infusion seems to be going into basic re-engineering, but in general slowly re-implementing Vertica seems like a flawed business model.
  - AlexClickHouse 1y ago
    ClickHouse predates Apache Arrow.
AndreKR 1y ago
[dead]
xiasongh 1y ago
Has anyone compared ClickHouse and StarRocks[0]? Join performance seems a lot better on StarRocks a few months ago but I'm not sure if that still holds true. [0] https://www.starrocks.io/ https://www.starrocks.io/
- fermuch 1y ago
  Yes! There is a benchmark on ClickBench: https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQiI6ZmFsc2UsIkFsbG95REIgKHR1bmVkKSI6ZmFsc2UsIkF0aGVuYSAocGFydGl0aW9uZWQpIjpmYWxzZSwiQXRoZW5hIChzaW5nbGUpIjpmYWxzZSwiQXVyb3JhIGZvciBNeVNRTCI6ZmFsc2UsIkF1cm9yYSBmb3IgUG9zdGdyZVNRTCI6ZmFsc2UsIkJpZ3F1ZXJ5IjpmYWxzZSwiQnlDb25pdHkiOmZhbHNlLCJCeXRlSG91c2UiOmZhbHNlLCJjaERCIChEYXRhRnJhbWUpIjpmYWxzZSwiY2hEQiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwiY2hEQiI6ZmFsc2UsIkNIWVQiOmZhbHNlLCJDaXR1cyI6ZmFsc2UsIkNsaWNrSG91c2UgQ2xvdWQgKGF3cykiOmZhbHNlLCJDbGlja0hvdXNlIENsb3VkIChhenVyZSkiOmZhbHNlLCJDbGlja0hvdXNlIENsb3VkIChnY3ApIjpmYWxzZSwiQ2xpY2tIb3VzZSAoZGF0YSBsYWtlLCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJDbGlja0hvdXNlIChkYXRhIGxha2UsIHNpbmdsZSkiOmZhbHNlLCJDbGlja0hvdXNlIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJDbGlja0hvdXNlIChQYXJxdWV0LCBzaW5nbGUpIjpmYWxzZSwiQ2xpY2tIb3VzZSAod2ViKSI6ZmFsc2UsIkNsaWNrSG91c2UiOnRydWUsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHR1bmVkLCBtZW1vcnkpIjpmYWxzZSwiQ2xvdWRiZXJyeSI6ZmFsc2UsIkNyYXRlREIgKHR1bmVkKSI6ZmFsc2UsIkNyYXRlREIiOmZhbHNlLCJDcnVuY2h5IEJyaWRnZSBmb3IgQW5hbHl0aWNzIChQYXJxdWV0KSI6ZmFsc2UsIkRhZnQgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkRhZnQgKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJEYXRhYmVuZCI6ZmFsc2UsIkRhdGFGdXNpb24gKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkRhdGFGdXNpb24gKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJBcGFjaGUgRG9yaXMiOmZhbHNlLCJEcmlsbCI6ZmFsc2UsIkRydWlkIjpmYWxzZSwiRHVja0RCIChEYXRhRnJhbWUpIjpmYWxzZSwiRHVja0RCIChtZW1vcnkpIjpmYWxzZSwiRHVja0RCIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJEdWNrREIiOmZhbHNlLCJFbGFzdGljc2VhcmNoIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR2xhcmVEQiI6ZmFsc2UsIkdyZWVucGx1bSI6ZmFsc2UsIkhlYXZ5QUkiOmZhbHNlLCJIeWRyYSI6ZmFsc2UsIlNhbGVzZm9yY2UgSHlwZXIgKFBhcnF1ZXQpIjpmYWxzZSwiU2FsZXNmb3JjZSBIeXBlciI6ZmFsc2UsIkluZm9icmlnaHQiOmZhbHNlLCJLaW5ldGljYSI6ZmFsc2UsIk1hcmlhREIgQ29sdW1uU3RvcmUiOmZhbHNlLCJNYXJpYURCIjpmYWxzZSwiTW9uZXREQiI6ZmFsc2UsIk1vbmdvREIiOmZhbHNlLCJNb3RoZXJEdWNrIjpmYWxzZSwiTXlTUUwgKE15SVNBTSkiOmZhbHNlLCJNeVNRTCI6ZmFsc2UsIk9jdG9TUUwiOmZhbHNlLCJPcHRlcnl4IjpmYWxzZSwiT3hsYSI6ZmFsc2UsIlBhbmRhcyAoRGF0YUZyYW1lKSI6ZmFsc2UsIlBhcmFkZURCIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJQYXJhZGVEQiAoUGFycXVldCwgc2luZ2xlKSI6ZmFsc2UsIlBhcnNlYWJsZSAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwicGdfZHVja2RiICh3aXRoIGluZGV4ZXMpIjpmYWxzZSwicGdfZHVja2RiIChNb3RoZXJEdWNrIGVuYWJsZWQpIjpmYWxzZSwicGdfZHVja2RiIjpmYWxzZSwicGdfZHVja2RiIChQYXJxdWV0KSI6ZmFsc2UsIlBvc3RncmVTUUwgd2l0aCBwZ19tb29uY2FrZSI6ZmFsc2UsInBncHJvX3RhbSAocGFycXVldCwgbG9jYWwgc3RvcmFnZSkiOmZhbHNlLCJwZ3Byb190YW0gKHBhcnF1ZXQsIGxvY2FsLCBwYXJhbGxlbCkiOmZhbHNlLCJwZ3Byb190YW0gKHBhcnF1ZXQsIGxvY2FsICsgY2FjaGUpIjpmYWxzZSwicGdwcm9fdGFtIChmZWF0aGVyLCBsb2NhbCArIGNhY2hlKSI6ZmFsc2UsIlBpbm90IjpmYWxzZSwiUG9sYXJzIChEYXRhRnJhbWUpIjpmYWxzZSwiUG9sYXJzIChQYXJxdWV0KSI6ZmFsc2UsIlBvc3RncmVTUUwgKHdpdGggaW5kZXhlcykiOmZhbHNlLCJQb3N0Z3JlU1FMIjpmYWxzZSwiUXVlc3REQiI6ZmFsc2UsIlJlZHNoaWZ0IjpmYWxzZSwiU2VsZWN0REIiOmZhbHNlLCJTaWdMZW5zIjpmYWxzZSwiU2luZ2xlU3RvcmUiOmZhbHNlLCJTbm93Zmxha2UiOmZhbHNlLCJTcGFyayI6ZmFsc2UsIlNRTGl0ZSI6ZmFsc2UsIlN0YXJSb2NrcyI6dHJ1ZSwiVGFibGVzcGFjZSI6ZmFsc2UsIlRlbWJvIE9MQVAgKGNvbHVtbmFyKSI6ZmFsc2UsIlRpbWVzY2FsZSBDbG91ZCI6ZmFsc2UsIlRpbWVzY2FsZURCIChubyBjb2x1bW5zdG9yZSkiOmZhbHNlLCJUaW1lc2NhbGVEQiI6ZmFsc2UsIlRpbnliaXJkIChGcmVlIFRyaWFsKSI6ZmFsc2UsIlVtYnJhIjpmYWxzZSwiVXJzYSI6ZmFsc2UsIlZpY3RvcmlhTG9ncyI6ZmFsc2UsIllEQiI6ZmFsc2V9LCJ0eXBlIjp7IkMiOnRydWUsImNvbHVtbi1vcmllbnRlZCI6dHJ1ZSwiUG9zdGdyZVNRTCBjb21wYXRpYmxlIjp0cnVlLCJtYW5hZ2VkIjp0cnVlLCJnY3AiOnRydWUsInN0YXRlbGVzcyI6dHJ1ZSwiSmF2YSI6dHJ1ZSwiQysrIjp0cnVlLCJNeVNRTCBjb21wYXRpYmxlIjp0cnVlLCJyb3ctb3JpZW50ZWQiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6dHJ1ZSwiZW1iZWRkZWQiOnRydWUsImRhdGFmcmFtZSI6dHJ1ZSwiWVRzYXVydXMiOnRydWUsImF3cyI6dHJ1ZSwiYXp1cmUiOnRydWUsImFuYWx5dGljYWwiOnRydWUsIlJ1c3QiOnRydWUsInNlYXJjaCI6dHJ1ZSwiZG9jdW1lbnQiOnRydWUsIkdvIjp0cnVlLCJzb21ld2hhdCBQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsInBhcnF1ZXQiOnRydWUsInRpbWUtc2VyaWVzIjp0cnVlLCJsb2dzIjp0cnVlLCJTaWdMZW5zIjp0cnVlLCJvYnNlcnZhYmlsaXR5Ijp0cnVlLCJkZWRpY2F0ZWQiOnRydWV9LCJtYWNoaW5lIjp7IjE2IHZDUFUgMTI4R0IiOnRydWUsIjggdkNQVSA2NEdCIjp0cnVlLCJzZXJ2ZXJsZXNzIjp0cnVlLCIxNmFjdSI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDUwMGdiIGdwMiI6dHJ1ZSwiTCI6dHJ1ZSwiTSI6dHJ1ZSwiUyI6dHJ1ZSwiWFMiOnRydWUsImM2YS5tZXRhbCwgNTAwZ2IgZ3AyIjp0cnVlLCIxMiB2Q1BVIDQ4R0IiOnRydWUsIjEwIHZDUFUgNDBHQiI6dHJ1ZSwiMTJHaUIsIDEgcmVwbGljYShzKSI6dHJ1ZSwiOEdpQiwgMSByZXBsaWNhKHMpIjp0cnVlLCIxMkdpQiwgMiByZXBsaWNhKHMpIjp0cnVlLCIxMjBHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMTZHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMjM2R2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjMyR2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjY0R2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjhHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMTJHaUIsIDMgcmVwbGljYShzKSI6dHJ1ZSwiMTIwR2lCLCAzIHJlcGxpY2EocykiOnRydWUsIjE2R2lCLCAzIHJlcGxpY2EocykiOnRydWUsIjIzNkdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCIzMkdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCI2NEdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCI4R2lCLCAzIHJlcGxpY2EocykiOnRydWUsImM1bi40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsIkFuYWx5dGljcy0yNTZHQiAoNjQgdkNvcmVzLCAyNTYgR0IpIjp0cnVlLCJjNS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsImM2YS40eGxhcmdlLCAxNTAwZ2IgZ3AyIjp0cnVlLCJYTCI6dHJ1ZSwiSnVtYm8iOnRydWUsIlB1bHNlIjp0cnVlLCJTdGFuZGFyZCI6dHJ1ZSwiMTYgdkNQVSAzMkdCIjp0cnVlLCJkYzIuOHhsYXJnZSI6dHJ1ZSwicmEzLjE2eGxhcmdlIjp0cnVlLCJyYTMuNHhsYXJnZSI6dHJ1ZSwicmEzLnhscGx1cyI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDcwMGdiIGdwMiI6dHJ1ZSwiUzIiOnRydWUsIlMyNCI6dHJ1ZSwiMlhMIjp0cnVlLCIzWEwiOnRydWUsIjRYTCI6dHJ1ZSwiTDEgLSAxNkNQVSAzMkdCIjp0cnVlLCJjNmEuNHhsYXJnZSwgNTAwZ2IgZ3AzIjp0cnVlLCIxNiB2Q1BVIDY0R0IiOnRydWUsIjQgdkNQVSAxNkdCIjp0cnVlLCI4IHZDUFUgMzJHQiI6dHJ1ZSwiNjQgdkNQVSAyNTZHQiI6dHJ1ZX0sImNsdXN0ZXJfc2l6ZSI6eyIxIjp0cnVlLCIyIjp0cnVlLCIzIjp0cnVlLCI0Ijp0cnVlLCI4Ijp0cnVlLCI5Ijp0cnVlLCIxNiI6dHJ1ZSwiMzIiOnRydWUsIjY0Ijp0cnVlLCIxMjgiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsInVuZGVmaW5lZCI6dHJ1ZX0sIm1ldHJpYyI6ImhvdCIsInF1ZXJpZXMiOlt0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlXX0= https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQi...
momono 1y ago
[dead]
tnolet 1y ago
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. But we chose ClickHouse and now we just pump in data with little to no optimization.
- nasretdinov 1y ago
  I imagine with Postgres there's also an option of using a plugin like Greenplum or something else, which may help to bridge the gap, but probably not to the level of ClickHouse.
  - tnolet 1y ago
    yes, we looked at Timescale also. But they were much younger then and Clickhouse was more mature. Clickhouse cloud did not exist yet. We now use a mix of Altinity and Cloud.
- xmodem 1y ago
  We migrated some analytics workloads from postgres to clickhouse last year, it's crazy how fast it is. It feels like alien technology from the future in comparison.
  - apwell23 1y ago
    are those like embedded analytics in the app or internal BI type workloads ?
    - tnolet 1y ago
      For us these are just metrics on customer facing dashboards inside our app. They are basically realtime and show p95, p99, avg. etc over time ranges. Our app can show this for 1000s of entities in one dashboard and that can eat up resources pretty quickly
- tucnak 1y ago
jangliss 1y ago
Thought this was Clickhole.com and was waiting for the payoff to the joke
curtisszmania 1y ago
[dead]
hexo 1y ago
Whats up with these unscrollable websites? i dont get it. i scroll down a bit and it jumps up making it impossible to use.

366 points by tbragin 1y ago

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 hardware and software. Looks like that'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't be surprising. (Also "Peak memory usage: 3.59 MiB" for that? Nice.) This is a really great article - very clearly explained, good diagrams, I learned a bunch from it.

amluto 1y ago
> 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 Clickhouse does this optimization, but I didn’t see it mentioned. Generically, one can find the kth best of n elements in time O(n): https://en.m.wikipedia.org/wiki/Selection_algorithm https://en.m.wikipedia.org/wiki/Selection_algorithm And one can scan again to find the top k, plus some extra if the kth best wasn’t unique, but that issue is manageable and, I think, adds at most a factor of 2 overhead if one is careful (collect up to k elements that compare equal to the kth best and collect up to k that are better than it). Total complexity is O(n) if you don’t need the result sorted or O(n + k log k) if you do. If you’re not allowed to mutate the input (which probably applies to Clickhouse-style massive streaming reads), you can collect the top k in a separate data structure, and straightforward implementations are O(n log k). I wouldn’t be surprised if using a fancy heap or taking advantage of the data being integers with smallish numbers of bits does better, but I haven’t tried to find a solution or disprove the existence of one.
- Akronymus 1y ago
  > This can be done trivially by keeping the best three found so far and scanning the list. That doesnt seem to guarantee correctness. If you dont track all of the unique values, at least, you could be throwing away one of the most common values. The wiki entry seems to be specifically about the smallest, rather than largest values.
  - recursive 1y ago
    What? The algorithm is completely symmetrical with respect to smallest or largest, and fully correct and general. I don't understand the problem with unique values. Could you provide a minimal input demonstrating the issue?
    - Akronymus 1y ago
      I cant because I completely misread the wiki article before commenting and have now read it more carefully and realized I was wrong. Specifically I went in thinking about top 3 most common value.
  - datadrivenangel 1y ago
    With an equality that returns true/false, this guarantees correctness. If there can be 3 best/biggest/smallest values, this technique works.
  - senderista 1y ago
    The max-heap algorithm alluded to above is correct. You fill it with the first k values scanned, then peek at the max element for each subsequent value. If the current value is smaller than the max element, you evict the max element and insert the new element. This streaming top-k algorithm is ubiquitous in both leetcode interviews and applications. (The standard quickselect top-k algorithm is not useful in the streaming context because it requires random access and in-place mutation.)
    - Akronymus 1y ago
deleted 1y ago
[deleted]
baq 1y ago
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.
- sofixa 1y ago
  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.
  - baq 1y ago
    My point is all of this can be hosted on a single bare metal box, a small one at that! We used to do just that back in mid naughts and computers only got faster. Half of those cloud services are preconfigured FOSS derivatives behind the scenes anyway (probably…)
- rfoo 1y ago
  > so much from your macbook At least on cloud I can actually have hundreds of GiBs of RAM. If I want this on my Macbook it's even more expensive than my cloud bill.
  - baq 1y ago
    You can, but if you need it you’re not searching for a product market fit anymore.
  - nasretdinov 1y ago
    Strangely I've found inverse to be true: many backend technologies are actually quite good with memory management and often require as little as a few GiB of RAM or even less to serve production traffic. Often a single IDE consumes more RAM than a production Go binary that serves thousands of requests per second for example
ww520 1y ago
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 can process the 600MB data 166 times. The rank selection algorithms like QuickSelect and Floyd-Rivest have O(N) complexity. It's entirely possible to process 600MB in 70ms. [1] https://www.tomshardware.com/features/ssd-benchmarks-hierarchy https://www.tomshardware.com/features/ssd-benchmarks-hierarc... [2] https://www.transcend-info.com/Support/FAQ-292 https://www.transcend-info.com/Support/FAQ-292 [3] https://www.intel.com/content/www/us/en/developer/articles/technical/memory-performance-in-a-nutshell.html https://www.intel.com/content/www/us/en/developer/articles/t...
- codedokode 1y ago
  They mentioned that they use 125 MiB/s SSD. However, one can notice that the column seems to contain only about 47500 unique values. Probably there are many reviews with zero or one votes. This column is probably stored compressed so it can be loaded much faster.
  - ww520 1y ago
    That’s true. With such a small data domain, there would be a lot repeated numbers in the 160M values, leading to highly compressible data.
    - codedokode 1y ago
      I found in the article that the column uses 70 Mb of storage. if it was sorted (i.e. if it was an index) it would take even much less space. I don't understand though how they loaded 70 Mb of data with 125 MiB/s SSD in 70 ms.
      - valyala 1y ago
        There is no need in loading data block, which has no rows with column values, which might be included into the final set of rows. If every column in every granule has a header containing the minimum and the maximum value seen in the granule, then ClickHouse can read and check only the column header per every granule, without the need to read the column data.
skeptrune 1y ago
Strong and up to date intuition on "slow vs. fast" queries is an underrated software engineering skill. Reading blogs like this one is worth it just for that alone.

tmoertel 1y ago

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 number of lucky rows. SELECT * FROM Population WHERE weight > 0 ORDER BY -LN(1.0 - RANDOM()) / weight LIMIT 100 -- Sample size. 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't prevent the optimization.)

tschreiber 1y ago
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_limit_for_lazy_materialization (default value 10) that controls the max n for which lm kicks in for LIMIT n.
- tmoertel 1y ago
  Awesome! Thanks for checking :-)
- geysersam 1y ago
  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?
  - nasretdinov 1y ago
    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".

simianwords 1y ago

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

ahofmann 1y ago
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.
ryanworl 1y ago
This is a well-known class of optimization and the literature term is “late materialization”. It is a large set of strategies including this one. Late materialization is about as old as column stores themselves.

jurgenkesker 1y ago

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.

EvanAnderson 1y ago
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.
- osigurdson 1y ago
  Having a SQL like syntax where everything feels like a normal DB helps a lot I think. Of course, it works very differently behind the scenes but not having to learn a bunch of new things just to use a new data model is a good approach. I get why some create new dialects and languages as that way there is less ambiguity and therefore harder to use incorrectly but I think ClickHouse made the right tradeoffs here.
theLiminator 1y ago
How does it compare to duckdb and/or polars?
- thenaturalist 1y ago
  This is very much an active space, so the half-life of in depth analyses is limited, but one of the best write ups from about 1.5 years ago is this one: https://bicortex.com/duckdb-vs-clickhouse-performance-comparison-for-structured-data-serialization-and-in-memory-tpc-ds-queries-execution/ https://bicortex.com/duckdb-vs-clickhouse-performance-compar...
- nasretdinov 1y ago
  In my understanding DuckDB doesn't have its own optimised storage that can accept writes (in a sense that ClickHouse does, where it's native storage format gives you best performance), and instead relies on e.g. reading data from Parquet and other formats. That makes sense for an embedded analytics engine on top of existing files, but might be a problem if you wanted to use DuckDB e.g. for real-time analytics where the inserted data needs to be available for querying in a few seconds after it's been inserted. ClickHouse was designed for the latter use case, but at a cost of being a full-fledged standalone service by design. There are embedded versions of ClickHouse, but they are much bulkier and generally less ergonomic to use (although that's a personal preference)

ohnoesjmr 1y ago

Wonder how well this propagates down to subqueries/CTE's

deleted 1y ago

[deleted]

meta_ai_x 1y ago

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

nasretdinov 1y ago
Are you implying that ClickHouse is too large? You can build ClickHouse with most features disabled, it must be much smaller if you do that.

Onavo 1y ago

Reminder clickhouse can be optionally embedded, you don't need to reach for Duck just because of hype (it's buggy as hell everytime I tried it). https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-engine-on-a-bicycle https://clickhouse.com/blog/chdb-embedded-clickhouse-rocket-...

sirfz 1y ago
Chdb is awesome but so is duckdb

justmarc 1y ago

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

vjerancrnjak 1y ago

It's quite amazing how a db like this shows that all of those row-based dbs are doing something wrong, they can't even approach these speeds with btree index structures. I know they like transactions more than Clickhouse, but it's just amazing to see how fast modern machines are, billions of rows per second. I'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. 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. The compute engine (chdb) is a wonder to use.

apavlo 1y ago
> 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"
- vjerancrnjak 1y ago
  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 but not to these magnitudes .
  - FridgeSeal 1y ago
    Some of the “new SQL” hybrid (HTAP, hybrid transaction-analytical processing) databases might be of interest to you. TiDB is the main example off the top of my head.
  - beoberha 1y ago

mmsimanga 1y ago

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.

skeptrune 1y ago
Is Clickhouse not already more popular than DuckDB?
- nasretdinov 1y ago
  28k stars on GitHub for DuckDB vs 40k for ClickHouse - pretty close. But, anecdotally, here on HN DuckDB gets mentioned much more often
codedokode 1y ago
I was under impression that servers and databases generally run on Linux though.
- mmsimanga 1y ago
  Windows still runs on 71% of the desktop and laptops [1]. In my experience a good number of applications start life on simple desktops and then graduate to servers if they are successful. I work in the field of analytics. I have a locked down Windows desktop and I have been able to try out all the other databases such as MySQL, MariaDB, PostgreSQL and DuckDB because they have windows installers or portable apps. I haven't been able to try out ClickHouse. This is my experience and YMMV. [1]https://en.wikipedia.org/wiki/Usage_share_of_operating_systems https://en.wikipedia.org/wiki/Usage_share_of_operating_syste...
  - anentropic 1y ago
    surely you have Docker though?
  - codedokode 1y ago

dangoodmanUT 1y ago

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)

simonw 1y ago
They have an interesting version that's packaged a bit like DuckDB - you can even "pip install" it: https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb
- AYBABTME 1y ago
  They don't do static builds AFAICT, which would make it a real competitor to DuckDB.
  - auxten 1y ago
    chDB author here, You are right, we have not made a static libchDB. BTW, I guess you are a golang developer?
    - AYBABTME 1y ago
      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.
      - ryadh 1y ago
        That's great feedback, thank you! I just added your comment to the GH issue: https://github.com/chdb-io/chdb/issues/101#issuecomment-2824107674 https://github.com/chdb-io/chdb/issues/101#issuecomment-2824... Ps. I work for ClickHouse

skeptrune 1y ago

>Despite the airport drama, I’m still set on that beach holiday, and that means loading my eReader with only the best. 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.

apwell23 1y ago

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

anentropic 1y ago
or Apache Doris ...I'm also curious

higeorge13 1y ago

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

devcrafter 1y ago
Yes, it does work with limit offset as well

kwillets 1y ago

Late Materialization, 19 years later. https://dspace.mit.edu/bitstream/handle/1721.1/34929/MIT-CSAIL-TR-2006-078.pdf;sequence=1 https://dspace.mit.edu/bitstream/handle/1721.1/34929/MIT-CSA...

ignoreusernames 1y ago
Same thing with columnar/vectorized execution. It has been known for a long time that's the "correct" way to process data for olap workflows, but only became "mainstream" in the last few years(mostly due to arrow). It's awesome that clickhouse is adopting it now, but a shame that it's not standard on anything that does analytics processing.
- kwillets 1y ago
  Nothing in C-store seems to have sunk in. In clickhouse's case I can forgive them since it was an open source, bootstrap type of project, and their cash infusion seems to be going into basic re-engineering, but in general slowly re-implementing Vertica seems like a flawed business model.
- AlexClickHouse 1y ago
  ClickHouse predates Apache Arrow.

AndreKR 1y ago

[dead]

xiasongh 1y ago

Has anyone compared ClickHouse and StarRocks[0]? Join performance seems a lot better on StarRocks a few months ago but I'm not sure if that still holds true. [0] https://www.starrocks.io/ https://www.starrocks.io/

fermuch 1y ago
Yes! There is a benchmark on ClickBench: https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQiI6ZmFsc2UsIkFsbG95REIgKHR1bmVkKSI6ZmFsc2UsIkF0aGVuYSAocGFydGl0aW9uZWQpIjpmYWxzZSwiQXRoZW5hIChzaW5nbGUpIjpmYWxzZSwiQXVyb3JhIGZvciBNeVNRTCI6ZmFsc2UsIkF1cm9yYSBmb3IgUG9zdGdyZVNRTCI6ZmFsc2UsIkJpZ3F1ZXJ5IjpmYWxzZSwiQnlDb25pdHkiOmZhbHNlLCJCeXRlSG91c2UiOmZhbHNlLCJjaERCIChEYXRhRnJhbWUpIjpmYWxzZSwiY2hEQiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwiY2hEQiI6ZmFsc2UsIkNIWVQiOmZhbHNlLCJDaXR1cyI6ZmFsc2UsIkNsaWNrSG91c2UgQ2xvdWQgKGF3cykiOmZhbHNlLCJDbGlja0hvdXNlIENsb3VkIChhenVyZSkiOmZhbHNlLCJDbGlja0hvdXNlIENsb3VkIChnY3ApIjpmYWxzZSwiQ2xpY2tIb3VzZSAoZGF0YSBsYWtlLCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJDbGlja0hvdXNlIChkYXRhIGxha2UsIHNpbmdsZSkiOmZhbHNlLCJDbGlja0hvdXNlIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJDbGlja0hvdXNlIChQYXJxdWV0LCBzaW5nbGUpIjpmYWxzZSwiQ2xpY2tIb3VzZSAod2ViKSI6ZmFsc2UsIkNsaWNrSG91c2UiOnRydWUsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHR1bmVkLCBtZW1vcnkpIjpmYWxzZSwiQ2xvdWRiZXJyeSI6ZmFsc2UsIkNyYXRlREIgKHR1bmVkKSI6ZmFsc2UsIkNyYXRlREIiOmZhbHNlLCJDcnVuY2h5IEJyaWRnZSBmb3IgQW5hbHl0aWNzIChQYXJxdWV0KSI6ZmFsc2UsIkRhZnQgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkRhZnQgKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJEYXRhYmVuZCI6ZmFsc2UsIkRhdGFGdXNpb24gKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkRhdGFGdXNpb24gKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJBcGFjaGUgRG9yaXMiOmZhbHNlLCJEcmlsbCI6ZmFsc2UsIkRydWlkIjpmYWxzZSwiRHVja0RCIChEYXRhRnJhbWUpIjpmYWxzZSwiRHVja0RCIChtZW1vcnkpIjpmYWxzZSwiRHVja0RCIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJEdWNrREIiOmZhbHNlLCJFbGFzdGljc2VhcmNoIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR2xhcmVEQiI6ZmFsc2UsIkdyZWVucGx1bSI6ZmFsc2UsIkhlYXZ5QUkiOmZhbHNlLCJIeWRyYSI6ZmFsc2UsIlNhbGVzZm9yY2UgSHlwZXIgKFBhcnF1ZXQpIjpmYWxzZSwiU2FsZXNmb3JjZSBIeXBlciI6ZmFsc2UsIkluZm9icmlnaHQiOmZhbHNlLCJLaW5ldGljYSI6ZmFsc2UsIk1hcmlhREIgQ29sdW1uU3RvcmUiOmZhbHNlLCJNYXJpYURCIjpmYWxzZSwiTW9uZXREQiI6ZmFsc2UsIk1vbmdvREIiOmZhbHNlLCJNb3RoZXJEdWNrIjpmYWxzZSwiTXlTUUwgKE15SVNBTSkiOmZhbHNlLCJNeVNRTCI6ZmFsc2UsIk9jdG9TUUwiOmZhbHNlLCJPcHRlcnl4IjpmYWxzZSwiT3hsYSI6ZmFsc2UsIlBhbmRhcyAoRGF0YUZyYW1lKSI6ZmFsc2UsIlBhcmFkZURCIChQYXJxdWV0LCBwYXJ0aXRpb25lZCkiOmZhbHNlLCJQYXJhZGVEQiAoUGFycXVldCwgc2luZ2xlKSI6ZmFsc2UsIlBhcnNlYWJsZSAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwicGdfZHVja2RiICh3aXRoIGluZGV4ZXMpIjpmYWxzZSwicGdfZHVja2RiIChNb3RoZXJEdWNrIGVuYWJsZWQpIjpmYWxzZSwicGdfZHVja2RiIjpmYWxzZSwicGdfZHVja2RiIChQYXJxdWV0KSI6ZmFsc2UsIlBvc3RncmVTUUwgd2l0aCBwZ19tb29uY2FrZSI6ZmFsc2UsInBncHJvX3RhbSAocGFycXVldCwgbG9jYWwgc3RvcmFnZSkiOmZhbHNlLCJwZ3Byb190YW0gKHBhcnF1ZXQsIGxvY2FsLCBwYXJhbGxlbCkiOmZhbHNlLCJwZ3Byb190YW0gKHBhcnF1ZXQsIGxvY2FsICsgY2FjaGUpIjpmYWxzZSwicGdwcm9fdGFtIChmZWF0aGVyLCBsb2NhbCArIGNhY2hlKSI6ZmFsc2UsIlBpbm90IjpmYWxzZSwiUG9sYXJzIChEYXRhRnJhbWUpIjpmYWxzZSwiUG9sYXJzIChQYXJxdWV0KSI6ZmFsc2UsIlBvc3RncmVTUUwgKHdpdGggaW5kZXhlcykiOmZhbHNlLCJQb3N0Z3JlU1FMIjpmYWxzZSwiUXVlc3REQiI6ZmFsc2UsIlJlZHNoaWZ0IjpmYWxzZSwiU2VsZWN0REIiOmZhbHNlLCJTaWdMZW5zIjpmYWxzZSwiU2luZ2xlU3RvcmUiOmZhbHNlLCJTbm93Zmxha2UiOmZhbHNlLCJTcGFyayI6ZmFsc2UsIlNRTGl0ZSI6ZmFsc2UsIlN0YXJSb2NrcyI6dHJ1ZSwiVGFibGVzcGFjZSI6ZmFsc2UsIlRlbWJvIE9MQVAgKGNvbHVtbmFyKSI6ZmFsc2UsIlRpbWVzY2FsZSBDbG91ZCI6ZmFsc2UsIlRpbWVzY2FsZURCIChubyBjb2x1bW5zdG9yZSkiOmZhbHNlLCJUaW1lc2NhbGVEQiI6ZmFsc2UsIlRpbnliaXJkIChGcmVlIFRyaWFsKSI6ZmFsc2UsIlVtYnJhIjpmYWxzZSwiVXJzYSI6ZmFsc2UsIlZpY3RvcmlhTG9ncyI6ZmFsc2UsIllEQiI6ZmFsc2V9LCJ0eXBlIjp7IkMiOnRydWUsImNvbHVtbi1vcmllbnRlZCI6dHJ1ZSwiUG9zdGdyZVNRTCBjb21wYXRpYmxlIjp0cnVlLCJtYW5hZ2VkIjp0cnVlLCJnY3AiOnRydWUsInN0YXRlbGVzcyI6dHJ1ZSwiSmF2YSI6dHJ1ZSwiQysrIjp0cnVlLCJNeVNRTCBjb21wYXRpYmxlIjp0cnVlLCJyb3ctb3JpZW50ZWQiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6dHJ1ZSwiZW1iZWRkZWQiOnRydWUsImRhdGFmcmFtZSI6dHJ1ZSwiWVRzYXVydXMiOnRydWUsImF3cyI6dHJ1ZSwiYXp1cmUiOnRydWUsImFuYWx5dGljYWwiOnRydWUsIlJ1c3QiOnRydWUsInNlYXJjaCI6dHJ1ZSwiZG9jdW1lbnQiOnRydWUsIkdvIjp0cnVlLCJzb21ld2hhdCBQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsInBhcnF1ZXQiOnRydWUsInRpbWUtc2VyaWVzIjp0cnVlLCJsb2dzIjp0cnVlLCJTaWdMZW5zIjp0cnVlLCJvYnNlcnZhYmlsaXR5Ijp0cnVlLCJkZWRpY2F0ZWQiOnRydWV9LCJtYWNoaW5lIjp7IjE2IHZDUFUgMTI4R0IiOnRydWUsIjggdkNQVSA2NEdCIjp0cnVlLCJzZXJ2ZXJsZXNzIjp0cnVlLCIxNmFjdSI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDUwMGdiIGdwMiI6dHJ1ZSwiTCI6dHJ1ZSwiTSI6dHJ1ZSwiUyI6dHJ1ZSwiWFMiOnRydWUsImM2YS5tZXRhbCwgNTAwZ2IgZ3AyIjp0cnVlLCIxMiB2Q1BVIDQ4R0IiOnRydWUsIjEwIHZDUFUgNDBHQiI6dHJ1ZSwiMTJHaUIsIDEgcmVwbGljYShzKSI6dHJ1ZSwiOEdpQiwgMSByZXBsaWNhKHMpIjp0cnVlLCIxMkdpQiwgMiByZXBsaWNhKHMpIjp0cnVlLCIxMjBHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMTZHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMjM2R2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjMyR2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjY0R2lCLCAyIHJlcGxpY2EocykiOnRydWUsIjhHaUIsIDIgcmVwbGljYShzKSI6dHJ1ZSwiMTJHaUIsIDMgcmVwbGljYShzKSI6dHJ1ZSwiMTIwR2lCLCAzIHJlcGxpY2EocykiOnRydWUsIjE2R2lCLCAzIHJlcGxpY2EocykiOnRydWUsIjIzNkdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCIzMkdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCI2NEdpQiwgMyByZXBsaWNhKHMpIjp0cnVlLCI4R2lCLCAzIHJlcGxpY2EocykiOnRydWUsImM1bi40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsIkFuYWx5dGljcy0yNTZHQiAoNjQgdkNvcmVzLCAyNTYgR0IpIjp0cnVlLCJjNS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsImM2YS40eGxhcmdlLCAxNTAwZ2IgZ3AyIjp0cnVlLCJYTCI6dHJ1ZSwiSnVtYm8iOnRydWUsIlB1bHNlIjp0cnVlLCJTdGFuZGFyZCI6dHJ1ZSwiMTYgdkNQVSAzMkdCIjp0cnVlLCJkYzIuOHhsYXJnZSI6dHJ1ZSwicmEzLjE2eGxhcmdlIjp0cnVlLCJyYTMuNHhsYXJnZSI6dHJ1ZSwicmEzLnhscGx1cyI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDcwMGdiIGdwMiI6dHJ1ZSwiUzIiOnRydWUsIlMyNCI6dHJ1ZSwiMlhMIjp0cnVlLCIzWEwiOnRydWUsIjRYTCI6dHJ1ZSwiTDEgLSAxNkNQVSAzMkdCIjp0cnVlLCJjNmEuNHhsYXJnZSwgNTAwZ2IgZ3AzIjp0cnVlLCIxNiB2Q1BVIDY0R0IiOnRydWUsIjQgdkNQVSAxNkdCIjp0cnVlLCI4IHZDUFUgMzJHQiI6dHJ1ZSwiNjQgdkNQVSAyNTZHQiI6dHJ1ZX0sImNsdXN0ZXJfc2l6ZSI6eyIxIjp0cnVlLCIyIjp0cnVlLCIzIjp0cnVlLCI0Ijp0cnVlLCI4Ijp0cnVlLCI5Ijp0cnVlLCIxNiI6dHJ1ZSwiMzIiOnRydWUsIjY0Ijp0cnVlLCIxMjgiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsInVuZGVmaW5lZCI6dHJ1ZX0sIm1ldHJpYyI6ImhvdCIsInF1ZXJpZXMiOlt0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlXX0= https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQi...

momono 1y ago

[dead]

tnolet 1y ago

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. But we chose ClickHouse and now we just pump in data with little to no optimization.

nasretdinov 1y ago
I imagine with Postgres there's also an option of using a plugin like Greenplum or something else, which may help to bridge the gap, but probably not to the level of ClickHouse.
- tnolet 1y ago
  yes, we looked at Timescale also. But they were much younger then and Clickhouse was more mature. Clickhouse cloud did not exist yet. We now use a mix of Altinity and Cloud.
xmodem 1y ago
We migrated some analytics workloads from postgres to clickhouse last year, it's crazy how fast it is. It feels like alien technology from the future in comparison.
- apwell23 1y ago
  are those like embedded analytics in the app or internal BI type workloads ?
  - tnolet 1y ago
    For us these are just metrics on customer facing dashboards inside our app. They are basically realtime and show p95, p99, avg. etc over time ranges. Our app can show this for 1000s of entities in one dashboard and that can eat up resources pretty quickly
tucnak 1y ago

jangliss 1y ago

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

curtisszmania 1y ago

[dead]

hexo 1y ago

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