PostgreSQL Beyond Django: Strategies to Get Max Performance with Álvaro Justen

Speaker 1: So my name is Álvaro. Um I'm from Brazil. This is my first Django con actually. But I'm known as Turikas on the internet, so you will find like GitHub, Twitter, etc. That's my email. And I'm here to talk to you about Postgres. So I started with free software in 2004 and programming in Python 2005. So in 2008, I think, I started using Django for some projects. And Postgres was like the natural uh choice. Uh

Speaker 1: it's like an uh free software database, it's flexible, it's robust for production environments. And uh The thing is, usually people don't use uh Postgres the most uh it can like provide in terms of performance. I'm going to talk just a little contextualization here. So I run this little company in Brazil called Pythonic Café. Here are my contact details. And there we work with open data, like open Brazilian data, and we develop software to clean and analyze this data. So for example, we could

Speaker 1: take the deforestation database from the federal government, and then uh clean everything, try to figure out who is the responsible for each uh kind of deforestation, and then uh cross this data with other Open public databases. So for example, we have elections this year, and I can find which candidates are responsible for deforestation in the Amazon, for example. If I cross this database with another database, we have like the company registry. We have all the partners for all the companies in the country, registers in the country. So we can also find

Speaker 1: other kind of commercial interests of the politicians and the people who the forest. So that's uh what we do and we use Postgres a lot uh for all our projects I will talk about our setup and data set. So I prepare a little Django project for this talk. You can access on GitHub after the talk. And uh uh you can actually download the the the whole data set I'm going to use uh here. So First, I'm going to give like general advice about performance. I'm not going into like deeper details on this general advice because this is

Speaker 1: like more common sense, I think Then I'm going into more details on how data modeling can affect database performance. This is something that people usually don't think about Then I'm going to talk a little bit about full text search in Postgres. The best way to ingest data And you can take a picture of this slide. These are the two links probably you're going to need So the first one is the link to the slides, and the second one is to the application I told you. So uh let's start. I'm using Docker Compose.

Speaker 1: Uh so we are running just two little services, Web, the Django container, and DB, which is the Postgres container. I'm using Python 3. 11, Jungle 4. 2, Psycho PG 299, Postgres 16. I'm using this library that I created called Rose. It will make it easier to ingest data on Postgres. And I started everything from this template uh we created. It's like a cookie cutter uh jungle template for putting everything in containers and make it easier to start new projects. So the dataset we are going to use is from this project called Brazil.

Speaker 1: io which I founded in 2018. We have lots of data sets in there and the most fundamental one is this with the company registry. So we have all the companies in the country Like it's like 69 million companies registered, all partners and all information about public information we can access about these companies. This is a very fundamental dataset we can like join with others to find things. I work together with investigative journalists and we find a lot of useful information from this dataset. So talking about

Speaker 1: the table we are going to create. So there's only one table, the company. I'm not going to put partners and other tables here. because it's it's a short time to to talk about uh to too man too much data and I also already cleaned the data so uh we are going to work like just from the import modeling in jungle, importing data and so on. Sorry. So we have 43 columns, uh 69 plus million rows. And the compressed file CSV is six almost seven gigabytes and uncompressed it's seventeen gigabytes.

Speaker 1: So let's start with the general advice. First thing, uh, if if you are suffering from performance issues, uh you should know the problem. I mean You must investigate what's going on, right? Before making any change, uh know how to use like the X Plane and PSQL uh client and try to investigate what 's happening, right? Then you should write better queries. So You should not load every data uh every time. Sometimes you just need some columns of the the table. So the Postgres server don't need to fetch all the columns and transfer through the network to Django.

Speaker 1: You can use defer and values to do this. You can also avoid um one plus n queries using select related. You can of course create indexes. There are some links here you can use to help in this process, but as I said, I'm not going into the details here because this is like general device. Um okay. One very important thing uh is to use the same software. So if you have like Postgres 12 in your development environment and Postgres 13, 14, etc. in production , bad things can happen.

Speaker 1: So try to use the same version and please do not never use SQLite for testing. Some people use this because SQLite is faster for run the test faster. And it 's it's not a good thing to do. Um and uh I I advise you to upgrade to the latest version. Uh Postgres has like many improvements. Every year there's a new version. and uh you can like save some space and also uh get queries run faster just upgrading. And it's very important also to know your infrastructure. Usually programmers don't know too much about infrastructure.

