Full Text Search with Django and PostgreSQL: More Facets, Less Dependencies!

Speaker 1: Hi everyone, welcome to our talk and thanks for joining us today. This is a talk about faceted search. Specifically attempting to mimic features of Elasticsearch using Django and PostgreSQL's Fulltext Search. My name is Colin Copeland and I'm co-presenting with Jason Judkins. We both work at Cactus Group building Django Web Applications. If you build web applications, we're always looking for Python and Django expertise to join our team. And as of this year, Cactus is now employee-owned. If you'd like to join a small team that gets to collaborate on a diverse set of projects, visit the link here to learn more about us. So we're going to start off by covering the problem we faced, and then we'll compare Elasticsearch to PostgresQL's full

Speaker 1: text search. And then we'll move into exploring how to implement faceted search for our Netflix catalog sample site. There's going to be code, SQL, and Python and Django. And we'll finish with a demo of the open source tool we've built. All of the code examples you see here in this talk are available online. You can visit this GitHub link to see the code and learn more So Cactus was working on a Django upgrade for a longtime client in project, the Arizona Tobacco Enforcement System. This project helps the Arizona Attorney General's office coordinate tobacco enforcement inspections. It's used daily. And inspections occur across the state, and report data is uploaded to the site into Postgres QL and Elasticsearch.

Speaker 1: As with most upgrades, we review dependencies, both Python and others, including search. Django Haystack provides a search interface to Elasticsearch, and we noticed it hadn't been updated in a while. So we thought this was a good opportunity to step back and assess the plan forward. As we saw in the last slide, we're not searching a large data set. We have less than 100,000 rows in our database. We're also not using the advanced text search features of Elasticsearch. So we thought perhaps we could remove a dependency from our software stack. So we considered alternative options, specifically PostgreSQL 's full text search. Full text search offers many features, including phrase search, ranking, and highlighting.

Speaker 1: But it's obviously not as powerful as Elasticsearch. There's no faceting or custom rankings or other advanced search features. So we wondered, is it even possible to support the same user experience with PostgreSQL? Elasticsearch is very powerful and a great tool. And so it all depends on your use case. For us, the biggest missing piece was faceting. If we can add faceting to our PostgreSQL full text search implementation, Then perhaps we could simplify things and remove Elasticsearch from a software stack. But how can we handle that? So we began researching. Django has a great community. And first we found William Vincent's DjangoCon 2019 talk, Search from the Ground Up.

Speaker 1: It's a great talk, we highly recommend it. And it walks through building a list view and then adding full-text search capabilities. So from here, all we needed was faceting. Then we found Simon Willison's blog post, who also helped create Django. His blog post is from 2017, and Simon shares a lot of great knowledge here. His post covers search, search and filters, facets and navigation, and even more advanced topics like searching multiple tables using unions. Also, Simon's blog post is open source. So with these resources, we felt confident enough to try it out. So, what exactly is faceted search? I think the best way to think about it is enriching your text results by combining text searching with filtering.

Speaker 1: Your public library's online catalog probably uses it, and e-commerce sites like Amazon and REI typically use them too. I think it's easiest to explain with an example. So here's the REI site. First we search for tents and we find 491 products. There are filters in the left navigation bar with different classifications, categories, and sleeping capacity. And they include helpful item counts. For example, there are 78 two-person capacity sleeping bags. These counts tell you more about the underlying data. You can filter by these facets. So here we limit to two-person tents. The filters impact each other

Speaker 1: too. The categories filter is limited by our capacity filter. So of the 78 two-person tents, 10 are rooftop tents. So that's search facets and filters. And again, revisiting where we are today and where we want to be. We're using PostgreSQL with Haystack and Elasticsearch, and our new method is PostgreSQL full text search. But we still need to filter data, and a common tool for that is Django Filter. We use Django filter on projects regularly at Cactus. And we thought maybe we can build on top of that for faceting. A quick recommendation and shout out, Django Filter is a great open source Django app for filtering data. It's maintained by Carlton Gibson and other contributors.

Speaker 1: You should definitely check it out. Now I'm going to pass it off to Jason to walk us through some code.

