Database Tuning at Zerodha - India's Largest Stock Broker

okay um first of all uh good afternoon everyone uh I hope the lunch was good uh but obviously not too good so that you don't sleep off while I give this talk uh and welcome to my presentation of how we use postris in zeroda uh and what what have we learned from it and our mistakes our experiences everything and where we are right now so setting up the context for the talk um to quot my favorite Salman B movie race three our learnings are our learnings none of your learnings uh what it means is that everything that I'm going to speak about here is something that we have learned in our experience in the context of the data that how we use how we import it might not apply to even one person in this room and that is how databases should be it it should not be extremely generic either um you might disagree or be triggered by how we news postgress and that is okay I have been told by uh Kash our CTO to not make any jokes even pg3 months uh little bit about me uh I've been at zeroda for since day one of Tech Team um as the 10x engineer uh these are all memes that we have internally about each other and been managing the backend uh full stack backend for the entire time I've been at zeroda gone through all possible databases um my SQL postest redis uh mongod DV uh click house cockroach the list is endless uh and and before I get into this talk I first of all like to I mean say thanks to the core team of postgress uh because I've come across multiple languages databases softwares Force or Enterprise but I don't think there has been anyone better at documenting their features as well as postgress has done I don't think there is anyone that has a better blueprint of what they want to do in their future updates like postgress has done I don't think there is I might be wrong here again because as I said it's our learnings but I don't think there is anything as resilient as postrace has been for us um and we have done ridiculous things with it and this just worked uh from upgrading from postgress 8 is where we started to postgress uh we right now at PG-13 uh and it has the updation has never caused an issue no data loss nothing and that is U like cannot be more thankful to the code development team of postgress and the community of postgress which has always been super nice in answering any of our doubts on the slack channels so uh history of uh postgress usage in seroa we started out uh first of all let me set a bit of context of how zeroda imports or uses its data and maybe that will be helpful in understanding why we do things with postris the way we do it the as you know V zeroda is an fintech Indian broker maybe I should have introduce zeroda first I don't think everyone knows uh what zeroda is so we are a stock broker uh we uh Robin Hood of India or Robin Hood is zeroda of us uh we deal with stock market and uh we import trade books we basically build a software for people to trade on so which means that we have to deal with all kinds of financial information and it also means Computing a lot of financial information like pnl of uh profit and loss of users how much The Ledger of users how much money they have transferred in transferred out all critical financial information that we store and we use postgress for it uh markets are open from 9:15 to 3 3:30 every day after that M6 is open but I don't think we ever cared a lot about it but uh yeah markets are open from 9:15 to 330 for majority for most of our Traders um and we our systems that we have built some of them are read only throughout the day and become right only at night many of many of the systems that are built are usually read and write throughout the day and night but our systems are a bit different than that and the systems that I have worked on uh we have a trading platform called kite uh which has a transactional DB which again uses postris that is a read write throughout the day but console which is our backend back office platform where all the trade books all the information regarding anything the user has done throughout the day on our Trading platform gets imported in that import happens at night that is the rights of bulk rights happen at night but majority of it it remains a readon platform throughout the day with very few rights so that is the context on which we built our schemas our queries our databases and how we scale so uh we started off with importing around uh so when I joined zeroa used to have 20,000 clients um not even all of them are active and we used to import around 150 MBS of data per day at best and I used to have uh I am saying I a lot here because at point of time it was just two or three of us uh I mean if you have read our blogs you would know that we are a very lean very small team and we still have remained so like that so I used to face a lot of issues with scaling even that 100 MB of data when we started out with um when I look back back at it lot of things that I did was extremely obviously dumb uh lack of understanding of how data Works understanding of how databases work um over indexing issues under indexing everything every possible thing that you can think of can go wrong in a database um for example let's say uh the log files overflowing and causing the database to crash so everything that can possibly go wrong uh has gone wrong with us we have learned from it uh We've improved our softwares way we deal with uh storing our own data so started off with 100 MB uh 100 MB failed uh there was postgress 8 uh improved on our schemas improved our schema design improved the way an app has to built on has to be built on top of um our databases not rewrote our apps multiple times uh again if you have read any of our posts you would know that we we rewrite a lot of our things multiple times over over and over again um it is mundan might be but it solves it solves a lot of headache for us by removing uh Legacy code Legacy issues and I would say Legacy schemas too because you might have started with a schema that doesn't make sense right now uh because your queries have changed the way you deal with the data has changed so we end up rewriting everything we know that nothing is constant Everything Will Change needs to change everything will break and that's okay we are okay with it uh we currently deal with hundreds of GBS of import every single day um uh absolutely no issues at all I mean there are plenty of issues but postgress has worked fine for us till now though we have other plans of doing other things with it but till now again nothing as resilient as good as postgress has been for us so how do we manage uh this big amount of data I've put a question mark there because when we when we started out um understanding our data better I remember this was six years back probably I remember sitting with Nan our CEO and even Kash and Nan used to be like so so we are very close to Big Data right because big data used to be this fancy term at that point of time I never understood what Big Data meant uh I assumed that it's just a nice looking term on your assume right you you're you're managing Big Data um eventually we uh eventually I guess we all realize that all of that is pretty much hogwash uh there are companies which need big data there are companies which don't need big data you don't have to be a serious engineering company if you I mean if you don't need to have big data to be a serious engineering company you can make do with little less data so um I'm going to be this talk is probably going to be a bit of an over overview of how we manage our data till now but um I glad to I'll be more than glad to take questions at the end of it if there are more doubts or anything else uh first thing is uh index uh but don't overdo it so when we started out I I thought that indexing was like a fullprof plan to solve everything that is there realized it much later that indexing itself takes a lot of space indexing in itself uh uh you can't index for every query that you write you need to First understand that there are some queries that need to be fast and some queries that you can afford it to be slow and that's okay so how we have designed our systems is the queries that um are the the the number of queries are higher for let's say a particular set of columns those columns are indexed and uh the columns that are not indexed they might be queried and but we don't index them at all and that's okay those queries might take a long enough long time but they're not user facing they are backend reports that it generated over time not everything has to happen in 1 second or half a millisecond or stuff like that so we're very aware of that when we index we use partial indexes everywhere U that's another thing that we learned that uh even if you're indexing a column you can partial indexing will be much more helpful for you in categorizing the kind of data that you want to search um the second thing is materialized views um I'll combine materialized views and the denormalization point into one uh the reason being uh if if any of you have done engineering here you would you would have studied database systems and one of the first things that that is taught to us is normalize normalize normalize everything right and when we come out we we come out with this with this idea that we need to normalize uh all of our data sets you'll realize that this works well on smaller data as the data grows those join queries will stop working those join queries will become so slow that there is absolutely nothing you can do to fix it so we took a conscious decision to denormalize a lot of our data sets so majority of our data sets majority of our tables have nothing to do with each other and we are okay with that it obviously leads to increase in the size of data that we store but the the trade-off that we get in improvement improvement of query is much higher than the size increase we can always Shard and make our database smaller or delete data or do whatever but query Improvement is a very difficult task to pull off uh if you if your entire query is a bunch of nested joints across uh two heavy tables we avoid that everywhere and one of the ways we avoid it is obviously as I said we denormalize a lot and we uh have materialized views everywhere in our system uh and that is one of the easiest cleanest fastest way to make your queries work faster if there is a bunch of small data set that is getting reused all over your postgress query multiple times over use width statements use materialized views and it will be uh your queries will automatically be fast I don't want to give you statistics about 10x fast or 20x fast and all because it again depends upon data your query your server size all of those things so no no metrics as such being thrown here but it will have a much better experience than doing multiple joints across massive tables avoid that at all costs um one more thing is understanding your data better and by that I mean I feel like uh and this is something that I've learned after talking to a lot of people uh of different companies or uh different startups and how they work and they pick the database first and then they figure out how to put the data into the database I don't know why they do that maybe the stack looks more uh Rockstar like I guess uh if you choose some fancy database and then try to pige and hold the data into it uh picking first understanding the data then understanding how you will query the data should be the first step before you pick what kind of database and how you will uh design the schema of the database if you don't do that if if you say that you know what it's it's a postgress conference it's going to be just postgress in my stack there will be nothing else nowhere uh postgress is like the one true solution for everything so that's that's not going to work um then the next point is post is Db tuning around queries uh one more thing we have uh realized is many people tune the database this something that I came across again very recently while I was dealing with another company uh database stack they have tuned their database in in a wholesome manner that means that the entire database has a set of parameters that they have done PG tuning for uh and it caters to every single table that is there in database and that is a terrible approach if you have a lot of data a better way to do is you tune your D there's no denying that but you also tune your tables maybe a particular table needs more parallel workers maybe a particular table needs frequently vacuumed compared to the other set of tables that you have in your DB so um you need to you need to tune based upon the queries that hit those particular tables rather than the entire database in itself um the last I mean understanding a query planner I'm sure there is uh there's a mistake understanding a query planner so uh another mistake when I started out was I'm sure I don't know how many of you feel that way with a query planner of postgress or any database is a little hard to understand um and I felt that for the longest time I would it will just print a bunch of things and all I will read is the the last set of things right so it took this much time it accessed this much data and that's all I understood from those query planners took me a very long time to understand the direction of the query which is very very important to understand uh direction of the query would be what is called first a where clause and and Clause a join clause in your entire query if you do not understand that you will not be able to understand your query plan at all and it's very easy to understand a query plan of a simple query right if you do a select star from whatever table and fetch that you don't even need a query plan for that if the database is if the if there's if the index is not there that query will be slow you don't need a query plan to tell you that but query plan is super helpful when you're doing joints across multiple tables and uh understanding what kind of uh sorts are being called is very very important to understand I think me and Kash must have sat and debugged multiple queries trying to understand the query planner of it all and pogus is very funny with its query planning so uh there will be a certain clause in which a completely different query plan will be chosen for no reason at all and you have to and there have been reasons where we don't I still don't understand some of the query plans that are there but we have backtracked like into a into a explanation for ourselves that if we do this this this this then our query plans will look like this and if we do these set of things our query plans will look like that this is better than this we'll stick to this and we have followed that everywhere and I don't think I don't think you can look at a documentation and understand a query plan either this is something that you have to play around with your queries play around with your data to get to the point um the queries that I would have in my system on my set of data would have a you reduce a you reduce the data by half and the query plan will work very differently just the way postgress is and that is something that you have to respect you have to understand and if you don't understand query plan uh forget about optimizing your queries DB schema nothing nothing will ever happen you will just keep vacuuming which which brings me back to the last point and this is this is funny because I was in the vacuuming talk the one that happened right before for uh uh right before lunch break so the first thing he said was do not turn off autov vacuum the first thing I would say is turn off autov vacuum so uh and I'll tell you why we do that and why it works in our context and might not work for someone else autov vacuum is an incredible feature if tuned properly if you have seen the tuning parameters they're not very easy to understand what does delete tuples after X number of things even mean there they're not easy to what does nap time mean how does someone who has not dealt with database for a very long time understand the set of parameters there that is documentation and all of that but it's really hard to read an abstract documentation and relate it to a schema um we we played around with every single parameter that autov vacuum has nothing worked for us and I'll tell you why we would bulk we would bulk import billions of row in a fixed set of time now you might say that well if you are importing everything in a fixed set of time why don't you trigger why don't you write your autov vacuum to work right after the UT has been done that UT is never under our control the files can come delayed from anywhere any point point of time and because none of it is under our control we decided that autov vacuum is not a solution for us turned it off because it was it was going to run forever and ever and ever we vacuum uh so I hope most of you know the difference between vacuum full and vacuum analyze but if you don't know vacuum full a very simple explanation vacuum full will give you back your space that you have updated deleted vacuum analyze will improve your query plan we don't vacuum full anything because that completely blocks the DB we vacuum analyze all our queries right after doing a massive bulk UT uh we we realize that um I'm sure if you have been in the talk he spoke about Max parallel workers while autov vacuuming we understand that autov vacuuming uses the parallelism of postgress that is inbuilt into it which we don't but we don't really care about it because this happens late in the night and vacuuming taking half an hour more or 10 minutes more doesn't make a big difference for us at that point of time so in this context in this scenario turning off autov vacuum and running vacuum on our own as a script that triggers vacuum for multiple tables one after the other once are Imports are done works for us but to uh to reiterate again it might not work for your context and maybe autov vacuum is the better solution but remember that autov vacuum has a lot of pitfalls and I will I mean I read postgress 13 documentation a while back it still hasn't improved to an extent that I thought it should have by now and it still has its set of issues while dealing with massive sets of data um but I hope I hope it gets better over time and uh if if some if some code developers can do it then it has to be postest so I hope they do that so yeah um okay so this is another interesting part of the the talk I guess but before I get there um I remember speaking to someone outside and they said that how is your setup like and uh what do you do for um replica and Master Slave and all of of those set of things so I guess this will be triggering for everyone we don't have a Master Slave at all we don't have a replica either uh we have one database and one one node we have started it using foreign database rapper uh why we have shed it like that I will explain I'll get to that um but we have shed it using foreign database rapper so we have divided our data across multiple Financial years and kept older historical fin IAL years in a different uh database server and connected both of them using FDB and we query the primary DB and it figures out from the partitioning that the other the data is not in this database server right now and it is in the other database it figures it out fetches the query for us fetches the data for us um no slave setup uh our backups are archived in S3 we are okay this is by the way to reiterate this is uh a back office platform we do not promise that we'll have 100% off time we are okay with that we understand that if postgress goes down which has never ever happened again thankfully to postgress but even if it goes down for whatever number of reasons we have been able to bring that back up bring the database back up by using S3 within minutes and I have restarted postgress that is pointing towards a completely fresh backup from S3 with maybe 15 20 terabytes of data under under a minute or two so it works so there is there there might be fancy complicated interesting setup to make your replicas work but this also works and I many people might call it jugar uh hacky way of doing things but I don't think it is I think it's a sensible approach we don't want to over engineer anything at all if this works why have a bunch of systems that you need to understand just to manage manage um a replica setup now coming back uh to the question of if we don't have a replica how do we load balance we don't but what we have done differently is that we have a we have a second postgress server that sits on top of our primary DB and acts like a caching layer we have uh we have an open-source uh piece of software called SQL Java which is a a sync uh job based mechanism that keeps pulling the DB and then fetches the data stores it in another postgress instance um and then eventually our app understands that the fetch is done data is ready to be served and it fetches the DV fetches the data from the caching layer so we end up creating around 500 GB worth of I would say around 20 30 millions of tables per day uh I remember speaking I remember asking someone postgress slack a long time back that we are doing this thing where we creating 20 million tables a day and they like why are you doing this isn't there another way of doing it and we're like no this works and the reason why we do this is because uh postgress in in itself supports sorting uh which red this doesn't postgress I mean at that point of time uh it it lets us do pagination it lets us do search on top of trading symbols if we need I mean search on top of any columns that we need to do if necessary so we have postgress setting as a caching layer on top of our primary postgress and all the queries first come to the SQL jobber application they go to our primary DB nothing gets hammered to the primary DB though so the primary DB is not under any load at any point of time I mean there is a query load but it's not getting hammered at all the hammering happens to this caching DB which gets set eventually at some point of time with the data and then we serve the data to to our end users and that remains for the entire day because as I said during the day the data doesn't change a lot so we can afford to cach this data for the entire time duration there are some instances in which we need to clear our C clear the cache we can just delete the key and then this entire process happens all over again for that particular user so that's our that's how our postgress caching layer is Works has worked fine for us every night we clean the 500gb so how we do is every night uh we have two 500 GB discs uh pointing at to the server we switch from disk one to dis two then the disk one gets cleaned up then the next day goes to dis goes back from disk two to disk one and again the new tables are set all over it again works fine uh never been an issue with this um coming back to our learnings with postgress yeah sorry can hello are you able to hear me yeah yeah uh you know you're telling that about kite platform so uh from the kite platform data that is LP enement right so from the kite platform data you are porting to the console database yeah so that is a nightly job yeah that's a nightly job that's a nightly job yeah so that's what you telling in the console uh uh system that create millions of data right yeah so um okay maybe I should explain this again so uh you place your orders your trades and everything on kite right at the night we get a order book or a trade book that gets imported into console we do that to compute the buy average which is the average price at which you bought that stock or the profit and loss statement which you'll use for taxation for any other reason that you might need it for that is why we import data into console so to fetch these set of statements you have to come to console to fetch that now when you are fetching the statement we this caching layer sits on top of that your fetches go to this caching layer first it checks if there is already a prefetched cach for you ready or not if not the query goes through the DB the the data is fetched put into the caching layer and for the entire day the caching layer is serving the data to you not the primary DB the primary DB remains free at least for you as the user so let's say you come in you lay around in console you load a bunch of reports everything is cashed for the entire day in this caching layer so primary DB remains as it is till the till that night so that night we would have gotten all the trades orders any things that you have done on your trading platform into uh console we import all of that we clear our cash because it's a fresh set of data your pnl your Ledger your financial statements have changed because maybe you have traded that day maybe have bought stocks that day or anything would have done happened to your account that day so we clear our cach then next year when you come and fetch the data again all of this is set all over again and then whenever you can keep revisiting console keep fetching whatever amounts of data you want to it will come from this cache unless of course you change the the date parameters only then we uh uh go and fet the data from our primary DP but we have realized that most users use the data of a particular time frame and they don't they don't want to come and check for last 3 years what has happened it is always last 6 months last 2 months last one month and they check that once they go back and uh we cannot obviously build a system where every single date range has to be uh equally scalable and equally available uh we are very aware that the older which I'll talk about how we have shed we are very aware that our older Financial year data points don't need to be available all the time at the highest possible metrics of a server uh they don't have to be at a don't have to be served at a very fast rate either right so these are the decisions that we have taken and it has worked fine for us uh might not work for another person but yeah so I I hope that answered your question one doubt on that kite is also having postgress DB right so are using PG dump or postgress utilities itself uh no so kite uh uses postgress for its market watch uh so market watch is the place where you add different scripts or different stocks and it tells you the current price of the stock uh though we have we have plans of moving away from that to S DB um that has got nothing to do with this uh how I mean I guess you're asking a more of a how a broker Works question or how a trading platform works but you place an order the order comes as an exchange file at the end of the day for us and we import that so there is no PG dump that happens from kite to console so those are completely two different silos that have very little to do with each other they rarely share data among each other and they're never in the hot path because we understand that kite is a u extremely fast read and WR platform where everything has to happen over milliseconds and it can't ever go down so these set of fundamentals will not really work there so there is no connection of PG dumping kite data into console kite Works throughout the day console Works after the day after your trading Market is done that's where you come to console and check how well you have performed in the day so I that's that one more just caching layer do you have in kite also caching layer on kite yeah it's redis it's predominantly redis caching layer so we also use caching uh redis caching on everywhere actually it's not just kite we pretty much use redis like uh if you have used our platform coin uh it used to set on a $5 digital ocean droplet for the longest possible time because everything was cached on a redis uh instance and used to work just fine so we use redis predominantly to cach we don't use redis in console for these kind of caching layer because sorting and pagination is not supported on it uh this is a very specific requirement here it works here so we use postgress here for that is it fine yeah that's I use this skyen console that's why I asked this cool no issues um thank you so our learnings with postgress and um I'll start off with how we because I I remember my my summary of my talk uh that is there on the posters and Etc outside talks about how we Shard and why we Shard the way we do it um if you have seen cus DB extension or a lot of sharding examples all over the world of all the DBS in the world how they set it up is have a have a have a master DB have a parent DB or whatever and have tenets to every single child that is connected to it now how those tenets uh work is that you query the master DB it figures out that these set of tenets are in this uh child setup or the sharded setup and the query goes there we believe that there is no reason to add another column that has these IDs on it we actually in most of our tables we have deleted all our IDs U extra data don't need it so we follow that in a lot of places so um what we did decided was was that we partition our database per month because it works for us then for every single Financial year we put it in a different database uh server and we connect it via FDB rapper and that is our entire sharded setup uh has worked fine for us but I would I would say that at our scale um and our scale is 30 40 terab of 50 terabytes you can say right now um it it's starting to falter a bit it's not it's not a great experience anymore and which is why we are moving to a very different setup different way of sharding maybe that is for another talk but till now we could scale uh to millions of users serving billions of requests uh 500 600 GBS of data per day using just foreign data rapper and a SQL jobber caching layer on top of our primary DB no nodes no uh load balancer nothing at all um so our learnings of postgress um has been that this is something that is a there is a gut feeling when you write your queries or when you write when you look at a database schema that uh our gut feeling is that every query has a time to it like for a particular amount of data for a particular query should not take more than x number of milliseconds I guess that comes with experience many of you can just look at the data look at the query and know that something is wrong if even if it's slow by a few milliseconds you can figure that out so we have a hard limit that certain queries cannot cross this limit and we optimize and keep on optimizing based on that um most of our heavy queries are in an async setup like the job or cash you said we ensure that none of it is on the hot path of an app um there is no glory in storing to too much data so we we delete a lot of data uh so someone was surprised that our total database is 50 terabytes or um yeah probably around 50 or 60 not more than that for sure um and one of the reasons why it is 50 and not 500 terabytes is we delete a lot of data we do not believe in storing data that we do not need what what does it mean is that we uh for most of the computations that we do for most of the Imports and inserts and everything that we do we have a hot backup or whatever you can call it of the last 15 days or last 15 days after that we have checkpoint backups of last one month last two months last 3 months one backup for each month we do not have any backup in between any of those dates because we can go back to any single month and regenerate everyday's data till now we are okay doing that because we have that a night where uh anything can go wrong and we can run these set of computations and come back to the current state that is right now maybe it doesn't work for others but I again this is another experience that I've learned looking at databases of others that there is a lot of frivolous data that people like to keep for no reason at all because it just makes the database looks bigger and I don't know makes it looks fancier just delete it it doesn't it's back it up in a S3 and put it somewhere like don't have to be in a database why does six-year-old data unless it's a compliance that is being set by the the company you work for or the organization you work for unless it's a compliance that you have to do it it can be an S3 backup it can be a file um doesn't have to be in a database and you don't have to be responsible for every query of last 10 years to be served under 1 millisecond doesn't make sense it will never scale don't do that um the other thing that I've also noticed is a lot of people write maybe this is a front end develop are going into backend issue uh where a lot of the logic that should have been done by postgress gets done by the app and I've noticed that in a lot of places and I think that is uh something that fundamentally should change post this in itself can do a lot of competitions like summing average window functions you can do so many things by overloading into postd rather than your app doing it um and I find that strange because your app should be responsible for just loading the queries fetching the data it should not be Computing for most of the scenarios I think I mean I don't know why this this this is something that we had done as a mistake too and we learned and I hope that uh maybe if there is only one learning from my entire talk uh because I've noticed this in a lot of places uh is to overload your postgress with most of the computations it can do it faster than any app that you write unless I don't know you using r or something else but still poist will be really fast so try to do that and um yeah uh as you would have noticed that our engineering setup is very lean we are it's not overwhelming or underwhelming it's stay whelmed I guess uh we we don't overdo anything at all we we always uh we always hit the limits of what we have right now in every possible way and only then look out for other Solutions and it has worked pretty good for us we have never over engineered any of our Solutions till now and we have always organically found solutions for whenever we have come across issues if postgress hasn't worked for us then that's fine we'll find another solution for it so as I said sometimes postgress is might not be the answer sometimes a different database would be the answer for you and you should be I guess humble enough to accept that and move on from postgress most databases are very similar to each other if you go through there how they design the data how the schemas are made unless you're dealing with columa databases they're very similar and this the the uh the fundamentals remain the same across all databases if they are not then that is a wrong database so even if so your route is experimenting with click house a lot and the fundamentals are very similar so do not be afraid to experiment with a different set of databases we all do that a lot in our free time uh because because I mean it's a strange way to I guess end the talk but post gu might not be an answer for every single problem though we found an answer for a lot of our problems and you should be okay with that uh thank [Applause] you hello um so even if the application users are you can have R inside post so that that that Sol the problem anyway but my question is uh when you say the caching layer has 20 million tables um do how do you take care of the catalog bloat or do you just drop and recreate the whole cluster every night we just rmrf the entire data okay Fant yeah that's what I was thinking the other problem is uh even with that um I've had scenarios where uh you run into extfs or whatever file system related limitations on because like poster stores everything in a single directory right so have you had hit something like that and if so what do you do yeah I mean U I would I would categorize it as some of the mistakes we did at the beginning the file limits were wrong at the to begin with but post that we' have never AC never really come across any file limit issues uh we have I mean more than happy to admit it we have come across issues where the we have run out of integers for our ID because that's a number of columns we stored in one single go that also has happened so uh and then the import stopped then we had to do a ridiculous amount of things alter you know how much time would have altering the table would have taken but but no we didn't uh it was a it was a server configuration mistake that from our end but it was never the issue of post so I haven't come across it in my experience okay thank you so you said you hardly have any crashes or any know downtime so is it with some kind of a ha solution or it's just you know the instance doesn't crash what's the magic oh what's the m i mean I think the magic is by the post developers no uh I think the reason we don't have a lot of Crash is we um we have ensured that all our apps are not sitting on top of massive databases they're always sitting on top of caching layers one uh you cannot ever ever ever scale an app on top of 10 20 terabytes of data and expect it to work without crashing it will crash if that happens it will overload and we have crashed our databases but the mistake was not of postgress that is wrong to expect that the mistake was that we thought our app can easily query that much data in this much amount of time and be fine with it it will never work as soon as we meet it asnc as as soon as we made it uh behind our caching layer it worked absolutely fine so it's uh again to there's the same answer it wasn't the issue of post it was our mistake that we had to rectify thanks okay so we'll take last questions after that you go offline questions uh this is regarding today's morning session right like kaas was addressing that uh before covid you could able to take uh 2 million request and during covid like you are able to scale up to 8 million to 12 million uh without scaling your system how did that happen um okay um I'm going to S sound a little dumb here I guess but caching is a magical layer on top of everything I guess we were already ready to serve uh we did increase we did increase our primary DB servers uh the number of cores number of parallel workers that query the database all of those tuning had to change obviously now was it over provisioned uh no it was never over-provisioned it was always 1db so there is no over-provisioning 1 DB it's not like it was multi- sharded setup so it was 1db we added more cores to it the the jobber is a separate server that runs the the caching server that we call it right right so that was never over-provisioned that is still whatever we started with it's the exact same setup till now 16 CES 32 GB Ram still now and that's how we started three years back uh works fine um I don't know man the I guess that's how good the caching layer is uh you can say that probably we over proficient before that because when you we by default start with this 16 uh course 32 when you're dealing with a pogus DB because we are used to tuning it for that so we know the tuning parameters for those set of numbers so that's how we start off with that usually in that case maybe that's how we started here like that we thought that it would work fine have you ever forecasted that have you ever forecasted that load uh sorry I couldn't load load load tested uh yeah couple of times uh the maximum load that we have gone to uh was four or five uh and that's it it's never been more than that our post database has been overloaded multiple times and every single time it has been loaded has been our mistake where we have skipped the caching layer and hit the database directly and as I said that will never scale it doesn't matter if it's one terabyte or 500 GB it it will not work so we have every time we consciously write a new API endpoint we ensure that the first uh thing first Frontier has to be the caching layer on sitting on top of it and everything has to be async it cannot be concurrent uh it cannot be concurrent queries hitting the DB and uh an HTTP API endpoint waiting for the response to happen uh again that will not scale your app will go down for sure eventually everything will be in a weight IO situation and nothing will work thank you

Channel: Perfology

Views: 74,153

Keywords: Database tuning, Zerodha, stock broker, database optimization, performance tuning, SQL optimization, database management, Zerodha technology, Indian stock market, tech tutorial, database performance, database tips, IT infrastructure, software development, database best practices

Id: XB2lF_Z9cbs

Length: 44min 36sec (2676 seconds)

Published: Sun Jul 07 2024