Way back in 2008 I started a site to track military reading lists. I needed something for searching, and I was most familiar with Sphinx, so, that's what I used.

Sphinx served me well over the last 18 years. But it runs as a separate process, and it felt like overkill to have an additional architectural component since I only have about 3000 books in the database. And PostgreSQL has a perfectly good full-text-search capability with a nice Ruby integration in pg_search. So, time to make the switch!

This article from Daniela Baron was really helpful on how to make the cutover. She was spot-on with her explaination of how calculating vectors on the fly just is too slow. I saw this even on my small site, where searching a couple thousand books by title/author/description took upwards of two seconds.

Since I'm using PostgreSQL 18, I used a generated column rather than a trigger. So the migration looks like:

class AddSearchVectorToBooks < ActiveRecord::Migration[8.1]
  def change
    execute "ALTER TABLE books ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(author, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'C')
    ) STORED"
    add_index :books, :search_vector, using: :gin
  end
end

Notice the per-column weights, pretty cool. And the model integration - i.e., pg_search_scope - returns a standard ActiveRecord scope, so, it all integrates easily with will_paginate.

Here's an example search link that returns 200+ results. Very snappy!