Unlocking the full potential of PostgreSQL indexes in Django | Haki Benita

Speaker 1: Hi everyone, it's great to be here uh in Django Con Europe of 2021. Um So my name is Haki Benit. I've been working with Django for a very long time. And I actually do this for a living, working with Django and databases in PostgreSQL and SQL. And I also like to write about it specially about performance and solving problems And I'm very excited to be here in DjangoCon today and talk to you about uh performance and indexes and PostgreSQL. So before uh we start, I want to take a second and talk

Speaker 1: about this thing called performance. Usually when we talk about performance, we think about speed, but in fact, there's more to performance than just making things faster. So performance to me is uh what is the best way to balance all the different resources at our disposal? So, for example, you have CPU, you have memory, you have disk space, and all of these things cost money. So you also have the cost that you want to optimize for. Now, just as an example, to demonstrate why performance is not always speed, consider, for example, a background process that is doing some work.

Speaker 1: Now if this background process takes um I don't know one minute instead of 30 seconds to execute or to complete. It might not affect your application in any meaningful way. However, if this background process consumes a lot of memory or consumes a lot of CPU, it might actually harm the other parts of the system might affect them. So in this case you might not want to optimize for speed, you want to optimize for other types of resources such as CP you or memory. So this is something that we are going to talk about throughout this talk. We are not optimizing just for speed. We are optimizing for other things. So who's this stock for?

Speaker 1: We are talking about indexes in PostgreSQL and Django. So obviously this is for developers working with Django. But I'm going to talk in different levels. So if you are not even sure what an index is, if you're not working a lot with databases, If you never even explicitly created an index, then this is a great introduction video for you. If you have some experience working with indexes, but you're not sure when to use what type of index, and you are curious about it, then this stock is especially for you. And finally, if you're a seasoned developer, you feel comfortable with the PSQL console, you consider yourself an expert in indexing, I think

Speaker 1: that I can offer some fresh perspectives that might surprise you. So let's start. To uh come up with different ways of using indexes in Django and Postgres in general. I want to um Present actual use cases for a real project. So today we're going to work on a URL shortener in Django and we are going to see different types of solutions. solutions and how we can make them better using different types of indexes. So a URL short and for those of you who are not familiar with what exactly it does, then a URL shortener takes um like a small key that is appended to a usually very short URL

Speaker 1: And this short URL can be resolved into a very long URL. This is very useful in platforms that you have a restricted number of digits, like Twitter, for example. Or if you're sending SMS messages and you have a limited number of characters that you can use. Another nice feature of URL shorteners is that it provides you some analytics. So a lot of URL shorteners Count the number of clicks. So it serves like for analytics. So this is a URL shortener. And if we want to develop a URL shortener in Django, we can create Something uh like this model. So this is a short URL model. We have a primary key, which is a big integer.

Speaker 1: And we have the key. Key is the last part of the URL which identifies the full URL. We use a char field and we want to keep it short. This is also uh it must be unique because this is the identifier that we use to identify the full URL. Then we have the full URL we want to redirect to. We want to keep track of when this uh short URL was created. So we have a daytime field and we want to count the number of hits. So every time we redirect we increment this counter. So just for demonstration purposes, I created this model , generated migrations, applied them, and loaded one million short URLs into this table.

Speaker 1: Okay, so let's start So the first thing we want to do with a short URL table is to actually resolve a short URL to a full URL. This is what our project actually does So to do that, we can write this very straightforward function. We call this result. The function receives an argument which is their key, the last part of the URL. And it returns the URL. If the URL does not exist, we get none in return. And if it does, we get the URL to redirect. Okay, so throughout this talk we are going to jump between Django and the console and PSQL and PostgreSQL because we want to see what the SQL looks like and what the execution plan looks like

Speaker 1: So in this case, if you want to see what the SQL looks like, one way to do that is to turn on this slogger in Django settings. Okay, this is very convenient. Once you uh add this logger, Django will print out the SQL statements that it executes. straight in your console. This is very convenient, but just for debug purposes, don't turn this on in production. So if we turn SQL logging on and we execute our function trying to resolve this key. Into its full URL, we can see that Django constructed an SQL query that fetches all the fields from the short URL table and for a given key. Okay, so this is a straightforward query.

