How to Ride Elephants Safely: Working with PostgreSQL when your DBA is not around with Richard Yen

Hi everyone, welcome to working with PostgreSQL when your DVA is not around. My name is Richard and I'm very excited that you can join DjangoCon 2023 and attend this presentation Just a little bit about myself. I'm a software developer and support engineer at EnterpriseDB, where we work to help customers get the most out of Postgres. Prior to working at EDB, I was a Pearl Web developer. I was a Perl web developer and eventually became a Postgres DBA because our DBA moved on to another organization. I worked on my first Django project in 2020 during the pandemic, and because of the great experience I had, I am here today I've been using Postgres since the early 2000s, and I know that it can be a bit intimidating for people, so that's why I'm here sharing this presentation with you.

So who is this talk for? It's for Django developers. And Postgres is one of the default database engines supported by Django. And if you want to get the most performance out of it, this talk will help you definitely get started. Or maybe someone else manages your database in different environments and you'd like to get a little more involved to collaborate and to see how your Django app can be improved by tuning the database. This talk, it assumes that you're not intimidated by command line interfaces and you know a little bit about Linux, just enough to be dangerous. Or maybe you're like me, someone who um someone else manages your database and that person is on vacation or you maybe that person quit.

Or maybe you even just never had a DVA and now you need to learn how to use Postgres. So if any of these things describe you, this talk is for you. So where do we begin? Well, with Postgres, there's a lot to cover. As you can see, these are just some of the topics that we can have a 30 or 40 minute talk on. And I don't think we can squeeze all of this in into a 45-minute talk. So we're just going to go over a few of these today and just kind of give you an idea of what we want to achieve in the next 40 minutes. First, we want to be able to log in the database to start and stop it. You know, sometimes maybe there might be situations where the database is down and if you can get in, maybe you can start it back up. Uh maybe

uh you want to be able to uh take a backup of the database before some catastrophic uh damage occurs. So maybe your DB is not available and some kind of performance issue is coming or you're seeing a lot of errors in your app. You want to take a backup just in case something worse happens. Then we also want to be able to diagnose some performance or stability issues by reading the logs. And then you also want to be able to identify any changes that can improve performance. uh in the database. And finally, we want to be able to understand Postgres directory and file structure. So that way you know what where to look for things and where to not look for things and what things not to delete.

So just a quick uh roadmap. We're gonna get into the database. We're gonna look around. We're gonna understand how it's all set up. Uh we'll figure out how to do some maintenance on it, and then we'll look for ways to improve uh performance And then go over things that you don't want to do and talk about where we want to find help. So without further ado, we'll go ahead and get started. So we want to be able to start and stop. So I think first of all you want to have SSH uh access to the database host. So uh You'll need to either talk with the DBA or look at your application config to see where the what the host name is for either your development or stag or production database, whichever one you need to work with at the moment.

And then just a side note, if you're using things like RDS or Azure , you will not be able to get into the database itself using SSH. So you'll have to use the console to do the starting and stopping. So once you've SSH into the machine and you maybe you discover that the database is down, before starting it back up, you're going to want to do some sanity checks to make sure that it's safe to bring it up. So first of all, you want to make sure that there's enough disk space or make sure there's no full partitions using the DF command. And then You also want to look at the database logs to figure out why it was shut down to begin with. We don't want to just bring up a database just because it's down. We want to figure out uh what caused it to go down

and maybe if you bring it back up it'll just shut down again by itself. So we'll need to know those kind of things Assuming that that's all cleared, the way that we typically start Postgres is using the systemctl command, just like any other Unix service. You'll want to use SystemctL start Postgres UL. For some older versions and some distributions, you'll need to tack on a version string to the end. So you want to look at your listing of system CTL services and to be able to choose the right service to start out. Under the hood, systemctl will call pgctl, which is the uh the actual command that starts the database.

So if you don't want to use systemctl or you feel uncomfortable or a little too confusing, pgctl is the command that you actually want to use. In order to use it, you'll need to know where the database lives. And oftentimes that will be stored as an environment variable called PG Data. And if you don't know what that is, you will need to look for a directory with a PostgreSQL. com file. And that file is usually where the database lives. So assuming that you know where the PG data directory is, you basically can use PGCTL and pass in the D for directory for the PG data and then start and that'll start at the database

