Pratica di Shadowing: Database Sharding! Designing Data-Intensive Applications chapter 6 - Impara a parlare inglese con i video

Okay, so let's start fresh and then we'll start doing some diagramming.

So what we are doing today is we're talking about this book as usual,

Designing Data Intensive Applications, specifically chapter six today, which is essentially sharding.

Why do I keep blanking on the term that they use?

Because I don't use that term.

They use the term partitioning, which is still a legit database term.

When I think of partitioning personally, I think of like doing different physical partitions, but on the same disk essentially,

because you can partition out tables and things like that.

But you can use the term partitioning for distributing data across many machines as well.

So that's what we're going to do today.

We'll talk about that.

If you are reading along, get caught up to this chapter.

And then over the course of the next week, I'm going to try and read chapter seven and we'll do another stream on that and so on.

And then I will put this up too.

If you're not already following somewhere like on YouTube or on LinkedIn, wherever, you can sign up for this email list and get,

like I said, I forgot to do an email today, but in the future, get email updates.

So let's jump into actually doing some stuff.

So here's some of our diagrams from last week.

let's see oh there was a question this chapter only lists range

and hash partitioning back in the day there was also static partitioning I guess

because of the bookkeeping involved

that no longer is used in practice yeah static partitioning I

feel like maybe I'm misunderstanding that's kind of the same thing as range partitioning

because range you can configure those ranges manually, right?

As just static, like, Hey, all the users from A, B, and C go to this shard, D, E, and F go to this shard.

But maybe you're referring to something different there.

I think that's kind of the same thing in my head, at least.

Okay.

So let's, let's talk about sharding.

So the, the interesting thing

that I noticed about what the book does is it actually talks a lot more about sort of,

I don't know the correct term for this, but like shared sharding in the sense of where you sort of break down all of your data into some size chunks,

maybe one gigabyte chunks or 10 gigabyte chunks, and you do primary and replication in those chunks.

But you might have a single physical server that is the primary for some chunks and is a replica for other chunks.

So it's a little bit more of this distributed environment, and that has pros and cons.

That has advantages for, that does have nice advantages for like, if you want to enable some kind of auto rebalancing,

that can be kind of nice because it makes it somewhat easier to do auto rebalancing, but that can also have negative implications on performance and things like that.

So let's talk about a couple different ways of sharding, and we'll also talk about some stuff maybe that the book didn't emphasize as much.

So let's say you have some application servers.

Oh, that's kind of, let me zoom in a little bit more.

Ah, okay, 100%.

All right.

So we have some application servers and we want these to connect to a database, right?

So typically what you would do if you're kind of at a smaller scale, you would just have a database here and you'd be making connections, right?

So you'd establish connections, you would run inserts, updates, deletes, whatever directly to your database.

And I'm only have one box here, but like realistically, you might not have a single application server.

You could, but you might have multiple, whether that's due to demand, whether it's due to just wanting to distribute across different availability zones or regions,

but you have one or 10 or a hundred

or a thousand application servers that are connecting to a database and doing things.

There reaches a point, right, where that is basically not going to be sufficient to handle your traffic.

And this could be for a few reasons.

One is it could be the sheer number of requests, the queries per second

that are getting sent at your database is starting to get

too high either for whatever the network path is to the database that's starting to get too congested or the database itself,

like maybe it's a machine that has 32 cores and 128 gigabytes of RAM and, you know, a couple of terabytes of storage,

or maybe it's some kind of elastic storage, but like maybe like the CPU is just like pegged at 99%.

And it's like, we need more capability here.

So one solution is, which is called vertical scaling is to just make this server bigger, right?

Oh, let's say it had 32 core CPU and 128 gigs of RAM.

Just, I'm just going to make it bigger by physically growing it.

Let's make this a bigger server, right?

Let's double the number of CPUs.

Let's give it 64 CPUs and 512 gigabytes of RAM, right?

Like, so that works for a while, clearly, right?

You can start with a small server.

And as your traffic starts to grow, you just make a bigger server.

And that actually can work for quite a while, right?

Like, it's not like, oh, you need to go to sharding once you have over a terabyte of data or whatever.

There's lots of instances where you can have many terabytes of data and tens of thousands of queries per second.

And if you have a big enough machine that can handle that.

The other thing though, that ties in here, and they say the same thing in the chapter is like, even if you have a single machine that's serving all of this,

you still are going to want replication for the sake of all the reasons we talked about last time, right?

Like having replicas and other availability zones in case your server breaks.

You can also send some of your read traffic to replicas, right?

So this doesn't mean necessarily literally a single server because then that's a single point of failure.

You probably, even when you're very small, it's like always recommended to have some kind of redundancy for failovers and things like that.

So we'll just put these here and we'll say replica.

So you should always have at least one or two replicas.

But this is, it's still a single database just that is getting replicated to others,

either to handle read traffic or to take over as the primary in case this one fails, right?

So this is sort of like assumed that you want these replicas in here.

You can make these a little bit smaller too.

Okay.

So, but then kind of the reason why we're here is like, eventually this might not be sufficient.

You may reach a point where, you're on a huge box.