Speaker 1: This is what you would write if you had to do it yourself. And To see how the database is planning on executing the query, we are going to use uh explain. Explain is an SQL command that you have in most RDBMSs. And it shows you how the database is planning to actually execute the query. So if we add explain and paste our Select statement here, we can see the execution plan that the database came up with. So in this case, we can see that Postgres decided to use an index scan. Right here using the unique index we have on the key column. Okay, so

Speaker 1: when we create uh indexes in Django by default Django will create a B tree index. So the first thing we want to understand is what is a B tree index? So just like the slide says, B tree is like the king of all indexes. If you're not sure what you're using, using a B tree index. This is the default type of index, and this is most likely what you want like 90% of the case We are going to see other types of indexes, but for the time being, we are going to stick with B tree. So how Btree works, let's say we have these values one through nine and we want to index them with a B tree index So like the name suggests, a B tree index is actually a tree structure.

Speaker 1: And we have the root block. This is uh the terminology that is used by the database So this is the root block and this is how a B tree works. So everything lower than three goes to the left block. Everything between three and seven goes to the middle block. And everything greater than seven goes to the right block. These low-level blocks are called leaf blocks because they are the leaves of the tree So to demonstrate how the database is using a B tree index, let's try to search for the value 5. Okay? So first we start with the root of the index. And we see that 5 is between 3 and 7. So we need to go to the middle block, middle

Speaker 1: leaf block. This is this block. Now that we have the block, we have the values inside the block and we scan the values in the block and search for the value that we are looking for. In this case, we are looking for the value 5. The value 5 contains the actual value 5 and a pointer to all the rows that contains the value 5 for this column. So this is how a B tree index works So now let's try to see different variations of Btree Index. So going back to the query, when we wanted to resolve this key to the full URL, the database constructed a query, an SQL state. that fetched all the columns from the table for a given key. But

Speaker 1: if we look at the query, we can see that we don't actually need All the columns. We just want to resolve um key to a URL. So we just want this column, right? So the question is What if we fetch only what we need? Going back to the function resolve, we can change the function that instead of fetching all the fields, we explain Tell Django that we only want the URL. By providing it with flat true, we can return just the value for the first call. Okay? And if it's not found, we return none If we look at the SQL now, we can see that Django constructed a query that only fetched the URL, which is what we need.

Speaker 1: If we look at the execution plan , Okay, we can see that once again Postgres used the uh unique index we have on the key So that's good. Postgres use the index to find the row and then access the table to fetch the URL from the row, right? This is where a nice feature of Postgres and a nice feature that was added to Django in version 3. 2 comes in very handy. This is called inclusive indexes. Some of you may know this as covering indexes, but it's called inclusive indexes. Inclusive indexes lets you include additional values inside the index. That are not actually indexed. To do that, we need to switch. Instead of using db index

Speaker 1: true or unique true on the field, we need to explicitly create a unique constraint. Okay? And If we add this include argument with the name of the field that we want to include in the index, but not actually the index, we make this an inclusive index. Okay? So if I generate migrations and apply them and run the SQL again, this time the execution plan is a bit different. This time, Postgres used What's called an index-only scan. So this time Postgres was able to fulfill the query without actually going to the table. And that's because we want to fetch by the key, which is indexed in the index, right?

Speaker 1: And we want to get just the URL, which we included in the index. So Postgres was able to fulfill the query without accessing the table. You can spot that by looking at the execution plan and searching for index-only scan. Okay, so this is inclusive indexes, and as I mentioned, you might know them as carving indexes. You should know that they can make the index very large, so you need to be careful with it It's not the worst thing to access the table. And I think that a lot of good candidates for uh inclusive indexes are usually existing composite indexes, which are indexes that include a lot of columns. So sometimes you can make these

Speaker 1: these uh indexes better by making them inclusive instead of composite. Okay, so the next scenario you want to implement is try to find unused keys. Unused keys are keys that nobody ever tried to use. So these are short URLs with zero hits. So we implement this function called find and use keys and it returns a query set of short URLs with zero hits. So once again we want to see what the query looks like. This time we don't need to use the logging because the function returns a Django query set. We can use this attribute. called query and print it and we get the SQL generated by Django.