Now let's say you need to stop it for some reason. The same idea, you just do PGCTL, pass in the data directory, and press stop. Now by default, the stop command will wait for all sessions to uh to end before actually stopping the database. So uh if if suppose you have like a coworker who's actually using the database The database will not stop until that person is out of the database. But we, you know, a lot of times my customers come into situations where that person using the database is gone for the day. And doing a simple stop will not work. So in those situations, you'll need to use uh PGCTLF. The M stands for mode and then F is for fast.

So the fast mode will actually stop all the queries, uh terminate all the connections, and then uh and then stop the database. Sometimes that's not even enough. Maybe some kind of background process is running and it the the database is is is wedged, it's stuck. So in those situations you would need to use MI, which is immediate, and you use MI stop, which actually under the hood causes the database to crash. So you're basically crashing the database on purpose. And uh while that's okay in most cases, you'll need to know that once you start the database again, you're gonna be you're gonna start it up in recovery mode. which actually could be something that takes a very, very long time because Postgres

will do consistency checks against all the directories and you know that could be several gigabytes, hundreds of gigabytes, or even terabytes. So you want to use the MI stop very sparingly Okay, so uh let's say the database is up and now you want to connect to it. So uh what you'll need to know is the host name for the database and you'll need to know the port, username, and password Now, if you don't know those things, ask your DBA. If you can't talk to the DBA, maybe you might want to look in your application config. Look in the password manager or AWS secrets, wherever your application gets the database information for the connection.

Once you have that information, you'll use a program called PSQL, which is the database command line interface that Postgres comes shipped with. Some people prefer using a GUI database and options include things like PG Admin or DVBr. And you can Google those to find more information about it So you pass in to PSQL your host, port, username, and then you type in the password. And this is the kind of output that you would get. So as you can see at the top, uh PSQL and then the dash H is for the the host name and the U for user and then you actually uh the second EDB admin there is the

uh the database name that you want to use. So in uh Postgres you can actually have uh multiple databases which are different, I guess, workspaces for people to use. And the way that it's organized is kind of like a It's kind of like a namespace or schema in in Oracle. And if you use MySQL before or SQLite , a database is its own separate entity. You can notice that I don't pass in the port here because it's using the default, which is 5432. If you need to use a non-default port, you'll pass in a dash lowercase p

And once I'm in, I get a command line, uh edb admin equal and a greater sign. That's the uh typical way you'll see the database name, equal, and greater than, and then you can start typing queries. In this example, I use backslash D or backslash DN. Backslash D will list out all the different tables and sequences or views that can uh be accessed by the user at the at at this very moment. Sometimes the uh there are other schemas or namespaces that are not um Not listed by default. So if you want to see what default uh what namespaces there are, you do backslash DN for namespace And as you can see, there is a namespace or schema called my

schema, and then there's also the results schema. So um now that we're in, let's explore a little bit more. So what else is going on? Like maybe there's some kind of performance issue, maybe there's You know, you just want to know how many connections there are or what people are doing. You can actually type in a query called select star from PGStat Activity. And what that does is that it will show what's going on at that very instant. Now, uh the caveat here is that depending on the username that you provided, If you're not a super user, you will not be able to see everything. And people using

uh Azure and RDS, uh, those uh you will have limited uh visibility as well. Uh something also important is To know where the logs are. So if you use show log directory and press enter, it will give you the directory where all the logs are being put. And from there you can actually see uh historical data, like what queries are run, uh how long they took, things like that. If you're using RDS, I think the logs will be living in the console and you'll have to find it from there. Now from within the database, you can also do things like cancel a query or terminate a session. These are pretty powerful tools. You'll need super

