Five months ago, PostHog rebuilt its data warehouse on DuckDB. Now Benjamin Knofe-Vider explains in Why we’re moving our warehouse off DuckDB why they’re leaving it. It caught my attention for two reasons: a couple of weeks ago I wrote about DuckDB 2.0, and this isn’t the usual “this tool is useless” post. Quite the opposite, it starts by saying they’re still fans.
What it describes is more interesting than a critique: where the limits of an engine designed for a single machine lie when you turn it into a cloud service for thousands of customers.
Why they chose DuckDB
The starting point was ClickHouse. A shared ClickHouse cluster didn’t cope well with hours-long data modeling queries, and it lacked a cost-based query optimizer. DuckDB let them give every customer their own instance, and tools like dbt could connect by speaking the Postgres protocol. On paper, it fit very well.
Single node is a virtue… until it isn’t
Knofe-Vider says DuckDB is probably the best single-node tool for analyzing heaps of data, and that the node can be your laptop. It almost always finishes the query, and if it runs out of memory, it spills to disk.
The problem is that those same traits work against you in the cloud. Customers ran more complex queries than expected, so they had to provision huge instances. Adding disk was possible, but queries would have been too slow and expensive.
And here’s the key point of the post: to run a single-node engine safely in the cloud, you have to size the machine before the query arrives. They couldn’t find a reasonable way to tell a customer what resources their query would need, and predicting CPU and memory for a specific query was even harder. A distributed engine spreads the work once the query has arrived; a single-node one forces you to get it right in advance.
They also mention reading data from S3 without async fetching, something DuckDB “only added recently”. That’s DuckDB 2.0’s asynchronous I/O, precisely the improvement I was most interested in from that release. It came too late for them.
DuckLake at a trillion rows
To use DuckDB as a full warehouse they installed DuckLake by default, the lakehouse format from the same creators. And they went all in: they put all of PostHog into DuckLake, as the first customer of their own product. When they moved off it, it held about 2 PB, roughly a trillion rows and 15.3 million snapshots.
DuckLake follows DuckDB’s philosophy: keep things simple and run almost anywhere. Its catalog has to be able to live in many different SQL databases, and that portability limits engine-specific optimizations. The problems they describe are very concrete:
| Problem | Effect |
|---|---|
| Every commit re-read the statistics for the entire catalog | A single-table write could take more than 3 minutes |
| Over 99% of statistics rows belonged to tables that no longer existed | Cleaning them up made writes 30 to 50 times faster, but they kept growing back |
| Many connections opened to the metadata database | Expensive on shared Postgres instances |
They added indexes and other tweaks, but to keep offering DuckLake they had to stay fully compatible with the official schema, which left them little room. And since they couldn’t land changes upstream as fast as they needed, they ended up carrying patches in a fork. They say it themselves: that defeats one of the main benefits of using DuckLake, the community.
Speaking Postgres is harder than it sounds
The part that surprised me most is the protocol one. Offering direct Postgres connections on port 5432 sounds great: any client, any BI tool, any library just connects. In practice, it was much harder to scale than HTTP:
- Handling reconnects, TTLs and idle connections at scale is hard, and long-running connections are expensive.
- There’s very little load-balancing tooling for the Postgres protocol (pgwire) compared with HTTP. They point to the difference between an AWS NLB and an ALB.
- HTTP headers make routing in Kubernetes much more flexible.
- And there’s an endless list of Postgres clients, each with its own quirks. Customers kept asking them to fix their particular client or library, and however many fixes they shipped, coverage never got good enough.
It’s worth remembering every time a product boasts of being “Postgres compatible”. Being compatible with the protocol is one thing; being compatible with everything real clients expect from Postgres is something else entirely.
What’s next: Trino and HogLake
They’ve rebuilt everything on their own flavor of Trino, a distributed query engine, and created their own open table format, HogLake. They’re testing it first on PostHog’s own data, at petabyte scale, before handing it to design partners.
I like that the post itself says they’ve learned not to hype the tech behind a warehouse so much, and that what counts is the experience it gives. Coming from the people who announced the switch to DuckDB five months ago, it’s a lesson applied to themselves.
My takeaway
I don’t read this as a DuckDB failure. I read that DuckDB is excellent at what it’s designed for, one machine and one person or process analyzing data, and that turning it into a multi-tenant cloud warehouse is a different problem. As I mentioned when writing about AWS acquiring DuckLabs, at CARTO we use DuckDB too, so production experiences like this one are of particular interest to me. The server mode in 2.0 points towards the cloud, but PostHog’s story shows it’s only one piece: you still need to figure out how to size, how to distribute and how to scale a catalog.
And along the way, two ideas that apply beyond DuckDB. A single-node engine forces you to get resources right before execution, and in a multi-tenant service that’s very hard. And Postgres compatibility has a long tail that never shows up in demos and is very costly to cover.
Source: Why we’re moving our warehouse off DuckDB, Benjamin Knofe-Vider, PostHog.