Speaker 1: This works because this time we have a query set and not just a string. So once again, uh Django constructed a query to fetch all the columns where hits equal zero. And If we want to see what the execution plan looks like, we can use uh this. This is very handy. I think that it was added in Django uh three if I'm not mistaken this is very handy so looking at the execution plan this is how Postgres is uh planning on actually executing the query We can see that Postgres is doing a sequential scan on the table. Sequential scan on the table means that Postgres is going to scan the entire table. And look for rows with zero

Speaker 1: hits. So if we try to optimize that and Add a B tree index on the hits field. We do that by adding a db index true, generate migrations and apply migrations and See what the database is doing now. This is with the index. We can see that now the database is using the index we just created on the hits column to find all the rows with zero hits. Okay. So at this point, a lot of very, very good and decent developers would stop and say, that's great, Haki, right? We have a fast execution plan, we have an index, and it's being used.

Speaker 1: But let me remind you, resources and performance is more than just speed. So let's look at the size of the index. So going to PSQL, we can issue a slash DI plus. I see that the slide is missing the slash DI plus. Anyway, you'll see that in the next slide So the size of the index we just created is seven megabytes, okay? And To try and optimize that, let's consider for a minute what do we know about unused keys? First of all, most short URLs are used. Just think about it. When someone creates a short URL. will usually try to test it immediately and it will not be unused.

Speaker 1: And we don't actually care about uh short URLs that were used. We only care about the ones where the hits equal zero. So the question is, why even index them in the first place? This is where another very useful feature that I very much encourage you to use. And that's partial indexes. Partial indexes lets you index just a part of the table, which is the part you need So to use a partial index, we can remove the db index true from the column and instead explicitly create an index on hits. And by adding a condition, we can make this a partial index. So if we generate and apply migrations and run the execution plan again, we can see that now

Speaker 1: Postgres is also using the partial index that we just created. But that wasn't the point. Remember, we said that the problem that we've had and the problem that we try to solve is that the index, the full index, was very, very big, right? With seven megabytes. So if we look at the size of the partial index, the index that only index uh short URLs with zero hits, we can see that the size is now 88 kilobytes. Okay, so that's 99% smaller. So imagine that you don't have 1 million short URLs, you have 1 billion short URLs. This won't be just 88 kilobytes and 7 megabytes. It can be multiplied by hundreds and hundreds. So partial index are very very useful and you should use them whenever possible.

Speaker 1: It can save you a lot of disk space and as a result make your queries faster because databases the database will need to do less I. O. I found uh nullable columns in especially Django to be good candidates for partial indexes. And I wrote about this here. This is a story of how we save 20 gigabytes of um unused uh indexes just by moving, just by recreating some indexes, partial indexes. So let's move on to the next um uh use case, the next problem that we are trying to solve. So we are uh we want to uh find uh short URLs by domains.

Speaker 1: So we want to find all the short URLs that are targeting a specific domain. Now, let me remind you, we don't have the domain on the table. We have the domain as part of the full URL. So to extract the domain from the URL, we can use a custom function in Django that uses a regext. To extract the domain part from the URL. Okay? So this is our function, getForDomain. You give it the domain that you want to search, and it uses a custom function to do it. And what is uh this is how the query looks like. You can see that Django used the expression and the regex that we generated in the function.

Speaker 1: to query for the domain and once again we want to see the explain plan you can see that Postgres decided to do a full table scan on the short URLs table And we can also spot that this time the query took a bit more than before. So this time the query took three seconds to complete. And that's only on 1 million rows. So this is where another variation of B tree indexes comes in Andy, and that's function-based index. or as I like to call them, FBIs. And using function-based index, you can actually index an expression. So if we take the expression we used in a function before And we feed that into an actual index, Django

Speaker 1: index, generate and apply migrations. Django will create an index that indexes this specific expression. So if again we try to uh view the explain plan for the query, we can see that this time uh PostgreSQL was able to use the index on the expression So we basically uh used an expression to create an index just on the domain portion of the URL. So we can see that Postgres used the index and the query was much much faster. This time it completed in 51 milliseconds which is 60 times faster than before the full table scan. So function-based indexes are tricky

