A Guided Tour Through Postgres Internals with Elizabeth Garrett Christensen

Thank you so much for having me. I love Django Khan. Um I do quite a bit of community stuff with Postgres and I copy all of the great ideas that you guys have. And I you guys are real leaders in kind of how to build um an awesome community and Thank you for inviting me back. I always love speaking here. Um Postgres internals is without a doubt the most boring topic I could be talking about at the end of this conference. I'm gonna try to make this entertaining and painless. Um and if I don't succeed, you can buy me a drink later and that'll be more entertaining than this. I work at a company called Crunchy Data. We do managed Postgres on cloud. We have a new data warehouse product.

And then we also do Postgres stuff in Kubernetes. This isn't really part of my talk, but Postgres 17 is coming out tomorrow. I've been watching and every everything's tagged and ready to go. Um if you are not on version 13 or above, you should be upgrading your Postgres. There's not a ton of super headline awesome features in Postgres 17, but there is some cool performance stuff, especially with B tree indexes. I know those are used heavily by um the Django community. So there's some cool stuff in there if you end up upgrading. I'm going to talk through a bunch of kind of little commands in Postgres. I have some queries. Here's a QR code

or a link to a gist file. Um nobody needs these slides because they're not that good. Um but if you need the sequel, it's in there. All right, so I kinda wanna just go through at a high level. I know lots of you have tons of Postgres experience. Some of you don't have a ton, so I'm going to start at the very beginning and then we'll get into some more complicated stuff. So if you've never gone into uh Postgres or into PSQL, there is a command line interface for Postgres. It comes and installs with Postgres itself. You can't really install it as a side piece.

Um you're fine, you already have it. If you have like you know your company's Postgres is running at Amazon RDS, you will have to install Postgres in order to work with the PSQL command line. It's not the biggest package in the world. Like it it could be smaller, but it's not terrible. If you're like on a Mac, you can just brew install Postgres. There's lots of other distributions that they provide as downloads that work pretty well. To get into the command line interface for PSQL, you'll just connect it to your host. If you're connecting to a remote host, like you have a database somewhere else that your company manages, you'll need to create a connection string with, you know, your user, password, host, port, and all that stuff so you know who you are.

Once you're inside PSQL, you can ask it, you know, who you are and can confirm all of that stuff. That's the slash con info command. You can do a slash du and find out who else is in this database. So who else has permissions, what other applications might be in here, all kinds of stuff like that. You can change the formatting with the backslash X auto. So if you've ever been in like in Postgres and you've queried through the command line. the the queries just get really, really long and they go out to the right and they're like impossible to read. So this is a way that it'll just batch up all the records for you and it's easier to read. So I know that people hate CLIs.

I hate CLIs. I had to learn the Amazon CLI this year and I told my boss I'm not learning any more CLIs this year. So if you don't like CLIs, it's fine. You can do most of the stuff that I'm going to talk about today in a GUI. Lots of people use PG Admin. It's very popular. I personally use DBaver. I think it's just a better interface. And then there's other Pythonic, you know, data grip tools and other stuff like that. So yeah. So now that we have kind of connected, I'm going to talk through some of the ways that we can kind of see what's going on inside our database. I'm going to start at the very, very beginning. So if you have a Postgres

installation, you may have more than one database inside of it. Um this is a nice way to to experiment, start test projects, do lots of stuff without touching um touching your other stuff. I like have probably like a hundred test databases. This is a small sample. So you can find out all the databases that are inside this Postgres instance with the backslash L. If you want to change databases, you'll land often in the Postgres database, but if you find other databases that you want to work inside, you can do the backslash C and you can go into another database. I don't know how many of you know this, but the entire Postgres ecosystem runs on Django. Um so all of our conference registration, memberships, like

the whole Postgres world runs in Django. So the Some of the screenshots in here are from the the Django database that comes with the Postgres properties. So backslash D plus will get you like a display of table information and show you what your table names are. Uh this is at the point that you're kind of trying to figure out what you've got, right? If you're exploring your own database or maybe someone's asking you for help, um this is a good place to start to see you know what you've got. If you do the backslash D plus, that'll show you the size of each of the tables. And if you need to like, you know, look at storage size, this is like an easy way to do it. Once you know what table you want to look at, you can do a describe table, and that's the backslash

D. And you can, and then you'll add the table name to it. The backslash D plus is really, really handy, right? Because it's got tons of data about the table. So it's sort of describing for you what columns, data types, if you have primary keys, foreign keys. So if you're, you know, kind of messing around in your um, you know, your Django code and you're trying to like figure out what's happening and what's been mapped to what and how it's displaying in the database, this is a great place to like confirm what 's happening. Um another just small plug for why I'm showing you this stuff in PSQL and I'm not showing you this stuff in queries. is that the backslash um DT, the describe

