Database Sharding | Techlist.io

figmaMay 4, 20263 min readCurated summary

PGKeeper: Building the Bouncer We Needed for Postgres | Figma Blog

Figma built PGKeeper to replace PgBouncer as its PostgreSQL connection and load-management layer. Growing traffic, sharding, and stricter reliability requirements exposed PgBouncer’s limits in scalability, prioritization, backpressure, connection protection, and extensibility. PGKeeper is a custom Go service positioned between Figma’s DBProxy routing layer and PostgreSQL, designed to protect databases from overload and connection churn. ## Figma’s Database Architecture - PostgreSQL powers Figma’s OLTP workloads. - Figma scales through horizontal and vertical sharding across multiple database instances. - DBProxy hides sharding complexity from application code by: - Parsing and analyzing queries. - Selecting the appropriate PostgreSQL instances. - Rewriting requests into queries for the selected targets. - A dedicated set of connection-pooler replicas serves each PostgreSQL machine, creating an n-to-one relationship between poolers and databases. ## Why PgBouncer Was No Longer Enough - **Limited scalability** - PgBouncer’s single-threaded architecture created a vertical scaling ceiling. - Adding replicas helped, but uneven load distribution caused performance degradation. - **Insufficient load management** - PgBouncer could not prioritize critical traffic over lower-priority or misbehaving requests. - It lacked effective backpressure and advanced load-shedding algorithms such as Controlled Delay (CoDel). - CoDel sheds work based on how long requests wait, rather than simply counting queued requests. - **Unsafe connection behavior** - PostgreSQL connections are expensive resources. - Rapid connection creation and churn could destabilize database nodes. - Recovery after overload could trigger another surge of connections, creating cascading failures and prolonged overload. - **Limited extensibility and control** - Figma needed deep observability, feature-flagged rollouts, admission control, and fair resource sharing. - Even maintaining small PgBouncer patches proved costly. - Extending PgBouncer substantially would create an ongoing maintenance burden. ## Why Connection Pooling Could Not Live in DBProxy - Figma generally limits each PostgreSQL instance to roughly 100 pooled connections. - Hundreds of stateless DBProxy replicas sit in front of those databases. - Giving every DBProxy replica its own pool would either exceed database connection limits or require complex coordination. - Centralizing pooling in a separate service provided a better fit for the mismatch between many routers and a small fixed connection budget. ## Why Figma Built PGKeeper - PGCat addressed PgBouncer’s single-threaded scalability problem, but customizing it would require deep changes to its core execution paths. - Those changes would likely require Figma to maintain a long-term fork. - Figma therefore created PGKeeper as a Go-based service tailored to its infrastructure and operational requirements. - Its role is to act like a goalkeeper: protecting PostgreSQL from harmful traffic and protecting connections from uncontrolled churn. PGKeeper was chosen because Figma needed more than a basic connection pooler: it needed a scalable, observable, controllable layer capable of prioritizing traffic and preventing database overload.

Read original

Database Sharding PostgreSQL Go Load Balancing+3

lineAug 1, 2025Original article

Replacing the Payment System DB Handling (opens in new tab)

