sdairs

I think we do allude this in the post, if my understanding is correct. AFAIK both ClickHouse and Postgres are using MVCC, so when an UPDATE is run, a new version of the data is written and the original data is not changed. In ClickHouse, we write a patch part, it's merged automatically by a background process or by FINAL. In Postgres, you write a new tuple and mark the old one as dead, which will be cleared up by a VACUUM.

I'd wager the overhead in Postgres is probably lighter (though it's also light in ClickHouse), so you're right, this would be an interesting test. We actually had something like that planned, to run concurrent UPDATEs and SELECTs at different volumes for a period of time, to see how they each cope. Will definitely do it!

If you're refering to the experimental ACID transactions, we didn't test them; I'll check what state they're in and see if its worth including yet, if not, we'll for sure come back to them later and do a similar test!

One of the post authors here; I just want to stress that the goal of the post was not at all to be "haha we're better than postgres" - we explicitly call out the caveats behind the results.

Prior to July 2025 (ClickHouse v25.7), ClickHouse did not support UPDATE statements. At all. Like most columnar, analytics databases. We spent a lot of effort designing and implementing a way to support high-performance, SQL-standard UPDATE statements. This test was pretty much just trying to see how good of a job we had done, by comparing ourselves to the gold standard, Postgres. (If you're curious, we also wrote about how we built the UPDATE support in depth https://clickhouse.com/blog/updates-in-clickhouse-2-sql-styl...)

We have some updates to the post in progress; we originally deliberately used cold runs for both ClickHouse & Postgres, because we wanted to look at the "raw" update speed of the engine, vs. the variability of cache hits. But TL;DR when you run a more "real world" test where caches are warm and Postgres is getting very high cache-hit ratio, its point updates are consistently ~2ms, while ClickHouse is somewhere ~6ms (bulk updates are still many multiples faster in ClickHouse even with the cache in play).

Sadly, for the people who cheat, it is, indeed, worth it. :/

Is the proposition in the OP not pretty much what you're suggesting in your blog? They're currently not using the query builder syntax, instead its pretty much "improving on SQL as strings" with a bunch of the other ORM-like benefits (type safety, autocomplete, etc.)

Perhaps saying "ORM" is a bit of a misnomer, but they're discussing the DX ergonomics of an ORM and acknowledging the exact challenges you describe

Without doubt, the majority of the market for OLAP today is still internal warehousing & BI. But the market for using OLAP behind features inside user-facing B2C/B2B apps has been kicking off for quite a few years now. Big consumer apps like Stripe, Uber, Shopify...pretty much every B2B SaaS with a usage/metrics dashboard...they're usually punting queries off to an OLAP to populate those stats/charts. That's where something like this might come in handy, I can't imagine it being using for general internal reporting (in the current form, anyway.)

It's an interesting proposition. There's a lot of good tooling for data engineering work on OLAP, but there's really not much out there for devs that need to integrate with them.

Does it have anything to help a dev with query profiling / optimisation?

Interesting, that's not a problem I've come across before particularly - could you share more?

Are you looking for setups for OSS ClickHouse or managed ClickHouse services that solve it?

Both Tinybird & ClickHouse Cloud are managed ClickHouse services that include ingest connectors without needing Kafka

Estuary (an ETL tool) just released Dekaf which lets them appear as a Kafka broker by exposing a Kafka-compatible API, so you can connect it with ClickHouse as if it was Kafka, without actually having Kafka (though I'm not sure if this is in the open source Estuary Flow project or not, I have a feeling not)

If you just want to play with CH, you can always use clickhouse-local or chDB which are more like DuckDB, running without a server, and work great for just talking to local files. If you don't need streams and are just working with files, you can also use them as an in-process/serverless transform engine - file arrives, read with chDB, process it however you need, export it as CH binary format, insert directly into your main CH. Nice little pattern than can run on a VM or in Lambda's.

Very cool!

Could you share the key difference between this and the previous pg_analytics, and motivation of making it a separate plugin?

The answer is to pay school teachers more, not demand everyone else be paid less.

Lots of interesting stuff happening with the GIL with PEP703 seemingly greenlit. https://discuss.python.org/t/a-steering-council-notice-about...

Honestly it's fascinating that such a small smell can lead to such surprisingly oversized results. I wonder how many little signals like this get missed all the time...

What was the improvement to the original 1 second CI test?

I don't disagree with you, and I believe you're probably teaching people many of the right things that will set them up to do well in the future. But...the tricky part about learning just concepts and not the skills is that they are quite abstract and its very hard for a junior to immediately see how they can applied in practise when getting their first job. They're also quite hard to demonstrate to an employer. Learning SQL gives you some tangible to do that can help you to learn something about many of those areas, maybe not too deep, but while also getting a hands-on skill that easier to demonstrate to an employer. The reason I think SQL is the right skill there is that its specific enough that its actually useful, but its generic enough that its widely applicable to many employers (rather than specialising on one specific tool, like say Spark).

For example, you might learn the theory behind internal combustion engines, but does that make you more attractive as a Mechanic's apprentice than a kid who's rebuilt his lawn mower's engine? Neither are 100% perfect, but the hands on experience might be easier to demonstrate that you can be effective on day 1, and you can learn some of that theory on the job. Idk if that's the best analogy, and I'm not saying it's 100% the case here.

I did a degree in Compture Science and learnt a lot of theory, its all super interesting and personally I still love understanding all of the theory and low level details behind software...but many of the DEs working in Enterprise don't have that background (or interest). And that's not a bad thing, they're just as competent data engineers as I am.

I guess it's a matter of what your end goal (and timeline) is. Some folks aren't looking to be a word-class DE expert, for some its just a job and DE is a booming space with above-average compensation. In that case, you're probably not inclined to sit in a class room and learn all the theory, you just want to land the job and get on with it. Nothing wrong with that at all, some of the best get-stuff-done engineers are like this. For others, even if they find the space fascinating and they want to be at the top, they might be more interested in one very specific hand-on area and they only need as much theory as they pick up day-to-day.

Anyway, I don't think your approach is wrong, and everything you're teaching is great for someone who is genuinely interested in the field of DE - but I also don't think its all absolutely necessary if you just want to get your first DE job.

RE. DE vs ETL developer; I think DE has become more of an umbrella term like "Software Engineer" which encompasses many different roles. An ETL developer probably falls under Data Engineering these days, its just a specific niche.

Hey, it's my blog - apologies! I am really bad at frontend and this was the first iteration of me trying to build my blog from scratch doing everything myself. It's definitely not perfect! Appreciate the feedback and I'll try to make it more readable on mobile, sorry!

Author of the post here, I had no idea it got posted here and just noticed a huge traffic spike. So, hello!

Something I got asked a lot in response to this post, or maybe berated for, is that there is a lot more to Data Engineering than SQL.

FWIW, I agree. There is so, so much more to DE than SQL.

The point of this post was purely to cover entry-level DE, and not 'This is the only thing you'll ever need in your career'. In my view, developing your SQL skills teaches you a lot of the fundamentals of working with data, enough that you can start to use it in a professional setting. From there, you will develop a lot more skills than just SQL, but your journey could take you in so many different paths that I find it hard to agree with folks who say you should always learn X,Y,Z specific tools. Some folks will end up in Python shops, or Scala, or Bash, or no-code, Airflow, Dagster, Prefect, Control-M, Spark, Flank, Pandas, Polrs, BigQuery, Snowflake, Redshift, ClickHouse, MSSQL, Hadoop, dbt....Some might even go down the K8S and Terraform road, becoming more of a platform-focused DE...

There is just so much potential variance depending on where you work and who you are, that it doesn't sit well with me to shoe-horn everyone into a pattern of "First learn Airflow, then learn BigQuery, then learn Spark" when you could have a successful career and never touch a single one of those tools.

So, the reason I focused on SQL here, is that in my career consulting with hundreds of DE teams - the only consistent skill across every single team has been SQL. I do not believe SQL is all you'll ever need, but I do think its all you need to get started. It's the only skill I see being relavent regardless of which team you join, and that will still be relevant in 15+ years time.

It's a shame that ClickHouse Inc has not tried to solve the operational complexity of doing this. You might get some performance out of your queries, but at the cost of adding a bunch of new tools & infrastructure just so you can duplicate some data into yet another database. If all you offer is faster SQL, its a tough trade off to make.

We do track the real $ cost of time & materials, but tbh I don't think it's anything too exciting. I'll see what we can share!

Sure :D

I've worked on products across the spectrum, Enterprise software that releases twice a year, smaller stuff that ships once a month, and now a SaaS that releases many times a day. I don't have the data to compare, but I genuinely don't believe the rate of bugs was materially different between any of them.

We make faster changes, but the changes are much smaller, and so the surface area to test is smaller. The more changes you make, the more time you need to validate. If you make a weeks worth of changes, you're going to need an appropriately longer validation cycle than if you make 1 change. It scales, and in my experience, many products with slower release cycles aren't appropriately scaling up the validation time to match.

That said, this doesn't necessarily mean we're writing code and 30 minutes later its in production...there's still an iterative dev cycle with lots of validation happening...but if something is ready to go, ship it!

Hey, Al from Tinybird here (co-author of the post). We've made up to 20 production releases per day some days. It's transparent to our users, they aren't even aware the upgrade is happening, there's no upgrade button to hit, there's no downtime. We release often because we release small and fast. It's not like those 20 releases are always fundamentally changing the product. We would rather fix a minor bug or two and get that out to our customers ASAP, than hold on to it for a few months and drop a huge change. In a vast majority of cases, a user won't even consciously notice something changed.

Doing this kind of fast iteration has its risks, but it has its benefits too. We de-risk it, in part, by having extensive CI, which is why it was so important to us that the CI is fast & reliable.

Delivering larger, less-frequent updates has its own risks. You're not practising your release process as frequently, so it's a much bigger event. You're pushing many, many more changes in one go, so there's a lot more surface area for something to go wrong, and rolling it back is a much bigger job. And dropping many/bigger changes to the user experience is much more noticeable.

Again, this isn't the right process for everyone, but it works for us and its how we've managed to build a product that delivers value to our users.

This is awesome! We did something similar for our internal logging at Tinybird (which itself is built on ClickHouse) and I recently turned into it a (very simplified) Starter Kit that others can fork and use in their projects. https://github.com/tinybirdco/log-analytics-starter-kit

Rather than writing to files and using an agent like beats to tail them, it sends logs directly from the application code with a basic POST request. Obviously, you could just as happily tail the file and forward the logs, but this approach reduced the footprint of tools and made it work in serverless environments.

Sorry!

Hey! We're hiring at Tinybird, this might be interesting for you https://www.tinybird.co/job-offers/backend-engineer?

Hey! You might be interested in one of the roles we have open at Tinybird (we're fully remote). We're building a high-performance data platform ontop of an open source database (ClickHouse in C++).

https://www.tinybird.co/job-offers/clickhouse-developer

https://www.tinybird.co/job-offers/backend-engineer

Tinybird | Remote | Frontend Engineer | Backend Engineer | Open-source ClickHouse Engineer | SREs | Developer Advocates | Events Management | Account Executives | Full time | tinybird.co

Tinybird is a realtime analytics platform built on top of open source ClickHouse. We go beyond just a managed database, to create a service that makes it easy to build fast, scalable applications over data.

We are a FULLY REMOTE company with no offices, everyone is welcome!

- ClickHouse Developer (C++) https://www.tinybird.co/job-offers/clickhouse-developer

- Backend Engineer https://www.tinybird.co/job-offers/backend-engineer

- Jr Backend Engineer https://www.tinybird.co/job-offers/junior-backend-engineer

- Sr Site Reliability Engineer https://www.tinybird.co/job-offers/senior-site-reliability-e...

- Developer Advocate https://www.tinybird.co/job-offers/developer-advocate

- Account Executive https://www.tinybird.co/job-offers/account-executive

- Events & Marketing Manager https://www.tinybird.co/job-offers/events-marketing-manager

If you're looking for one that gives you more control, there is a starter kit from Tinybird to build your own GA replacement over a managed ClickHouse. It has the basics OOTB, but you can customise it as much as you want, capture different data, do different analytics, custom frontend, etc. https://github.com/tinybirdco/web-analytics-starter-kit