user access in order to do that. If you are using a user that's shared, so like let's say I have a EDB admin user, or let's say I have a Django user If I log in as Django user, I can actually cancel other Django users queries. So you can actually control the the queries and activity of the things that you have access to Okay, and this is done by using these two commands, pg cancel backend and pg terminate backend. Now pg cancel backend will take a process ID and then it will uh cancel whatever query is running And then PG terminate backend will take that process ID and end that session altogether.

So these are useful if you have the if you have the access and you're trying to uh uh prevent some kind of infinite loop or or or resolve some kind of uh problem that your application is is uh doing in interacting with the database So here's uh just a snapshot of the PG stat PG stat activity output. As you can see, it's in a table format, and because of how wide uh the rows are it wraps around which is uh a little bit hard to see so uh what you can do is actually you can use a backslash x command which turns on the expand extended display And what that does is it turns all the rows in lists them out all the columns in

in rows as you can see here on the left. So for the first row, record one, you get to see all the columns, dat ID, dat name, PID, leader PID, and so on And then for the second row, it's listed as uh record record two, and then all the all the columns are listed in that in that way. Okay, so that could be very useful, especially if you have large and wide tables that you need to look at. Okay, so um in the interest of time I won't be able to go too deep into this, but let's move on to configuration. So all the configuration is located in the PostgreSQL. com file. And the PostgreSQL. conf file

once again usually lives in the PG Data folder. In some instances, like if you're using a Debian install or Ubuntu, you'll probably find it in the slash ETC Postgres folder. If you want to look at all the configuration, the current state of the configuration, you can actually use uh a command called show all which will actually print out all the uh configuration parameters and their values Now some of these configuration parameters can be changed without a restart. So you can change them on the fly just for your particular session, or you can change them for all the users, depending on your super user access or not.

The uh another way to look at the configuration is uh using this query, select name and its setting from PG settings where context is Cub or User Now I share this with you because the user level context are parameters that you can change for yourself. You can actually tell Django to make those changes for itself as well For SickHop, if you have super user access, you can actually make changes for all the users and reload the configuration and then have different parameters or different values for those parameters. And uh that might be useful uh in certain situations.

And uh when SICHUP is is a context where the values can be reloaded without actually restarting the database If you are using the database manually using PSQL, you can use set param , set as a as a command to set a parameter to a certain value for your particular session. And then if you need to make a change for the entire system , you would use alter system set param to value. Now I'll get into this a little bit later. I'll show you an example of how you can use this. When you are done making these changes, like you know, set

param or alter system set param , you'll want to use uh PG ReloadConf to reload the configuration Or you can actually go into the shell, like if you have root access to the OS, you can do systemctl reload, or you can find the process ID of the Postmaster. uh process and do a kill hub on that process ID. Okay. Some things that you might want to look at. We we find a lot of customers needing to change these things because of their experience with uh with how Postgres works. The first one is searchpad. So like I said earlier, the namespaces are not uh all shown by default So when I first did that backslash D, you just saw the

two views in the public schema. And then when I when I did backslash DN, you saw that there were other tables in other uh schemas like the my schema and the results schema. So in order to make that make those all show up by default, you can change the search path. So you can say set search path to results, public. And when you do backslash D, it will look both in the public schema and the results schema to get to find out what tables are available So for Search Path, you may want to make that change to include some certain namespaces by default. Some customers they prefer not to do that and they use the fully qualified

table name. So when you do select star from table. You either it will look in default by public and when you don't uh look in public by default and when you want to look in a different schema you say select star from results dot table. And then it'll look in the results schema instead of the public schema. WorkMEM. WorkMEM is a pretty important parameter. It defines the memory that's allocated for things like sorting and hashing. So if you join many tables together and you filter them and you need to sort them. All that stuff is sorted in a allocated space of memory called Workmen.

Now sometimes that is not enough. uh by default and what have what ends up happening is the query is so big that it spills to disk and because disk is uh is slower, the query becomes slower. And by increasing workmen accordingly, you can actually get a faster performance from the database. Now, you can do set work mem for that particular session, or you can do alter system set work mem like I mentioned earlier. Now, if you do an alter system and you set the work node globally, that could be dangerous because uh every session uh is allocated that much workmen to work with. Now if you have a

