What is DATABASE SHARDING?

Summary

The script explores database optimization techniques, emphasizing the limitations of traditional indexing and introducing sharding as a solution for handling large datasets. It explains sharding with the pizza analogy, dividing data across multiple servers based on a key attribute, and discusses horizontal partitioning. The script also touches on challenges like cross-shard joins and inflexible shard sizes, proposing solutions like consistent hashing and hierarchical sharding. It highlights the importance of consistency and availability in databases and suggests using indexing on shards to improve performance. Finally, it mentions master-slave architecture for fault tolerance.

Takeaways

• 🔍 Query optimization is essential for handling large databases, and traditional methods like indexing may not suffice for massive datasets.
• 📚 The concept of sharding is introduced as a solution for distributing large datasets across multiple servers, similar to sharing pizza slices among friends.
• 📈 Sharding involves horizontal partitioning of data using a key attribute, which helps in managing and scaling databases effectively.
• 🔑 Horizontal partitioning is differentiated from vertical partitioning, which uses columns to partition data.
• 🛡️ Consistency and availability are key attributes of a database; consistency ensures that data is synchronized across the system, while availability ensures the system is always running.
• 🔑 The choice of sharding key is crucial and can be based on attributes like user ID or location, depending on the application's needs.
• 🚫 Sharding presents challenges, such as the complexity of performing joins across different shards, which can be computationally expensive.
• 🔄 The inflexibility of shard sizes is a limitation, but techniques like consistent hashing and hierarchical sharding can help address this issue.
• 📈 Hierarchical sharding allows for dynamic breaking down of large shards into smaller ones, increasing flexibility in database management.
• 🔍 Creating indexes on shards can improve query performance, especially when queries involve attributes different from the sharding key.
• 🛡️ Master-slave architecture can provide fault tolerance in sharding setups by allowing multiple slaves to replicate data from a master server.
• 🚀 While sharding offers benefits in read and write performance, it is a complex solution and should be considered carefully, with simpler solutions like indexing or NoSQL databases explored first.

Q & A

What is the main topic discussed in the video script?

The main topic discussed in the video script is database optimization, specifically focusing on the concept of sharding as a method to handle large volumes of data.

What is an SQ optimizer mentioned in the script?

An SQ optimizer is a tool or technique used to optimize SQL queries, though the script suggests that it might be considered 'old school' for handling very large datasets.

Why might indexing be insufficient for a database with a lot of data?

Indexing might be insufficient for a database with a lot of data because it can help speed up query times but does not address the fundamental issue of data distribution and scalability that comes with large datasets.

What is the analogy used in the script to explain sharding?

The analogy used in the script to explain sharding is a pizza that is too large for one person to eat alone, so it is broken into slices and shared among friends.

What is horizontal partitioning and how is it related to sharding?

Horizontal partitioning is the process of dividing a database into parts, each part stored on a different server. Sharding is a specific implementation of horizontal partitioning where data is distributed across multiple servers based on a key attribute.

What are the key attributes of a database mentioned in the script?

The key attributes of a database mentioned in the script are consistency, which ensures that data is accurately stored and retrieved, and availability, which refers to the database's uptime and reliability.

Why is consistency considered more important than availability in the context of databases?

Consistency is considered more important than availability because it ensures the accuracy and reliability of the data. While uptime is important, having accurate data is crucial for the integrity of the database.

What is the problem with joins across shards as mentioned in the script?

The problem with joins across shards is that they can be inefficient because the query must access data from multiple shards, which may involve network communication and can significantly slow down the process.

What is the issue with the inflexibility of shards in sharding?

The issue with the inflexibility of shards is that once the data is partitioned, it is difficult to change the number of shards without significant reorganization of the data.

What is consistent hashing and how does it address the inflexibility of shards?

Consistent hashing is an algorithm that allows for a dynamic number of shards by distributing data across a virtual ring of hashes. It helps address the inflexibility of shards by allowing the system to add or remove shards without significant disruption.

What is hierarchical sharding and how does it help with the inflexibility problem?