The LINE Billing Platform successfully migrated its large-scale payment database from Nbase-T to Vitess to handle high-traffic global transactions. While initially exploring gRPC for its performance reputation, the team transitioned to the MySQL protocol to ensure stability and reduce CPU overhead within their Java-based environment. This implementation demonstrates how Vitess can manage complex sharding requirements while maintaining high availability through automated recovery tools. ### Protocol Selection and Implementation - The team initially attempted to use the gRPC protocol but encountered `http2: frame too large` errors and significant CPU overhead during performance testing. - Manual mapping of query results to Java objects proved cumbersome with the Vitess gRPC client, leading to a shift toward the more mature and recommended MySQL protocol. - Using the MySQL protocol allowed the team to leverage standard database drivers while benefiting from Vitess's routing capabilities via VTGate. ### Keyspace Architecture and Data Routing - The system utilizes a dual-keyspace strategy: a "Global Keyspace" for unsharded metadata and a "Service Keyspace" for sharded transaction data. - The Global Keyspace manages sharding keys using a "sequence" table type to ensure unique, auto-incrementing identifiers across the platform. - The Service Keyspace is partitioned into $N$ shards using a hash-based Vindex, which distributes coin balances and transaction history. - VTGate automatically routes queries to the correct shard by analyzing the sharding key in the `WHERE` clause or `INSERT` statement, minimizing cross-shard overhead. ### MySQL Compatibility and Transaction Logic - Vitess maintains `REPEATABLE READ` isolation for single-shard transactions, while multi-shard transactions default to `READ COMMITTED`. - Advanced features like Two-Phase Commit (2PC) are available for handling distributed transactions across multiple shards. - Query execution plans are analyzed using `VEXPLAIN` and `VTEXPLAIN`, often managed through the VTAdmin web interface for better visibility. - Certain limitations apply, such as temporary tables only being supported in unsharded keyspaces and specific unsupported SQL cases documented in the Vitess core. ### Automated Operations and Monitoring - The team employs VTOrc (based on Orchestrator) to automatically detect and repair database failures, such as unreachable primaries or replication stops. - Monitoring is centralized via Prometheus, which scrapes metrics from VTOrc, VTGate, and VTTablet components at dedicated ports (e.g., 16000). - Real-time alerts are routed through Slack and email, using `tablet_alias` to specifically identify which MySQL node or VTTablet is experiencing issues. - A web-based recovery dashboard provides a history of automated fixes, allowing operators to track the health of the cluster over time. For organizations migrating high-traffic legacy systems to a cloud-native sharding solution, prioritizing the MySQL protocol over gRPC is recommended for better compatibility with existing application frameworks and reduced operational complexity.

Database Sharding Database Design Grpc Mysql+4

lineJun 18, 2025Original article

Replacing a Payment System Database That Processes (opens in new tab)

The LINE Billing Platform team recently migrated its core payment database from Nbase-T to Vitess to address rising licensing costs while maintaining the high availability required for financial transactions. After a rigorous Proof of Concept (PoC) evaluating Apache ShardingSphere, TiDB, and Vitess, the team selected Vitess for its mature sharding capabilities and its ability to provide a stable, scalable environment on bare-metal infrastructure. This migration ensures the platform can handle large-scale traffic efficiently without the financial burden of proprietary license fees. ### Evaluation of Alternative Sharding Solutions Before settling on Vitess, the team analyzed other prominent distributed database technologies to determine their fit for a high-stakes payment system: * **Apache ShardingSphere:** While it offers flexible Proxy and JDBC layers, it was excluded because it requires significant manual effort for data resharding and rebalancing. The management overhead for implementing shard-key logic across various components (API, batch, admin) was deemed too high. * **TiDB:** This MySQL-compatible distributed database uses a decoupled architecture consisting of TiDB (SQL layer), PD (metadata management), and TiKV (row-based storage). Its primary advantage is automatic rebalancing and the lack of a required shard key, which significantly reduces DBA operational costs. * **Nbase-T:** The legacy system provided the highest performance efficiency per resource unit; however, the shift from a free to a paid licensing model necessitated the move to an open-source alternative. ### Vitess Architecture and Core Components Vitess was chosen for its proven track record at companies like YouTube and GitHub, offering a robust abstraction layer that makes a clustered database appear as a single instance to the application. The system relies on several specialized components: * **VTGate:** A proxy server that routes queries to the correct VTTablet, manages distributed transactions, and hides the physical topology of the database from the application. * **VTTablet:** A sidecar process running alongside each MySQL instance that manages query execution, data replication, and connection pooling. * **VTorc and Topology Server:** High availability is managed by VTorc (an automated failover tool), while metadata regarding shard locations and node status is synchronized via a topology server using ZooKeeper or etcd. ### PoC Performance and Environment Setup The team conducted performance testing by simulating real payment API scenarios (a mix of reads and writes) on standardized hardware (8vCPU, 16GB RAM). * **Comparison Metrics:** The tests focused on Transactions Per Second (TPS) and resource utilization as thread counts increased. * **Infrastructure Strategy:** Because payment systems cannot tolerate even brief failover delays, the team opted for a bare-metal deployment rather than a containerized one to ensure maximum stability and performance. * **Resource Efficiency:** While Nbase-T showed the best raw efficiency, Vitess demonstrated the necessary scalability and management features required to replace the legacy system effectively within the new cost constraints. ### Practical Recommendation For organizations managing critical core systems that require horizontal scaling without proprietary lock-in, Vitess is a highly recommended solution. While it requires a deep understanding of its various components (like VTGate and VTTablet) and careful configuration of its topology server, the trade-off is a mature, cloud-native-ready architecture that supports massive scale and automated failover on both bare-metal and cloud environments.

