On The Look-Out For Your Data

Speaker 1: Okay, please welcome Markus Holderman.

Speaker 2: Thanks everybody for coming and today I want to talk about the data we have in our databases. This pile of information that we have that we want to see and want to find something inside. And um I want to give you an idea of how we can do that this in Django and what we should look out for when doing that. A short introduction to myself about myself. I'm Markus Holtermann, I'm a Django contributor. I'm a software engineer at Laterpay, and I happen to be one of this year's DjangoCon Europe um organizers. Let me start off with asking the question, what is search? And let me start off with asking

Speaker 2: how to search in Django. Well, let's start answering the first question. And when we look at the Oxford English Dictionary and use non-technical terms to define search, we find an explanation for the word for the verb to search, which goes like try to find something by looking and otherwise seeking carefully and thoroughly. Okay, so what does this actually mean? Let's take this apart. We try to find something by looking. So does this mean that if we fail at f looking something for something, we are not searching? Does it mean that

Speaker 2: when we try but can't find something, this is fine? And we shouldn't worry about that? There are these two words carefully and thoroughly in there. Does that mean when we don't look carefully and thoroughly we are not searching? Are we not searching correctly? Do we search wr the wrong way And well this is one of s what I wanna like look into when we think about how we search in Django. And looking at this definition though, I see that and can understand that search is hard. And we actually all know this. You have this situation where you wanna leave your apartment, your house.

Speaker 2: Wanna go something, so you grab your coat, your shoes, you grab your where are my cohes? Where's my wallet? So you look for minutes Well probably not ours, but you look around until you eventually find them probably at the place where they belong, in the bowl, in the in the corridor, and on the cupboard. But why didn't we look there right away And should we should we have looked there right away? And when you look at this from a more computer science perspective, then Let me ask the question for you to pick a number between 1 and 0 that I've thought of Like you can go with is it number one? I said no. Is it number two? Nope. Number three? Still nope.

Speaker 2: And you can go on and on and you will eventually come up with the number I picked. Great It's not really efficient though. Like imagine five billion numbers. You're gonna ask all day long. So you go with let's make it random. Is number eighty-five? Nope. Seventeen. Nope. Forty-eight? Nope. And well, if you keep track of the numbers you asked, you may hit the number I t I thought of earlier, but you may not. So there needs to be an improvement to that. And in computer science, in a when you have a search uh a sorted inf set of information, you can use something called binary search Which is in this case, is it number 50? No, it's smaller.

Speaker 2: Is it number 25? No, it's still smaller. Is it number 12? Yes So with only three questions you narrowed down this number out of a hundred, which is pretty great. Now let's try to apply this to Django and let's see even where we find this in Django or when using Django. And Who of you think they or think they have built or used search in Django? Can I get a show of hands? Okay, that's about half the room, maybe a bit more. That's good But I think all of you who have used Django or the RM have actually used search.

Speaker 2: Let's look at this code example. We have a view that gets a request in the primary the primary key. We use getObject of 404 to fetch the particular article from the database, and then we return a response to the user. Well, this is search. It's a particular point part of search, but a user asks for something to f to and asks us for an article with a particular primary key. and we try to find this article. If we don't, we return a four or four. We don't give a server error, so we are careful about this. So looking thinking about the definition, trying to find something by carefully and thoroughly looking for it, this is search.

Speaker 2: It's a particular search, it's an equality search in the database we use, but it's still search. And we can make this more complex or in c as complex as we want. We can add only published articles that have been written in this particular time period, it's still a search. We still try to find something given some constraints. So this is fine, this is this works. But how about searching text? Because this is something where we well we currently look up something by a primary key And well, searching text is a bit more complicated because the difference between text and like columns in a database or primary key is

Speaker 2: it's kind of unstructured data. Think about search engines like Bing, Dr. Go, Google, all those others that are out there. What information do they have? Like literally what information do they have? the URL of a website they saw, it's the time they last visited it, it's the bare HTML they were able to download, it's maybe the title tag they passed out of that. And then there's all this f other fancy stuff that comes on top of like trying to figure out the meaning of what the text actually says. But searching text essentially means we have this pile of unstructured data that we want to find something in. And think about the

Speaker 2: the quote or the definition of trying again, trying to find something by looking and otherwise seeking carefully and thoroughly. This means in the context of searching text, we should probably well return all the things that kind of match our search terms. So we go ahead, modify the view we had, and we do this. Instead of using getObject of 404, we use getList of 404. which returns a four or four if you don't have at least one object. And instead of looking something up by primary key, we look it up by well you using I contains on the text of an article and looking up the search query from the from the um get