Speaker 1: feature because they are useful if you are using a lot of expressions like the one we just saw. But I personally think that you should avoid them when possible because We can uh for example in this case where we create a short URL, we can extract the domain and uh add another field on the short URL with the domain. So now let's look at another interesting uh use case, and that's reverse lookup. This time we want to search a key by the URL. Okay. So far we resolved the key part to the URL and now we want to do that backward. Okay, we want to take a URL that we redirected to and find all the keys that point to it

Speaker 1: So we write a function called reverse lookup. It accepts a URL and it tries to find a short URL, all the short URLs that point to that URL. So once again we look at the query and the query is pretty straightforward. Fetch all the fields from the short URL table where The URL is the URL we provided. And we look at the execution plan and we can see that Postgres is doing a sequel scan, which is a full table scan on the table. It takes 100 milliseconds to complete. Okay. So once again, we can try to add a B tree index on the URL and see if it can make things faster. generate migrations, apply migrations, and once again

Speaker 1: view the execution plan and we can see that Postgres is using the index and the query is now much much faster. Okay So, are we done? Is that it? Are we finished? Can we go home? I think that we can do better. And if you stuck so far in this talk, you know that we can do better. So if we look at the size of the index, okay, the size of the index on the URL field is 47 megabytes. That's big. That's more than half the size of the table. Okay, so this index will not scale very well. Okay, so what do we know about the URL? We know that URLs are not unique.

Speaker 1: Okay. We can have many keys pointing to the same URL, right? But we know that this is not very likely. So we can assume that URL is almost unique, meaning it's not unique, but It's probably pretty much unique. Okay. So this is where a very uh exotic and interesting type of index, which is not a B tree index, this is a completely different type of index called hash index comes in handy. Okay. So let's see how hash index uh work in PostgreSQL. So let's say you have these values in a table Okay, so to construct a hash index, the first thing you need to do is take these values and apply a hash function on them. So Postgres has a lot of uh hash functions depending on the type

Speaker 1: In this case, this is a character, so the hash function is hash car. Now, I just say you don't need to do anything of any any of this. This is something that Postgres does behind the scenes when you create a hash index. So we have the values and now we have the hash value. Next we want to divide these values into buckets. So we use modulo the number of buckets The number of buckets is also something that Postgres determines for you. Don't need to explicitly say how many buckets you want. So in this case, we want two buckets. So we divide the values according to their hash values um two buckets. So A would be in bucket one and B, C, and D would be in the second bucket. And then we can build the index.

Speaker 1: So this is what basically what the index uh the hash index looks like. You might notice that the hash index does not contain the values, it just contains pointers to rows. So if we want to use the hash index and search for the value B, we apply the hash function on the value, get the hash value, and then try to search for the bucket. This time it's bucket zero. So the value B may be in bucket zero, not necessarily, but it's possible that if it's in the index, it's in this bucket. So we fetch the bucket and we scan the rows and we try to search for the value. Okay, so to use a hash index in Django, the first thing you need to know that this is a Postgres specific feature.

Speaker 1: So you need to import that from a country Postgres indexes hash index So this is how you create an hash index. Previously we used an index, now it's a hash index, and we want to index the field URL. Okay, so now generating migrations, applying migrations, and uh viewing the execution plan for the uh query when we have a hash index in place. So the first thing we can see that the hash index was used, right? Postgres did an index scan using the URL HIX, which is the hash index we created and we can also see that the query was

Speaker 1: faster. Okay so the query was faster with the hash index than with the btree index. But that's that's not the point, right? The point was that the B tree index was 47 megabytes in size, but the hash index is smaller, it's 32 megabytes in size So that's a 30% savings in size. That's huge. So when uh uh hash index is good, is uh is a good um uh type of index. So when the values are almost unique, hash index can be beneficial. One of the uh benefits of usually get hash index is that the hash is um not affected by the size of the values that it indexes.

Speaker 1: Okay so we use the hash index on url feed URLs can be pretty big. Just imagine that the URL can contain query params , hash fragments, encoded strings, can be a pretty big string. And the B true index actually stores the value in the leaves, but the hash index does not. The hash index hashes the values. So a hash index is not affected by the size of the values it indexes. We've already seen that as a result of that the hash index can actually be smaller than a B tree and it can actually be faster than a B tree. The thing that you need to remember about hash index is that prior to Postgres 10, they were discouraged. They were not production ready, but they are now. So if you are using recent versions of Postgres