Database Sharding Mysql Database Migration High Availability+4

figmaMay 16, 20242 min readCurated summary

Keeping It 100(x) With Real-time Data At Scale | Figma Blog

Figma’s LiveGraph powers real-time collaboration by subscribing to GraphQL-like queries and updating clients automatically. Rapid growth—tripled sessions since 2021 and fivefold view-request growth in one year—exposed limits in its single-server, mutation-based architecture. Figma launched “LiveGraph 100x,” a redesign focused on scaling reads and database updates while preserving performance and enabling a safe migration. ## LiveGraph’s Role in Figma - LiveGraph keeps data synchronized across collaborative features such as: - File editing - Comments - FigJam voting - It exposes a web API for subscribing to GraphQL-like queries. - Results are returned as JSON trees based on a schema of entities, relationships, and views. - A custom React Hook automatically re-renders interfaces when subscribed data changes. ## The 100x Scaling Initiative Figma’s growing user base increased both the number and cost of LiveGraph client sessions. At the same time, the underlying database evolved from one PostgreSQL instance into vertically and horizontally sharded infrastructure. The redesigned system needed to: - Preserve or improve service-level objectives for initial loads and updates. - Support more database shards reliably and efficiently. - Scale reads and database-update processing independently. - Allow incremental, transparent migrations without disrupting users. ## Limitations of the Original Architecture Originally, LiveGraph consisted of: - A single LiveGraph server. - An in-memory query cache. - One PostgreSQL instance. - A cache that tailed PostgreSQL’s logical replication stream. PostgreSQL writes row mutations to its write-ahead log, including pre- and post-row images and a monotonically increasing sequence number. LiveGraph used these mutations to update cached query results directly rather than recomputing them. This design worked well at smaller scale because: - All updates came from one primary database. - The replication stream provided a global ordering. - Each row mutation could be applied directly to the relevant cached results. ## Sharding Breaks Global Ordering As the original PostgreSQL instance reached capacity, Figma introduced vertical shards and began moving toward broader horizontal scaling. This invalidated the assumption that all database updates arrive in one globally ordered stream. - Multiple shards can generate updates simultaneously. - Their updates have no guaranteed global order. - LiveGraph therefore needed an architecture that could process distributed database changes while maintaining reliable, timely query updates. The growing load made it necessary to rethink LiveGraph fundamentally rather than continue extending its single-database design.

Read original

Database Sharding PostgreSQL Distributed Systems GraphQL+4

figmaMar 13, 20243 min readCurated summary

How Figma's Databases Team Lived to Tell the Scale | Figma Blog

