The denormalized query engine design pattern

Most web applications need to offer search functionality. Open source tools like Solr and Elasticsearch are a powerful option for building custom search engines… but it turns out they can be used for way more than just search.

By treating your search engine as a denormalization layer, you can use it to answer queries that would be too expensive to answer using your core relational database. Questions like “What are the top twenty tags used by my users from Spain?” or “What are the most common times of day for events to start?” or “Which articles contain addresses within 500 miles of Toronto?”.

With the denormalized query engine design pattern, modifications to relational data are published to a denormalized schema in Elasticsearch or Solr. Data queries can then be answered using either the relational database or the search engine, depending on the nature of the specific query. The search engine returns database IDs, which are inflated from the database before being displayed to a user - ensuring that users never see stale data even if the search engine is not 100% up to date with the latest changes. This opens up all kinds of new capabilities for slicing, dicing and exploring data.

In this talk, I’ll be illustrating this pattern by focusing on Elasticsearch - showing how it can be used with Django to bring new capabilities to your application. I’ll discuss the challenge of keeping data synchronized between a relational database and a search engine, and show examples of features that become much easier to build once you have this denormalization layer in place.

Transcript

1. The denormalized query engine design pattern Simon Willison, DjangoCon US - 16th August 2017 Slides will be here: http://lanyrd.com/sftkxk
3. Design patterns
4. denormalized query engine • Relational database as single point of truth • Denormalize all relevant data to a separate search index • Invest a lot of effort synchronizing the two
5. Relational weaknesses • They’re not great at counting • You should avoid queries that read more than a few thousand rows • MySQL can only use one index per query
6. Search engine strengths • Horizontal scaling • Aggregations and counts • Queries across multiple indexed fields • Relevance calculations and scoring • … and text search too
8. Users 1- 10,000 Users 10,000- 20,000 Users 20,000- 30,000 Users 30,000- 40,000 MySQL MySQL MySQL MySQL Sharding by user
10. http://aaronland.info/talks/mw10_machinetags/#91
11. Smart query routing • Send a user’s queries about their own data to the relational database • Send queries about other users and public data to the search index
12. Solving a scalability crisis at Lanyrd
14. • Search Solr for events where… • attendee_ids matches [giant list of Twitter IDs] • start_date is in the future • Scale Solr horizontally with replication
16. • Open source search engine built on Apache Lucene • Interface is all JSON over HTTP - easy to use from any language • Claims to be “real-time” - it’s close enough • Insanely powerful query language (a JSON DSL) • Strong focus on analytics in addition to text search • Elastic means elastic: scales horizontally
17. Faceted search and aggregations
19. GET /emails/_search { "query": { "match": { "Body": "security" } }, "aggs": { "role_type": { "terms": { "field": "role_type" } }, "party": { "terms": { "field": "party" } } } }
20. Relational database sync strategies • updated/last_touched timestamp • Indexing queue (redis/kafka) • Subscribe to database replication stream
21. last_touched class Conference(models.Model): name = models.CharField(max_length=128) url = models.URLField(blank=True) # … last_touched = models.DateTimeField( db_index=True, default=datetime.datetime.utcnow, ) # Reindex all conferences when associated guide is edited: guide.conferences.all().update(last_touched=datetime.datetime.utcnow()) Indexing code needs to track most recently seen last_touched date time
22. Redis/Kafka queue • Any time an object needs reindexing, add the type/ID to a queue • Every few seconds, clear the queue, de-dupe the item IDs, fetch from database and reindex
23. Replication log • MySQL supports replication… and you can listen to the replication stream itself • github.com/noplay/python-mysql-replication
24. Dilithium Master MySQL Replica MySQL Dilithium (Python) Kafka (message queue) Indexing code (Python) Elastic search SQL queries Replication Replication Kafka Kafka
25. Tips and tricks
26. Inflate IDs into objects • Avoid ever showing the user stale data! • Return row IDs from search… • … fetch them from the database for display • Relational databases are REALLY FAST at primary key lookups (and prefetch_related)
27. Self-repair on deletes • If the search index returns an ID that no longer exists in your database… • Quietly drop it from the output (9 results instead of 10) • Queue for deletion from the index via celery
28. Accurate filter trick GET /events/_search { "query": { "term": { "saved_by_users": "123124" } } }
29. Accurate filter trick recent_ids = user.event_saves.filter( # Created within last 5 minutes created__gte=( datetime.utcnow() - timedelta(minutes=5) ) ).values_list('event_id', flat=True) # [776, 4124, 3414]
30. Accurate filter trick "query": { "bool": { "should": [ { "term": { "saved_by_users": "123124" } }, { "ids": { "values": [776, 4124, 3414] } ] } }
31. More use-cases
32. Recommendations • Search for events where saved-by matches my- friend-1 or my-friend-2 or my-friend-3 or … • Find events similar to my-last-10-saved-events • Search engines are great at scoring! Boost by in-same-city-as-me, boost more by saved-by- my-friends
33. Geographic search • Elasticsearch has great support for geo… • Find documents within X radius of point Y • Find documents contained in polygon Z • Combine these with search and filters
34. Visualizations
35. Real-time map/reduce • Elasticsearch can be thought of as a real-time map/reduce system • Like Hadoop, but you can expose it to your users!
36. denormalize to a query engine! in summary… Elasticsearch is pretty good