We are fully embracing LGTM ourselves but this is really interesting. Loki for us has been great, though, so what is better about CH other than maybe sql being more expressive than LogQL?
ClickHouse recently has been a breath of fresh air compared to using timescaledb for a long time. Although psql is the greatest there is and I really enjoyed the fact that I could rely on a single database system to run everything, when it came to migration maintenance and deployment it's really a pain and it also feels like development on timescaledb is a bit wishy washy with all the structural changes from version to version and it really feels like an alpha product sometimes.
There is significant work happening in the PostgreSQL ecosystem to make "use a single system to run everything" possible. ParadeDB is one such system pushing on full-text/vector search, with some light aggregations, at the index level. There is also work being done by DuckDB folks with pg_duckdb and others like Xata. (disclaimer: I work at ParadeDB)
Clickhouse is *really* gatekeeping the "zero copy replication" where you store data on object-storage and have high availability from the open source version.
That feature is central for ClickHouse Cloud offering which essentially is what lets others have (the rest of) ClickHouse for free. If you need that feature I think it's quite obvious you'll have to pay. I don't think it's an unreasonable stance at all.
P.S. I don't believe you are right to call it "zero copy" either. The object storage itself is replicated and definitely does copy and/or split data a lot under the hood. In some sense it's no different from ClickHouse's replication, apart from that ClickHouse can't use erasure codes to reduce the data footprint, whereas many advanced object stores can
> You can open a pull request as an experiment, without aiming for it to be merged - it will be tested with the same level of scrutiny as production releases. Found a new memory allocator, a new compression library, a new hash table, a data format, or a sorting algorithm? - bring it to ClickHouse, and it will expose it inside-out
Clickhouse has been a game changer for some of the companies i have worked in the past. This reminds me of this podcast episode (1) from the Rust in Production pod about their Rust adoption.
The query speed deserves the praise, but the JSON ingestion path has quiet footguns nobody mentions here. Every numeric column comes back as a string over JSONEachRow, so a forgotten Number() cast silently turns arithmetic into string concatenation, and with input_format_skip_unknown_fields enabled a single typo in a column name drops that field with no error at all. Worth wiring an assertion that inserts a row and reads it back into CI before trusting the dashboards.
If your data is too big for postgres, it seems like moving straight to Clickhouse is the best option. We have been through an whole array of distributed database technologies, and Clickhouse might be first one that doesn't have too many compromises.
I think the main advantage of ClickHouse over DuckDB is *MergeTree family. It lets you sort data in the background, which allows for absurd levels of compression and performance when done right. ClickHouse can easily be 10x as performant as DuckDB querying Parquet when querying non-indexed columns, and obviously infinitely faster than DuckDB when you're touching primary key.
There are so many comparisons between the two, but realistically ClickHouse and DuckDB occupy completely separate niches, where DuckDB is just a really powerful analytics _engine_, and ClickHouse is a full database management system, with replication, MergeTree engine, etc.
It is sad they are afraid to mention on the page that "data processing for a web analytics system ... similar to Google Analytics" was actually something used in Yandex.
We use Clickhouse in a rails app for our customer facing dashboard analytics, logging, and datalake type stuff where Postgres is too heavy and expensive. The web admin panel they built is great and we’ve had solid performance.
For our metrics and autoscaling engine at Cloud 66, we went through 5 iterations before settling on Clickhouse:
1. Redis
2. Cassandra
3. Handrolled: Ruby + RabbitMQ
4. Handrolled: Go + RabbitMQ
5. Clickhouse
Every time we reached some limit or huge optimization burdens that were unfeasible. Clickhouse has been rock solid for the past 4 years.
I used to keep all of OnlineOrNot's timeseries data entirely in a hot postgres db with the rest of the relational data.
Used to take a few seconds to get a week's uptime data and do some useful analysis.
Since moving to Clickhouse I think I can grab a full year's data in around 200ms (probably less if I try optimising it). Still completely blows my mind everyday.
Love clickhouse it is just super performant. I've had to tweak a few queries for performance here and there but it has been more than stellar. I'd initially set up a real time pipeline ingest for us to handle our larger incremental ingests when i started (used redshift in the past, very expensive and quite slow in comparison), it has been unnecessary so far, as clickhouse has just been able to chew through tons of data and large transformations without breaking a sweat.
Only issue i had was that the default configuration had some quite heavy handed tracing turned on which tanked performance on the relatively little machine we had for it, it has since been scaled up and is the core of our data stack.
I can't recommend clickhouse enough. If you were truly large I'd probably choose something else, but as long as we're staying on a few nodes it is manageable complexity and we enjoy using it.
On top of being a really good OLAP database, what has been a game changer for me is the built-in connectors for bringing in data from remote sources. It can manage automatic recurring import of a s3 folder containing parquet/json files and it can also connect directly to Postgres. For our data warehouse at a medium sized newspaper, we switched from Druid+postgres+trino to just one big clickhouse node and I’ve never looked back. Much more performant, practical, and a lot less maintenance.
34 comments
[ 2.6 ms ] story [ 54.9 ms ] threadP.S. I don't believe you are right to call it "zero copy" either. The object storage itself is replicated and definitely does copy and/or split data a lot under the hood. In some sense it's no different from ClickHouse's replication, apart from that ClickHouse can't use erasure codes to reduce the data footprint, whereas many advanced object stores can
Managers rejected it because it wasn't well known and was seen as "some database made by Russians."
On a personal level, it's quite sad to have seen that train coming so early and not been able to get on board.
Wow
1. https://open.spotify.com/episode/0TBKDUhO0KihBxEzZqnQx1
Agree that Level 3 is what inspires confidence. But we need to invent new business models to sustain in the era of vibe-coded databases.
There are so many comparisons between the two, but realistically ClickHouse and DuckDB occupy completely separate niches, where DuckDB is just a really powerful analytics _engine_, and ClickHouse is a full database management system, with replication, MergeTree engine, etc.
Every time we reached some limit or huge optimization burdens that were unfeasible. Clickhouse has been rock solid for the past 4 years.
Redis, Cassandra, RabbitMQ and Clickhouse. RabbitMQ looks like a black sheep in this lineup.
Used to take a few seconds to get a week's uptime data and do some useful analysis.
Since moving to Clickhouse I think I can grab a full year's data in around 200ms (probably less if I try optimising it). Still completely blows my mind everyday.
Only issue i had was that the default configuration had some quite heavy handed tracing turned on which tanked performance on the relatively little machine we had for it, it has since been scaled up and is the core of our data stack.
I can't recommend clickhouse enough. If you were truly large I'd probably choose something else, but as long as we're staying on a few nodes it is manageable complexity and we enjoy using it.