​Postgres at Any Scale​
CC Attribution - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Leinweber, Will
Postgres has powerful datatypes and a general emphasis on correctness which makes it a great choice for apps small and medium. Growing to very large sizes has been challenging—until now! First you'll learn how to take advantage of all PG has to offer for small scales, especially when it comes to keeping data consistent. Next you'll learn techniques for keeping apps running well at medium scales. And finally you'll learn how to take advantage of the open-source Citus extension that makes PG a distributed db, when your app joins the big leagues.
 All right, 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 Will Lineweber, 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. And 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, 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 going to 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 a 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. 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. A database that has just a little bit of data in it, but you're constantly writing and reading, is going to 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 going to have and the sort of the data processing that you're going to use. And so before you even get started, you want to plan, you know, if you can, where your database is going to end up, where your application is going to 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, very, the very first thing is please take backups. Now, every database talk that you go to, someone's going to 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 want to 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, you know, 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 going to 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, you know, I don't want to, you know, it's, you know, again, very important that you test your backups. You know, you have heard this before, you hear it again. I said it before, I'm going to say it again because it is so important, you know, please test them. You don't want to be in a situation where things are on fire and you, that's when you find out that your backups don't work. And so please, I just want to 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 login count, you might think that there will 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, you're not just, 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 it's, I don't think people think about this as an advantage of Postgres as much as it actually is. You know, there's a ton of that you can do with Postgres, you know, 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 going to 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. You know, aside from, I mean, 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, you know, 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 going to go into are, you know, for some reason or another, like, seem like they're much less well-known. And despite Rails and, you know, popularizing the things of, you know, embracing constraints, that kind of stopped at the database layer. And at least for the initial, you know, several years of Rails history, the database was treated just as like a replaceable hash. And you could swap from one database to another and, you know, and that's all great. But I don't subscribe to that myself. I think that if you can, you know, 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 ActiveRecord and my favorite Sequel has let you use more and more of database-specific features instead of just the least common denominator features 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, you know, 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, but, you know, and, but 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, and so if you get something into your application code that starts writing some inconsistent data, you know, maybe not like, you know, database inconsistent, but inconsistent with the logic of your app, you know, it could go on writing this bad data for weeks before you notice. And then you have this horrible, horrible cleanup process to do. And because you can't figure out what is true and what's not. And, you know, cleaning up a mess like that, you know, I've seen, I've seen it happen. I've seen, I've worked with people who are working through that. And it's just, 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 going to 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 your application, 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 in to it being not null. I 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, you know, something like that. And if that's not a primary key, if that's not a not null, if you go look right now, there's going to 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 of my users are going to have a unique email address, go ahead and make that a unique index. Now, 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 going to notice it and have the things be enforced to be unique. And then later on, you know, after your application has been going, like if this is actually the bottleneck, you can go ahead and drop it later. But go ahead and go through, you know, 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, you know, for example, you have a, like, a reservation kind of thing where I want, I don't want anyone to have a, you know, have the two reservations, like, with the same person with the same other room, you can have, 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, 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 something 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 into 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 model, which I use in almost all of my tables of having, you know, a couple of my real columns and then a JSONB column serves 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, you know, the beginning is always, you know, 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, you know, 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. You know, 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 you can, 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, you know, we have checks and code that makes sure that doesn't happen. But having this be fixed in the database was, you know, just so much of a weight off my mind knowing that the database is going to 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 UUID 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 going to 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, WAST, one instead of WAST, 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 and 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 want to store Fibonacci numbers. You can do a check like that. Like really, you know, any sort of custom validation that you want to 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, you know, maybe when you start getting above 100 gigs, 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's 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 PGDump 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. It's all open source. If you're running a database by yourself, please, please, please use Wally. What it does is it takes the write ahead log files that Postgres generates and sends it off the machine to S3. There's some other plug-in back ends if you're using Azure or GCE or something like that. There's other back ends 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 write 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. 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, create a follower or 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 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, you know, not as important data is extremely powerful. Again, you have to invest in monitoring and alerting 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 with a lot of these things when I was putting this talk together. Like, a lot of these things are like, I can do, 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 Resolvency and Complex Edit systems. You can look for that on GitHub. And I'm going to redo part of it here because I 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, this one of the boundaries of the 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 there pushing you away from economic boundary because otherwise your company is going to go out of business. And so you go up to the line and then you go 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, you know, 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, 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, you know, we're only going to operate systems at like this capacity or this, you know, this is the performance. These are the, how many requests per second we want to be hitting. And no, no, no worse than that. The problem is you tend to go over it and then, you know, you kind of freak out and you go, oh no, and you bring it back. And you keep going, 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, you know, push the, you know, 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 know, you have a big outage and it's a problem. And that, I never got to use the fire thing before so I want to just, I want to do it one more time. 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. And you push, you know, I don't care how much money it costs, I don't care, you know, 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 know, 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 like 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 going to 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 databases and you have your application talk to all of them. You know, people have been doing sharding for a long time. There's a lot of homespun approaches. You want to, 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, you know, 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 nodes, it'll distribute that out against all the machines in your cluster and so on. This is sort of a complicated view of it. But it's actually, the approach that we take is actually, you know, pretty simple and straightforward. And so we have these ideas 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 stored and used. So for example, I have these two tables, one is CloudWatch 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. It 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, you know, 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, you know, 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 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. One is more for real-time SQL on, like, event data. So let's say you're taking, you're tracking clicks or page views or, you know, metrics from your system, so on. And the other one is more of a multi-tenant model, which I'm going to start with first. The multi-tenant model is more, let's say, you're building an application like Salesforce, where each, you know, customer of Salesforce has their own set of data, and it doesn't, you know, go from customer to customer. And, you know, this is a model for, like, a lot of companies where their customer's data doesn't mix with each other. 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 backups, the wall-y for, you know, continuous, right, ahead log protection, scaling works so you can both get bigger individual nodes and you can add more nodes to the system, 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, when your queries come in, as long as it's, like, where customer ID or where org ID equals three, we know that all of that data is just going to be on one particular node, so we can send all the queries there. And that just behaves exactly like a single node postgres at that point. And it doesn't have to be concerned with all the other data of all the other customers. And you get the results back. And this is more, if you were going to shard yourself, if you're going to say, oh, I need to, you know, build an application and I need to do the sharding, this is probably much like what you would do in your own application code. But this way, you know, it's all taken care of and you don't have to maintain all that code in your application. But it's more analogous to what you think, what people think about sharding. The other model is the more parallel model. And what's great about here is that you can 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 machines in your cluster. 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 larger scales, you don't have to re-architect your system so much to, you know, use some other model. 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, JSON B 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 query planning phase where all of the nodes get hit and then it comes back, 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, postgres users, it's more like memory bound. But occasionally you'll have a query that is CPU bound. Postgres, when you connect, of course, it forks a back end and it's largely single threaded. There's been some advances on 9.6 and the next version where sequential scans are done in parallel, but, you know, largely you're going to be able to do that. 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 of, like, even like, 500, 700 times improvement because it could use all the CPUs at once. We 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 using the Yahoo Cloud serving benchmark, we were able to get, you know, about half a million writes per second on a 32 node cluster. When we're doing bulk insert, it was like 7 million, but that's kind of cheating because bulk insert is not usually people's workload pattern. And what this, this is a pretty cool way, how we worked around that you can connect to any node. It's a, you know, a 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. And then finally, we have a gem that you can use if you're using the multi-tenant model and instead of saying belongs to you, you say that I'm multi-tenant on user and then inside your code blocks, you can wrap it in this with block and then it makes sure that ActiveRecord scopes all of your queries with that proper org ID. And 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 ready to go. That's all I have for you. Thank you very much. We do have, you know, I can answer maybe a couple of questions now looking at the time but also we do have a booth and I'm happy to help talk there. But thank you very much. Sorry. So the question is using, instead of an integer using what? Oh, UUID. Yeah. So I, yeah. So the question is using UUIDs as things instead of integers. So I like it a lot. Some of my more Postgres DBA colleagues warn against it because it does take up more space and joins are a little slower but I just really like using UIDs everywhere just because like I know that, you know, one, if I do short in URLs, I'm not exposing secrets about my system and then also like, I don't know, I think it's fine. But it could be a performance bottleneck. It's something to be aware of. But I tend to go with UUIDs as IDs first and then only in, 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 int. It actually takes up the same amount of size in the database because the things are memory aligned to 64 bits anyway. And only, the only time it would save you space is if you have two 32-bit types together because of the bit packing, which you probably don't have. So please, please use big int. Otherwise, you're going to have a really nasty migration problem surprise you after what, 32 million. So please use big ints. Yeah, so one terabyte is, you know, starting to get big. So the question is how easy is it to migrate once you're at one terabyte? If you can take the downtime, like a dump restore can be pretty quick because the way that dumps restore work is they emit a lot of the header framing data. So it's just the tuples coming in. I would see if, like, just take a dump and see how long that takes and, like, if that's an acceptable downtime. But the, usually there are some changes to the application layer, but because it is still post-crust, like, that part isn't, like, usually it's just the downtime of migration that people are worried about. 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. That makes it a lot easier. So the question is if your medium size is a performance to sharding then. There can be, especially if you do have CPU bound queries. The other thing is it's always easier to start sharding before everything's on fire because once you start getting big, then, you know, then it's more like a scramble. Like, so, like, it's hard, again, because of the flirting with disaster thing, it's hard to get people to do it before it's meaningful, but I would, you know, project your data growth and see, like, work back in time when you start having to do it. Yeah, so what have I used in past year testing on the database? And mostly that was for backups and restores. Mostly just, I haven't found, like, a good tool that you can use. It's mostly, like, just writing a couple scripts that, you know, run it, you know, once a day and, you know, try and, you know, make sure that it's sorted, restore. Yeah, 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 can get the authentication of where to download your backups from, like, you're looking at, you know, like, maybe a 20 line Ruby script that, like, maybe sends an email after it didn't work. So yeah, it's mostly been hacky home-spun stuff. All right, that looks like it. Thank you very much. Please come out at the booth if you have any more questions.
 All right, 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 Will Lineweber, 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.  And 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,  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 going to 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 a 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.  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.  A database that has just a little bit of data in it, but you're constantly  writing and reading, is going to 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 going to have and the  sort of the data processing that you're going to use.  And so before you even get started, you want to plan, you know, if you can,  where your database is going to end up, where your application is going to 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, very, the very first thing is please take backups.  Now, every database talk that you go to, someone's going to 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 want to 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, you know,  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 going to  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, you know, I don't want to, you know, it's, you know, again, very important  that you test your backups.  You know, you have heard this before, you hear it again.  I said it before, I'm going to say it again because it is so important, you know,  please test them. You don't want to be in a situation where things are on fire  and you, that's when you find out that your backups don't work.  And so please, I just want to 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 login  count, you might think that there will 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, you're not just, 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 it's, I don't think people think about this as an advantage of Postgres  as much as it actually is. You know, there's a ton of that you can do with Postgres,  you know, 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 going to 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.  You know, aside from, I mean, 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, you know, 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  going to go into are, you know, for some reason or another, like, seem like they're much less  well-known. And despite Rails and, you know, popularizing the things of, you know, embracing  constraints, that kind of stopped at the database layer.  And at least for the initial, you know, several years of Rails history, the database was  treated just as like a replaceable hash. And you could swap from one database to another  and, you know, and that's all great. But I don't subscribe to that myself.  I think that if you can, you know, 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 ActiveRecord and my favorite  Sequel has let you use more and more of database-specific features instead of just the least common  denominator features 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, you know, 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, but, you know, and, but 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, and so if you get something  into your application code that starts writing some inconsistent data, you know, maybe not  like, you know, database inconsistent, but inconsistent with the logic of your app, you  know, it could go on writing this bad data for weeks before you notice. And then you  have this horrible, horrible cleanup process to do. And because you can't figure out what  is true and what's not. And, you know, cleaning up a mess like that, you know, I've seen,  I've seen it happen. I've seen, I've worked with people who are working through that.  And it's just, 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 going to 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  your application, 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 in to it being not null. I  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, you know, something like that. And if that's not a primary key, if that's  not a not null, if you go look right now, there's going to 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 of my users  are going to have a unique email address, go ahead and make that a unique index. Now,  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 going to notice it and have the things be enforced  to be unique. And then later on, you know, after your application has been going, like  if this is actually the bottleneck, you can go ahead and drop it later. But go ahead  and go through, you know, 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, you know, for example, you have a, like, a reservation kind of thing where I  want, I don't want anyone to have a, you know, have the two reservations, like, with the same  person with the same other room, you can have, 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, 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  something 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 into 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  model, which I use in almost all of my tables of having, you know, a couple of my real columns  and then a JSONB column serves 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, you know, the beginning is always, you know, 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, you know, 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. You know, 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 you can, 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, you know, we have checks and code that makes sure  that doesn't happen. But having this be fixed in the database was, you know, just so much  of a weight off my mind knowing that the database is going to 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 UUID 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 going to 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, WAST, one instead of WAST, 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 and 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 want to store Fibonacci numbers. You can do a check like  that. Like really, you know, any sort of custom validation that you want to 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, you know,  maybe when you start getting above 100 gigs, 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's  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 PGDump 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. It's all open source. If you're running a database by  yourself, please, please, please use Wally. What it does is it takes the write ahead log  files that Postgres generates and sends it off the machine to S3. There's some other  plug-in back ends if you're using Azure or GCE or something like that. There's other  back ends 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 write 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. 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, create a follower or 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  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, you  know, not as important data is extremely powerful. Again, you have to invest in monitoring and  alerting 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 with a lot of these  things when I was putting this talk together. Like, a lot of these things are like, I can  do, 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 Resolvency and Complex  Edit systems. You can look for that on GitHub. And I'm going to redo part of it here because  I 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, this  one of the boundaries of the 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 there pushing you away from economic boundary because otherwise  your company is going to go out of business. And so you go up to the line and then you  go 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, you know, 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, 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, you  know, we're only going to operate systems at like this capacity or this, you know, this  is the performance. These are the, how many requests per second we want to be hitting.  And no, no, no worse than that. The problem is you tend to go over it and then, you know,  you kind of freak out and you go, oh no, and you bring it back. And you keep going, 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, you know, push the, you know,  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 know,  you have a big outage and it's a problem. And that, I never got to use the fire thing  before so I want to just, I want to do it one more time. 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. And you push, you know, I don't care how much money it costs, I don't  care, you know, 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 know, 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 like 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 going to 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 databases and  you have your application talk to all of them. You know, people have been doing sharding  for a long time. There's a lot of homespun approaches. You want to, 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, you know, 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  nodes, it'll distribute that out against all the machines in your cluster and so on. This  is sort of a complicated view of it. But it's actually, the approach that we take is actually,  you know, pretty simple and straightforward. And so we have these ideas 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  stored and used. So for example, I have these two tables, one is CloudWatch 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. It 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, you know, 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, you know, 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 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. One is more for real-time SQL on, like, event data. So let's say you're taking, you're  tracking clicks or page views or, you know, metrics from your system, so on. And the other  one is more of a multi-tenant model, which I'm going to start with first. The multi-tenant  model is more, let's say, you're building an application like Salesforce, where each,  you know, customer of Salesforce has their own set of data, and it doesn't, you know,  go from customer to customer. And, you know, this is a model for, like, a lot of companies  where their customer's data doesn't mix with each other. 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 backups, the wall-y for, you know, continuous, right, ahead log  protection, scaling works so you can both get bigger individual nodes and you can add  more nodes to the system, 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, when your queries come in, as long as it's, like, where customer ID or where org  ID equals three, we know that all of that data is just going to be on one particular  node, so we can send all the queries there. And that just behaves exactly like a single  node postgres at that point. And it doesn't have to be concerned with all the other data  of all the other customers. And you get the results back. And this is more, if you were  going to shard yourself, if you're going to say, oh, I need to, you know, build an application  and I need to do the sharding, this is probably much like what you would do in your own application  code. But this way, you know, it's all taken care of and you don't have to maintain all  that code in your application. But it's more analogous to what you think, what people think  about sharding. The other model is the more parallel model. And what's great about here  is that you can 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 machines in your  cluster. 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 larger scales, you don't have to re-architect  your system so much to, you know, use some other model. 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,  JSON B 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  query planning phase where all of the nodes get hit and then it comes back, 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, postgres users, it's more like memory bound. But occasionally  you'll have a query that is CPU bound. Postgres, when you connect, of course, it forks a back  end and it's largely single threaded. There's been some advances on 9.6 and the next version  where sequential scans are done in parallel, but, you know, largely you're going to be  able to do that. 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 of, like, even  like, 500, 700 times improvement because it could use all the CPUs at once. We 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 using the Yahoo Cloud serving benchmark, we were able  to get, you know, about half a million writes per second on a 32 node cluster. When we're  doing bulk insert, it was like 7 million, but that's kind of cheating because bulk insert  is not usually people's workload pattern. And what this, this is a pretty cool way,  how we worked around that you can connect to any node. It's a, you know, a 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. And then finally, we have a gem that you can  use if you're using the multi-tenant model and instead of saying belongs to you, you say  that I'm multi-tenant on user and then inside your code blocks, you can wrap it in this  with block and then it makes sure that ActiveRecord scopes all of your queries with that proper  org ID. And 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 ready to go. That's  all I have for you. Thank you very much. We do have, you know, I can answer maybe a couple  of questions now looking at the time but also we do have a booth and I'm happy to help talk  there. But thank you very much. Sorry. So the question is using, instead of an integer  using what? Oh, UUID. Yeah. So I, yeah. So the question is using UUIDs as things instead  of integers. So I like it a lot. Some of my more Postgres DBA colleagues warn against it  because it does take up more space and joins are a little slower but I just really like  using UIDs everywhere just because like I know that, you know, one, if I do short in URLs,  I'm not exposing secrets about my system and then also like, I don't know, I think it's  fine. But it could be a performance bottleneck. It's something to be aware of. But I tend  to go with UUIDs as IDs first and then only in, 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 int. It actually takes up the same amount of size in the database  because the things are memory aligned to 64 bits anyway. And only, the only time it would  save you space is if you have two 32-bit types together because of the bit packing, which  you probably don't have. So please, please use big int. Otherwise, you're going to have  a really nasty migration problem surprise you after what, 32 million. So please use big  ints. Yeah, so one terabyte is, you know, starting to get big. So the question is how  easy is it to migrate once you're at one terabyte? If you can take the downtime, like a dump  restore can be pretty quick because the way that dumps restore work is they emit a lot  of the header framing data. So it's just the tuples coming in. I would see if, like, just  take a dump and see how long that takes and, like, if that's an acceptable downtime. But  the, usually there are some changes to the application layer, but because it is still  post-crust, like, that part isn't, like, usually it's just the downtime of migration that people  are worried about. 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.  That makes it a lot easier. So the question is if your medium size is a performance to  sharding then. There can be, especially if you do have CPU bound queries. The other thing  is it's always easier to start sharding before everything's on fire because once you start  getting big, then, you know, then it's more like a scramble. Like, so, like, it's hard,  again, because of the flirting with disaster thing, it's hard to get people to do it before  it's meaningful, but I would, you know, project your data growth and see, like, work back in  time when you start having to do it. Yeah, so what have I used in past year testing on  the database? And mostly that was for backups and restores. Mostly just, I haven't found,  like, a good tool that you can use. It's mostly, like, just writing a couple scripts that,  you know, run it, you know, once a day and, you know, try and, you know, make sure that  it's sorted, restore. Yeah, 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 can get the authentication  of where to download your backups from, like, you're looking at, you know, like, maybe a  20 line Ruby script that, like, maybe sends an email after it didn't work. So yeah, it's  mostly been hacky home-spun stuff. All right, that looks like it. Thank you very much. Please  come out at the booth if you have any more questions.