You're on a box that has 64 CPUs and half a terabyte of RAM.

And if you have a really high traffic app, hundreds of thousands of simultaneous users logging in, doing things, a social network,

whatever, and you have hundreds or thousands of application servers all trying to connect to the same single monolithic database, that can be a point of congestion.

That can be just too much of a bottleneck, right?

Even a very, very big server when you're talking about a a really high scale application.

So where people turn to after this generally is some form of partitioning or sharding.

And there's a number of ways that this can be achieved, right?

Like it's one term, but people use it in different ways.

So like one really simple way that we could imagine doing this is like, let's say, let's say we have,

you know, we'll use like some kind of social media website as our example, right?

So we have a bunch of users and every user has posts

and they have their bio information and they have likes and they have replies, right?

So we have all this data that's sort of all centered around users, right?

Users are like the main thing and then users have other stuff.

100

So one of the things that you could do when you're like, okay, this has gotten too big.

101

We need to start breaking this up into multiple shards is the following.

102

We're going to take our whole database.

103

Hmm, got to be able to drag this over here.

104

Take our whole database and basically split it into two.

105

This is like a very bare bones, simple example.

106

And we'll see why this might not be the best idea here shortly.

107

But we can do something like this.

108

Let's make sure this fits next to me on the screen there.

109

So we do something like this.

110

Sorry, phone is buzzing over here.

111

And what we're going to do is there's a number of ways we could configure this but we'll say something very simple.

112

We'll say users, and maybe we could do it by ID, we could do it by username,

113

whatever, but username A through, I don't know, A, B, C, D, E, F, G, H, I, J.

114

Let's say J because there's probably more people earlier in the alphabet in terms of the names.

115

Oh, that may not be true, but say A through J.

116

Let's say A through O, right?

117

So all the users, their user accounts, their posts, their likes that have a username in this range are going to go to this database.

118

And then O through Z are going to go to this other database, right? um

119

so this is like a really simple model right

120

and then in your application servers you actually would need to sort of have logic in there

121

that you know you get a user decides to log in

122

and let's say their username is ben right

123

so your your application server has logic in it

124

that says okay ben is trying to log in so in order to fetch his user account information his profile picture

125

the location of his profile picture to load the profile, all that.

126

I've got to route a request.

127

You know, request comes in.

128

Why is that line so thick?

129

Let's do that.

130

So a request comes in.

131

I need to, in my application servers has logic to say, okay, the username starts with B.

132

So we're going to route this request to load all this profile information over to this one of the database servers, send it back, you know,

133

do whatever the application needs to do to format things

134

and then send that back off to my client right my phone

135

or my web application whatever it is right

136

and then conversely right someone with the last name

137

or first name stan right logs in and we need to route

138

that to this other one right because that's where we know we know these ranges

139

and but the the problem here it's not necessarily a problem

140

but oftentimes this leads to more complexity than is necessary the problem is

141

that all of the logic for figuring out which user goes to

142

which server is stuck in your application code right

143

so whatever you've implemented this in could be a laravel app

144

it could be a ruby app it could be a javascript app

145

but all of

146

that logic has to be embedded in every single you know place where a request is made to a database

147

and this of course could be extracted into a library an orm something like

148

that but it all has to be like embedded in your application servers.

149

And that's something that for this simple example doesn't sound that bad.

150

We have two servers.

151

We're doing a very simple division of, you know, part of the alphabet goes to this database, part of the alphabet goes to the other.

152

But the reality is there's a lot of situations where

153

that is actually like pretty bad or where that's going to get really, really complicated because for one,

154

we're kind of assuming a pretty simple schema here of like, hey, everything's sort of centered around a user, but sometimes you have some of your tables you want to distribute based on username,

155

and some of them you want to distribute based on identifier, and some of them you want to distribute based on their geolocation around the world,

156

and there's different ways of distributing the data for different tables in your schema or documents in your document database, whatever it is.

157

And so the logic to figure out which requests go where, which rows get inserted where,

158

which select statements go where, can get really complicated.

159

And there's another complication, there's lots of complications, and we're not gonna be able to talk about all of them.

160

But what happens when you want to do something like, like you want to load a timeline.

161

So you have a user, let's say, you know, we'll say Ben, right?

162

So that's going to, you know, all of Ben's stuff is going to go over here in this database.

163

But what happens when you need to load my timeline for me to see what the people I'm following are posting?

164

Well, some of my followers fall into this one or followees, people I follow.

165

Some of them fall into this one.

166

So I would actually have to, in my app server, have logic that says, okay, in order to load the timeline, Maybe we're just doing a very simple like load a timeline that's sequenced by

167

when it was posted rather than like an algorithmic thing.

168

Go over here, select all of the people who Ben follows from this database in the users table.

169

And then go do the same also over here on this database.

170

Find all of those and then do a join with the posts table

171

and say find all of the posts from all of these people that have been posted in the past 24 hours.

172

So we were doing this join on each server and then we're sending a result set back to the app server.

173

And then my app server has to have logic to take multiple different result sets.

174

Here, we're only looking at two, but this could be more complicated.

175

We could have 10 database servers with 10 different slices of the alphabet.

