What does HackerNews think of ClickHouse?

We achieved more than 3 times speedup using "on-demand" transparent huge pages in ClickHouse[1] for a very narrow use-case: random access to a hash table that does not fit in the L3 cache but is not much larger.

But there was a surprise... more than 10 times degradation of overall Linux server performance due to increased physical memory fragmentation after a few days in production: https://github.com/ClickHouse/ClickHouse/commit/60054d177c8b...

It was seven years ago, and I hope that the Linux kernel has been improved. I will need to try "revert of revert" of this commit. These changes cannot be tested by microbenchmarks, and only production usage can show their actual impact.

Also, we successfully use huge pages for text section of the executable, and it is beneficial for the stability of performance benchmarks due to lowering the number of iTLB misses.

[1] ClickHouse - high-performance OLAP DBMS: https://github.com/ClickHouse/ClickHouse/

There were countless attempts to extend or replace SQL:

OQL: https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-langua...

More modern:

PRQL: https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 (it is so obscure that Google replaces it to "Malay language")

Another example: ClickHouse supports standard SQL with many features such as window functions, SQL/JSON, and extends it to make it convenient for data analysts by adding: - higher-order functions; nested data structures, arrays, tuples, and maps; aggregate function states as first-class citizens, unrestricted usage of aliases in any place of expression, etc.

https://github.com/ClickHouse/ClickHouse/

I'm an everyday user of ClickHouse, and I'm finding its SQL implementation the most pleasant to use! Although it's unsurprising, because I'm also one of its authors... I'm also welcoming innovation and improvement of SQL without the introduction of a completely different language.

Simply by larger sizes of compressed blocks, which are limited to page size in Postgres, and by improving the data locality by sorting, which is inherent for LSM-trees.

But if you want higher compression, you need to consider column-oriented DBMS, such as ClickHouse[1]. They are unbeatable in terms of data compression.

[1] https://github.com/ClickHouse/ClickHouse

Disclaimer: I'm a developer of ClickHouse.

I also found it strange that PostgreSQL cannot simply support data formats of previous versions. In contrast, the latest version of ClickHouse[1] (23.1) can be installed over the version from 2016, and it does not require format conversions or any other migration procedures.

[1] https://github.com/ClickHouse/ClickHouse/

Syscalls can be heavier than expected. One example is when an application is run inside gVisor. Another example is when a lot of eBPF code is attached. A third example is when a program is run under strace.

Disclaimer: I'm working on ClickHouse[1], and it is used by thousands of companies in unimaginable environments. It has to work in every possible condition... That's why we set the TZ variable at startup and also embed the timezones into the binary. And we don't use the glibc functions for timezone operations because they are astonishingly slow.

https://github.com/ClickHouse/ClickHouse/

Memory bound alternative for the COUNT(DISTINCT ...) aggregate function in SQL.

ClickHouse[1] has a few functions for that, with different tradeoffs in memory usage, precision, and performance:

    - uniqExact - the same as COUNT(DISTINCT ...) - the exact version, using a hash table;
    - uniqCombined - a combination of a linear array of small size in memory arena, a hash table, and a HyperLogLog - this data structure is similar to HyperLogLog++; the log2 of the number of cells is controllable by the user;
    - uniq - a cardinality estimator based on adaptive sampling by hash, lower quality to memory usage compared to HyperLogLog, but beats it in CPU efficiency;
    - uniqTheta - uses the Theta sketch algorithm, whatever it means;
    - uniqUpTo(N) - my favorite, uses a linear array to give the precise answer up to N, and always answers N + 1 when the number of distinct elements is larger than N;
    - groupBitmap - an exact calculation using Roaring Bitmaps, makes sense for dense integer sets;

[1] https://github.com/ClickHouse/ClickHouse/

What is often forgotten in designing a data structure for a cardinality estimator - is that it should work well not only for a few large states but also for a large number of small sets.

For example, in a query like follows:

    SELECT URL, COUNT(DISTINCT UserID) FROM pageviews GROUP BY URL

You should expect that there are a large number of URLs, but most of them will get just 1..2 unique UserIDs. Obviously, it's not practical to allocate even a 4 KB HyperLogLog for every resulting record. That's how complex combined data structures are justified. They should start with ~16..64 bytes of automatic memory in arena or stack, and only then upgrade to a HyperLogLog.

+ totally agree with that.

There is a use case when you can improve performance by keeping compressed (LZ4) data in RAM and decompressing by small blocks that fit in cache. This is demonstrated by ClickHouse[1][2] - the whole data processing after decompression fits in cache, and compression saves the RAM bandwidth.

[1] https://presentations.clickhouse.com/meetup53/optimizations/ [2] https://github.com/ClickHouse/ClickHouse

The most developed analytical query engine as of today is ClickHouse [1].

It is open-source, has the separation of storage and compute and the best performance.

ClickHouse is a source of inspiration for many emerging database engines: Velox, Doris, DuckDB, Datafusion...

[1] https://github.com/ClickHouse/ClickHouse

Disclaimer: I work on ClickHouse.

I'm not sure what you mean. ClickHouse is an open source project. You can check it out here https://github.com/ClickHouse/ClickHouse

I am amazed at the variety of repos shared - by you, as well as others. Thanks!

Repo links:

https://github.com/facebook/zstd

https://github.com/google/brotli

https://github.com/ClickHouse/ClickHouse

https://github.com/evanw/esbuild

https://github.com/figma

https://github.com/tailwindlabs/tailwindcss

https://github.com/caddyserver/caddy

https://github.com/haproxy/haproxy

https://github.com/uNetworking/uWebSockets

> After the JetBrains hack

AFAIK there was no evidence that solarwind fiasco was because of jetbrains [0], also the clickhouse is open source [1] under apache2 license and you can check the source and question it by yourself.

[0] - https://www.zdnet.com/article/jetbrains-denies-being-involve...

[1] - https://github.com/ClickHouse/ClickHouse