Postgres_at_Any_Scale____2_0_311.png Postgres_at_Any_Scale____2_312_539.png Postgres_at_Any_Scale____2_540_1751.png Postgres_at_Any_Scale____2_1752_2471.png Postgres_at_Any_Scale____2_2472_2519.png Postgres_at_Any_Scale____2_2520_4475.png Postgres_at_Any_Scale____2_4476_4607.png Postgres_at_Any_Scale____2_4608_17531.png Postgres_at_Any_Scale____2_17532_21923.png Postgres_at_Any_Scale____2_21924_23651.png Postgres_at_Any_Scale____2_23652_26387.png Postgres_at_Any_Scale____2_26388_27047.png Postgres_at_Any_Scale____2_27048_27095.png Postgres_at_Any_Scale____2_27096_29795.png Postgres_at_Any_Scale____2_29796_33347.png Postgres_at_Any_Scale____2_33348_33863.png Postgres_at_Any_Scale____2_33864_35963.png Postgres_at_Any_Scale____2_35964_37247.png Postgres_at_Any_Scale____2_37248_37667.png Postgres_at_Any_Scale____2_37668_37715.png Postgres_at_Any_Scale____2_37716_38279.png Postgres_at_Any_Scale____2_38280_38507.png Postgres_at_Any_Scale____2_38508_38951.png Postgres_at_Any_Scale____2_38952_39539.png Postgres_at_Any_Scale____2_39540_40007.png Postgres_at_Any_Scale____2_40008_40571.png Postgres_at_Any_Scale____2_40572_40955.png Postgres_at_Any_Scale____2_40956_41351.png Postgres_at_Any_Scale____2_41352_42059.png Postgres_at_Any_Scale____2_42060_42779.png Postgres_at_Any_Scale____2_42780_42827.png Postgres_at_Any_Scale____2_42828_43871.png Postgres_at_Any_Scale____2_43872_44231.png Postgres_at_Any_Scale____2_44232_45263.png Postgres_at_Any_Scale____2_45264_45779.png Postgres_at_Any_Scale____2_45780_46595.png Postgres_at_Any_Scale____2_46596_47891.png Postgres_at_Any_Scale____2_47892_48623.png Postgres_at_Any_Scale____2_48624_48995.png Postgres_at_Any_Scale____2_48996_49955.png Postgres_at_Any_Scale____2_49956_50363.png Postgres_at_Any_Scale____2_50364_50507.png Postgres_at_Any_Scale____2_50508_53951.png Postgres_at_Any_Scale____2_53952_54767.png Postgres_at_Any_Scale____2_54768_55967.png Postgres_at_Any_Scale____2_55968_56015.png Postgres_at_Any_Scale____2_56016_56123.png Postgres_at_Any_Scale____2_56124_56169.png