Anatomy of a Database Operation

Speaker 1: Hi, good afternoon, thank you. Um this is not the first time that I've been scheduled to talk to people about databases just after their lunch break So I will do my best to make it really engaging and exciting, you know, all those things we know databases to be, just to make sure that you don't have your little afternoon nap. Okay, so I'm Karen Jex. I'm a senior solutions architect at Crunchy Data, and I'm going to be talking to you today about the anatomy of a database operation. or what happens behind the scenes when you send a query to your database. So this seemed like a really good idea back in December last year when I asked people for ideas of what kind of things they'd like to learn about databases, what they'd uh what they'd like to to know, what I could potentially submit as a talk for DjangoCon

Speaker 1: And somebody suggested that they'd like to know how databases work and store data under the hood so they could build a complete mental model of what happens when you call, select, or insert. Great idea, I thought. Fantastic. And then I realized just how much there was that I could talk about behind that, you know, one seemingly simple request. So this is my attempt to fit what I think are the uh kind of relevant important parts of that into I'll attempt 25 minutes so there's time for questions. If not then come and find me afterwards. So I actually learned Django for the purposes of this talk. The first time I've ever used it. I'm very proud of myself, and this is the extremely sophisticated website that I have built.

Speaker 1: We'll ignore the fact that most of the links don't actually do anything. So you can list beers, types of beer and breweries, you can search for your favorite beer, or you can add a new beer But what's happening behind the scenes when you do those things? How is the request passed over to the database? How are the results retrieved and returned to you? How is the data stored, retrieved and modified? And why should you even care about that? Okay, so one of the reasons that you might care is users tend not to be happy when they see things like this, when they're trying to retrieve uh data, uh view, view some information on their website. And they're really definitely not happy when they see that type of thing. So having a bit of an idea of what's going on behind the scenes in your database can be a really helpful thing

Speaker 1: So, this is me, as you can see from that diagram that represents my career so far. It's been very database-shaped. I was a DBA for about 20 years, then I went into database consultancy. And I'm now a senior solutions architect, so I help customers to design, implement, and maintain their database systems. Which means actually that I haven't been doing a lot of this real nitty-gritty um behind the scenes Postgres stuff for a while, so I had to relearn some of it to be able to remind myself how to explain it to other people. I'm also heavily involved in the Postgres community, as you might have seen when I did my lightning talk earlier in the week. I'm a recognized contributor to the project.

Speaker 1: I'm on the Postgres Europe Board of Directors and I'm leading the Postgres Europe Diversity Initiative. So if you want to talk to me about any of those things later, I'd be more than happy to do that. Okay, so the basic Postgres system architecture is your classic client-server model. Your client sends a request to the server. And the server sends a response back to the client. But I'm assuming that you would prefer a bit more detail than that. So we'll we'll dig in a little bit. So in our case, the client is our Django app, and for the purposes of this presentation, I am pretty much treating the left hand side of this slide as a black. box because you all know so much more than I do about what happens on that side of things. And I'm going to concentrate more on the right-hand side.

Speaker 1: So I'm going to concentrate more on what happens once it gets to Postgres. What's Postgres doing with it? So instead of an agenda, I've created a little diagram. So we've got the three basic steps to perform a database operation. We're going to send a request over the network to our Postgres database. Postgres is going to execute the request, it's going to access data and it might be modifying data. And then it's going to send the results back to the user. So we'll have a little look at each of those steps but concentrate mainly on the second one where Postgres is actually executing the operation. Okay, so first of all, we need to send a request to the database. So as an example, we're going to open the beer types

Speaker 1: page. For the page to open and display the types of beer, we need to figure out what um request is actually going to be sent to the database, what query is going to be sent to the database? So unless you're exclusively using uh custom SQL queries, which I I doubt Then this is where the Django ORM obviously comes into play. I am not going to try and teach you anything about Django or the Django ORM, that is definitely not my area of expertise. I'm also not attempting any optimizations for the purposes of this talk. I'm just showing you what happens. And fortunately Haki did a really great job yesterday of showing some of the things that you can look at in terms of optimizations.