176

And then we need to actually do like another join or merge, I guess you would say on the application server side,

177

make sure everything is sorted properly.

178

And maybe the data already comes in sorted from the database, but then we still need to make sure we're inserting in the proper order

179

and then send this whole collection of posts back here, or at least some of them.

180

And then maybe we cache others to be fetched later on.

181

Right.

182

So essentially what happens is when you start to do this, and again, this example right here is only showing two databases with two alphabetical ranges,

183

but this same exact thing can apply, you know, you might start to outgrow this.

184

And both of these servers are huge and they're not keeping up.

185

So then you divide it into four and you have four segments of the alphabet.

186

And then six months down the road, your app keeps growing and that starts to be a problem.

187

So now you divide it into eight shards and then into 16 shards, right?

188

And so it's the same principles that apply though.

189

And you're, if you do it this way, where your apps have to directly know which servers to talk to,

190

which ones have which ranges of users on them.

191

That means your app server, which already might be pretty complicated depending on the app, might already have a lot of code.

192

Now you're building out whole modules of your app server,

193

you know, custom ORM or something like that to handle all of this complexity of knowing which database to move things to.

194

And then that gets even more complicated when you have to start, like you have these databases here, but then when you grow it, from two to four, right?

195

So like, let's say we split this one up into two and split this one up into two, then whatever that like custom library, that ORM is,

196

has to sort of synchronize with the upgrade of the databases to make sure

197

that it is aware of any changes to the scale of the database.

198

And it just gets very complicated, right?

199

Like that's a situation that you ideally don't want to be in.

200

See, scary joins.

201

Yeah.

202

Joins, that is sort of a general principle, even with some of the other techniques we're going to talk about, is when you have a single server,

203

doing joins is generally like not that, you know, I mean, you got to be careful about joins,

204

but there's generally no big deal, right? because everything is on the same disk, sharing the same RAM, you can do joins fairly quickly.

205

The moment you start spreading rows out across servers in any way on a database,

206

you start having to pay attention to when you're joining things across tables or across shards more

207

because now joins may require network round trips, which is much lower latency than higher latency than just reading from RAM or reading from a local disk.

208

when we add shards how do we move the data from original shards to new shards that's a great question

209

and again it's an it depends thing uh i will get to the answering

210

that soon because first i want to talk about something

211

that helps with this which is a proxy

212

and then we'll talk about what you're mentioning there is like scaling up or you could be scaling down but essentially re-sharding,

213

changing the way that you want to distribute data across these, which is going to happen, right?

214

Like, again, you know, the size of a database isn't a static thing.

215

Generally, it's growing.

216

It can shrink, right?

217

Like maybe you decide, okay, we're going to start to purge data more aggressively.

218

And then your database actually gets half the size and you're like, oh, I don't need as many shards now, things like that.

219

So you do need some way to be able to redistribute load.

220

So let's, the next thing I was going to talk about here before talking about redistributing load is something that will help.

221

So all of this complexity that we just talked about, what is something that could help me manage these things better?

222

And a big component here is adding another layer between your app servers and a database.

223

And I actually talked, I believe, a little bit about proxies last yeah we mentioned we talked about proxies a little bit here

224

and how this can help with failovers and things like

225

that this is from last week's stream

226

which by the way those are on uh youtube

227

if you want to go let me drop the link to those

228

but if you want to go watch the old streams obviously

229

if you're coming from youtube this is obvious because you're there right now

230

but that's the youtube channel so you can go watch um done a couple of these streams all already.

231

Okay.

232

So in this scenario, adding a proxy helps.

233

Oops.

234

So let's take a look at how that works.

235

We'll move the app servers up here.

236

So use the same distribution.

237

But what we're going to do is we're going to add another layer called a proxy.

238

And let's talk a little bit about what proxies help with in this environment.

239

Proxies can do a bunch of useful things, not just in sharded database environments, but all the time, right?

240

Like we talked about last time, one of the things a proxy can help with is when you need to do a failover or a server upgrade,

241

even without shards, that can be something that helps a lot with making that smoother.

242

So there's a number of reasons why this helps, but one of the big reasons specifically with sharding why we

243

want this zoom in a little bit more is this helps with

244

that complexity that we were talking about of figuring out

245

which rows go where

246

and do i need to send a query to just one

247

of my databases my shards right for reference one of these things here is considered a shard

248

so this is one shard this is another shard

249

because what you can do is now application servers instead of

250

having to open connections to every one of your shards it can have a single connection

251

open to a proxy and there might be multiple proxy servers there might not just be one

252

but each application server can connect to just one of the proxies and then the proxy

253

has the built-in knowledge there of what is the layout of this sharded database

254

so it gets to keep track of and store.

255

Hey, everything that goes down on this database are all my users in this range.

256

Everything that goes to this other database are my users in this other range.

257

Again, we can have more complicated sharding schemes using the tests.

258

As an example, I mentioned the tests earlier, that's a sharding system for MySQL databases.

259

There's a lot of different ways that you can do sharding.

260

You can shard based on ranges.

261

You can shard based on hashes of IDs.

262

You can have lookup tables where you actually in your proxy or you connect to another database, but you look up, hey,