Hierarchical sharding is a technique where a shard, which has too much data, is further divided into smaller pieces or 'mini-shards'. This approach helps overcome the inflexibility problem by allowing the system to dynamically adjust the number of data pieces as needed.

What is the role of a master-slave architecture in the context of sharding?

In the context of sharding, a master-slave architecture is used to provide redundancy and fault tolerance. The master holds the most current data and handles write requests, while the slaves replicate the master's data and handle read requests.

Why is indexing important on shards?

Indexing is important on shards because it can significantly improve the performance of queries that are not based on the sharding key. By indexing other attributes, the database can quickly locate and retrieve data within a shard.

What challenges does sharding present when it comes to practical application?

Sharding presents challenges in practical application due to the difficulty in maintaining consistency across distributed data. It requires careful planning and consideration of data access patterns and query performance.

Outlines

00:00

🍕 Sharding for Database Scalability

The first paragraph discusses database optimization and introduces the concept of sharding as a solution for handling large datasets. It compares sharding to sharing a pizza with friends, where the pizza represents the database and the slices represent partitions of data distributed across different servers. The explanation covers horizontal partitioning using a key attribute, contrasting it with vertical partitioning. It also touches on the importance of database consistency and availability, and how sharding can be used to improve performance by reducing the dataset size for each query. The paragraph concludes with potential issues related to sharding, such as the complexity of joins across shards and the inflexibility of shard sizes.

05:05

🔄 Overcoming Sharding Challenges with Consistent Hashing and Hierarchical Sharding

The second paragraph delves into strategies for addressing the challenges associated with sharding. It introduces consistent hashing as an algorithm to manage data distribution across a dynamic number of shards, mentioning memcached as a database that uses this technique. The paragraph explains how hierarchical sharding can be used to break down large shards into smaller, more manageable pieces, thus overcoming inflexibility. It also discusses the benefits of creating indexes on shards for improved query performance and touches on the use of master-slave architecture to ensure high availability and fault tolerance in the event of a shard failure. The summary concludes with advice for those considering sharding, suggesting the use of ready-made solutions like indexing and NoSQL databases before implementing sharding.

Keywords

💡Database

A database is a structured collection of data, often stored electronically in a computer system. In the context of the video, the database is the subject of optimization and scaling discussions, highlighting the need for efficient data management as the volume of data grows.

💡Indexing

Indexing in databases refers to the creation of data structures that improve the speed of data retrieval operations. The script mentions indexing as a method to optimize queries, suggesting that it's a fundamental technique for enhancing database performance.

💡NoSQL

NoSQL stands for 'Not Only SQL' and refers to a category of database management systems that provide a mechanism for storage and retrieval of data which is modeled in means other than the tabular relations used in relational databases. The script briefly mentions NoSQL as an alternative to traditional SQL databases for handling large volumes of data.

💡Sharding

Sharding is a method of database partitioning that separates very large databases into smaller, faster, more easily managed parts called data shards. In the video, sharding is used as an analogy with a pizza being split into slices to explain how data can be distributed across multiple servers.

💡Horizontal Partitioning

Horizontal partitioning, also known as sharding, is the process of splitting a database into parts that are distributed across multiple servers. The script uses this term to describe how data can be divided based on a key attribute, allowing for scalability and improved performance.

💡Vertical Partitioning

Vertical partitioning is the process of dividing larger tables into smaller ones based on the columns, rather than the rows. The script contrasts this with horizontal partitioning, noting that vertical partitioning is less about scalability and more about managing table size.

💡Consistency

Consistency in databases refers to the property of ensuring that all transactions are processed reliably and that the database accurately reflects all transactions. The script emphasizes the importance of consistency, stating that it is a key attribute of any database system.

💡Availability

Availability in the context of databases is the measure of the time a system is operational and accessible to users. The script discusses the trade-off between consistency and availability, noting that while both are important, consistency often takes precedence.

💡Joins Across Shards