Speaker 1: So in this case the view is just selecting everything from the beer type table and ordering it by name. And this is the SQL statement that's generated by that request. You'll need a database connection, obviously. So if you don't already have a connection to the database, we'll we'll establish one for you. The Postgres server process, its name is very easy to remember, it's called Postgres. So that manages the database files, it accepts connections to the database. And it performs database actions on behalf of the clients. Anybody with kids of a certain age or have had kids within the last couple of decades might recognise uh Peppa Pig's Happy Mrs

Speaker 1: Chicken game for some reason. Um I was I was reminded of this, so bear with me. Hopefully the analogy will make sense. So happy Mrs. Chicken represents our original supervisor Postgres process. She listens on a port 5432 by default for incoming connections. When something connects on that port She forks a new Postgres server process, so in this case lays a new egg for that connection. And from that point on, the client and the new server process, the egg communicate without happy Mrs. Chicken being involved, she can move on to go off and lay other eggs for other clients. So we talk about that combination of the server process and the client process that's associated with it as a Postgres

Speaker 1: session. So when we talk about a Postgres session, that's what we mean. So once you've got your database connection, the original Postgres process moves aside and it just sits there waiting for new connection requests. And of course, there'll probably be other client connections at the same time, but we're just going to concentrate on one at a time for now. And so slightly off topic but related here, we generally try and keep the number of connections to the Postgres database fairly low. So we generally think of in terms of low hundreds of connections. The default max connection setting is 100, but several hundred is generally fine.

Speaker 1: Mainly Because a high number of client connections means a high database overhead. You'll be using things like memory and other resources. And rapidly creating and destroying server process is is expensive. So you want to avoid doing that too often. So you'll often use a connection pooler between the client and the database. So that lets you reuse and buffer those connections between your database and application. And it will reduce that rapid creation and destruction of processes. So you could use connection poolers that are built into your client, such as Django, or you can use something like PG Bouncer, which is a Postgres connection pooler, it's a lightweight connection pooler, very easy. to use to um to manage those connection balls for you.

Speaker 1: Okay, so now we've got our database connection, we know what we want the database to do, we can send that query over to Postgres to be executed. Which takes us to step two, so actually executing the operation in the database. So as I said, this is the bit that we'll spend most time looking at, how Postgres is actually going to execute the database operation. So remember, this is the query that we're actually executing. We're selecting all of the columns from the beer type table and ordering them by name. To be able to reserve uh to return results to the um user, Postgres needs to go through several steps to execute the query.

Speaker 1: First, it needs to pass the statement. It needs to check that there are no syntax errors, that all the objects exist, etc. Then it does steps called transform and rewrite. So it takes things like views into account, makes sure it knows which objects it needs to access. Then it plans, it uh plans the query to figure out the best way to actually retrieve the data that it needs to access. And finally, it actually executes the plan. So it actually goes and accesses the tables, fetches the data blocks, applies the filters, sorts, aggregations, etc. Okay, so the first step, the past step, is the query syntax actually correct?

Speaker 1: Have you correctly used the database keywords, etc. ? Do the objects that you're trying to access exist? Have you spelt the table names and column names correctly? Do you have permission to access the objects that your query is trying to access? So we'll assume that the statement generated by the Django ORM is actually correct, but this is the stage where if you had, for example misspelt one of the tables in your query, you'd get an error saying something like relation, uh so in this case beer types doesn't exist. So next we've got the transform and rewrite stages. So the transform part is where it interprets things like views to understand which actual tables, functions, etc.

Speaker 1: it really needs to access. So if we here, for example, have said that we want to select from beer view, Postgres has to understand that that means it needs to go to the beer and the beer types tables and apply whatever filter we have in our uh view to get the actual information. So it will rewrite the query to go down to the actual base tables. Then the rewrite phase is going to make more modifications. That's going to take things into account, such as rules, for example, row level security. So I'm not going to go into row level security. in detail but it would do things like add a filter to say which objects, which rows a user's actually allowed to access. So that would be added at this point as an extra filter

Speaker 1: Once we've done the transform and rewrite, we then start the planning phase. So this is where uh Postgres generates The execution plan. So the details of the way in which Postgres is actually going to go and get the results of the query. So remember SQL is declarative. You tell Postgres what you want, you tell it the results you want to get. But you don't tell it how it's going to do it. It's Postgres that works out how it's going to do that. So we're going to see things like how's it going to access the tables? Is it going to use an index scan or is it going to do a scan of the whole table? Is it going to join tables using nested loops, hash joins, merge joins? What order is it going to join the tables in? Which tables