263

for this row, what shard should this go into?

264

So it's sort of a custom thing where you put the information in a table.

265

And then based on that, it can actually four different tables use different sharding techniques,

266

but all of that information can be known by your proxy servers.

267

And then your proxy servers also are aware of what's the, what you would call the topology of your cluster,

268

which basically means how many shards do I have, which ones are the primaries, which database servers are the replicas, and the book talks about this as well.

269

Typically, that kind of information of the layout of your,

270

what would you call it,

271

your topology often is stored in some kind of shared distributed key or data store like Zookeeper or ETCD, right?

272

So that it can get that information from somewhere else.

273

But basically your proxy knows about how things are sharded and which database servers there are.

274

So your app servers can connect to a proxy and they can just pretend like there's no shards.

275

They can just be like, Hey, I'm connecting to a regular old database server.

276

I'm going to connect to this and say, Hey, select star from users where username name equals Ben.

277

Or the more complicated example we did before,

278

you know, select all of the people Ben follows and then do a join with their posts,

279

but only include posts from the last 24 hours and send me back those results and give me back, giving them to be sorted by time.

280

And again, it doesn't really have to worry about the fact

281

that there's multiple servers because it just sends that to the proxy.

282

The proxy parses the query, figures out, okay, can I send this query to, you know, I'm going to get this single query.

283

Proxy figures out, do I need to send it to just this server

284

or do I need to send it to both of the servers?

285

If it needs to bring results back together and join them, it can get the results back together.

286

It can handle the merging of results all here in the proxy.

287

That is more expensive, right?

288

That's still not the ideal.

289

Ideally, you'd only have to send it to one server, but if needed, it can handle like rejoining everything.

290

and then to the app server, it's just a normal database.

291

It just got a bunch of rows and columns back, right?

292

So a couple of questions I'll just bring up here.

293

So yeah, I've used shard IDs before when learning the concept.

294

Yeah, so you can use shard IDs.

295

One of the things that is actually probably one of the most common ways of doing sharding

296

is not to explicitly use names, but is to use hashes, which we'll talk about in a second.

297

Oh, there's so much to talk about.

298

I feel like we're gonna run out of time before we get to everything.

299

Does the proxy perform joins then?

300

So yeah, that depends.

301

Ideally, what we would do is even if there's a join in a query, the proxy should be smart enough, if it's a well-designed proxy,

302

to look at the query and go, okay, there's a join here.

303

Can I just send this join query down here and let this database handle it?

304

In other words, does it have all of the data in the tables

305

that it needs or it realizing oh it doesn't

306

so what i actually need to do is have a query

307

sent to each one of these they get the result sets send it back to me

308

and then i as the proxy do the joining the merging

309

and then send it back so it depends on how smart your proxy is

310

but something like the test is a very smart uh proxy right it has a full like mysql query parser

311

and it can plan out queries and decide, do I send it to just one server or is this a, sometimes it's called a scatter gather.

312

If you need to send a query to multiple shards and then gather the results later, that's the term you'll probably hear.

313

You scatter your queries out and then you gather them up back at the proxy.

314

So yes, but something like Vitesse has a very smart proxy.

315

Will you also need to scale your proxy to avoid SPOF?

316

I'm not sure what SPOF is.

317

Maybe I am just not that smart. But...

318

Oh, single point of failure.

319

Okay.

320

Yes.

321

Sorry.

322

I just haven't heard it acronymified like that.

323

Yes.

324

So, well, scaling your proxy, that might not be the right term to use, but like I had mentioned, you can have multiple proxies set up.

325

So this is one box.

326

Maybe I should say proxies.

327

But the idea is proxies.

328

You might actually have two or three or five or 10, again, depending on the scale, proxies set up.

329

A given application server will only connect to one, but that way if one of them fails,

330

all those app servers just go reconnect to a different one.

331

Can I share a GitHub link where I wrote this?

332

What do you mean where you wrote this?

333

If you wrote your own sharding system, that's very cool.

334

Yeah.

335

Put it in the chat.

336

okay let's see i gotta get rid of that

337

proxies all right so yes

338

and i will just remind people we've got quite a few people

339

that have come in so thanks for coming uh we're talking about this book right now

340

and we're talking about sharding particularly and some of the how

341

that works and pros and cons um so yes let's talk about there's

342

so many things to talk about let's talk about the hashing so like i had mentioned on And a part of the, well, let's back up.

343

A part of the problem with doing explicit range-based sharding like this, there's a few, but one of them is just balance, right?

344

You might say when you first started up this sharding scheme, you know, you looked at your user base and you said,

345

okay, half of my usernames start between A and O, and the other half go between O and Z.

346

And you might have just looked at your whole users and you said, that's a good halfway point.

347

But six months later or a year later, more users sign up, your user base doubles, triples, quadruples, 10Xs.

348

And maybe you start to realize due to like one explanation for this could be simply,

349

you know, maybe your app was popular just in the United States at first, but it blew up and became more international.

350

But then people in other countries might have different letters that are more common for their first or last names.

351

So all of a sudden you start to get a surge of man, my database over here is 2x the size of my database over here because the number of people signing up,