Speaker 1: But uh it's important to understand what's going on. Uh so for example check the configuration parameters. Uh you could use like the full text search instead of other solutions for searching and you will end up with like less data transfer between instances. You could avoid using salary and Redis, for example, in using procrastinate to uh a task create a task you using Postgres. So there are lots of things you could do to Simplify your infrastructure and data flow. But I'm not going to the detail of this. I just put some links so you can search afterwards

Speaker 1: But just as an example, I'd like to show you the difference of just upgrading Postwares. So if I had this table with company data imported on a Postgres 12 instance, I'd get this 13 gigabytes of table size And five gigabytes of index size. If I just upgrade the version, same data, same columns, everything. I will save five percent just upgrading the version, right? So if you can't upgrade because your your cloud provider doesn't uh provide the support for the new version, consider changing your provider

Speaker 1: because you can save a lot of money just up uh running the new version. and you also will have some performance gains just upgrading. So let's talk about the app As I said, it's just one table, but I've implemented five different models. So the models uh will have like the same data But there are just little differences in this data modeling. So I can show you the differences for which model and you can check the performance gains between them. There's just one view, so the user can search for rows. I've created a very simple, I'm not a front-end developer at all

Speaker 1: uh a very simple form that the user can like type some query uh to search for the company name and also select the stage so I'd like To search a company from Sao Paulo, for example, I can select the state and put a name and I can search. And there's also one management common to import the data I'm not going like to show every line of code here. You can check afterwards on GitHub. But that's the the main thing, you know. So the first implementation is the laziest one. It's like just copy the column names, use some simple types, and do not create indexes, etc.

Speaker 1: So also use this Django primary key. I've used just these types, no indices, and that's the space used. So I got fourteen gigabytes uh of table space, and there's a very important distinction here. Um Some people do not create indexes and some people create a lot of indexes. Sometimes your indexes are not going to be used Because Postgres decides uh it's it's better to not use a specific index. And you can waste a lot of uh space just creating indexes that won't be used

Speaker 1: And Postgres has statistics about it. You can check which indices are not being used and delete them I have some very special cases where the index size is bigger than the table size But it sh it should not be like the default, you know. The the default scenario you should have like little indices uh and comparing to table size it should not be like very very uh proportionally uh very big. So let's check the code. Oops, sorry. So this is the first one.

Speaker 1: But it doesn't matter for you, I mean. It's just the the field types and the parameters we are using here. So I didn't add a primary key, as you can see. So Django is creating a primary key for me. The fact is is that in this dataset I have this UIG field, which is a primary key. So I could be using this as a primary key and avoid Django creating another column and another index, etc. So this is the first flaw of this um this implementation and I see a lot of this during code reviews and uh when I see uh uh

Speaker 1: like legacy code. So the first thing I'd like to discuss with you is the size of the ID It seems to be something like simple. I have like all the tables have have uh an ID, but Do you really need a sequential ID? For example, in this case, I do not need because every company has a number and I can actually generate an UID hash. uh of this uh using this number and then I can have a primary key right so in this case I don't need it and another thing is that before Django 32 the ID of each record uses four bytes.

Speaker 1: That's the autofill, right? So for every one million rows it will use Four megabytes plus the index size of this column, right? But after Django 3. 2, uh it now uses eight bytes. And uh forever uh every one hundred million rows, you are going to use almost half a gigabyte more just storing like zero zero zero bits uh and usually you don't need this So you can change on the project settings the default auto field variable or inside the app configuration you you can also change this field.

Speaker 1: Um let me show an example. So I have here oops my Fancy interface. Um here I can put for example padaria, which bakery in Portuguese. Uh I can select a state and then Click search. I've also created this field called model in which I can select from which table it's going to Execute the query against. So for this first model, I'm going to click search and it it returned like in 0. 033 seconds. But this is actually not the the correct result

Speaker 1: because I just truncated the table just seconds before starting this talk. So uh I I didn't load the data here. So we cannot like count this value has uh the the correct value. We we will check the time afterwards using the the other models Okay, so uh regarding IDs um If you can use like or generate the IDs offline, for example, if you have some kind of methodology or business rule, uh it's preferred to have this instead of letting jungle create this information