tables for Postgres is 12 separate SQL queries. This is a small sample of it. So you can do this yourself. Like if you want to get all of your internals described, you can totally find the queries that are probably on Stack Overflow or in the Postgres docs, and then you can run those in a GUI. But PSQL does a good job of kind of collapsing all that for you. Another cool thing that you can do with PSQL and all of the stuff that I'm kind of talking about today is you can do an echo So you can have the commands that you're running be echoed and then you can get the SQL for them. So like let's say that you want to echo, you know, the describe table thing because You want to change that query a little bit and you want to know specific stuff about tables, you can pull all the SQL and then kind of rearrange it yourself and decide what you want to do with it.

Um and then once you get past the describing the databases, describing the tables, describing what's in the tables, what columns and rows there are, you have to switch to SQL to actually see the data. There's no um PSQL for like data. And then you can also if you're mucking around and messing around in Postgres Um, you can find all your settings. You can they they're literally a select query if you if you're an an admin, you can just find all of your Postgres settings. This is super helpful if You're on a managed platform because you may not have all the bells and whistles to go into your underlying configuration files. And this is nice for people to just take a peek at what their settings are if you're going through the Postgres

docs. Um, one of the things I do at Crunchy Data is write like tutorials. Um, some of the stuff that I've showed you today is already inside tutorials that we have. Like I have one about basic PSQL, I have one about the echo stuff I was talking about. These all run in a web browser in like WebAssembly, so you don't need to install Postgres or do anything with your computer. You can just, it even works on a phone. I was like showing somebody Postgres business on a phone recently. So um that's uh learn. crunchydata. com if you want to mess around with that. All right, so let's kind of transition here into talking about um what's happening in the database. So

I know you guys are all really good at using like the Django tools to kind of see what's happening as it happens. Postgres has a lot of the same kinds of things, although they're stored in the database in a slightly different way If you're using some kind of monitoring, this is not a replacement for that. So I'm going to talk about a couple different things you could do. This is more just like things that are in the database if you wanted to to dig in a little bit more, not a replacement for, you know, some kind of um full application monitoring. So there's a table inside Postgres called PGstat activity that will tell you everything that's happening inside the database, how long it's been running for, when it started, and what the state is. This is super helpful.

Like if everything is not working and your database is completely not taking queries or you know things aren't working, you can go in and and see what's running and then you can um you can stop the the PID that's actually stopping the database from working. You can also see in the PGSTAT activity who is doing work in the database. So you can see if your application has connections. how many connections that application has, individual users, um, lots of stuff like that. So if you are kind of messing around trying to figure out um how many connections your application has open, I know Um, I was talking to a couple of people last night about just like the connection management, knowing how many connections are open or how many connections are idle

is super helpful when you're trying to figure out what to do with your connection management. Um several of the slides I have kind of sprinkled in here are like queries that have you know specific stuff kind of pulled out of them. And the reason for that is that the These internal tables that I'm talking about are just huge. There's tons and tons of data in them and it's virtually impossible to kind of talk about if I don't show you a small sample of it. So if you're if you're wondering if there's a lot more than there needs to be, this is Postgres, so of course there is Yeah. So another cool table for kind of um what's happening is the PGStat database table

that will show you all the transactions. Um that are happening in individual databases. This is kind of the way that people measure transaction volume If you run in this query now and you run it an hour from now, you will know what your transaction volume per hour is. And you could do that for days. But this is super helpful. for just knowing how how busy you are. Postgres holds a bunch of locks, which I'm sure you guys are super familiar with. You probably run a Django migration and it locked your database and Now you're not allowed to run Django migrations. Maybe it's just me. But yeah. You know, there 's a lot of things

A couple of things inside Postgres that will actually lock Postgres tables. Um, and those are table alteration commands, like if you change a column or do something like that. Um there's a couple other things that will lock your tables. Um a lot of times like I help um on our support team and we spend a lot of time dealing with locks. Um it is Kind of one of the unfortunate things about a relational database that's not trying to lose your data is that it needs a minute to like commit its transactions. Um But yeah, so you can look at what is locked. Like there's a table where the locks are stored. Um one of my colleagues wrote this query because what happens with these locks is something will lock. And then it'll be sort of this, you know, cascading effect, right?

