Django with PostgreSQL Superpowers | Paolo Melchiorre

Speaker 1: Hello everyone, and I'm very happy to be here with you all, even if remotely. In this talk, we see how to use some great feature of Postgres as a database in Django. In the Django database backends section, the Django documentation, we can read that Django attempts to support as many features as possible on all database backends. However Not all database backends are alike. Django per se is a database agnostic web framework, but real-world projects are not. Postgres is the richest feature of a Django supporter database and we'll see in this talk how to use some of this superpower.

Speaker 1: Before moving on, it's important that I make this clear. Okay, it seemed clear to me, so we can move on. Seriously, I would like to underline that I'm not a database administrator. So who am I? I'm Paolo Vecchiore and I'm the CTO of 20Tab, a Pythonic software company for which I work remotely. I'm a software engineer and a longtime Python backend developer. After using Django for a few years, I became a contributor to the project. I also use Postgres as a database for all my Django projects and now it's time to create one of them. As usual, to create a Django project, I use the latest Python 3

Speaker 1: stable, create and activate a virtual env, in which then I install the latest stable Django. Then using Django Start Project Command, I created a basic file for our project. Let's see what it takes to add Postgres to this newly created project I think you are familiar with this drawing from The Little Prince. This drawing is used as the header of the Twitter account of Psycho PG. a Postgres driver for Python. I think it represents its goal very well. Python with Postgres inside. Psycho PG is the most used and advanced Postgres

Speaker 1: driver for Python. It implements the Python DB API 2. 0 specification. And it's distributed under the LGPL license. The library was released 20 years ago and over the time has been constantly improved and kept aligned with Postgres. Version 3 is currently being developed. Psycho PG is a wrapper for libpq, the Postgres C Lite client library. To install this package on a Debian-based system, you can use the Happy T package manager. For most operating systems, the quickest way to

Speaker 1: install Psycho PG is using the package available in the Python package index. And now let's see how to use Psycho PG in Django. To use Postgres as a database in our Django project, we modify the settings. heading the Psycho PG-based database backend and the connection parameters for our Postgres database, which we may have locally or remotely. If you embrace the 12-factor methodology, you can define a database URL variable in your environment. Depending on whatever you use, Django database URL or Django configuration, your database section should look something like this in

Speaker 1: this example. Let's now see our database in action. We'll use the example model defined in the making queries section of the Django documentation. For our test queries, we'll only use an author model and an entry model, but containing various types of field we can search on. We can perform basic queries like this in our model, but uh actually we can run these queries using all the other supported databases as well. What we really interested in is using Postgres specific feature from Django. For this same reason

Speaker 1: in 2014, Mark Temily A Django Core developer started a crowdfunding campaign to develop a module that contains fields for a number of Postgres-specific data types. The campaign was successful and the new model was merged in Django 1. 8. The module now contains possible specific fields, indexes, functions, extension, and so on. Over the years, important functions have been added such as JSON fields, full text search, random UUID, and operator classes. JSON fields have become usable also in other supported databases, but only from Django 3. 1, released last year, five years after being introduced in the Postgres.

Speaker 1: module so to use all the function of the Postgres module just add it in the installed apps list in the setting file of your project And now let's get to know some of the feature of this model better. I took this photo during the spring day after the JangleCon Europe 2017 in Florence. In that day, I completed a pull request to add a database function in the Postgres module for Django 2. 0. I was helped by Mark Tamil. the original creator of the Postgres module and by Marcus Alterman, a Django Core developer, both in this photo.

Speaker 1: The database function I'm talking about is a random UUID. The random UUID database function returns a version 4 random UUID. It's contained in the PG Crypto module that provides cryptographic functions for Postgres. It can be activated using the crypto extension migration operation. But from Postgres 13, this function is included in the core. To see the function in action, we'll add a UD field in our entry model. This field uses The related Python module ends only when used on Postgres this store in a UED data type.

