Using a virtual column for trigram indexes in PostgreSQL

Posted . Visible to the public.

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.

def 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

  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.

Profile picture of Florian Leinsinger
Florian Leinsinger
Last edit
Tobias Kraze
License
Source code in this card is licensed under the MIT License.
Posted by Florian Leinsinger to makandra dev (2025-10-22 15:24)