Implementing Vertical Sharding

Sharding:- The process of storing a large database over numerous servers is known as database sharding. Only a certain amount of data can be stored and processed on a single system, or database server. By dividing the data into smaller pieces, or "shards," and storing them across multiple database servers, database sharding gets around this restriction. The basic technology used by all database servers are typically the same, and they collaborate to store and handle massive amounts of data.

Vertical Sharding:- The main idea of the business sharding method is to specialise databases for various functions. It is known as vertical sharding or longitudinal sharding. A database initially comprises of numerous tables that belong to various businesses. But, following sharding, tables are divided into various databases based on the company, and the pressure is also divided into many databases. The solution to allocate user tables and arrange tables in various databases via vertical sharding in accordance with business needs is shown in the diagram below.

Let's implement this step by step.

Idea is- A group of tables in separate databases.

Business Case: One database server houses a table connected to payments, and another database server houses a table related to authorisations.

Better Load Handling.
Handle larger scale.

When switching to a microservices-based architecture, the database will need to be vertically shard. So let's consider how to make this action happen.

Tables should be moved from one database server to another

For receiving updates and storing meta data. Due to the fact that the tables will be scattered over several databases, we will have. We would need a method for organising this data so that everyone could see things the same way. Moreover, the API server must be changed in response to any configuration changes (Table ownership). So, to address these issues, we will use Zookeepar.

Let's think How to move tables from one mySql database to another one.

The main goal is to transfer table T2 from DB1 to DB2 as quickly as possible.

Use "mysqldump" to dump the table T2 and the Binlog location. Along with the binlog location, the dump will include all of the table's data.

Restore the dump to another database load dumpp.sql to DB2.

Ensure that only changes relevant to the intended table T2 are applied before starting the replication between the two databases.

Now newer change will flow to DB2.

when DB2 is virtually caught up to other databases. -- Table T2 should now be called T2_Backup. As soon as a "table not found statement" is seen, sync between the two databases should halt. -- To reflect that table T2 is now available on DB2, update the entry in Zookeeper.

Once the aforementioned changes to Zookeeper have been completed, the API server is reached, and connections to DB2 can then be established from that table.

Table Not Found errors will occasionally occur consistency over availability for a little period of time.
For small and medium-sized tables, this strategy works well, but replication lag will be a problem for large tables.

That's the wrap buddy 💡