ActiveRecord workaround for pg_search and tsvector columns

Everything I found for optimizing pg text search performance via pg tsvector column suggested adding a postgres trigger, as described here

https://thoughtbot.com/blog/optimizing-full-text-search-with-postgres-tsvector-columns-and-triggers

class AddTsvectorColumns < ActiveRecord::Migration
  def up
    add_column :products, :tsv, :tsvector
    add_index :products, :tsv, using: "gin"

    execute <<-SQL
      CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
      ON products FOR EACH ROW EXECUTE PROCEDURE
      tsvector_update_trigger(
        tsv, 'pg_catalog.english', description, manufacturer_name, name
      );
    SQL

    now = Time.current.to_s(:db)
    update("UPDATE products SET updated_at = '#{now}'")
  end

  def down
    execute <<-SQL
      DROP TRIGGER tsvectorupdate
      ON products
    SQL

    remove_index :products, :tsv
    remove_column :products, :tsv
  end
end

My model was set up simply like so:

class Office < ApplicationRecord
  include PgSearch::Model

  multisearchable against: [:tsv]
  ...

After adding a create operation to my app I realized that the pg_search gem was rebuilding the search documents before the PG hook could set the tsv column properly. This resulted in the new record being unsearchable.

There are a lot of directions you could go in to skin this cat, but my solution was to do away with the PG trigger, and manually maintain the tsv column with an ActiveRecord hook on my searchable models:

So I removed the triggers from my db and updated my model like so:

class Office < ApplicationRecord
  include PgSearch::Model

  multisearchable against: [:tsv]

  before_save :update_tsvector

  private

  def update_tsvector
    # Create a string with the values of my searchable columns
    column_string = [name, city, state, street, zip].join(' ')
    
    # Query to use PG to generate the tsvector
    query = <<~SQL
      SELECT to_tsvector('english', \'#{column_string}\');
    SQL
    
    # Hit PG with the query to generate tsvector and save it on the tsv column before save
    self.tsv = ActiveRecord::Base.connection.execute(query.chomp).first.values[0]
  end
end

Of course you'd want to factor this hook out into a model concern, with a class method to defind searchable columns, and probably other optimizations, but this is the initial brain dump, so enjoy!

tasandberg/Gist.md