Postgres Full Text Search with pg_search

A high level overview of using Posgtres for full text search using the pg_search gem.

Transcript

1. Postgres Full Text Search in Your Rails App
2. • Why, Pros and Cons • A bit of Postgres • Under the Hood • PgSearch • Search Scopes • Multi search • Performance, Indexes, etc
3. Why?
5. https://github.com/ PublicLibraryAssoc/cpldl
6. Wanted:
7. Wanted: Full Text Search without external programs
8. Wanted: Full Text Search without external programs No additional server infrastructure
9. Wanted: Full Text Search without external programs No additional server infrastructure Works with AWS RDS
10. A bit of Postgres:
11. A bit of Postgres: Evolved from Ingres
12. A bit of Postgres: Evolved from Ingres Open sourced in 1995
13. Under the Hood: Two key data types
14. Under the Hood: Two key data types ts_vector
15. to_tsvector(config, text) outputs a normalized tsvector
16. SELECT to_tsvector(‘english’, ‘Hello Boulder Ruby’) -> boulder,hello,rubi
17. SELECT to_tsvector(‘english’, ‘Hello Boulder Ruby’) -> boulder, hello, rubi W AT.
18. Under the Hood: Two key data types ts_vector ts_query
19. SELECT to_tsquery(‘english’, ‘ruby’) -> rubi
20. Operators: @@ vector @@ query && || vector @@ (A && B) vector @@ (A || B)
21. SELECT body FROM comments WHERE to_tsvector (‘english, body) @@ ( to_tsquery(‘english’, ‘ruby’) && to_tsquery(‘english’, ‘rails’) );
22. A Word of Warning: Never pass user input directly to ts_query.
23. DIY: class Comment < ActiveRecord::Base scope :search_all -> (query) { where (“to_tsvector(‘english’, body) @@ #{sanitize_query(query)}”) } Comment.search_all(“ruby”)
24. pg_search
25. pg_search Developed by Pivotal’s Casebook dev team. Inspired by Aaron Patterson’s Textactular
26. Install: gem ‘pg_search’ include PgSearch
27. Pro-Tip: config/application.rb config.active_record.schema_format = :sql
28. Building a Search Scope pg_search_scope :search_comments, against: :body pg_search_scope :search_all, against: [:body, :title]
29. Dynamic Search Scope class Person < ActiveRecord::Base include PgSearch pg_search_scope :search_by_name, lambda do |name_part, query| raise ArgumentError unless [:first, :last].include?(name_part) { :against => name_part, :query => query } end end person_1 = Person.create!(:first_name => “Grant", :last_name => "Hill") person_2 = Person.create!(:first_name => “Hugh", :last_name => "Grant") Person.search_by_name :first, "Grant" # => [person_1] Person.search_by_name :last, "Grant" # => [person_2]
30. Searching Through Associations Quickly implement cross-model searching
31. Searching Through Associations Quickly implement cross-model searching Heads up • Lookup speed can’t be improved via database indexes.
32. Searching Through Associations pg_search_scope :search_users, against: [:email], associated_against: { profile: [:first_name], roles: [:name] }, using: { tsearch: { prefix: true }, dmetaphone: { any_word: true }, trigram: { threshold: 0.1 } } https://github.com/PublicLibraryAssoc/cpldl/blob/master/app/models/user.rb#L31-L36
33. Additional pg_search features • :tsearch • :trigram • :dmetaphone
34. Additional pg_search features def up execute 'CREATE EXTENSION pg_trgm;' execute 'CREATE EXTENSION fuzzystrmatch;' end
35. :tsearch • The standard full text search implementation
36. :tsearch • The standard full text search implementation • Supports Weighting, Prefix searches, and Stemming
37. :tsearch • Weighting Allows you to assign a rank of A, B, C, or D A -> D is High -> Low importance. pg_search_scope :search_full_text, against: { title: 'A', subtitle: 'B', content: 'C' }
38. :tsearch • Prefix Allows you to match on partial terms pg_search_scope :whose_name_starts_with, against: :name, using: { tsearch: { prefix: true } }
39. :tsearch • Prefix Allows you to match on partial terms pg_search_scope :whose_name_starts_with, against: :name, using: { tsearch: { prefix: true } }
40. :tsearch • Dictionaries and Stemming Stemming removes common endings, like -ing, -y, and -er. Normalizes the search lexemes.
41. :tsearch • Dictionaries and Stemming pg_search_scope :search_text, against: :text, using: { tsearch: { dictionary: “english” } }
42. :tsearch • :any_word pg_search_scope :search_users, against: [:email], associated_against: { profile: [:first_name], roles: [:name] }, using: { dmetaphone: { any_word: true }, }
43. :dmetaphone • Install the contrib package def up execute 'CREATE EXTENSION fuzzystrmatch;' end • Add the migration rails g pg_search:migration:dmetaphone • Matches “Geoff” and “Jeff”
44. :trigram • Install the contrib package def up execute 'CREATE EXTENSION pg_trgm;' end • Creates three letter substrings. Boulder Ruby -> [“ Bo”, “Bou”, “oul”, “uld”, “lde”, “der”, “er “, “r R”, “ Ru”, “Rub”, “uby”, “by “, “y “]
45. :trigram • :threshold pg_search_scope :search_users, against: [:email], associated_against: { profile: [:first_name], roles: [:name] }, using: { trigram: { threshold: 0.1 } }
46. Limiting features on Fields • :only pg_search_scope :search_users, against: [:email], associated_against: { profile: [:first_name], roles: [:name] }, using: { trigram: only: [:email], threshold: 0.5 } }
47. Unaccenting • Add migration for contrib package def up execute 'CREATE EXTENSION unaccent;' end • Add option pg_search_scope :gringo_search, against: :word, ignoring: :accents
48. Unaccenting Heads Up • Requires Postgres 9.0 or newer • Prevents indexing Alternatives • Use Trigram + GIN index • Using an SQL incantation to wrap or replace the function and make it immutable http://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations/11007216#11007216
49. Multi-document Search • Add migration to enable pg_search_documents table rails g pg_search:migration:multisearch • Add multisearchable to your model class Course < ActiveRecord::Base multisearchable against: [:title, :summary, :description, :topics_str, :level]
50. Multi-document Search • Indexing existing data PgSearch::Multisearch.rebuild(User) or a rake task rake pg_search:multisearch:rebuild[User]
51. Multi-document Search • Multi searchable Associations User has_one :pg_search_document PgSearchDocument belongs_to :searchable, polymorphic: true
52. Multi-document Search • Searching and Finding Records PgSearch.multisearchable(“ruby”) => Returns a ActiveRecord::Relation of PgSearch::Document => Can call .searchable on each PgSearch::Document to get the original resource. • Configuration is the same as pg_search_scope
53. Performance and Indexing • You can create a “tsvector” type column to store lexemes. • One tsvector column is needed for each searching method. • You’ll also need to create a trigger function to update the tsvector column when content changes in the original model object. • SQL schema format required.
54. Performance and Indexing pg_search_scope :fast_content_search, against: :content, using: { dmetaphone: { tsvector_column: 'tsvector_content_dmetaphone' }, tsearch: { dictionary: 'english', tsvector_column: 'tsvector_content_tsearch' } trigram: {} # trigram does not use tsvectors } https://robots.thoughtbot.com/optimizing-full-text-search-with-postgres-tsvector-columns-and-triggers
55. GIN vs GiST Index GIN • Generalized Inverted Index • Must be “tsvector” data type GiST • Generalized Search Tree • Can be “tsvector” or “tsquery” data type
56. GIN vs GiST Index GIN • ~3x faster lookup than GiST • ~2-3x larger than GiST GiST • ~3x faster for initial build than GIN • Moderately faster to update than GIN
57. GIN vs GiST Index GIN • Best for static data • Better for >100,000 unique lexemes GiST • Faster to update, so good for highly dynamic data • Better for <100,000 unique lexemes
58. Thank You! @shepbook jared@koumentis.com Special Thanks: • Grant Hutchins, Pivotal and the pg_search contributors • Adam Sanderson “Postgres: The best tool you’re already using.” (Talk on Confreaks.) • Dan Croak, Thoughtbot “Optimizing Full Text Search with Postgres tsvector Columns and Triggers” (Blog Post)