And all these queries will be waiting for it. And you're probably trying to find out like Why is this query waiting? Like why is it not able to run? And then it's gonna take you a minute to figure it out. So um some one of my colleagues wrote this kind of LOX query that just like has a CTE at the beginning and then just kind of gives you the the original thing that locked everything up. So that's super helpful if you're like, okay, I've got to end the process that's locking like a hundred things. Um Postgres also has uh views for everything that's happened, so maintenance tasks that happen inside Postgres, right? Um if you're super familiar with vacuum. I'm sorry. I have also spent a lot of time working on Postgres

Vacuum. Um again, it's kind of just an unfortunate remnant of the transaction system that, you know, needs to kind of keep um dead stuff around. But you can check on things like if you um if you have a copy command, like if you're running some kind of ETL process at night and loading a bunch of data. Um you can just check on the process of um of those maintenance tasks, which is pretty handy sometimes. If you want to dig in more on vacuum, you can query Postgres and kind of ask it, you know, how oft how long ago did you vacuum and different things like that. All right, so um we've kind of covered what's inside of our database, what kind of data we have, we've covered what's happening.

Um Postgres has this whole other world of cumulative stats. um that are really good. And there's been a lot of development even in the last couple versions of Postgres to kind of build out some of this stuff. If you're using a Postgres monitor, probably some of the monitoring tools are built off of these pieces. But you know, they're kind of fun to just like get in and queer yourself to Um so the PG stat statements, if if you're not familiar with it, is kind of the um query tracking piece of Postgres. PGstat statements ships with Postgres, but it's not turned on. So you have to go and add it as an extension and then add it to your library. Um, but it is like

the most helpful thing you could do as an application developer to kind of find out like what's happening with your queries um how often good queries run. It's it's definitely the place to start when you're trying to do some query optimization and work, you know, kind of if you're working backwards from the database to your application code. So here's a query to just find your slowest 10 queries in PG stat statements. Surprising no one, the first slowest query in this database is a refreshing materialized view, which If you have any of those, they take like hours to run, depending on how big they are. But you can go through it and you know some of the stuff you're obviously not going to be able to fix. And then you can just pick the things that you want to work on.

Um there is a pg. io user table. Um there are a ton of I. O. um and memory related things inside Postgres now. If you're interested in memory usage, if you're concerned about memory usage or you just love kind of that kind of piece of of Postgres. Um some of these internal tables have have a ton of data in them. So one of the things that you can get out of PGstat. io user tables is a cash hit ratio. And so Postgres is keeping track of how many things it served from the memory. So that like shared buffer memory and then how many things that it had to read from the underlying disk. So, you know, if you're familiar with kind of how the ideal Postgres

world works, is that you want the vast majority of your data in Postgres's memory so that everything is super fast. So you're looking for a cash hit ratio, you know, in the 90s, but you can find out what it is. Um there's a PG stat uh user table um that has a bunch of information about what's happening when tables are queried and scanned or indexed. And this will show you kind of um This is a little query that somebody wrote that I think is kind of cute because they like did it up so that if there are more uh sequence scans than there are index scans, you're missing an index. So um anyways, this you know this is one way that you can find out kind of what what is happening on tables in terms of like actual um query behavior.

And then PGSstat user indexes, kind of similar to the one I was just talking about, has a ton of information about the indexes, how often they're used, what's going on with them. Um and then you can kind of decide like, oh okay, great, like that's index is being used. Or you can be like, this index is never used and I should delete it because um it's taking up space on my disk. You can reset all these stat tables that I just talked about, which is super handy. So, like if you go and you do a bunch of indexing and you do a bunch of great stuff with your queries. Um and you don't want to like muck up all of your stats tables, you can reset it. Um I sat down and showed someone my slides last night and showed them the slide and they reset all of their stats and I don't think they meant to do that

So maybe think about it before before you copy something from one of my slides and run it on your database. Yeah, so I'm just gonna kind of wrap up by like what I sort of really wanted people to get out of this talk and kind of people to walk away with is sort of you know that there's a bunch of data in your database, right? Like you know that Postgres has all of your users and all the data and all the settings, but there's actually a bunch of other information that is collected in the database, you know, for all of this activity and transactions, locks, connections, and then all of this like kind of performance over time stuff. um that you have at your fingertips and you can just do whatever you want with. I have a Postgres meetup that's online

called Postgres Meetup for All. It's pretty new. We're starting in October. If anybody feels like joining an online um group of Postgres people that are kind of loud and obnoxious. If you want to find me on social media, I'm SQLiz or as some people call it, SQLiz. Um and this is uh another chance if you need the gist file. And I'll take some questions. I'll take questions, I guess, in the hall so you guys can get your snacks. Awesome