This SQL Trick Powers Netflix-Scale Load Balancing

Imagine Netflix drops a brand new season of a show and millions of users hit play in the same minute. Now, here's the problem. Netflix doesn't have one server. It has hundreds and they're not equal. If we send equal traffic to all, the smaller servers crash first and the whole system slows down. So today we'll build a weighted load balancer in SQL server using window functions, cumulative ranges and a simple modular trick. Let's begin with the problem statement. Netflix has three edge streaming servers available in a region. A higher weight server means the server can handle more streaming sessions. And the requirement is that for every incoming request, assign it to a server in relation to the weight and capacity of the server. Which means that the traffic is weight distributed in the ratio 5 is to 3 is to2 in our example. We have the two tables. the servers table that stores server capacity as weights and the request table which has the unique request number in request ID the request time and the user ID who made the request. Now the core idea here is that the sum of the weights of the three servers and the example is 10. So every request should map into a slot between 1 and 10 and that is what we'll be identifying to a SQL query. the slot assignment using the modulo trick for each request. So first of all we are going to think of total weight as total slots. So in our example since we have the weights as 5 3 and 2 the total slots are going to be sum of those which are 10. So for server S1 there are five slots for S_2 3 S32. So now we're going to build the cumulative slot ranges using the window function. So what we going to do is we're going to use the sum function on the weight. We're going to calculate a rolling sum and we're going to order it by the server ID and call it as end slot. If you execute till here you'll see the end slot is the weight for that server. So the end slot for S1 is five. Now we need to identify what is the start slot for server S1. So the start slot is simply end slot minus the weight of the server plus 1. So the end slot is derived through this formula. So I'm just going to copy paste it here. I'm going to subtract the weight of the server. So wait plus one that's going to be the start slot for each server. So execute and you'll see that for server S1 the start slot is one and slot is five. For S2 it is 6 8 9 10 and so on. So now we have the ranges defined for each server. Now we'll also need the total weight or the total capacity of all the servers in a system. So we are going to calculate it in this SQL statement itself which we can later use in the next queries. So total weight will be simply sum [snorts] of weight and since we're using window functions we have to use the over clause but this is the total sum that we need not a cumulative sum. So we're going to keep the brackets empty. Now if you execute this you will see that the total weight is 10 for each record. We're going to put this within a CD. Let's call it server ranges as. Now in the next step, we're going to assign a slot to every request from the request table. So request one goes to slot one, request two goes to slot two and so on till it reaches the 10th slot and then it starts again from the first slot. So this is also called modulo hashing for repeatable routing. Now to implement this logic and give every request a repeating number from 1 to 10, you're going to use a formula to calculate the slot. So slot is equal to request ID from your table. So request ID minus 1. So the modulo starts with zero that's why request ID minus one. So for the first request here 1 min - 1 is going to give you zero. And we'll see once we write the whole formula why we need a zero here. Then we use the mod function and the next argument is the total weight. So the request ID mod weight and

the total weight is 10 for all the three servers. So we can pull it out from the CTE that we just defined. So total weight from the CTE. So select max of total weight because we just want a single uh record in the output. That's why we're using max from the CTE that we generated server ranges. So server ranges and then we're going to add a one because the slot numbers start from one. And you can see on the screen how this modulo formula is going to calculate the correct slot numbers and a repeated cycle of numbers and slots from 1 to 10. Let's execute our code till here and we can check it in the code. So you can see for request id 1 till 10 we have the slots 1 to 10 and for request ID 11 we again start the cycle from 1. So this is a useful algorithm that you can remember when you need to create a repeat table cycle of numbers. It's also useful in some weekday calculations like if you want to calculate the next Monday then Monday after that and so on. So now that we have the slots the only thing remaining is to assign them to a server. To do that, let's put this also within another CDE call request. Let's call it as request slots cuz the table is already called request. And then let's write our last select statement. So we need all these from the CTE. all the same columns from the CTE and we are going to join it with the server ranges CT that we created on the request slot that we generated between the start and end slot for each server that we calculated in the server range CD. So s start slot and s dot end slot and then we can pull in the server ID. Server ID from the server ranger CD can call it routed. So order by the request ID and execute. And you can see that the first five requests have been allocated or routed to server one. The next three have been routed to server two. Then server three and so on. And this is the entire query that we used. And that is how we implement a weighted load distribution.