Lessons learned from sharding Postgres at Notion
In this technical retrospective (2021), @craigkerstiens shares practical architectural lessons learned from running, scaling, or migrating production software under lean constraints.
HN engineers debated the tradeoffs between cloud convenience and raw server economics, database indexing strategies, and resisting premature architectural complexity.
At Kami (kamiapp.com) we had to make a similar transition last year - we'd seen usage grow over 20x in a 6-month period on our classroom product due to the pandemic, and we were storing all our content in a single-primary Postgres database. We had plenty of headroom before but we were suddenly running out of write capacity, even after scaling vertically up to ~150 vcpu. Our workload is super write heavy so scaling out replicas wouldn't have helped much at all, and I was spending a lot of time manually vacuuming in our daily off-peak as we were burning transaction ids at an astonishing rate, ~800 million per day.
I looked at a bunch of options but ultimately managing a sharded database seemed like too much overhead for our small (but expanding!) dev team - so we decided to move our heavily loaded tables to CockroachDB instead, as since it's mostly Postgres compatible our transition would be easier, and it would be much easier to manage as it automatically balances load and heals. We're running cockroach on GKE and it's a really nice fit for running on kubernetes.
Ended up working well for us - we still have our smaller tables on PG but we want to move it all to Cockroach over time. There were some teething issues as we learnt what to monitor on it (read amplification is the big one here, though pebble has helped a lot vs the previous rocksdb), and it does need a lot of hardware at high scale (we're running 50 16-vcpu nodes for it) but overall I'm happy with it.
Slightly unrelated, but notion's unofficial python API is cool AF! https://github.com/jamalex/notion-py ↗
It's way more powerful than their official API. The official API can only interact with databases, which (as anyone who uses Notion knows) is a tiny subset of the overall things you want to use Notion for.
The unofficial Python API lets you have complete programmatic access to all notion pages.
The tradeoff is that certain things break. However, after reading the code, I found it quite easy to fix the problems I ran into (https://github.com/jamalex/notion-py/pull/345 ↗) and I suspect you'll be able to do the same. (I empathize with maintainers not prioritizing their open source work ahead of family life, business, etc.)
Was surprised that it was even possible to have a nice Python class for every possible Notion object, let alone control them and update them on the fly. I wish their official API would be as flexible. Maybe one day.
Notion seems like an interesting data storage problem. The vast majority of the data is in the `block` entity, which are organized into a sort of nested set, written/updated individually (user edits one at a time) but read in ranged chunks (a doc).
Off hand this seems like an almost worst case for PG. Since the updates to blocks could contain large data (causing them to be moved often) and there is one big table; it seems likely that the blocks for a single notion document will end up being non-continuous on disk and thus require a lot of IO/memory trashing to read them back out. PG doesn't have a way to tell it how to organize data on disk so there is no good way around this (CLUSTER doesn't count, it's unusable in most use cases).
Arm chair engineering of course - but my first thought would be to find another storage system for blocks that better fits the use case and leave the rest in PG. This does introduce other problems, but it just feels like storing data like this in PG is a bad fit. Maybe storing an entire doc's worth of block entities in a jsonb column would avoid a lot of this?
Anyone starting a tech startup will face this decision: Should I start with a database that has built-in support for sharding? Or should I just start with MySQL or PostgreSQL and defer the sharding question for later?
Notion chose to do manual sharding (aka application-level sharding). That's what you end up doing if you didn't choose a database that has built-in sharding from the get go, because it is extremely hard to switch to a different database technology. (Larry Ellison compares it to a catholic marriage -- there is no divorce!)
I skimmed through the article to find the critical piece of info I was looking for: rationale for doing manual sharding. The rationale supplied in this article is "we wanted control over the distribution of our data." That's a weak explanation. It's the kind of thing you say to justify the bad choice made earlier on: failure to choose a database that supports automatic sharding from the get go.
I'm curious about whether using Postgres native partitioning was considered? If so, I'd like to hear the rationale for not doing it.
It seems like you made the database sharding decision as a result of vacuum taking too long. Partitioning the problematic table (assuming you're storing "blocks" in 1 giant table) would have enabled per-partition vacuuming, which will avoid long running vacuum processes.
This is a great read and primer for anyone looking to shard and know they need scale. Having helped a ton of people shard their data, my advice still remains don't do it until you know you have to. The old Citus data blog is a great resource for some guidance around best practices and how to's even if not using Citus.
That said some of the things you can do from day 1 make things way easier when the time comes later. Workspace ID/Tenant ID/Customer ID, having your data grouped that way makes it very shardable and saves you a lot of work later on in the case you didn't have that in your data model.
I'm not sure I buy that Citus/Vitess are magic, both are reasonably clear how they work and you can dig into it. At the same time I'd weigh the downsides of Citus (can't speak to Vitess) in that the online rebalancer isn't open source so at that point it's a proprietary product.