Full Text search in Django

Speaker 1: All right.

Speaker 2: So

Speaker 1: big hand and welcome to Marco.

Speaker 2: Thank you very much. Hello. Yeah. Thank you, Benton, the organizers for organizing this amazing event and the other speakers and everyone really. So excited to be here today. Yes, I'm Marco. As you can see from my accent, I'm Italian originally, but as I say, I live in London, where I work for A company that produced a platform for TV and movie production. And as I was explaining, I co-organized the Jungolando meetup. Again, please have a look on meetup. com. Currently we're doing online events, all for free. And yeah, play Dungeons and Dragons. Uh is there anyone that ever heard of Dungeons and Dragons in the room?

Speaker 2: Yes, few of them. Okay. So, why search? Something important. Well, we should have a talk about it. Uh, we live in a mode of information, of data, and every day we publish, access, um A lot more information and we need a way to navigate through all this information. What are the systems that we use for that? Well, we have categorization. We have humans that can categorize data or vertically using system like categories or horizontally like tags. But this is a manual operation

Speaker 2: that It's expensive. You need to have physical people, physical human operators there reading and deciding what goes in which bucket or not. And it's subject to bias because if I have two or three different operators, they are gonna categorize things in different ways Nowadays we talk a lot about machine learning. They can use some system to do this automatically. Well , reality is that it's still very expensive to set up this kind of system and train that to categorize data instead of human. So full text search is sorry. Uh full text search is still the best solution in these cases. It's easier to set up than a machine learning system to auto-tech

Speaker 2: rise, it's cheaper than human organization. And It's a tool that users are familiar with. Everyone uses Google or Spotlight or Mac or a lot of different things. So yeah, I divided search and Django in four different categories according to different approach, and I connected them to four classes in Dungeons and Dragons. I'm gonna have a quick explanation of what a class is in a second. So yeah, today I'm gonna do a tour of this four approach and explaining why you should use one and not the other. They are all valid, but in different contexts. So let's start with the first one.

Speaker 2: Standard textual queries, the fighter. So when you are a new player of Dungeons Dragons, you start, you have to learn the game. Part of this rule, they are the same for every player, but every single player chooses a class. a profession if you wish. The fighter is usually a new players friendly profession, class, because it's easy to get started. You have a spore, the shield, you it with your spore, power with your shield, it's simple. And the same way, standard textile queries is the easiest way to start doing text search in Django. It's a simple uh it's a simple check if uh a series of character

Speaker 2: string is present into another string You don't have to do any setup. If you have Django, you will have one of the databases, PostgreM, MariaDB , Mongo , sorry, Oracle, and it will just work automatically. What's the downside of this is well fighter in Dungeons and Dragons is very easy to get started with, but it doesn't go that far. Start like eating with this wood at the beginning, but even after a while. He's still doing the same thing. Same for standard text wall uh queries. They are very good to get started. Ideal for an MVP, but eventually they are not gonna do much more than that for you.

Speaker 2: They can tell you if it says string is inside another string. only that. Uh they are not gonna be able to tell you how many times it's there is a match for each record You will not be able to do any kind of ranking with this kind of search and no spell check. If you misspell something, there is not gonna be maybe you will look in for. There's just like no result How Django implements standard textual queries using three lookups, the first two available for every database, the third only for Postgres Base one is double underscore contains the normal

Speaker 2: text comparation. It's gonna check if every character is that. And this is the default and is case sensitive So is gonna differentiate between capital case uh and lowercase letters The second one, uh double underscore i contains, um, is case insensitive, that's where the i stand for, and it's not gonna make a difference between them Uppercase and lowercase. The third, an accent, is available only on Postgres and is gonna do accent folding. So it's gonna check letters regardless if they have accent or not.

Speaker 2: So for instance I have here in this example an N with a tilde that is a Spanish and I think Portuguese also. Character, this is also gonna match a normal N. So, yeah, what's a good use case for standard textual queries? My opinion, filters in the admin interface. It's an excellent use case. You can implement that quite quickly and your users are internal users. They're gonna use the admin. They are gonna have um Some knowledge of the data set of your platform and of the platform itself. So even if you give them a tool that is not that flexible, they are gonna still make uh they're gonna still use that properly

Speaker 2: What's a bad use case for standard textbook queries? If you need something more complex, if you want something for a sterile user, for instance. If you have a website with a lot of articles and you want to allow users to search into these articles, this is not going to work quite well for them. So for the next one we have the wizard. External services. Usually when you talk about Search in Django, the first thing that you hear is elastic searches. You had a couple of conversations with people today and Copenhagen as soon as I say, oh, you're gonna talk about Elasticsearch. Yes, but it's just one of the external services that you have. I put them all in one bucket and I'm gonna treat them as black boxes

