 Hello everyone, this is my first English talk and they have put it in the schedule just a few days ago So excuse me for reading some notes. Anyway, I'm happy to be here with you We are here to find out more about full tech search in Django with Postgres My name is Paolo Melchiorre and I'm Italian. I'm a computer science engineer I am a big hand Python developer for more than 10 years and Django developer for about five years At the present I'm working remotely at 20 tab As a senior software engineer I'm not a database administrator, but I'm a loyal user of Postgres in all my projects With this talk I want to show you how we have used Django full tech search and Postgres in a real project The main reason why we used Django and Postgres based full tech search Was because we prefer to implement full tech search without any external tools These are the main topics of this presentation full tech search in general existing solution for full tech search full tech search support for in Postgres Django support for full tech search The project concertia roma.com the next innovation in full tech search some personal conclusion and Any question after the talk full tech search the rise from the needed to do some documents search For example to file all documents that contain specific word and its variation If a document contain house or houses, it will be the same for the search Some example of everyday use of full tech search are search engine document search email searching and so on This is a list of some feature that we can find in an enough advanced full tech search solution That can we use in a real project Stemming rankings top order move multiple language accent support indexing and free search Alexis search and solar are two solution for the full tech search that today are popular and used There are others, but these are the only ones that I have used in my professional project They are losing based and written in Java Snap market was a startup where I worked in the past and that produced a mobile phone application for selling by just used items in this project I used elastic that are already been set up on the system But we have some difficulty managing and synchronizing it We had to apply some patchy to Java plug-in that we used for the compound of words in German and I didn't particularly enjoyed it Another project was goldscout.com is a website dedicated to show Sport videos uploaded by public user and it has About 25,000 videos The use of solar for full tech search in this project was a customer choice we always had some problems synchronizing the data and At the end we prefer doing or writing on postgres and or reading on a patchy solar This solution that I've spoken about our full future and advanced there are many online resources regarding it documentation article Frequently as well question But I found some problem in synchronization and I've always had to use a driver To connect with it. It's a bottleneck between the Django and the search engine In some keys I have to fix the code Personally, I am a more adept than an ops so I don't like to be forced to integrate the value system I prefer developing a software problem writing Python code Postgres has been supporting full tech search since 2008 Internally it used TS vector and TS query to process the data into search It has some indexes that can be used to speed up the search gene and gist Postgres has added support for fresh searching in 2016 Okay, before we define the full tech search Using the concept of documents The document is the general concept Used in the full full tech search and where the search is done in a database a document can be a field on a table The aggregation of more field on a table or in different table The module Django point country point postgres contain the support for the full tech search The support for full tech search has been present in this module since the one point 10 version Instead bring engine indexes indexes have been added in ten one point eleven version The gene index sorry the gene index is very useful to speed up full tech search The use of postgres full tech search in Django is more developer friendly for me Okay, let's look at the function of full tech search in Django Starting from the models present in the search documentation the official documentation We have a blog and an outdoor classes connected from an entry classes These are the basic search that we can use on model in Django using Filter with contain in the first case In the second case using case incentive it contains In order to get more results We can activating the unaccent postgres module So we can use the an accent extension to search without worrying about Accented the characters is most useful in various languages also in Italian This involve a full table table scan execution and it's potentially slow Another extension is trigram Activating activating the trigram postgres module we can use the trigram extension a Trigam is a group of three consecutive characters taken from a string We can evaluate the similarity of two string by the number of the trigram. They share It's more. It's a lot useful, but Sometime is not enough This is the base search lookup of Django And with these we can execute a real full tech search on a field But is a real simple example We can use a search vector to search on more field on the same object object or connected object like in this case When we pass a text to full text search by a search query We can apply operation of stemming and stop word removing Even on the user texts and on these we can apply basic logical operation We can use also the postgres rank to calculate the score of a document relation to a searching text And then we can use it to filter and to sort it We can set up the search vector to execute the stemming and stop word removing for Language and we can get these languages also from a class field It's possible to set up the search to give a different weights on various fields and the use and We can use these values in the search for filtering or ordering We can decide to add to the model the search vector field to speed up the search It's very fast But we have to update this field manually for example using Django signal or with postgres trigger Okay, this is the project We are working on concertaroma.com It's a website to insert and to search for show festival bands venue All in the city of Rome At the moment the website has the following numbers Bar are growing up and the website is online since 2014 This is the version number two is the old version of the website was developed some years ago with Django 1.7 and it runs on Python 2.7 The data was managed by postgres version 9.1 and the search is performed only using SQL like syntax With filter and so on This is the version three is the new version recently released Was developed with Django version 1.11 and it runs on Python 3.6 The data is managed by postgres version 9.6 and the search user It's full text version search engine Let's have a look at an example of manager defined for the band class It define a search method that contain all the full text search logic It's more complex that than the before example To better understand the mechanism we can take into consideration example of a simple test internally defined and This test setup we define the example data that we will use afterwards to test our search two bands and two musical journals that we assign to the two bands in The search test on the bands we simply invoked the search method of giving a search text and We got back the list of values for the fields nickname and rate Nickname is stored on the band table while rate is calculated by our search method at runtime In this example, we compared The our search result with a list of lists where we define it the pair composed of the bands nickname and the numerical value that is the search rate or in other words the numerical value that defines the importance of that We have seen a simplified the use of current feature of Django and the postgres full text search module itself Both of these software are getting better in these fields and These are some of the feature that can be available in the next feature Okay, in conclusion the following under condition we evaluated to implement the solution Not having any extra dependencies not doing too complex search Managing easily or the components synchronizing data is not requested postgres is already available in our stack and It's present a python only environments These are the resources that I used to prepare this talk and to develop the search function I showed you and we used on the website. I would like to thank 20 tab the company I work for To have given me this opportunity and mark tamlin the original Django country postgres developer for sharing with Everyone is work. Thanks everyone for timing that you have spent listening to me This presentation you released With creative commons and you can download it from my speaker deck account after some hours If anyone has any question, please wait after the presentation and if you want you can contact me here Thank you. Thanks Bono for for your presentation. Hello really interesting talk I was wondering because in one of the slides if you got get a few slides back. You're showing the rating I'm not going to ask how exactly do we calculate the rating because it's I can imagine that's propriety information We're doing something similar, but I'm wondering can you speak? Oh, sorry. Thank you I'm wondering how much control do you actually have on the fact how the rating is Generate can you for example say I want to rate higher Words that are longer than five characters so that if they appear early in the sentence Can I put a higher value of this one? Sorry? For me, it's difficult to understand clearly your question. Can you come here and Can this is the search rank? Okay, let's say you have an example My list iris is playing a throne on in July, right? And someone is searching for Cyrus Will that be ranked high if you want to say oh, this is Cyrus appearing on the second as a second word in the sentence So if I find another sentence that it's appearing on the as a first word I want to run that one higher. Do we have that kind of controlling Postgres? You can control with the these other these other functionality Sorry The functionality is the weights at weight, but I'm searching okay You can specify the weights for some field and In Postgres you can specify For type of weights from hay to see 2d. Sorry, and you can change the number Related to these weights and Configuring a lot of these type of number you can have different type of weight of final weight There is also two different type to calculate the rank in Postgres TS rank and TS rank CD and that have different logic the first one Count the frequency of the word in your documents and the second one have a very complex logic, but it's more flexible and we use to Construct a search rector with 10 or 11 of these you I showed you in the example. So We worked a lot and this feels to calibrate and two settings Very low details the results in the search instead of Having some trouble and searching to fix the synchronizing like in other solution, but I think is It's advanced enough to To setting a lot Can you give us some more information about The difficulties you encountered in synchronizing the document database with the classic search engine like Solar or elastic search Thank you. Okay In both the project I speak it before Then there is there was a guy that managed to synchronize this Postgres database and the elastic search of solar Engine and he even controlled a lot of problem of of timing sometimes the the user want to flag for example never said flag a concert to to like and He have to wait The data came from the database to The other engine like to search and every time is a slow process especially when the the volume of your data grow up and You can you can have a very good machine to Put on elastic search for for example, but in this project This was a very little project so we don't have a lot of big machine to do that and with postgres we Don't don't have this problem at all because the data and the full-text search is in the same In the same PC It's good the last question to polo No So let's thanks again paolo for his interesting presentation Thanks, Paul. Thank you