Speaker 1: The database will not generate it for you, so it's recommended to use the fault. But note that the UED for caliber is passed to default and not an instance of it. Using this function you can update all the values in a model way faster than cycling over all the entries and generating a new value with the Python function. I presently use this technique to set in few seconds UIDs in a nearly one million rounds table. Pretty fast. I took this other photo during this print day after EuroPython 2017 in Rimini.

Speaker 1: I promoted a working group on Django and some developers joined me. Today we started a transition of the Django project website search function from Elasticsearch to Postgres full-text search solution. Since then, I've written an article and give more than one presentation of full text search with Django. So I skip the implementation details, but at the end of this talk, you'll find references to retrieve them. The full text search support in the Postgres module has specific fields, expression, and functions. If your Postgres version is recent enough, you can also use specific indexes, phrase searches, or web search style.

Speaker 1: Without any customization, we are able to perform a full text search on a single field of the entry model. For example, we can search for a word in the plural form and have results in singular form. This is a very convenient way to start using the Postgresful search out of the box. To speed up the full text search, we can add a search vector for the entry model and use it to create a new functional gene index on the same model. The functional index are an addition of Django 3. 2 available for all Django database backends, but GeneIndex is only available for Postgres

Speaker 1: backends. After that, we can search for a word using a syntax similar to the one used by search web search engines and have more accurate results You can use this syntax using the search query with the search type attribute. Furthermore, the SQL queries will be faster thanks to the gene index With this photo, we move virtually in the northern Europe, more precisely in Norway. I took this photo because I really liked the effect of these typical houses on water.

Speaker 1: All similar to each other but repeated, like data in an array. The array fields make Postgres array types available in Django. They are very convenient for storing arrays of similar data without creating a new model for them. You specify other Django model field as basis. Also its sides can be defined and it can even be multi-dimensional. For example, we can store multiple emails in our auto model by defining an array of email using the email field as base. We

Speaker 1: can then query our authors looking for an email. The content of the field itself is represented as a list. The resulting SQL code use all Postgres specific operators for arrays. Unfortunately, the default array field widget in the Django admin is a simple input text with comma separated values But using this Python package, you can represent in the Django admin the values as a multiple dynamically addable input text.

Speaker 1: I took this photo in San Francisco. We are now moving virtually in California because the package we are going to talk about is provided by the California Civic Data Coalition, an open source network of journalists and computer programmers from news. organiz organization across America. Django Postgres Copy is a Python package to quickly import and export delimited data with Django Support for Postgres copy command. The copy command moves data between tables and standard file system files. Copy to copies the content of a table to a file and copy from copies data from a file to a table.

Speaker 1: To more flexibility, Django Postgrescopy uses a temporary table that are automatically dropped at the end of the session. To benchmark Postgres copy, we use a file containing all the geographic names from the OpenStreetMap project. We create a new model that maps each column contained in the CSV file into a field. We have to replace the model default manager with the one from the posters copy. package. Here we use the file with all the geographical names Hovidally. The file is more than 200 megabytes.

Speaker 1: To upload the file, we use the appropriate query set method in which we indicate the path of the file. The loading speed is impressive, almost 1 million records in just over 3 seconds. Under the hood, Django Postgres Copy executes several SQL statements. First create a temporary table based on the content of the file. Then upload the content of the file to the temporary table, in this case in just over two seconds. insert the data from of the temporary table into the table managed by the feature Django

Speaker 1: model, eventually applying some transformation information and finally deletes the temporary table. To reduce the space and transmission bandwidth, we have compressed our file in gzip format. reducing the size of a fifth. But we can pass our compressive file directly to Postgres Copy without having to decompress it. Loading is even done in a slightly shorter time than misered before. I want to repeat it, almost one million record in just over three seconds. Impressive. Okay, now we are virtually moving back in Italy

