[HN Gopher] Lessons learned from sharding Postgres at Notion
___________________________________________________________________
Lessons learned from sharding Postgres at Notion
Author : craigkerstiens
Score : 178 points
Date : 2021-10-06 18:50 UTC (4 hours ago)
(HTM) web link (www.notion.so)
(TXT) w3m dump (www.notion.so)
| capableweb wrote:
| Many companies I worked with stopped working with Notion in the
| past because of performance problems, the application was simply
| too slow. Has this changed with the new sharded architecture, as
| I haven't used it since probably last year?
| alberth wrote:
| Curious, what app have you seen people migrate too?
| methyl wrote:
| I can notice the performance no longer being the problem for
| me. The only gripe I have with Notion is too much flexibility,
| it's easy to completely mess something up even by accident.
| lucis wrote:
| You mean editing documents you're just taking a look?
|
| Notion has a lock feature [1] on a document-level.
|
| [1] - https://www.notion.so/Lock-page-
| content-d2b995727c0b483f9f35...
| sergiotapia wrote:
| Agreed, our Notion ended up being a soup of disparate
| outdated notes all over the place with no rhyme or reason
| across a 480+ employee business.
|
| I'm founding engineer of a new startup now and I advocated
| for Guru. https://www.getguru.com/
|
| I like how information is marked stale/verified and the
| permission are much tighter. The data ladder is also better
| structured. I'll see how it works as our headcount increases.
| hinkley wrote:
| From personal experience (not with Notion) once you fall behind
| it's hard to catch up, and nobody declares an emergency until
| it's already happening.
|
| Doing big surgery takes time, meanwhile your workload continues
| to grow and grow. It's not uncommon for a major win to only
| reset the clock by 6-12 months, and then you either have to run
| to the next one, or you have two teams working on separate
| angles at the same time. All the while people are learning as
| they go because the business settled on catch-up instead of
| capacity planning.
|
| Being a little successful can be tough.
| tempest_ wrote:
| The app is a lot more snappy, not super fast, but usable.
|
| Search is still pretty slow.
| craigkerstiens wrote:
| 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.
| ablekh wrote:
| I'm not sure what you meant by "online rebalancer" above, but
| _shard rebalancer_ in Citus _is_ open source since March 2021:
| https://www.citusdata.com/blog/2021/03/13/scaling-out-
| postgr....
| pykello wrote:
| Ex-Citus here. The open sourced shard rebalancer blocks
| writes to the shard being moved. Online rebalancer (closed
| source) uses logical replication and doesn't block writes to
| shards being move, except for a brief period. Everything is
| the same except how shard moves are implemented.
| ablekh wrote:
| Understood. Thank you for the clarification.
| craigkerstiens wrote:
| Yeah, echo'ing on here to be extra clear. What is open-
| sourced holds a write-lock while rebalancing. So that it
| rebalances sure, but it's only marginally better than a
| dump/restore. You can still read yes, but I'm not sure of
| many applications that can be okay with no writes flowing
| to a table for hours while rebalancing is happening.
| dkhenry wrote:
| Vitess is pretty close to magic for a lot of use cases. The
| downside of its rebalancer is that the re-sharding process
| requires a full expansion of the shards that are being re-
| balanced, which is to say if you have two shards [-80] and
| [80-], and you want to split out part of the first shard you
| need to provision two new shards [-40] and [40-80], let the
| data copy into them, and then you can mark them as active and
| delete the old [-80] shard. In this brave new cloud world, that
| not as much of an issue as it would have been in the old data
| of your own datacenter.
|
| The big problem everyone I have talked to about sharding runs
| into is managing the shards as you expand. In this case it
| looks like notion over sharded so they can spin out up to 480
| physical nodes, but when they need the 481 it is going to be a
| nightmare, thats what Vitess gets you for free, expand to any
| number of shards and just never worry about it again
| alberth wrote:
| >>" Workspace ID/Tenant ID/Customer ID, having your data
| grouped that way makes it very shardable"
|
| Dumb question, if someone starts off by grouping their data
| only by Customer ID and then later needs to shard. Couldn't all
| of the sharding problems go away if they simply created a new
| Customer_Tenant table to map customer_id into tenant_group_id
| craigkerstiens wrote:
| Sorry, wasn't clear enough, tenant ID = customer ID =
| workspace ID. There is often a benefit though to
| materializing that ID onto all child tables. If you take a
| salesforce.com example, every customer has an "orgID", from
| there have accounts and accounts have opportunities. It's not
| common for people to put the "orgID" on the opportunity from
| a data modeling perspective, but when it comes to sharding it
| gives you a big win.
|
| If you have that tenant discriminator on all tables then it's
| easy to route it to the appropriate physical and logical
| shard right away vs. having to do some DB join first in your
| request to figure out where it goes.
| alberth wrote:
| Are you suggesting creating entire database schemas
| specific to a customer and put the customer/orgID in all
| table names themselves?
| slownews45 wrote:
| No - you put customer ID column in every table
| craigkerstiens wrote:
| Yep, exactly this. By having customer id on every table
| it lets you know how to route appropriate queries and
| construct them as well.
| louwrentius wrote:
| I still wonder so hard what a single bare metal server would have
| done in terms of performance. [0]
|
| May have saved months of planning that may never have been
| required.
|
| [0]: https://yourdatafitsinram.net/
| 0xbadcafebee wrote:
| This can quickly become untenable depending on the availability
| and contractual requirements you have. You're literally
| building on a single point of failure. And if it's really big
| and you design wrong, recovery could take _weeks_.
| louwrentius wrote:
| Put a slave next to it, that's not the point. AFAIK this is
| what stackoverflow still does and it works for them. Why not
| for notion?
| notJim wrote:
| Notion may have a much higher proportion of write volume
| than stackoverflow, given that most of stackoverflow's
| traffic must be from people who come in from Google, read
| the answers, and leave. Notion is productivity app, so I
| would guess most people are actively writing.
| theodric wrote:
| I sharded in my pance
| sgarrity wrote:
| The Notion blog has had some quality posts, but it seems to be
| missing an RSS feed.
|
| You can subscribe via email (no thanks), and I realize there are
| some good ways to turn an email subscription into a feed
| (Feedbin.com handles this nicely). Still, I thought some public
| shaming here might encourage the to build this basic blog feature
| (that would help people follow their company!).
| mbell wrote:
| 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?
| StreamBright wrote:
| I would try to use a simple k:v system that is much easier to
| scale. Even S3 would be a good candidate. Maybe I am missing
| the point.
| endisneigh wrote:
| On a related note, how far can a single Postgres instance get
| you?
|
| If you had some behemoth with 32TB ram and 1PB storage could all
| of motion fit?
|
| I'm obviously ignoring the obvious single point of failure here,
| but sometimes the simplicity could be worth it if you're willing
| to handle that.
|
| I'd be curious to hear about a site that's in the Alexa 1000
| architected how I describe.
| aeyes wrote:
| > If you had some behemoth with 32TB ram and 1PB storage could
| all of motion fit?
|
| Postgres has a bunch of low level locks and buffers (protected
| by locks) which are essentially single threaded. So even if you
| had a 500 CPU instance at some point you'd not be able to get
| more throughput out of it.
|
| Of course it also depends on what you are doing. Large tables
| with high update rate are harder to handle than large tables
| which are insert only. My personal opinion is that Postgres
| tables with >100GB data (without indexes) are a starting to be
| a pain to work with, no matter how much RAM, CPU or IOPS you
| have.
| endisneigh wrote:
| Is there a relational database that could efficiently
| leverage such a single powerful machine?
| haneefmubarak wrote:
| I imagine you want NUMA aware software specifically
| designed for multi-socket systems; off the top of my head
| the ones I've heard of are IBM DB2, SAP HANA, and Microsoft
| SQL Server -- although I've had a chance to actually use
| any of them.
| garrettf wrote:
| Howdy all, author here! Sharding our Postgres monolith was a huge
| undertaking for a small team. We got a huge boost from folks that
| joined within weeks before the switch-over date. If you're
| interested in thinking about problems like this, I'd love to
| chat. Plus we're also hiring across all roles--check my profile
| for details.
|
| I'm happy to answer questions about the project here, feel free
| to reply below.
| ClassAndBurn wrote:
| What were the limitations that required you to move all
| customers to a shared system at once?
|
| Could you have selected some workspaces with lower traffic to
| migrate first? That would have decreased the load on the
| primary, potentially speeding up the replication, which is a
| flywheel to enable more customers to migrate to shards.
| garrettf wrote:
| Good question, that was an option. The main motivating factor
| here was that vacuums were beginning to take dangerously
| long. O(weeks) to complete, independent of the load on the
| database. While migrating spaces in segments would have
| reduced the number of records future vacuums need to scan, we
| were already running against the clock to complete one vacuum
| prior to TXID wraparound[0]. To kick off replication for
| specific spaces we would have needed to write our shard key
| to all data owned by those spaces. That would further
| contribute to TXID growth, and was not something we were
| comfortable doing.
|
| At the end of the day, this is something we could have
| explored in more depth, but we were ultimately comfortable
| with the risk tradeoff of migrating all users at once vs. the
| consequences of depending on the monolith for longer, largely
| thanks to the effort we put into validating our migration
| strategy.
|
| [0] https://blog.sentry.io/2015/07/23/transaction-id-
| wraparound-...
| gunnarmorling wrote:
| Nice write-up! Two questions:
|
| - Can you share details on the routing? I.e. how does the app
| know which database + schema it needs to go to for given
| workspace?
|
| - Did you consider using several databases on the same Postgres
| host (instead of schemas within a single database)? Not sure
| what's better really, curious whether you have any thoughts
| about it.
|
| Thanks!
| garrettf wrote:
| > Can you share details on the routing?
|
| All in the application layer! All of our server code runs
| from the same repo, and every Postgres query gets routed
| through the same module. This means that it was relatively
| easy to add a required "shard key" argument to all of our
| existing queries, and then within our Postgres module consult
| an in-app mapping between shard key range and DB+schema.
|
| Plumbing that shard key argument through the application was
| more difficult, but luckily possible due to the hierarchical
| nature[0] of our data model.
|
| > Did you consider using several databases on the same
| Postgres host
|
| If I recall correctly, you cannot use a single client
| connection to connect to multiple databases on the same host,
| and so this could have ballooned our connection counts across
| the application. This is not something we explored too deeply
| though, would love to hear about potential benefits of
| splitting tables in this way.
|
| [0] https://www.notion.so/blog/data-model-behind-notion
| gunnarmorling wrote:
| Ah yes, good point about connections being DB-specific. The
| schema approach seems more light-weight in that regard.
| Thanks!
| rednet wrote:
| Followup question: does the sharding happen within the app
| that talks to the DB, or do you shard traffic before it hits
| them? In the former case the total number of DB connections
| required presumably grows something like n^2.
| bamazizi wrote:
| Curious about thought on using something totally different like
| column oriented db, like ClickHouse, would've suited a block data
| oriented app like Notion better?
|
| Does anyone with experience have any thoughts in favour or
| against such implementation?
| dilyevsky wrote:
| Totally different usecase with clickhouse - the point queries
| there still slower than citus but it kicks its ass on multi row
| aggregations. For auto-sharded oltp db cockroachdb or tidb seem
| like way better options
| akarki15 wrote:
| +1. First thought i had when i saw this was like why is notion
| using a postgresdb in first place- i can see it being used for
| customer data (which probably doesn't require sharding) but for
| the content itself, a nosql or colum oriented db is better fit.
| the question i have is what is notion storing in these sharded
| postgresdb?
| notJim wrote:
| Overall this sounds very similar to Etsy's sharding setup, which
| I think descends from Flickr's sharding setup. Interesting that
| this is still the way to do it.
| alberth wrote:
| >> "Why 480 shards? ... 480 is divisible by a lot of numbers".
|
| This is an important point, doing things divisible by _12_ gives
| you a lot of flexibility. It's not a coincidence both time
| (clocks) and degrees (360) are multiples of 12.
| infogulch wrote:
| Numberphile made a video about "highly composite numbers" /
| "anti-primes", which are numbers that have more factors than
| all smaller numbers. https://youtu.be/2JM2oImb9Qg
|
| There is an oeis sequence of them that starts "1, 2, 4, 6, 12,
| 24, 36, 48, 60, 120, 180, 240, 360, 720, 840...", which notably
| does not include 480. https://oeis.org/A002182
| jedberg wrote:
| It's true that 480 isn't on the list, but you'll notice there
| is a big skip in the list between 360 and 720, so if you want
| something in between, 480 is a good choice since it's still a
| multiple of 12.
| infogulch wrote:
| Yeah, to be clear, I think there are many important factors
| that go into choosing a shard count, and blindly jumping to
| something just because it's some platonic mathematical
| ideal seems naive. I just thought this is a neat related
| fact and is probably a good starting point if you're in the
| process of choosing a shard count.
| mikepurvis wrote:
| But 480 is 2*240, so it's definitely a close contender.
| alberth wrote:
| That's super interesting. It's counterintuitive but 480 has
| the same # of factors that 360 has (which is 24 factors)
|
| https://www.calculatorsoup.com/calculators/math/factors.php
| ulucs wrote:
| so it's weakly highly composite?
| smolder wrote:
| I think I you can still say it's highly composite, but
| doesn't qualify as an anti-prime when it isn't the lowest
| number with that many factors.
| moralestapia wrote:
| This is the kind of engineering that it's almost impossible
| to screen in interviews (particular for HR-minded pals) and
| is a big part of what makes a 10x-100x engineer (which
| definitely DO exist).
|
| Obviously not this thing just by itself, but this sort of
| knowledge, applied to everyday decisions that get compound
| over time.
| flowerlad wrote:
| 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.
| zozbot234 wrote:
| PostgreSQL supports native sharding out of the box. It's
| "manual" in that you have to set up table partitions and
| foreign data access yourself, but that's arguably still
| preferable to hacking together the whole thing as a pure
| application-level concern.
| nosefrog wrote:
| What open source databases support automatic sharding? I think
| their only other option would have been something like
| cockroachdb, which probably wasn't very mature when notion
| started.
| flowerlad wrote:
| Check out Implementations section on this page:
|
| https://en.wikipedia.org/wiki/Shard_(database_architecture)
___________________________________________________________________
(page generated 2021-10-06 23:00 UTC)