Figma’s database stack grew nearly 100× from 2020, pushing its single-Postgres architecture beyond the limits of vertical partitioning. After adding caching, read replicas, and vertically partitioned databases, the team found that individual tables were reaching terabyte and billion-row scales, creating vacuum reliability issues and approaching AWS RDS IOPS limits. The solution was to pursue horizontal sharding while preserving Postgres, minimizing application changes, avoiding massive backfills, and maintaining consistency and rollback options. ## Scaling from One Postgres Database - In 2020, Figma ran on one large Postgres instance. - By the end of 2022, it had introduced: - Caching - Read replicas - Around a dozen vertically partitioned databases - Related tables, such as those for Figma files and organizations, were grouped into separate database partitions. - Vertical partitioning reduced pressure on the system and provided valuable short-term runway. ## Why Vertical Partitioning Was No Longer Enough - The team monitored multiple scaling constraints, including: - CPU and I/O utilization - Table size - Rows written - Database IOPS - Some tables grew to several terabytes and billions of rows. - Large tables began affecting reliability during PostgreSQL vacuum operations, which prevent transaction ID exhaustion. - High-write tables were on track to exceed the maximum IOPS supported by Amazon RDS. - Because a table is the smallest unit of vertical partitioning, splitting databases by table group could not solve these limits. ## Requirements for the Next Scaling Strategy Figma established several design goals for horizontal scaling: - Minimize developer changes and preserve the existing relational data model. - Make future scale-outs transparent to application teams after initial compatibility work. - Avoid months-long backfills of large tables. - Roll out changes incrementally to reduce outage risk. - Preserve rollback capability after physical sharding. - Maintain strong consistency without relying on difficult double-write schemes. - Support near-zero-downtime scale-outs. - Favor technologies and techniques the database team already understood, given the limited runway. ## Evaluating Alternatives - The team considered CockroachDB, TiDB, Spanner, and Vitess. - Moving to another database would have required a risky migration between storage systems while preserving consistency and reliability. - Figma already had substantial operational expertise running Postgres on RDS; replacing it would mean rebuilding that expertise under severe time pressure. - NoSQL systems were also unsuitable because Figma’s application depends on a complex relational data model and requires the flexibility of relational queries. - The team therefore favored a lower-risk approach that retained Postgres and offered greater control over the migration. ## Practical Direction Figma’s experience shows that vertical partitioning can be an effective intermediate step, but it cannot solve limits imposed by individual tables. For systems with rapidly growing relational workloads, horizontal sharding within a familiar database ecosystem can provide a safer path to scale when it is introduced incrementally and designed around consistency, rollback, and minimal application disruption.

Read original

Database Sharding PostgreSQL Distributed Systems Database Replication+2

figmaMar 12, 20243 min readCurated summary

How We Built a Custom Permissions DSL at Figma | Figma Blog

Figma’s original permissions system—a large Ruby `has_access?` method in its monolith—became too complex, risky, and expensive to maintain. As collaboration features expanded, permission rules involving roles, links, hierarchies, organizations, billing, and deleted files caused bugs, delayed projects, and heavy database load. Figma responded by building a custom permissions DSL and cross-platform logic engine to make rules more modular, flexible, performant, and easier to debug. ## Why Permissions Became Difficult - Figma’s collaboration model requires detailed access rules for files and other resources. - Access can come through: - Roles inherited from parent folders, teams, or organizations - Link-sharing settings - User roles and authorship levels - Passwords, expiration periods, and organization restrictions - Originally, permissions lived in a Ruby monolith using ActiveRecord. - A model-level `has_access?` method accepted a user and resource, performed database queries, and returned a Boolean. - Product engineers had to call this method correctly from controllers. ## Problems with the Original System ### Complex Logic and Difficult Debugging - `has_access?` methods grew into long functions with many optional parameters. - Engineers were reluctant to modify them because mistakes could expose access to large numbers of files. - All permission logic for a resource was intertwined, making it difficult to isolate or test individual rules. - Debugging often required adding many print statements and understanding the entire permissions implementation. ### Inflexible Hierarchical Permissions - Permissions were nominally represented by hierarchical integer levels, such as edit access being higher than view access. - Boolean flags introduced exceptions that undermined the hierarchy, including options such as: - `ignore_link_access` - `org_candidate` - `ignore_archived_branch` - A user could have a higher access level but fail a lower-level check when a flag changed the behavior. - These flags differed between resources, forcing engineers to remember numerous special cases. - Figma needed granular, non-hierarchical permissions that could operate independently or define new permission hierarchies. ### Excessive Database Load - As Figma scaled, permission checks accounted for roughly 20% of database load. - This created a serious scalability concern because database capacity had physical limits. - Although the database team was pursuing vertical and horizontal sharding, Figma also needed to reduce and better control permission-related queries. ## Building a Custom Permissions DSL - Figma generally prefers adopting open-source or commercial solutions, but existing options did not adequately address its requirements. - The company chose to build: - A domain-specific language for expressing permissions - A custom cross-platform logic engine - A migration plan for moving critical permission rules into the new system - The intended result was a permissions system that improved developer ergonomics while increasing correctness and performance. Figma’s experience shows that permissions can become a foundational scalability and reliability problem when implemented as one growing authorization function. A dedicated, composable DSL can provide clearer rules, more flexible access models, and better control over database usage.

