Five months ago, Post Hog rebuilt its warehouse on DuckDB instead of ClickHouse after a shared cluster couldn't handle hours-long modeling queries and lacked a cost-based query optimizer. DuckDB gave every customer their own instance, and tools like dbt could connect over Postgres. We're still fans.
But we underestimated how big and complex customers' workloads would get. We didn't want them to have just a good experience, we wanted the best experience you can get when you pay someone else to run your warehouse. It should work at any scale, at a fair price.
So we spent the last three months re-architecting it, this time without Duck DB. We love Duck DB. It's probably the best single-node tool for analyzing heaps of data, and that node can be your laptop.
It's robust and reliable. It almost always finishes your query, and if it runs out of memory, it spills to disk. The same features that are great locally gave us problems in the cloud.
Customers ran more complex queries than we expected, so we had to add huge instances. Adding disks was an option, but queries would have been too slow and expensive to be viable. To run a single-node engine safely in the cloud, we have to size the worker before the query arrives.
The ergonomics here are wonky. We couldn't find a good way to tell customers what resources their queries would need. Predicting CPU and memory for a specific query was even harder.
Users shouldn't have to worry about this. They should be able to run whatever they need to get their work done. Our job is to handle the rest and make it as predictable as possible.
We hit other bottlenecks too, like fetching lots of data from S3 without async fetching. Duck DB only added that recently. To use Duck DB as a fully-fledged data warehouse, we installed the Duck Lake extension by default.
It is a new lakehouse format from the same creators and follows the same principles as Duck DB: keep things simple and run on almost any setup. That works well up to a point, but at Post Hog's scale, we needed something designed for distributed scale from the start. Duck Lake's catalog schema has to stay portable across many SQL databases, which limits engine-specific optimizations.
At our scale, a single-table write could take more than three minutes because every commit re-read the statistics for the entire catalog. We tuned Duck Lake with table indexes and similar tweaks. But to keep offering Duck Lake, we had to stay 100% compatible with its existing, official schema.
This left us with fewer optimization options. Another problem is that over 99% of the catalog's statistics rows belonged to tables that no longer existed. Cleaning them up made writes 30 to 50 times faster, but the rows kept growing back.
We also spent a lot of time on the number of connections Duck Lake opens to metadata databases. That hurts on shared Postgres instances, where every connection has a real cost. Duck Lake at Post Hog didn't start small and ramp up.
We fully committed and put all of Post Hog in a Duck Lake to dogfood as the first customer. When we moved off it, it held about 2 PB: roughly 1 trillion rows and 15.3 million snapshots. It broke in lots of ways that were hard to fix within Duck Lake's scope.
We'd rather have contributed to the existing community and helped it grow. But we couldn't land changes as fast as we needed, so we ended up carrying patches in a fork which defeats one of the main benefits of using Duck Lake: the community.
Source: Hacker News · Summarized by HeadlinesBriefing