Speaker 1: Create, you can definitely use a hash index i wrote a lot about it uh hash index is in this article you can check it out later there's uh Very interesting size comparison that I've done with a B3 index, but I don't have much time to go over this right now because we have one more index type left. So check out this article if you're interested in hash indexes. Also, I would just mention that hash index has some restrictions because of the way it's built. So you can't use a hash index to enforce unique constraints. You can't have multiple columns in a hash index. And because it does not contain the values, just the hashes, it can be used for sorting and for range searches.

Speaker 1: But for our use case, it's ideal. So for the last use case that we're going to go over, we want to find all the URLs that were added recently. I see that I'm finished with my 30 minutes, so I'm going to steal like five minutes from the QA. So uh the last scenarios I want that we want to um try and implement is find URLs that were added recently. Okay? So We construct this function. It receives a date and fetches all the short URLs that were created starting at this date. Okay? So to use the function we can uh

Speaker 1: construct a date and send it to the function and print the query. So this is what Postgres uh this is the SQL that Django constructed. Okay. And we want to see the execution plan. So uh Postgres did um scan the entire table, did a full table scan, which makes sense because there's no index. So once again, we can try to add an index on the field. This creates a B tree index, generate and apply migrations, and once again get the execution plan We can see that Postgres used the B tree index and the query is now much, much faster, but That's not the point.

Speaker 1: We are interested in more than just speed, right? So let's check out the size of the index in the database. So using PSQL slash DI plus with the name of the index, we can see that the BTR index on the creation date is 21 megabytes in size, which is roughly 20% the size of the table So, can we do better? Is there some exotic type of index that we haven't talked about yet that can save the day? So You know that there is. You know that there is. So what do we know about the creation date? We know that the creation date is set When the short URL is created. So naturally it's incremented. Okay? So as we add new short URLs which are appended at the end of the table.

Speaker 1: The dates of the creation date will naturally be incremented. So one might say that the table is sorted on disk by the creation date This is actually true for other types of fields like a sequentially generated primary keys also naturally sorted on disk. So this statistics is actually something very important that the database keeps on your data and it's called correlation. Okay? So when you have uh indexes with naturally high correlation, sorry columns with naturally high correlation. There's a very special type of index called block range index or in short bring that you can use. Now, brain is one of my favorite types of indexes because coming from Oracle, I always miss the bitmap index.

Speaker 1: So how does the brain index work? So let's say we have this values in a column, okay? Brain works by grouping together ranges of adjacent pages. So pages that sit next to each other on the disk are grouped into ranges So in this case we are grouping three adjacent blocks, okay, three adjacent pages. So these are our ranges 1, 2, 3, 4, 5, 6, and 7, 8, 9. So the BRIN index works by keeping only the minimum and the maximum value in each range. So 1, 3, 4, 6, and 7 through 9. This is the BRIN index. This is what a BRIN index looks like. So if we want to try and search for the value five

Speaker 1: in our table using the BRIN index, we scan the ranges. So the first range is one through three. So the value 5 is definitely not here. The next range is 4 through 6. Now 5 might be here. Note that I'm not saying that it's necessarily here. I'm saying that it might be here And finally, seven through nine, five is definitely not here. So using the index, we were able to restrict the search just for these blocks, just for these pages, four through six. So instead of scanning nine blocks, the database can now scan only three. Okay, so this is how a brain index works. Now remember that we talked about correlation and what happens and that brin index is a good candidate when the values are sorted on disk.

Speaker 1: So let's see what happens when the values are not sorted on the disk. Let's say that the same values, but this time we shuffle them around. Okay. So once again we create uh ranges of three adjacent blocks and create the bin index. So we have two through nine, one through seven, three through eight And now once again we want to search for the value of 5. So this time the index is completely useless because 5 can be in any one of these ranges. So the index is not helping us at all in minimizing the number of blocks that we need to read in order to fulfill the query. Okay? So this is our brain index work. And if we want to create a brain index in Django, once again we go to Contrae Postgres Indexes and import the brain

Speaker 1: index. We use it in the index clause of class Meta and we index the creation date and we give this a name. I'm going to mention this parameter in the end. Okay So generate and apply migration and issue an execution plan, and we can see that Postgres used our index. Okay? So this is the brin index that we just created. Your the keen eye uh reader might see that uh the execution time is now slightly uh slightly slower. Okay. But that wasn't the point, right? Because we were not trying to necessarily optimize for microseconds optimization.

