ruby 65 lines · 3 tabs

Multi-Column Full Text Search with tsvector

Shared by codesnips Jan 2026

3 tabs

class AddSearchVectorToArticles < ActiveRecord::Migration[7.1]
  def up
    execute <<~SQL
      ALTER TABLE articles
      ADD COLUMN search_vector tsvector
      GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,  '')), 'B')
      ) STORED;
    SQL

    add_index :articles, :search_vector, using: :gin
  end

  def down
    remove_index :articles, :search_vector
    remove_column :articles, :search_vector
  end
end

class Article < ApplicationRecord
  scope :search, ->(term) do
    query = term.to_s.strip
    next none if query.blank?

    tsquery = Arel.sql("websearch_to_tsquery('english', ?)")
    rank    = Arel.sql("ts_rank(search_vector, #{tsquery})")

    where(
      sanitize_sql_array(["search_vector @@ #{tsquery}", query, query])
    ).order(
      Arel.sql(sanitize_sql_array(["#{rank} DESC", query]))
    )
  end

  def self.highlight(term, column = :body)
    return all if term.blank?

    select(
      "articles.*",
      sanitize_sql_array([
        "ts_headline('english', #{column}, websearch_to_tsquery('english', ?)) AS snippet",
        term
      ])
    )
  end
end

class ArticlesController < ApplicationController
  def index
    @term = params[:q].to_s.strip

    scope =
      if @term.present?
        Article.search(@term).highlight(@term)
      else
        Article.order(created_at: :desc)
      end

    @articles = scope.page(params[:page]).per(20)

    respond_to do |format|
      format.html
      format.json { render json: @articles, methods: :snippet }
    end
  end
end

3 files · ruby Explain with highlit

This snippet builds multi-column full-text search on top of PostgreSQL's tsvector type, keeping all of the search logic inside the database instead of an external service. The pattern is useful when an app needs "good enough" ranked search over a couple of text columns (say a title and body) without pulling in Elasticsearch, and it scales well because the heavy work is done once at write time and indexed.

The AddSearchVectorToArticles migration adds a generated tsvector column, search_vector, using Postgres' GENERATED ALWAYS AS (...) STORED feature. The expression concatenates title and body through to_tsvector, giving each field a weight (setweight with 'A' for the title, 'B' for the body) so title matches rank higher later. Because the column is STORED and generated, it stays in sync automatically on every insert and update — there is no trigger to maintain and no application code that can forget to refresh it. A gin index on search_vector is what makes the @@ match operator fast; without it, each query would sequentially scan and recompute nothing but still read every row.

The Article model exposes the query surface. Article.search turns a user string into a websearch_to_tsquery, which understands quoted phrases and or/- operators the way a search box user expects, and is safe against malformed input. The scope filters with @@ and orders by ts_rank so the most relevant rows come first. Note the use of sanitize_sql_array and bound parameters — the raw query text is never interpolated directly, which avoids SQL injection while still letting Postgres parse the query language. A short input guard returns none so an empty search does not match everything.

The ArticlesController wires the scope into a normal index action, degrading to Article.all when no term is present and paginating the result. The trade-offs worth remembering: the 'english' config controls stemming and stop-words, so a multi-language corpus needs a per-row config column; websearch_to_tsquery requires Postgres 11+; and the STORED column adds a little write cost and table size in exchange for far cheaper, index-backed reads.

Related snips

ruby

class CommentsController < ApplicationController
  before_action :set_post

  def create
    @comment = @post.comments.build(comment_params)

System test: asserting Turbo Stream responses

rails hotwire turbo

by codesnips 4 tabs

ruby

class Post < ApplicationRecord
  belongs_to :author, class_name: 'User'
  has_many :comments, dependent: :destroy

  scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
  scope :draft, -> { where(published_at: nil) }

ActiveRecord scopes for reusable query logic

rails activerecord patterns

by Alex Kumar 1 tab

ruby

module Api
  module V1
    class UsersController < BaseController
      def show
        user = User.includes(:profile).find(params[:id])

ETags for conditional requests and caching

rails caching http-caching

by Alex Kumar 1 tab

ruby

class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author)
                 .order(created_at: :desc)
                 .page(params[:page])
                 .per(10)

Turbo Frames: infinite scroll with lazy-loading frame

rails turbo hotwire

by codesnips 4 tabs

package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions

by Leah Thompson 1 tab

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

Share this code

Here's the card — post it anywhere.

Multi-Column Full Text Search with tsvector — share card

Link copied