Speaker 1: is it going to use as the driving table, for example? Adam already spoke about execution plans on Wednesday and gave a really good overview of some of the elements there. So I'm assuming that most of these concepts aren't completely new to most people, but we'll just have a quick look at some of them now. And I do know that execution plans can be pretty daunting to understand, pretty daunting to read until you get used to them. I asked people, as part of my preparation for this talk What kind of things they wanted to hear most about the you know the topic of the talk, and almost half said query execution plans. So I might have to actually submit an entire workshop on reading and understanding

Speaker 1: query execution plans at some time. But for now we'll just concentrate on a few little hints that will help you to understand those. So, as we saw earlier in the week, we can use explain to see the execution plan. So, what Postgres is planning to do to execute the query. And I've added just two options here. The first is analyze. That means it's actually going to execute the query. So it's not just going to say, I think this is what I'm going to do It will plan what it thinks it's going to do and then it will actually do that and bring back some real information to tell you about what it's done. Important thing to remember about that is that if you're executing a statement that makes changes in your database and you don't want those to be permanent

Speaker 1: Make sure you put this explain analyze within a transaction block and roll back at the end. And I've included the buffers option because that's a really good one to help identify which parts of the query are the most I. O. intensive. So once you get that query execution plan, it's made up of a series of nodes or steps in the plan. This is one of the nodes from the plan that's generated by our simple query. So for each node we see what the node does and what object it's acting on. And we see here some estimated statistics. So We've got two costs here. The first one is the startup cost. So that's the cost

Speaker 1: to bring back the first row. And the second is the total cost, the cost to bring back all rows. Sometimes those two will be the same, sometimes they're different. Usually the one we're interested in is the second one, how much it costs to bring back all of the rows, but there are times where actually just um being able to get one row back quickly is the most important We get a row count estimate and we get an average row width that's going to be brought back. If we're using the analyse keyword, then we'll get the actual statistics back as well, how long it took and how much was accessed. Okay, so this is the whole plan. So here we can see that we've got a sort and a sequential scan node.

Speaker 1: Sort is our root node. And the results of the sequential scan node are going to be fed up into it. So any nodes that are indented will feed into the less indented node that's above it. So here we've got a sequential scan on our beer type table. So it's reading through the whole table, which is expected because we asked it to bring back all of the rows. So to bring back all of the rows, it needs to read the whole table. And then we've got a sort. This is a really small table, so our sort is being done in memory. We can see here we've got a quick sort memory Okay, so let's take a slightly more complicated query. In this case I've removed buffers just to make the output easier to read.

Speaker 1: Normally I would leave that in there to actually get that information. So this one is getting a list of beers where the type of beer is Irish Red Ale. No particular reason. So we can see we've actually got several nodes in this case. We've got a bitmap index scan that's feeding up into a bitmap heap scan on the beer table. We've got a sequential scan on the beer type table, and that and the bitmap heap scan on the beer table are feeding up into our nested loop join. So that's what's joining the two tables And that in turn feeds into our root sort node. So that's how we're reading the plan. We can see that things are feeding up from the most indented up to the least indented.

Speaker 1: So before I go through the individual steps in that plan, just a quick aside, or not so quick maybe, to show the way in which the data is actually stored inside your tables. So Postgres tables and indexes are made up of a series of 8K pages, or it could be a different size if you've compiled Postgres with a different page size for some reason, but let's assume that you've not done that You'll have a series of 8K pages. This is a simple representation of the layout of one of those pages. So the data fills in the free space from the end of the page towards the beginning. And then you've got row IDs that are pointers towards that data that fill in the space from the beginning of the page towards the end.

Speaker 1: The default table type in Postgres is a heap table, so it's an array of data pages where your rows aren't stored in any particular order Thank you again to Adam and Haki for expertly setting the scene on Postgres indexes so that I don't need to go into too much detail here. So as you've already heard, there are multiple index types in Postgres, but B tree is the default. It's a multi-level tree structure. So you get a single root or meta page at the beginning. You've got one or more levels of internal pages. That each points down to the next level in the tree. And then you've got a single level of double-linked leaf pages. So those each contain tubels that actually point to the table rows.

