A pythonic full-text search with Paolo Melchiorre

Speaker 1: Hello. Hello everyone, and thanks for having me. I'm very happy to be here with you at DjangoCon US for the first time. So thank you. I want to tell thank all organizers for making this conference possible and you all for being here. And now, if you are asking yourself what is a pythonic for text search, I'll show you an example. This is the search function in the Django website Raise your hand if you ever ever used this search function. So almost all of you. The search function is based only on Postgres and Django. And I was the one who built it five years ago. So the next question is

Speaker 1: who am I? I'm Paolo Macchiore and I'm the City of TwentyTab Hotonic Software Company based in Italy. engineer, longtime Python backend developer, and after using Django for a few years I became a contributor to the project. I also like attending conferences, taking pictures of talks and then tweeting about them. So Unfortunately, I cannot do this while I'm presenting my talk. I want to ask you to help me to carry out an experiment today. It's the first time I tried. So please take photo during my talk and post it thanking me. I want to see how many different moments of the talk you can publish

Speaker 1: and I promise I'll retweet all of them. Thank you And now I want to try to explain a bit more about the title of this talk. So I think you can read the definition of Pythonic by entering import these in the Python interpreter. These are only the first principle from the Zen of Python by Tim Peters. The most important for me is the third one, and I think it's also the most difficult to follow. For the second part of the title, we can read directly from Wikipedia its definition. Full text search refers to the technique to for searching a computer stored document in a full-text database. There are a lot of search engines that already provide a full-text search implementation as

Speaker 1: year is defined The most popular search engine library is known and is Apache Lucene, an open source software written in Java. Based on Lucene, there are two very popular search engine platforms that I used in the past. Sol, which is part of the Apache Software Foundation, and Elasticsearch, that is a product of the Elastic Company. We can say various things about external engines. On the good side, they are very popular, they have a lot of features, and you can find a lot of online resources about them. On the bed side, you can always need you always need a driver to use them from Django.

Speaker 1: They have their specific query language and it's common to have synchronization issue. This is a simplified diagram of the data flow from the user passing through the database and then to the search engine. The synchronization phase can have various issues and delays because the data just inserted in the database may be not immediately visible in the search engine when we perform a query on it. So why don't we search directly on the database? We can use a big one with the lasting memory, Postgres. Postgres is a very popular, popular and

Speaker 1: lasting database. It added full text search in the core years ago with specific data types, special indexes. And since then, many new useful new features have been added every year until the latest version. The main concept of full text search in Postgres is the document. A document is the unit of searching in a full-text search in a full-text system. For example, we can search for a magazine article or better, the union of some of its part. Another example of a document where we can perform a full text search is a Django documentation page, for example.

Speaker 1: It has several different parts that we can use to build a search document. For example, the title, the body text, the table of contents, the parents page, and so on. But building these documents and implementing a full-term search directly on the database can be a low-level task. To do this, we can use instead a web framework which already has support. for Postgres Footer Search. Of course Django. Django added Fulter Search support few years ago in the country Postgres module with specific fields, expression and function. Also in this case, since then many new useful features have been added every year until the latest version

Speaker 1: In the Django documentation, there is the definition of document-based search as a full-text search with advanced feature like weightening, categorization, highlighting, multiple languages. And we can implement all of them with Django itself. To better understand how the full text search in Django works, we are going to see how to perform some queries from the basic one to the more complex one. Seven years ago I created this repository with some code to run all these queries we are going to see now. I'll share the link at the end of this talk so you can try them on your own. Okay, we can use the block models as defined

Speaker 1: the making queries section of the Django documentation. Here we have two classes with few fields, an author with a name and an entry with headline and body text, and also a connection with the author. We can perform basic query on this model using standard field lookup. For example, we can search an author using the extra part of its name. And we can perform a case incentive query uh to have more results using the worried case But to use the advanced search feature I'm going to show you, we have to add the Django Country Postgres module in the installed app setting of your project.

Speaker 1: The first thing we can try now is Trigram, to have results also when we don't remember correctly the name of an author. We have to activate Trigram extension to do this. And then searching for an author we can have results with similar but not identical names Here you can see the trigram similar lookup. We can also perform a full text search on a specific field using the search lookup. It's very easy. For example, we can search for a word in the plural form and have results in the singular form. But also the search engine will ignore very common words in your search query, for example, article in English.

Speaker 1: and similar. But we can search a text in s in more than one field using the search vector function We can define our document in the sense that we said before as the union of the body text and the headline of the entry model, for example. After that we can search for a word and have more results that can match the query in both the model fields To search using a more complex query text, we can use the search query expression. We can also use common search syntax directly in the query using the web search type. After that, for example, we can perform a search query for a word

Speaker 1: removing results that contain a second word, having potentially a more precise result. To perform a full text search in a specific language, we can use the search config expression. We can specify the language in both the document and the query. After that we can have more precise search results than before in the language specified. And Postgres supports almost 30 different languages. If you also want to list relevant results first, we can use the search rank function. Based on this query text and the document, Postgres

