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?
answer
- t.virtual with type: :tsvector, as:
- stored: true for an indexable column
- 8.1 defaults to VIRTUAL on PostgreSQL 18
- add_index using: :gin
- where with @@ and a bound query
basics
~20 sDeclare 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 sA 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
Recall t.virtual with type: :tsvector and as:, a GIN index, and a where fragment with @@ and a bind.
Explain what stored: true does, why Rails 8.1 on PostgreSQL 18 changes the default, and how Rails reads generated columns back.
Build the search scope safely, index the right column, and know when trigram or a search engine is the better tool.
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.