The rising pains of database structure

In 2020, Figma’s infrastructure hit some rising pains on account of a mixture of recent options, making ready to launch a second product, and extra customers (database site visitors grows roughly 3x yearly). We knew that the infrastructure that supported Figma within the early years wouldn’t have the ability to scale to satisfy our calls for. We had been nonetheless utilizing a single, giant Amazon RDS database to persist most of our metadata—like permissions, file info, and feedback—and whereas it seamlessly dealt with lots of our core collaborative options, one machine has its limits. Most visibly, we noticed upwards of 65% CPU utilization throughout peak site visitors as a result of quantity of queries serviced by one database. Database latencies turn into more and more unpredictable as utilization edges nearer to the restrict, affecting core person experiences.

If our database turned utterly saturated, Figma would cease working.

We had been removed from that, however as an infrastructure group, our objective is to determine and repair scalability points proactively earlier than they arrive near being imminent threats. We would have liked to plot an answer that would scale back potential instability and pave the best way for future scale. Plus, efficiency and reliability would proceed to be high of thoughts as we carried out that resolution; our group goals to construct a sustainable platform that permits engineers to quickly iterate on Figma’s merchandise with out impacting the person expertise. If Figma’s infrastructure is a sequence of roads, we are able to’t simply shut down the highways whereas we work on them.

We began with a couple of tactical fixes to safe a further 12 months of runway, whereas we set the muse for a extra complete strategy:

1. Improve our database to the most important occasion obtainable (from r5.12xlarge to r5.24xlarge) to maximise CPU utilization runway
2. Create a number of read replicas to scale learn site visitors
3. Set up new databases for brand new use circumstances to restrict progress on authentic database
4. Add PgBouncer as a connection pooler to restrict the impression of a rising variety of connections (which had been within the hundreds)

Whereas these fixes moved the needle, that they had limitations. By analyzing our database site visitors, we discovered that writes— like gathering, updating, or deleting information—contributed to a good portion of database utilization. Moreover, not all reads or information fetching may very well be moved to replicas on account of utility sensitivity to replication lag. So, from each a learn and write perspective, we nonetheless wanted to dump extra work from our authentic database. It was time to maneuver away from incremental modifications and search for a longer-term resolution.

We first explored choices for horizontally scaling our database. Many widespread managed options are usually not natively appropriate with Postgres, the database administration system we use at Figma. If we selected a horizontally scalable database, we might both must discover a Postgres-compatible managed resolution, or self-host.

Migrating to NoSQL databases or Vitess (MySQL) would require a posh double learn and write migration, and NoSQL particularly would additionally warrant vital application-side modifications. For Postgres-compatible NewSQL, we might’ve had one of many largest single-cluster footprints for cloud-managed distributed Postgres. We didn’t need to bear the burden of being the primary buyer to hit sure scaling points; we’ve got little management over managed options, so counting on them with out a stress take a look at at our degree of scale would expose us to extra danger. If not a managed resolution, our different choice was self-hosting. However since we had relied on managed options thus far, there could be vital upfront work to accumulate the coaching, data, and abilities our group would wish to assist self-hosting. It could imply a big operational value, which might take away from our give attention to scalability—a extra existential drawback.

After deciding in opposition to each paths ahead for horizontally sharding, we needed to pivot. Reasonably than horizontally shard, we determined to vertically partition the database by desk(s). As an alternative of splitting every desk throughout many databases, we might transfer teams of tables onto their very own databases. This proved to have each short- and long-term advantages: Vertical partitioning relieves our authentic database now, whereas offering a path ahead for horizontally sharding subsets of our tables in the future.

Our approach to partitioning

Earlier than we might start the method, nevertheless, we first needed to determine tables to partition into their very own database. There have been two necessary elements:

1. Affect: Shifting the tables ought to transfer a good portion of workload
2. Isolation: The tables shouldn’t be strongly related to different tables

To measure impression, we checked out common lively classes (AAS) for queries, which describes the typical variety of lively threads devoted to a given question at a sure time limit. We calculated this info by querying pg_stat_activity in 10 millisecond intervals to determine CPU waits related to a question, after which aggregated the knowledge by desk title.