352

it wasn't balanced nice and even across the alphabet.

353

It was skewed.

354

And so now you get this problem of this had worked well before and now it doesn't work that well.

355

So this ties in with one thing we could talk about, which is resharding.

356

And someone asked, How do you like change the way that you shard?

357

One thing that you could do here, and at least the way that this works in Vitesse, again, different in different technologies, is like you would actually, if you want to change your distribution,

358

you would create a whole new set of servers over here. So new shards.

359

And for these, you would say, you know, again, you could add more than two.

360

You could actually upgrade to like four shards, for example.

361

In fact, maybe let's do that.

362

I'm going to get rid of the replicas.

363

Let's just assume we're doing replication, but we won't explicitly write that out for all of them here.

364

So let's say we're going to change our ranges and we're going to add more shards.

365

So we're going to double our shards from two to four.

366

So now I do not know what the proper alphabet distribution is, but A, B, C, D, E, F, G, H.

367

Let's say A to H.

368

Then I'm just going to kind of make stuff up here.

369

H to M.

370

Oops.

371

M to V.

372

V to Z.

373

I guess I shouldn't be include, you know, you know what I mean, right?

374

Like one of these ranges is inclusive.

375

One of them is exclusive.

376

So we create these brand new shards and then we start a reshard process, which basically what that's going to do is while this over here,

377

this is all still working, right?

378

Your app server is connecting to these and making queries, but behind the scenes, your proxies, or it could happen directly from these nodes over here,

379

we're going to go start populating all of these shards, right?

380

So we're going to basically, behind the scenes, maybe it takes an hour, maybe it's a ton of data, and it takes a couple of days, but we're going to copy everything over here,

381

and we're going to keep the data in sync.

382

And then once we're ready to officially make the change, we would do a cutover where the proxy stops sending queries to these shards over here.

383

So we'll kind of move them off to the side as if they're decommissioned or something.

384

so we stop sending things over there just move those down there

385

and we replace those with these ones over here this drawing

386

is pretty getting pretty ugly just going to delete those lines there

387

so now we have four shards

388

and I'll just update this to have four lines actually let's just get rid of

389

that line here I wish I could just what I need here is like neural link

390

so I I can just like think what I want to be drawn and it gets drawn.

391

How cool would that be?

392

It's like every teacher's dream probably.

393

All right, Excalibur, let's do this.

394

There we go.

395

Okay, so now we have these new shards here, okay?

396

And we're no longer using our old ones.

397

And so then we can shut these servers down when the time is right.

398

So that's kind of how this works.

399

One way to do it, right?

400

Another way that the book talks about, let me see, does the book have a good diagram?

401

It did have a few diagrams on this.

402

So let's see.

403

I'm talking less about it, but one of the, actually,

404

yeah, so there, page 211.

405

Let's go here.

406

Okay, yeah.

407

So if you're using like the book talked a little bit more about,

408

if you're using the technique of distributing all of your shards aren't like kind of their own isolated databases,

409

but where you're sort of chunking all of your data into, let's say, 10 gigabyte chunks, when you need to add more servers to your sharded setup,

410

you can keep all of the existing servers let's say you had four originally and you're upgrading to having five,

411

you can basically just take one chunk from each of the existing servers or a few chunks, put it onto your new node four,

412

and then incorporate that new node and kind of switch everything over to, hey, now the data is more distributed and more spread out.

413

But this requires a slightly different logic in if you have a proxy

414

that requires different logic because it's not as quite as clean as this node has one range

415

and this node has another range because you might have a bunch of different disjoint ranges

416

within each node so that might be advantageous for some things like rebalancing but disadvantages

417

i don't know if that's a word if i said that right but it might be a disadvantage for

418

certain performance cases like if you're doing large scans of ranges and things like that

419

let's see new shards can also work like replica of existing shards

420

and populate data yeah so that it depends because

421

if you're changing the layout of the way the shards work

422

that might not be quite as simple

423

but yes you could have them replicate directly uh yeah

424

and then there needs to be there needs to be some form of catch-up to make sure

425

when you do that switch over, everything is fully caught up.

426

You may not want to do that directly to the shard though, depends.

427

But because the proxy is the thing that is actually managing the connections from application servers.

428

So in order to synchronize the switch over, you might actually want it communicating from new inserts and updates from the proxies.

429

okay so anyway just to kind of keep in mind like i'm sort of using

430

mostly one example of sharding

431

but the book emphasizes actually a different kind of sharding a little bit more

432

which is this whole idea of chunking your data and distributing these chunks these partitions across servers

433

so let's see we should probably move these labels to the bottom

434

so the uh other thing

435

that this discussion came from is sometimes using explicit ranges is not actually the best

436

because even with this like okay i redistributed my load

437

but again remember

438

that problem of like oh all of a sudden I have a million users sign up whose first name is, you know, or that is over here, right?

439

I had a million Carls with a C sign up for my application.

440

Now, all of a sudden, not only does this shard have more data on it, but this shard is a lot more strained in terms of queries

441

because a higher percentage of all the queries that are getting sent into here and the proxy,

442

