Databases, Django APIs, and AI - Haki Benita
Published November 8, 2026
This video features Haki Benita at DjangoCon Europe 2026 in Athens, Greece.
WORKSHOP: Django from the trenches: Advanced Indexing and Concurrency in Django and PostgreSQL by Haki Benita
Building applications to serve actual users is really hard! Traffic spike, data accumulates, queries becomes slow, response time suffer and you sre left completely baffled! In this workshop we'll optimize real-life scenarios in a Django application using advanced indexing techniques in PostgreSQL. We will also identify and tackle concurrency issues and experiment with different approaches to prevent them, without bringing the system to a halt. By the end of this workshop you'll learn how to prepare your Django application for the real life.
More information: https://2026.djangocon.eu/speakers/8QRVC3/
Follow DjangoCon Europe on social media:
*X - https://x.com/DjangoConEurope/
*LinkedIn - https://www.linkedin.com/company/djangocon-europe/
*Mastodon - https://fosstodon.org/@djangoconeurope
#django #djangocon #djangoconeurope #djangoconeurope2026
Automatically transcribed, so expect mistakes in names and technical terms.
Speaker 1: Hope you had a good time with the keynote with the carton. So our next topic is We all know the Django Postgres is a backbone for uh the the Postgres SQL is uh most Django application backbone, right? And knowing how to add the indexes how to avoid data logs and knowing what happened to the database under the load. That makes the good application great, right? So our next speaker, Haki Panita, is a software consultant as well, tech technical lead. And he's well known in the community. He writes a lot about the database performance and the uh optimizing database query. So I'll hand over to Haki
Speaker 1: Banat. He's talking, gonna take us through the trenches of the databases. Hi.
Speaker 2: Thank you. Hi everyone. Glad to see so many people who doesn't care about security. Uh so uh I'm working with uh Django and Postgres for uh I think ten years maybe. maybe even more. And during that time, I've built together with my team several successful products that serve millions of users. uh active paying users every month. And during that time I also got to experience the full life cycle of development starting from building something cool and see it break down in production. See my abstractions uh you know fall apart, rebuild, refactor, optimize, and um
Speaker 2: in the next 90 minutes We are going to talk about some of the takeaways from our journey. So today we are going to focus on two things First, we are going to talk about indexes, and we are going to talk about some lesser-known features and types of indexes. Okay? And we are going to talk on how they work and when you should use them. This is the first part. The second part of the talk, we are going to talk about concurrency How we can handle concurrency, identify concurrency, okay, and hopefully tame it with minimal impact on our application. So we are going to build together
Speaker 2: a URL Shortener. Okay? I'm sure that many of you are using a URL Schortener. It's a very simple system. That takes a very long URL and provides a very short URL that redirects To that log URL. The reason you would want to use such a system is if you want to give links in a text-constrained environment like SMS or tweets. Then you want to take this big piece of text, make it short, and whenever we redirect from the show to the long URL, we keep track of the number of hits. So if you have campaigns and you want to keep track of how many people click
Speaker 2: the link, you would use this type of system. So we provide a short URL that redirects to a long URL and we keep track of the number of hits So to build this type of system in Django we can start with a very simple model. We call it ShowTurl. And we have ID, this is our primary key. We have a key field. The key field is a short piece of text at the end of the URL that uniquely identifies Our entry. Okay. You can also see at the top that I have a unique constraint on it to make sure that it's unique. I can't have two short URLs with the same key. Then we have the URL, which is the actual URL we want to redirect to.
Speaker 2: We keep track of when we created the short URL and finally we keep track of the number of hits, how many times we redirected. So I loaded a bunch of objects into this model, and this is the type of system that we are going to work on from now on. So We are going to present in Django, but at the end of the day, it's about SQL and databases. So let's see how we can actually get the SQL that Django is executing when we are interacting with it. So the first option is you take a query set like this for example and you use the query object and you just print it.
Speaker 2: This gets you the query that Django is going to execute when you actually evaluate this query set. Sometimes it's useful to not only get the query but also get the execution plan. In other words, how the database is planning to execute our query This is called an execution plan. In this case, we want to find the short URL by the key. So the database would use an index scan an index scan on the key. Okay, on the index that we have on the key. The thing about execution plan and the explain command is that it's not actually executing our query. It just goes to the database, provides the query, and said, and says, How are you going to execute this query?
Speaker 2: And we get the execution plan back Sometimes it's also useful to produce the execution plan but also execute the query. We can do that by using explain analyze When we do explain analyze, the database would execute the query, return the execution plan along with some additional information. So you can see at the bottom You can see at the bottom we have the execution time, how long it actually took to execute our query. Okay, you can also see the planning time, how much time it took the database, Postgres in this case, to come up with this execution plan. And it auto also contains uh
Speaker 2: a very interesting piece of information, uh which is how many rows did the database expect to get versus how many rows it actually got when it execute our query. So we are going to use explain analyze throughout this workshop to explore investigate how the database is actually executing our queries. Another useful way of viewing the underlying SQL executed by the ORM is To turn on the logger for the database backend. When you do that, the database Django, I'm sorry, Django would print out all of the SQL that it executes in the database. So let's start actually implementing something.
Speaker 2: Okay, so the first thing we want to implement is look up by key. Remember, every time someone hits a show TRL, we need to go to the database We need to find the URL that we want to redirect to and redirect to that URL. So we can start with a very, very simple function resolve we get a key and we return the URL very simple we can see that we just uh short url object get by key. If we find something, we return the URL. If we don't find anything, we return none. Okay, now if we want to see what the database is actually executing, we have our SQL logging turned on, and this is what the database is executing.
Speaker 2: We can run explain on it and see how the database is planning to execute this query. In this case, the database is going to use the index that we have on the key. Okay? So let's pause for a second and talk about what this index is. Okay? Show of hands. Who's using B tree indexes? Right. Who's using other types of indexes? Who's not sure? Fair enough. Fair enough. So let's talk about B tree index. This is the king of all indexes, what you normally want to use, what you're using if you're not sure what you're using. This is a B tree index. As the name suggests, it has a tree-like structure. You have the root node, which acts as a directory to the leaf blocks.
Speaker 2: What's interesting about the B tree index is that the leaf blocks Store the actual indexed values and they are sorted from small to big. So we can see that if we have these values one through nine and we want to index them using a B tree index All the values are stored in leaf blocks, sorted from small to large, and then the root block acts like a directory into this leaf blocks. So let's try to look for value 5 in our B tree. We start by scanning the root block from left to right. We scan the root block, we read it from disk. Usually it's stored in memory, but we start from the root block, we work our way from left to right. So is five less than three? No. Going on to the next entry.
Speaker 2: Is five between three and seven? The answer is yes. We follow the link. We read the next leaf block, the middle one. And then we start scanning from left to right. We encounter four. This is not a match. Next value, five, this is a match. At this point we can follow the link. To the block in the table. Now let me ask you a question. I scanned through the B tree index and I found the value 5. Which is what I was looking for. I followed the link, I found a row. Should I keep going? Yes, no. Should I keep going? I see some no's, I see some yes. It depends. Dude, that their answer to every programming question. It depends. Depends on what?
Speaker 2: Yes? So in our case are two point. So in our case if this is a unique index, we don't need to look further because there's there can only be one, at most one. But if it's not a unique index There's situations where I need to keep looking. Okay, so let's go back to our query. Okay, we now know that the database is doing an index scan and we know that we have the key, we want the URL. Do we really need all the other fields? No, we don't do anything with them. We read them from the disk. We serialize them in Django. We do all this work, but we don't actually need any of these fields So we can try and do a very small optimization
Speaker 2: and since we know that we only need the URL instead of fetching the entire object, reading all the data from the database and then instantiating a short URL object. In Django, we can just go and fetch the URL. We do that by using values list URL. This tells the database that we only want the URL. And we use flat true to just return the value instead of you know tuple. So this is our new result function. Now we get the key, we fetch just URL. If we don't find anything, we return none We look at the query and we can see that it looks it looks more efficient, right? Smaller, we fetch only what we actually need, just the URL.
Speaker 2: Now if we produce an execution plan, we can see that Postgres is using our index, right? So in this specific query, how many blocks do I need to read from disk in order to fulfill this query? First, I need to scan the root block. That's one, right? Then I need to read the leaf block. That's two. Then I need to follow the link. to the table and do a hip fetch. That is read a block from the table. So that's three. So to get the URL using an index, I need to read three blocks.
Speaker 2: Okay? Three blocks. Now, let's take a step back and think about it for a second. This query is executed a lot. This is basically the bread and butter of our application. This is what it does. It takes keys, resolves them to URLs. This is happening a lot. We already know that we only need the URL from the table. We don't care about everything else, just the URL. So what if Okay, what if we can just include the URL right there in the leaf block next to the key? You get what I'm saying?
Speaker 2: Right? Mind blown. Take the URL. Instead of going to the table, just put it right next to the key in the index. So this is called an inclusive index, also known as covering index, and inclusive indexes allows us to store additional information in the leaf blocks. So in Django, if we want to include the URL in the leaf block of our unique index We can add this include URL. And now we have an index on the key and in the leaf blocks right next to the key we also store the URL If we create this index
Speaker 2: and then we produce our execution plan, we can see that now slightly different. Can anybody spot the difference? This is a workshop, you can talk. What's the difference? Only previously we had an index scan. Now we have an index-only scan. An index only scan means That the database was able to fulfill our query, answer our query without accessing the database. So how many blocks do we need to read now? Two. Why two? Root? Leaf. Do I need to go to the table? No. I have the data right there.
Speaker 2: So, quick recap about inclusive indexes. So they are also known as covering indexes. So if you want to look up information about inclusive indexes, this is also a term that's being used. They're useful when you need to uh when you want to be able to answer queries without accessing uh the table. It's mostly useful when you have very very wide tables like data warehouse-like tables where the row can be very very wide and you need a very small portion of the uh columns in the table. Now I speak with a lot of enthusiasm about this, but I'm actually not a big fan of inclusive indexes because they have a major disadvantage.
Speaker 2: What's the disadvantage of inclusive indexes? Gets very large. Why? Be We basically took the URL and now we store it twice. Once in the table and another time in the index. This is very wasteful. This makes the index very big and URLs can be very big, right? So I personally found that good candidates for inclusive indexes is when you have Non -unique composite indexes. Okay? When you have non-unique indexes on more than one field. And I'll explain why. When you have composite unique indexes, they are usually there to enforce some kind of constraint.
Speaker 2: So we keep them. If you have a non-unique composite index, then this index must be for performance, right? So in some situation, if the selectivity for the prefixed columns in the composite index is Low enough, then you might be able to uh get away with an inclusive index instead of a composite index. I know this was a lot of big words, and if you want we can get back to that at the end. Let's do another one. Okay? This time we have a fancy dashboard and we want to find all the URLs by domain, right? I'm Haki, I'm using this service and I want to find all the short URLs that point to my domain, akibanita.
Speaker 2: com. Right? This is what I want to do. So we can implement this function. Find by domain. Simple function. We accept a domain and we return a query set of short URLs. Okay? To extract the domain part from the URL, we use a regular expression. You can see the expression right there. Now before before I move on to the actual implementation, I want to do a quick uh side note. If you notice, I'm using alias Anybody knows about alias? Yeah? Okay. So there's two ways to do it. One way is to use annotate
Speaker 2: and one way is to use an alias. Anybody knows the difference between annotate and alias? You wanna answer? Yeah, ls uh it will not be in the result of the Exactly. So alias allows us to give a name to this expression, but it's not going to be included in the output. I can use it inside the query set, but it's not going to be included. If you see the actual queries, you'll see that on the left you have annotate and you see substring happening twice. On the right, yeah, the right one we're using alias.
Speaker 2: So we can filter on it, but it's not included in the output. Which one do we want, by the way? Which one do we want? Do we want alias or annotate? We want alias, right? Because we don't need the domain in the output. We just want to search by it. So we're using alias. Okay. So we want to um find by domain and we now have this nice query. We can produce an execution plan. And we can see that the database is now doing a sequential scan. We don't have an index, so the database is just Doing a sequential scan, scanning through the table, reading each and every block, parsing the row, applies the regular expression, looks to see if it's a match, and returns the row.
Speaker 2: And we can also see that it takes about three seconds to scan through the entire table and produce results for this query. So once again, let's take a step back and think about what we know about this query. First we know that this is a very useful query. Our users asked for it, otherwise they wouldn't have done it. Right? It's right there in the dashboard. We want to use it. Now every time, every time someone looks or uses this query, I need to go over the entire table, right? And I need to evaluate this regular expression to extract the domain part from the URL.
Speaker 2: Now if you've worked with regular expressions, you know that they can be very slow So every time someone asks for this, I need to re-evaluate this regular expression. So now Bear with me. What if, okay, what if we can just index the result of this expression? What if we can index the result of this expression? This way, instead of having to recalculate this regular expression every time we try to access the table, we just have the results right there in the index. Okay, so this is what it looks like in Django. We create an index
Speaker 2: and instead of providing a list of columns we provide an expression. Now we use the exact same expression. That we use in our query set. Okay. One second Okay. So we produce an execution plan. And we can see that the database is now using our index. You can see a bitmap index scan using short URL domain, FIX functional index, function-based index. We can see that the query was significantly faster. Previously, took around three seconds, now
Speaker 2: just 50 milliseconds. That's 60 times faster. And the reason it's so much faster is that We don't need to re-evaluate the expression again and again. We just have the results right there in the leaf block. The thing about function-based indexes is In order for the database to be able to use the function-based index, you need to make sure that you use the exact Same expression that you used in the index. If some of you here think that the database is somehow smart enough to understand that two expressions are equivalent, Note that the database is not that smart. You need to use the exact same expression. In this example, I'm changing the expression very slightly and we can see that the database is unable to use our
Speaker 2: function-based index. So, quick recap about function-based indexes. So function-based indexes It allows us to index the result of an expression, making it unnecessary to re-evaluate this expression over and over again. Function-based indexes also have the coolest acronym in the index world, FBI. Remember that. Function-based indexes. So uh a fuck a function-based index is very useful for optimizing queries. But once again, I present function-based indexes and there are lots of valid use cases for function-based indexes, but once again, I'm not a fan.
Speaker 2: And I will tell you why. If I presented you with this problem, would you solve it like this? No, right? What would you have done? You would just add a field, right? You would just add a domain field and when someone creates show TRL, you would extract the domain once, you put it in a field, and that's it. In recent versions of Postgres, you can have even have a generated column which automatically populates the field for you. Right? So why bother with a function-based index? So I actually asked this question once and I got a very interesting uh response. A lot of the features That we see in databases
Speaker 2: are very old. They go way back. And way back in the day, people used to host closed source Application software on their servers. You would be a big enterprise. You would purchase an Oracle license You put it on your rack in your basement. You had all these IT people, right? And you bought this software, you host it yourself. And then at some point a DBA would notice that the application is making a lot of queries using some expression and it's scanning the entire table. Now this IT guy, he can't He can change the software. This is closed source. So these types of features allows him or her
Speaker 2: to optimize without touching the code. Okay If you're interested, there are several uh useful use cases for function-based index. uh which are not the one I just showed and you can check them out here. Okay? Advanced stuff. You should check it out. So Let's do another one, another use case. This time we want to find all the unused keys. These are all the short URLs. That no one ever used, where hits are zero. Okay?
Speaker 2: So we have this function, find unused keys Very simple, we return a query set of SHORT URLs and we just select all the objects where hits equals zero. We look at the query, select star from Short -Yor where it hits equal zero. We produce an execution plan. What is the database doing? That's right. Sequential scan on the entire table. I promise you, by the end of this workshop, you're going to come back, you're going to run explain, and they're actually going to make sense to you. So the database is scanning the entire table looking for
Speaker 2: rows where hits equal zero, right? So you can do that. It takes like a hundred milliseconds. At some point some PM would come to you and say, dude, that's super slow. Can you fix that? I can't spare a hundred milliseconds. I need to be super fast. And you say, sure man, no problem. And what you do, you slap a B tree on it. You just go to the field and you say db index true. You apply the migration, you now have an index on it. Right? The PM goes to the dashboard, runs the query, and very fast, right? Very fast. So are we done? Query using the index. We're done.
Speaker 2: Yeah. Yeah, not really, because you're going to have so many different kits. So your index would be one. Quite big, right? Yeah? You kind of spoiled my uh thing here, but okay. Never mind. It's a leading question. Okay. So let's let's talk about performance. So far we we put a lot of effort into making things run faster. But in fact If we want to talk about performance in a broader sense, then it's not just making things go very fast. We need to balance different types of resources. For example, if you have a batch
Speaker 2: job running at night, it takes 10 minutes and it's just running every night when the system is mostly idle. That's fine. Right? You wouldn't, for example, try to optimize it to run in just I don't know two seconds and have it consume lots of CPU or memory. Which might cause you cost you money. You say that's fine. I'm trying to optimize for cost of this in this case. Or if you have a task running during the day in the background that consumes lots of CPU and it hurts other concurrent sessions in your database, then you might say, I'm willing to have it run for a bit longer, but I don't want it to consume as much CPU.
Speaker 2: So you need to optimize for different things. So the resources that we normally consider are CPU, memory. Disk space, right? And this all translates to cost because if I need a process that consumes lots of disk space, then I need to pay for it. If I have my server set to auto-scaling and I have a very heavy uh task that consumes lots of extra memory, I'm paying for it. That's bad, right? So let's look at the size of our index. We use slash DI in Postgres, DI plus. And we do a wildcard search and we see
Speaker 2: that our index is seven megabytes. Okay? Seven megabytes. Now, as you already said , our our index includes All the rows in the table, right? All the rows in the table, all hits. But we don't actually care. About all hits. Nobody asked us how many short URLs you have with more than a thousand or less than five or between thirty and seventy. They just said we want to find unused indexes where hits equal zero. So what if, okay, once again, what if
Speaker 2: we can just index A portion of the table, the part of the table that we actually care about. Instead of indexing everything, we just index this small portion that we care about. This is where a type of B tree index that I actually like, okay, becomes useful. This is called partial indexes and it allows us To index just a part of the table. So first thing we need to ditch this dB index through. We do not want the full index. Instead, we want to create a partial index So we create an index on the ID field and notice that we add a condition.
Speaker 2: You see this condition q hits equals zero. If you look at the actual SQL that is generated by the migration, you'll see that we are creating an index on hits, and notice the where clause at the end. You see? We are indexing hits where hits equal zero. Okay? This makes it a partial index. Now, if we execute our function again and producing the execution plan We can see that the database is using our partial index, right? And we can also see that the execution is very fast.
Speaker 2: faster than when we started and even faster than the the small index the the full index but that's not all If we go back to the database and we look at the actual size of the index, we see that the index now is significantly smaller. Previously the full index was seven megabytes. This one is just 88 kilobytes. Now at this point people usually tell me dude come on it's like six megabytes. Here take like 50 cents go buy some storage, right? But the thing is this is just a demo so naturally everything is much smaller. If you go back to your databases and check the sizes of the B tree indexes, you'll find that they can get very, very, very big.
Speaker 2: And big indexes cost more money, cost more maintenance. They are harder to move around, interact with. So partial index can definitely be useful in situations like this. Yeah.
Speaker 3: So what if I do like a where it's smaller than one? Would it also
Speaker 2: More than one? You mean less than one? I would guess uh no. But check me on that. I think so, yeah. I don't think it's smart enough to evaluate this expression, but you know what, maybe it is. I wouldn't do it unless I really have to.
Speaker 3: So the condition is the condition going to be evaluated every right? Yeah. Is there a way where you could say well once it's it's screened out of the index won't put it back in because in this case like if we've hit it once it's never going to go back to the
Speaker 2: Oh, that's pretty advanced. I don't know if you can communicate that to the database. So first of all, the first part of the question, do the is the database need to evaluate this uh ex condition? Whenever uh it adds a new row to the table, the answer is yes, because it needs to figure out if it needs to go into the index or not. When you update a row, This is essentially delete an insert, so this expression is going to be re-evaluated and depending on the result, might delete the row from the index. Now whether I can uh add you know special conditions to uh let the database know that once it's out, never back in, I don't think so. I don't think you can even express that as a constraint. But interesting question. So
Speaker 2: partial indexes. Super cool. Super useful. Use them whenever possible. Seriously. Good candidates that I found, especially in a in Django, is when you have a nullable nullable column with an index on it, usually you don't want the nulls to be included in the index. But in Postgres they are. So if you have notable columns, notable fields with an index on them, you might benefit from A partial index. In Django specifically, every time you use a foreign
Speaker 2: key, Django would create a B tree for you implicitly, even if you don't say anything It will go and create an index for you. So if you have a nullable foreign key and you didn't set db index to false, Then you have a full index on this column. What you normally want to do in this case, assuming that you don't care about nulls, is you want to set db index to false and create a partial index on the foreign key Okay, we actually managed to save a lot of storage this way by simply identifying columns which were nullable and had indexes on them. Okay Okay.
Speaker 2: Now let's do questions so far by the way? No? Yeah?
Speaker 3: On the performance side of the partial index with your example it probably will help prevent some uh tree rebalancing because like if it if you stole the it Every time you increment you might have to rebalance the tree.
Speaker 2: Yeah.
Speaker 3: But when it 's
Speaker 2: first of all, it depends on what I'm indexing. In this case, I index the ID. So I expect good correlation and uh and yeah the index is smaller because it contains just a small portion of uh the the small portion of the table. So naturally it would be easier to maintain.
Speaker 3: Yeah. Can we hope to get similar performance by doing what you just said? So for example, instead of zero, I store no for X. and I only index the ones that have hits for some reason, but not this specific kit. Would I be able to use that uh to use that index if I say that I only want the no values that are not no. Would would that s use the index
Speaker 2: The question is if I have a partial index on not null and I have a query that looks for everything that's not null that is null, like the negate, the opposite. I wanna hope that yes, but I I think that no. Just like the function based indexes, you need to use the same expression. Yeah, this is by the way a big challenge and uh the article I I showed you before tries to um address this challenge by using generated columns. to eliminate situations where you have uh difference small differences in the expression that causes the database to not being able to use the index. Yeah.
Speaker 2: Well uh you can index a Boolean. I have to say that because I read So there's no point you're using your basic space. I'll tell you what, I have an example at the end using a haystack. You're going to like this analogy. And if it doesn't answer your question, then ask again. Okay? So next scenario we want to do a reverse lookup. Reverse lookup is we want to find the URL We want to find the keys by the URL. Previously we wanted to resolve. We wanted to take the key, find the URL to redirect to.
Speaker 2: Now we want to do the opposite. We have a URL, we want to find all the keys For example, I launched this promotion for some landing page on my website. I created different short URLs for different platforms, one for SMS campaign, one for email, one for social. And I want to find all the short URLs pointing to this URL. So this is what the function looks like. Show it URL, uh objects filter by URL, very simple, nothing complicated. We can See what the database is doing behind the scenes basically select star from show to URL where URL equals something. Once again, we go through the motions. We produce an execution plan. What is the database doing?
Speaker 2: Full table scan. Why? No index, right. Full table scan, no index. And we can see that the timing is roughly 100 milliseconds. So someone complains, dude, that's super slow. Can you make it faster? So what do you do? You slap a B tree on it, right? And you go for lunch. So you go, you add, no problem, let me fix this for you. You go, you add an index on the URL, right? You generate migrations, apply them, everything. You tell them come come let me show you something. You produce the execution plan again. What do we see? It's fast. And the index is used. We went down from 100 milliseconds down to less than almost a tenth of a millisecond.
Speaker 2: That's a pretty big improvement. I think we earned our lunch, right? So let's once again look at the size of our index. So our index on the URL is 47 megabytes. Okay, that's uh that's big. That's the biggest we've seen so far. Right? It's actually almost half the size of the table. By the way, why is it so big? The URL is big. And what does the B tree do? Stores the value in the leaf block, right? So we just index the URL, very big, stored twice. We talked about it previously when we talked about inclusive indexes.
Speaker 2: Now we have an actual index on it, very big. So what do we know about URLs in our case? First of all, we know that unlike key, the URL is not unique. Okay, we can have multiple short URLs pointing to the same URL. However When we look at the data, we see that there are very few duplicates. Just because the nature of the data is Even if I have multiple short URLs for the same landing page, I most likely have UTM parameters and stuff like that. So URLs usually won't be exactly the same
Speaker 2: So I don't have a lot of duplicates. So it's not unique, but it's almost unique. So I have very few duplicate values. So now I want to present to you Okay, I want to present to you an innovative new type of index. I call it the ugly duckling of under of indexes. This is like what you would do if I had asked you to implement an index, yet nobody does it. Okay? And of course I'm talking about hash index. Makes perfect sense, yet nobody uses it. So let's talk about a hash index. Let's start by understanding how a hash index works. So to understand how the index works, let's build one. Okay?
Speaker 2: So we have these values a, b, c, and d, and we want to build a hash index on it So the first thing that we do is we apply a hash function on the value and we get the hash value. You can see that Postgres has these hash values for different types. You have hash for text, char, arrays, even for JSON and timestamps. Okay? So now we have our hash values and now we need to divide them into buckets. So in this case We want to divide into two buckets. So essentially we do modulo two. And we can see that A goes into bucket one. B, C, and D goes to bucket zero, right?
Speaker 2: Because these one is odd, six, eight, and zero are even. So we have two buckets, and this is our hash index. Amazing, right? Let's try to use this hash index to find something in the table Let's use this index to try and find the value B. So we go through the same process. We apply a hash function on B, we get the hash value. From the hash value We identify the bucket, in this case zero. So we go to the index and we start reading the values in bucket B. And now I found A value with a matching hash value. So now I can go to the table at position two, read from the table, and use
Speaker 2: my result. Now question. If I go to the table, position two, am I necessarily going to find a row with the value B? I'll ask again. I'll ask again. I found a matching hash value in my hash index It points to block number two in the table. I go to block number two in the table, I read the row. Am I necessarily going to find the value B? No, why not? Collision. We can have multiple values with the same hash value. So
Speaker 2: we are not necessarily going to find a value, but we might. Okay? So let's go back to our model, remove this B tree index, and use a hash index instead. Okay, notice that hash index is a Postgres specific type of index. So we need to import that from Django Contrab Postgres indexes. And we define the hash index on the URL. Personally, I like to use suffix for index names that indicates the type. So I use HIX. This is not Aki index, this is hash index. Okay, so we
Speaker 2: generate the migration, we create the index, we produce an execution plan, and we can see that Postgres Use our hash index. And another thing that we can see is that the index, the the query was actually faster than the B tree. So the hash in this case was faster than a B tree. But that's not all. If we look at the actual size of the index. We can see that the B tree on the URL is 47 megabytes and the hash is just 32. So in our case , In our case, the hash index is
Speaker 2: smaller and faster than a B tree index. So I did an experiment. I'm going to show you a scary chart. We're going to work through it. Don't worry. So I did an experiment. Okay? And this is what I did. We have the key Which is a short unique text and we have the URL non-unique, very big, right Then I created a B tree index and a hash index on each of them. So four indexes. Okay? Then I started inserting rows into the table.
Speaker 2: This is what you see in this axis below. That's I'm starting to insert row into the table. Now While I was inserting the rows, I took snapshots of the size of each index. Okay? So let's see some interesting things. First of all, if we look at the green and the red one, these are our B tree indexes The first thing that you see is the index grows linearly. As I add more rows to the table, to the index, the size of the index grows linearly. That makes perfect sense Another thing that you can see is that
Speaker 2: the size of the B tree on the URL is constantly bigger than the B tree on the key. Why is that? Yeah? URL is bigger. In other words, we now understand that the B tree index, the size of the B tree index is affected by the size of the values in the index because the values are actually stored in the leaf blocks. So if I index big values, I'm going to get a big index. Now, if we point our attention to the hash index, these are the two joined lines here, the yellow and the blue one. The first thing that we see is As we add more rows to the table, the size of the hash index on the key and on the URL is the same.
Speaker 2: Why is that? Yeah. Exactly. Hash index does not store the actual values. It stores hash values. And the hash values are the same size. So in other words, we learn that hash index, unlike B tree, is not affected by the size of the indexed values. So I can index a huge piece of text. And I'm going to pay the same as if it were just a single digit. Okay? There's another interesting thing that goes on. We saw that the B tree is growing linearly, but we see that the hash index has
Speaker 2: like these bumps, like these steps. You see these steps? Like Step. Step. What is these steps? What are these steps? New bucket. What are these steps? New data. New data? Okay. New rows. But why steps? Why bumps? Okay, tell us the answer. It's the buckets. It's the buckets. Okay. So a hash index allocate space for buckets. When the buckets When the bucket fails, we need to do a bucket split. When we split a bucket, we allocate size the same size of the existing bucket. And then we can start insert rows until this bucket fills up and then we have another split.
Speaker 2: This is why you see the hash index grows in bumps. Every bump is a bucket split. Okay. So attacking the one before that one working design is separate. So the nice different I think I think these are different values, so it it makes sense that maybe why is it interesting? Yeah, you can see this here as well. I'm guessing lots of splits, small splits. My guess. I I this is my educated guess. So hash index is ideal when we have very few duplicates. Why is that
Speaker 2: Can I use a hash index? Does it make sense to use a hash index on rows that have lots of duplicates? If I have a lot of values which repeat themselves, does it make sense to use a hash index? No, why not? Exactly. We have lots of collisions, then we have an uneven hash because we have one bucket that starts to fill up and the others are empty. So we need to have Lots of distinct values, lots of unique values, not lot of repetition. We also saw that unlike a B tree index, hash index is not affected by the size of the indexed values We also saw that it can be smaller and faster than a B tree index under some circumstances.
Speaker 2: And the reason I called it the ugly duckling of index types in Postgres Is that if you go to Postgres documentation prior to version 10 and you go to the page for hash index, you'll see this huge warning saying do not use this, this is not production ready. So a lot of people got used. To the idea that hash index is not production ready. But starting at Postgres 10, it is ready and it's very, very useful. Okay? So hash index has a few limitations. First of all, you can create a unique hash index. You can create a unique hash index.
Speaker 2: And you can create a composite hash index. That is, you can create a hash index on more than one field. You also can the database can't use hash index for sorting and it can't use a hash index for range scans. Like between. Why is that? Because the hash can't search. Exactly. It doesn't store the values, so it's useless in these cases. There is, however One trick that you can do. I said that you can't create a unique hash index. That's not possible. If you try to do that, you get an arrow. However, and this is Very cool.
Speaker 2: You can still enforce uniqueness using a hash index. So This is actually my contribution to Django 6. 1, the ability to create exclusion constraint with hash. So shameless plug, that's me. Okay, so if you want to enforce uniqueness with a hash, you can actually use an exclusion constraint. And I think that it's easier to look at the SQL. So an exclusion constraint, okay, is basically every time you insert a row into the table. It evaluates this expression, URL with equals.
Speaker 2: And if it matches, there's a constraint violation So basically what I'm saying with this exclusion constraint is if someone tries to add a row and you find a matching row with the same URL, Throw, throw an exception. So essentially I'm enforcing uniqueness with an exclusion constraint. Now I can instruct this exclusion constraint to enforce the constraint using a hash. Which means I essentially enforce uniqueness with a hash. Did I make too many hops or was it fine? Did you get the point? I can enforce uniqueness using a hash. This is not a unique constraint, but I still achieve uniqueness. This is what the constraint looks like.
Speaker 2: Unique hash, exclude using hash. And if I try to add a URL that already exists , I can see that I get a exclusion constraint violation. So essentially, I enforce uniqueness with a hash. Do you unique note of the value of the hash? No. Okay. It uses the hash index to enforce the constraint, but it's still Enforcing it for the actual value. By the way, I can uh use the same approach to enforce uniqueness with other types of indexes which are not lossy.
Speaker 2: And as an edit bonus, I have an ash index that is enforcing this constraint, so I can also use it for queries, okay, like the one we just did. How much time do we have? Half an hour? Hmm? Yeah, we're not going to make it all the way to the end. I have like uh 240 slides. Okay. Now let's do another scenario, let's do a range scan. Okay, let's do a range scan. Basically, we want to answer the question, give me all the URLs that were created in this
Speaker 2: time period. So this is what the query looks like. We want to find all the short URLs created in February of 2020. Okay Now we have some assumptions here. The first assumption is that when we add a short URL, the data is set by the application. So naturally, as we add more short URLs into the table, The date is incrementing. In other words, we have good correlation. If we look at the first row, we'll see the minimum date. We look at the last row, we see the maximum date. This is called correlation, close to one, meaning perfect correlation. There's Perfect correlation between the actual position of the row on disk to its logical value. So it's like one, two, three, four, five, six, seven, eight.
Speaker 2: Okay? Good correlation. So, start the same process. We execute the query, we see a sequential scan. Right? Takes 19 milliseconds, full table scan. What do you do? Dude it super slow, make it fast. You slap a B tree on it, right? So we slap a B tree on it. We execute the query again. We can see that the index is used. Query is faster, right? We take the size, the B tree is around two megabytes, we put it in a table. Are we done? Of course not. There's always more
Speaker 2: So now I want to introduce to you another index that I'm very fond of, which is not used very extensively, and that's block range index or Bryn. Anybody here uses Bryn? No? Yeah, some sort of? Okay. So let's build a block range index together so we understand how it actually works. So a Bryn works on adjacent ranges of pages. Pages is a block that contains rows. Okay, so let's imagine that we have a single row in a table block, and this is our table. This is how it's organized on disk.
Speaker 2: The first block, 1, 2, 3, 4, 5, 6, 7, 8, 9. Okay? So the first step for a brim is to divide the table into ranges of adjacent blocks. Basically you take three blocks, three blocks, three blocks. And then for each range, we keep the minimum value and the maximum value This is it. This is a brint. Now let's use our brin index and try to find something in the table. So let's say we want to use our brin to search for the value 5. So we look at the first range. One through three. Is five in this range?
Speaker 2: No, right? So no point in looking. I can eliminate blocks one, two, and three. What about the second range, four through six? Might be here. Might. Is there any guarantees that I'm going to find five in this range? No. It might be there. It's not necessarily there. And seven through nine, definitely not there. So using our Brain index, we were able to minimize our search in the table to blocks four, five, and six. I was able to eliminate two-thirds of the table, which is this is what an index does. So let's create a brin index.
Speaker 2: on our created at column. This is what it looks like Brin. I forgot the import, but Bryn is also Postgres specific. So you need to import that from Postgres Contrib Um something something indexes. Okay. Now, this is what the actual SQL looks like. Create index using brain created that and we are going to talk about pages per engine a sec. Now we execute our query, produce an execution plan, and we can see That the database is using our index. It's not doing an index scan, it's doing a bitmap index scan, which is fine. But it's still using our index and we can see that the execution time
Speaker 2: is low, but it's higher than the B tree. The B tree was very fast. This one is faster than full table scan, but slower than a B tree. But that's not all. If we check the size of the index If we check the size of the brand, it's ridiculously small, just 64 kilobytes compared to more than two megabytes for the B tree. So now, for the first time since we started this workshop, you actually need to make a compromise. You actually need to decide. Whether you want to pay
Speaker 2: pay two megabytes for three milliseconds, right? Because when we started, full table scan was 19 milliseconds. Right? Now if we pay 64 kilobytes for the brin, we can reduce the time to 5 milliseconds. If we want to improve five to two milliseconds, just three milliseconds, we need to pay two megabytes. So that's a lot. Now once again, we're talking about you know kilobytes and megabytes, but you go to your systems, that's gigabytes. Okay? So in this case, a B tree is faster, Bren is smaller. Now let me ask you a question
Speaker 2: What if we index the same values, but this time they have very poor correlation Meaning it's not a nice one, two, three, four, five. Everything is nicely sorted in the table. What if we take the exact same values and we shuffle them? So we do the same thing, we take ranges of three adjacent pages, we keep the minimum and the maximum, and now we try to look for the value five, and in this case, for the same exact values The index is completely worthless. Okay? So if we have poor correlation, a brin is useless.
Speaker 2: What if we increase the number of adjacent pages from three to five? Exactly. What we get, we get a smaller index. Previously we had three entries, now we have just two. But what did we compromise? Accuracy, right? Because now instead of minimizing our search just to three blocks, we minimized our search to five blocks. So smaller, less accurate. And if we reduce the pages range instead of three we do two, we get a slightly bigger index, but more accurate.
Speaker 2: Now we can minimize the search just to two blocks. So you can actually control this parameter. It's called pages per range. The default is 128. And the minimum is two. I personally strongly recommend that you start very, very low. Because I think that when you get back to your systems, you'll notice that the additional storage from the low pages range Okay, compensates for the accuracy. You want the accuracy more than you want the extra megabytes for the brain. Bryn is very small.
Speaker 2: So Bryn ideal when data has good correlation. If our data doesn't have good correlation, the BRIN is completely useless. Brin is also ideal for tables that don't update frequently because by default it only updates on vacuum. So Bryn is more suited for data warehouse, DSS. systems where you have batch processing you throw data during the night or a few times during the day and you can rebuild your indexes and remember to adjust pages range to find the ideal size. What I presented to you is the default op
Speaker 2: for brain, min max. For each range, we keep the minimum and the maximum But Brin actually comes with other operators that you can use. So min -max, that's the default. You also have min -max multi. So instead of just keeping one minimum and maximum for each range, you can keep multiple minimums and maximums. This is useful when you have data with good correlation, but sometimes you have outliers. So imagine you have a table like one, two, three, four, five, a million. 6 7 8 9 minus 1000 these outliers okay they
Speaker 2: cause the ranges to distort Right? Which makes the bin index not ideal. So what the dear people of Postgres did is they allow to use min -max multi, which keeps multiple ranges Multiple min-max values for each range. Personally, in a real production system, I strongly suggest that you use min -max multi. Way more resilient to outliers, and we all have outliers There's also uh Bloom, you can actually create a Bloom filter on each range. And uh this actually makes Bryn useful. For data with very low correlation. You can actually index your UIDs, random UIDs with Brin
Speaker 2: using a Bloom op. Bloom? Bloom is a special data structure that can quickly answer the question, is this value definitely not in this range? Okay. So bring index. Uh there's a lot to read. I trust that you'll do it. Let's talk a little bit about some common misconceptions regarding indexes. Okay. Okay So, first of all, indexes are not free. Indexes consume space. We saw that. B trees can be very, very big. And also , Indexes requires maintenance. So every time I insert, delete, or update, the database needs to maintain the index as well.
Speaker 2: So let's do a little experiment. I create this table T I have five columns and I'm now inserting a lot of rows into the table. Imagine this is like a batch processing job when you load a bunch of data into your database, right? So this took 118 milliseconds. Okay? Remember this number. Now I go and do this again. Okay, only this time I create an index on every column. After I created an index on every column I load the exact same number of rows with the exact same values and it's now seven times slower.
Speaker 2: Okay? Why is it slower? Because it needs to maintain the indexes as well. As a general rule of thumb, if you are loading a bunch of data into a table, it's better to load the data first and then create the indexes. Okay? Let's let's look at another misconception and I worked very hard on this analogy and I hope this will answer your question. Okay So let's let's create a table for a haystack. Everybody knows what a haystack is? Hopefully. Okay. And now we put a lot of hay
Speaker 2: into our haystack and just one needle. One needle in our haystack. And we create an index. Okay? Now, if we want to find a needle in a haystack We have just one, it makes sense to use the index, right? I use the index to identify the single needle that I have in my haystack and I go and fetch it. But If I'm looking for hay in a haystack, that it makes sense to use the index. No, right? Because I'm going to read 99% of the table. So why bother with the index? Just read through the entire table, right? So Postgres is actually smart enough.
Speaker 2: To figure this out. Postgres maintains statistics on our data and he knows that there's a lot of hay in a haystack. So don't bother with the index. So at this point, people usually ask me, okay, what's the number? Like 20%? Is it like 30%? For 5%? Would it go for the index? So I don't know. There's no magic number. It really depends on the data types, the size of the rows and everything. But The thing to remember here is if you're looking for a needle in a haystack, it makes sense to use the index. If you're looking for hay in a haystack, it does not. Okay, so regarding your question about the bullion, if you have a table
Speaker 2: that has a column that can only have two values, Then if you're scanning for 50% of the table, it might not make sense to use the index. So when I see indexes on columns like that, I ask myself, does it make sense? On the other hand, if you only if you have lots of false and just a few trues and you want to curate a partial index on the true, that might make sense. So indexes make query faster, they make inserts and updates slower, they can take a lot of disk space, we saw that. And indexes are not always the best option. Sometimes you just need to scan the table.
Speaker 2: It's fine. Uh okay, so this this is all the stuff that we talked about. We talked about different types of indexes and features. It seems like 10 hours ago, but it was just 90 minutes ago when we talked about uh inclusive covering partial, right? Function-based indexes when we did the scenarios on our short URL. We talked about a hash index. Hopefully I made it look a bit more attractive than it used to be. And we talked about Bryn. We also talked a little bit about performance. I don't know if you noticed, but we had a pretty good process that we took to evaluate. We started with uh just producing an execution plan
Speaker 2: Taking note of the time on sizes of things. Then as we evaluated different indexing techniques, we created a B tree, we took the size, we took the timing, we look at the execution plan. We put everything in a table and then we can make a decision. We even had to make some compromises if you recall when we talked about the Bryn. And uh That's it, I think. I have another section about concurrency, but I don't think we're going to make it in like 10 minutes. So uh that's it. Thank you. Thank you
Speaker 1: Anyone got questions?
Speaker 2: Oh yeah, do you have questions? Yeah. Thank you. We actually have time for questions
Speaker 4: I assume you uh did the benchmarks using uh solid state disks and not uh spinning disks. And uh if you did you also also try with spinning disks?
Speaker 2: With sorry?
Speaker 4: With spinning disks, old hard old hard drives.
Speaker 2: Oh.
Speaker 4: I assume all those benchmarks have been done with uh SSDs.
Speaker 2: Yeah, my benchmarks are on this beast and that's SSD. Yeah. Okay. But regarding your question, by the way, there are different uh weight parameters you can tune depending on your discs So if you have fast disks like SSD, you might want to make uh you know the cost of reading from disk cheaper if you have fast disks. Yeah, questions? You don't have to, it's fine. Uh
Speaker 5: so during the hash index section you you kind of highlighted the hash functions that are used. Are they like hard hard coded into Postgres? So Postgres determines which function is used based on the data type? And like is there any way to influence that if I want to use something other than like those perfect hash functions? Is there any way to like customize it?
Speaker 2: So uh when you create a hash index, you don't really need to worry. about um the hush function, Postgres is doing that for you. If you wanted to provide a custom hush function I never done it, but I guess you can create a type and you can provide a hush function for this type. This is just an educated guess. I never had to uh actually do something like that.
Speaker 1: I had a question about you know you mentioned
Speaker 2: Are you allowed to ask questions?
Speaker 1: Yeah I'm still a audience So you know you had a dysfunction-based um uh indexes where indexed the result of the function Yeah what happened when the new roles get inserted? It does that recalculate every time the new roles get executed, like inserted?
Speaker 2: Okay, so the question is in a function based index, do I have to recalculate or reevaluate all the expressions every time I add a row No, I don't see a reason why you would have to do that. You uh evaluate the expression on the new row and you index the result.
Speaker 1: Okay.
Speaker 2: Okay.
Speaker 1: Anyone
Speaker 2: I don't know if I want to take your question, man. You have you have all the answers already.
Speaker 6: Come on. So uh coming from the R star tree type of indexes, GIES related. Spartial stuff uh related. Does it make the same sense as in an R start uh index? to have a brain index uh that has a huge range, then inside it another brain index that's small that makes that range smaller. and go on and go on since it is very small in the disc until you pinpoint uh the final range and essentially uh getting the advantage of uh slightly more space but a lot more accuracy and speed like an R stay an R star index in essence.
Speaker 2: So it wasn't that bad. No. So if I understand correctly, uh instead of thinking about it like a brain inside a brain You can say I want to implement my on my own op to index the ranges the the values inside each range in a way that would be optimized for special queries. First of all it's it's possible that it already exists. I think that there's uh some kind of a If I'm not mistaken, I need to check, but I think there's a box operator which is makes brain suitable for what you said. But I think that you should be able to implement your own your own op
Speaker 2: for Bryn and do what you just said. I don't know if it's going to be exactly like a Bryn inside a Bryn, but you can do something probably like that Okay.
Speaker 1: Any more?
Speaker 7: Um another aspect of having smaller indexes that I can think of Is uh the um the index staying in memory for more queries because it's less likely to be evicted. Um but that's difficult to show in a benchmark. Have you You notice something in production where by switching to a smaller index you get a performance impact? Well
Speaker 2: when you use smaller and more specialized type of indexes, the database naturally needs to scan less rows to satisfy the query, making it uh faster, I guess. It is difficult to show during a benchmark. This is where we need to exercise common sense and logic to answer questions rather than try to prove it. But yeah, also uh remember that uh smaller indexes are easier to maintain. So if you want to make changes for example, drop, create the index, it it's uh easier when the index is small. When it's very big, it can be challenging to manage. Hope this answered your questions even a little bit.
Speaker 1: Any more? Okay.
Speaker 8: Thank you. My question about CPU. It was one of uh it was on one of your slides. Uh so my question is um Do indexes often improve the performance on the CPU or it depends because we'll have you know more rights, etc.
Speaker 2: Yeah, so uh when you use an index, naturally the database would have to use some CPU. In our case We managed to save CPU by not forcing the database to reevaluate the regular expression every time. So even if you know scanning through the index requires a bit more CPU than just going through the table, we eliminated a big chunk of work, which is re-evaluating the regular expression. So this is how we uh tackle the CPU angle in this specific case. Thanks.
Speaker 1: Any more? Okay, thank you, Arki.
Speaker 2: Thank you.
Speaker 1: Thank you, guys. I think we have a break now.
April 15 - 17, 2026
Athens, Greece
Note: We understand that names change, people change, and bodies change. We respect each individual's journey and privacy. If you have any concerns about a video or need us to remove content, please don't hesitate to contact us. We will handle your request with care and promptly address any issues.
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026