Every desk’s diploma of “isolation” proved core as to whether it might be simple to partition. Once we transfer tables into a unique database, we lose necessary performance akin to atomic transactions between tables, overseas key validations, and joins. Because of this, transferring tables can have a excessive value with respect to how a lot of the Figma utility must be rewritten by builders. We needed to be strategic by specializing in figuring out question patterns and tables that had been simple to partition.

This proved to be troublesome with our backend tech stack. We use Ruby for the applying backend, which companies the vast majority of our internet requests. These, in flip, generate most of our database queries. Our builders use ActiveRecord to put in writing these queries. Because of the dynamic nature of Ruby and ActiveRecord, it’s exhausting to find out which bodily tables are affected by ActiveRecord queries with static code evaluation alone. As a primary step, we created runtime validators that hooked into ActiveRecord. These validators despatched manufacturing question and transaction info (akin to caller location and tables concerned) into Snowflake, our information warehouse within the cloud. We used this info to search for queries and transactions that persistently referenced the identical group of tables. If these workloads turned out to be pricey, these tables could be recognized as prime candidates for vertical partitioning.

As soon as we recognized which tables to partition, we needed to give you a plan for migrating them between databases. Whereas that is easy when carried out offline, going offline isn’t an choice for Figma—Figma must be up and performant always to assist customers’ real-time collaboration. We would have liked to coordinate the info motion throughout hundreds of utility backend situations, so they might route queries to the brand new database on the appropriate second. This could enable us to partition the database with out utilizing upkeep home windows, or downtime, for every operation, which might be disruptive for our customers (and in addition require off-hour work from engineers!). We wished an answer that met the next objectives:

1. Restrict potential availability impression to <1 minute
2. Automate the process so it’s simply repeatable
3. Have the power to undo a latest partition

We couldn’t discover a pre-built resolution that met our necessities, and we additionally wished the pliability to adapt the answer for future use circumstances. There was just one choice: construct our personal.

Our bespoke solution

At a excessive degree, we carried out the next operation (steps 3–6 full inside seconds for minimal downtime):

1. Put together shopper functions to question from a number of database partitions
2. Replicate tables from authentic database to a brand new database till replication lag is close to 0
3. Pause exercise on authentic database
4. Look forward to databases to synchronize
5. Reroute question site visitors to the brand new database
6. Resume exercise

Getting ready shopper functions appropriately was a big concern, and the complicated nature of our utility backends made us anxious. What if we missed an edge case that broke after partitioning? To de-risk the operation, we leveraged the PgBouncer layer to achieve runtime visibility and confidence that our functions had been configured appropriately. After partnering with product groups to make the applying appropriate with partitioned databases, we created separate PgBouncer companies to nearly break up site visitors. Security groups ensured that solely PgBouncers might instantly entry the database, which means shopper functions had been all the time related through PgBouncer. Partitioning the PgBouncer layer first would give shoppers leeway to route queries incorrectly. We’d have the ability to detect the routing mismatch, however since each PgBouncers have the identical goal database, the shopper would nonetheless efficiently question information.

As soon as we verified that functions are ready with separate connections for every PgBouncer (and sending site visitors appropriately), we’d proceed.

The “logical” choice

In Postgres, there are two methods to duplicate information: streaming replication or logical replication. We selected logical replication as a result of it permits us to:

1. Port over a subset of tables, so we are able to begin with a a lot smaller storage footprint within the vacation spot database (lowered storage {hardware} footprint can improve reliability).
2. Replicate to a database, which is operating a unique Postgres main model, which means we are able to carry out minimal-downtime main model upgrades with this tooling. AWS has blue/inexperienced deployment for main model upgrades, however the characteristic shouldn’t be but obtainable for RDS Postgres.
3. Arrange reverse replication, which permits us to roll again the operation.

The primary situation with utilizing logical replication is that we’re working with terabytes of manufacturing information, so the preliminary information copy might require days, if not weeks, to finish. We wished to keep away from this to not solely decrease the window for replication failure, but in addition the price of restarting. We thought of fastidiously coordinating a snapshot restore and beginning replication on the appropriate level, however a restore eradicated the opportunity of having a smaller storage footprint. As an alternative we determined to research why logical replication’s efficiency is so sluggish. We found that the gradual copy is a results of how Postgres maintains indexes within the vacation spot database. Whereas logical replication copies rows in bulk, it inefficiently updates indexes one row at a time. By eradicating indexes within the vacation spot database and rebuilding the indexes after the preliminary copying of knowledge, we lowered the copy time to a matter of hours.