you know, maybe like instead of it being an even 25% each, maybe this is getting like 40% of the queries.

443

And then this is getting 20 and 20 and 20 and so on.

444

Right.

445

So you've got to distribute, you got to try to keep your data distributed and you got to try to keep your queries distributed.

446

So there's not too much imbalance of performance.

447

So one of the solutions around this, someone mentioned this earlier, Maybe I can pull this up.

448

Where was that?

449

Where was that?

450

Shard IDs.

451

I don't know where the comment went.

452

But yeah, so hashing is essentially what we're talking about.

453

And the book talks about this too, that one in my, based on my knowledge, one of the most common ways of sharding is based on hashes.

454

So what you do is you take a hash function.

455

It doesn't necessarily have to be a super cryptographically secure hash function.

456

It could be, but you take your username.

457

So let's say, um, no, let's see what I guess.

458

Do I just want to use a username?

459

Yeah.

460

Let's, um, what would be a good example?

461

Um, well, let's just, I'm just going to do some kind of like made up username here.

462

So ABCD that's a username.

463

Actually, we'll just use mine.

464

So we have a username that comes in.

465

So that way it looks like an actual username.

466

And what you want to do is you want to figure out where this goes.

467

Normally, this would just come in as like an insert, right?

468

Insert this new user with this new username, send it to the proxy, and then it would come down here.

469

But the complications are one data distribution, like we talked about.

470

Another is some platforms let you change your username.

471

Twitter lets you do this.

472

YouTube lets you do this.

473

So if you change your username, does that mean that not only your user row, but all of your posts and everything, if I then changed it to,

474

I don't know, ZDicken, does that mean I have to move all of my associated posts

475

and rows and everything to a different shard, right?

476

Or does it stay here, but then that breaks the sharding rules?

477

Anyway, so one of the things that you can do is run it through a hash function.

478

So you have a simple hash function, could just be like, you know, a SHA hash or I guess, you know, whatever, right?

479

Some kind of hash function

480

that essentially turns a string and you want it to be something that even for minor differences in a username, you get wildly different output, right?

481

So I'm just going to like say it generates some big hash after you run it through this function, right?

482

So now every user, even if I a bunch of users

483

that start with b

484

so let's do another one here um i'm very bad at

485

coming up with usernames um i don't know i'm just gonna

486

say barn right just a word comes in here okay those

487

both start with the letter b in our old system those would both show up on this database

488

but this hash function is going to produce a wildly different hash for

489

that and so this user could end up on this shard

490

and this user could end up over on the first shard

491

and so the what this does is if you have a good hash

492

that you know generates very random-esque outputs they obviously can't be truly random they need to be repeatable

493

but that usually gets you a pretty good distribution because even

494

if it's like oh a new country started supporting my app

495

and there's a bunch of people in that country whose usernames start with A, that's not going to suddenly just slam this server because if there's lots of different names that start with A,

496

some will end up on one shard, some will end up with another, some will end up on another.

497

And it generally does a better job at keeping things even.

498

Probably not perfect, but a better job.

499

So this is a very common way of doing it in the tests, right?

500

That's the example I've used a couple of times, but also in other sharding schemes as well, because it's a good way to get a repeatable output,

501

but an output that varies widely, even for similar inputs that hopefully will distribute things pretty well across all of these nodes that you have.

502

Yeah.

503

So yeah, there's range-based sharding, which is the simple example and can work in some cases.

504

There's hash-based sharding.

505

You can combine them, right?

506

It's not like you have to go for your entire database.

507

We can only use hash or we can only use range.

508

You can actually, in a lot of systems, break this down by table.

509

So there might be certain tables where it makes sense to hash the username and put it in the database.

510

There might be other tables that are maybe more like metadata tables

511

that have nothing to do with a user that you want to hash by something else.

512

It also like which things you hash by have a big impact right

513

so like the main example we've been talking about here is hashing in some way shape

514

or form based on like your username based on you know

515

everything for one user gets lands in the same shard

516

but there might be some applications where it doesn't make sense

517

to shard by username maybe it makes sense to shard by like organization right if you think about

518

github is actually a great example i don't know exactly how their database is sharded but GitHub uses Vitesse, the sharded MySQL.

519

And they, again, don't know their exact sharding scheme, but I would guess that at least some of their sharding is based on GitHub organizations, right?

520

Like you can have a user on GitHub, but if you're like a company or an open source project, whatever, you can have an organization.

521

And then within that organization, you can have your repositories and your users that are part of the organization.

522

So you might shard based on org and everything for one organization, right?

523

Let's say you're, you know, whatever, you're a company, right?

524

That builds web applications and you have lots of different repositories for each of the websites you build.

525

That one organization, everything for it, all of the users who are a part of it,

526

all of the repositories, all of the issues, all of the pull requests, all of that data lives on the same shard.

527

And then there's additional complications with that.

528

Cause it's like, well, a user, you know, Ben J.

529

Dickin, I forget what my GitHub handle.

530

Uh, I think it's BD Dickin, but I could be a part of five different organizations.

531

So do I go on the shard for, you know, uh, planet scale as an organization,

532

or do I go on the shard for the other organization that I'm a part of or whatever, right?