Speaker 2: um request. So we are both careful here because we still return a 404 if this article is off if not we don't find an article, we don't return a server error. We d still try because we use iContains. We just we don't use contains, so we make sure we don't care about uppercase, lowercase, mixed case words and so on. But when we look at the database perspective of that, we get pretty much the SQL. And this is not particularly efficient. Unfortunately. So if you're on Postgres, Postgres can't you you physically can't build an easily build an index on this to index to to make such queries efficient

Speaker 2: MySQL has this full text search flag index thingy. I'm Not really sure if this is actually what we want. So let's stick to Postgres here. Um you can however make this more efficient in Postgres. And there's a Postgres extension called Trigrams. And Trigrams are character combination or in a trigram is when you have a piece of text or or or string and you chunk it off in in parts of three characters with certain constraints. And using the Postgres Trigram extension

Speaker 2: You can chunk up your entire text into trigrams and then use that for search. But to give you an idea of how what trigrams look like If you use the showTrigram method in Postgres on SQL, then and the term I love Django, you end up with this. So this is indexable in Postgres. And it's indexable by with an operator class that the Postgres Trigram extension provides. Now Django has class-based indexes for a few versions now. And with a bit of internal stuff that's not documented But still fits on one slide, you can create an class-based index that creates an

Speaker 2: effectively creates an index that's usable in an iContains query and can be used with an iContains query. So the key here is the gist trigram ops operator up here. And that we use upper here because Django's iContains turns the search term into uppercase letters as well. And this works. This stuff underneath is somewhat Django internal, not documented, but well so be it. We can we should all look into how Django is build how Django does things and then leverage the things we we need. And we can add this add to a we can create a an index We can add this to an index definition on a model, and Django

Speaker 2: will just happily create a migration for that. Now search and text. So we've found an efficient way to search through the pile of information to all the news articles, all the blog articles we write, and provide users with an interface to actually find something. Great. Now they come ahead and like actually use their search and unfortunately don't they don't really find the things they're looking for. Because what you're doing here is while searching text Not really that what we look or what we understand when we talk about searching text. When we talk about searching text, what we actually might mean is something called full

Speaker 2: text search And full text search has a few constraints or few things it does compared to what we've done so far One of that is that we don't really care about the word order in your search terms. It doesn't really matter if you look for Django migrations or migrations Django. The result should be the same. There's no prioritizing words that come first or words that c or less prioritizing words that come last There's something in in language that's called stemming. And effectively words has a have a base or stem that they originate from. So you have a computer, you have the verb to compute, you have computation, and the stem is

Speaker 2: compute. So it doesn't really matter if there's a computer doing something? And if there's somebody computing something, or if there's a computation happening, like it's the meaning that there's something being computed and like evolved, that's what somebody is looking for And this is what we like look for when we search. And there's also something called stop words. Django is the best provides this very same meaning as Django Best. Is and the and on by for These are words that don't really provide that much meaning to a in a text.

Speaker 2: Well, they provide context in a in a sentence, but they don't provide context and information in a search query. I say oh great, we have all these features and we want to do this. How the heck are we going to do this? If you have Postgres, you can use the double underscore search lookup. And the documentation of this on this topic is pretty great. So I'm not gonna go into details here. This is a link for that on Django 2. 0. Instead, I wanna go into a bit more advanced part of of where search can go. And for now we've Uh we've used our database as a data as a as the search engine effectively.

Speaker 2: But what happens when Even Postgres built-in search doesn't really give us the information and doesn't really do all the fancy things we want to do. Well, we go and use external tools And this is great, they do their job properly and they are built for doing full text search and other kinds of searches. And only to name a few, Xapien, Solar, Lucine, Vuj , Elasticsearch, there's heaps more out there They are great. They do their job properly. And they all have their benefits and and and problems and they have their quirks and you it takes a bit to get used to them and understand them.

Speaker 2: But once you get a hold of them and hang off them, great So now you have a different problem though. It's not that the search engine you use doesn't really provide the doesn't deliver search results that you want. The problem that you have is that It's different or that you first need to get information into the search engine, into their data store. But because effectively You had your data Postgres or database before and now next to your database you have your search engine as a kind of second database. And you need to synchronize them And since Django one point nine there's been um uh in Django one point nine we added the the

Speaker 2: Django added a feature called transaction hooks. Which means that when you start it when you have when you are inside a transaction, you can tell Django to do something after a transaction has successfully committed. So you do all the things and then you may have nested transactions and then eventually you do transaction commit and the data is saved to the database saved at the database. Django gets the positive response back from the database transaction successful and then Django is going to call all the certain transaction hooks you registered over time in the correct order and all these things Great, so you have your safe your overwrite your save method on the model

Speaker 2: and whenever you do that you update your search. You probably don't want to do this inside the request, but like trigger uh asynchronous targ like uh task like salary. But you can effectively update your search reliably every time you save an article. Now keep in mind please that the call dot update method on a query set does not call save on a every instance. So you need to do something there as well. Similarly, you want to delete an article from your database, whatever whatever or whyver you want to do that, you can use the same idea. There's a bit of a gotcha here because Django sets the primary key to null after it deleted an instance. So you need to keep track of the primary key before you actually

