Slide 1

Slide 1 text

On The Look-Out For Your Data @m_holtermann #djangocon Europe 2018

Slide 2

Slide 2 text

I’m Markus Holtermann @m_holtermann • github.com/MarkusH • markusholtermann.eu @laterpay • laterpay.net • Django Contributor • Software Engineer at

Slide 3

Slide 3 text

What Is Search?

Slide 4

Slide 4 text

How To Search In Django?

Slide 5

Slide 5 text

What Is Search?

Slide 6

Slide 6 text

What Is Search? Try to find something by looking or otherwise seeking carefully and thoroughly. — Oxford English Dictionary

Slide 7

Slide 7 text

What Is Search? Try to find something by looking or otherwise seeking carefully and thoroughly. — Oxford English Dictionary

Slide 8

Slide 8 text

What Is Search? Try to find something by looking or otherwise seeking carefully and thoroughly. — Oxford English Dictionary

Slide 9

Slide 9 text

Search Is Hard

Slide 10

Slide 10 text

Searching In Django?

Slide 11

Slide 11 text

from django.shortcuts import get_object_or_404, render from blog.models import Article def article_view(request, pk): article = get_object_or_404(Article, pk=pk) return render( request, 'article.html', context={'article': article}, )

Slide 12

Slide 12 text

Searching Text

Slide 13

Slide 13 text

from django.shortcuts import get_list_or_404, render from blog.models import Article def article_view(request): articles = get_list_or_404( Article, text__icontains=request.GET.get('query', ''), ) return render( request, 'articles.html', context={'articles': articles}, )

Slide 14

Slide 14 text

SELECT * FROM blog_article WHERE text ILIKE '%Looking for text%'

Slide 15

Slide 15 text

Trigrams

Slide 16

Slide 16 text

-- As superuser # CREATE EXTENSION pg_trgm; # SELECT show_trgm('I love Django'); show_trgm ---------------------------------------------- - {" d", " i", " l", " dj", " i ", " lo", ang, dja, "go ", jan, lov, ngo, ove, "ve "}

Slide 17

Slide 17 text

from django.contrib.postgres.indexes import GistIndex class TrigramGistIndex(GistIndex): suffix = 'trgm_gist' sql = 'CREATE INDEX %(name)s ON %(table)s %(using)s \ (UPPER(%(columns)s) gist_trgm_ops)%(extra)s' def create_sql(self, model, schema_editor, using=''): statement = super().create_sql(model, schema_editor, using=using) statement.template = self.sql return statement

Slide 18

Slide 18 text

Searching Text

Slide 19

Slide 19 text

Full-text Search

Slide 20

Slide 20 text

Word order doesn’t matter “Django Migrations” = “Migrations Django”

Slide 21

Slide 21 text

Stemming computer, compute, computation = comput

Slide 22

Slide 22 text

Ignoring Stopwords “Django is the best” = “Django best”

Slide 23

Slide 23 text

__search & PostgreSQL https://docs.djangoproject.com/en/ 2.0/ref/contrib/postgres/search/

Slide 24

Slide 24 text

External Search Tools

Slide 25

Slide 25 text

No content

Slide 26

Slide 26 text

from django.db import models, transaction class Article(models.Model): # ... def save(self, *args, **kwargs): with transaction.atomic(): transaction.on_commit( lambda: update_search(self.pk)) super().save(*args, **kwargs)

Slide 27

Slide 27 text

from django.db import models, transaction class Article(models.Model): # ... def delete(self, *args, **kwargs): pk = self.pk with transaction.atomic(): transaction.on_commit( lambda: delete_search(pk)) return super().delete( *args, **kwargs)

Slide 28

Slide 28 text

Maintain A Complete Search Index

Slide 29

Slide 29 text

What Is Search? Try to find something by looking or otherwise seeking carefully and thoroughly. — Oxford English Dictionary

Slide 30

Slide 30 text

• Example: https://github.com/MarkusH/talk-django-search • Search in Django: https://docs.djangoproject.com/en/2.0/topics/db/search/ • Choosing a PostgreSQL text search method: https://blog.2ndquadrant.com/text-search-strategies-in-postgresql/ • Trigram Extension: https://www.postgresql.org/docs/10/static/pgtrgm.html • Full-text search: https://www.postgresql.org/docs/10/static/textsearch-tables.html

Slide 31

Slide 31 text

Thank you! @m_holtermann

Slide 32

Slide 32 text

import blog.indexes from django.contrib.postgres.operations import TrigramExtension from django.db import migrations class Migration(migrations.Migration): dependencies = [('blog', '0002_auto_20180503_1925')] operations = [ TrigramExtension(), migrations.AddIndex( model_name='entry', index=blog.indexes.TrigramGistIndex( fields=['body'], name='body_trgm_gist')), ]

Slide 33

Slide 33 text

-- Creates extension pg_trgm CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- Create index body_trgm_gist on field(s) -- body of model entry CREATE INDEX "body_trgm_gist" ON "blog_entry" USING gist (UPPER("body") gist_trgm_ops);