Speaker 2: Thank you, Colin. First, we need example data. Something simple and easy to explain, how to create facets and search features. We found a dataset on Kaggle for Netflix titles, a small less than 10,000 row database. Next, we create an app for our data, and we're going to call it Films. Now starting with modeling the data. The Netflix data included these types for columns. So we create a basic model called film. Next, the views. A simple list of films. Here we're using Django 's class-based views, but it could be a normal function view too. Next, the template to list the films.

Speaker 2: Also simple at this point. And finally, we want to route a URL to our view. Here it will just be films. And this is the basic view of our film list. And now that we have our film list, but how do we add faceted navigation? How do we get the facet data with counts? Referring back to our models, let's pick a column to facet by. Type seems like a simple first choice. So let's first look at this in SQL. Start with the basic select distinct query, two values, movie and TV show. But we don't know the counts of each value.

Speaker 2: So let's replicate this in Django. We start with an all query set, and we can use the SQL aggregate functions to add counts Here we count the titles and group the results by the type column. Here we have 6,000 movies and 2,000 TV shows. The counts adjust when we add the WHERE clause. First, you limit the titles to those released in 1999 and then count the results by the column type. The dataset now only has seven TV shows released in 1999. We can start by returning the value of type for films. Then we can further annotate and show the total count for each value.

Speaker 2: For display, add the query set to our view context. and then iterate over and display the film types in our template. And there it is, our first faceted display. But this is just displaying the numbers. Now we want to use it. One approach is to add an anchor link to each type, so when the user clicks to type, it's included as a query string argument. Then in the view we can use the request object to inspect the get parameter and check if film type is in the query string. If so, filter the query set by the provided film type.

Speaker 2: Click on TV show, and the titles are only TV shows. Great. But the facet counts didn't change. Looking back at our view, types are annotated for all films. If we want to adjust these based on filters, we update the context variable to use the filtered query set. And now you can only see our chosen TV show's facet count. What's next? We have our type field facet, but what else do we still want? We want to have be able to do multiple facets, support different field types, remove selected facets, customize facet options, such as ordering, pagination and full

Speaker 2: text search. Django Filter provides the familiar filtering to functionality, so let's use that here. Create a filter set for films. Extend filter set from our faceted filter set. You notice here that there is a new configure facets method, and we configure one facet for that type field. Next, update our view. Rather than filtering query set manually, we switch to a filter set and get query set. Now we use show facets template tag to render the facets on the filter set. And this is the result.

Speaker 2: Now we can start adding more facets for our searches. So looking back to our first facet choice where we chose type, we wanted to do our next facet type with genre. But wait, instead of genre, our dataset calls the column listed in. This data column, the data in this column has many genres listed for each film. For example, a movie could be listed in multiple genres in one field as you see here. So we needed to transform this into data that we can use for a facet. So we need to clean up the listed end column into a field we can fast it on. We start by creating a new model called genre.

Speaker 2: Next, we added a many-to-many field for our new genres column. We want to show you how your data may not easily be usable and you may need to adjust your data model. Here, we use functionality to transform the provided listed end column data into individual objects that we can turn into a facet. We use split and strip, for example, to separate and remove commas from the string of genre names. And we then create a new genre or find one that already exists and use that in our new many-to-many relationship. Next, adjust our filter set to include our genres field and to add the genres facet to our configure facets. And this is the result of adding the additional facet.

Speaker 2: Now I'm going to hand it off back to Colin for a look at search.

Speaker 1: Thanks Jason. So now that we have facets, let's talk about search. This will be a quick intro to how full text search works, but you should watch William's talk for more in-depth information. In summary, PostgreSQL converts documents or text into lexemes. In other words, normalized tokens you can see here. Quick brown fox is a common example sentence that contains all letters of the alphabet, and so we're just using it as an example. Here we see that the vector excludes stop words like the and implement stemming to find derivatives of root words, like lazy, which can match laziest and laziness. So we use the match operator

