Using a virtual column for trigram indexes in PostgreSQL

Full-text search can reach its limits in terms of flexibility and performance. In such cases, trigram indexes (pg_trgm) offer a lightweight alternative.

You can base the index on a virtual column that combines multiple text attributes. A virtual column stores the result of an expression as if it were a real column. It is automatically updated when the source columns change and can be indexed like normal data. This keeps your query logic consistent and avoids repeating string concatenation in every search.

Copydef search_text_expression(*columns)
  columns.map { |column| %{COALESCE("#{column}", '')} }.join(" || '|' || ")
end

def change
  enable_extension 'pg_trgm'
  
  add_column :my_table, :search_text, :virtual, type: :text, as: search_text_expression(:foo, :bar, :baz), stored: true
  add_index :my_table, :search_text, using: :gin, opclass: :gin_trgm_ops
end

This will speed up queries like

Copy  MyTable.where('search_text ILIKE ?', '%query%')

Warning

Functions like concat() cannot be used here because they are not IMMUTABLE.
Index expressions must be immutable, so we concatenate manually.

Info

For PostgreSQL 18+, you can also use stored: false.

Related cards

• Postgresql: trigram indexes for searching
• Trigram indexing as an alternative to PostgreSQL fulltext search
• A simple example with a GIN index in Rails for optimizing a ILIKE query