Scaling Postgres and MySQL: Making 768 Servers Look Like One

Scaling relational databases to handle millions of queries per second and petabytes of data requires moving beyond vertical scaling and read-replicas to a sharded architecture. By implementing a sophisticated proxy layer, organizations can distribute data across hundreds of servers—such as a 768-server cluster—while presenting a single, cohesive database interface to the application layer.

The Limits of Vertical Scaling and Read-Replicas

Vertical scaling (increasing CPU and RAM) and adding read-replicas are effective initial scaling strategies, but they eventually hit three critical bottlenecks:

• Write Bottlenecks: In Postgres, all writes must be recorded in the write-ahead log (WAL) and flushed to durable storage on the primary server. This creates a single write bottleneck that cannot be solved by adding read-replicas.
• Data Capacity: Replicas are full copies of the primary data. Adding them increases read throughput but does not increase the total amount of data the system can store.
• Backup Latency: Backing up a massive, monolithic database to object storage can take hours or days due to bandwidth limitations, which is unacceptable for strict Recovery Point Objective (RPO) and Recovery Time Objective (RTO) guarantees.

Solving Scale with Database Sharding

Sharding distributes data and queries across multiple distinct primary servers. This removes the single-node limit on write throughput and storage capacity. For example, storing one petabyte of data can be achieved by using 256 shards, each with one primary and two replicas, totaling 768 servers.

To prevent this complexity from leaking into the application code, a proxy layer is used to make the distributed system appear as a single database.

The Role of the Router Proxy

While simple proxies like PgBouncer handle connection pooling and request queuing, sharding requires a "router" proxy. The router must understand the data topology—how data is distributed across shards—and route SQL queries accordingly.

• Data Distribution: The router uses a sharding strategy, typically hashing an ID column, to determine which shard should store a specific row.
• Simple Reads: For queries targeting a specific ID, the router forwards the request directly to the corresponding shard.
• Complex Reads: For range queries (e.g., WHERE id BETWEEN 3 AND 5), the router parses the query, identifies all relevant shards, distributes the query, aggregates the results, and returns the final set to the client.

Defining Data Topology

Routers use JSON configuration files to define how tables are sharded. For instance, a configuration might specify that a user table is sharded on the id column using a specific hash index. This metadata allows the router to generically handle diverse schemas and query patterns.

High Availability and Traffic Distribution

To handle millions of queries per second, a single proxy is insufficient. A fleet of proxies is deployed behind a Network Load Balancer (NLB). The NLB assigns incoming connections to a specific proxy for the duration of the connection's lifetime, ensuring the application only needs a single connection string (e.g., mydb.pscale.com).

The request flow is as follows: App Server $\rightarrow$ NLB $\rightarrow$ Router Proxy $\rightarrow$ Database Shard

Industry Perspectives and Trade-offs

While sharding via tools like Vitess (for MySQL) and Neki (for Postgres) is a proven path for massive scale, the community highlights several technical challenges and alternatives:

• Complexity of Distributed SQL: Critics note that sharding introduces difficulties with sequences, foreign keys, distributed transactions, and cross-shard joins. These operations often exhibit different performance characteristics than single-shard queries.
• The "Scale Up" Argument: Some engineers argue that maximizing the capacity of the largest available single server and optimizing caching layers should be prioritized before adopting the operational complexity of sharding.
• Alternative Architectures: Some suggest that synchronous multi-write-master systems (such as Oracle RAC) can scale relational databases horizontally without the need for manual sharding or proxy routers by using high-speed RDMA networks for lock coordination.

"The day you can no longer inspect the essential state of your system is the day your company better be included in NASDAQ and ready to pay a few hundred engineers 300k salaries."

Summary of Recommendations

PlanetScale recommends transitioning to a sharded architecture for Postgres and MySQL once data volume exceeds a few terabytes. At this scale, the bottlenecks of monolithic primaries and the limitations of backup windows typically make sharding the most viable path for continued growth.