Speaker 1: because usually usually the data comes with a primary key uh where like you collect the data Like for example in this case every company has a unique ID number and every person has a unique, for example, SSN and so on. And and I've also developed a methodology to creating these uh UIDs in a consistent manner and also offline. So you don't need like to to look into like a central database or API to to check every ID to to to know every ID you need for your objects. I'm not going to talk about this today but you can check urlid. org to

Speaker 1: to know more. Who knew about this already? About the the integer big integer autofills Okay. Let's go to the second implementation. This one is more compact. So it doesn't use Django's primary keys, because there's a primary key column already. And it also uses small integer field when possible. Before creating this model version, I've executed like select min field name max field name from table.

Speaker 1: to check all the the minimal and maximum values for each column. And then if they are in the range of a small integer field, I can replace the integer field with the small integer field And uh the the saving in space will be two bytes for a plus uh times the number of int columns. times the number of rows. So we can save a lot of spacing here just changing from integer to small integer. For example, for each 100 million rows, you are going to save, like if you have five columns, change it one gigabyte for every one million rows.

Speaker 1: And you po probably don't need to go until this nine quintillion I don't know how to pronounce ID number, right? I think from minus two billion to plus two billion is fair enough for almost all the tables we have. So with this little change, I've saved almost three percent of space for this case. Obviously it depends on how many columns you have, how many rows. But in this case I've saved almost three percent. And the thing is now I also have a A new feature, let's say.

Speaker 1: On the the first implementation, I only had the ID uh indexed The ID created by Django indexed it, right? So if I would like to search for a company ID using like the UUID, there were no indexes for this, for this kind of search, right? But now since I've set the primary key to that UID field, I have an Linux on this column and I can also look up my companies faster. uh since it's like a real ID it's not something created automatically and sequentially So let's show you the differences. It's not that much, but you can see the primary key here, right

Speaker 1: And then you can see also some small integral fields. In this case, we have actually more than five, so the saving space would be like more than one gigabyte per one. hundred million rows. And I can choose here the second implementation and run the search. So as I said, uh don't count on the first one because the table was almost empty. So this is like the the real time. uh to to look this uh to execute this query on my machine right so here i'm executing like

Speaker 1: a where So where state equals to AC and then the legal name and brand name like padaria, right? And it's like almost 10 seconds to return. And I could show you here in the logs the query is like this. So where? Uh this is just a little filter I put like to return only active companies UF is the estate in Portuguese. And uh razão social is the legal name and Nomi fantasia is the brand name, so it's it's like a a like

Speaker 1: operation, right Oops. Okay, so let's go to the third implementation Now, uh what I've changed is the the order of the columns. Just the order of the columns. So if you put first on the model declaration fixed sides fixed size columns from the biggest to the the smallest And then variable size columns, post groups will use more efficiently the space to store every row.

Speaker 1: I'm not going into the details about it. There's a link in here you can read more about. But just changing the column order. I've saved three percent of space. Same data, same columns, just the order. So let's see the code. I've added some comments so you can see how many bytes each type of column uses. So for example, UID uses 16 bytes, right? Date field and integer field four bytes. Small integer two bytes Boolean, one byte, and then the variable length

Speaker 1: field uh like this more text, etc. So if Postgres creates the table this way, um when it reaches a row It can calculate like easily with just multiplying by the the the column size, the the field type size, and you can reach any of these fixed size fields here without needing to like worry or look up how much uh how many bytes that information in that row will uh be using right because it's fixed So it's not uh only better for saving space, but it's also faster for executing queries.

Speaker 1: So So just changing it will have you have your queries uh running faster, right? Um let me see if I changed anything else. No, that's it. So all the text and decimal fields are here. um okay so I could like change here obviously could be like some fluctuation here in in the the time the query execution because For each table, Postgres could have more or different statistics regarding when I I've inserted data. But more or less the time would be like the same in this case But it will like save space and for some types of queries

Speaker 1: it will run faster. In this case, since I'm using a text field Postgres will need to reach that variable length field, right? So in this case, this query won't run just faster because of it. Oops. Okay, so let's go back to the slides. Who knew about this already? Nobody. Okay. So a little little tip here. Doing all those steps uh it takes a lot of time. So if if possible, try to automate those things. I've implemented a command on the rows

Speaker 1: command line called schema. And you It can like detect for you uh which type of each column if you have for example data in a CSV or XLS file. I mean Doesn't matter. It can look the data, try to figure out the data types, and then you can have the output like uh using uh a jungle model declaration or you can use like format equals sql or whatever So try to you I'm not like advertising to use my library here, but try to use something to automate the process. It would be easier the then like to every time you need to create a new model