Speaker 1: will calculate our rank number. We can use this annotated rank to order the results and also to filter them. To perform a fine-grained full text search, we can use the weight attribute of the search vector function. For example, we can decide that words in the entry line are more relevant than words in the body text. After that we'll see a new rank number in our result performing the search with the same query text. This is a very efficient way to incrementally improve our search functionality based on the search result we see.

Speaker 1: We can also highlight the search result using the search headline function. To do that we have us we have to specify the field we want to highlight. we can filter and highlight at the same time having words highlighted in the filtered search result. Postgres will highlight all search query matching words and also variants on them, for example, plural or singular form. So we have seen various features of the Postgres Fulter search with Django, but annotating the search documents on each query can be an expensive operation for your database.

Speaker 1: To speed up the full text search we can create a GIN functional index in the entry model. From this moment the search will use the functional index created to carry on Exactly the same search performance before but um everything will be faster and also the workload on the database will be lower There are cases in which however the functional index may not be sufficient. For example, when we want to add fields of our related model to the research to the search document. And to do this we can create, we can add a crear vector field directly in our model

Speaker 1: And we can activate also an index on it to perform fast searches. In this case, however, we have to manually update our search vector field before running a new query. The update query is not obvious, but uh after its definition we can uh demand its execution by a trigger or in a cron job for example and it can be work that we don't have to do manually So as you can see, the search query performance is very simple now because we have a search vector field and is

Speaker 1: at the same time very very fast. I started using the full text search in Django 1. 10 when it was released for the first time and used frequently the Django documentation search to find information about this function. I started asking myself how was implemented the search function in the Jagu website itself. I noticed that the search was performed at the time only in English contents and in some cases there was raw HTML tags in the results. So I studied the Django website source code and I found out that the continuation was generated with Sphinx and although Postgres

Speaker 1: was used as a database, the search were made on an external search engine So I propose to fix that on the Django developer main list. A lot of Django developers share different opinion about my proposal. The main dubs were the amount of work to be done. the equivalence of the search feature and the database workloads. The safe thing were less maintenance, a lighter setup, and the exclusive use of Django on its own website. This is a photo of me at the DjangoCon sprints I organized during the EuroPython 2017. I proposed to work on the Django project website, trying to use the full text search based only on Postgres.

Speaker 1: And at the end of the day, we had created a working proof concept of this search. But in the following months I wrote an official pull request with a complete working version of the full text search. I received lots of suggestions from other developers and after a lot of comments they merged my pull request. and was the first of many other pull requests. This is a Django documentation page and these are the parts we are using now to build the search documents. You can see that each part of the document has a different weight to build the ranking of the results And this is a small adaptation from the Django

Speaker 1: project code where you can see definition of the search document We have different weights for different parts. We extract the language configuration directly from the model field and most of the parts are extracted directly from the JSON field which contain the documents generated directly by Sphinx. So today the Django website full text search is multilingual, it's based only on Postgres and return clean results. It's a low maintenance solution. It's way easier to set up than before. and also support a web search syntax. Here you can see an example

Speaker 1: or a lot of this feature. For example, we have done a search in French in the French version of the documentation using the web search syntax. There is a word, a exact phrase and a word we don't want in the results. And there is also a proof of concept of highlighting. It's a pull request still opened in the in the repository. Currently the Django Project website search support 28 different languages thanks to Postgres. This is the complete list. However, only few languages in this list as translation of the documentation, the one highlighted. We need the help of all of you to translate the Django documentation into many other languages.

Speaker 1: So please join if you know one of these languages and join a translation team and start translating. As I already said new full new full text search feature are released every year in both Postgres and Django and I hope to add all of them in the Django website search like misspelling support, search suggestion, search statistic, autocomplete and so on. Okay, now before saying goodbye I want to share with you some tips based on my experience when using full text search in Django. First is read the documentation in the Django website because it's full of information about all the full text search features you can use.

Speaker 1: The second is read also detail about full text search in the Postgres website because it helps you a lot to understand how thing works at a lower level. Read the source code if you can of both projects with GitHub because there is something that you can find only in the source code. Last one is search for question on Stack Overflow, not for answer. Try to answer questions by yourself instead of reading them because it's a way to improve a lot. The last is you can also study this presentation because you've released with the Creative Common license

Speaker 1: I hope I've been able to show how it's possible to develop a more complete full text search using less software in your stack. Do more with less is the motto of 20Tub is our version of Pythonic. And we have developed many Django projects using Postgres and Django, and you can find out more about open source project we had and Pythonic work using this context. And you can find also um my in my context what I do in this in this field and using this QR code you can download this presentation directly from my website and all the links uh and other um reference to the to the repository.

Speaker 1: But before saying goodbye, I want to thank all of you attending my talk, the other speakers for their interesting talks. and also thank the organizer to make this conference possible. But in particular, I want to thank all the volunteers for all the conferences I attended. Because each of them has allowed me to grow as a Django developer and to be a better member of this community. Thanks again. Grazie.