Speaker 2: So from Django point of view, you give them data and you get that answer to your search queries. But yeah, I'm not going to make any differentiation now that working internally. Why I said that this is the wizard? Because for me this is the exact opposite of the father. Wizard is very hard to get started with in Dungeons and Dragons You have to learn the basic root system but also the magic system that is huge, different school of magic, level of magic, understanding what means that you have prepared the spell or that you know a spell. um it's it's very convoluted and external services also they require an initial development

Speaker 2: uh you have to connect that with your platform you are increasing the uh complexity of the architecture now all of a sudden you have another piece Besides your database and your application, you also have this search engine and you have to manage different copy between production, development, and staging if you need to. It has to scale, maybe scale in a different way from the rest. And also the system, they contain a redundant copy of your data, and you have to manage the fact that now you have two copies of your database and you keep them in sync. Uh what are the pros why you should do all this work? Well, because once you've done it, it's amazing. When you have a powerful wizard in Dungeons and Dragons, you can do whatever you want.

Speaker 2: You can have like fire raining from the sky. You can like turn one of your light in a giant you can fly be invisible. And for external services you can do a lot. You can manage millions of records. This is actually uh quite easy even with uh cheap hosting this kind of system they easily manage big numbers they have a lot of different features spell checking for instance faceting weighted search um spatial ranking system. So yeah, external services is the high cost, high value solution. You go for that when you have development time to invest, but you won't you will have a huge return from it

Speaker 2: How does this system work? As I say, they are different, but there are some similarities between them. So, first of all, you have to define a schema of your search index. This is You can see that that's the model of the search index. Then you have to index all the existence record that you have in the database in the system. You usually have like a salary task to do that and you just go through everything. And last but not the least, you have to keep up to date every m changing your database with the system. Django has signals like post save and post delete that can be used for that. So you can register specific event to

Speaker 2: being called after a save on after deletion of an object so that you can use that to update the copy that is in the search index. And Chris was mentioning like five minutes ago that there is another tool that I didn't know. that actually can connect your Postgre database with um uh directly with Elasticsearch. Um maybe we can talk about this later on Zule uh learn this today. Yes, so what's a good use case? As I say when you have a lot of data, when you have millions of records, or when you have uh very big requirement around search and uh when it's a central part of your application

Speaker 2: A bad bad use case is when you have constraint about development time, if you need to release something quickly. This is not the best way to do it. Same if you have constraints about architecture complexity, you don't have dedicated team that is helping you with uh DevOps and system administrations Uh this can be a breakfair for you um day of breaker for you. Next one is the Bart. The Postgres full text search. As the link said, it is only available in Postgres Who is a BART? A BART is one that sings

Speaker 2: and talks. They solve problems to diplomacy and deception. Uh yeah, they're talking guys. They go there and instead of fighting, they try to find a solution with words. What's their weakness? Well, you cannot really use words. You the other person doesn't know the language that you're speaking. Same with the Postgres full text search. It's language dependent. Sorry. It's language dependent. You use a series of dictionary built into Postgres And they depend on any single languages. So you will have a set of dictionary for English, set for Danish, a set for Italian, and when you try to use the incorrect one, you will have terrible results The advantage is that you have a kind of a semantic search, advanced ranking system, you can search in more than one field

Speaker 2: at the same time, giving different weight to each field that you're searching it. And it's extremely configurable. This is a solution that is almost at the same level as an external service. It doesn't have the same performance, but like most of the functionalities are there. So how does this work? So Postgres. Is gonna take whatever you want to index in your search system and normalize the words, remove stop words, and then apply frequency score for each word. Let's make an example Let 's take the Django tagline, the web

Speaker 2: framework for perfectionists with the deadlines. If we ask um Postgres to create a search vector for this , In our Django application, Django is gonna do this for us, so don't worry, you don't have to write SQL. First, it's gonna normalize it. So that line will become deadline. We have removed the final part It will remove subwords, D, for, and with. They add no more meaning to the phrase, so they will be removed and ignored for the search, and then it will apply a frequency score to each word. using uh how frequent is that word in the English language. So that line for instance is less frequent than what

Speaker 2: As I say, this is language dependent. I'm specified that I want this process to be done for English because If I ask the system to do, if I ask Postgres to do the same operation with the same string for three different languages, I'm gonna have three completely different results As you see for English, you have what I just showed you. For Italian, it is not gonna normalize the word deadline because Italian has different rules about plurals And as the word for is not a stop word in the Italian language. Same for Danish. In this case, I think I removed the for word , but uh

