Five months ago, we rebuilt our storage on DuckDB alternatively of ClickHouse. We did it since a shared ClickHouse collection couldn't grip hours-long modeling queries, and it lacked a cost-based query optimizer. DuckDB gave all client their own instance, and tools akin dbt could nexus complete Postgres. We're motionless fans.
But we underestimated how big and complex our customers' workloads would get. We didn't desire them to have fair a fine experience, we wanted the finest cognition you can get whenever you pay person alternatively to run your warehouse. It should activity at any scale, at a fair price.
So we spent the final three months re-architecting it, this period without DuckDB.
We affection DuckDB. It's likely the finest single-node tool for analyzing heaps of data, and that node can be your laptop. It's sturdy and reliable. It nearly continually finishes your query, and if it runs out of memory, it spills to disk.
The identical features that are awesome locally gave us problems in the cloud. Customers ran additional complex queries than we expected, so we had to add huge instances. Adding disks was an option, but queries would have been too dilatory and costly to be viable.
To run a single-node motor safely in the cloud, we have to size the employee before the query arrives. The ergonomics current are wonky. We couldn't discover a fine way to inform customers what resources their queries would need. Predicting CPU and recollection for a particular query was equal harder.
Users shouldn't have to concern concerning this. They have to be capable to run any they need to get their activity done. Our job is to grip the remainder and create it as predictable as possible.
We hit another bottlenecks too, akin fetching many of data from S3 without async fetching. DuckDB lone added that recently.
DuckLake is built for compatibility, not scale
To use DuckDB as a fully-fledged data warehouse, we installed the DuckLake expansion by default. It is a new lakehouse format from the identical creators and follows the identical principles as DuckDB: keep things uncomplicated and run on nearly any setup. That plant fine up to a point, but at PostHog's scale, we needed item designed for distributed measure from the start.
DuckLake's catalog schema has to remain portable throughout many SQL databases, which limits engine-specific optimizations. At our scale, a single-table compose could obtain additional than three minutes since all commit re-read the data for the complete catalog.
We tuned DuckLake alongside array indexes and akin tweaks. But to keep offering DuckLake, we had to remain 100% compatible alongside its existing, "official" schema. This remaining us alongside small optimization options.
Another issue is that complete 99% of the catalog's data rows belonged to tables that no longer existed. Cleaning them up made writes 30 to 50 times faster, but the rows kept expanding back.
We additionally spent a lot of period on the figure of connections DuckLake opens to metadata databases. That hurts on shared Postgres instances, anywhere all association has a genuine cost.
DuckLake at PostHog didn't commencement small and ramp up. We completely committed and put all of PostHog in a DuckLake to dogfood as the archetypal customer.
When we moved off it, it held concerning 2 PB: approximately 1 trillion rows and 15.3 myriad snapshots. It bankrupt in many of ways that were difficult to fix inside DuckLake's scope.
We'd fairly have contributed to the existing community and helped it grow. But we couldn't district changes as accelerated as we needed, so we ended up carrying patches in a fork which defeats among the chief benefits of using DuckLake: the community.
Speaking Postgres is harder than it sounds
Offering straightforward Postgres connections complete harbor 5432 appears akin a awesome idea. In practice, it was much harder to measure than HTTP.
Missing reconnects and managing TTLs and idle connections at measure is hard, and long-running connections are expensive. There's additionally extremely small tooling for burden balancing pgwire compared alongside HTTP. Compare the ergonomics of an AWS NLB alongside an ALB and you'll see what we mean. HTTP headers additionally create routing in Kubernetes much additional flexible.
There's additionally an endless catalog of Postgres clients, and all expects its own niche Postgres behavior. Customers kept asking us to fix their client or library. We spent a lot of period shipping many of those fixes, but safety never got fine enough. We'd fairly expend our period construction an outstanding storage than chasing the lengthy rear of Postgres compatibility.
We've learned not to hype the tech rearward a storage so much. What counts is the cognition it gives you.
Still, we're blessed to portion what we're cooking next. We rebuilt everything about our own sensation of Trino and created our own open array format, HogLake. And yes, we're evaluation it at PostHog's petabyte measure first. We made it firm before handing it to scheme partners.
If your current storage is dilatory or expensive, let's talk, particularly if you have a lot of data to query. We're continually looking for scheme partners.