say um a a you know like a hundred users on your Django app then you and you allocate one gigabyte of workmen you can quickly start uh allocating 100 gigabytes which you may not have available on them. machine. So uh you only want to set work mem to like the average uh uh query set size and then for on a per session basis uh alter work mem to meet whatever require performance requirements you have at that moment. Finally, I think you may want to change or look at these log parameters They control what gets logged

in the database logs. And I think They they can show you like things like when a process began, when a user logged in. what the username is that ran a query, where where the host came from. So like let's say you have a whole cluster of web servers and you want to know which which web server issued a particular query. then that uh that uh IP address or host name will will get logged there. Um I want to take this moment to give an aside on the fact that database logs are not the same as wall logs.

So what are wall logs? So wall logs, uh wall stands for write ahead log. And what that is, is it's basically a journaling system that uh Postgres uh uh uses to um as a as a means of just disaster recovery and wall files they live in pg data in the pg wall directory Okay. And uh I share this with you because uh some customers they go in there and they expect to see logs because they think, oh uh a write-ahead log must be a database log and that is not the case. So uh when you look in wall pg wall you you will not uh find anything useful

and in fact you should not touch anything in there Because if you do, you could potentially uh corrupt your database or prevent disaster recovery. Okay, now the way that it works is um whenever a insert or update or delete uh occurs uh that gets reserved in memory and then when it's committed it gets flushed to a wall file And at checkpoints, a lot of different things happen, but basically what ends up happening is that the files in PG Wall get merged with the actual files of the database. Now the reason why uh Postgres does this is that it also is a um it helps maintain performance.

Because PG wall files are only 16 megabytes in size, um the database files itself could be uh tens of gigabytes and really large so to to actually write directly to those it could slow down the database quite a bit so um once again do not look in pgwall for anything useful and do not delete those files ever Okay. The screenshot just gives you an idea of how what things look like. So if you look at this top uh line in in orange, barlib PostgreSQL 15 main, that is the PG data folder. Now within the PG data folder you'll see base global um you know PG notify, PG wall, PG Exact, all these file, all these uh

are are things that the Postgres uses to make the database run. Now as you can see the PG wall folder is just a bunch of these strangely named files all 16 megabytes each. those are not very useful. They're all binary data that you'll um that you as a user or or or a developer would not be able to make sense of Okay. All right. So we talked about configuration, talked about a little bit about logs, talked about wall. Now how do we control who gets to access the database This is controlled by a file called pghpa. com. Now the pghpa conf file

basically allows connections to specific databases by specific users and IP addresses. And that's nice because you can basically control which users get to connect to which databases. You don't, you know, you not you know the Django user would not need to look at some kind of uh secret database that uh is for like HR or something like that And any changes that you make to PGHPA comp is something that you can reload without the starting the restarting the database. You can use a PG reload comp or a kill hub. By the way, PG HBA stands for uh HBA stands for host-based access. So based on the host ,

you can control who gets the access database. So uh so in this example uh screenshot here the uh you can see that it says host all all and then 12701 slash 32 So a host that tries to connect will all users will be able to connect to all databases if they are coming in from the local host. Now you can change that cider mask. You can change it to you know all the all the uh IPs in your data center, all the IPs in a particular subnet. By changing those you can control which uh which

connection requests can connect to a database. So in this particular example you can see it's it's global. But if you want to make changes to it to kind of clamp down who gets access, you make these changes and then reload. Alright, I'm gonna go into some um maintenance uh maintenance topics. So we're gonna talk about vacuuming. So as a developer, and if you get into the database and you say, oh, something's running slow and I don't know what's going on. And sometimes you might come into uh you'll see that oh vacuum is taking up a lot of uh disk I. O. and it's running kind of slow. And you'd be tempted to

uh to terminate those vacuum processes. But I'm going to explain to you why vacuum is important and why you should not necessarily kill those processes right away. So vacuuming it will uh help maintain performance by preventing disk bloat. The reason why uh the database can possibly bloat is because things like deletes and updates, they they don't actually delete data out of the database. They simply flag a row as deleted. Because uh that that row might be still visible to someone who is you know within a transaction So it's a it's a way to provide uh uh