Joins across shards refer to the process of combining data from different shards, which can be computationally expensive. The script identifies this as a potential problem with sharding, as it requires data to be pulled from multiple sources and then merged.

💡Consistent Hashing

Consistent hashing is an algorithm that provides a way to distribute data across a set of servers in a way that minimizes reorganization when servers are added or removed. The script suggests consistent hashing as a solution to the inflexibility of shard sizes, allowing for a dynamic number of shards.

💡Hierarchical Sharding

Hierarchical sharding is a technique where a shard is further divided into smaller pieces or 'mini-shards'. The script describes this as a method to overcome the inflexibility of traditional sharding by allowing for dynamic breaking down of large shards into smaller, more manageable pieces.

💡Master-Slave Architecture

Master-slave architecture is a type of data architecture where one server (the master) is the primary source of data, and other servers (the slaves) replicate the master's data. The script mentions this architecture as a way to provide fault tolerance in the event of a shard failure, with slaves taking over if the master goes down.

Highlights

Query optimization using an SQL optimizer is discussed as an old school approach.

Indexing is introduced as a basic method for database performance improvement.

The concept of NoSQL databases is introduced but dismissed for the given scenario.

Sharding is introduced as a solution for handling large datasets by partitioning data.

A pizza analogy is used to explain the concept of sharding.

Horizontal partitioning is described as a method of data distribution across servers.

Vertical partitioning is mentioned in contrast to horizontal partitioning.

Sharding is distinguished from traditional servers by focusing on database servers.

Consistency and availability are highlighted as key attributes of a database.

Sharding can be based on attributes like user ID or location in applications.

The problem of joins across shards is discussed as a challenge in sharding.

The inflexibility of shard sizes is identified as an issue.

Consistent hashing is suggested as a solution for dynamic scalability in sharding.

Hierarchical sharding is introduced to address the inflexibility of shard sizes.

Indexing on shards is recommended for improving query performance.

Master-slave architecture is presented as a solution for handling shard failures.

The practical application of sharding is acknowledged as challenging due to consistency issues.

The importance of using ready-made solutions like indexing or NoSQL databases is suggested before sharding.

Transcripts

So.

So how do you query this database?

So I would optimize the queries using an SQ optimizer. Well,

let's say we have a lot of data. So optimizing queries is so, you know,

old school.

Just so.

I could make an index on the table. Alright. Indexing is, indexing is cute,

but we are looking for something which is serious. Right, we got lots of data.

So can we use a NoSQL? No, we are not gonna learn audio m s.

Now for the final time, what do you think we should do? So shorting,

I'll use shorting. Hmm.

Okay.

Hired. What is sharding?

Let's say you have pizza and you can't have the entire thing by yourself.

So you break it into slices and call your friends.

Over eight friends.

Now each of these friends is going to get one slice of pizza.

What you have done effectively is partitioned the pizza according to each

friend's share.

Just like that we can have servers which are going to be taking the load of the

requests, which are, which are being sent into it. So if there's a server here,

now how do you get that to the pizza model?

User id number zero is going to start here. 100 starts here, 200.

What you have effectively done is taken all the server requests that you had and

mapped them onto a pizza such that each of these slices is going to be

served by one server, in this case, server id.

Number six.

The key thing to notice here is we couldn't eat the entire pizza by ourselves.

We needed friends to finish the pizza. To handle the pizza effectively.

And when you're getting friends along,

you're effectively taking the range of the pizza and breaking into pieces.

When you're doing that, you are partitioning the pizza.

This kind of partitioning,

which uses some sort of a key to break the data into pieces

and allocate that to different servers, is called horizontal partitioning.

Horizontal partitioning depends on one key,

which is an attribute of the data that you're storing to partition data.

You can contrast this with vertical partitioning.

There's a link in the description below,

which uses columns to partition data effectively.

But we are focusing on the horizontal partitioning bit and specifically we are

focusing on one concept, which is sharding. Now,

we mentioned that sharding is taking one attribute in the data and partitioning

the data such that each server gets one chunk. But what I mean by servers,

the servers here are database servers.