Speaker 1: thinking about all those stuff, you know. What I usually do is uh import all the data uh as text like every column will be like text And then I can run specific queries on that table to detect the fields, then I can create the final version with all the fields typed correctly, etc. So the fourth implementation would be to uh improve the performance uh using full text search. I'm skipping a natural step here that would be create an index for that uh those like queries run

Speaker 1: faster Okay, you could do this, but I'm not going to do this because I have only 45 minutes. So I'm just jumping directly to a full text search. What I've done here is to add a new field to the model called search data. It's a search vector field. So it's going to take the text I want to index, uh create a vector and store on this column, right? But I've also created an index for this column. So the column is a vector that represents the words, for example, on that row. So if It's like that.

Speaker 1: It will be easier for Postgres to like calculate pre-calculate this vector and then the query could run faster. But adding an index would be much much faster. So here we have like three scenarios. One is the the last implementation, right? No search vector field. And uh I could use full text search on that scenario using some uh Postgres functions to create this search vector Uh for example from a column one or more text columns I have, right? So I can transform the the strings and these columns into

Speaker 1: this search vector and then run a query against this vector. So Postgres would be like going through all the rows on the table, calculate the vector, and then check if the vector matches my query, right? If I add this field, I will pre-calculate the vector. So Postgres would also run through all the rows, but the vector will be already calculated so it can just run the queer the query against. But if an I add an index, uh Postgres would not need to to go through all the the rows. You can use the index to find out which vectors to to check

Speaker 1: and then it will be much faster Another thing I've done here is to create a trigger. And uh a trigger is just a little way for the database to perform an automatic task. So for example, uh after inserting or updating a row, uh update automatically this field or update for example my search vector. based on on that uh new data I put on on that column for example. It's much faster than doing inside your application. So Postgres uh People there are like working for dozens of years optimizing every possible thing inside the database.

Speaker 1: And uh it would be much, much faster to use a trigger, for example, inside a database than doing uh filling like this search data field inside your Python or your Django project Some people avoid uh creating triggers, but I think this is a very good feature from databases. And uh if you don't put like too many, too much logic in there, like business logic. um it will save a lot of time and all the other things could like run smoothly So in this case, uh the table size uh is bigger than the last implementation, but I have a new feature here, right? Uh I have the full text search.

Speaker 1: So I'm not comparing the values just because I mean it's something new we we've added. So in total we have now 21 gigabytes and uh With this implementation, I can search here and have the query executed like in one fifty milliseconds, right? Same data, same results. I mean the the ranking here could be different because the full text search uses a different ranking algorithm than the other one, but uh the result is consistent, right?

Speaker 1: Okay And there's also another possible implementation, which is not creating that search vector field, but also pre-calculating the vector. So in the the last implementation I said we could do like three ways, right? pre-calculating uh calculating um the vector during search execution and also matching the the vectors for executing the search Then a second method creating this field without an index. And the third, creating the field and also the index, right?

Speaker 1: The other option here is to create an index of the search vector without having to create the search vector. It could be like a little complex in the beginning, but uh you don't uh need to use like index For just the plain value of a column, right? You can calculate an expression based on the column values and then create an index for that expression If Postgres detects you your query uses that expression, you can uh it can use the index. So if for example I I have uh A query like select

Speaker 1: asterisk from my table where column A plus column B equals to 10 Something like that. I could index column A plus column B, right? And this is will be pre-calculated and stored in the index, not in another column. So that's what I've done here. No trigger, no more search data field. I've created this compound gene index That's a result of the expression to calculate the vector field, the search vector, and then I could save 20% uh of the space comparing to the fourth implementation. So

Speaker 1: I'm going a little deeper here in the code to show you these differences. This is the fourth implementation. I have here the new field called search data, right? This is a search vector field. And it's by default it's new and it will be filled by the trigger, right? I've also created here this index, this gin index for this new field. And the migration to create the trigger is pretty simple actually. So I give a name for the trigger and I say that before any insert or update on this table

Speaker 1: the fourth company table. Postgres will for each row execute this procedure. It's just calling a function, right? This this function already exists in Postgres and uh it's just upgrade uh updates the the search vector. So I pass here the the the name of the field which has a the search vector the language um here is portuguese And the fields I I want it to like concatenate and insert on the search vector. That's it. But on the last version, I don't have the search data here anymore.