Read original

Database Sharding Access Control Ruby Authorization+3

figmaApr 3, 20233 min readCurated summary

The growing pains of database architecture | Figma Blog

Figma outgrew its single Amazon RDS PostgreSQL database as traffic increased roughly threefold annually, pushing peak CPU utilization above 65% and making latency unpredictable. Initial fixes—larger hardware, read replicas, new databases, and PgBouncer—provided temporary relief but could not adequately reduce write load or handle replication-sensitive reads. Figma ultimately chose vertical partitioning, moving groups of related tables into separate databases as a lower-risk, incremental path to scalability. ## The Limits of a Single Database - Figma stored metadata such as permissions, file information, and comments in one large RDS instance. - Increasing users, new features, and preparation for a second product drove database traffic sharply upward. - Peak CPU utilization reached more than 65%, with latency becoming less predictable as the database approached its limits. - Full saturation would have made Figma unavailable, so the infrastructure team addressed the risk before it became an outage. ## Tactical Measures for More Headroom Figma introduced several short-term improvements: - Upgraded the database from an `r5.12xlarge` to an `r5.24xlarge` instance. - Added multiple read replicas to distribute read traffic. - Created separate databases for new use cases to prevent further growth of the original database. - Added PgBouncer to pool connections and reduce the impact of thousands of application connections. - These changes provided approximately another year of runway, but writes still consumed substantial resources. - Some reads could not be moved to replicas because the application was sensitive to replication lag. ## Evaluating Horizontal Scaling Figma considered horizontally sharding the database but found substantial technical and operational risks: - Many managed horizontally scalable databases were not natively compatible with PostgreSQL. - Migrating to NoSQL or Vitess would require complex double-read and double-write migration strategies. - NoSQL would also require significant application changes. - A managed distributed PostgreSQL system could make Figma an unusually large customer, exposing it to untested scaling limits. - Self-hosting would require new expertise, training, and considerable operational investment, diverting attention from the core scalability problem. ## Choosing Vertical Partitioning Instead of splitting individual tables across many database nodes, Figma chose vertical partitioning: - Groups of related tables would be moved to separate databases. - This approach immediately reduced load on the original database. - It preserved a future path toward horizontal sharding for particularly large or demanding table groups. - The strategy was considered more incremental and operationally manageable than replacing PostgreSQL or adopting a self-hosted distributed system. ## Selecting Tables to Move Figma evaluated candidate tables using two criteria: - **Impact:** Moving the tables should remove a meaningful portion of the database workload. - **Isolation:** The tables should have limited dependency on tables that remained in the original database. - To measure impact, the team analyzed average active sessions (AAS), which estimates the average number of active threads handling a query. - They gathered query activity from PostgreSQL’s `pg_stat_activity` view at 10-millisecond intervals to identify CPU waits associated with individual queries. Figma’s experience shows that database scaling does not always require an immediate move to distributed infrastructure. Carefully selected vertical partitioning can reduce pressure on a primary database while limiting migration risk and preserving more ambitious scaling options for the future.

Read original

Database Sharding PostgreSQL Amazon Rds Pgbouncer+3