 but your data is in there, trust me. In fact, that's why I created it there, right? Like it says, look, here, you put the data here, right? You can't interact with this right now because there is no process to interact with it. There is no database running, but those are the files where your data will be. The confusing thing though, is that there are already files. I made nothing, right? I just said make a cluster, right? And look at this, there are like a thousand files in there. Actually there are a thousand files in there, you don't see them. So what the hell, right? So it turns out that when you do in a DB, Postgres is going to like, actually make a few databases in there for you, like they're called template databases, right? And in fact, all other databases you create are created in the images of those databases. So they're kinda like, that's why they're called templates, right? So there is already a bunch of like, boilerplate data in here. And we're not gonna go through all of that because you take the whole doc to just do that, right? But stuff's in here, it's not our stuff, because we didn't make it, right? We just said make me a place where I can make stuff, right? But it's going to be in here. So let's start the database. So to do that, I'm gonna use this command called pgcdr, which is pgcontrol. You can use whatever you want, there are many ways to start this. But basically I said, yo, listen, look, that's your data directory, same directory that I created, right? Then there's a log file or whatever, not hyper-relevant in this point. Start, right? And it's like, okay, I'm gonna start. And now if I do this, okay, some stuff is running, right? So now Postgres is running, right? And I'm sure people are familiar with PSQL, which is the Postgres command line client. If you're not, this is the wanna be 2000 millennials version of pgadmin, right? So like, I don't do GUIs, I do command lines. So that's my tool of choice, right? So that's how I interact with the database, right? I interact with the database, which is really that set of files through the process, which I get to through this thing, right? Okay, but I can't interact with it because when I set PSQL, like just get me into my database, it's trying to get me into a database with my name and it doesn't exist. Okay, let's create the database. Yeah, wanna create the database with my name. Just add a URL to the circle. So, yeah, there it is. Now it's my database, right? My name. That's a whole database, right? So when you walk around people like, oh yeah, man, my production database and like, whoa, that's all it is. I just made a whole database, right? Turns out there's no data in it, but it's a whole database, right? So first thing I wanted to dispel is this like fear of, oh my gosh, database. No, it's just a database, right? I could have run that script like a gazillion times and made a gazillion databases, but yeah, database is not that scary a concept, right? Database is a logical grouping of a set of files on a file system with a process that you interact with. That's it, not a big deal, right? And in fact, Postgres uses the Linux file system heavily to do its job, right? So it's built on the shoulders of giants, that's why it's so good, right? So don't be scared of databases, that's simple to create one. Okay, so now I've created a database, right? Now what I'm gonna do, is I'm gonna list all the databases in this cluster. Now this is super confusing, right? Because I'm like, I'm inside a database and I'm saying, what are all the databases? What do you mean, you're inside a database? There's only one database, the one you're inside. This is where Postgres starts to get a little confusing because there are actually, that thing, PG databases of you that's operating in the cluster level, I'm not going to go into depth of like, what's exactly happening, but these are all the databases and you can see one of them is mine. You see it has my name on it, that name, right? So there are other databases, there's a database called Postgres, there's database called template one, database called template zero. Those are all the boilerplate databases you get when you're just in a DB, right? So that's why it's a cluster, right? Not one databases, four actually, right? The one I'm dealing with is the one I'm inside, but I can actually see a little bit of information about other databases because they're all in the same cluster. Makes sense so far? Yeah, okay, very good. So made a database, now I'm going to make a table. So let's create a table, right? I'm going to make a simple table, made a judgment call, I don't want to talk about table spaces today, it's going to take a while. But look, I'm creating a table here, create table, name on my table is byte test. It has a column called test byte and it has exactly one column and as you can see the type of that column is car, one byte. You can only have one byte in a row, right? Okay, very good, have a table, right? Let's look at my table, it has one column type character and it has nothing in it, that's not surprising at all, right? So far, no magic, yeah? Okay, so where's the data for this? If I were to create data for this, where would it go? That's what we are here for, where's my byte? Okay, so Postgres very happily gives you this utility, right? It's kind of like a function that you just run within the client that says PG relation file path, right? Which is basically saying look, everything in Postgres is a relation, table is a relation, the name of my relation is byte test. What is the file path for that, PG relation file path for that? And it's like, look, it's in base, that thing, that thing. That's an actual file. So remember we put all our data in scale 2019, right? Inside it, there's a directory called base. Inside a directory, there's a data base, oh sorry, there's a folder called 16384 and there's a file called 16385. That's an actual file, okay? So now if I open it, it's got nothing in it. That's not surprising because I didn't put anything in it, right? But it is an actual file, which means if I were to put data in it, that's where I'd expect it to show up, right? Yeah, okay, sure. What the hell is, what is this thing? What's up with slash, slash, right? So the way it works is base is the base directory where all your tables go. Now they may go to a different place if you were doing something like tablespaces or file mounts or whatever, right? But right now, it's just a base directory collection of place for all your tables and all your databases. The second part that you see, 16384, that's the identifier of my database. Remember, there can be more than one database. In fact, there will be exactly four databases because when I listed all the databases, there were four of them. Postgres, template zero, template one and the one I made with my name, four databases. The tables for the database are going to be under their respective directories, right? So mine is 16384, so all my tables underneath that, okay? In fact, that's fairly easy to check here. OID, object identifier, 16384, 16384. And the object identifier of my table is 16385, which is why the file path is base 16384, 16385, yeah? Okay, enough talking. I am going to insert one byte of data into this. I inserted one byte of data. Totally it's one byte of data, it's one byte of data. There's one byte of data. It's a one, actually. So it's not the integer one because integers in Postgres take four bytes. It's the character one, which is exactly one byte. Any character is one byte, right? Asking. So this means if I go look in that file, that file, okay, there's a bunch of stuff in it, right? So let's print out that file. So the very naive implementation of this would actually have one written, right? So people familiar with what cat is, it's like the output utility, yeah? It outputted nothing. There is nothing in that file. You know what that is? That's a series of null characters. There is nothing in that file. If you're not concerned, at this point, you should be very concerned, right? Because I just wrote one to my database and my database came back and said, got it, I have your one. In fact, if I go back into my database, if I go through my thing and I select, there's a one, okay? But there's nothing in the file and that's where it should be. What the hell? Right, so this is where I'm gonna start getting really technical. So how many of you have heard of this thing called the wall? Okay, good. You all can probably leave, right? This talks not for you. So this is happening because of something called the write ahead log, right? So let's pretend that we are postgres for one second. How would I do this if I were postgres? Somebody came to me and said, look, here's a table. I have a file for that table. They give me a byte. Now obviously I need to have some sort of organization because they wanna do some queries and stuff on top of it. So I'm gonna come up with my library system of putting data for tables and rows or whatever, right? And then my library system begins with having a base and then a folder and not a folder, but I've gotten to my file now and I have to write stuff into my file and they're gonna be rows. So I guess I'll have some sort of library system for putting rows of data into the same file but I have to put it in there and postgres stint. So what did it do? So let's pretend we're postgres. How would I have done it? I would have opened the file, seeked to the first location that was empty, spit out whatever you told me to, close the file, exit it. How hard is that? It's pretty simple, right? In fact, you F open, F sync, sorry, F seek, F write, F sync and you out, right? Those are all the C commands you would do, right? So you basically open the file, go to the location you wanna put it in, you put it, you tell it, go save it, and then you're like, okay, I'm done, and you come back and you're like, I got this. How difficult is that? Why can't it do that? Let me tell you all the things that can go wrong while you're doing this. So in this case, it turns out it's not very difficult because it's one byte of data, okay? Pretend it was four bytes of data. I opened the file. I went to the location I was supposed to write it to. I started writing it. I sent the first packet down the network. I sent the second packet down the network. I died. Now I have a corrupt file, right? Part of my file's written. Part of my data's written to the file. What about the rest of it? Who's going to tell you that I wrote part of it? Who are you gonna know what part of it it was? There's no structure to it, right? Because you don't know where I started and where I would have stopped. I didn't tell you how much I was going to write. Okay, you can make this a little bit easier. You know what? Tell me how much you're gonna write to begin with. Tell me you wanna write 15 bytes and then I'll count 15 places from there. Good, now that's a start, right? Like now we're getting down to like, okay, it's not as simple as open it, go to the place, write it, come out, right? Yes, things can go wrong. What if two people were doing it? What if three people were doing it? It gets monstrously complicated to maintain writing bytes of data to a single file when you start looking at this from the perspective of enterprise or from like scale, right? No pun intended. So the way it turns out Postgres manages this is through the use of something called the write-ahead log. It's, think of it as like a ledger, right? So what Postgres does is that it makes a note of what you asked it to do and does what you asked it to do later, right? So it's kinda like, okay, what do you want? Okay, you want me to put a one into this row, what else? Okay, you want me to put some four into some other table. Okay, you want to update this and delete this thing, it's literally a ledger of all operations that are being performed against that database and as the name implies, write-ahead log, it's append only. You can't go back and change something. You can't change your mind about like, oh yeah, yeah, yeah, I remember that thing that I did and I told you it was great and you came back and told me you did it. Did you already do it? Yeah, I didn't want to do that. No, it's right ahead. You can't, the history is immutable. You just keep on writing to it. If you wanted to change something, right, you could roll it back, you could issue a command that would do the opposite of that, but you never go to get to go back and change it, right? So when you actually issue a command to Postgres, the, think of it as like translation of that command is written through this write-ahead log, right? So what Postgres does is like, okay, you want to insert a byte over here, right, got it. I'm gonna go and I'm gonna make a note of the fact that you want to insert a byte and then I'm gonna come back and tell you, got it. I will insert your byte, but where does it get my one from? Because I selected from the table, so does it go through the log and find out what it should have been? No. So the first copy of my byte went to that log. Postgres has a cache and in memory cache called the shared buffers, right? It's literally an area of memory that Postgres uses as a page cache over the data on your disk. Because if it were going to your disk every single time, it would be way too slow, so it has a cache in memory of all the data on your disk. Not all of it, it depends on the size of the cache, but relevant pieces of it. So really what Postgres is doing when you ask it to write that one, is it does two things. It goes and makes a note in this log and then it sets the byte in the cache that is backing that table. So that table has a cache and then that cache is written to disk. It's not on the disk as I showed you. I kept caching that file and nothing came out, but it's in the cache and so in that cache, there is a one sitting there when I select, it's coming out of that cache. Well, shit. How will it get to disk? Because the cache is in memory. I'm gonna shut down my computer. Let's see if this thing is in here. Oh my God, look at that. I've been talking for like five minutes, I think, and it magically appeared. What the hell? So it's really interesting. So I'm gonna show you a little experiment. I'm gonna wipe that table out. Insert a different one, different new byte. Let's find out where, what the file part for this is, if you're like really keen, if you're observing very keenly, you'd see that the OID has changed because when you truncate a table, you basically create a new file node. So now it's no longer 16385, it's 388, okay? Okay, I wrote it and no surprise, it's a bunch of nulls, okay? It's there, it's not there, because we're gonna find out if it lied to me, because the database, one of the first things you want a database to do is guarantee that it does its damn job. Have you heard of ACID, right? Atomic, consistent, isolated, durable, the D, the durable, how are you durable if you don't do your job of writing the damn thing through the file system? Oh, I killed Postgres. If it was in its memory cache, it should have gone now. You don't have a memory cache, you don't even, you are not even there, I just killed you. What we're gonna do is we're being Postgres backup. Brought Postgres up, go in here. What do you expect? Where from? There's data, what the hell, right? So this is why log good, let's cat the server log. If you look at this, Postgres is complaining that I was extremely rude, it's like, look, you interrupted me, and then you killed me. And then I found out that you hadn't, I hadn't finished the job I was going to do. So I'm going to redo my job, I found some inconsistent stuff, I fixed it all, and now I'm back up. That's Postgres going through the right ahead log, finding out what it should have done, what it would have done eventually if you hadn't killed it. Sinking that back, putting that data back into your file system, and then coming back up in a consistent state, durability. That's how your byte gets to the actual file system. You don't actually have to worry about it, Postgres will get it there. I just proved that Postgres, first of all, this is not an advocacy for killing or putting database in production. I just did it to prove a point, so don't do it, right? But even if you had killed it, are you waited long enough? Did your data eventually get to your file system? I'll show you another way data gets to your file system. Go ahead and wipe that table out again. Okay, put data in here again. As expected, it now has a new ID. I don't think there are any surprises at this point. What do you think this is? Empty, right? It's there. Another way to get data to your file system is this thing called the checkpoint. Press this button, come outside. Oh, look, my data's there. Right? So there's three ways to actually get data to your file system. One is you just wait. Two is you kill your database. Three is you issue a checkpoint. There are actually a couple more like really intricate ways of doing that. I don't wanna go into that right now. But essentially what's happening in all three cases, even though they look like bread butter and honey. Right, honey, honey. What's happening is that there's a reconciliation between your file system and your write-to-head log. The write-to-head log, the wall, is the ultimate source of truth. When you ask Postgres to do anything, it goes to that, the write-to-head log. It's a ledger, right? Which is why if you go around and talk to a bunch of people and be like, oh, my wall files, I need my wall file shipped, my wall file's taken, my wall files need to be backed up, my wall's backing up, my wall size, my checkpoint, blah, blah, blah. It's all about the wall, right? Because that is the ultimate source of truth. Your file system, it turns out, is just an optimization over that truth. Because even if you delete it, now don't do this. If you do this, I'm not responsible for this. But even if you deleted all of your files, your data files, theoretically, if you paid somebody a lot of money, they could reconstruct all of that through your wall. Because that is the record. It's kind of like a play-by-play. It's like, oh, first this happens, this happens, this happens, this happens, this happens, this happens. You just play it all out and you'd recreate your entire system at points in time. In fact, if you heard a point-in-time recovery, that's how it happens. It's extremely expensive and extremely slow, but it's possible. It's correct. That's the first motto of Fours Cross. It's correct, right? The file system is present as merely an optimization. Now, of course, I love academia. I used to research for a while. So to me, that's all I wanted. But if you go to the industry and you go to somebody and be like, oh yeah, I'm sorry, your website's down, but it's there, you give me 14 days, I'll bring it back up, don't you worry. People are gonna be like, I don't give a shit. I don't have 14 days. I'll be out of business in 14 days. So in the real world, the file system is extremely important, but from a pure corrective perspective, it's just an optimization, right? Because the wall is the truth, right? So when you write a byte of information, first it goes to the wall and it's in the cache. So when you're querying it, you're reading it off the cache. Then one of three things happens. Your database dice, a bunch of time passes, or you hit something called a checkpoint. In all three of those cases, what happens is that there is a synchronization of your file system with your wall. Basically, there's a checkpoint in your wall that says, look at this point, my file system looked exactly as the net sum of all of my wall operations. Checkpoint. Then your data gets to disk, right? Okay, this is why it's fine if your memory turns over. If pages are kicked out of your memory, when they get kicked out of memory, they will get written to disk, right? So don't worry about eviction and shared buffer sizes, et cetera, but that's how your file data gets to disk. If you guys were observing keenly, you would have noticed that I said shared buffers. What's shared? So it turns out, look, I have two Postgres shells open, right? Like two connections open to the database. How many of you knew this? Every time you open a connection to a database, you start a process, not on the client, on the server. You actually fork a parent process on the server, which is why it's extremely expensive to open the connections to Postgres, because your forking whole process, that's not even the hard part, right? Forking process, they all have to be in sync, right? It's shared buffers because the memory is shared between all of those connections. Every time you open it, you have 500 open connections to your Postgres database. You have 500 processes apart from all the Postgres stuff which is like there are like six, seven processes. Running on that machine, every process starts with a minimum amount of memory, 50 megabytes or so, yeah? And they all have to be kept synchronized. They all communicate between each other. There's a bunch of semaphores that are running around. 500 of them. Do you know how many ways 500 connections can go around talking to other 499 connections? PNC, a lot of ways, right? So that's why you're the pooler, right? Don't open 500 connections. But now you know, every time you open a connection, that's what's called shared buffers. So I'm gonna do this again. Next time you do PSYC or Graph Postgres, which is basically like the Bible of checking your Postgres, everyone does this. Even though, like how many of you can tell me what the key is? I'm sure there are a few people, but whatever. I'm just saying that like it's so like, wrote memory that people don't even realize what all the UNIX flags are. Anyway, next time you do this, right? I hope this thing is a little more familiar to you. Look, there's a checkpoint or process. What do you think it does? Checkpoints? Yeah, right? There is a wall writer process. What do you think it does? Write to the wall, right? There is a writer process, which I'm not going to go into a lot of detail right now, but let's move on. So now you know that the damn byte got to do the disk. Okay, very good. Let's update it. In fact, you know what? Why update it once? Update it a lot of times. So I have this script, it's basically, I think like 10 million update commands and I'm not gonna run all of them. But it just does that, set the byte and it cycles through all the 250 valid, 255 characters. There's like a null in there which is really annoying, which I don't do, there are 254 characters, because how are we gonna see null, it's annoying. So I skip null and then all the other remaining ones, and I'm just gonna run the script. So here we go. And that run, I go for some time. Like at this point, there are like at least 20, 30, 40,000 updates, right? Okay, good. So I'm gonna go into my database, right? Hopefully this is not surprising. How many bytes of data do we have in here? One, right? My update command said update, which means it was just updating in place, right? It was just overwriting it. How many bytes of data should I have in here? One, just one byte of data. Turns out we stopped at 10, right? Just cycling. Okay, I'm gonna flush. Remember, it may not be sent to disk yet, because it's probably in the wall somewhere. When I wrote all those update commands, you know what happened? It went to the wall, right? It's just cycling through, it's making note of all those commands in the wall, right? I'm gonna be like, look, flush it. Get it all out in disk. Be done with it. Remember what my data looked like before? Like, I don't expect it, it was like a bunch of gibberish, but it kinda showed up here, just a little bit of it. You know, it was just like this big. It should be the same thing this time, but slightly different, right? Because now it's M instead of one. Let's see what it looks like. What the hell? What? I only had one thing in there. That's way more than one thing. Okay, so, here's the funny thing, right? You're not the only one. There are a lot of people using database. That's the whole point of database, in fact, because if not, you could just get out a pen and paper and make a note of everything, right? You want to share things, right? Sharing things turns out extremely annoying, extremely expensive. You need a protocol. You have to agree on how you're gonna share things, right? And so, that walks us into this extremely complicated space of Postgres called NVCC, which is Multiversion Concurrency Control. In many fancy words, what that is, is a protocol for sharing things. Multiversion Concurrency Control. There are multiple versions because there is concurrency. People are doing things at the same time, so I have to make multiple versions of it. Why the hell would I have to make multiple versions of it? That's a good question. Okay. Let's go in here. I'm gonna open two Postgres shells. I have two Postgres shells, okay? Open two transactions. Yeah? We'll copy some commands from here. There we go. Run this, run this. Okay. Run the exact same two commands on two shells. Okay. Select a bunch of stuff and then the value of the byte. You can see that the actual value of the byte is M in both cases. Not surprising, right? Okay. Then I selected something called Xmin, Xmax, and the XID current. I open a transaction and what Xmin and Xmax are, are basically markers on that row, right? So it's saying, listen, this row, the only people who are allowed to see it are the people who have a transaction ID greater than 38,449. Oh, hold on. Step back. What the hell's a transaction ID? It's an ID that your transaction gets. That's it. That's what I selected. TXID current. What is the current ID of my transaction? Every time you open a transaction, you get an ID, right? Xmin says, you have to be, your transaction ID has to be at least 38,449 for you to see this. Xmax is zero, which means anyone can see it after that. As long as your transaction ID is 38,449, you get to see this. Next one says, same thing. But look at that transaction ID. I started this transaction first, so it's 38,451, 452. Both of them are green and 449. They get to see it. Do you see how this is gonna get complicated? Very quickly. Watch. Why not? B. Okay. What do you think this is? When I run this, it's gonna be M or B? M, M, M. Yeah, of course. That's the whole point of a transaction, right? Look, I will put on blinders. I'm in my own world. It's gonna be M because it's my transaction. I'm pretending like I'm the only one in the world, even though other people are doing things with it. Right? What do you think this is? B, right? Come on. They'll be ridiculous if I set it to B and then you came back and said it was still M. Why? Because some other guy believes it's M. Huh? I just told you it's B. Okay, fine, it's B. Right? So now you see we have a problem. One person believes it's B and the other person believes it's M. Which one's true? You know what Postgres says? Both are true. Why not? You all get your own reality. Of course, there has to be one synchronized reality at the end. And that's what your whole like the commit is, right? Let's commit. It's like merge my realities. Okay? What do you think this is? Is it M? Is it B? M. No, it's not. It's B. It's really funny because you can make it be M if you want it. You can choose which reality you want to live in. My current choice of reality was I would like to always live in the most current reality. Now we're walking into something called transaction isolation, right? It's basically blinders. The way I look at them is like horse blinders, you get small blinders, big blinders, or full blinders. So these are the small blinders, which is I'm gonna let you all pretend that there are multiple realities until you choose that they don't get to be more realities. You get the choice. You, the author of the transaction, the person who started the transaction, gets a voice in when you want your realities to start coming together. And the way I started this was with something called the transaction isolation setting of read committed. That should be pretty obvious, right? Read committed. Read stuff that's committed. It committed, so you read it. It was not committed, so you didn't read it. Transaction isolation of read committed. Let's try this again. Begin. Oh yeah, sure. Begin. Oh man, I hate this. Okay. Show transaction isolation. Oh no, it's without a thing, isn't it? I hate this. Show, show transaction. Okay. If I downloaded the transaction, it would have been the same thing, okay? Yeah. Read committed. So according to the SQL standard, there are four things you could be doing. There are four levels, there are four settings. Read uncommitted. Read committed. Repeatable read and serializable. So if I set it to read uncommitted, I would have seen that be. Even before it had committed, right? Yeah, that's true. According to the SQL standard, you would have seen it. You know what Postgres says? We're better than the SQL standard. What Postgres says is, I'll let you have a level called read uncommitted, but really it's read committed. Literally. So if you did set transaction isolation to read uncommitted, Postgres will be like, sure, yeah, you're read uncommitted. No, you're not. You're read committed. So Postgres is going to keep you from shooting yourself in the foot as much as possible. And the authors, and I agree with the authors, believe that the least shooting in the foot possible starts with read and read committed. It's like, you don't wanna be reading something other guy's doing, right? I don't know what the other guy could be screwing around in this transaction, right? He's just like, oh, I'm in my own world. Yeah, set it to F, set it to G. I don't know, set it to whatever I want it to be. And you're like, FG, what is it? Right? So Postgres is like, no, you do not wanna be reading other people's halfway work. You want to be reading when they're made up their minds, right? Read committed. That's where Postgres starts. Okay, what's the next one? What is repeatable read? Let's take a look. Oh, where are we at with this thing? Okay, we're at B. 10 minutes, oh no. Rough. I don't think we're gonna finish, by the way. There's always the danger with this talk, but whatever. Okay, so we're gonna try this thing with repeatable read, okay? So the first session, I'm gonna do this. In the second session, I'm gonna do this and then I'm gonna talk about what that is. Okay, so look, oh man, I set it to B. It was already B, let's set it to C. Okay. Okay, so I did the same thing, exact same thing, right? And you can see that in one case, in here I updated it to C, the other one, it's a B. Okay, but if you notice, at the top, when I began the transaction, I said, look, start with repeatable read. Yeah, in both cases. In fact, it doesn't matter for the other guy, it only matters for this guy. So the guy who's updating doesn't matter what read level he's at, because he's not really reading after the commit. So now I'm gonna commit, okay, I'm gonna press enter. Yeah, what do you think this is? So last time, I'll remind you, it was an M and I pressed enter and you all thought it would be an M, but it was a B. This time it's a B and I set it to C and I pressed commit. So if it was like last time, this time there should be a C, right? But it should be repeatable read, because I'm able to repeat the read I did last time. It was a B, you changed it, you committed it, it's still a B. Repeatable read. Pretty straightforward, okay. Serializable, this one's fairly complicated, but before we get to that, I wanna show you something really interesting. How many of you know about locks in Postgres? Locks? Like pessimistic locks, like roll locks, okay, sure. That's right, why not? Begin, repeatable read. This guy is still repeatably reading. I said this to D, I think it was a C last time, right? Yeah, D, D. This guy wants to get it on the fun. He's gonna be like, update, buy test, set test buy, come on, E. Yeah, E is a character, why not? What? If this was normal Postgres behavior that you've come to expect, you know what they would have done? It would have blocked, right? It'd be like, I can't do this, because somebody else is doing this right now. Let's try this again. I'll show you. Begin a transaction, go back to transaction. Let's start again. Start again, okay, set it to D, set it to E. Okay, that's more acceptable, right? This guy is like, I can't do it, can't do it. Some other guy is screwing around the bike, so you gotta wait, right? So this guy is holding the lock, this guy's waiting for the lock. See, it's not come back. You wanna commit this? No, no, no, you can't do this. Normally it would have just let you overwrite it, but this time it's like, no, you can't do that. Repeatable read prevents you from shooting yourself in the foot without realizing it. Right, it's like, look, I don't think you want to be doing this because somebody else just screwed with the thing you wanna screw it. Are you sure? Right, so that's the error, right? It's like, I can't, somebody else changed the thing you wanted to change. What you'll have to do in this case is retry, right? So it's up to you, the caller, the application, to be like, sorry, I know what I'm doing, set it. Normally, all of your applications, I'm assuming, not I don't wanna make generalization, most of your applications, in production, are running in read committed, which means if you have these races, you're just gonna overwrite it. But if you run it in this, you're gonna be safer, but you're gonna have to get smarter, right? Because Postgres is saying, I'm gonna throw you errors when I'm guessing that you don't wanna be doing what you're doing. In the case of races, what, when you're running read committed, what you're saying is, I'll take the race, fine, yeah, whatever. Yeah, he started at 10, he started at 20, last, last commit wins, right? Whoever won, won't. How would you feel if I said that about your bank account? They put in $1,000, they took $2,000, last commit wins, you know, whichever, I don't know, right? So your balance was $1,000 and then was $2,000? If $1,000, I guess you have $1,000. If $2,000, congratulations, you have $2,000, right? Of course you're gonna be really upset, right? That's the really scary part about starting to understand MECC, because now you're gonna be wondering, and you're welcome. When you go back home, oh, shit, right? I only have like 100,000 lines of code. I only call the database, like, I don't know, like 400 times in every file. What's racing with what? I don't know. You might wanna find out, right? So that's the really scary thing about databases, right? Like, they're so low down, there's such a base abstraction that applications are built on top of, about five minutes, shit. That when you really start designing them correctly or really start thinking about correctness as a first class concern, it gets really complicated really quickly. So serializable, which is the last application, the transaction level which I'm not gonna get into right now, is exactly the same thing as repeatable read, but it's a little bit smarter because it does something called predicate locking, which is, it's going to predict that you would have raced. It wouldn't even look like you were racing, but it's gonna be like, you know what? I think if you had done this in the other order, you wouldn't have gotten what you wanted, right? So that's what serializable does. I'm not gonna go into that right now, but that is transaction isolation. That is why you have multiple copies of things. That's why when I ran all those commands, my file got bigger because Postgres had to keep track of all of them because everyone and their mother wants a different reality. Postgres like, well, how do I do that? I just guess I keep track of everything. That's why it's all got bigger. How do you get rid of that bloat? So that dead space in between is called bloat, right? And I heard somebody today come up to the booth and complain about bloat, right? They were like, oh man, I had like all these rows and like just like getting really slow. So let's look at that really quick and then I'm gonna wind down the top. So I have all this data, right? But I just have one byte in there. So you can do this thing where you can vacuum this table. And say, look, clean up all the bloat. Just wipe it out, just get rid of it. Like there are things in there that people can't even see anymore and you're just keeping track of it for no reason. Clean it up. When you do that, first of all, when you do that, Postgres basically rewrites your file. So don't do this in production without thinking about it because you will lock up the file, to lock up the entire table, right? But when you do do that, hey, we're back to the little bit. Bloat cleaned up by vacuum, right? There is a place I couldn't get to which is called delete. So I created a byte to change the byte. I couldn't delete the byte because I ran out of time. Two out of three, I'll take it. I think I have a minute or 30 seconds for questions. I'll take one question and then after that I'll take questions outside if you have any. Yes, go ahead. Good question. So the question is, the data is written to the file system. What happens if you go in there, open the file and screw around with it? You will host your database. So don't do it, right? So basically the file is, there's a very special format for the file and in fact it looks a little bit like that. It has a very special, it's a data structure, right? It set up a very special way because Postgres wants it to be that way. If you screw around with it, yeah, Postgres is gonna be like, can't open that file, what the hell is that? That's not my creation, what will you do? Good news is, you could recover from it. So from the wall, you could run down and get back to where you wanted to be. Your company is gonna be real upset with you while you do it, because it's gonna take time, but you could do it. All right, I don't have time for any more questions. Gonna keep me honest. If you have any more, it's a fairly basic talk, but if you have any more questions, I'll be outside. Hope you enjoyed it. Thank you. Testing. Hey, in the back, can you hear me okay? Anybody think I'm too loud? Okay, great. All right, I think we're ready to get started. So this talk is a talk intended for beginner intermediate audience about the specific topic of locking. Make sure you're in the right place. I'm curious, how many people in here are Postgres DBAs? And how many people are devs? Okay, great. Yes, this talk will hopefully help you out with some pain points. Awesome, so good mix of people. Talk has created a comments license. There's a link in the program if you find it easier to read on your phone or on your laptop. Just look up the scale schedule and there's a link to download these slides. So as I said, the beginner intermediate talk is pretty much for both classes of people in the room. And we're gonna go through a little theory of what locks are and different types of locks in Postgres and how they interact with each other, and then really concentrate on a practice. So a lot of sort of common pain points and solutions, like a lot of case study type stuff. So what are locks in Postgres? Postgres has its own notion of locks, has its own built-in locking system and it's not the same thing as file system level locks, if you're familiar with those. It's not the same thing as max time value archive delay or max standby streaming delay. Those are, those can cause lock-like or delay-like behavior in replicas, but that's not where we talk about when we're talking about locking. And there are a bunch of lock types in Postgres and they all have these kind of, oh, sorry, so there are virtual transaction AD locks. Every transaction has a sort of lock associated with it. There are extend locks, which you see rarely, but those are locks that Postgres takes when it needs to make a table storage physically bigger on disk when it needs to actually extend the file that's representing the next segment of the file of the table, sorry. If you ever do see a bunch of extend locks, it's a good indication that you have IO problems and you will like, you will definitely know it. And other page locks, which you don't usually need to think about very much. In fact, all three classes of locks that I just described, I'm kind of mentioning for completeness sake because you will see them in the PG locks table if you go exploring for locks on a live system, but you don't really need to worry about them. The everyday sort of thing is table locks and row locks. So why would you have locks in the first place? What causes a lock? You can get them implicitly. Basically every SQL statement that runs will take some sort of lock. Even if it's just a select or a copy, it'll take an access share lock, I believe. DDL takes stronger locks because it kind of needs to keep certain things from being modified while it's in the process of changing your table structure, whatever it is that you're changing. And vacuum and auto vacuum themselves actually do take locks, too. And in fact, those can conflict with DDL as we'll see a little later on in the talk. And also, you can just take a lock if you want to. In SQL, you can write this kind of thing. Lock table, my table, in mode. Loud name. You can get yourself into trouble this way, but sometimes you need to do it. Any other option of locking the whole table or locking the subset of rows in the table. We will talk a little bit more about that later. There's a select for update. And a few other select for share key mode that says, so these rows that I'm specifying with this work laws I want to lock. Anyways, so what about all these lock modes that I mentioned? There, you can basically, the easiest way to think of them is precedent levels. So one level will lock out another level, but not first, and they all have these really confusing names like share, update, exclusive mode, which doesn't really mean a lot to me intuitively. So don't try to memorize them, at least not up front. You can just do what everybody else does and look at the docs. This page here shows all the different lock levels. Has a table of which ones conflict with which other ones. And then for each individual locking level, it'll tell you this lock type is taken by vacuum, and it blocks DDL and other vacuum or auto vacuum or whatever it happens to be, pretty user-friendly. And also on that page right below the table locking, they're a low-level locking, the same thing for rows. So Postgres is pretty good about locking. Even though it can be obtained point, Postgres really does its best to always use the least annoying lock mode possible. So for instance, if you're running DDL, you can still write to the table. If you're doing auto vacuum, obviously you can still write, which is good, because auto vacuum happens all the time. If you're writing rows, Postgres will still allow other queries to read those rows. Although because of the MVCC semantics, they might be seeing an earlier version of the row from like before you wrote it. And, sorry, that's a formatting there. And if you're writing some rows on a table, you can still write other rows on a table if only they don't overlap, that's cool. So let's talk about the examples, the promised examples. That's pretty much it for the theory. Before we do, any questions on what I said so far? Philosophical questions we'll have done for at the end. Nope, okay, that was kind of abstract. So a really common problem is, that I guess I've alluded to a couple of times already, is vacuum and DDL, not playing well with each other. Let's say auto vacuum is running, you've got a really long auto vacuum on a table and it's not letting you run new DDL statements and they're sitting there just walking. You can't do your migration. Or, you can get the other thing, by the way around. And one naive solution that people sometimes attempt is they go, oh, well, auto vacuum's got some problems. Why don't we just disable auto vacuum? That'll fix everything. But don't do that, don't, don't, don't. Because if you do, you will quickly get bloat and more importantly, you will start driving up towards transaction ID wraparound. And you've only got two to the 32 minus like three or four transaction IDs for the database and if you get to the point where you're gonna wrap around, then suddenly it won't know like what transactions are in the path to which ones are happening right now. So it'll prevent that horrible corrupting event from happening by shutting down your database. You really turn off auto vacuum for a long time. And then you'll just have to have vacuum everything annually and you can get it in user mode. Not much fun. So actually, paradoxically, a decent solution is make auto vacuum more aggressive. Make it happen faster, make it do its work and just get out of there. Another good technique in this case can be to just vacuum manually because auto vacuum has various cases where it'll back off when other activity is happening to a table to try to be less obnoxious. But that has the result of making auto vacuum take longer to run. So sometimes you just wanna blow through a bit of vacuum. As far as making auto vacuum more aggressive, I can actually have a whole set of talk about vacuuming. We might be able to start this in the Q and A time period if there's interest. But a couple of interesting settings to look at are auto vacuum, vacuum scale factor, which you can adjust from per table basis. Large table is only getting point one is the default scale factor. That means it's only getting vacuumed every time 10% of its rows are updated or deleted. And if the table has hundreds of millions of rows, it's not getting vacuumed often enough. You can also set auto vacuum vacuum cost delay to a lower value to make it just take breaks that are shorter when it hits its... Okay, I don't want to do too much detail. But take breaks that are shorter therefore get through a bit faster. And another solution is if you have really big tables and they take forever to vacuum, think about partitioning them. Especially with new post-crest versions with built-in partitioning, declarative partitioning is getting really nice and not terribly painful to manage. Or you might even orchestrate that data to somewhere else like another warehouse. So what else? Well, I already kind of intentionally mentioned auto vacuum sprees. That's the process that goes through and reclaims old transaction IDs in a sense so that you don't run out of them. And you'll know that this is happening if you do a PS, a Denix shell, and you see auto vacuum, blah, blah table, parentheses to prevent wraparound. You wraparound? Something like that. Wraparound, wraparound. And the other thing about auto vacuum sprees is another naive but bad approach is to try to kill that process. But if you kill it, it'll immediately start over because preventing wraparound is so important. And I guess this is another variant on what I was saying earlier. If you don't want auto vacuum hogging your table and locking it up at unpredictable times for a long period, think about maybe where you'll have a low traffic period where you can just run an explicit vacuum ramp and put down all that technical debt and then it won't happen the next day. So that's pretty exhaustive in terms of the DBL versus vacuum situation. Another problem that we sometimes see is people using select for updates or they're not totally cognizant of the consequences. So the deal with select for updates is your, it's a way of saying, as I mentioned earlier, I want to do something to these rows and nobody else wants to use rows as well. The way you would do it is, sorry, start doing explicit transactions by saying begin and then you'd say select blah from table blah, where, condition, for updates. And then you'd do your thing with those rows and eventually commit at which point other people would be able to write those rows. One, well, I'm actually really easy to hook on with select for updates is that select for updates conflicts with other select for updates. Makes sense if you think about it. So if you have a table where there are all sorts of processes trying to lock bits of it and change bits of it, but they're changing like overlapping bits, then you can end up with a pretty pathological situation. We actually ran into a situation with one client where they had effectively serialized all access to their table. And we're no longer getting the benefits of a relational database at all. But, yeah, so it's easy to use overviews without really realizing it. And another really specific observation is that people tend to write this into queries when they're trying to use a table as a queue. And if you're thinking about using a table as a queue, don't. There are all sorts of great queuing systems out there like RabbitMQ, or take your favorite, and use this as it is. There's just an impedance mismatch with relational model and a queue model. Another common problem, you'll see processes that are idle and tracked transactions. This shows up in a pgstat activity in the state column. It's literally a spring, just all lowercase idle space and space transactions. So when a backend is in that state, what it means is that somebody called began, and then they did a statement or two, and then they never got around to calling commit for some reason. And the backend hasn't done anything for a while, so it's just sitting there. And so what that means is it's holding any locks that it had from the previous statement. And also it's data are not visible to anybody, and they're not even committed. So here's the example of that. So the rows in right here are exact started when the actual transaction started, when the person called began, or the program, whatever. Query start is when the last query inside of that trend. So if you see things where a transaction starts and or query starts are pretty far in the graph, that's a big red flag. Looking at your data, they also present auto vacuuming, and that can be actually really important if you need to do a transaction that you have around auto vacuum on the entire table because you don't advance the last transaction ID counter and effectively reclaim all those transaction IDs for you until you finish the table. So we also had this situation with a client recently where they had an IIT, and that can be hard, especially in organizations where there's a fixed separation between DDAs and Devs, and maybe they don't get along very well. So one thing you can do actually, although it's kind of rude, is just kill those transactions after a while, set some reasonable threshold and be like, look, if you haven't called Commit in 10 minutes, you have commitment issues, and you're not gonna commit, so your transaction's going away. And if nothing else that will give you attention, maybe not the kind of attention that you want. In fact, in recent versions of Postgres, there's a setting in PostgresEqual.com that you can set to just auto kill them. If not, if you're on an older version, it's trade-hilled to write a Chrome job. But if you don't want to be that mean, you can just write a Chrome job that looks for these transactions in PgStatActivity and logs them or emails someone, maybe emails like your entire developer list and says, hey, where'd this query come from? So that's about it for idling transaction. Another problem is certain types of DDL I want to highlight. So almost every type of altered table statement takes an access-exclusive block on the table, even if only briefly. So that means that while it's doing that operation, like everything is queuing up, even selects can't see the table. And actually, so how many people here are using Postgres 11 or later? Well, I guess not later. Yeah, okay. So you're gonna be immune to this problem, so you should be happy. Let's say you want to add a column and you want to make it not null and give it a default. So when you do that in pre-11 versions of Postgres, it takes an access, excuse me, takes an, we're live again. Do I sound okay? Great, thank you for your patience. So we were talking about DDL and specifically the case where you, you're adding or altering a column with the constraints not null and or default. And the problem is that it takes this access-exclusive block that prevents any other activity on the table, but then it rewrites the entire table, which can take forever. And if you do this at the wrong time, you can just bring your system to its knees, basically, with so many queries queued up. Things start timing out on the web server side and it's a bad scene. Oh, a good tip for that actually is when you're running DDL, think about running it inside a transaction because transactional DDL is this great feature that Postgres has. Again, do my DDL statement, and if you don't like it, like control C and roll back. But the other cool thing about this is that it's completely fixed in Postgres 11. There's a clever scheme where it basically enforces the constraint going forward for new rows and then it sort of fake backfills the old rows so that when people request them, they get the default value if there's a null value in there. That's really cool. Implemented by my former colleague, Andrew Dunstan. So another reason to keep upgrading Postgres. The workaround for this, if you're not on Postgres 11, is to create the column without any constraints initially. So no, not null, not a default. And then backfill all the existing rows, like go and set that one column value to the default value. You want it to have, probably do that in a batch of maybe like 5,000 rows at a time. Just a little beyond the scope of the start, but, and then use triggers so that when new rows come in, the default gets filled in, even though you don't have an explicit default constraint. And then once you have every row backfill, you can alter the table and add that constraint. And yeah, if you add a null constraint, then you do, it does have to check the entire table to make sure there aren't any null values present. It doesn't necessarily have to rewrite the entire table. But seriously, use Postgres 11. So that actually concludes this section of my talk where I'm talking about like common pitfalls, gotchas to watch out for. And next we're gonna go on to sort of like, so you have a situation where you think there's locking, but how do you find out exactly what's going on in the database. But first, any other questions? Yes, oh, actually, they're gonna bring the mic to you so we can record your question. For update, we generally weren't doing a lot of that, but we saw the workaround of like order by just to kind of like serialize the access of the rows better. Well, I guess, when are some good examples to be using select for update? And like, are you, because we're still seeing a little bit of locking with like while updating tuple, like on some ups or queries? Yeah, one example that jumps out at me from my consulting experience is we had a client who had written an auction platform. And so they needed to update a row or potentially update multiple rows, possibly related rows joined to a single table. And they needed to make sure that nobody else is doing that so that the bid would go through in the right order. That's legit. And they were doing pretty good job of scoping it down to just those rows that needed to be infected. Like if we know, we definitely know which rows we're gonna be modifying if you recommend select for update or just let the database handle it. It depends on the use case. I would be inclined to use select for update if you're gonna be inside a transaction where you're doing multiple things with those rows and then committing, you know. Then I would, if it's possible to do it and get the right semantics, I would definitely think about just letting Postgres handle the concurrency. And then one other, if I can? Sure. Kind of like, have you seen the brain tree high volume like safe operations track of like, what's safe to do on a table or not without that scope? I have not seen the brain tree thing that you're talking about. I guess the question, if I'm understanding it correctly, is how do I as a DBA keep track of what types of, say alter table statements are gonna take a relatively brief lock versus which ones are gonna take really a big long lock? A couple of resources for that. So there's that, there's actually just the Postgres documentation page on the web for alter table. And it gives you every single like possible type of alter table expression. And it just says, this is going to take an access exclusive lock except we're otherwise noted. And I think it is pretty good about noting cases where you need to rewrite. I'm not sure if it really like says, by the way rewrites are terrible and they take forever, but if you like search for the word rewrite. The other one is, I mean, I think this kind of points up the idea that sometimes it's, even in the the Wild West DevOps world, it's nice to have somebody acting as the gatekeeper for new DDL, or at least to have some kind of, maybe if we're using GitHub have like a pull request approval process. So the people don't just like fire off DDL that might cause problems. Any other, yeah. Could you wait for the mic please? Yeah, I'm not a database expert, know a little bit about it, but we're talking about locking either with the transaction or select for update. If we have other transactions that are coming in simultaneously from other sources, say your batch processing a bunch of data is coming in, you're wanting to update the database, does the system automatically block and wait so that those transactions, the other ones finish when they're unlocked? Or do you need another table to keep track of what's locked, what's unlocked? How do you typically handle a bunch of things coming in where they're going to be trying to write when they're locked? And that's part of the magic of Postgres. It actually does kind of automatically handle all that stuff for you with it. So you don't really need a separate table to keep track of. We're in the middle of processing this, wait till it's done. You don't really need to do that for yourself. Nope, you don't. I mean, you can just fire off that query and if it conflicts with the other query, it'll wait until it's done. It'll lock. Yeah. And usually that's okay. If it's blocking for effort, then you'll probably know about it and get to diagnosis. Okay, I'm gonna move along. So speaking of diagnosing things, my pleasure. So how do you tell when you have a locking problem in the first place? How do you even know to look at this? So the really classic symptom is like queries are really slow. They're a lot slower than usual, but resource utilization is also low. It seems like the machine isn't very busy. It got low IO utilization, specifically. Excuse me. You might still have fair to meddling CPU utilization. So if you see that symptom, you wanna really take a deep dive and see what locks are really happening right now. And you've got two options. I call them Null Mode and Later Mode. Null Mode is what it sounds like. You get in there and run some queries against system tables and see what's happening in real time. Later Mode is you go into plusgooseql.com if you turn on or turn up certain logging settings, collect a whole bunch of data and then you analyze it offline later. So the nice thing about Null Mode is you can do it now. You've got an emergency. The problem with it is it's pretty time consuming and it's really easy to be in a situation where you're writing these queries faster than the locks. Sorry, the locks are going by faster than you can write queries to actually capture them and everything is confusing. And you have to really know what you're doing. You have to be able to write queries that join PGStatActivity to PGLocks at a minimum. So I don't really recommend it. Even though I know how to do it, most of the time when I do it, I just come up with what you handed. If you do wanna do it, as mentioned, PGLocks and PGStatActivity are your friend. The very basic way to do it is to grab the PID from PGStatActivity, or sorry, join PGStatActivity and PGLocks on the PID of the process involved and particularly look for processes where the lock isn't granted. In other words, they are waiting on the lock because someone else has a lock. You can also do fancier joins that take a snapshot of a bunch of information at once. This URL is a blog post for me about a query that I used to investigate a vacuum problem doing that, but I'm not gonna go into too much detail about that. So later mode, a contrasted with now mode, it's so much easier. You get a lot more data because you just have this huge volume of logs that have been written to disk faster than any human could look at things. And you have the opportunity to extract some nice reports that are pretty user friendly and also catch logs that don't take very long like maybe hundreds of milliseconds, oops, sorry. And the disadvantage is that it takes a little time for you to collect the data, but that's the only disadvantage really. So what I usually say is whenever there's an emergency just get set up to use later mode, like turn on that extra logging if you wanna look at things in real time, great, but then after 15 or 30 minutes feed those logs through a log analysis program. Let's see what you can see. And speaking of log analysis programs, the kind of best of breed one is PG Badger. It's great, you just feed it your logs gives you this HTML report with a bunch of tabs and it covers a whole bunch of things besides logs. It will tell you about vacuum actions, temp files, what your top queries are by normalized queries, like if you're in 80, 20 rule situation where the number one query is taking like 80% of your time it shows you that very nicely. Because you never know, you could be wrong maybe logs aren't the problem or there are other problems. One issue with using PG Badger is you need to make sure that you have space for those profuse logs, maybe a few gigabytes. And you may need to make sure that you have the spare throughput to do extra logging. This is almost never an issue on modern problems, but it can be sometimes an issue, sorry, modern systems. It can sometimes be an issue on RDS where IOPS is like finite and you have a threshold. I mean, you have a ceiling, profusion IOPS is actually a ceiling, not a floor guarantee. And the other thing to be aware of is that in RDS there's only one partition, so logs and data are on the same device and they're using the same finite amount of throughput. Oh, another one that's kind of interesting is a real edge case, but if you're using RCSLGD there's an option, remote CSLGD that is, there's an option to write each log line across the network synchronously and wait until it finishes getting written on the remote side before you go on. And that is an absolute performance killer because if you can imagine if your network is slow then suddenly all of your queries are slow. And it looks like this, you see the suspicious hyphen at the beginning of the log file path that says do, oh sorry, the hyphen says do not flush. So you want it to be there. If there's not a hyphen there and you're using RCSLGD, put a hyphen there. Okay, so quickly running through how to run PG Badger, I'm not gonna go through all of these options explicitly but you can refer back to the slides if you wanna do a test run or you run into an emergency. Basically this is turning on like all the logging that you can possibly have. And maybe the most notable part of this is you turn log and min duration statement down to zero. That means that every single SQL query that's running gets logged. And also log auto vacuum and duration is zero so you can see all the vacancies are happening. You're useful with logs. And then you, once you've got the logs you run them through PG Badger. This is how I like to do it, just a few options. Pretty much self-explanatory. So I wanna pop out of key in a first second. I just wanna give people a quick look at PG Badger if you haven't seen it already. How many people here are already PG Badger users? Okay, a couple. This is the sample PG Badger report from the PG Badger website. And I'll tell you what kind of locks you have. So often you can go back to that table and the docs that I mentioned and easily infer from that what's creating those locks because a lot of these locks are only used, only created by a couple of types of statements. It'll tell you which queries are most often waiting for other queries to release a lock so they can do their thing. And these are normalized queries meaning that any parameters that are in them are abstracted out. So like you know, where X equals one, where X equals two, blah, blah, blah. It's all regarded as the same query. A hidden feature of PG Badger is that you can double click on these queries and I'll indent them where they mean for you. And then it'll show you the converse which is the queries, oh sorry, it's not the converse. It'll show you the queries that spend the most cumulative time waiting which is not the same thing as waiting most frequently. And that's mostly what you get out of the locks section. So hopefully with that information you at least have a couple of clues for SQL queries to go back and examine in your code and you have some idea of what specific types of queries are out there. The types of locks are out there. And also to kind of have super ultra, excuse me, fast tour of PG Badger gets you a bunch of generic query information, gets you temp file information to both the size of your average temp file and also the number of temp files. And temp files can actually look, temp file problems can look like log problems. So it's important to always check these two tabs when you're investigating something blockish because if you're writing really large temp files you can overwhelm your throughput and wind up start for IO. And in that case, it looks a lot like a query that has slowed down because of blocking. But the smoking gun, if you're looking at, if you're looking at metrics at system level and data dog or SAR or whatever your favorite tool is, is that you will see high throughput in this case but in a case where the problem is exclusively locks you will not see high throughput because nothing can get in and do its job about PG Badger. Oh, this one. Oh, wow, I spent a cumulative four hours doing this query. Usually you'll end up spending hours doing some query that only takes up a few milliseconds each but you might be able to reduce the query from taking 10s of milliseconds to single milliseconds by doing some intense optimization or something. That's a little outside the scope of this talk too but PG Badger is cool. That's the takeaway I want you to have from this. And as far as my talk goes, that is it. Thank you very much. 20 minutes for Q and A, so. At my previous job, we were using PG Badger and we were always kind of afraid to put long menderation statements like all the way to zero. How much of an in-bag do you find that? I don't really find it a problem to set, well, sorry, backing up. It's almost never a problem to set it to zero for a couple of hours and just grab a large volume of logs. It can be a problem to set it to zero 24-7 only because it generates such a large volume of logs. So you have to think about things like log rotation and maybe set up a log rotate, config stand set to compress your files and stuff like that. We were doing it, we set it to like five seconds. Yeah, that's great. I think five seconds is super generous. A lot of people do one second or even half a second. Okay, appreciate it. Sure. PG Badger with any hosted providers. We're on Peroku, for example, so we haven't, right, seems like just a quick Googling like we're not going to be able to use it. Yeah, I'm actually doing that with a client right now. So the answer is that Heroku does allow you to download logs using the Heroku PG column logs command. Yeah, exactly, you save the logs to a file. The problem is that logman duration statement is not setable in Heroku version, Heroku Postgres version previous to 9.6, which is such an arbitrary like restriction, I have to say because that's not true in the Postgres community world. Every version of Postgres can log whatever you wanted to log, but for whatever reason, if you're on an older version, it's definitely worth upgrading for that functionality. And Heroku Postgres upgrades use PG upgrade under the hood. So it's an in place upgrade, it's not two types. Okay, so yeah, this is the thing I covered earlier on the talk, but usually any form of halter table takes an access exclusive lock. So that's the form of lock that doesn't let anything in, including selects. And depending on what kind of alter statement you're doing, it can be a longer lock or a shorter lock, for instance, if you're dropping a column, I think it just has to, excuse me, note that in the table and note that in the system table somewhere and then it's going. But if you're adding a column, then you can get into these situations where it's like backfilling default values for every existing row that you have in that table or something. And even for a short lock, like a drop lock, sorry, a lock caused by a drop, but they're all the same lock type. Even for a short lock, it can be problematic if you have a super high volume of queries because they will, the next query to come along will be like, okay, I'm getting the lock queue, I'm waiting to get a lock and the next one behind that will get in the queue and then you could end up with like a thundering herd of queries that all like, try to happen one after another when the alter table statement completes. Yeah, sometimes you do have to take downtime to do DDL and it's frustrating. It really depends on what specific alter table statement you're running. So maybe we could talk afterwards and figure that out. Cool, thank you very much. Thanks for sticking around to the end of the scale and thank you to all the scale volunteers who have made this possible and for most and whatnot. See you next year.