Speaker 2: he left the and with so yeah uh you you have to specify which language are you using on the other hand you can get a lot of interesting things. For instance , you will have a better ranking if you have multiple matches In the same record. So if you have a long article that is talking about Django and you search for the word Django , Postgres is gonna be able to tell you that it found the word that you were looking for more time in this record. than in another record and giving a different rank. It's gonna match by proximity if you're uh searching for website as two

Speaker 2: words. Um if when these two words they come together, they are gonna match higher to refer these two words come into different parts of the sentence. And you can specify the importance of the field. All of this, just configuring Django. And by the way, this doesn't require an external library. This is all built in in Django. So, what's a good use case? Searching the article content that it was the bad use case for the standard text or query. In this case, it's an excellent use. Use of this one while a bad use case would be searching in a list of movie titles. This is very specific. Why so?

Speaker 2: Movie titles are in general names. Because the normalization process only works on words that are part of a dictionary. If you take people name, movie title, uh, artist name, and band 's name. uh you will likely find words that are not in a dictionary like recently last Christopher Nolan movie Tenet is it's not gonna be in a dictionary And so the normalization process is just gonna ignore all these words. Now, last but not the least, the barbarian. The barbarian in Dungeons and Dragons is a big guy.

Speaker 2: He's big, fast and strong and is angry usually. Goes around with that. Juan's word or you jacked and shop things enough first and then he asked question. I try to think what's weak about the word baron. I cannot think of any weak points, to be honest, but there is one actually. They cannot read or write. They are limited in this. You could argue that they don't really need to, but yeah, they can't. Postgres trigram, this is another feature that is specific to Postgres and it works in a different way from the full text search And is language independent. So it technically doesn't read. You consider each character as an independent entity

Speaker 2: This is a big strong point because that's not a delimitation of the full text search as. And it also will work very well in case you misspelled something Because if you change one character, it's still gonna compare all the other characters in the strings and it's still gonna give you matches. On the other hand, if you're looking for something a little bit more semantic, you have uh large taxes. And when I talk about large taxes, I'm talking about the size of each record, not the number of records that you have. This can be less precise than uh full text search. How does it work? It's very mechanical. It's 3 grams

Speaker 2: for 3 gram. We'll explain what that means in a second. So first Postgre is going to pre-pend and append space to each string that he wants to use in the search index. Then it's going to divide the string in group with three characters. The title are three grams. Then the filter the list of three grams is going to be filtered for duplication and order rhythm And only at this point it would be used for comparation. It is a bit complicated, but let's make an example just to understand. The string Django, for instance, you have Uh Postgres it will take two spaces and prepend to Django and then a

Speaker 2: pand uh another space. And then we start taking uh three characters from the total frame. So the first trigram will be space space D. Second one would be single space DJ, then DJA and so on. This would be the entire list. So this point he will uh order this list for You will order this list and remove duplicates. So what you end up with is what you can see in the slide. It's quite unreadable, but there's a three -gram version of the string Django At this point, Postgres can use it to compare and search

Speaker 2: and use the number of program that match between two strings to give a distance of the two strings. Let's make another example. If you search the string Django in the other string, Django is a word. A web framework, Postgre is gonna apply the same transformation to both strings and uh then check The number of trigrams that are the same and over the number of total trigram that you have in both this in both these strings, and then give you a similarity score that is just like the proportion between. uh trigram

Speaker 2: that are the same with um uh trigram uh the total trigram of the street strings. In this case it would be like 0. 2 uh 27 approximating and the maximum is one if is exactly the same swing. If you miss Taljango in this case uh and you write jade dango uh inverting the first two characters you will still have a result you will still have some similarity is gonna be less of course and if you spell it right but it's still gonna give you a match These words particularly wow when young looking like for long sentences and um uh for long sentences you just misspell something

Speaker 2: What's a very good use case? A list of unique words. I use this personally in a list of um uh artist names with like uh singers and bad name. Uh you couldn't use a dictionary there because Each DJ of the word they have like different um names, sometimes they are not even pronounceable. And with this system it was creating a really nice approximation. What's the best use case? As I said , if you have articles or lull pieces of text, probably full text search is a better option. And note about this system, uh, as I say, they are both features of Postgres. As they come out from Postgres, they are not very performant, they are extremely slow.