Can contrast this with what we have been talking till now about normal servers.

Normal servers are application servers.

They're platform servers which deal with data,

but they try to be as stateless as possible to keep things decoupled and

really nice and clean. This is going to be dealing with the meat of the data,

alright? And we can't afford to have any goof ups over here.

Consistency is important.

This is one of the key attributes of any database that whatever data

you persist in it is what you can read out of it later on.

And there is some sort of synchronization that if a person makes an update,

the new request is going to read that update. Okay?

So that is consistency. Also what we look at is availability,

meaning that the database should not crash and stay down.

You don't want that, you want your application to be running all the time,

but consistency trumps availability. When it comes to data,

in most cases there are more things to think about.

What should you shard your data on? In our case, we have used user id,

but in applications like Lindo, which use location,

you could shard on the location. And then if a person says,

find me all the users in C x,

then X may fall in this shard and all you need to do is just read through this

shard, which is what this database, database server number seven can do for you,

right? That shard is going to be smaller in size,

it's also going to be easier to maintain,

probably going to give you faster performance. Everything good about sharding?

And the first problem that you have to take into consideration is joins

across shard. If these are across shards,

what's going to happen is the query needs to go to two different shards.

They need to pull out their data, then join the data across the network.

And this is going to be extremely expensive. So one of the problems here

joins.

The second point comes when you look at the pizza and you realize that this is

completely inflexible. The shards are inflexible.

You can't have more pizza slices or less pizza slices. It's already done.

But we want our database servers to be flexible in number.

So one of the really good algorithms for this is consistent hashing.

You should have a look at that. There's one database which actually uses this,

and that is mem cached,

right? This doesn't really implement consistent hashing.

You can use an application logic about the database mem cashed

to get your work done. So it's not really a problem. It might be a problem,

but you can't have dynamic number of shots.

Now to overcome this problem, what we do is take a shot,

which has too much data in it and then dynamically break into pieces.

So this pizza slice is like a pizza for us. Yeah,

when we magnify it enough, it's going to be a really large slice,

and then we break it into smaller pieces.

So there's going to be some sort of a manager for every particular shot,

which is going to map the requests to the correct mini slice,

so to speak, in the pizza slice, single pizza slice.

Using this technique, which is hierarchical sharding,

we can get rid of the inflexibility over here. So point number two

is no longer a big problem. Now,

one of the smart things to do here is to create an index on these shards.

Assuming your query requires that this index could be on a completely different

attribute compared to the user id.

And one of the good examples of this is find me all the people in New York who

have age greater than 50. So if these are the city IDs,

then New York is going to land, let's say here, and then you can index on age.

So you'll find all users

in New York within a given range of age.

So all of your queries are fast.

So that's the most important thing about sharding.

Your read performance goes up and your right performance goes up because all of

your queries fall on one particular point. But what happens if a shard fails?

Let's say there's some sort of electricity issue over there. In that case,

you could have something like a master slave architecture.

The master slave architecture is a very common architecture.

What happens in this is that you have multiple slaves which are copying the

master. Whenever there's a right request, it's always on the master.

The master is the most updated copy,

while the slaves continuously pull the master and read from it.

What then happens is if there's a read request,

it can be distributed across slaves. While if there's a right request,

it always goes to the master. In case the master fails,

the slaves choose one master amongst themselves, right?

And so there's good single point of failure tolerance over here.

Conceptually, it's quite easy. You just take your data,

break into pieces break into ranges essentially,

and then persist in different places.

But when it comes to practical application,

this is quite tough because this guy consistency is

difficult to do.

And if you're just starting out with your system and you you're thinking about

charting, I suggest that you take into consideration.

Although mechanisms like indexing, like using NoSQL databases,

which internally actually use these kind of concepts,

but to use those ready-made solutions or to use well-known

solutions like indexing is probably the way to go before you go for sharding,

a database even more difficult than sharding is to hit the like and the

subscribe button at the same time. If you're able to do that,

then you'll get notifications for further videos and I'll catch you next time.