--- title: Implementing Multi-Table Full Text Search with Postgres in Rails teaser: tags: web,postgresql,rails author: - Derek Prior - Caleb Hearth published_on: 2013-08-16 --- Easily searching across an application's data is a pervasive need. If you are lucky, you can get away with simple sorting or searching on a single column, but it is more likely that you need full text search across multiple models, all from a single search field. There are many standalone services, some hosted and some not, offering full text search. We looked at a few of these, but decided that adding [too much to our classes] or having an external service running during tests weren't things we wanted for this project. [too much to our classes]: https://thoughtbot.com/blog/post/50655960596/sandi-metz-rules-for-developers Thanks to the power of Postgres' [full text search], rolling your own search isn't too difficult. [full text search]: http://www.postgresql.org/docs/9.2/static/textsearch.html If all you need is to search over a few models' text and string fields, this approach is probably the simplest thing you can do. ## SQL Changes We'll need to construct a [database view] which presents a polymorphic relationship to the individual result and the text column being searched. [database view]: http://www.postgresql.org/docs/9.2/static/tutorial-views.html ```sql CREATE VIEW searches AS SELECT statuses.id AS searchable_id, 'Status' AS searchable_type, comments.body AS term FROM statuses JOIN comments ON statuses.id = comments.status_id UNION SELECT statuses.id AS searchable_id, 'Status' AS searchable_type, statuses.body AS term FROM statuses UNION SELECT users.id AS searchable_id, 'User' AS searchable_type, users.name AS term FROM users ``` From here, we add [`gin`] indices to the columns on which we are searching. In our case similar indices to these made the difference between a 3-5 second lookup and ~100ms. [`gin`]: http://www.postgresql.org/docs/9.2/static/textsearch-indexes.html ```sql CREATE INDEX index_statuses_on_body ON statuses USING gin(to_tsvector('english', body)); CREATE INDEX index_comments_on_body ON comments USING gin(to_tsvector('english', body)); CREATE INDEX index_users_on_name ON users USING gin(to_tsvector('english', name)); ``` ## Ruby [Aaron Patterson]'s [Textacular] is the only non-standard dependency we'll introduce here: [Aaron Patterson]: https://twitter.com/tenderlove [Textacular]: https://github.com/textacular/textacular ```ruby gem 'textacular' ``` Textacular will manage searching over all text and `varchar` columns. We follow Rails' conventions in our database view, which makes hooking a model up to it as simple as any table-backed model. The `Search` class below automatically hooks into the `searches` view we created. Luckily, ActiveRecord already presents us with a solution to polymorphic associations in the form of the `_id` and `_type` columns. All we have to do is tell `Search` about its `searchable` relationship, define the `results` method to perform the search, and extend `Textacular` in the model. ```ruby class Search < ActiveRecord::Base extend Textacular belongs_to :searchable, polymorphic: true def results if @query.present? self.class.search(@query).preload(:searchable).map!(&:searchable).uniq else Search.none end end end ``` The call to `preload` is used instead of `include` because Rails can't include polymorphic associations. Calling `preload` still loads the related models in as few SQL statements as possible, but does not allow for querying on the related models. This isn't something we need since we immediately map to `searchable`. Since we could potentially get a result for a model multiple times, for example if the same term appeared in a status and a comment, we also call `uniq`. The interface for Search look like this: `Search.new(query: 'books').results`. ## Caveats * Rails lacks any support for creating or updating database views. Because the view must be created by calling execute, Rails is unable to dump the view into `db/schema.rb`. Your must make the following change to your application configuration: ```ruby # config/application.rb config.active_record.schema_format = :sql ``` * If the view needs to change to include additional searchable content, the `up` method on the migration has to redefine the view entirely and the `down` method must redefine the view in its previous form. * Remember that creating indices blocks writes by default, which means that the site needs to be in maintenance mode. [Create indices concurrently in Rails] to avoid this. [Create indices concurrently in Rails]: https://thoughtbot.com/blog/post/56828751507/how-to-create-postgres-indexes-concurrently-in ## Conclusion Overall, we've enjoyed working with this method of full text search. Because it's awkward to change the view we'd recommend not approaching the search too iteratively; add the tables you know you'll want to search up front and save yourself some headache. For even more depth on full-text search in Postgres including materialized views, ranking of different fields, misspelling toleration, and dealing with accents, check out the [Postgres full-text search is Good Enough!] post by [Rach Belaid] or Caleb's talk on this subject [Multi-table Full Text Search in Postgres]. [Postgres full-text search is Good Enough!]: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough [Rach Belaid]: https://twitter.com/rachbelaid [Multi-table Full Text Search in Postgres]: https://calebhearth.com/talks/search.html