 So what exactly I'm going to talk today? So I'm going to talk about the production environment related to DB servers that we have. And it will include all the main transactional and CI reporting DBs. So why I'm including some specific application here? Because like we have only 80% database servers which are on Postgres. So today I'm going to talk only about Postgres related stuff. So next we are going to talk about replication across globe. So and load balancing and connection pooling through PG Bouncer, automation through Puppet, version control for DV level changes, and some about security where it will include data security, user access control, and OS level security. So as the company grows, one thing that comes in free that how your volume of data also grows. And now that comes with new challenges like how your existing setup is there. Maybe that cannot work if you are suddenly going to double, triple the data. So if you can see the graph exactly how the data has grown in like the last six years. So when I think in 2010 we were on that 8.3 version. And suddenly I mean it when the new requirement came keep coming when we wanted the data to be available in different regions. First we started with only one data center that was in India. But as we wanted to do targeting based on your geographical level. So we wanted the data to be available in every region. So when till the time data size was small, it was good. But as soon as data started increasing, so existing setup you start getting so many calls and things are not working fine. So yeah. So today current stats that we have. So we have about 60 hosts that is spread across four regions. And we have different DVs. The size starts the smallest one is like 10 GB. The biggest one that is going to be 20 TB about. And there are four or five transactional TVs which are most critical. I mean that data we can't afford to lose. So their average size is about 80 GB. And normally how much query we are firing. I mean in per database that is about 18,000 query per second that we are firing. And maximum at peak time it can go to 54 K as well. And in this whole app, in reporting DVB, we have every day the data that we are loading that about 200 GB of data that is being loaded there. So how many of you are DVAs here? Only one, two, OK? So I think most responsible person in the organization. I mean you are managing the most critical resource of your company that is the data. And so much responsibility on your soldiers. So when any organization is hiring you as a DVA, so the main they will put their expectation in very simple line that like I have mentioned, OK? I'm being generous here. I've not mentioned 100% uptime. So it's like they want it to be as most as you can achieve. There should not be any data loss. And of course my performance would be good. My queries should always return back in some millisecond. I should not see the pace loading, loading, loading if I'm going for doing anything. So even if it is customer interface or even internal users are there. Oh, if they are seeing the query like that was running for like one second. And suddenly on some day it started to being 10 seconds. They will come running. What exactly happened? Have you made any change there? So now it's like a 10-time growth. So these are all three expectations they put in simple words. But for us when we are going to fulfill those expectations, OK? So it's not as simple as that. So what exactly you need to, basic things that you have to think about if you want to achieve or fulfill these expectations. So the most important thing I believe that is how your DB, I mean what is your DB architecture? When ever it is like existing application or if new application is coming. So it matters a lot. For example, like many times what happens that after going into production you feel that for example, I will take a schema design that how you have done. Maybe you made it too much normalized, OK? You thought when you were designing, OK, I'm being so cool and I'm just normalizing all the tables and making this Paul chunks. Now we have so many maybe foreign key constraints. You have so many relations. When you are writing the query, maybe you are having so many joints there. So that is directly going to affect your performance there. So only thing is not that because normalization in school, I'm going to just use it. I have to also see my use cases exactly on that basis I should design it. Second is multiple disaster recovery plan. So I mean that's the truth disaster happens. In a system can't be always good, healthy up and running. So you have to be prepared for that and not exactly like you have only one plan. You need to be as critical as the database according to that as many as plans you can do, you should do it. Maybe it will go directly on hardware cost or something, but it depends again that you have to tell your management if you want this much, I need at least to do this much. And one important thing is time to time disaster drill. So normally you should not keep waiting that, OK, I have everything ready and when something happens, I will, OK, my things already I will go and just fix it, no. Better to make disaster by yourself and see how you are handling it, how much time you are taking, how much is your response time there. So this makes you confidence that in night when you are sleeping and your system is going down, OK, you know exactly that how you have to handle it. Proper data backup, because you don't have to lose any data. So as many as of backups you can do, just do it. And PR setup, point in time recovery setup. So even if all users are not super user, only DVAs who are super user, but they are also human. They can also make mistake. So what if you, my mistake just dropped something. So you should have a method already in your production so you can move behind the time and whatever was snaps at that time, you can recover it back. So it is just like overview that how a proper DV architecture. I've just made one diagram showing like how the data flow happens there. You, if you have a master DV, how you're planning that, how many slave or stand wise you need in case, how the data will flow from one reason to another reason. And for example, I will just talk here about, yeah. So Postgres is providing two kinds of streaming replication, right? That either it can be asynchronous or synchronous. So if I know that this particular database is that critical that I can't afford to lose a single transaction that happened there, for example, my monetization thing. So whatever payment is happening for any of my client. So I don't want to lose it because it will directly affect the relation with our clients there. So in that case, okay, I will go with, okay, I need a synchronous replication as well where I have the read only application who can go there and read it. But yes, I in parallel, I can set up one more synchronous replication as well. There's a problem that, I mean, a performance issue that is going to happen there because you know that in synchronous, if it is till the time the log is not raised to that slave, it won't commit the transaction and the existing master, right? So maybe the right time will increase slightly there. But again, that depends on your use case. If on the basis of criticality of that, you can just decide that what kind of architecture you want. This is an example. In our use case, we have ads serving in different regions. We have like to make those serving faster. We have like a geo-based targeting in different regions. So we have ads servers running. So we want the data that is being populated in our master database to get replicated to different regions. Also, the issue is it's not like it can be one, because there may be 100 ads server nodes are running, okay? So in that case, they will be pointing to DB. So maybe in a ratio, if you say one to 10, I will need 10 DB server nodes there. So, how I set up my this thing that I don't give overload on network as well, okay? So it should be like, okay, this streaming replication and Cascade is streaming replication that came in version 9.2, right? Before that, when 9.0 got released, you have only streaming replication there, but you had not Cascade. It means from one master to only multiple slaves cannot be, but from that next node, you cannot again do replication there, right? That restriction was there. So the nightmare was that time to achieve that because network was an issue, that volume of data that we were generating there, and we are sending between regions. So we couldn't use this streaming and so we were using Sloney that time, okay? How many of you guys have used Sloney or aware about Sloney? Yeah. So, okay, no offense for Sloney, it works good, but what my experience is when it is over network, I mean, if it is within LAN, it is fine. If there is no issue, your events are happening, it is getting logged in their Sloney log, it's getting replicated, but when it is over LAN, okay? So what happens if you have any network issue? And for example, just think your network was down for two hours or three hours. So till that time, what is happening? Your data is getting accumulated in their Sloney log to get waiting for replicating to that node. So that used to be a nightmare when any network issue used to happen because now you had no option that after three hours, your delay is going to recover. You have to maybe break the replication or you have to set it up. So in this case, when this cascaded streaming replication was introduced, it was means I think most blissful thing for us to get rid of this Sloney architecture and move it to there, okay? So this is one part where you have replication. Other than that, what, I mean, one is there, that hardware level that how you are managing your nodes, that how many nodes should be there, what should be master, what should be standby, what should be reading slave, other than that, what else matters? So proper data modeling that I think I already told about that. So how you are creating your schemas, how you are, okay, Postgres provides inside DB, you have a schema thing also, right? So you can have your logical separation within our database at a schema level. For example, I have one customer table maybe and I have some data, I have different for advertisers and I have different for publishers. So I can create two schemas there. I can create one advertiser schema, I can create one publisher schema. And tomorrow if my data size is increasing, if just think now it's not being handled on a single host where it was already hosted. So in that case what I will do, I can just migrate my maybe publisher schema to some other DB on some other host and it is as easy, means easy as that, right? So application will only need to just change their configuration where they were pointing to host one, now they need to just point to host two or if you're using CNIM then as easy you don't need to change anything else. So this way your, so how to keep, I mean things separate, how to do the data modeling there, how the relation between tables should be there. So these all things matter a lot. Use of constraints, okay? Constraint is really cool thing. You can, you don't need to be dependent on applications. What you want from your side, your maybe foreign key constraint, check constraint, unique constraint. These all things you can handle at DB level itself. So that is fine but many times how you are using constraint again directly affects performance of your application queries that are or stored processors that are being fired on that database. So this also needs to be done carefully. Minimizing the dependency between DBs. So we have a concept of DB link in PostgreSQL, right? So even if you are like want to join between two DBs, you can use the DB link there, right? For example, there's a database A and there's a database B and there is some use case like that you need to query between those two tables, between those two DBs. Now, there's option that PostgreSQL is providing use DB link and you can join between them, right? But it's again, it's a dependency that you are creating or you are just putting in your application, right? That okay, if my those two DBs are sitting in single LAN, so it works fine. But what if again you are in future, there's a chance that you are migrating your DB from that host to some other host or maybe some other network. So it means that becomes a dependency for you when you want to migrate that thing. So it's better to go to APIs when you want to talk between DBs from application side, instead of putting so many dependency within your database design. So that helps with applications, proper retention policies, yeah. So I created my database, I created my tables. Currently it is started with like 1000 rows when to 10,000 rows goes to, it keeps increasing. I'm happy till the time everything is working fine. But it's good to think about when you are designing your data model that okay, is there, do I need to keep each data, each and every data from the start that is existing in the application or I mean, sorry, existing in the database? So if possible, you should think about this point of view also that when designing that and from start only, this should be my retention policy. For example, some, there's application required, they will be always querying data for like last one year. They don't need whatever was there earlier to that. So if at the time of design or application development only, if you think about this thing, so you can have a retention policy where you can just archive your old data and keep your DB, not grow that big and it in a healthy shape. So this is one more concern. So disaster recovery plan. We don't have till now complete auto disaster recovery thing. Still there are two things that we are doing manually. So we had evaluated this pacemaker and this DRBD but exactly we couldn't answer, means for example, your master is going down or there was some blip or it was like not available for some seconds, okay? So in that case, the question, that's what the question was when you want to make your standby as master, so that decision how to take. So if you want to move to this auto disaster recovery, so you have to be like a time limit or in which you want that, okay, sure, if my master is down for this much time, I'm not going to worry if it is going to come up again or not, I'm just going to flip it. I'm going to make my standby as master. So this is one part of disaster recovery thing. Second thing is like what if I'm expecting that when I'm going to test the trigger file on standby and making it as my master, what if it doesn't come up? That is a very rare case, I haven't seen it, but yeah, that can be possibility. That if, I mean, I'm not sure if I don't have any live use case for that or live experience for that, but if it happens, so just thinking about that, you should have second backup plan ready that if your master was down and the standby that you were thinking to expect as come as new master if that is also lost. So next thing that comes, okay, my DB backups, that needs to be kept always up to date, right? So normally, PGDOM, that is the utility that normally we use for taking the backup of the database and it depends on the size of DVs there. If it is very small, so we keep taking the backup and keep compressing it and storing it there for half an hour interval. If it is size is some big, so maybe one hour interval. Yes, it is like not full disaster, it's not proper disaster plan. You have a loss of data of one hour if it is going to be that, but it's like it's second backup plan. You have something, you have not lost everything, right? And this, that I already talked, sorry. That I already talked that I, if what if I by mistake drop some object, I made some wrong updates and I want to divert to some proper time. So yes, it's always good to have one PITR ready and only thing it costs some extra hardware, right? You have, you should have some extra storage space there if it is available. So that's again, I'm repeating same thing. So how much critical is your data on that basis? You can just decide what all type of recovery plans you need. Yeah, so about this that Cascaded as our setup, right? So okay, now it works fine. I have on the network when I'm going from one region to different reason from, so my data is going over network and it's not choking much because between two regions I have only one, one node that's who are communicating. And after that node, I have, I can put as many as slaves that I want, right? If like at server in my application, they are increasing currently today they are 100 and tomorrow they're making adding 50 more nodes. I can also go and just add five more nodes and I'm good. So that is fine, but when you have this Cascaded thing there are some points that you need to keep in mind, right? So I mean it's little bit precaution that you need to take because for example, if your structure is like this, right? You have one master, you have one other slave and from that slave it's like hierarchy, right? From that slave you have multiple slaves going on there. So for example, I have one standby sitting next to my master in the same reason, okay? And my master went down. Now I'm touching my, touching trigger on my standby and making it as master, but in that case if just think these other slaves, other stand nodes which are still pointing to old master, just think you just test the trigger on the new standby, sorry, new master and till the time the existing master that was there it came up. So what will happen, your other slaves, they are still pointing there and they will start replaying from the point where they were waiting. So because now we have started your new standby as master so all these slaves like you have 15 slaves there all are now out of sync. So now if you will point them to your new master they will say that like it's gone, right? So there's no other option than again doing the resetter. So little bit of precautions when you are doing a disaster recovery and you have like multiple cascaded nodes there. So these things need to be taken as care, okay? So now I have like so many nodes which is ready to be used as read-only databases. But how I control that the load between all these nodes that connections that are coming from client is properly balanced, okay? So for that we are, our architecture is there we are using one load balancer, okay? Within load balancer we have PG bouncer for connection pooling and after that we have the DB nodes. So any client connection that is coming that's first pointing to load balancer and it's like it sees where least number of connections already redirects there. And then PG bouncer is there who is controlling that how much connection at a time I can execute and how much I can keep in pool there. So after that it's connecting to DB and returning the results. So this is how existing there. So what benefit I'm getting here? For example, today some issue happened on like two or three nodes in that reason. And I just want to move those nodes and I want to add those nodes. So in that case I have PG bouncer sitting there. I can just, you know, for example, sometime I can increase pool size on existing things. And these three nodes till the time I set up again. So in that case what will happen? Client will even don't know that what exactly happened on the side. They will, for them everything will be working fine. So in that case you are getting for your all read-only clusters you are getting like 100% uptime. There's never a downtime there. Yeah. So okay my all, I mean my setup is fine, my setup is fine, my architecture is fine, my disaster recovery plan is fine. Now what about performance, okay? So these things that needs to be taken care is proper index creation that obviously depending on what kind of queries you are going to fire there, what kind of filters you are using there. So on that basis you have to take care that second is query optimization. So many times how the query is being written may not be the best way how it has been written. So there you have to go to review, you have to do your, you have a very like easy to write, explain, analyze, where you can just see where exactly it is taking more time and you can just rewrite your queries and that will optimize this. There is about a table partitioning when your data is growing like here, I will give you example of our reporting DB. So like I had told that every day you have 200 GB of data that is being loaded. So I think the best solution that could be there, that to control it, that you have created your partition on maybe on the day basis, right? And you are just loading direct instead of doing trigger based partitioning, what you are doing, your scripts are taking care to directly load in those partitions. And when you have like some retention policy of one year for those daily tables. So what you do, you just go and remove that partition. So without any issue, I mean things working fine, even if the data size is too big. Proper vacuum that is very, very important in all this transaction databases. I mean normally your auto vacuum parameter, their threshold should be as that, it should take care of it itself. You don't need to do any extra care for that. But in case if you are doing some intensive deletion or updation, so it's better always to take care that you don't forget to manually vacuuming and analyzing the tables. What else? Okay, the one, I think, feature that PostGas is providing that enrich in, you can write your own user functions, write your, that is stored procedures, whichever language you choose, you are comfortable with. So like we are using a lot, a lot of it in our read only parts for ad serving. Where best thing is you are making your all complex business logic there to retrieve the data from that database. It is saving your time and how many times the client is hitting DB for getting that data. So in all your complex calculation or if you have multiple select things or all you can aggregate at one place, you can return the result in one time there. So and third is get rid of dependency of language. So you are now not worried that your application is either using Java or either using Python. Your, that business logic is sitting here. Whoever is using whatever languages can call this stored procedures and can use it. Yeah, so this is not related to PostGas. I just included is like that practice we are adopting, we have adopted for doing any DB change. So we took, we take care that all the schema change or all any of the type of data change maybe update or insert that is not coming through application but going through DBAs where you are making some, means the schema change there. So you don't directly do it. You always have a version control if it. So you always send a pull request with that change and you make the change. And again you, there's a script that there's a hook there actually which always take the first schema dump from the database and push it into that gate. So this way, every time you have the actually one, one way you are doing the auditing that who made what changes where and what exactly happened which day. So that is a, I think that's a good practice. This is, I mean this is in itself it is a big topic that how you are doing deployment. I just covered two points here just that we are using this puppet and shift models for making these all our deployment automated. So normally any kind of OS level or DB level configuration changes that no one goes and logins on the host and does it but you can maintain through puppet models where you just made changes and there are means puppet set up is there and it takes care of automatically pushing it to the required hosts. And we are using some shape recipes also for packages installations that we are doing there. For example, we have some cron jobs that is running there to, for example, taking a data backup that's a simple example. So those kind of things are being taken care by shape recipes. And this is important feature of PostgreSQL that we are using because you have everything set up. So you have to be sure that things are up and running fine and what all stats you can collect from your database. So these existing so many views that the PostgreSQL is already providing. So maybe you can just write your own script over that and keep hitting those stat tables and may get daily data that how much changes are happening. Next is everything is being logged in PZLog if on the basis of your requirement, how much you want to monitor that and so on that basis if you can log everything and can use other tools like Grafite and Nagios for plotting those graphs or everything there. So yeah, but I'm sure there are maybe if some PostgreSQL committee members are there. So there is still we feel that limitation with how this statistics are being collected there. There are chances that some more column and more fields can be added there. So little bit about security when your data needs to be insecure. So how one way that we are doing is controlling the user access. So you have this PostgreSQL is enriching this providing you how you want to grant permissions. If you want to do it on database level, you want to do it to schema level, you want to do it on table level. So you can properly utilize this thing. Just give them what they need. If someone is asking for you user only who needs some read access, some read there gives them only that access. Don't give them anything extra. Okay, and that even for DVAs, it's not like for DVAs, they all have super user access. But what one thing we are doing, I will just show you the examples there. So this is one example that for the applications user how you can revoke if first thing if your database is by default in your public schema. So by default, they are getting most of the privilege there. So first one practice is you should create the things in some other schema than public. Always good to have your schema there. But even if it's there, you can, you have the option that you revoke the default permission and grant them for if you're, I have used here in like grant all, but what you want you can just give that permission there. This is something one good feature that alter, you can, for some particular you just like, for example, I created a read-only user, okay? And I just gave them the read permission for the tables, but it will only happen for the existing tables. Then when the next time, if you are pushing some new schema change, you're creating some tables. So those won't be affected there. So that user won't have the access on the new tables. So if you want that, once I have given read-only access to some user and whenever I'm changing, without any extra command, it should have already that permission. So you can use alter default privileges for those users. Okay, second is access restriction. So there's, you have PGHB account for that, right? Host base authentication. So you have one thing that I think good to avoid is using trust where they can connect without any password there if they have access to that box. The best way is to use empty five authentication. And here the best part is you can, you know, you can restrict some IPs if you don't want a particular user to connect to a particular DB. And in that case, you can restrict it easily. Also, you can allow also on the base of DB level, user level, so good restriction there. I mean, you can control there very well. Even if you want to use IDENT type in PGHBA, so you can use PGIdentConf as well. If that is not enough, so you have some OS levels, if security as well that you can go through, you can use, we are not using this, these are the option that is just mentioned there because for us, database servers are within VPN and only the clients are allowed there to connect, no outside connection is coming there. So that's already a firewall there. So, but if, in your case, if there's any requirement, so you can control these things on OS level as well. So using SSL or using IP tables or using, you know, as a Linux thing there. So yes, if someone is telling you that in less price, if you are getting something free, you will get quality or service or something, only two things you are going to get. So no, that's wrong. With minimal cost, we are getting a good quality, we are getting a lot of features with thing and we are managing a good amount of data there without much issues there. And obviously next versions are there which are coming with the new features and the thing is, as our requirement is growing, the PostgreSQL community is also that activity is also coming with new features and it's working fine for us. Thank you. Any question? Yeah. Yeah, so I told that till the time there was not cascaded SR replication, we were using Sloane for cross there because we wanted only one node to one node replication. But now we have, we moved to this. Sloane is now only being used for some existing internal databases within LAN. So like for example, in reporting database, I want some data from my customer thing for there. So in that case for five tables, maybe I am using Sloane for to replicate there. Yes, so that's what we are controlling. Sorry, I will just repeat your question if I got it correct. You are telling that how you are controlling the queries which needs to go to slaves and which needs to come to masters, right? So normally it's a user thing that how we are controlling. Two things that is helping us to control this, the user access grant permission and the PGHV from which IP is the queries are coming. So from these two ways we are controlling that even there, for example, I created some read user that I always want it should go to slave, it should not come to master. So I will create a CNA model for that slave thing there. I will create, I will restrict that in PGHV on master that for this user, this should not connect to master. So even someone tries to connect there, it will fail. Yes. So we are controlling it fully that application should use what user and to where. Anything else? Okay, thank you.