ACID compliance or uh viewability to multiple sessions called MVCC multi multiversion uh concurrency control. So these updates and deletes, they don't they don't actually uh modify the existing data on the database. Now once they're flagged as deleted, they just there, right? And if you keep updating and deleting, you're gonna have a lot of these rows that are flagged and invisible to most users. And that will take up space. Now, wouldn't it be nice to reuse some of that data? If it's deleted and no one's using it, no one can see it, all the sessions that could see it are all ended, they can be reused now. So what Vacuum does, it actually scans through

all the database tables and flags them as reusable. So a future update or future insert can actually reuse that row. So as you can see, vacuuming is a very, very important part of main keeping your database trimmed and making sure that things continue to run at a good performance level. Now there is a program that comes built into Postgres called AutoVacuum. And what AutoVacuum does is that it will at periodic intervals, which is every minute basically, it will look to see, oh, is it time to vacuum a particular table?

And if it says no, it's not time, it'll look at the next table, next one, next one, and find out uh if there's any tables that need to be vacuumed. And sometimes we will find a table and say, hey, let's vacuum this one. We need to, it's about time. And those are all controlled by uh the auto-backing parameters, which uh which are all in the PostgreSQL. com file. Now Sometimes those tables are really big and it needs to be vacuumed. Now you can kill the vacuum process, which A minute later, when the auto vacuum wakes up again, it says, Oh, I need to vacuum this table. And then it'll start vacuuming it again. And you'll have this perpetual uh situation of uh slow performance because it's trying to vacuum this

really large table. So usually I recommend that customers they they just wait for the vacuuming to finish But if they absolutely cannot wait because it's you know preventing users from uh from working or using their application, uh you can terminate the back end. So basically kill the query. And then manually run it. Manually vacuum it. Oftentimes the vacuum is slow because some delay is set. So you'll want to manually run it with uh with a with a delay of zero. And then you'll also uh probably want to change the maintenance work mem. and and give more uh maintenance workman

to the vacuum process to to work with and hopefully that'll go a little bit faster Okay, so that's a little bit about maintenance uh related to vacuuming. Now uh another maintenance task is to take backups. So you know maybe you are working in a development environment and you want to take a snapshot of your database, you want to save that data and be able to restore it again later. You'll use a program called PG Dump. And what this does is that it will provide a plaintext dump of the database. It's kind of like PSQL. It takes in the uh the host name, port, user, and database. And then out comes a human readable database, snapshot of the database with all the different commands like create table and insert and stuff like that.

You can actually limit which uh which namespaces or which tables get dumped by uh by passing in the related flags. You can also tell it to compress the dump and provide a binary version of that dump. Now the caveat is uh Well, I'm sorry. Uh the the thing about PG Dump is that it will uh basically translate all the binary data in the uh the base folder, the the all the database files into uh into like you know plain SQL. So what you end up with is uh is something that will Most likely 99% of the time

will get loaded into a database without any kind of errors. So it will not copy any kind of corruption that you might have in your database So taking a PG dump is uh is pretty uh important, especially in a production environment, to uh kind of safeguard against corruption. The alternate is to take a PG-based backup. Now the PG-based backup is uh is is a bit different from PG Dump. It basically takes a snapshot of the entire PG data directory. So it it takes the database files as is in their in their binary format. It doesn't try to convert it into something human readable. But it includes all the things like indexes, foreign key constraints, things like that.

So it's good to preserve the state of a database really quickly. Because it doesn't need to take in take any extra effort to um to translate that stuff into SQL. In order to take the PG based backup you'll need to set maxwell senders because what it does is it treats it treats it like you're trying to create a replica uh replica database and And that will require uh Maxwall Sender to send uh the send the wall information and the uh the binary information. And you'll need to invoke PG-based backup with a user that has a replication privilege. Once again, it is faster because it doesn't need to do any translation.

