The growing pains of database architecture | Hacker News Reader

The growing pains of database architecture

figma.com

193 pointskiyanwang53 comments

Since Figma has documents, and each document only has a limited amount of users working (writes) on them, it makes a lot of sense to horizontally shared those. Whereas other tables, for authentication and other user logic, it makes more sense to be vertically sharded. Though it could also make sense to horizontally shard them over regions.

> Though it could also make sense to horizontally shard them over regions. that is assuming the user who are editing the document are in the same geo region. How one shard the db, would depending on some internal stats of figma and how the applications are used.

Maybe I missed it from the article, but did they consider having multiple, independent instances of the entire document database for different clusters of users?

I would think that most documents tend to have a small-ish set of users.

From an earlier blog post[1], the system for synchronizing file/document state is independent from their database. As I understand it, their database is for metadata rather than actual document content.

> It’s worth noting that we only use multiplayer for syncing changes to Figma documents. We also sync changes to a lot of other data (comments, users, teams, projects, etc.) but that is stored in Postgres, not our multiplayer system, and is synced with clients using a completely separate system that won’t be discussed in this article. Although these two systems are similar, they have separate implementations because of different tradeoffs around certain properties such as performance, offline availability, and security.

This post[2] goes into more detail on how they spin up backend processes to serve as the source of truth while documents are open (we took heavy inspiration from it when building https://plane.dev, which aims to be an open-source implementation of that architecture.)

[1] https://www.figma.com/blog/how-figmas-multiplayer-technology... [2] https://www.figma.com/blog/rust-in-production-at-figma/

Very interesting to read, especially having done similar migrations it’s nice to see that the same choice is made by bigger players too (in terms of how to carry out this migration).

I was surprised to see that they had to cancel those ~10 queries that were in flight in the moment when they needed to switch over the query traffic. When doing this with ProxySQL, there was an option to: pause all connections such that they can’t create new transactions and queries, while not cancelling running txs/queries, and then wait for all ongoing txs/queries to finish, and then do the switch and unpause.

I was surprised that they went for database partitioning first.

Caching and optimization weren't mentioned at all, but I guess they already maxed out that path

I've been in situations like this where the cost of killing active queries was lower than the cost of pausing traffic (and having it potentially back up or time out) for the extra time it would take for those queries to finish.

Just because you can wait for them to finish, doesn't mean it's better to when you look at the cutover as a whole.

Ah indeed, this is a trade off.

Also, if you asked me to pick my poison: things get partially available / degraded for a long period of time, or there's a blip of full unavailability during a cutover, I'd pick the latter 9 times out of 10. I find people are pretty good about writing code to deal with "does it work y/n" but people are often a lot less good about "does it nominally work but is going so slow it will never complete / other things will time out in unexpected orders before this finishes / etc". Some of the worst incidents I've seen were "partial" outages that spanned a long time period until the right thing could be drained/kicked/whatever.

This took me a long time to accept in my career, but I do believe you've summarized this in a way that rings true for me as well.

Take that one big hit vs death by a thousand cuts!

This is a really good writeup of a very complicated topic. Thank you.

This kind of thing there's a lot that needs to be kept in mind, I've never done a live database migration before to this scale. I'm a beginner with distributed systems but I find them highly interesting.

I feel that along of the work that was done in this article should have been the responsibility of a Kubernetes operator.

My dream is multimaster databases that mean we don't need to have so much pain in doing database migrations. I wrote an eventually consistent Python socket server that asynchonously streams messages to its replicas. I use a client side timestamp to provide global ordering. In theory if two servers had differing system clocks, they could cause the causality of messages to be the wrong way round in a strict sense. But ALL replicas shall agree on the ordering, server agreement among replicas that are caught up is deterministic. If the servers are not caught up, a server shall serve a stale value or a value that only that replica knows about.

The database essentially uses event sourcing and takes the last write to determine victory.

Here's a diagram of a log where the cluster bounced between values, it's a very big diagram you'll have to zoom in. You can see states that were forks of the true cluster agreed value. https://github.com/samsquire/eventually-consistent-mesh/blob...

I wrote a Jepsen test that because it is not linerarizable, it fails.

My script abandons the C(onsistency) of CAP and maintains (A)vailability and (P)artition tolerance. It assumes the network shall heal and the replication shall resume. In PACELC theory in During normal operation it prefers Availability during Partitions and Else Latency in normal operations.

How do you build a system around a database where values can no longer be valid at any time? If I kick off a process in the database with a value that was only reported by one replica that was partitioned away from the majority of the network, what do I do?

My immediate thoughts are to create dependency relationships to values that are on the agreed path, data that is based on a failed branch is not valid.

https://github.com/samsquire/eventually-consistent-mesh

> How do you build a system around a database where values can no longer be valid at any time?

This is a difficult problem, and there aren't nice general solutions. If you read the original Amazon Dynamo paper, for example, there's a good discussion of why eventual consistency is OK for their applications (https://www.allthingsdistributed.com/files/amazon-dynamo-sos...), and more in Werner Vogel's CACM article from two years later (https://dl.acm.org/doi/10.1145/1435417.1435432).

There are conceptual frameworks (like CALM https://arxiv.org/pdf/1901.01930.pdf) that provide a way to reason through the behavior of systems under eventual consistency (and provide a path to data structures with behavior that's easier to reason about than last-writer-wins).

> My script abandons the C(onsistency) of CAP and maintains (A)vailability and (P)artition tolerance.

I'd recommend you check out "Rethinking Eventual Consistency" (https://www.microsoft.com/en-us/research/publication/rethink...). PACELC is a better tool than CAP for thinking about this stuff, but Figure 1 in that paper is even better. Specifically, there is no need to abandon either availability or consistency in the majority partition.

The confusion here comes from CAP's unhelpful use of "total availability", which isn't a system property that most datacenter applications care about. Mobile, IoT, and other frequently-disconnected applications do care about it.

To Figma blog team: Please fix your links. "Open in new tab" both opens it in new tab AND loads in current tab.

Why not distributed databases like CockroachDB?

CockroachDB is presumably what they’re referring to in:

> For Postgres-compatible NewSQL, we would’ve had one of the largest single-cluster footprints for cloud-managed distributed Postgres. We didn’t want to bear the burden of being the first customer to hit certain scaling issues

I find their claim a bit hard to believe.

It is in inherent of a good engineer’s thinking to always consider the worst. Problem is sometimes, it can come across as a boast, or out of touch. It’s neither. It’s really the manifest anxiety of the whole thing shitting its pants and falling over.

At some point failures start getting measured in millions of dollars per minute.

It's smart to be cautious.

"Why not distributed databases like CockroachDB?"

CockroachDB you have two options both equally horrible. Pay for a hosted solution or run your own. I would pick hosting my own, but that would be some work.

this seems like a non response. this is always the case regardless of the database you choose.

Yeah, with https://ydb.tech it would have been much smoother too.

A disclosure that you are part of the YDB team would have been appropriate: https://github.com/orgs/ydb-platform/people

Your comment is pretty unhelpful either way though.

Nice spam/sales link

I wonder what their query routing service at the end of the article does and what it’s use is? If they have vertically partitioned their database, shouldn’t their application queries also be split in such a way that they query data from the partitioned database and join them somewhere ?

I don't have any experience with RoR but your question piqued my curiosity and it seems like you can configure Rails to dynamically decide which database to connect to. That means that in case of a query routing service, your application backend can ask it which databases to connect to based on the requirement (say, a list of entities) and the routing service can provide the relevant connection details which the backend can use to connect without a predefined list of databases in its .yml file. That means your application doesn't have any knowledge of how many databases are there since that knowledge is centralized at the query routing service and can change as migrations happen.

Atleast that is what I guessed from some brief reading - I may be totally wrong.

It's pretty impressive they didn't have any of it until now.

Ah yes, PgBouncer or mysqlProxy. The idea makes sense. However, it never works. You can't push all your db traffic through a single vm (PgBouncer). Next! Someone on the team suggests 2 vms! This is not the path forward. What you need is a cloud supported load balancer to push all the traffic through and just health check the port 3306/5432.

1. Why can you not push all your traffic through a single PgBouncer? 2. You can have three PgBouncers and clients can have their own balancing of available database proxies as well, the entire migration is doable with multiple PgBouncers too - it’s just a bit more code to implement the pausing and the switch over for many instances simultaneously.

I am wondering if simply switching to AWS Aurora is an option here?

The growing pain of messing up the right click on links, is what it is!

Q: Do ALL your customers need to access EVERYONE ELSES data!?

For most businesses, one customer is only interested in their own data. So that's where you can scale out - put each customer on their own virtual instance.

CQ: What exactly do you mean with "virtual instance"?

CREATE DATABASE ${app}_${username}.

Okay, you use the username as the the key. What is the benefit over putting everthing in a partitioned database with partition keys?

You don't need to to sharding if there is no relations between users. Just spread them out on different servers.

If there is a relation, like who is friend with who, and not much else, you can lift out that into it's on service and scale it individually.

My take: have a few database instances (machines) each holding the data for a group of customers. With postgres you can even put different customers on the same database instances but in different schemas.

This way you get all the benefits of the relational model (you can use foreign keys, transactions consisting of multiple tables, etc.) and the performance benefits of additional machines that are not just read-replicas.

Centralized shared tables can be in a separate database which can also hold the mapping between customer-ids and database instances.

Only drawback is that management is more difficult -- backup, migrations, etc. Specifically, you need to handle the case where some customers have migrated their database and others had not yet.

Run a separate db instance for each customer. With virtualization you can run many instances on the same physical machine. Depending on the tech stack all instances can be light weight. We did this in the early 2000 and our code was slow and machines even slower, but we could still scale to over 1k instances cheaply by spreading them out between different machines and used DNS for "routing" and its faster then todays centralized services.

AFAIR, you can register in Figma as a single user, i.e., user = customer. So, one DB instance for each of the 4 million users [1]? Sounds like a lot of overhead.

[1] https://techcrunch.com/2022/06/08/figmas-dylan-field-will-di...

How is that different from horizontally scaling (which they said they considered but couldn't find a managed solution for)?

>Our developers use ActiveRecord to write these queries. Due to the dynamic nature of Ruby and ActiveRecord, it’s hard to determine which physical tables are affected by ActiveRecord queries with static code analysis alone.

Interesting, in other programming envs. Active Record is considered as "antipattern"

The "Active Record" pattern is used by many ORMs. Are you saying those ORMs use an antipattern?

That is perfectly possible.

Which ORM forces you to bloat your core/domain models with db methods like "user.Save()"?

e.g EF Core (C# ORM) doesnt

I would say that ORM's are an anti-pattern.

Do you have any proof ?

As a rule of thumb, I avoid using the word "proof" in this way. Proof is great as a mathematical noun, a publishing noun, or a baking verb. What you're asking for, based on your use of "any proof" is better encapsulated in the word "evidence". Evidence accumulates. Often encapsulated in units called "exhibits". Given some test, if greater than 50% of the evidence leans one way, then you have a preponderance of the evidence. If 75% leans that way, the evidence is "clear and convincing". If 99% leans one way, the evidence is "beyond a reasonable doubt".

In this case, we'd start with the test. From Wikipedia, "An anti-pattern in software engineering, project management, and business processes is a common response to a recurring problem that is usually ineffective and risks being highly counterproductive."

Then we look at the top answer on Stack Overflow (2)

=== Exhibit A ===

  But ORM can be a pain:

    You have to learn it, and ORM libraries are not lightweight tools;
    You have to set it up. Same problem.
    Performance is OK for usual queries, but a SQL master will always do better with his own SQL for big projects.
    It abstracts the DB. While it's OK if you know what's happening behind the scene, it's a trap for new programmers that can write very greedy statements, like a heavy hit in a for loop.

=== Exhibit B ===

  Do not try to write your own ORM, unless you are trying to learn something. This is a gigantic piece of work, and the old ones took a lot of time and work before they became reliable.

So, I have presented some initial evidence that tends to support the idea that an ORM is an anti-pattern. If you disagree, you are welcome to cite additional evidence to the contrary. But keep in mind the test elements.

I don't consider "learn something" is anti-pattern. It depends on quality of the library itself to know if it's worth learning.

Setting something up is similar. It depends on complexity of toolings.

Performance is also dependant on the quality of the generated native query.

It abstracts the DB, which i consider a good thing in long term, and of course, some ORM gives you enough escape hatches in a good way.

Overall, i love working with a "good" ORM rather than working directly with SQL (for example).

But the real world libraries is not good enough for the ORM ecosystem to have good faith on DX side.

ORM allows you to get away from writing stringly typed SQL and use correct types instead - reduces amount of boring mistakes

ORM allows you to focus more on modeling domain model instead of db model

ORM speeds up writing boring queries like insert or update

ORM can detect changes and update stuff accordingly

ORM prevents people from SQL Injections

ORM can manage migrations and db schema versioning

ORM allows you to change DBs to some extent easier

ORM still allows you to write raw query when you need it

Try C#s EF Core with LINQ.

I would suggest looking at jooq. It's a great orm.

I think JOOQ isn't a proper ORM, but it is splitting hairs on the definition. It calls itself an OOQ (object oriented querying)

ORMs become an antipattern when they try to abstract away the database completely by masking write operations and paging lists implicitly. Developers need to know precisely when these operations happen to manage performance.

JOOQ doesn't do that. Active record does. Hibernate does (but it's configurable)

I agree that jooq isn't a typical ORM. MyBatis is in the same camp. I really can't believe it took so long for people to realize old-school ORM solutions like Active Record and Hibernate were bad ideas. I cringe whenever I come across Hibernate code.

The "object oriented" in jOOQ Object Oriented Querying stands for an object oriented query model, not mapping to object oriented target data structures.

As a rails developer Inhave grown to despise active record and consider it an anti-pattern as well.

Reply on news.ycombinator.com