Through logical replication, we had been in a position to construct a reverse replication stream from the newly partitioned database and again to the unique. This replication stream was activated simply after the unique database stopped receiving site visitors (extra on this beneath). Modifications to the brand new database could be replicated again to the outdated database, and the outdated database would have these updates within the occasion we rolled again.

The critical steps

With replication solved, we discovered ourselves on the essential steps of coordinating question rerouting. Day by day, hundreds of shopper companies question the database at any given time. Coordinating throughout this many shopper nodes is vulnerable to failure. By performing our sharding operation in two phases (partitioning PgBouncers, then information), the vital operation of partitioning information would solely require coordination throughout a handful of PgBouncer nodes serving the partitioned tables.

Right here’s an outline of the continuing operation: We coordinate throughout nodes to cease all related database site visitors solely briefly to ensure that logical replication to synchronize the brand new database. (PgBouncer conveniently helps pausing new connections and rerouting.) Whereas PgBouncer pauses new connections, we revoke shoppers’ question privileges on the partitioned tables within the authentic database. After a quick grace interval, we cancel any remaining in flight queries. Since our utility principally points brief length queries, we usually cancel slightly below 10 queries. At this level, with site visitors paused, we then must confirm our databases are the identical.

Guaranteeing that two databases are the identical earlier than rerouting shoppers is a basic requirement for stopping information loss. We used LSNs to find out if two databases had been synchronized. If we pattern an LSN from our authentic database as soon as we’re assured that there are not any new writes, we are able to then look ahead to the reproduction to replay previous this LSN. At this level, the info is equivalent in each the unique and the reproduction.

After we’ve checked that the reproduction is synchronized, we cease replication and promote the reproduction to a brand new database. Reverse replication is ready up as beforehand talked about. Then, we resume site visitors in PgBouncer, however now the queries are routed to the brand new database.

We’ve since efficiently carried out the partitioning operation many occasions in manufacturing, and every time, we met our preliminary goal: handle scalability with out impacting reliability. Our first operation concerned transferring two high-traffic tables, whereas our last operation in October 2022 concerned 50. Throughout every operation, we noticed a ~30 second interval of partial availability impression (~2% of requests dropped). Right now, every database partition is working with significantly elevated headroom. Our largest partition has CPU utilization hovering ~10%, and we’ve decreased the sources allotted to among the decrease site visitors partitions.

And but, our work right here shouldn’t be achieved. Now with many databases, shopper functions have to keep up data of every one, and the routing complexity scales multiplicatively as we add extra databases and shoppers. We’ve since launched a brand new question routing service which can centralize and simplify routing logic as we scale to extra partitions. A few of our tables have excessive write site visitors or billions of rows and terabytes of disk footprint, and these tables will hit disk utilization, CPU, and I/O bottlenecks individually. We all the time knew that if we solely relied on vertical partitioning, we’d ultimately attain scaling limits. Going again to our intention of maximizing leverage, the tooling we created for vertical partitioning will make us higher geared up to horizontally shard tables with excessive write site visitors. It has offered us with sufficient runway to sort out our present initiatives and preserve Figma’s “highways” open, whereas additionally seeing across the bend.

Keep tuned for extra particulars on these initiatives. Scaling our database structure is each a posh and thrilling activity that’s important for Figma’s success. For those who’re fascinated by engaged on initiatives like this, we’re hiring!

Nothing nice is made alone, and the next database group members had been instrumental in bringing this workstream to life: David Harju, Dylan Visher, Erica Kong, Gordon Yoon, Josh Bancroft, Kevin Lin, Langston Dziko, Ping-Min Lin, Rafael Chacon Vivas, Roman Hernandez, Sammy Steele, and Yiming Li.

I would additionally prefer to thank all of our cross-functional companion groups, and particularly the next people: Jared Wong, Josh Tabak, Karl Jiang, Kevin Stewart, Kyle Hardgrave, Michael Harris, Ricky Zein, Shloak Jain, Tommy MacWillliam, William Li.