The Challenge

1. Search across article titles, content, and summaries
2. Weight results appropriately (titles more important than body text)
3. Be fast and scalable, even with many articles
4. Integrate well with Rails and ActionText

The Approach: PostgreSQL's Virtual Columns + pg_search

1. Extract and store plaintext versions of rich text content directly in the articles table
2. Use PostgreSQL's virtual stored columns to maintain a tsvector index
3. Utilize the pg_search gem to make implementation clean and Rails-friendly

Implementation

Step 1: The Migration

class AddSearchableToArticles < ActiveRecord::Migration[8.0]
  def change
    # Add columns to store plain text content
    safety_assured do
      change_table :articles, bulk: true do |t|
        t.text :content_plaintext
        t.text :summary_plaintext
      end
    end

    # Add virtual stored tsvector column based on the text columns
    tsvector_expression = <<~SQL.squish
      setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
      setweight(to_tsvector('english', coalesce(slug, '')), 'A') ||
      setweight(to_tsvector('english', coalesce(summary_plaintext, '')), 'C') ||
      setweight(to_tsvector('english', coalesce(content_plaintext, '')), 'D')
    SQL

    # Add the searchable column with the expression
    safety_assured do
      change_table :articles, bulk: true do |t|
        t.virtual :searchable, type: :tsvector,
                  stored: true,
                  as: tsvector_expression
        t.index :searchable, using: :gin, name: 'articles_on_searchable_idx'
      end
    end

    Article.find_each(&:refresh_searchable)
  end
end

• I added content_plaintext and summary_plaintext columns to store the plain text extracted from ActionText
• Created a virtual stored column that combines these fields with different weights (A=highest, D=lowest)
• Added a GIN index for fast searching
• Used the safety_assured method to bypass strong_migrations warnings

Step 2: Updating the Model

class Article < ApplicationRecord
  include PgSearch::Model

  has_rich_text :content
  has_rich_text :summary

  # Add callbacks to update plaintext columns
  before_save :update_content_plaintext
  before_save :update_summary_plaintext

  # PgSearch configuration for full-text search
  pg_search_scope :search_full_text,
    against: :searchable,
    using: {
      tsearch: {
        dictionary: "english",
        tsvector_column: "searchable"
      }
    }

  # Other methods...

  def refresh_searchable
    update_content_plaintext
    update_summary_plaintext
    save!
  end

  private

  # Update the content_plaintext column from rich text content
  def update_content_plaintext
    return unless content&.body
    self.content_plaintext = content.body.to_plain_text
  end
  
  # Update the summary_plaintext column from rich text summary
  def update_summary_plaintext
    return unless summary&.body
    self.summary_plaintext = summary.body.to_plain_text
  end
end

• Includes PgSearch for search functionality
• Has callbacks to extract plaintext from rich text when saving
• Defines a search scope that uses the searchable tsvector column

Step 3: Controller Integration

def index
  @articles = article_scope.published
  @articles = @articles.search_full_text(params[:search]) if params[:search].present?
  @articles = @articles.with_any_tags(params[:tags].split(",")) if params[:tags]
  @tags_cloud = Article.tags_cloud

  render Views::Articles::Index.new(articles: @articles, tags_cloud: @tags_cloud)
end

Step 4: Adding the Search UI

# Search bar for index view
div(class: "mx-auto max-w-2xl my-8") do
  form(method: "get", action: articles_path, class: "flex items-center") do
    div(class: "relative w-full") do
      div(class: "absolute inset-y-0 start-0 flex items-center ps-3 pointer-events-none") do
        icon("magnifying-glass", class: "h-5 w-5 text-gray-500")
      end

      input(
        type: "search",
        name: "search",
        id: "article-search",
        class: "input input-bordered w-full ps-10 p-2.5",
        placeholder: "Search articles...",
        value: request.params[:search]
      )
    end
    
    button(
      type: "submit", 
      class: "btn btn-primary ml-2"
    ) do
      "Search"
    end
    
    if request.params[:search].present?
      a(href: articles_path, class: "btn btn-outline ml-2") do
        "Clear"
      end
    end
  end
end

The Results

1. Performance: Searches are lightning-fast thanks to PostgreSQL's GIN index on the tsvector column
2. Maintainability: No complex database triggers to maintain - just Rails callbacks
3. Good UX: A clean search interface with helpful "no results" messages
4. Flexibility: The ability to weigh different fields according to their importance

Lessons Learned

1. Keep it simple: While database triggers might seem necessary for complex problems like synchronizing ActionText content, simpler solutions like callbacks often work well.
2. Use the database's strengths: PostgreSQL's tsvector and virtual columns are powerful features that handle search needs efficiently.
3. Balance duplication and complexity: Storing duplicate plaintext versions of rich text might seem redundant, but it simplifies the architecture and improves performance.
4. Think about the UX: Adding details like preserving search terms in the input field and showing helpful "no results" messages makes a big difference to users.