Speaker 1: We wanted to solve a problem of a big index. So what is the size of the B tree of the brain index? The size of a brain index is 248 kilobytes. So just as a reminder, the B3 index was 21 megabytes and the BRIN index is 99% smaller. So I'm willing to pay this price, like add another millisecond to every query to save uh so much space. So I'm not going to talk about pages per range because I don't have much time. We can talk about this in the Q<unk>A if you really want to. I'm seeing David like popping up on my screen saying, dude, you need to get off. Okay. I'm I'm going to.

Speaker 1: One minute. One minute.

Speaker 2: Okay.

Speaker 1: And so brain index is ideal when you have data that is naturally sorted on disks. You can find that with this column in the Postgres Information Scheme And this is it. This is like my last slide. So when to use uh indexes. Um Indexes make queries faster, but they also make inserts and updates slower. Okay, and they can also take up a lot of space. So My uh my recommendation to you, bottom line, is just don't overdo it. Okay, just use them wisely. You now know many, many types of indexes. And you can use them wisely

Speaker 1: to create an indexes that make things faster but in a reasonable way. So just to wrap up my part of the presentation here, I was Aki Benita. I write a lot about Python, Django, PostgreSQL in my blog. I'm also pretty active on Twitter, you can look me up, and I send stuff to subscribe of my newsletter every month. And if you have any questions, comments, insights, uh you can do so in the chat or you can send me an email. This is my email address. So uh this is David you want to take over?

Speaker 2: Yes, uh and uh please please uh um to the um face -to-face meeting uh which you can find uh in the link below and uh ask anything else with uh you want to know with Aki Benita we don't have more time uh it was a great presentation and uh Adam to the face to face meeting. Thank you all.

Speaker 1: And of course, thank you to the organizers of DjangoCon. Wow, a lot of people here. How's everybody doing?

Speaker 3: Fine, thank you.

Speaker 1: Who am I talking with? I'm new to GT.

Speaker 3: Yeah I like I like the talk. There's a topic that we often forget to to think about it about indices and we only know it when our app is crashing in production. So

Speaker 1: Okay, did did you find out about a new type of index that you did not know before?

Speaker 3: Yes, yes, the uh inclusive ind dishes I was not really aware of then So uh yeah, I learned certainly something new. Um

Speaker 1: it's probably a good thing that you're not familiar with inclusive indexes. Ninety-nine percent of the time they are used incorrectly.

Speaker 3: Okay. Just a question, like in in in Jang uh in your examples when you create the end dishes in Django you always provide a name yourself. Can Django also do that automatically? For us or

Speaker 1: um I if I remember correctly there are some types of index that you have to provide a name for. So um yeah, I think that Django will will uh warn you if you're not adding a name, so don't worry about it. But as a general rule I think that um when you can you should provide a name because at the end of the day you're going to see the index name in execution plans and maybe logs and traces. And you want to be able to identify a Django is using some hash value, you know, not always recognizable.

Speaker 3: All right, thank you.

Speaker 1: Cool. Anybody else want to tell me what type of index he likes best? Wow Jitsi is cool. I never use Jitsi. Anybody is using Oracle here? Or everybody is using Postgres.

Speaker 4: Hello. Hi Haki.

Speaker 1: Hi.

Speaker 4: I have a question about the uh B tree itself. Um Can you please uh briefly explain uh why uh B3 is uh somehow um uh you know used in uh Postgres? Because even it's a I think it's a sequential service. or somehow uh there is uh more uh optimization over B tree that uh Prosgres uses. That's why it's a very uh convenient to use. Uh can you please explain something about it

Speaker 1: Um well B tree index, let's let's go backwards. I think that if you you gather like ten developers in a room and ask them to create an index I I'm pretty sure that most of them will come up with a B tree index or something that looks like a B tree index or have a tree structure So I think that uh B tree uh satisfies the most general cases that most developers uh encounter. I also think that Breinde index is most of the time the most suitable index for the task. As you've seen with hash index, they have many restrictions and they can all and they're very um not ideal for any type of use case