Speaker 2: Thank you so much for that. Uh we have a couple of minutes for questions. I'm going to start off with a question. You support Postgres 's full text search, but is there anything weird that you found with Postgres ' implementation? at all and have you worked out a way to get around it? There was a question on Twitter about TS A vector. Is that something that you've had fun with in the past?

Speaker 1: So there is TS TS search is how it's implemented under the hood in Postgres is the definition of the search. Yeah originally it was uh an external extension of Postgres and they they merged in the core from 8. 3 uh version I think. And so it's under the hood is the name of the the the search vector.

Speaker 2: Cool. Does anyone else have any questions?

Speaker 3: Yeah, I was wondering uh can you use uh you showed us weighted uh Weighted search on different fields, but also at the beginning you showed us uh the trigram, the the fuzzy search. Is it possible to combine both and have an index on multiple fields and also do uh fuzzy searching on those

Speaker 1: Yeah, uh it's what we do now in the exactly in the Django project website because sometimes people misspell words and it's easier to have the pre -commerce rank and the full text search rank. If both of them are very high, it's possible that we are found the right results. So it can be done and we are doing this.

Speaker 3: Okay, thank you

Speaker 4: Um regarding ranks, uh why are they limited to four?

Speaker 1: It's why it's the implementation uh in Postgres. Actually if you read the documentation in the Postgres as suggested, there is a way to improve that part to customize. the way that rank corresponds to a value and for the most cases you can rely on the default but if you have special needs you can Go there and create your special configuration and create your special ranking

Speaker 4: in Postgres

Speaker 5: there's a contributed um index type called ROM for full text searching. And I'm wondering if you've ever thought about having that be used in the Django full text search.

Speaker 1: I uh don't understand the first part. Where is implemented in?

Speaker 5: All right, so one one there's a contributed module to Postgres called RUM And it's a special index for TS vector. And it's it's a bit fat it takes up more space, but it's faster, and that's much faster for ranking. And I'm wondering if you've thought about having it In the Django full text search.

Speaker 1: Yeah, and I know about uh room, you say okay. The this is uh um an extension developed by the main author of uh the full text search in Postgres many years ago. And it's very well and it's a great extension it worked very faster. I tried it but I'm waiting they have more stability before uh suggest to support also in Django if someone other one can be can try to open up a request and do that

Speaker 4: Hi, thank you for covering search and Django and Postgres. It's actually a qu I think it's a really cool topic. Um

Speaker 1: Thank you.

Speaker 6: So when I was reading through the Django

Speaker 4: documentation trying to set this up a couple of months ago and I this might have changed, there was a reference that Django

Speaker 6: made or the documentation made to that you have to go and set up triggers in your database in order to keep your search vectors updated. And that was a uh I don't know, I just like when I heard I had to go set up like set up triggers like in the database outside of migrations, I it I wasn't sure what to do with that. I was wondering is that still a thing or is that something that you've had to deal with?

Speaker 1: So you your question is if you have to set up that trigger to update your you only need to do that if you create a search vector field. If you rely on the functional index, it's not required. You can define your index and it updates automatically. But it's the case for a lot of uh situation like for example Django project uh we have a search vector field but only because we up to date it when we um J we are going to generate from scratch the documentation every time. It's something we do locally and then we deploy. But in other case you can uh rely on the functional index and if you have a very complex

Speaker 1: situation you can think to uh do a cron job that do it or maybe use a trigger there is different way to do it

Speaker 7: Thank you. So something that comes up often when I search through the Django docs is that I'm searching for a particular function, so say select related. And when I do, I click on it and it takes me to the um API reference uh for Quarry set, instead of like taking me to the section about select related. Uh would we need a like large refactor of how things are stored or is there a notion of like sub-documents or should we index in terms of sections instead to allow us to when we search for a method be immediately taken where it's mentioned in the document.

Speaker 1: Yeah I thought about it and I I think we cre when we create with Sphinx the documentation page we have a body text part. It 's uh HTML uh as you can see in read the docs for example and we put this um HTML uh document directly in the Postgres. But the Postgres full text search is able to extract and remove all the tag, HTML tag for us and search directly in the code, in the in the content, sorry. This is something very useful, but for having a reference to the exact part extra the extra

Speaker 1: section on the this document we need to improve the generation of the document from Sphinx. Maybe we can try to generated also in XML version and have a field directly in Postgres to navigate this content. to decide that we want we want to go in that part because when we um uh when we created our document is a vector with a lot of name have a lot of laxam and that's it. There is no we we lost everything about the HTML part before. So it can be done but it's not it's not simple but it's interesting. Thank you

Speaker 1: But before I want to complete the experiment and take me a photo of all of you. as I usually do. Sorry for the delay. So say hi. Hi. Thank you.

Speaker 2: Let's thank Carlo again.