Breakout Session Presented by Dan Robinson of Heap at PGConf Silicon Valley 2015 on HackerTube: recommended by 4 Hacker News commenters across 5 separate months, 2016–2020.
talk · advanced · dated
Dan Robinson, then CTO of Heap, explains how Heap runs its product analytics backend on PostgreSQL and CitusDB. It covers conversion funnels, retention and cohort queries across tens of billions of events and 50 TB of data, plus Postgres and Kafka together and the indexing strategy.
HN commenters point to it as a first-hand overview of Heap's Postgres/Citus architecture and its distinctive indexing approach.
“Dan from Heap Analytics has a great talk about how they use Postgres and Citus.”
8 Hacker News comments link this video. Each one links back to its thread.
I was just watching this[0] excellent talk from one of the Heap Analytics engineers on their use of CitusDB + Postgres to do event processing. What use-case would TrailDB be the obvious, hands-down way to go (vs maybe Postgres + Citus)? [0]: ▶ this video
in “TrailDB – An Efficient Library for Storing and Processing Event Data” · View on HN
You need a queue in front of your database irregardless of write latency. Otherwise you tie your availability to database availability as downtime (even for upgrades) is often unavoidable and network problems are common. Dan gave a pretty good talk about the high-level details of how/why postgres a couple of years back: ▶ this video . One reason of why pg is that SQL is really powerful for building complex queries.
in “Running 10M PostgreSQL Indexes in Production” · View on HN
Regarding a), a sharded postgresql (i.e. with citus data) can easily accomodate that workflow with just a tiny bit of extra overhead. Re c) this strongly depends on the usecase, I've seen companies use a) to avoid split-brain problems and having to manage two data pipelines to great success at similar scales. You might find ▶ this video interesting.
in “How Discord Stores Billions of Messages Using Cassandra” · View on HN
Keep in mind that Heap collects a huge amount of data and has a huge dataset. I like this[1] talk, it gives you an overview of their architecture. Great post though, thank you for sharing. [1] ▶ this video
in “When to Avoid JSONB in a PostgreSQL Schema” · View on HN
If you haven't already seen it, Dan from Heap Analytics has a great talk about how they use Postgres and Citus. ▶ this video
in “Building a Scalable Postgres Metrics Backend Using the Citus Extension” · View on HN
I'm curious as to how you plan to scale PostHog to larger users. As the person who scaled Heap, here is my honest opinion of this. I think there is going to be a huge challenge ahead in scaling query performance. This was perpetually a challenge at Heap and was for a long time the main limitation on Heap's growth. The challenge was tough enough for Heap and PostHog is going to be at a huge disadvantage due to the lack of multi-tenancy. When you use Heap, your data is stored across Heap's entire cluster of machines. When you run a query, that query is ran simultaneously against every single machine in Heap's cluster. Even though your data may be taking up something like .1% of the total disk space, when you run a query, 100% of the disk throughput of Heap's cluster will go to processing your query. It's not an overstatement to say this alone results in a >50x improvement to query performance. I honestly think Heap wouldn't be possible without multi-tenancy. It's hard enough as is to get queries that process multiple terabytes of data to return in seconds when you have a fleet of dozens of i3s available. I'm not sure how you would do that with a fleet a tiny fraction of that size. If you're curious about Heap's infrastructure, Heap's CTO, Dan Robinson, has given a number of talks on how it works[0][1]. That's not to say that PostHog won't work for anyone. I previously tried (and failed) to start a company based on optimizing people's Postgres instances. One of the big takeaways
in “Launch HN: PostHog (YC W20) – open-source product analytics” · View on HN
As of right now, our schema is literally: user_id | event_id | time | data where data is a JSONB blob that contains every other piece of information about an event. Currently, we get a row estimate of one for pretty much every query. We've been able to work around the lack of statistics by using a very specific indexing strategy (discussed about in a talk Dan gave[0]) that gives the planner very few options in terms of planning and additionally by turning off nested loop joins. We are planning on pulling out the most common properties that we store in the data column, which will give us proper statistics on all of those fields. I am currently experimenting with what new indexing strategies we will be able to use thanks to better statistics. [0] ▶ this video
in “When to Avoid JSONB in a PostgreSQL Schema” · View on HN
That get's into our indexing strategy which Dan talks about in this talk[0]. Currently our tables aren't completely insert only, so a BRIN index wouldn't work for us, as one row in the wrong place can cause a huge amount of extra reads. [0] ▶ this video
in “When to Avoid JSONB in a PostgreSQL Schema” · View on HN