Building a Speedy Autocomplete with Postgres on Rails

Transcript

1. Building A Speedy Autocomplete With Postgres On Rails Bradley Priest @bradleypriest bradley@tradegecko.com
2. Building A Speedy Autocomplete With Postgres On Rails Bradley Priest @bradleypriest bradley@tradegecko.com Not Too Slow
3. Building A Speedy Autocomplete With Postgres On Rails Bradley Priest @bradleypriest bradley@tradegecko.com Search Not Too Slow
4. Building A Speedy Autocomplete With Postgres On Rails Bradley Priest @bradleypriest bradley@tradegecko.com Search Not Too Slow pg_search
5. API: - Rails + Postgres + Redis Main Site: CoffeeScript + EmberJS ~3 other Rails mini-apps
6. I
7. hstore JSON PostGIS tsearch
8. - Every new PG Feature is my hammer - So many nails - I’ve made plenty of over-optimisations - Mostly hstore - e.g. Product Prices/Options
9. My Problem Line Item Autocomplete
10. Plenty of Options
11. tsearch PostGIS JSON hstore
12. Postgres Hammer #1 - pg_search https://github.com/CaseCommons/pg_search - Postgres Full Text Search for dummies
13. pg_search
14. pg_search V1 class Variant < AR::B include PgSearch pg_search_scope(:autocomplete, against: [:name, :sku] ) ### end
15. pg_search V1 Variant.create(name: ‘bacon’) - Variant.autocomplete(‘bacon’) ✔ - Variant.autocomplete(‘bac’) ×
16. pg_search V2 class Variant < AR::B include PgSearch pg_search_scope(:autocomplete, against: [:name, :sku], using: {tsearch: {prefix: true}} ) ### end
17. pg_search V2 Variant.create(name: ‘bacon’) - Variant.autocomplete(‘bacon’) ✔ - Variant.autocomplete(‘bac’) ✔
18. pg_search V2 class Variant < AR::B include PgSearch pg_search_scope(:autocomplete, against: [:name, :sku, :barcode], using: {tsearch: {prefix: true}} ) ### end
19. pg_search V2 class Variant < AR::B include PgSearch pg_search_scope(:autocomplete, against: [:name, :sku, :barcode, :description], using: {tsearch: {prefix: true}} ) ### end
20. pg_search V2 class Variant < AR::B include PgSearch pg_search_scope(:autocomplete, against: [:name, :sku, :barcode, :description], associated_against: {product: :name}, using: {tsearch: {prefix: true}} ) ### end
24. Dang!
25. I but
27. What are the options? - Build it yourself - tsvector column (But this requires SQL)
28. Build it yourself class Variant < AR::B include PgSearch def self.autocomplete(term) match = “%#{term}%” self.where(‘name ilike ? or sku ilike ? or description ilike ? or barcode ilike ?’, match, match, match, match) end ### end
29. Build it yourself class Variant < AR::B include PgSearch def self.autocomplete(term) match = “%#{term}%” self.joins(:product) .where(‘variants.name ilike ? or sku ilike ? or description ilike ? or barcode ilike ? or products.name ilike ?’, match, match, match, match, match) end ### end
30. Bonus - Use AREL def self.autocomplete(term) match = “%#{term}%” search_arel = arel_table[:name].matches(match).or( arel_table[:sku].matches(match).or( arel_table[:description].matches(match).or( arel_table[:barcode].matches(match).or( Product.arel_table[:name].matches(match) ) ) ) ) self.joins(:product).where(search_arel) end
31. However Variant.create(name: ‘chunky tasty bacon’) - Variant.autocomplete(‘bacon’) ✔ - Variant.autocomplete(‘chunky bacon’) ×
32. What are the options? - Build it yourself - tsvector column (But this requires SQL)
33. tsvector “A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to make different variants of the same word look alike.” - Postgres manual - http://www.postgresql.org/docs/9.2/static/datatype-textsearch.html
34. tsvector def change add_column :variants, :search, :tsvector end class Variant < AR::B include PgSearch pg_search_scope(:autocomplete, against: :search, using: {tsearch: {prefix: true}} ) end
37. coalesce() ...returns the first of its arguments that is not null… coalesce(null, ‘’) #=> ‘’ Make sure we don’t have any pesky nulls
38. setweight() ...used to label the entries of a tsvector with a given weight, where a weight is one of the letters A, B, C, or D... setweight(to_tsvector(name), 'A')
39. to_tsvector() ...parses a textual document into tokens, reduces the tokens to lexemes, and returns a tsvector which lists the lexemes together with their positions in the document... to_tsvector(‘simple’, name)
40. to_tsvector() to_tsvector(‘simple’,’a fat cat sat on a mat’) #=> 'a':1,6 'cat':3 'fat':2 'mat':7 'on':5 'sat':4 to_tsvector(‘english’,’a fat cat sat on a mat’) #=> 'cat':3 'fat':2 'mat':7 'sat':4"
41. Product has_many Variants
42. Product has_many Variants Arbitrary no-op to fire Variant trigger
43. Product has_many Variants AFTER not BEFORE
44. Seed the :search Column
45. Uh-Oh AFTER not BEFORE
46. schema.rb doesn’t know about triggers application.rb - config.active_record.schema_format = :sql But then you have to deal with even more merge conflicts than schema.rb
47. Alternative lib/variant_trigger_setup.rb
48. Alternative add_tsvector_to_variant.rb
49. Alternative lib/tasks/variant_triggers.rake
50. Et Voilà
52. FIN Questions? Suggestions? Insults?