 Everyone, welcome, thanks for coming to my talk. I'm here to talk about Postgres at any scale. Just start off some contact information if you have any questions afterwards. My Twitter is Lineweber, I'm WillLineweber, and the company I work for is CitusData, and that's our website there. For those of you who don't know me, you may have installed my wonderful gem bundle, which all it is, is it requires bundler. I made this a long time ago, and after getting frustrated because you know, you do bundle install, bundle update, and so on, but when you want to go install, you have to do gem install bundle ur, and I got tired of making that mistake myself so many times, I just made this gem. And it has, all it is, is a gem spec, and so this might be the highest number of downloads per line of code of any gem. So you may have this on your system. So anyway, this talk, I'm gonna go over three different stages of life in a Postgres database, starting with when you're just starting out and your data is small, and how to take good advantage of Postgres' features at that small scale, and then when you get into a medium size, how that sort of changes, how the operations work for medium-sized Postgres database, and then when you get to very large scales, some strategies of how you can handle a lot of data. And so, it's not, when you talk about large sizes, there's a couple different things that you can mean. You can, it, Proper Use of Postgres depends differently on if you overall data size, but then also on the flux of the data, how much data you're putting into the system, how much data you're pulling out of the system. You know, a database that has just a little bit of data in it, but you're constantly writing and reading is gonna behave differently than a database that may have a lot of data, but you're only ever reading maybe the most recent amount and you're not really touching any of the old things. So these are, when you're looking at designing, not only your system, but what servers, types of servers you need for your database and so on, it's important to look at both the overall size that you're gonna have and the data processing that you're gonna use. And so before you even get started, you wanna plan, if you can, where your database is gonna end up, where your application is gonna end up. And that's, you know, of course, easier said than done. Oftentimes things start small and they just keep snowballing out of control while you're scrambling from fire to fire and then it's easy to ignore the database part until it becomes a little bit too late. So I hope that some of the things in this talk will help prepare for that, put some things in place when you are small and you have the time to sort of put some of these constraints in place. Number one, the very first thing is please take backups. Now every database talk that you go to, someone's gonna say like you should take backups and yes, you should do this. And it's important to not only take the backups but also restore them from time to time to make sure they're good. And you wanna do this when you're just starting out your database is small because the feedback loop of knowing that you have a good backup system in place helps is a lot easier when the data is small because the total backup restore cycle is so much quicker. And you also maybe don't have as much going on to distract you of like a system that's been running in production for a while. When you're also small, the tool that comes with Postgres PGDump it's gonna work very well. What PGDump does is it takes a logical backup and so that means if you need to restore to a different system architecture, a different version of Postgres, that works just fine. We're going to talk a little bit later about another tool that does what's called a physical backup and that one you need to match the same versions and so on. And I don't wanna, it's, again, very important that you test your backups. You have heard this before, you'll hear it again. I said it before, I'm gonna say it again because it is so important, please test them. You don't wanna be in a situation where things are on fire and that's when you find out that your backups don't work. And so please, I just wanna stress this enough. The main thing though, that I think is interesting to think about Postgres when you're at a small size is that you can embrace the constraints that Postgres allows you to have. It has a huge number of constraints that you can opt into. And what I like to think about in this is that you get to enforce the assumptions that you have about your data. Now you might be storing, you always think that all of these integers that I'm storing in this, like it's the user's age or log-in count, you might think that there'll always be a positive number. There's ways in Postgres that I'll show you that you can enforce that to be true and that way it's not just assumptions in your code that is relying on that data being there but it enforces it at the database level. And this is really the best bang for the buck that you can get out of Postgres and I don't think people think about this as an advantage of Postgres as much as it actually is. There's a ton of that you can do with Postgres, fancy common table expressions, index types, esoteric index types and so on but I think this is sort of the unsung hero of Postgres and we're gonna go into that. So it also took me a long time to come to this realization that the constraint system is as powerful and useful as it is. Aside from, I'm sure that a lot of people know that you want to use, if you have a unique column, it's not enough to just say validates unique in Rails because it could change underneath the hood so you need to put the unique inside the database constraint. But a lot, and so a lot of people know that but all of these other constraints that we're gonna go into are for some reason or another like seem like they're much less well known. And despite Rails and popularizing the things of embracing constraints, that kind of stopped at the database layer and at least for the initial several years of Rails history, the database was treated just as a replaceable hash and you could swap from one database to another and that's all great. But I don't subscribe to that myself. I think that if you can pick one database and really use its features and embrace their constraints and keep that notion from your application down to the database layer, you can have your database do a lot for you. And what I'm happy that in the last couple years, ORMs like Active Record and my favorite Sequel has let you use more and more of database specific features instead of just the least common denominator feature set across all of the SQL databases. And the reason that I think this is so important is because let's say that you write bugs at a constant rate between modifying your database and modifying your code. And I do mean you, I've never written a bug myself, my code's perfect. But I understand that this happens. And so if you say that you write codes, bugs at a constant rate, the problem is that your code changes so much more frequently than you change your database schema. Just for an example, like one of the apps that I work on, it's a little over a year old. It's had 71 migrations, but it's had over 1200 releases. And so in each one of those releases is a chance for a bug to be introduced into production. And since there's so few changes to the database relative to that, it's easier to get that right and have that be sort of the last guard against bad data. And so if you get something into your application code that starts writing some inconsistent data, maybe not like database inconsistent, but inconsistent with the logic of your app, it could go on writing this bad data for weeks before you notice. And then you have this horrible, horrible cleanup process to do, because you can't figure out what is true and what's not. Cleaning up a mess like that, I've seen it happen, I've worked with people who are working through that and it's just really a nightmare. And so if you use your database sort of as a last line of defense against bad data, like of course you don't want bugs in your application, but if you can have your database reject some of this bad data, you're gonna be in a much better place. And also, so one, it doesn't let bad data get in. And then also with the database is doing the rejection, you'll know right away when you deploy something bad to production, because you'll start getting errors in your application logs that your database was rejecting these writes. And so let's start off very simple. This is maybe the simplest constraint that you can put in your database is adding to your columns not null. And despite how simple this one is, I see time and time again of applications that because by default, when you create a table, you have to opt into it being not null. I really wish that the SQL standard was the other way around where all columns were not null by default, and you had to say, okay, this one I want to allow nulls, but unfortunately that's not the world that we live in. But as simple as this is, I bet right now if you look at your application and you can say, oh, of course, all of my users have an email address, or something like that, but if that's not a primary key, if that's not a not null, if you go look right now, there's gonna be some weird cases where something went wrong and that is nulled out. And as simple as this is, so any of these things that you think, oh, this should always have that, that's an opportunity to go enforce that in the database. Moving on to a little bit higher up is, if you say all my users are gonna have a unique email address, go ahead and make that a unique index. Now, if you pay a little bit of penalty and performance if you're creating an index that you're not using and that's the only way that you can enforce a unique column is by having a unique index, but at this stage of the game, when you're a very small app, it's better to take that performance hit because you're not gonna notice it and have the things being forced to be unique and then later on, after your application's been going and this is actually the bottleneck, you can go ahead and drop it later, but go ahead and go through all of your columns, all of your tables that like, this should be unique, go ahead and make it unique. One of the other things that's also very interesting that Postgres lets you do is you can have a unique across two different columns and so if it's, for example, you have like a reservation kind of thing where I don't want anyone to have a, have the two reservations like with the same person with the same other room, you can make the unique index on both of those and so you can have one user has several reservations and one room has several reservations, but together that is only one set of uniques and that's a very powerful thing that you can take advantage of. A little bit different is to think about data types as constraints. One of the great things about Postgres is that you have like a really wide range of data types. You can have, you know, your various integers, various text columns, but you can also have data types such as I used for one project the Inet data type because I was storing MAC addresses and I was able to make sure that only valid MAC addresses were being in. There was a little bit of a little bit of advantage because it stored it just as the number of bytes rather than the string, but really it was just kind of nice to have, make sure I know that if anything goes wrong, I'm not going to get, you know, someone's like name inside the MAC address column. Like as unlikely as that is, like I can know that that is prevented. Another thing you can do is, I've seen some people start to get excited about this part of Postgres and start writing their own data types because the extension system is actually pretty straightforward so you can pop your own semantic data types in your database, which is nice. And then JSONB is a new data type a couple years old and what's very nice about this is if you are storing JSON, it can be, you know, make sure, first off that it's well formed, even if you don't use any of the special operators, but then there are a ton of special operators that you can get data in and out and there's a bunch of great contacts and other talks about using this sort of data type, the JSONB to have like a sort of semi-structured approach, which I use in almost all of my tables of having, you know, a couple of my real columns and then a JSONB column so as my semi-structured grab bag to sort of get the advantages of both the relational model and then sort of like a no-SQL semi-structured model. I don't really want this to be a super code heavy talk, but this was a little more work to show this one off. The, so more, one of the really cool range data types that isn't super well known, especially amongst Rails developers is the range data type and so this stores a beginning and an end in one column together and you can do some really cool things like make sure that the beginning is always before the end, like that just happens for you, otherwise it'll redo it. You can say that this is an exclusive or exclusive range, if it includes or excludes the ends. You can use it with numbers. I've only ever used it with timestamps to sort of do a thing and this is actually straight from our billing table. What we do is so that when you create a formation with us, we store when you created it and then the end time we store as, Postgres lets you deal with infinite timestamps and so we store the end as infinite and then when you deprovision it, we mark that as the end period and then we can make sure with a little bit of some extra work that there's no overlaps in these periods and so what this is called is an exclusion constraint and so we can say we never want to accidentally be billing someone twice for the same formation. So like for example if we change the price on something, we'll end the old one and start the new one but it would be a disaster if they overlapped it all and of course we have checks and code that make sure that doesn't happen but having this be fixed in the database was just so much a weight off my mind knowing that the database is gonna reject any sort of things that would result in billing someone incorrectly. There is one little trick here. We're using UUIDs for our primary columns and unfortunately you can't easily use UUIDs in an exclusion constraint and we found this little hack and what that does is it's an internal method of Postgres that takes a UID and sends it out as just a byte array and byte arrays do work in exclusion constraints so it's unfortunate you have to do this but it's a nice little trick. And then enums, these ones I'm not super sold on myself like I'm using them more and more now. For example, they're very good for like small sets of keys that you're not gonna be changing so much so for example I'm using them for AWS regions because sure they do add more regions over time but it's not that often and this just prevents like little typos of like I know one time I said like US West one instead of West like manually in IRB and then that got saved and then like that was hard to track down so having that, you know, again like it just prevents these little tiny little small errors and then if all that's not enough I know that was a lot but if that's not enough Postgres has this one last thing which is really awesome called a check constraint so you make a function usually in just in PL SQL in Postgres but I've also done this with the PL v8 which lets you run JavaScript inside Postgres and use that for my check constraints and what this is is a function that you define and every time you do a write, an insert, an update it'll just run your row with that thing and then you can say is this good or bad and so this way you can do things like you can say that I only want positive numbers I only want maybe for some reason you only wanna store Fibonacci numbers you can do a check like that like really any sort of custom validation that you wanna do you can do honestly though this is probably in sort of the diminishing returns like this is sort of like I don't think I'm using any check constraints in production right now it's nice to know that it's there but this is maybe getting a little bit too far and so that's it for sort of the small sizes thinking more about medium sizes this is maybe when you start getting above 100 gigs or you start doing a lot of writes the interesting thing about this period when I was putting the talk together is I realized that most people don't spend a whole lot of time here either it's an application that stays small or it's one that's just like shooting right through medium up to the large scales but there is still some times to take a look at your database and make some improvements at this stage so hopefully you're in a good place with constraints and data types and all that stuff from before if not this is a good time to go review that and like do some homework but so if you assume that we're in a good space with the structure of our database the biggest difference here is you start running out of RAM on your database to keep everything cached and so you can do some strategies like deleting old data that kind of helps but it tends to just kind of postpone the problem this is when you taking logical backups with PG dump doesn't quite work anymore this tool here Wally so me and my colleague who wrote this we spent five years at Heroku Postgres and we ran this on every single database cluster on Heroku and we're using it today that's all open source if you're running a database by yourself please please please use Wally what it does is it takes the right ahead log files that Postgres generates and sends it off the machine to S3 there's some other plugin backends if you're using Azure or GCE or something like that there's other backends it can do but the main one that most people use is S3 and what's great about this is if the machine were to disappear completely off the face of the internet you can restore everything from these right ahead log files and fortunately back in 2011 right before one of these big, big Amazon auditors we just put this in place on everything on Heroku Postgres like right before the auditors like a couple weeks and if that wasn't there I would probably still have a job but I don't know how good the database team would have been to it would have been it would have been it would have been bad so please please please use Wally again it's important to test this one thing that we do is we use the Wally infrastructure for when we want to do like a create a follower or you know so on like we're using that same infrastructure all the time and that way we know implicitly that these backups are good and valid this is when you want to start thinking about having a hot standby so you know having this running continuously so if your main thing goes you want to fall over to it when you're at the smaller sizes it's not that important because you can just restore quickly from any sort of backup but when you start getting a lot of data just the time it takes to you know send the data over the network to start restoring it just starts taking longer and longer so having a hot standby up is very good one of the nice things that Postgres has that again isn't super well known in developer communities is with the async replication it has on a transaction by transaction basis you can say this is an important transaction I want this to be synchronous like let's say it's a new user signing up and that one it won't return until it has written it to the replicas but for the most of your transactions you can leave that as asynchronous mode it'll return right away and you just trust that it'll make it there eventually and being able to mix those two for your important and like not as important data is extremely powerful again you have to invest in monitoring and learning and this is another thing that everyone says you should do but people tend to do only until it's a little too late and this is you know sort of a theme of a lot of these things when I was putting this talk together like a lot of these things are like I can get up here and tell you oh you should do this, you should do this but I was trying to think of like why is it the case that everyone says you should do it but like it turns out like no one is actually doing it and I was reminded of this talk that I saw a YouTube video of flirting with disaster is the name of the paper and then the talk was this results in complex additive systems you can look for that on GitHub and I'm gonna redo part of it here because I just thought like when I was reminded of this this is actually maybe the explanation of why people don't do what they ought to do and so if you think that you have this one of the boundaries of a system is the economic boundary and you can go towards it but if you go past it this is when your company fails and goes out of business and so there's a natural gradient they're pushing you away from economic boundary because otherwise your company is gonna go out of business and so you go up to the line and then you get pushed right back down there's another boundary of the workload boundary this is how much you can work your employees at your company and again like if you push it too far they get burned out and quit and so again there's a natural gradient away from there and then finally there's a performance boundary and this is sort of the interesting one for our systems the problem is you don't know where the performance boundary actually is until you go past it and so what we do is we set up this error margin and we say we're only gonna operate systems at like this capacity or this is the performance these are the how many requests per second we wanna be hitting and no worse than that the problem is you tend to go over it and then you kinda freak out and you go oh no and you bring it back and you keep going over it and then going back but then after that happens a couple times you're like why are we freaking out so much why don't we just push it it's fine the system's operating fine let's push the boundary back the problem is you don't know actually where that actual boundary is until eventually you go over and you have a big outage and it's a problem and that I never got to use the fire thing before so I wanna just that's nice and so anyway so you have a big outage and everyone freaks out and you're like okay we never want that to happen again I don't care how much money it costs I don't care how much we have to work let's never have this embarrassing outage again so you push it all the way against but the problem is over time like you start thinking like oh yeah you remember that outage from a couple years ago oh yeah that was bad and like ha ha ha and then you're right back repeating this again and again and I think that really explains why there's all these things that you know you should be doing but until you hit it like that's what happens and I really yeah I remember when I first saw this for the first time like I encourage you to go watch this talk it's only like maybe 20, 25 minutes on YouTube and it's he does a better job of explaining it than I just did but I really thought that was an interesting way to think about systems so finally you start running out you know no matter what strategies you do you put in place eventually if your data keeps growing you're gonna come to a point where it just can't fit in a single machine anymore and so what you can do there like a very popular approach is sharding and so this is instead of having your application which is the star talk to one giant database you make lots of little small database and have your application talk to to all of them you know people have been doing sharding for a long time there's a lot of homespun approaches you wanna you know a lot of people do this in their application layer but this is where you know the company I joined a year ago is sort of meant to help with that and so Citus is an open source Postgres extension that you install on Postgres you install on a couple other nodes of Postgres and it transforms it into a distributed sharded database and yeah so it's open source you know you're welcome to go check it out and run it yourself the so as soon as you outgrow the single-node Postgres what Citus does is it takes care of not only just the sharding so when you do a write it goes into the correct node but also the reads it'll distribute that out against all the machines in your cluster and so on this is sort of a complicated a complicated view of it but it's actually the approach that we take is actually pretty simple and straightforward and so we have these idea of workers and shards but really all they are are Postgres servers and tables and so you can go peek into all of these things inside and it's just you know standard Postgres like that's being stored and used so for example I have these two tables one is cloud watch metrics and the other is just a general events table and they're both sharded tables and so when I when I look at this from my application it just looks like one table like my application doesn't have to know that anything funny is going on just connects over regular Postgres and it's one table but if you connect to any of these workers in the back you can see that what's really going on is there's a lot of you know tables here and each one of those tables on that server is a shard and you know another server is going to have another set of shards and so on and so when writes come in you say that we are one of these tables this is my shard key we go ahead and run a hash function on that we know what shard it's going to go to and then we write it to that one shard and just to show that how it works most Postgres stuff is done with these so-called catalog functions that are under the hood sort of like somewhat hidden from you and so we have a catalog function here that or a catalog table that shows the start and end range of each one of the shards that is created and so we run the hash function we know if it's you know from int min to a little bit over int min we know what shard that goes to and then you know the next set and so on and so that's how the the writes work but when you want to you know think about how to architect your application for taking advantage of you know distributed Postgres there's really two kind of ways that you can think about it uh... one is more for real-time sequel on like event data so let's say you're taking your tracking uh... clicks or page views or uh... you know metrics from your system so on and the other one is more of a multi-tenant model uh... which I'm gonna start with first uh... the multi-tenant model is more let's say you're building an application like Salesforce where each you know customer of Salesforce has our own set of data and it doesn't you know go from customer to customer uh... and you know this is a model for like a lot of uh... companies where uh... their customers data doesn't mix with each other uh... and what's nice about this is it's easy to migrate to it's easy to maintain because all of the Postgres features that we talked about before the uh... backups the wally for uh... you know continuous uh... right ahead log protection uh... scaling works you can both get bigger individual nodes and you can add more nodes to the system uh... and so on and the way that this works is all of your tables if you denormalize it a little bit and add the customer or tenant ID to each one of the tables uh... when your queries come in as long as it's like where customer ID or uh... where org ID equals three we know that all of that data is just gonna be on one particular node so we can send all the queries there and that just behaves exactly like uh... a single node Postgres at that point it doesn't have to be concerned with all the other data of all the other customers and you get the results back uh... and this is more if you were going to shard yourself if you're gonna say okay I need to you know build an application and uh... I need to do the sharding this is probably much like what you would do in your own application code uh... but this way you know it's all taken care of and you don't have to maintain all that code in your application uh... but it's more analogous to what you think what what people think about uh... sharding the other model is the more parallel model and what's great about here is that uh... you can uh... have your data not just you can insert it in one place but the queries when they come in they get distributed across all of the nodes all of the all of the machines in your cluster uh... what's nice about this is one that it's Postgres and so if you started with Postgres you don't have to when you get to these large scales you don't have to rearchitect your system so much to uh... you know use some other model uh... you can also if you have a Postgres team in your company people experience with it you don't have to like they don't have to go learn how to operate and maintain a completely different database and then also because it is Postgres whenever Postgres comes out with a new feature such as like JSONB a couple years ago all that comes for you because it's just you know extension on top of Postgres yes so when the query comes in it like I said we do a distributed querying planning phase where all of the nodes get hit and then it comes back uh... the data and what's nice about this also it's not very common that someone's query is CPU bound usually what I see customers uh... Postgres users it's more like memory bound but occasionally you'll have a query that is CPU bound uh... Postgres when you connect of course it forks a back end and it's largely single threaded uh... there's been some advances uh... nine six and the next version where sequential scans are done in parallel uh... you know largely you're gonna be single threaded but because this is working on all of the nodes in your cluster you get to use all of the CPU all at once and so you know I've seen some cases even like five seven hundred times improvement because uh... it could use all the CPUs at once uh... you also have a you know one example of Citus MX which lets you connect to any single node instead of just one coordinator node and uh... uh... using the yahoo cloud serving benchmark uh... we're able to get uh... about half a million writes per second on a 32 node cluster uh... when we're doing bulk insert it was like seven million but that's kind of cheating because uh... bulk insert is not usually people's workload pattern uh... and what this this is this is a pretty cool way how we worked around that you can connect any node uh... it's a you know DNS entry that does round robin so when you connect it just goes to any one of the backing nodes and then they communicate with each other uh... and then finally we have a gem that you can use if you're using the multi-tenant model and uh... instead of saying belongs to you say that I'm multi-tenant on user and then inside your code blocks you can wrap it in this uh... with uh... block and then it makes sure that active record scopes all of your queries with that proper org ID and uh... what's nice about this is you can use this even before you start using Citus you can have your as long as your columns have tables have those columns you can have this in place and then when you need to scale out everything is all uh... ready to go uh... that's all I have for you thank you very much uh... we do have uh... you know I can answer maybe a couple questions now looking at the time but also we do have a booth uh... and I'm happy to help uh... talk there thank you very much I'm sorry so the question is using instead of an energy using what oh you ID yeah so I yes the question is using you IDs as uh... things instead of integers uh... so I like it a lot uh... some some of my uh... more uh... postgres dba colleagues uh... worn against it because it does take up more space and joins are uh... a little slower but I I just really like using you IDs everywhere just cuz like I know you know one up if I do short in your house I'm not exposing secrets about my system and then also uh... I think it's it's fine but it it could be a performance but like it's something to be aware of but I I tend to go with you IDs as ideas first and then only in if I if it if I can think about it and it's really going to be a problem then maybe go to integers and also if you are using integers for your columns please use big and uh... it's uh... it actually takes up the same amount of size in the database because uh... the things are memory line to uh... sixty four bits anyway and uh... only the only time it would save you space is if you have two thirty two bit types together because of the the bit packing which you probably don't have it so please please use big and otherwise you're gonna have a really nasty migration problem surprise you after uh... what thirty two million so please use big and uh... yes one terabyte is uh... you know starting to get big uh... so the question is uh... how easy is it to migrate once you're at one terabyte uh... if you can take the downtime like a dump restore uh... can be pretty quick because uh... the way that dumps restore work is they admit a lot of the header uh... framing data so it's just the tuples coming in uh... I would see if they like just take a dump and see how long that takes and like if that's an acceptable downtime uh... but the there usually are some changes that to the application layer but because it is still post-crest like that part isn't like usually it's just the downtime of migration that people are worried about usually the actual code changes tend to be okay there like some queries you might have to rewrite but usually it's not so bad especially if it's multi-tenant uh... that makes it a lot easier uh... so the question is if your medium size is a performance discharging then uh... there can be especially if you do have cpu bound queries uh... the other thing is it's always easier to start sharding before everything's on fire uh... because when it once you start getting big then you know then it's more like a scramble it's like it's hard again because of the flirting with disaster thing it's hard to get people to do it before it's painful but I would project your data growth and see like work back in time when you start having to having to do it uh... yeah so what have I used in past you testing on the database and mostly that was for backups and restores mostly just I haven't found like a good tool you can use it's mostly like just writing a couple scripts that you know run it uh... you know once a day and you know try and you know make sure that it did restore I don't I don't know if there's any good not that I know of any good off the shelf tools but like you know like as long as you get the authentication of where to download your backups from like you're looking at you know like maybe a twenty line ruby script that like maybe sends an email after it didn't work so yeah it's mostly been hacky homespun stuff alright that looks like it thank you very much uh... please check us out uh... the booth if you have any more questions