We want PostHog to become the first choice for product analytics at any scale. To do that, users should have a smooth experience exploring their product data - including not waiting minutes for queries to load.
In this post, I’m going to break down the why’s and how’s of several major performance improvements we've made in the last few months to achieve that goal.
This article is part of our A Universe of New Features launch week series
The free-form querying experience in PostHog allows you to ask questions about your Trends, Funnels, Retention, and Cohorts with complicated filtering to top it off.
These queries are processed by ClickHouse, where event, user, and group data is stored in a raw format without any preaggregation. ClickHouse excels at analyzing billions of events in seconds even with complex filtering.
However, as awesome as ClickHouse is, nothing is without sharp edges and trade-offs.
PostHog allows users to send and analyze an arbitrary number of event and person properties with their data. We store these properties as JSON-encoded string columns in our tables.
One of the first issues we saw after moving to ClickHouse was that, for our largest users, filtering by properties was slow.
Benchmarking these queries using flamegraphs showed that the slowness came from two things: reading JSON properties from disk and (to a lesser extent) parsing it during query-time.
We ended up materializing the most used properties into new columns, leveraging the strength of a columnar database without running into column number limits.
Specifically, the new materialized columns are fast to read from disk as they compress really well and ClickHouse can skip parsing JSON entirely during queries.
On our PostHog Cloud setup, we saw this feature improve query performance by 55% on average, with the p99 improvement being 25x.
Over time, for larger PostHog users with over 10 million visitors, some simple queries like a count of unique users started timing out or running into memory errors.
We narrowed this down to one particular JOIN in our system:
This JOIN is as complicated as it is due to a restriction from ClickHouse: updating data is expensive. In ClickHouse, updates require rewriting whole parts of the table instead of individual rows.
Instead, the recommended approach is to use a ReplacingMergeTree or CollapsingMergeTree table engine and handle updating logic at query-time.
During data ingestion, when a given distinct_id had its person_id changed, PostHog emits a row with is_deleted=1 for the old person_id and a new row with is_deleted=0. The above query would then resolve the distinct_id => person_id mapping at query time.
However, in practice, this query was slow and used up too much memory, due to needing a subquery to aggregate data correctly. It also had subtle issues with using timestamps for versioning, which was problematic when ClickHouse encountered equal timestamps.
After noticing the problem, we realized we didn't need to actually emit rows with is_deleted=0 to behave correctly, and could move to an alternative schema, which can be queried as follows:
For PostHog users with over 10 million visitors, this sped up queries previously bottlenecked on this JOIN by up to 10x.
While debugging other performance issues, one question we kept asking is whether our data is laid out optimally for performance.
PostHog uses a ClickHouse MergeTree table engine to store event data on disk. MergeTree tables can have an ORDER BY clause, which is then used by ClickHouse to store the data in a sorted format on disk and to create a sparse index of the data. The sparse index can then be used to skip reading data during queries.
Our ORDER BY clause originally looked something like this:
With that in mind, let’s consider this simplified query counting the number of users who had pageviews within a given time range:
When executing this query, ClickHouse can leverage data being sorted and the sparse index to skip reading most of data from disk. In this case, events from other projects and organizations and events from months other than March.
However, almost all of our most time-sensitive queries in PostHog also filter by event type. After measuring and confirming this, we updated the ORDER BY clause to the following one:
This resulted in a roughly 23% query speed up on average. The best trick for performance optimizations is to skip doing unnecessary work.
Changing how data is sorted on disk is not cheap when you have billions on billions of events. For example, on PostHog Cloud, this took five separate attempts and multiple weeks in total to finish. In addition, PostHog has a lot of self-hosted users at various degrees of scale and technical skill who would need to repeat this process.
To tackle this problem, we ended up building a new async migrations system, which safely runs these long-running operations at scale with the press of a button, while handling common edge cases and keeping the platform up and usable.
As mentioned, PostHog can be self-hosted by our users. However, getting it working smoothly across a wide range of deployments at scale keeps our infrastructure team hard at work.
Some features coming in PostHog 1.34.0 (releasing on Thursday) which affect performance for self-hosted users are:
Performance work is never complete and PostHog has a lot of work ahead of us to make answering questions about your product fast, no matter your scale.
Some projects currently in the pipeline are:
Interested in chatting about ClickHouse performance or working on similar problems? Send me an email: karl+perf@posthog.com or join our community page.
Subscribe to our newsletter
Read by 100,000+ founders and builders
We'll share your email with Substack
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。
