Would you like to submit to ClickBench?

I can also do it if you would prefer...

ClickHouse code style recommends unsigned in every case when you don't need the sign: https://clickhouse.com/docs/development/style

No. It says the reverse: "11. unsigned. Use unsigned if necessary."

Unsigned is necessary only if you're working with bit fields, bit masks or require explicit modulo n-bit arithmetic.

For all other cases, use signed

I've recently implemented a similar service: https://adsb.exposed/?dataset=Birds&zoom=5 - a viewer for eBird data, where you can filter by various species and make visualizations with SQL.

Full write-up on how it is created: https://clickhouse.com/blog/birds

USearch is this type of library: https://github.com/unum-cloud/usearch

Used in ClickHouse and a few other DBMS.

I've implemented a similar site a few years ago, with one crucial difference, which makes it even simpler: https://pastila.nl/

The difference is that there is no "share" button, so you don't have to press it, and just copy the page URL any time.

Neat! I like your solution, much better for simple text sharing.

Exactly as in MS Access, Interbase/Firebird, and dBase II.

Flawed methodology - incorrect results.

Thanks for creating this issue, it is worth investigating!

I see you also created similar issues in Polars: https://github.com/pola-rs/polars/issues/17932 and DuckDB: https://github.com/duckdb/duckdb/issues/17066

ClickHouse has a built-in memory tracker, so even if there is not enough memory, it will stop the query and send an exception to the client, instead of crashing. It also allows fair sharing of memory between different workloads.

You need to provide more info on the issue for reproduction, e.g., how to fill the tables. 16 GB of memory should be enough even for a CROSS JOIN between a 10 billion-row and a 100-row table, because it is processed in a streaming fashion without accumulating a large amount of data in memory. The same should be true for a merge join.

However, there are places when a large buffer might be needed. For example, if you insert data into a table backed by S3 storage, it requires a buffer that can be in the order of 500 MB.

There is a possibility that your machine has 16 GB of memory, but most of it is consumed by Chrome, Slack, or Safari, and not much is left for ClickHouse server.

Yeah I feel like I'm on crazy pills, I'm OoM'ing all these big data tools that everyone loves very trivially -- duckdb OoM'd just loading a CSV file, and Polars OoM'd just reading the first couple rows of a parquet file?

I do want to get a better reproduction on CH because it seems like it's an interplay between the INSERT INTO...SELECT. It's just a bit of work to generate synthetic data with the same profile as my production data (for what it's worth I did put quite a bit of effort into following the doc guidelines for dealing with low-memory machines).

ClickHouse predates Apache Arrow.

I vaguely remember an old bug in atop, leading to a very unusual consequence.

Atop will do an invalid memory write and crash with a segfault. But this writing is performed on a memory page mapped to a hardware timer. Despite not being able to write into that page, just touching it somehow changes how this hardware timer works. Then, the OS detects that this timer is inaccurate and switches to a different clock source (which you can see in /sys/devices/system/clocksource/clocksource0/current_clocksource). As a result, every call to clock_gettime becomes slower, and the system becomes slower as a whole until it restarts.

In short, a segfault in atop leads to the whole system's performance degradation. But this was found around maybe 7 years ago.

This was found by the very same Rachel that's sounding the alarm here

https://rachelbythebay.com/w/2014/03/02/sync/

That is such an interesting bug!