Speaker 1: with this photo that I took in Abruzz in Abruzzo, the region where I live. I'm showing you this photo because now we are going to talk about trees. Postgres L3, exactly. Django L3 is a tree extension to support hierarchical tree-like data in Django models using the native Postgres extension L3. It's a simple and faster alternative to implement materialized path compared to more used Django. packages. The package has a path field and an abstract tree model To add the tree-like hierarchy to the Henry model, we add

Speaker 1: a path field inheriting from the tree model provided by Django L3. We also add a dedicated Postgres GIST index on the same field to speed up queries. This is a tree representation of the example hierarchical structure that we have stored in the path field of our model. I took this example from the Postgres F3 documentation so you can reproduce it. Here we perform a hierarchical query to filter all the contained models of a particular path.

Speaker 1: Sort the results by tree and then take all the subpaths. The resulting SQL statement use the L3 operator. to filter the table and the gist index to speed up the operation and the sorting With this photo, we are now moving on the part of my last hike on the Italian appenais. I already used this photo in my recent talk about maps with Django, which you can read on my blog. But I want to briefly talk about the geographical extension of Postgres used by GeoDjango. PostJS is a Postgres

Speaker 1: extension and it's also the best database backend for GeoJumbo. It internally integrates special data and has special data types, indexes, and functions. In this chart, I have synthesized the compatibility table of the geographic backends supported by GeoDjango. In the Geo Django documentation, there are three compatibility tables for special lookups, database function, and aggregate function. As you can see, PostJS is the only geographical backend that supports 100% of the feature. If you are interested in using this feature, you can see

Speaker 1: my previous talk. There are also many other possible specific features that can be used directly in Django. For example, you can use a lot of indexes. and the aggregation function only available in Postgres. You can also use Trigram extension to perform fast searching for similar strings. And there are specific fields only available in Positbus like Rage field, case insensitive text field, and more. Before saying goodbye, I want to share with you some tips based on my experience as a Postgres user with Django. First one is to read the documentation on the Django website

Speaker 1: because it's full of information about the Postgres module. Read the details about the Postgres feature directly in the Postgres website. It helps you to understand how things work under the hood. Read also the source code of both projects on GitHub because there is something you can learn only from the code. And last, search for questions on Stack Overflow, but try to answer a question by yourself instead of really. the answer last but not least you can also study this tool because it's risen with a creative common license The Psycho PG library I talked to you about is under active development

Speaker 1: and you can use this contact to learn more about it, get involved and also sponsor its development. The company work for 20Tab is one of the sponsors of this library. The next can be you. In 20Tab, we have developed many Django projects using Postgres. You can find more about our open source project and our platonic work using this context. To find out more about my personal work with Chang and Postgres, use all my contacts. Using this QR code, you can download this presentation on my website Thanks again for having me and enjoy the next talk in the conference.

Speaker 2: Hello. You did a poll before the talk started, but uh I don't think you answered your own poll. So what is uh your favorite super feature In Postgres.

Speaker 1: Yes. Yeah, my my favorite super feature is uh of course full text search, also because it's the first Postgres specific feature I ever used and uh ever talked about in a conference like this one But working with Postgres, the Postgres module, I find out during the years that there are a lot of them. using all the features of Postgres can improve your project uh very much. So I tried in this talk to also present the other feature I I like and I use daily in working

Speaker 1: project. I hope also you now can use it or I found some new new feature you haven't used before. Thanks.

Speaker 2: I guess if nobody else is going. Um do you have you ever tried uh to use uh array Postgres arrays? uh instead to replace uh many too many uh true tables and uh do you think that people would be interested in such a feature in Django as a library

Speaker 1: So um your question is to replace a many to many table with a ray field. It's correct.

Speaker 2: Yes, so you you have uh you have uh an array field in each of the end table and using a trigger Whenever the true table updates, you can update uh the array fields and that way uh you can use those array fields in query instead of doing joints uh through the true table.

