PostHog Rebuilds Data Warehouse on DuckDB over ClickHouse
PostHog Rebuilds Data Warehouse on DuckDB over ClickHouse
Summary
PostHog has announced that it has rebuilt its v2 data warehouse on top of DuckDB, replacing ClickHouse as the query engine for analytical warehouse workloads. This architectural shift addresses the challenges of multi-tenancy, declarative query optimization, S3/delta lake support, and data stack tool integration (e.g., dbt) by using fully single-tenant DuckDB instances and a Postgres Wire protocol endpoint.
What happened?
PostHog officially transitioned from ClickHouse to single-tenant DuckDB instances for its query engine. This new architecture addresses multi-tenancy issues by spinning up isolated DuckDB instances that sleep when idle. It translates the Postgres wire protocol for query communication, while storing the actual data in DuckLake on Amazon S3. Discussions across developer communities like Hacker News indicate high interest in this novel approach.
Why it matters
While ClickHouse is an excellent real-time database for extremely large, flat tables, it often struggles with classic data warehouse workloads that require complex joins across multiple tables. PostHog’s migration highlights a growing trend: instead of running complex, shared multi-tenant clusters that are difficult to scale and secure, developers are increasingly turning to serverless single-tenant instances of embedded databases like DuckDB operating directly on cloud object storage (S3/Delta Lake).
Evidence
The official announcement from PostHog and the subsequent discussions on developer platforms validate this transition:
- PostHog Blog: Why we rebuilt our data warehouse on DuckDB over ClickHouse
- Hacker News Discussion: Why PostHog rebuilt its data warehouse on DuckDB instead of ClickHouse
- PostHog GitHub Repository
Analysis
The shift highlights the changing requirements of modern data architectures. While ClickHouse remains irreplaceable for event tracking (e.g., product analytics), data warehouse applications with dbt integrations often require relational queries and joins for which ClickHouse is not optimized. DuckDB offers state-of-the-art query optimization, seamless S3 integration via Parquet/Delta formats, and avoids typical performance interferences (the “noisy neighbor” effect) in shared clusters through the single-tenant model (“one DuckDB instance per tenant”).
Practical Takeaways
For developers and data architects, the following recommendations emerge:
- Separate Analytical Workloads: Evaluate whether real-time event streams (ClickHouse) and relational warehouse queries (DuckDB/S3) can be operated more efficiently in separate systems.
- Consider Single-Tenant Architectures: For SaaS applications, deploying dedicated, short-lived DuckDB instances per customer can simplify scaling and reduce infrastructure costs.
- Leverage Postgres Wire Protocol: Emulating Postgres allows existing BI and ETL tools (like dbt) to connect directly without requiring specialized drivers.
Open Questions
- How does DuckDB’s performance on S3 compare to ClickHouse over a long-term period with extremely massive datasets?
- What additional latencies are introduced by dynamically starting and stopping single-tenant DuckDB instances (cold starts)?