Speaker 1: to at signs to search, which returns true if a TS vector or document matches a query. Here we see searching for the term lazy returns true for this document. And searching cat doesn't return a match or false. Further, the WebSearch to TSQuery function provides web-like searching. It accepts queries in a syntax familiar to common search engines. Here the words quick and fox match because both words exist in the document, even though they're not side by side. However, if we quote the phrase quick fox, that match fails since brown is between the two terms.

Speaker 1: This is a phrase match. Similarly, we can exclude terms. Here we search for quick and not fox. The match fails because the document includes Fox. But if we search for quick and not cat, the document matches. These are simple examples, but hopefully they illustrate how full text search is useful And luckily, Django provides an interface to these search methods. So let's break this down. First we construct a search query, the text we're searching for, and then a search vector, which are the fields we include in our search. And then we annotate a query set and perform the search.

Speaker 1: So in this example, we search for community and find the community TV show. But we probably want to search additional fields too. We can combine them like we do here, adding description and cast to our search. So if we now search for community, many more results are returned. But say we want to limit these results to only those that include Allison Bree. So we just adjust our query to include her name, and we're back to a single result. Lastly, say we want to find Alice and Bree's other works. We can simply add a minus sign in front of Community, and the results include other TV shows and movies without community.

Speaker 1: So now all we have to do is integrate this into our faceted filter set. First we add a basic text search field to the filter set, pointing to a filter search method. When the filter set has data for that field, it will call this filter search method. So here we use our search functionality we just reviewed and also include ranking. PostgreSQL will score the results so we can sort by the score and show the best matches first. So to see this in action, Jason will lead us through the full demo project.

Speaker 2: Thank you, Colin. Now I'm going to walk you through a small demonstration of the app that we created. As you can see here, we have our list of Netflix films and TV shows. Over on the right, you have a search bar and our type and genre facets that we created. As you can see, we can easily switch between just showing TV shows and movies. And you can remove those restrictions. And genres works just as well. International movies, comedies, and you can remove it. So let's say we have an actress that we wanted to do a search on, and we mentioned Alison Bree earlier. So if we do a simple search on Alison Bree , And we now show a list of all the movies and TV shows that are currently listed in our database.

Speaker 2: And let's say if we want to try to do a search for a specific show, but we can't think of the name. So I know it's a TV show, so I can use that TV show facet. And now we do have a list of the four shows that she was in. Community is the one that I'm looking for here. But let's say for examp for an example, we can't remember what the actual name of the show is. We know it takes place in a college. So let's add college to our parameters on the search And now you get a list of just the shows and movies that she was in what it had to do with college. And of course, if I want to go TV show, I find community listed. Now let's say I decided I wanted to do everything.

Speaker 2: I had only seen her in community and I wanted to see what else she was in so I can actually do a search without college with our minus And now we have a list of all the TVs and shows that she acted in that did not have her doing anything with college. And now I'm handing it back off to Colin, who's going to do a quick recap of what we talked about today.

Speaker 1: Thanks, Jason. To wrap things up, let's review a few caveats you should take into consideration before using the project. As we demoed with genres, your existing data model may not be suited for faceting with PostgreSQL. So you may need to create lookup tables and normalize relationships in SQL to use for faceting. And as you may have noticed, each facet translates to a SQL count query. So depending on your use case, you may need to add indices or caching to improve performance. For look and feel, we use Tailwind CSS to style and theme our demo faceted search pages.

Speaker 1: So you will likely need to override the templates in your project to match your styling. Additionally, the interaction between facets and the existing Django filter form widgets isn't streamlined and will likely require some polish to improve UX for your users. Since we didn't need Django Res framework support for our project, support is not included yet. So this may be a blocker for projects using DRF with a single page app. One area for improvement is that our demo full text search vectors and queries are computed on the fly. But you can improve performance with Django's search vector field, which will pre-compute the vectors.

Speaker 1: And you can use Postgres QL's generated columns to automatically update these fields when they change. Lastly, these columns can use GIN indices to improve performance. So with all of that said, if this project does seem like a good fit, hopefully you can remove a dependency or consider using PostgresQL as an alternative to full text search. Thank you so much for joining us today. Again, all of this code is available on GitHub. The reusable app is only alpha, but we hope folks find it useful. Feel free to contribute if you're excited about it. Thank you.