533

So that's like some of the complexities with sharding is figuring

534

out what is the best way to distribute this data to minimize the number of like network hops

535

that are going to have to be made to join data between servers and all those kind of complexities.

536

So it's a very fun thing to, it's a very fun challenge, right?

537

Because like basically this is kind of the interesting thing, right?

538

I actually was listening to an interview.

539

I just tweeted about it.

540

I tweeted kind of like a funny clip.

541

But let me, what's the guy's name?

542

One of the Instagram co-founders.

543

What's his name?

544

Oh, I forget his name.

545

Anyway, he was talking about how there's a lot of companies that sort of prematurely optimize, right like you know you currently have 20 users

546

and you're trying to build a solution that scales to a billion users

547

when you don't even have that yet

548

and his point is like first get product market fit for your application

549

and then you're going to need to scale

550

and you can solve these these scaling problems right um

551

so the cool thing is even though sharding is like it

552

can kind of be looked at as like oh it's complicated there's all these decisions

553

that need to be made about like what schema goes where, how do I shard this table versus that table?

554

How do I minimize the amount of cross shard joins or scatter gather queries that need to happen?

555

Like all that stuff is pretty cool or like those are challenges, cool challenges.

556

But the good news is if you're having to solve those problems as a company, that probably means you have many terabytes of data.

557

You probably have many tens of thousands or hundreds of thousands or maybe even millions of users.

558

And hopefully as a company, you're doing very well.

559

So it's a very good thing, right?

560

And that's like the cool part about scaling a company

561

or scaling a database or scaling an application is usually you're doing it because things are going very well.

562

And then you get to start to get into these really interesting, challenging technical problems.

563

So, yeah, a lot of people have joined in the past few minutes.

564

It's nice.

565

So, what else?

566

What else should we talk about with sharding?

567

What else do I want to talk about with sharding here?

568

Hmm.

569

Well, here's something we could discuss.

570

This kind of ties in with what we did last week.

571

So last week we looked at, let's see, I guess I don't have, one of the things we talked about is like

572

when you do primary and replicas and then you have a proxy in between, how this can be very nice for like if a primary fails, which that happens sometimes, just out of our control,

573

power outage in AWS, or there's a network config issue where your primary can no longer be reached, things like that.

574

if something happens where your primary goes out, usually it can be very quick.

575

Like we can quickly identify like, oh, the primary died.

576

And so we need to remove that and we need to replace it with one of my replicas.

577

That still takes maybe a couple of seconds.

578

And it means that there's a period of time where your database might be unavailable.

579

Although if your proxy is really good, it can actually buffer queries and just make it where, you know, all that happens is it looks queries run a little bit slower to your app servers.

580

But it also means you temporarily lose a replica if you were relying on those.

581

So there's those issues of basically like, when you have a single primary server, even if it's behind a proxy and all that,

582

that is, several people pointed this out, single point of failure.

583

That's the SPOF that I I did not know what that acronym was, but it's obvious now.

584

So single point of failure.

585

With replicas, you have a way to sort of switch over to that, but there still is some period of time where something is unavailable, right?

586

One of the cool things that is maybe a lesser thought of benefit of sharding is that not only, again, like in each of these databases,

587

we should have at least one replica, if not multiple replicas in there.

588

But the cool thing too is even disregarding the replicas, right?

589

Like let's say this database here, turn it red.

590

Let's say that one became unavailable.

591

Maybe like the server actually really died or maybe it just temporarily lost connectivity, right?

592

Like in the data center, something was misconfigured and there's 60 seconds where that server was unavailable.

593

But then a minute later, it comes back online.

594

The cool thing is you didn't actually cause, because ultimately, right, like your application servers depend upon the database.

595

And so in a single database, that would mean all of your users, basically your whole app goes down and is unusable for that 60 seconds or five minutes or however long it is.

596

When you have a sharded database, what this would mean is, okay, there's going to be some negative impact.

597

Some users aren't going to be able to log in or load their profiles or some posts won't show up or whatever, which is bad.

598

But actually, your app probably will still stay online, right?

599

It's just that some users will be impacted.

600

And if you have lots of shards, some companies have hundreds of shards that they're spreading data across or even thousands of shards.

601

It's very isolated where it's like, again, it sucks.

602

You wanna get your users back online as quickly as possible,

603

but it's not something that's gonna impact every user it only impacts some small percentage of your users.

604

And then of course you want to address it

605

and get your replica switched over or get things back online as quickly as you can.

606

So that's another kind of cool thing about, it's like failure isolation basically is another advantage of sharding.

607

And again, this is similar in the other technique that we talked about here.

608

Let me see if I can find like a better picture of this.

609

Yeah, I think like a few pages prior, there's like a good picture.

610

I think it's this page, yeah.

611

So again, this is like the, we have a certain number of nodes, but then those nodes each have different chunks of the data.

612

it's a similar advantage here because

613

if like node one goes down you've only lost one leader

614

and some followers

615

but then hopefully within a matter of a couple seconds things

616

can get switched over to find the new elect a new leader for

617

that primary and all this kind of stuff right so there's a similar failure isolation or failure mitigation advantage here Okay.