Speaker 1: And uh I have the search vector inside the index definition, right? So it's going to calculate this and start directly in the index. I don't need the trigger, it's going to do it like automatically. And of course, you could use other compound indexes to make this uh this kind of queries execute faster. Okay, so let's check. If I click here and run, it's also going to run. Oops Okay, a little bit more, I don't know why, but

Speaker 1: less than one second. Okay Let's go on. So there are also a lot of Other Postgres specific features. I'm going just to list it here because I don't have time for it and I'm also going to finish with the ingestion process. So you could search for array field, JSON field, and other integral range field and other things Postgres have that it it it helps a lot and it it could save space and also make queries run faster. Uh also Postgres has specific kind of indexes you could use

Speaker 1: So depending on of the type of query you want to execute, probably you won't use B3, which is the default kind of index. So it's also a good idea to read more about all the types and then select the the correct index for your use case. Full text search I said already. And there's also more in the Django documentation. Just to finish this part, I'd like to show you this query set I've created Postgres , I mean it it has the full text search, but you need like to to build the infrastructure, right? So it's not that easy uh to start using it Because you need to configure the

Speaker 1: search vector and all this kind of stuff. And here is is just a simple uh query set you could use to like uh not only executing the search on the language you want, but also ranking the results, ordering the ranking, etc. So you can just take this code from my GitHub and use it on your implementation if needed Okay, so let's talk about importing data. Uh I've created a management command to import data, and this management command has actually three types of uh Three mod uh models of working.

Speaker 1: The first one is like for each uh every CSV row, it it's going to call model. objects. create, right? So one at a time. I started running this and uh I've like spent uh a little time and then stopped the process because it would take like more than one day to finish And uh here there's a uh a little detail I'd like to to talk to you. Uh I'm using the compressed file, right? So the I/O on the disk is less than if like the the file would be uncompressed. And uh even with this, I mean there's the overhead of the compression, but

Speaker 1: Even trying to reduce the I. O. uh. it's not going to perform uh very good. The second thing is to use book create. Uh it's faster, but it will take like two and a half hours. I can't wait for this time. The third method is to use Postgres copy command. So there are lots of ways to invoke this comment, right? I've implemented on this Rose library uh this function called pg import which calls uh the the copy comment but you could use any other implementation this is just a handy function to

Speaker 1: It like creates all the copy SQL string for you. It detects like the field names and the dialect, the CSV dialect and the everything else. So you can just pass the CSV file name and the table name that database URL and it works. So it uses PSQL uh and then executes the copy, and I could execute in eight minutes twenty seconds, which more or less uh 123,000 rows per second versus less than 1000 per second, the the first method. Okay, so this library as actually has uh

Speaker 1: PG import and export, so you could also use the command line interface to export, for example, the the result of a query to a compressed CSV file. And this is just an example. So I think my time is almost over, but maybe I can take one question. I don't know. Thank you.

Speaker 2: Looking right now and see how you showed all the different models and how it was improving. It looks pretty easy, but looks like a tough journey to get to know how to improve each version that you had. How did you know that there was more to come? And how was your journey to find each of these improvements? How long does it take? What was your experience?

Speaker 1: Nice question. Well, I was not satisfied with the the results, basically. So the queries would like take too long to run. or I I would need to wait uh too long for the data to to be imported. And uh at every process I I try to figure out how I can do it faster. And uh it takes time. It was not like reading just one thing, you know. It it's like lots of iterations over it. uh but and i i think postgres unfortunately uh you you need to dig a little bit to to use it like uh uh maximum performance.

Speaker 1: But that's it. It's time and trying error

Speaker 3: Uh with the fields ordering thing, I suppose that if I already have a project with a bunch of models, the tables are already created. Um if I reorder the my fields, it probably won't make any difference, right?

Speaker 1: Yeah.

Speaker 3: Um any advice on like recreating all the tables, basically? That's

Speaker 1: I I've started creating a little Python script to read a table, do the correct alignment, and then create the SQL code to like create start a transaction to create a new table with all the align aligned data move everything and then override the the the first table So maybe I can release on GitHub and that's it, I think. I I don't know if Postgres has something like automatically to to do this? I I think no. Okay. Thank you so much. Thank you very much.