 Welcome back, everyone. Hope you all are enjoying the EuroPython conference. I'm Anmol Sasteva, and I'm going to be the session host for the next two sessions. Let's welcome our next speaker, Paolo Malkure, who is the CTA of a Pythonic software company named 20TAP. Paolo is a seasoned Python backend developer and actively contributes to the Django project. Today, he is going to talk about using Postgres SQL as a database in Django. So, pretty nice. Where are you joining from, Paolo? Hi, I'm from Italy. I'm the central part of Italy, and close to the sea. Nice. Out of the weather in Italy. Yeah, it's very hot now, so inside, it's fresh. All right. Awesome. Over to you, Paolo. We can start. Thank you. So, hello, everyone. And I'm very happy to be here with you, even if remotely. In this talk, we'll see how to use some great feature of Postgres as a database in Django. In the database section of the Django documentation, we can read that the Django attempts to support as many features as possible on all database backends. However, not all database are alike. Django, per se, is a database and agnostic web framework, but real-world projects based on it are not. Postgres, as the richest feature set of any Django supported database, I will see in this talk how to use some of this superpower. Before moving on, it's important that I make this clear. Now it seems clear to me, so now we can move on. Chokes apart, I would like to underline that I'm not a database administrator. So, who am I? I'm Paolo Macchiore, and I'm the CEO of Twentytab, a bitonic software company for which I work remotely. I'm a software engineer and longtime Python back-end developer. After using Django for a few years, I became a contributor to the project. I also use the 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 tree stable, create and activate a virtual amp, in which then install the latest stable Django. Then using the Django start project command, I created a basic file of our project. So let's see what it takes to add Postgres to this newly created project. I think many of you are familiar with this drawing from The Little Prince. This drawing is used as the header of the Twitter account of PsychoPG, a Postgres driver for Python. I think it represents its goal very well, Python with Postgres inside. PsychoPG is the most used and advanced Postgres 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. PsychoPG is a wrapper for libpq, the Postgres C client library. To install this package on a Debian-based system like Ubuntu, you can use the Happy Tea package manager. For most operating system, the quickest way to install PsychoPG is using the package available in the Python package index. And now let's see how to use PsychoPG in Django. To use Postgres as a database in our Django project, we modified the settings, heading the PsychoPG-based database backend and the connection parameters of 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 directly or Django configuration, your databases section should look like something like this. 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 only use an auto model and an entry model, both containing values type of fields. So we can search on. We can perform basic queries like this on our model, but actually we can run these queries using all other supported database as well. What we're really interested in is using Postgres specific feature from Django. For the same reason in 2014, Mark Tamil, a Django core developer, started a co-founding campaign to develop a module to contain fields for a number of Postgres specific data types. The campaign was a success and the new module was merged in Django 1.8. The module now contains Postgres specific fields, indexes, function, extension, and so on. Over the year, important functions have been added, such as JSON field, full text search, random UUD, and operator classes. JSON fields have become usable also in the other supported database, but only from Django 3.1 released last year, five years after being introduced in the Postgres module. So to use all the feature of the Django module, we just had it in the installed apps in the setting file of our project. And now let's get to know some feature of this module better. Okay, I took this photo during the spring day after the DjangoCon 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. The database function I'm talking about is random UUID. The random UUID database function returns a version for random UUID. It's contained in the PGCrypto module that provides cryptographic function for Postgres. It can be activated using the crypto extension immigration operation. And from Postgres 13, this function is included in core. To see the function in action, we'll add the UUID field in our entry model. This fields uses the related Python module and only when used on Postgres, this store in a specific UUID data type. The database will not generate it for you. So it's recommended to use the fault. But not that the UUID for callable is post. It passed to the fault and not an instance of it. Using the Postgres function, you can update all the values in a model way faster than cycling over all the entry and generating a new value for the related Python function. I recently used this technique to set in few seconds UUIDs in a nearly one million row tables. Pretty impressive. I took this other photo during the spring day after your Python 2017 in Rimini. I promoted the working group on Django and some developers joined me. That day we started the transition of the Django project website search function from Elasticsearch to Postgres full-text search. Since then, I've written an article and given more than one presentation on full-text search with Django. So I skip the implementation details. The full-text search support in the Postgres module has specific fields, expression, and function. If your Postgres version is recent enough, you can also use specific indexes for our search or web search style. 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 the singular form. This is a very convenient way to start using the Postgres full-text search out of the box. But to speed up the full-text search, we can add a search vector for the entry model and use it to create a functional gene index on the same model. The functional index are an addition of Django 3.2, available for all Django database backend. But gene index is only available in Postgres backend. After that, we can search for a word using a syntax similar to the one used by web search engines and have more accurate results. We can use these syntaxes 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 Northern Europe, more precisely in Norway. I took this photo because I really liked the effect of these typical houses on the water, 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 size can be defined and it can be even multi-dimensional. For example, we can store multiple email in our outer model by defining an array of email using the email field as a base. We can then query our authors looking for an email. The content of the field itself is represented as a list. The resulting SQL code uses all Postgres-specific operators for array. Unfortunately, the default array widget in the Django admin is a simple input text with comma-severated values, but using this Python package, you can represent in the Django admin the values as a multiple dynamically-headed input text. It's better. Okay, I took this photo in San Francisco. We are now virtually moving 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 or organization across America. Django Postgres copy is a Python package to quickly import and export the limited data with Django support for Postgres copy command. The copy command moves data between tables and standard file. Copy to copies the content of a table to a file and copy from copies data from a file to a table. To more flexibility, Django Postgres copy use 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 name 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 Postgres copy. Here we use the file with all the geographical names of Italy and the file is more than 200 megabytes. To upload the file, we use the specific QuerySet method to which we pass the path of the file. The loading speed is impressive, almost one million records in just over three seconds. Impressive. Under the hood, Django Postgres copy executes several SQL statements, creates a temporary table based on the content of the CSV file. Upload the content of the file to the temporary table in just over two seconds. It insert the data of the temporary table into the table managed by the featured Django model applying eventually some transformation and finally drops the temporary table. To reduce disk space and transmission bandwidth, we have compressed our file in a gzip format reducing the size to a fifth. We can pass our compressed file directly to Postgres copy without having to decompress it. Loading is done in a shorter time than before. I want to repeat it. Almost one million records in just over three seconds. We are now virtually moving back in Italy with this photo that I took in a virtual in a Brutso, the region where I live. I'm showing this photo because now we are going to talk about tree, 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 simpler and faster alternative to implement materialized path compared to the more used Django packages. The package has a path field and an abstract tree model. To add tree-like hierarchy to the entry model, we add a path field inheriting from the tree model provided by Django L3. We also add a dedicated Postgres GSD index on the same field to speed up the 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 L3 documentation. We perform a hierarchical query to filter all the contained models of a particular path, sort the result by the tree structure, and then take all the subpaths. The resulting SQL statement uses the L3 operator to filter the table and the GSD index to speed up the operation and sorting process. Everything is really fast. With this photo, we are now virtually moving on the path of my last hike on the Italian Appinines. I already used this photo on my latest hardtool series 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 Django. PostJS is a Postgres extension and it's also the best database backend for Django. It internally integrates special data and has special data types, indexes, and function. In this chart, I've synthesized the compatibility table of geographic backends supported by Django. In the Django official documentation, there are three compatibility table, one for special lookups, for database function, and for aggregate function. As you can see, PostJS is the only geographic backend that supports 100% of these features. If you are interested in using this feature, you can read my article about it. I'll share the URL at the end of this talk. There are also many other Postgres specific features that we can be used directly in Django. For example, you can use a lot of indexes and aggregation function that are only available in Postgres. You can also use the Trigram extension to perform fast searching for similar string. There are also specific fields only available in Postgres, like range fields and case-insensitive text fields, and more. Before saying goodbye, I want to share with you some tips based on my experience as a Postgres user with Django. The first one is to read the documentation in the Django website, because it's full of information about the Postgres module features. After that, read the details about this feature in the Postgres website, because 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 source code. And at the end, search for questions on Stack Overflow, but try to answer questions by yourself, instead of reading the answer. Last but not least, you can also study this talk, because it is released with the Creative Commons, with a Creative Commons license. The Psycopg3 library is under active development, and you can use this contact to learn more about it, get involved, and also sponsor its development. The company work for 20Tabby is one of the sponsors of this library. In 20Tabby, we have developed many Django projects using Postgres. You can find out more about our open source project and our Patonic work using this context. And finally, to find out more about my personal work with Django and Postgres, you can use all these my contacts. With this QR code, you can download directly this presentation on my website. Thanks again for letting me enjoy the next talk in the conference. Ciao to you. Thanks a lot, Paulo. It was a really informative and well-structured talk. Yeah, one question. Thanks. Yeah, so let me read it out for you. Can you please share the link to what you mentioned about post-GIS? Yes, in the talk, on my website, you can find the link to the official documentation Django, where there is all the graphic, the table for support of feature in Django back end and about post-GIS. All right. We are actually having a few more minutes. So if anybody has questions, please put it on the channel, metric channel. Else, Paulo will be happy to answer the questions in the breakout chat. Paulo will be happy to answer the questions in the breakout meeting room. Yes, of course. Yeah, you're having a few people typing. Let's wait for a minute or two. Yes, I'll share also the link to my talk in the chat. So if someone is interested, can read directly without using the QR code I shared before. So it's easier. Yeah. All right, I think we are good. So thanks again, Paulo, for joining in sharing your knowledge. It was really a pleasure listening to you. Thanks for having me. Yep, thanks. If anybody has any more questions, please feel free to reach out to Paulo over the breakout meeting room. Yep, thanks. Bye. Bye.