Speaker 2: trigger the deletion and go into the transaction. But that's fine, you can deal with that. Likewise the.delete on the query set doesn't call the individual delete method. So doing this, this is great, this works, awesome. There's just one tiny thing that we didn't consider yet. You write your article, it's added to the database, it's available in the search engine, great. That's fine. You delete your article, somebody hits the search engine, you get a return value for a result from the search engine, which still contains the deleted article because it hasn't been triggered and hasn't been propagated to the search engine yet.

Speaker 2: So you have this ti this time window of and where your search engine has more information than your or different information than your uh data database and has additional information. And this can potentially lead to several errors depending on how you actually implement your search So what I highly recommend is to maintain a complete search index. So what does it mean actually? Well Uh let's say you have your search result page, like a blog, news site, whatever, and you wanna deliver the and have your search dumb and wanna deliver this page to a user. You have essentially two options. You either search for the articles, then use the search it the primary keys, fetch all this from the database and show

Speaker 2: to the user. Or you have all the information in the search engine as well, you re and you use only the information that you get back from the search engine. That means you include things like the URL, the modification date, even if a user doesn't search by that, inside your search engine. Which means you will not hit your database at all when you um when you when a user hits your search. And when you think about this a bit further, if you for example use Elasticsearch and Postgres and manage to set up your website in a way that You have a re and you have a read heavy site with like a few people that write something to the data the base

Speaker 2: database every now and then. You could potentially build a website that is for unusual users and visitors only reads data from Elasticsearch and only your colleagues or the employees write to that. So If you put uh set up a maintenance for Postgres, you could potentially shut off Postgres and your website would keep running and nobody would notice because all the user-served content is served from Elasticsearch. And Postgres version updates can potentially be a bit tricky, but that's yeah, um a whole different topic. So what is search?

Speaker 2: We try to find something by looking or otherwise seeking carefully and thoroughly When we look at this again, we try to find something. So we try to not raise server errors. We did this. We managed to return a 404, which is Magnus which is far better to use it than oh server error. Five for four five for five hundred, five three, all these things that a usual user can't deal with. We are careful about this because the search engines we use are aware of stuff like

Speaker 2: stemming. They are aware of stuff like word order. They are aware of stuff like um making sure we use certain or that that potentially even words have a have similar meanings and like there's a whole bunch of things that Search engines can do that your usual database probably doesn't. And When we go this way, there's this this this point in time at some somewhere where you Wanna have an external search engine because the benefits of that even though the m maintaining and maintenance overhead of it is bigger, you don't want to have search as part of your

Speaker 2: implementate application and have it implemented in your application. And this is essentially this careful and thoroughly. You make sure that whatever a user enters is carefully evaluated and carefully and thoroughly like treated So there's a bunch of things. Um I need to make the first repository public because this is an implementation of a bunch of things I wrote while reading while writing the talk. Like a code examples of how you use icontains, there's the index clauses are in there, there's um analystic search integration, there is um a bit on this uh on the Postgres search uh inter

Speaker 2: Postgres internal searching in there. And well then there's this search in Django topic that I linked before and this two cred article on um on Postgres search. All right, thank you.

Speaker 1: Okay, thank you Marcus. We've got some time left for questions if we have any. Yeah, don't be shy.

Speaker 3: Hi Marcus, thank you. Um uh just when you were talking about maintaining the full search index and You were kind of saying put it into Elasticsearch and then put the article in there so that they read just straight from that. The thing that came to my mind is, well, why put it in Postgres at all? Why not? Like it's just a th why not drop Postgres and just F

Speaker 2: very fair point. Um let's say well there's the there's different ways to answer this I guess. Postgres is a or Django has an RRM which is an object relational mapper. Elasticsearch is not really relational. It's more document store. And If you want to use the ORM, then using Elasticsearch is like not really a way. But if you use the ORM and then deal with that yourself based on how your application actually works, this can be properly dealt with. The other thing is that your most people or most projects these days, from my perspective, are still relational. And

Speaker 2: I think in in many places objects or document stores or column stores or all these things, they have a they have a reason they're out there. But for most applications that are out there these days, it's still re um relational. And a lot of the benefits that relational databases are great at just vanish the moment where you go with some other inf um where where you try to force your un force your data into these other data stores and so yeah I think that's having the usual the the the keeping the or m part in there and talk to Postgres or like a database like this and then just yeah this is something that's that deals with the load so to say provides some benefits

Speaker 3: Perfect answer. Thank you.

Speaker 2: Thank you.

Speaker 1: Okay, do we have any more questions? Thank you very much again, Marcus. Then

Speaker 2: thank you.