Speaker 1: So this index here is representing the beer type index on my beer table. So the leaf pages will contain pointers to the rows that contain each of the different types of beer So I've just shown red ale here just to illustrate that. And when you see a diagram like this, it can be a bit misleading, but actually typically Over 99 % of the pages in an index will be leaf pages, so it's it's generally a very wide structure rather than uh deep Okay, so now Postgres has to put the plan in action. First of all, we had a sequential scan on beer type. A sequential scan, as I said, checks through each of the pages in a table to find the rows that it needs.

Speaker 1: In this case, we had a filter, we were filtering just for a particular type of beer So it will check as it goes to just bring back the rows that match the filter. And we're often taught that a sequential scan is bad. You'll be looking through a plan and if you see a sequential scan, you'll be, you know We don't need that, we need to add an index or do something else. But sometimes a sequential scan is actually the best way to go. It can actually be the most efficient way to bring back data. Another thing to be aware of is that Postgres won't necessarily need to be going down to disk for all of this. Some of these pages might already be in memory in the shared buffers, so you might not have to go to disk for all of it. So in this case, we're looking for the single row for Irish red

Speaker 1: ale in the beer type table. So again, in this case, you might be wondering why not use the index that I did actually create on that column. But the whole table actually only contains two pages. So it's really fast for Postgres just to grab those two pages and find the row it needs. It would take it longer to go to the index, look up what it needed, and then go to the table to find that. Okay, next we had a bitmap index scan. So that was on the index on the beer tables beer type ID column. This is going to traverse the index tree to find the entries that match the filter. So this time it's looking for the beer type ID that matches the value for Irish Red Ale. And it's going to create a bitmap of potential row locations.

Speaker 1: Normally those will be row IDs, so a pointer to the actual row location. Sometimes it might be a pointer to a page and Postgres will fetch the page and find the information it needs in there. That bitmap of row locations is then passed up into the bitmap heap scan node, and that's going to use those locations to go to the table and just pull out the information that it actually needs. Okay, so that was selecting some data. So far so good. Very, very quickly we will go through what happens if we need to modify some data or if someone adds a new beer or a new beer type. So, for example, if we want to insert a new beer type called

Speaker 1: IPA, Postgres is going to find a page in the table that's got some free space. and add a new row in that space available along with a pointer to the new row. If there's no space in any of the existing tables, it's going to create a new page and put it in there Once that transaction is committed, the new beer type will be visible to other sections. I'm not going to talk through transactions now because we did actually have a really good explanation of those earlier, and I'll carry on to the other part. But I will share these slides afterwards. And uh just a a little thing to be aware of that most Postgres clients, Django included, enable autocommit by default. So that means that it um implicitly each statement starts a transaction and commits at the end of it so you need to specify if that's not what you want.

Speaker 1: Deleting rows. If we delete a row, it doesn't actually physically get deleted. Postgres just marks it as dead. It will be removed later during a vacuum operation once Postgres is sure that it's not actually needed anymore. Updating a row is actually a combination of an insert and a delete. Postgres is going to create a copy of the existing row but with the new information and it's going to mark the old one as dead. So that can feel a bit counterintuitive sometimes, but it's all part of Postgres 's multiversion concurrency control mechanism, MVCC. And that's how Postgres internally maintains data consistency.

Speaker 1: It means that each SQL statement will see its own snapshot of data and it won't risk seeing um Inconsistent data that's been modified by other transactions at the same time. So that's how we get what we call transaction isolation The main advantage of this method, and it is a big advantage, is that locks for reading data don't block locks for writing data, don't block writes, and locks for writing data don't block reads. So it's a big advantage there that you're not locking things. But of course, the trade-off, as many of you will know, is that you need to periodically vacuum tables to remove those dead tuples that we saw to minimize table bloat.

