Does anyone use clickhouse in production? I was initially pretty impressed but when I really put it through its paces I could OoM it as soon as I actually started querying non-trivial amounts of data:
Yep. Clickhouse is absolutely great for tons of production use cases.
Unless you try to join tables in it, in which case it will immediately explode.
More seriously, it's a columnar data store, not a relational database. It'll definitely pretend to be "postgres but faster", but that's a very thin and very leaky facade. You want to do massively a complex set of selects and conditional sums over one table with 3b rows and tb of data? You'll get a result in tens of seconds without optimization. You want to join two tables that postgres could handle easily? You'll OOM a machine with TB of memory.
So: good for very specific use cases. If you have those usecases, it's great! If you don't, use something else. Many large companies have those use cases.
The majority of our queries have joins (plus our core logic often depends on fact table expansion with `arrayJoin()`s) before aggregations and we're doing fine. AFAIK whenever we hit memory issues, they are mostly due to high-cardinality aggregations (especially with uniqExact), not joins. But I'm sure it can depend on the specifics.
legorobot · 14h ago
Definitely agree with this, I think ClickHouse can do a lot with joins if you don't implement them naively. Keeping the server up-to-date is a part of it too.
They've made strides in the last year or two to implement more join algorithms, and re-order your joins automatically (including whats on the "left" and "right" of the join, relating to performance of the algorithm).
Their release notes cover a lot of the highlights, and they have dedicated documentation regarding joins[1]. But we've made improvements by an order-of-magnitude before by just reordering our joins to align with how ClickHouse processes them.
> More seriously, it's a columnar data store, not a relational database.
Could you explain why you don't think ClickHouse is relational? The storage is an implementation detail. It affects how fast queries run but not the query model. Joins have already improved substantially and will continue to do so in future.
mplanchard · 11h ago
Yes (via Clickhouse Cloud, which is pretty reasonably priced).
It’s important to structure your tables and queries in a way that aligns with the ordering keys, in order to optimize how much data needs to be loaded into RAM. You absolutely CANNOT just replicate your existing postgres DB and its primary keys or whatever over to CH. There are tricks like projections and incremental materialized views that can help to get the appropriate “lenses” for your queries. We use incremental MVs to, for example, continuously aggregate all-time stats about tens of billions of records. In general, for CH, space is cheap and RAM is expensive, so it’s better to duplicate a table’s data with a different ordering key than to make an inefficient query.
As long as the queries align with the ordering keys, it is insanely fast and able to enable analytics queries for truly massive amounts of data. We’ve been very impressed.
hodgesrm · 17h ago
It's used in production by many thousands of companies at this point. The ClickHouse Inc numbers are just a fraction of the total users.
p.s., It's also possible to break ClickHouse as you demonstrated. It used to be a lot easier.
Boxxed · 17h ago
I guess I'm curious how; I breathe on it wrong and it OoMs.
hodgesrm · 16h ago
One of the tradeoffs for ClickHouse versus databases like Snowflake is that you have to have some knowledge about the internals to use it effectively. For example, Snowflake completely hides partitioning but on the other hand it does not deliver consistent, real-time response the way a well-tuned ClickHouse application can.
When you use INSERT ... SELECT in ClickHouse you do need to pay attention to the generated table partitions, as they coexist in memory before flushing to storage. The usual approach is to break up the insert into chunks so you can control how many parts are generated or to adjust the partitioning in the target table.
It's possible the problem might be somehow related to this behavior but that's just conjecture. It's usually pretty easy to work around. Meanwhile if it's a bug it will probably get fixed quickly.
datavirtue · 16h ago
You have to have knowledge of the internals of any database you use. Not knowing is going to cost someone a lot of money and/or performance.
nasretdinov · 16h ago
One easy way to achieve this is to store really large values, e.g. 10 Mb per row. Since ClickHouse operates in large blocks you'd easily cause an OOM just by trying to read chunks of 8192 rows (the default) at a time, especially during merges, where it needs to read large blocks from several parts at once
bathtub365 · 17h ago
You don’t need a good product to have a lot of users, just good marketing and salespeople.
owenthejumper · 16h ago
I find Clickhouse fascinating, really good, and also really tough to run. It's a non-linear memory hog. It probably needs 32GB RAM for basics to run, otherwise it will OOM on minimal amount of data. That said, it won't "OOM", as in crash. It will just report the query would use too much memory, so it aborted the query.
_gmax0 · 10h ago
Heard from the grapevine that CloudFlare uses it for their analytics.
Clickhouse is great, but like any database if you run it at scale someone must tend to it.
hackitup7 · 17h ago
Yes for relatively large workloads
lossolo · 9h ago
7 years, 24/7 high volume, self hosted, no issues really.
david38 · 15h ago
It’s fantastic but it’s a columnar store. It’s not a Postgres replacement.
the__alchemist · 18h ago
Is there an ELI5 for this company? I'm having a difficult time understanding it from their website. Is it an alternative to Postgres etc? Something that runs on top of it? And analyzes your DB automatically?
jameslk · 17h ago
When Postgres takes a while to answer analytical questions like "what's the 75th percentile of response time for these 900 some billion requests rows, grouped by device, network, and date for the past 30 days", that's when you might want to try out ClickHouse
cluckindan · 17h ago
Or literally any other OLAP database.
Is it a surprise that OLTP is not efficient at aggregation and analytics?
nasretdinov · 16h ago
ClickHouse also has great compression and it's easy to install and to try since it's open-source. Also it's typically much faster than even other OLAP, often by a _lot_
swyx · 17h ago
maybe HTAP works for most people though
NunoSempere · 16h ago
That seems like the kind of problem that would be easily done through monte-carlo approximation? How hard is it to get 1M random rows in a postgres database?
I'm struggling with TimescaleDB performance right now and wondering if the grass is greener.
andness · 12h ago
Started migrating away from TimescaleDB some time ago too. Initially we self-hosted to test it out. It was very quickly clear that it was a lot better for our use case and we decided to go with Clickhouse Cloud to not have to worry about the ops. The pricing for the cloud offering is very good IMO. We use it for telemetry data from a fleet of IoT devices.
whatevermom · 14h ago
Migrated from TimescaleDB to ClickHouse and it was like night and day. Naive reimplementation of the service performed wayyyy better than timescaledb. Self-hosted.
sukruh · 16h ago
It is.
applied_heat · 16h ago
What is the workload or query that is causing issues?
NewJazz · 11h ago
We denormalized some data then wanted to quickly filter by it. I managed to find a decent index to get us through, but now I'm stuck with another dimension in my data that I'd rather not have. I think I'll have to create a new table, migrate data, then rename it.
jgalt212 · 16h ago
I'm not sure storing 900B or 900MM records for analytics benefits anyone other than AWS. Why not sample?
sethhochberg · 16h ago
A use case where we reached for Clickhouse years ago at an old job was for streaming music royalty reporting. Days of runtime on our beefy MySQL cluster, minutes of runtime in a very naively optimized Clickhouse server. And sampling wasn't an option because rightholders like the exactly correct amount of money per stream instead of some approximation of the right amount of money :)
There's nothing Clickhouse does that other OLAP DBs can't do, but the killer feature for us was just how trivially easy it was to replicate InnoDB data into Clickhouse and get great general performance out of the box. It was a very accessible option for a bunch of Rails developers who were moonlighting as DBAs in a small company.
jgalt212 · 14h ago
Yes, payments is an N=all scenario. Analytics is not, however.
antisthenes · 11h ago
Use-case dependent. For some analytics, you really want to see the tail ends (e.g. rare events) which sampling can sometimes omit or under-represent.
bandoti · 17h ago
Or if you have to use it because you’re self-hosting PostHog :)
arecurrence · 17h ago
Clickhouse has a wide range of really interesting technologies that are not in Postgres; fundamentally, it's not an OLTP database like Postgres but more-so aimed at OLAP workloads. I really appreciate Clickhouse's focus on performance and quite a bit of work goes into optimizing the memory allocation and operations among different data types.
The heart of Clickhouse are these table engines (they don't exist in Postgres) https://clickhouse.com/docs/engines/table-engines . The primary column (or columns) is ordered in some way and adjacent values in memory are from the same column in the table. Index entries span wide areas (EG: By default there's only one key record in the primary index for every 8192 rows) because most operations in Clickhouse are aggregate in nature. Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine. :)
This structure allows it to literally crunch billions of values per second (brutally, not with pre-processing, erm,
"tricks" although there is a lot of support for that in Clickhouse as well). I've had tables with hundreds of columns and 100+ billion rows that are nearly as performant as a million row table if I can structure the query to work with the table's physical ordering.
Clickhouse recommends not using nullable fields because of the performance implications (it requires storing a bit somewhere for each value). That's how much they care about perf and how close to the raw data type it is that their memory allocation uses. :)
porridgeraisin · 17h ago
> Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine.
> They are initially a new physical part that is later merged into the main table structure
The reason I mentioned it is because it's a huge surprise to some people that... from the docs: "The ALTER TABLE prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use. ALTER TABLE is considered a heavyweight operation that requires the underlying data to be merged before it is deleted."
There's also a "lightweight delete" available in many circumstances https://clickhouse.com/docs/sql-reference/statements/delete. Something really nice about the ClickHouse docs is that they devote quite a bit of text to describing the design and performance implications of using an operation. It reiterates the focus on performance that is pervasive across the product.
SQL, OLAP, Primary use case is fast aggregations on append only data, like usage analytics.
It's fast, it's........ really fast!!
But you need to get comfortable with their extended SQL dialect that forces you to think a little different than with usual SQL if you want to keep perf high.
simantel · 17h ago
It's an alternative to Postgres in the sense that they're both databases. Read up on OLAP vs. OLTP to see the difference.
No comments yet
doix · 17h ago
I guess you could say it's an alternative to postgres. It's a different database, that's column oriented which makes different tradeoffs. I'd say DuckDB is a better comparison, if you're familiar with it.
pythonaut_16 · 17h ago
Expanding for the original question:
Roughly speaking, Postgres is to SQLite what Clickhouse is to DuckDB.
OLTP -> Online Transaction Processing. Postgres and traditional RDBMS. Mainly focused on transactions and addressing specific rows. Queries like "show me all orders for customer X".
OLAP -> Online Analytical Processing. Clickhouse and other columnar oriented. For analytical and calculation queries, like "show me the total value of all orders in March 2024". OLTP database typically store data by column rather than row, and usually have optimizations for storage space and query speed based on that. As a tradeoff they're typically slower for OLTP type queries. Often you'd bring in an OLAP db like Clickhouse when you have a huge volume of data and your OLTP database is struggling to keep up.
ksynwa · 17h ago
What's the significance of "online" in these acronyms?
IMTDb · 11h ago
Online means you expect the responses to come quickly (seconds) after launching the request. The opposite is "offline" where you expect the results to come a long time after making the request (hours / days).
ClickHouse is designed so you can build dashboard with it. Other offline system are designed so you can build reports that you send in PDF over email with them.
edoceo · 16h ago
Live and real-time
stonemetal12 · 16h ago
It is a rather old acronym. The other option was batch processing, you will get your results in the mail type thing.
Here "Online" means results while connected to the system, not real time since there is no time requirement for results.
whobre · 17h ago
It's not like Postgres at all, except on the very superficial level. It is an analytical engine like BigQuery, Snowflake, Teradata, etc...
amazingamazing · 18h ago
How hard is it to self host clustered clickhouse? Is there parity with the hosted offering?
nasretdinov · 18h ago
It's quite easy to host your own instance, we've done it ~7 years ago and had a cluster of over 50 nodes without any major issues. What ClickHouse Cloud offers is "shared nothing" storage, via SharedMergeTree that has S3 as a backing store, and it allows to scale storage and compute separately. The implementation is closed source.
amazingamazing · 18h ago
Interesting - hardware is so cheap though, I guess most enterprises don’t want the hassle.
Personally I’d just go to a colo center buy a rack of super micro and call it a day. No way that’s more expensive after a year (per public pricing).
nasretdinov · 18h ago
Sharding in Open-Source version isn't automatic, so you have to manage it yourself, as in there is no automatic resharding and you need to insert data accordingly. IMO that's the biggest bottleneck in its adoption at larger scale. Previously you didn't have a choice in terms of whether or not to do sharding (and compute/storage separation if you want it), now you have more options, including one from ClickHouse authors themselves.
nine_k · 18h ago
Apparently it's not a bottleneck, it's a sales funnel.
nasretdinov · 17h ago
I don't see a contradiction here tbh. There's nothing wrong in not providing some extra functionality for free (especially for features that users will pay for). If you have engineering resources to manage sharding manually you're welcome to do so. Since ClickHouse is a commercial company and not part of Yandex they need to earn money one way or another to fund the database development.
marvinblum · 17h ago
It's not that hard, but there are a few pitfalls you can stumble into. I currently run three clusters for myself and have set some for clients in the past.
Some of the default config options are weird and SSL is something that needs to be addressed. Overall, still one of the easier DBs to maintain.
quantumwoke · 18h ago
Is this money for growth or exiting employee options?
vb-8448 · 18h ago
I'm wondering why ClickHouse need to raise more money? Aren't they profitable already?
jedberg · 17h ago
Usually by Series C, you're at a point where you could be breakeven or profitable, but it's because you're tackling a huge market with a lot of opportunities, so it makes sense to take on capital to accelerate growth to attack that market.
devops000 · 17h ago
only 2k users?
with 200$/month I have a good database. $1-5M revenue?
arecurrence · 17h ago
I've worked at a number of companies using Clickhouse and they all self-hosted. I imagine Clickhouse corporate is focused on large customers.
noleary · 17h ago
My understanding is that those 2,000 represent some very large and enterprise-y contracts. The GitHub itself has almost 2,000 contributors: https://github.com/ClickHouse/ClickHouse
brettgriffin · 17h ago
the ACV for a data warehouse is orders of magnitude beyond $200. Snowflake's ACV is something like $300k/yr
wooque · 16h ago
we use smallest cluster and it's $450/month, most companies probably pay much more.
bananapub · 18h ago
oof, that sucks [for everyone else]. I hope someone figures out how to make a sustainable business of this sort, eventually.
candiddevmike · 18h ago
Being a profitable database vendor is really, really hard. You absolutely have to lock down big customers during your hype cycle or you're done for. The time to value for customers is so long, it becomes such an investment and sales cycles become really laborious (as a former DB SE in the past).
hodgesrm · 17h ago
Or you focus on cost-efficient operation from very the beginning. Ironically databases are also one of the markets where it's possible to achieve profitability operating, extending, or supporting open source software. I did a talk at FOSDEM 2025 about how three specific companies (Percona, DBeaver, Altinity) achieved this. [0] It is possible because businesses depend on databases and are willing to pay real money to ensure they work properly.
Yep, there will be an alternatives on Azure and AWS soon enough, if not already.
dangoodmanUT · 18h ago
I love clickhouse and a lot of the team members, but some of the "ClickHouse, Inc." people seem very counter to the original mission of CH, which has been unfortunately been reflected in some negative ways to both the overall OLAP ecosystem, and clickhouse itself.
I've shared many of those thoughts with their team directly out of love.
Also that's Series D-E, money isn't real anymore
nasretdinov · 18h ago
I personally see ClickHouse still improving in terms of overall usability and becoming much more polished, introducing features like full-text indexing, JSON data type, etc, all open-source and completely free. The commercial offering deviates from the "bare-bones", "build-it-yourself" storage, but, again, in my opinion it makes perfect sense to commercialise this part of it, to allow the product overall to continue to evolve and be successful. Otherwise ClickHouse as an open-source database probably wouldn't be able to evolve so quickly since the needs of Yandex don't always align with the needs of other users of the database
Octoth0rpe · 18h ago
> Also that's Series D-E, money isn't real anymore
Could you explain this? Is this commentary on voting power dilution or their class a/b share rules?
mooreds · 17h ago
Not OP, but I took it to mean that the round was absurdly large. The norms/expectations around size of rounds are not what they once were.
I had the same thought the first time I heard about a 12M "seed" round.
ko_pivot · 17h ago
I’m guessing what they mean is that the valuation is so inflated at this point that the high dollar amount more reflects the likelihood of acquisition or IPO in the near term rather than some sort of substantive demonstration of confidence in the company and its founders.
PeterZaitsev · 18h ago
What differences from original mission do you see ?
jasonjmcghee · 18h ago
Depends on trajectory and capital need among other things. There are series B this size.
whiskeytwolima · 17h ago
I honestly just want to know why they didn't steam their shirts.
ajcp · 16h ago
I'm really confused by the wrinkle pattern. Were they stored with half the shirt stuffed in a Pringles can? Or were these shot out of a air-cannon? The more I look at the picture the deeper the mystery gets.
tylerhannan · 15h ago
I actually took that picture a few years ago.
It's a fun story.
Our first swag shipment with the new colours had just arrived, the founders were in one place together for one of the first times, the weather wasn't terrible in Amsterdam for one day.
Not a pringles can. Rather they were stuffed in a shipping box that came from a warehouse, manhandled by customs, and thrown onto them for the purpose of taking the photo.
#startuplife eh?
whiskeytwolima · 13h ago
Love it, and I've definitely been there. Too funny though.
winterbloom · 17h ago
so like iron? is it that important? asking legitimately
whiskeytwolima · 13h ago
Nah I don't think it's important. I just think it's funny.
https://github.com/ClickHouse/ClickHouse/issues/79064
Unless you try to join tables in it, in which case it will immediately explode.
More seriously, it's a columnar data store, not a relational database. It'll definitely pretend to be "postgres but faster", but that's a very thin and very leaky facade. You want to do massively a complex set of selects and conditional sums over one table with 3b rows and tb of data? You'll get a result in tens of seconds without optimization. You want to join two tables that postgres could handle easily? You'll OOM a machine with TB of memory.
So: good for very specific use cases. If you have those usecases, it's great! If you don't, use something else. Many large companies have those use cases.
They've made strides in the last year or two to implement more join algorithms, and re-order your joins automatically (including whats on the "left" and "right" of the join, relating to performance of the algorithm).
Their release notes cover a lot of the highlights, and they have dedicated documentation regarding joins[1]. But we've made improvements by an order-of-magnitude before by just reordering our joins to align with how ClickHouse processes them.
[1]: https://clickhouse.com/docs/guides/joining-tables
Could you explain why you don't think ClickHouse is relational? The storage is an implementation detail. It affects how fast queries run but not the query model. Joins have already improved substantially and will continue to do so in future.
It’s important to structure your tables and queries in a way that aligns with the ordering keys, in order to optimize how much data needs to be loaded into RAM. You absolutely CANNOT just replicate your existing postgres DB and its primary keys or whatever over to CH. There are tricks like projections and incremental materialized views that can help to get the appropriate “lenses” for your queries. We use incremental MVs to, for example, continuously aggregate all-time stats about tens of billions of records. In general, for CH, space is cheap and RAM is expensive, so it’s better to duplicate a table’s data with a different ordering key than to make an inefficient query.
As long as the queries align with the ordering keys, it is insanely fast and able to enable analytics queries for truly massive amounts of data. We’ve been very impressed.
p.s., It's also possible to break ClickHouse as you demonstrated. It used to be a lot easier.
When you use INSERT ... SELECT in ClickHouse you do need to pay attention to the generated table partitions, as they coexist in memory before flushing to storage. The usual approach is to break up the insert into chunks so you can control how many parts are generated or to adjust the partitioning in the target table.
It's possible the problem might be somehow related to this behavior but that's just conjecture. It's usually pretty easy to work around. Meanwhile if it's a bug it will probably get fixed quickly.
Clickhouse is great, but like any database if you run it at scale someone must tend to it.
Is it a surprise that OLTP is not efficient at aggregation and analytics?
There's nothing Clickhouse does that other OLAP DBs can't do, but the killer feature for us was just how trivially easy it was to replicate InnoDB data into Clickhouse and get great general performance out of the box. It was a very accessible option for a bunch of Rails developers who were moonlighting as DBAs in a small company.
The heart of Clickhouse are these table engines (they don't exist in Postgres) https://clickhouse.com/docs/engines/table-engines . The primary column (or columns) is ordered in some way and adjacent values in memory are from the same column in the table. Index entries span wide areas (EG: By default there's only one key record in the primary index for every 8192 rows) because most operations in Clickhouse are aggregate in nature. Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine. :)
This structure allows it to literally crunch billions of values per second (brutally, not with pre-processing, erm, "tricks" although there is a lot of support for that in Clickhouse as well). I've had tables with hundreds of columns and 100+ billion rows that are nearly as performant as a million row table if I can structure the query to work with the table's physical ordering.
Clickhouse recommends not using nullable fields because of the performance implications (it requires storing a bit somewhere for each value). That's how much they care about perf and how close to the raw data type it is that their memory allocation uses. :)
> They are initially a new physical part that is later merged into the main table structure
> A single DELETE is an ALTER TABLE operation
Can you explain these two further?
The reason I mentioned it is because it's a huge surprise to some people that... from the docs: "The ALTER TABLE prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use. ALTER TABLE is considered a heavyweight operation that requires the underlying data to be merged before it is deleted."
There's also a "lightweight delete" available in many circumstances https://clickhouse.com/docs/sql-reference/statements/delete. Something really nice about the ClickHouse docs is that they devote quite a bit of text to describing the design and performance implications of using an operation. It reiterates the focus on performance that is pervasive across the product.
Edit: Per the other part of your question, why inserts create new parts and how they are merged is best described here https://clickhouse.com/docs/engines/table-engines/mergetree-...
The database is OLAP where Postgres is an OLTP database. Essentially it very fast at complex queries, and is targeted at analytics workloads.
ClickHouse spun out of Yandex & is open source, https://github.com/ClickHouse/clickhouse
Disclosure: I started at Citus & ended up at ClickHouse
https://dbdb.io/db/clickhouse
It's fast, it's........ really fast!!
But you need to get comfortable with their extended SQL dialect that forces you to think a little different than with usual SQL if you want to keep perf high.
No comments yet
Roughly speaking, Postgres is to SQLite what Clickhouse is to DuckDB.
OLTP -> Online Transaction Processing. Postgres and traditional RDBMS. Mainly focused on transactions and addressing specific rows. Queries like "show me all orders for customer X".
OLAP -> Online Analytical Processing. Clickhouse and other columnar oriented. For analytical and calculation queries, like "show me the total value of all orders in March 2024". OLTP database typically store data by column rather than row, and usually have optimizations for storage space and query speed based on that. As a tradeoff they're typically slower for OLTP type queries. Often you'd bring in an OLAP db like Clickhouse when you have a huge volume of data and your OLTP database is struggling to keep up.
ClickHouse is designed so you can build dashboard with it. Other offline system are designed so you can build reports that you send in PDF over email with them.
Here "Online" means results while connected to the system, not real time since there is no time requirement for results.
Personally I’d just go to a colo center buy a rack of super micro and call it a day. No way that’s more expensive after a year (per public pricing).
Some of the default config options are weird and SSL is something that needs to be addressed. Overall, still one of the easier DBs to maintain.
with 200$/month I have a good database. $1-5M revenue?
[0] https://fosdem.org/2025/schedule/event/fosdem-2025-5320-buil...
Disclaimer: I run Altinity.
I've shared many of those thoughts with their team directly out of love.
Also that's Series D-E, money isn't real anymore
Could you explain this? Is this commentary on voting power dilution or their class a/b share rules?
I had the same thought the first time I heard about a 12M "seed" round.
It's a fun story.
Our first swag shipment with the new colours had just arrived, the founders were in one place together for one of the first times, the weather wasn't terrible in Amsterdam for one day.
Not a pringles can. Rather they were stuffed in a shipping box that came from a warehouse, manhandled by customs, and thrown onto them for the purpose of taking the photo.
#startuplife eh?