618

So this is going back to what we were talking about with hashing shard keys.

619

What was the question here?

620

Zoom out a little.

621

So are there any limitations with hash key?

622

I can think of not able to do range queries.

623

Oh, okay.

624

Yeah.

625

Yeah.

626

This is a good point.

627

So So if we had had, if we stuck with these ranges for our hashing or for our sharding keys,

628

one advantage of this over the hash is like if I wanted to say,

629

hey, give me all users between A and C, right?

630

That could still be fulfilled by a single server here.

631

Whereas with hashing, those are going to be distributed all over the place.

632

So one thing is like, well, do you really need that query? right?

633

Like when should you ever be needing a hundred thousand users all spread across, you know, that are, have a similar username.

634

Maybe you do, but you know, that's all a part of the challenge of scalability is like

635

what kinds of queries are going to run fast on this sharded setup?

636

What kinds of queries are going to run slower?

637

Okay.

638

I think one other thing that I want to talk about here.

639

Let's get rid of the red.

640

One other thing, and this is something that we see a lot at PlanetScale, which is very cool, is when you have a sharded database,

641

it actually makes backups go a lot faster.

642

Because if you think about it, if you had a single database server, let me just say like, so you just had one database server and it was like,

643

you know, 12 terabytes, it's still red there.

644

If you want to do a backup and let's say save it to like Amazon S3 or something like that,

645

you know, you have a certain bandwidth and a certain rate that you can back this up at.

646

So that 12 terabyte backup, you know, let's say it takes, again, it depends on so many factors, right?

647

But let's say that takes, that can do a terabyte per hour.

648

So let's say it takes 12 hours to do that backup, right?

649

Might be too slow, might be too fast, whatever.

650

But that's half a day to complete one backup.

651

And what if you want to take a backup every four hours instead of twice a day?

652

Whereas if your database is over here in sharded with four, what would that be?

653

That would be three terabytes per shard approximately, assuming we've done a good job of distributing that data.

654

So then if we can back up at one terabyte per hour, this whole thing, all of these can be backed up in parallel.

655

We get our backup, it combines it into whatever our S3 storage is, and that happens in three hours.

656

So then backing up every four hours is now very feasible because we complete the backup, there's an hour break, and then we kick off the next backup, right?

657

So we see that as a huge advantage at PlanetScale using Vitesse when people can have huge databases that are many,

658

many tens or even hundreds of terabytes.

659

But if they're well distributed across many shards, they can still back up in like an hour or two, which is pretty cool given that scale and that size.

660

so all right we are at we are slightly past 10

661

so I will hang for a little bit more a few more minutes

662

if there's any questions maybe I'll think of something else to say

663

but uh yeah drop questions if you have them in there I don't know

664

if you can tell but this is stuff I like talking about it's fun and then also

665

if you uh of course want to follow along right again

666

some people probably showed up late what we are talking about here well we're talking about sharding

667

but we specifically have been going over this book there's been a few streams we've done over the past few weeks

668

you can oops that's for signing up for the email list let me find the, oh, I thought I linked to YouTube.

669

Okay, there we go.

670

So there's the YouTube.

671

You can go watch the old streams for what we've talked about.

672

Okay, so there's a few questions.

673

Oh, that's one already.

674

Sharding also helps when you have to adhere to certain regulations.

675

Certain country needs citizens data to be located within borders.

676

Yeah, it can.

677

Some sharding solutions don't quite solve that problem

678

because it still might need to route the data to another country or something like that.

679

So you got to be careful.

680

But yeah, sharding can fix

681

that problem for you where you can have some of your shards in the EU to follow EU regulations

682

and other shards in the US to follow US regulations.

683

Yeah, there's all kinds of interesting data residency laws with Europe, especially.

684

Okay.

685

I think we will call it there.

686

Thank you everybody for joining.

687

This has been a very fun stream.

688

Read chapter seven next, and I will see you all soon.

689

Take care.

Cos'è la tecnica dello Shadowing?

Shadowing è una tecnica di apprendimento delle lingue supportata da studi scientifici, originariamente sviluppata per la formazione dei traduttori professionisti e resa popolare dal poliglotta Dr. Alexander Arguelles. Il metodo è semplice ma potente: ascolti un audio in inglese di madrelingua e lo ripeti immediatamente ad alta voce — come un'ombra che segue il parlante con un ritardo di solo 1–2 secondi. A differenza dell'ascolto passivo o degli esercizi di grammatica, lo shadowing costringe il tuo cervello e i muscoli della bocca a elaborare e riprodurre simultaneamente i modelli di discorso reale. La ricerca dimostra che migliora significativamente la precisione della pronuncia, l'intonazione, il ritmo, il discorso connesso, la comprensione dell'ascolto e la fluidità del parlato — rendendolo uno dei metodi più efficaci per la preparazione alla prova di speaking dell'IELTS e per la comunicazione reale in inglese.

Pratica di Shadowing: Database Sharding! Designing Data-Intensive Applications chapter 6 - Impara a parlare inglese con i video

Informazioni su questa lezione

Cos'è la tecnica dello Shadowing?