Speaker 1: Yeah, um we use in work project refills when uh the data the multiple data we need to add to a model don't need to have uh other um information on it. For example, like if you have um an user and you want to store more, I don't know, email field, phone numbers. but she don't really want to store other information about it. So it's way more convenient. You can also query one table and avoid to to join other true tables like many

Speaker 1: many too many um fields and uh get it and send I don't know in your template or in your REST REST API So we found it very a speed up when these data are not crucial or not necessary to add additional information. In this case you need a more complex structure. For example, I don't know a JSON fleet or another model. But usually it's it's worked very well.

Speaker 2: Thank you.

Speaker 1: Thank you for question

Speaker 3: I have also a question. Um it's uh about the JSON fields. Uh is it Uh does Jung also or or like uh PsychopG also provide like special integrations to neatly work with uh those JSON fields and puzzles

Speaker 1: Yes, uh as I said in the presentation about um the Postgres module in Django, um the JSON field was initially introduced with um for Postgres only and for five years you need Postgres to use the JSON field, the official JSON field. Now they migrated for all the other um backends, database backends, but you can still use uh with Postgres uh using Psycho PG and uh um using the JSON B data type that are in on Postgres. I use

Speaker 1: very frequently to store various types of data and it's very efficient And in as I said in the in the in the closing slides, you still have some specific uh indexes. to index json field on Postgres and also specific aggregated aggregate functions to use on uh Postgres and choose on Free D Postgres like uh arreg rate and uh similar so uh Now it's more portable the JSON field. You can also use on Sibyl Lite and MySQL, but I think still in Postgres it's it's faster and they perform better

Speaker 1: and so it's very convenient now to to use it and I think in the next release of Postgres Postgres 14 they will improve uh uh a lot so I'm waiting for for using this in new new speed up new new performance

Speaker 3: Yeah, thank you.

Speaker 1: Thank you for your question. So I don't know if you have ever used the the uh some feature from Proscus feature directly in Django and what is your favorite Or if you are using other database backend want to try Postgres

Speaker 4: Um one feature I've recently used that I was really new for me was uh foring tables. Uh and uh I don't think there are many libraries that handle that very well. But you can you can do it kinda with Django and I'm using it on the Django app. But uh there's no like direct support right for anything in Django Do you know of any libraries or any helpers for for stuff like that?

Speaker 1: No, unfortunately I haven't found any Django packages to support that. But like other feature you have in Polscl you can't use directly from Django, unfortunately. But they they they are in um they are in improving year by year so maybe someone

Speaker 4: exactly

Speaker 1: yeah there is still some feature very interesting we have to use directly with SQL Uh but I think it's only when you really need uh very fast performance. So or feature specific very specific and everyone are are free to open a pull request to the Postgres module And I did it in this it's very uh welcoming environment, so everyone uh that use particular feature in Postgres can do that and can share this knowledge with other developers. So I

Speaker 1: I would do to encourage to do that if you use it. Okay. I don't know also if there is people that use other database backends that are not available for Postgres. I'm very curious about that.

Speaker 2: Regarding the the previous question, I guess my favorite uh feature in Postgres, uh and also exists in other uh backends, but not all, uh is the trigger feature. Um and I use it a lot. I put them in in migrations uh in my Django projects and I use that a lot.

Speaker 1: Yeah. I use those also for um uh for updating um search vector fields for a full text search and was very convenient to use trigger to update this column every time other uh column um updated but today i'm i'm trying to do some researches and using generated fields this type of field that are not available in the Django FRM but I think it would be a good addition and can resolve some uh some type of problem you now have to solve with

Speaker 1: trigger so a good one thanks for for sharing Okay, I think the right now there is another uh another um talk going on so if you don't have any other um question saying goodbye and joining the Githertown so you can ask other questions over there. Thank you again for listening and for asking question Fight.

Speaker 3: Let's hear