But if the database was corrupted to begin with, it will copy that corruption along with it. Okay? All right, um let's see. So we're gonna talk about monitoring now. I think we're uh getting pretty close to the end. Uh So monitoring, you'll want to look at the logs. So we we at the very beginning I mentioned a parameter called show log directory. And within the log directory you'll be able to see um different rows of uh of of kind of entries of what what happened in the database. And Depending on how things are set up, you may or may not want to change

these two parameters, log line prefix and log min iteration statement. Log line prefix basically will will prefix any entry in the log with uh with the thing that you define. So it could be uh a timestamp, a IP address, and a user and the database. By default, Postgres will only log just the timestamp and that's pretty much it. So So if your DBA has not done this already, uh you may want to recommend that this get changed that you can do uh uh you can do a better job of tracking down where queries came from. I have a separate talk on this and it it actually takes a bit of time to go into the details of this. So I'm just going to mention that

long line prefix is something that you want to change. Logmin duration statement. If a query takes a certain amount of time, it will get logged. uh you know select star from you know whatever uh whatever that query was asking for if it doesn't take that much time then it won't get logged So let's say you want to find all the slow queries, queries that take more than one minute. So you set log min duration statement to one minute, and then anything taking less than a minute won't get logged. You'll never know that it got called. And anything that takes more than a minute will get logged with the amount of time it took and the query that it ran. Other parameters that you might want to

look into? The log statement parameter it basically logs all statements before executing them. So that won't that won't help you identify any queries that were slow, but it will help you identify uh maybe like some kind of pattern like oh you know this this query getting called a lot and uh maybe that'll that'll cause you to investigate deeper to um you know are there performance problems with that query or whatnot. Logmin error statement will only log uh the statements if certain error thresholds are reached. So uh By default, it's by if it's the error. So if the if the query that was

given is has a bad syntax or the query the table doesn't exist, it'll print out an error message and say, oh, this query failed. In some situations you may want to uh uh well in some situations you may want to log things like warning. Warnings are just pending uh issues like maybe you have a um uh transaction ID wraparound or something like that. Uh something that that could cause a problem in the future and the the query will get uh logged with it. Um fatal and panic are things that uh would should cause concern and investigation.

Panic is when the database crashes altogether and fatal is when the when the session gets ended for some reason or another. So by default, error, fatal, and panic will all print the queries and you'll be able to see what caused those error messages to come up. Long duration is not that useful. It just prints out a duration. It doesn't print out the query with it So it's not, in my experience, it hasn't been that useful, but for some people, maybe they just want to keep track of different durations and do some kind of like grappling up those values or something like that Log min duration statement is definitely more useful because as a developer you want to know which queries cause the

slowness. If you want to use uh PG stat statements, that's an extension that Postgres provides and that basically collects historical data in all in one view or table of uh what uh uh what queries were recalled and how long they took stuff like that okay um performance so you know we we We've identified some queries that are that are slow and we want to know why why they're slow, right? And our DBA can't help us with that right now. So we go in and we look and We find or how do you tell what a fast quer uh how fast a query is running? So you use a uh uh something called explain.

Now explain has two flavors. It has the explain flavor and the explain analyze flavor Now, explain just basically tells you what the query plans to do. And the explain analyze tells you what the query planned to do and how it executed and all the statistics that came out of it. If you are uh able to, there's a there's an extension called auto-explain and that uh if you uh set it up correctly, it'll uh it'll Print out explain analyze for uh for queries that that meet a certain slowness threshold I as a support engineer I found this very useful many times because

our customers, they uh use an ORM and Sometimes uh what the Oran does is a little bit uh you know unpredictable or it's hard to know So ExplainAnalyze will basically tell you what the ORM sent to the query planner and how the query planner executed the query. So here's an ex uh here's an example of explain and explain analyze. So uh at the top uh we see explain selects star from GG branch accounts And what you see is just that it scans the two tables and then it joins them together with the nested loop. And that's what it plans to do. Now in the bottom one you see explain analyze

and the same query and it shows you the same the same query plan but it also shows you the actual the actual time and the number of rows that it actually found uh during the scans. So as you can see here, the query took uh uh 25 milliseconds to do the sequential scan on pgbench accounts and then it took uh 0. 025 milliseconds to do the scan on pgbench branches And then after joining it all together and sending it back to the user, it took 61 milliseconds. So this is very useful to help you identify any kind of bottlenecks in your queries. So let's say you identify uh that there's there's a problem.