Speaker 1: So you have a better chance of getting better performance with a B tree than any other type of index Uh this is why I think that presentations like this one that educates developers about different types of indexes and how to uh like squeeze more performance and reduce the size of B3 indexes is so important. So to answer your question, I think that B tree is usually the most beneficial and I think it's most popular because it's the default.

Speaker 4: Mm-hmm. Okay. Yeah, thank you. Oh, one more one more follow-up question on that. So um uh because uh in the in the slides we have one uh particular scenario to uh explain about the uh performance, but in usually the large applications there could be uh multiple function call or multiple uh query in um execution right So on that case, I think uh using the particular um e small debugging like the explain uh won't be sufficient enough. What sort of tools you usually uh use uh for that?

Speaker 1: Okay, so if I understand the question correctly, you ask what type of tools I'm using to monitor um the performance of my SQL.

Speaker 4: Yeah.

Speaker 1: That's a great question. So I in Postgres I use statement timeout and auto-explain to log the execution plans of long-running queries

Speaker 4: Mm-hmm. Okay.

Speaker 1: And I also make a habit of setting a timeout for queries that Django executes. Usually it's a very low uh timeout. This is something that you set at the connection level or if you're using some connection pool like PG Bouncer. So for me, the timeout, the default timeout is uh 10 seconds So any query that cannot be completed in 10 seconds will be terminated by the database and I will get an alert. Unless of course I explicitly uh allow the query to execute for more than 10 seconds. I think that this is a good habit to have along with logging and statement timeout and auto-explain.

Speaker 1: You can have a pretty comprehensive look at the performance of your SQL queries.

Speaker 4: Okay. Thank you very much for your answer.

Speaker 1: Cool, glad to help. Any other questions about anything? Life

Speaker 5: Hey. Um so I I I'd made a question there on the on the other platform, but I'll do it here again, it's easier. So basically y you know the last index about the dates, the creation dates is basically used for the assuming everything is sorted. uh the question was if uh there is a way to after the fact run uh uh some type of script or anything that would force that uh those dates to actually be sorted because I have had an issue in the past where s most of the date would be using the creation date but some data was synced afterwards uh by any reasons and so the it was inserted and uh the table itself would not be certed on this

Speaker 5: mostly but not everything and if I wanted to have 100% reliability that I would get all the data. Would there be a way to actually sort it even if it was in another task? Uh eventually, periodically, and yeah.

Speaker 1: Yes, yes, there is. Um there's a command in PostgreSQL called cluster You can cluster a table uh according to a specific index and what the database would do is it will rebuild the entire table. in a way that the correlation of the fields that are indexed will be uh optimized, will be higher Okay, so uh you have cluster command which you can use but you need to be aware that this is a blocking command. So if you run this command on a table, this uh table would not be available for anything else.

Speaker 5: And if it's a big table.

Speaker 1: There's also PG Repack and PG Reorg that you should look into. I usually use PG Repack to um uh clear out bloat but maybe you can find a way to cluster the table based on an index using pg repack uh without blocking Okay.

Speaker 5: Yeah, thank you.

Speaker 1: That's a good question. Yeah, that's a good question. The fact that you even know that this is your problem, it's uh

Speaker 5: Yeah.

Speaker 1: It's good. Yeah. Yeah, that that's a When you see a lot of uh bitmap scans on indexes, you know the the in the the database assumes that the column has very poor correlation and you see instead of an index scan you see an index bitmap scan So that's usually a good indication that you should worry about the correlation.

Speaker 5: Mm-hmm.

Speaker 1: Okay. Junior developers, man. Not knocking. Okay, anybody else want to say something? I appreciate any comments you might have on the on the presentation if you learned um found out about new types of indexes. Maybe uh learn something new.

Speaker 5: Yeah, this will definitely be a reference talk for me to visit in the in the future where I I know there might be an ex and I usually Google Django Con talks and skim through the part where I remember. Something related. So that will definitely happen with this talk in the future.

Speaker 1: I'm glad I'm glad I could help, man. That's great news. Thank you So uh anybody else wanna say something before we pack up and move on to the chat Okay. Thank you all very much. It was great being here.

Speaker 3: Thank you. It was a great talk.

Speaker 1: Thank you all for viewing. Thank you. Bye.