Database Sharding — PlanetScale📹 The future of AI infrastructure: optimize and shard your database with agents.Watch the talk
Navigation<br>Blog|Engineering<br>Table of contents «Close »Table of contents<br>What is sharding<br>Sharding basics<br>Sharding strategy<br>Range sharding<br>Hash sharding<br>Other strategies<br>Cross-shard queries<br>UpdatesLatency<br>Data durability<br>Fast Backups
Conclusion<br>PlanetScale Postgres is the fastest way to run Postgres in the cloud. Plans start at just $5 per month.<br>Learn more
Get the RSS feed
Database Sharding<br>Ben Dicken [@BenjDicken] | January 9, 2025<br>What is sharding<br>Sharding is the process of scaling a database by spreading out the data across multiple servers, or shards. Sharding is the go-to database scaling solution for many large organizations managing data at petabyte scale. Your favorite companies like Uber, Shopify, Slack, and Cash App all use sharding with Vitess and MySQL to scale their massive databases.<br>In this article, you'll learn how sharding works and considerations for designing a performant sharded database cluster. Along the way, you'll be able to interact with database cluster diagrams, giving you the opportunity to really let the concepts sink in via hands-on examples.<br>Note<br>With our PlanetScale Vitess clusters, you can utilize Workflows that make explicit sharding much easier than other solutions. Built on top of Vitess, this allows you to horizontally scale without adding additional logic to your application code. Sharded tables appear as a single, unified table to your application. Sign up to try it out yourself or check out our video walkthrough if you want to see how it works on PlanetScale.
Sharding basics<br>Most small-scale web applications will have one or more application servers that connect to a single, monolithic database server. The applications store all persistent data on this single server, and send queries to it to meet application needs. This includes user account information and whatever other data the application needs in order to operate.<br>Below is an interactive database cluster with this setup. There is an application server on the left and a single database server on the right. Try clicking insert row and select row to see data get added to and retrieved from the database.<br>Each row inserted has a user_id, name, and age respectively. We'll use rows with this formatting for several examples going forward.<br>As a quick aside, you can configure the speed of animations in this post by selecting your desired speed below.<br>The architecture described above works well for low-demand systems that only need to make a few hundred or a few thousand queries per second.<br>However, popular software apps often have hundreds of thousands of concurrent users. In some cases, these applications need to store petabytes of information in the database and process millions of queries per second at peak hours. Such huge workloads demand spreading out our database across many servers, rather than just one. Sharding is a popular solution to this problem.<br>In a sharded database, we will have multiple separate database servers, each with a portion of the total data. Below you can see a simple configuration with an application server that can send queries to one of two database servers (shards). Try inserting and selecting rows again. For any of the remaining interactive visualizations, you can also click on a row in a shard to delete it or click on a shard's label to empty it completely<br>With this setup, the code running on the application server has to be aware of all of the shards, know which rows are stored where, and keep a connection open to each. This is not a huge problem with only two shards, but it becomes complex when there are hundreds of them. Storing this logic in the application code can quickly become messy and difficult to maintain.<br>A better option is to have the app servers connect to an intermediary server that we call a proxy . When an application server needs to use the database, it will send queries to a proxy . The proxy is then responsible for routing the query to the correct shard server<br>We have such a configuration below. Go ahead and click the insert row button to add rows to the shards. You'll see visually how the proxy server is used to manage the inserts.<br>Note<br>PlanetScale builds its sharding solution on top of Vitess. In Vitess, these proxy servers are known as Vitess Gates, or VTGates for short.
After filling up the shards, you can see that there are 6 rows in total, each database server storing 3. Of course, a real sharded database would store many millions or billions of rows per server, but we keep it small here for the sake of learning.<br>If you inserted more than six rows, you may noticed one or more of the shard servers pulse red indicating that they are being overloaded. If you inserted really fast, you may have seem the proxy server pulse red as well. Server overloading can happen when the query demand is too high or if the server is nearing its storage...