skip to content

In Rails 8.1 on PostgreSQL, how do you build sneaker search with a generated tsvector column and a GIN index, and why does stored: matter?

level: seniorimportance: should knowfreq 30%

answer

  1. t.virtual with type: :tsvector, as:
  2. stored: true for an indexable column
  3. 8.1 defaults to VIRTUAL on PostgreSQL 18
  4. add_index using: :gin
  5. where with @@ and a bound query

basics

~20 s

Declare t.virtual :search_vector, type: :tsvector, as: "to_tsvector(...)", stored: true, index it with using: :gin and query with a @@ fragment. Rails 8.1 makes a generated column VIRTUAL on PostgreSQL 18 unless stored: true, and older servers reject it without stored: true.

solid answer

~40 s

A generated column keeps the search document in step with the row: `t.virtual :search_vector, type: :tsvector, as: "to_tsvector('english', coalesce(name, '') || ' ' || coalesce(details ->> 'colorway', ''))", stored: true`, then `add_index :sneakers, :search_vector, using: :gin`, and query `Sneaker.where("search_vector @@ websearch_to_tsquery('english', ?)", params[:q])`. `stored:` is the version trap. Since Rails 8.1, on PostgreSQL 18 a generated column without `stored: true` is created VIRTUAL, computed on read, and the GIN index on it fails; on PostgreSQL 12 to 17, Rails raises `ArgumentError` unless you pass `stored: true`. Active Record never writes the column; it reads the value back after insert, but after an update it is stale in memory until `reload`.

go deeper

for a junior

Recall t.virtual with type: :tsvector and as:, a GIN index, and a where fragment with @@ and a bind.

for a middle

Explain what stored: true does, why Rails 8.1 on PostgreSQL 18 changes the default, and how Rails reads generated columns back.

for a senior

Build the search scope safely, index the right column, and know when trigram or a search engine is the better tool.

for a principal

Weigh in-database search against running a search service: data freshness, ranking quality and operational load.

## The goal A sneaker marketplace needs a search box: "jordan bred 1985" should find listings whose name, colorway or collaboration mention those words, with stemming, and without running a separate search server. PostgreSQL's full-text search does this with a **`tsvector`** (a normalised list of words) and a query matched with `@@`. How those functions and operators work is PostgreSQL's subject. The Rails questions are how to keep the vector up to date, index it, and query it safely. ## The migration ```ruby class AddSearchVectorToSneakers < ActiveRecord::Migration[8.1] def change change_table :sneakers do |t| t.virtual :search_vector, type: :tsvector, stored: true, as: "to_tsvector('english', coalesce(name, '') || ' ' || " \ "coalesce(details ->> 'colorway', ''))" end add_index :sneakers, :search_vector, using: :gin end end ``` - `t.virtual` declares a **generated column**: PostgreSQL computes it from the expression in `as:` whenever the row is written. No callback can forget to update it. - `type: :tsvector` is the column's SQL type. The PostgreSQL adapter also has a `t.tsvector` column method for a column you would fill yourself. - `using: :gin` builds a GIN index, the index kind that serves `@@` matches on a `tsvector`. - `coalesce` keeps a `NULL` name or colorway from nulling the whole expression. ## Why stored: decides whether this works | Server | `stored: true` | `stored:` omitted or `false` | |---|---|---| | PostgreSQL 12-17 | `GENERATED ALWAYS AS (...) STORED` | Rails raises `ArgumentError`: versions before 18 do not support virtual generated columns | | PostgreSQL 18, Rails 8.1 | stored, indexable | `GENERATED ALWAYS AS (...) VIRTUAL`, computed on read | Rails 8.1 added support for PostgreSQL 18's **virtual** generated columns and made them the default there when `stored: true` is absent. A virtual column is not on disk, and the index above cannot be built on it, so the migration fails at `add_index`. Why PostgreSQL refuses that index is its own topic; for the Rails migration, the rule is simple: **a column you index needs `stored: true`**. Writing it explicitly also keeps the migration identical across PostgreSQL versions. ## Querying ```ruby class Sneaker < ApplicationRecord scope :search, ->(q) { where("search_vector @@ websearch_to_tsquery('english', ?)", q) } end Sneaker.search(params[:q]).limit(20) ``` 1. The user's text is a **bind**, never interpolated into the SQL string. 2. `websearch_to_tsquery` accepts free-form text with quotes and minus signs, so a search box can pass input straight through. 3. Ranking with `ts_rank` goes in `order(Arel.sql(...))` with the query re-bound via `sanitize_sql_array`; `Arel.sql` marks the fragment as trusted, so it must not contain raw input. ## How Active Record treats a generated column - It **never writes** it: virtual columns are filtered out of the attributes Rails puts in `INSERT` and `UPDATE`. - After **create**, the PostgreSQL adapter reads the computed value back with `RETURNING`, because the column has a default expression. - After **update**, nothing is read back, so `sneaker.search_vector` holds the old vector until `reload`. It rarely matters, because you query this column rather than display it. - `db/schema.rb` dumps it as `t.virtual "search_vector", type: :tsvector, as: "...", stored: true`. ## Adding it to a large existing table - Adding a **stored** generated column makes PostgreSQL compute it for every existing row, which rewrites the table under a lock; on a large `sneakers` table, schedule it or plan it with the migration-safety tooling the team uses. - Building the GIN index can also take a long time; building it concurrently is a separate migration technique. - Test the expression on real data first: a `NULL` in an uncoalesced column makes the whole vector `NULL` and silently hides the row from search. ## When to move beyond this - Typo tolerance and fuzzy matching need trigram similarity (`enable_extension "pg_trgm"` and a GIN index with `opclass: :gin_trgm_ops`). - Faceted search, relevance tuning and synonyms across millions of listings are where a dedicated search engine starts to earn its operating cost. - Several languages need one vector per language or a `regconfig` column chosen per row.

  • Why does sneaker.search_vector still show the old value after update! in Rails 8.1?
    Active Record leaves generated columns out of the UPDATE and does not read anything back afterwards, so the in-memory attribute keeps the value it had. PostgreSQL has already recomputed the stored column. After create the value is fresh, because the adapter's INSERT returns columns that have a default expression. Call `reload` if you need it.
  • Why prefer a generated column over an expression index on to_tsvector(...) for search in Rails?
    Both work. An expression index requires every query to repeat the exact expression, or PostgreSQL will not use it. A stored generated column names the vector once, so the scope reads `search_vector @@ ...`, the schema documents it, and `ts_rank` reuses it. The cost is the extra column on disk.

  • Omits stored: true and expects the GIN index to build on PostgreSQL 18
  • Interpolates params[:q] into the @@ SQL string
  • Believes a generated column needs a model callback to stay current
  • Assigns search_vector from Ruby and expects Rails to write it
  • Expects search_vector to be fresh in memory right after update!

Assumes Rails 8.1.4: since 8.1.0 a generated column without stored: true is VIRTUAL on PostgreSQL 18, and Rails raises ArgumentError for it on PostgreSQL 12-17.