Speaker 2: If you add the gain index, that is again another thing that you can do directly from Django without writing any SQL yourself, this is gonna speed up the system. So if you want to use one of these two features, make sure to create a migration to add the gen index on the columns that you want to search for. And yes, these are like the four main categories of search. I'm pretty sure there are like more around there. For me, what is good about this is like you have so many options. And if there is something that D taught me, is that diversity is is amazing having so many options and so many technologies, so many people in the world, so many classes that do different things in different cases.

Speaker 2: Each one with a strong point and a weak point is something that is valuable and something that we should use and cherish from. So Choose one of them if you have to build a search system, change it in the future if you don't like it anymore, create a new one, modify something, just build cool thing and And be amazing. So thank you everyone for listening.

Speaker 1: Thank you so much, Marco. So um we've got some time for questions and uh I do have a A couple of questions. Oi. Thank you. Now the internet can hear me better. Yeah, we do have a little bit of time for questions before we go to uh lightning talks and um Yeah, um I've been wondering if uh you you just showed a slide with uh Danish on it. I

Speaker 2: will speak that truth.

Speaker 1: I'm not I'm gonna ask her in English. How how does someone find uh if their language is supported in the Postgres? And is is it uh should should people worry about

Speaker 2: documentation there is a less. Um it's uh quite easy to find it. I think there is like 36 languages included. It is on GitHub that the list of languages is human source.

Speaker 1: Or indeed if you want to improve your own language support you can uh you can improve it.

Speaker 2: Yes, I think so. I can just submit a pull request.

Speaker 1: And is does it vary the quality of the language support

Speaker 2: Oh yes, of course, because then again for other system works. Yes, to normalize this word. uh to make it possible to check for drivers like if you are um you not just plural singular like with english but other languages have different grammar like Italian or Spanish then more complex system or subjects in which for instance degree something is uh pink or small and you do this adding a suffix if you have a good uh dictionary formalization you can search ball get matched for like one ball, two balls, big balls and small balls.

Speaker 1: And and uh to get started uh using a full text search, is there some off-the-shelf uh solutions? Uh for uh for instance it's common to to need it for blocks and CMSs and and so on. Perhaps with the Wagtail or Django CMS.

Speaker 2: Yeah, so for um if you have a block, um I will just use um post if you're using Postgres database. I would use the uh Postgres Footback Search again. From Jaguar is the action. Your feature documentation is very good. You can just author write the uh word in search uh from the real search in HD from model and uh create search factor to decide if one field is more important than the other to just search for multiple fields But that's potentially just an extra step. You could just apply this merch on one field that is the content of the article. Uh

Speaker 2: that's it. Uh if you are not dealing with a huge amount of Would you get a huge amount of data? This is gonna be the best option.

Speaker 1: And there's a question from the internet, uh from Paolo, who asks, uh I developed the Django documentation search. I would appreciate if you can explain more about your opinion about that search so I can improve it. Thanks. It's a comment.

Speaker 3: I have one question there. Can you add your dictionary? Uh uh for the search.

Speaker 2: To Postquarch. Okay, so I'm gonna repeat the because uh for the For the internet. So yeah, Christopher was asking if you can add your own dictionary. Um you should add your dictionary to Django. I didn't try to be honest. I'm assuming that that is possible. I know that that's and I'm at least that's quite clear from the Postgres code base for URL. uh the list for languages. I think we should be able to install like a extension. I know some visual process it can be yeah it's on directly sequence of them they used to be enable for instance older version of Postgres they do not have run

Speaker 2: enabled by default uh but there is no node documentation to um uh uh have your database to enable it Yeah, I guess we can do the same thing with DJ, but I'm not sure.

Speaker 1: I think we have time for the next question also, although it's not uh directly related to uh full-text search. um for uh Tango London meetups. Uh if you have um any upcoming meetups and uh how you uh are you gonna keep on being online also in the future?

Speaker 2: Okay So we have uh we have a cap shuttle every month and now we have a date for sure for uh the rest of the year. just because uh we only have three months in advance. So if you found one in October, one in October, one in December. Uh I'm not sure if we published even yet on but yeah that's the best way to find information otherwise we have a twitter account um we are gonna keep them uh doing them online uh for the foreseeable future right now uh the uk just announced another six months of um of quarantine and there is a ban over there in doing uh this kind of gathering Um so we can uh

Speaker 2: we we don't know when we

Speaker 1: we will back

Speaker 2: to be in uh physical format So yeah, it's in the future. In the meantime, we keep doing them from what's called usually second Tuesday, but we may take it and again meet up at the Twitter workout on the best way.

Speaker 1: All right. Thank you so so much, Marco.

Speaker 2: Thank you.

Speaker 1: All right. Big hand for Marco.