Uh what you'll want to do is make sure that you're using the correct data types. So some customers have the habit of just using all text or all ints for their data, for their columns. And that isn't the best way to use Postgres. Postgres is uh is it has uh the ability to do indexing on uh uh on your on your specific data types and if you index correctly and uh set your data types up correctly you'll actually get better performance I know a lot of customers they use JSON because the whole world is using JSON. JSON is a text format and it's hard to build indexes.

So use JSON only when you have to. Try to extract the data out of your application and insert it into particular columns. so that uh so that the indexing and the query can run faster. Now indexing. It's very important to have indexes. Because the way that indexes work is that it basically is a shortcut to identify the data that you would want from that query. So here's an example. So I have updated the database. I have set BID to AID. In the real world, you may not want to ever do that, but let's say in that in this situation Now let's say I do explain analyze on PG Bench accounts

and I notice that I'm doing a sequential scan and it's taking me 45 milliseconds. Now in the real world, 45 milliseconds may be pretty fast, but in the application world, it could be kind of slow. So we're doing some sequential scan for these for this row, for this value where BID equals one. Now We're gonna scan the entire table and we're gonna only discover that only one row has BIED equals one. Now why do we need to scan the entire table? That would be a waste of time, right? Now if you have an index, basically it tells you uh Which which rows or which parts of the database have VID equals one?

And will only point you to that particular row that you need So if I create an index like you see here, create index PDBA B I D IDX on PD Bench Accounts B I D And I do this do the select again. You can see that this time around I do an index scan and it found my row in 0. 77 milliseconds. And once I found that, I go grab the data that I need, present it back to the user in less than 12. 12 milliseconds. So using P uh using Explain Analyze is actually very, very useful when it comes to improving the performance on your Django

app. Now, things to avoid, what not to do in the database. Okay , just really quickly, don't ever call kill 9 on any Postgres process. What that does is it causes Postgres to crash and enter the recovery mode and then it has to scan all the data files, has to replay the wall log, and it could take a very long time. That could be very slow and cause an outage for your application. Another thing is you want to be pay very careful attention to idle transactions So as a as a developer, always commit or rollback

any queries that are called When you're using PSQL, you want to make sure that you commit a rollback and get out of PSQL just so that there are no transactions left idle. Otherwise, we've seen many customers do this. You know, some some coworker started a transaction, got distracted, and then you know got up to get a cup of coffee. and never actually rolled back a transaction and all the other sessions were piled up because they needed to get a lock on it. On a table. And because they couldn't, it slowed down the database, it basically caused an outage. So you want to make sure by using PGstat activity to look for the phrase idle and transaction and

deal with those appropriately. You'll want to cross-reference with a view called PGLOX and that'll help you see what other sessions are being affected by this idle transaction. Okay. When it comes to making schema changes, try not to drop anything. So don't drop indexes, don't drop schemas, don't drop columns, don't drop tables, rename them. Rename them so that if you need to uh if you ever need to roll that back, you have to bring that table back or bring that index back, you just rename it back to what it was before. So that way uh there's a record of the old data that that you might need to

access. Or at least dump them, dump them to uh with pg dump to a file. And then that way uh you can come back and use them again if you need to Once again, do not delete anything from the PG Data folder, especially in the PG Wall folder. We've had many customers who thought they were deleting log files, but in reality they were deleting wall files and as a result uh they had uh they had database corruption and usability issues. So finally, you know, if you need help, there's actually a lot of places where you can get help uh with using Postgres. There's a Slack,

Slack channel. There are very active mailing lists. There's IRC, there's a wiki. The Postgres documentation is really, really good. I recommend that you take a look at it. And then finally, if you need person-to-person support, EDB is available to provide support for you and your organization. So thank you very much for attending this talk and hearing this presentation. And I hope that you enjoy all the other presentations that you will be viewing at DjangoCon 2023. Bye-bye