Implementing Vertical Sharding

1124 views Backend System Design

Vertical sharding is fine, but how can we actually implement it? 🤔

Vertical Sharding

Vertical sharding is splitting a database by the tables. Shards will hold a subset of tables. For example, all payments-related tables go to one shard, while all auth-related tables go to another.

So, how to implement it?

Need for a configuration store

For our API servers to talk to the correct database we would need a configuration store that holds the information for all the tables mapped to the database server that holds it.

For example, the Users table is present on DB1 while Transactions on DB2

Whenever the request comes, the API servers first check the config to find which DB holds the table and then fire the SQL query to that specific database for the table.

Reactive update

All API servers will cache the configuration to avoid an expensive network call to get the database ensuring we get a solid boost to the performance.

When a table is moved from one database server to another, the configuration will be updated and hence the changes would need to be reactively propagated to all the API servers. Hence our config store needs to support reactive communication.

This is where we choose Zookeeper which is resilient and battle-tested to achieve this.

Moving tables

Say, we are moving table T2 from database server DB1 to DB2. Moving the table from one server to another is done in 4 simple steps.

Dump the table T2

We first dump the table T2 from DB1 transactionally using the utility mysqldump that not only dumps the table data but also records the position in the binlog. This is like taking a point-in-time snapshot of the table.

Restore the dump

We now restore the dump to database DB2. This way we will have a database server with the table T2 containing data till a certain point in time.

Sync table T2 on DB1 and DB2

We now setup the replication from DB1 to DB2 specifically for sync changes happening on table T2. It is done through a custom job that will use the recorded binlog position and start syncing from it.


Once the table T2 is synced with almost 0 replication lag on DB1 and DB2 we cutover. We first rename the table to T2_bak and update the config in Zookeeper.

As we rename the table any queries going to DB1 for table T2 will start throwing “Table not found” errors, but as Zookeeper will propagate the changes to all API servers they would use DB2 to fire any query on table T2, thus completing the table movement.

This is how you can implement vertical sharding.

Arpit Bhayani

Arpit's Newsletter

CS newsletter for the curious engineers

❤️ by 17000+ readers

If you like what you read subscribe you can always subscribe to my newsletter and get the post delivered straight to your inbox. I write essays on various engineering topics and share it through my weekly newsletter.

Other essays that you might like

An in-depth introduction to Rolling Deployments

944 views 46 likes 2022-05-27

One of the simplest deployment strategies that make deployment a breeze is Rolling Deployment. It is the most widely ado...

Implementing Vertical Sharding

1124 views 75 likes 2022-05-25

Sharding is super-important when you want to handle the traffic that cannot be handled through one server. Sharding come...

An in-depth introduction to Blue Green Deployments

1309 views 60 likes 2022-05-18

Deployments are a pain if we are unsure about our release changes. But sometimes even if we know our changes well, somet...

An in-depth introduction to Canary Deployments

1982 views 117 likes 2022-05-16

Deployments are stressful; what if something goes wrong? What if you forgot to handle an edge case that was also missed ...

Be a better engineer

A set of courses designed to make you a better engineer and excel at your career; no-fluff, pure engineering.

System Design Masterclass

A masterclass that helps you become great at designing scalable, fault-tolerant, and highly available systems.

Enrolled by 700+ learners

Details →

Designing Microservices

A free course to help you understand Microservices and their high-level patterns in depth.

Enrolled by 17+ learners

Details →

GitHub Outage Dissections

A free course to help you learn core engineering from outages that happened at GitHub.

Enrolled by 67+ learners

Details →

Hash Table Internals

A free course to help you learn core engineering from outages that happened at GitHub.

Enrolled by 25+ learners

Details →

BitTorrent Internals

A free course to help you understand the algorithms and strategies that power P2P networks and BitTorrent.

Enrolled by 42+ learners

Details →

Topics I talk about

Being a passionate engineer, I love to talk about a wide range of topics, but these are my personal favourites.

Arpit's Newsletter read by 17000+ engineers

🔥 Thrice a week, in your inbox, an essay about system design, distributed systems, microservices, programming languages internals, or a deep dive on some super-clever algorithm, or just a few tips on building highly scalable distributed systems.

  • v12.4.4
  • © Arpit Bhayani, 2022

Powered by this tech stack.