Speaker 1: And that brings us to the final step, which is returning the results to the user. So Postgres returns the results to the client, to Django. So that they can then be presented to the user. So in this case, I'm displaying the different types of beer. Obviously, this is a ridiculously simple web page, it's just for demo purposes, and there are many, many things it could do better. And there are definitely some considerations for this data that's returned to the client. The main takeaway really is don't retrieve more data than you actually need. Aside from not wanting to perform more I. O. than you need to on your database. There's a network involved here. You know, you don't want users to be hanging around waiting whilst you retrieve um

Speaker 1: and send to them. data that's not actually needed, not going to be displayed. So if you're not going to use certain columns, don't select them. If you don't need to display all rows or you don't need to display them all at once, then just fetch the ones that you actually need. And I've put that little quote there because when I was a DBA, the database was always blamed for everything. You know, people always came to us saying that it was the database, but our mantra as DBAs was it's always the network. Right, I added in a couple of extra things for if we had time. I think I've probably got uh two minutes just to uh to tell you about them. The first is that Postgres configuration parameters really do have a big impact on a lot of the things that we've looked at today.

Speaker 1: So they'll have an impact on how operations are performed, on your connections to the database, on performance. And I did a whole talk on that in Edinburgh two years ago, I think it was now. So I won't go through that now, but if you're interested, then please feel free to go and have a look at that So I'm just going to talk about a couple of logging parameters that are particularly useful if you want to know more about what's actually going on in your database. You can also find out more in the Postgres docs and in the default Postgres configuration file, which actually contains a lot of really, really useful comments. So the first is logmin duration statement. That is going to make sure that any statement that runs for longer than the time you specify is written to your Postgres

Speaker 1: logs. It's disabled by default, so you won't get the statements written there. So that can help you track down unoptimized queries in your applications. Really useful for development and debugging purposes, but it could cause a lot of noise if you switch it on in production, so just be very careful. and set it to a value that is really whatever counts as too long for your system. And the second is log line prefix. This is a string that's added to the beginning of each Postgres log file that tells you things about Who, what, why, when, etc. about that particular error message or message. By default, you just get the timestamp and the process ID, but you can include as much information as you like you've got all sorts of different fields you can add and you can include any text you want.

Speaker 1: So the example I've given here means you'll get the timestamp, the host that's being connected from, the database username. the database that's being connected to and the process ID. And the very, very important bit that a lot of people forget is the space at the end so it's actually separated from the error message so it's actually legible. There was a question yesterday about how to learn SQL. Obviously, I highly recommend learning SQL because, you know, it's what I know and love. And I'm biased because I work for Crunchy Data, but I really love the Crunchy Data Postgres playground. So our engineers uh created this playground which is Postgres in your browser. So they use WASM to create a full Postgres instance in your browser.

Speaker 1: along with a selection of tutorials. Even though it's a full Postgres database, please do not use this for production, you know, unless you want to leave your browser open. And then I've just shown as an example the Learn SQL tutorial. So you can just select from a list of tutorials, you've got the different commands and explanations on the left, and you've got the creates a few tables for you and um and lets you work on that side. I've included the list of various resources um and references that I've spoken about. Again, I will share the slides so don't feel that you need to make a note of any of those now. And I will publish annotated slides as a blog post, but I can't guarantee when I will be able to do that. Thank you very much.

Speaker 2: Great talk, thanks. You mentioned sequential scans could be better in some scenarios. Would you be able to share some examples, please?

Speaker 1: So the example I gave was where it was only the table itself was only a couple of uh pages long. So Postgres would actually do that in a single fetch. It would just go and get those two pages, whereas to go to an index it would have to go to the index, so it's got to go to at least one page in the index, probably more , and then get the row IDs and then look those up in the table. So that's one example. Another one is if you're selecting more than a certain percentage of a table, and there isn't a hard and fast rule for it, it will depend case by case on how the data is structured, etc. But it has to um you know collect this list of where all of the uh where all of the rows are.

Speaker 1: So if I was selecting several different beer types, for example. I wanted it to find all of the red ales, all of the IPAs, all of the lagers, for example. It would have to go traverse the index to find each of those, get the list of row IDs that corresponded with those. And then go through the table picking out all of the things it wants. So that's actually a lot of page accesses. Whereas Even sometimes if you're collecting one percent of a table, it's faster just to scan through the table and throw away the ones you don't need. Thank you very much.