Search in Rails, four rungs up

Type "rails search" into a search box and the autocomplete offers you searchkick, a search gem, a search form and search_field, which is four answers to three different questions. Two of them are about a text input, and this post is not about the text input. The question worth an afternoon is what runs when the form submits, and there are four plausible answers to that, each one buying something concrete and charging for it.

Every plan and every error message below was produced against PostgreSQL 17.7 on a 200001 row table, activerecord 8.1.3.1, on a local database. Where a number came from my machine and would not reproduce on yours, it is not here.

Rung one: ILIKE, and the two indexes it ignores

Article.where("title ILIKE ?", "%#{params[:q]}%") is the first thing anyone writes, and for a table of a few thousand rows it is the correct thing. It is also the rung where the most surprising result lives, so it is worth running before dismissing.

A leading wildcard cannot use a btree index, which everybody knows. Here is the plan anyway, for a term that appears in exactly one row out of 200001:

 Limit (actual rows=1 loops=1)
   Buffers: shared hit=5707
   ->  Gather (actual rows=1 loops=1)
         Workers Planned: 2
         ->  Parallel Seq Scan on articles (actual rows=0 loops=3)
               Filter: (body ~~* '%tsvector%'::text)
               Rows Removed by Filter: 66667

5707 buffers, roughly 45 MB of heap, to return one row. Fine at 5000 rows, not fine at 200000, and the failure is gradual rather than sudden, so nothing tells you when you crossed the line.

The part people do not expect is that dropping the leading wildcard does not save you. LIKE 'Article 12345%' on an indexed column planned as an Index Scan reading 6 buffers. The same prefix with ILIKE, same column, same index:

 Limit (actual rows=11 loops=1)
   Buffers: shared hit=5707
   ->  Gather (actual rows=11 loops=1)
         ->  Parallel Seq Scan on articles (actual rows=4 loops=3)
               Filter: (title ~~* 'Article 12345%'::text)

Case insensitivity is the whole cost. ~~* is a different operator from ~~ and no btree on the plain column supports it. An autocomplete endpoint typing ILIKE 'q%' because case insensitive felt safer has quietly bought a full scan per keystroke.

The collation that decides whether a prefix is indexed at all

Even LIKE 'prefix%' is not reliably indexed, and the thing that decides is a database property most people never look at.

The Index Scan above happened on a database created with datcollate = C. Same 200000 rows, same statement, column declared COLLATE "en_US.UTF-8", index built the ordinary way:

 Limit (actual rows=11 loops=1)
   Buffers: shared hit=1274
   ->  Seq Scan on coll (actual rows=11 loops=1)
         Filter: (title ~~ 'Article 12345%'::text)
         Rows Removed by Filter: 199989

Add one index with a different operator class, CREATE INDEX coll_title_pattern ON coll (title text_pattern_ops), change nothing else:

 Limit (actual rows=11 loops=1)
   Buffers: shared hit=2 read=3
   ->  Index Scan using coll_title_pattern on coll (actual rows=11 loops=1)
         Index Cond: ((title ~>=~ 'Article 12345'::text) AND (title ~<~ 'Article 12346'::text))

Note the operators in the Index Cond. ~>=~ and ~<~ are byte comparisons rather than collation aware ones, which is exactly why the pattern index works and the ordinary one does not: under a linguistic collation, "everything between 'Article 12345' and 'Article 12346'" is not a contiguous range of the index. Check SELECT datcollate FROM pg_database before assuming either way. Rails writes this as add_index :articles, :title, opclass: :text_pattern_ops.

The percent sign your user typed into your query

Before leaving this rung, one bug that is not about performance. "%#{params[:q]}%" goes into a bound parameter, so it is not SQL injection. It is pattern injection, which is smaller and real: a user searching for 100% searches for %100%%, and the trailing % is a wildcard, not a character.

Active Record has the escaper, in active_record/sanitization.rb:132:

def sanitize_sql_like(string, escape_character = "\\")
  if string.include?(escape_character) && escape_character != "%" && escape_character != "_"
    string = string.gsub(escape_character, '\0\0')
  end

  string.gsub(/(?=[%_])/, escape_character)
end

sanitize_sql_like("100% done_now") returns "100\\% done\\_now", and the two queries differ by the backslashes:

WHERE (title ILIKE '%100% done_now%')
WHERE (title ILIKE '%100\% done\_now%')

The underscore matters more than the percent in practice, because _ is a single character wildcard and it appears in every identifier your users paste into a search box.

Trigrams make a leading wildcard indexable

pg_trgm is the rung between ILIKE and full text search, and it is one extension and one index. It cuts the string into overlapping three character pieces, indexes those, and can then answer ILIKE '%anything%' from the index.

CREATE EXTENSION pg_trgm;
CREATE INDEX articles_body_trgm ON articles USING GIN (body gin_trgm_ops);

The same query that read 5707 buffers a moment ago:

 Limit (actual rows=1 loops=1)
   Buffers: shared hit=22
   ->  Bitmap Heap Scan on articles (actual rows=1 loops=1)
         Recheck Cond: (body ~~* '%tsvector%'::text)
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on articles_body_trgm (actual rows=1 loops=1)

The price is on disk and it is steep. That trigram GIN index over the body column alone measured 31 MB, where the full text GIN index over title and body together measured 15 MB. You are paying close to the size of the text to index the text.

And it has a floor. A pattern with no complete three character run inside it yields no trigram to look up, so the index is skipped:

 Limit (actual rows=0 loops=1)
   Buffers: shared hit=5707
   ->  Parallel Seq Scan on articles (actual rows=0 loops=3)
         Filter: (body ~~* '%zq%'::text)
         Rows Removed by Filter: 66667

Two characters, index present, full scan. Every autocomplete has a two character prefix in it, because that is what the second keystroke produces.

Trigram is the right rung when the thing being searched is not prose: SKUs, email addresses, hostnames, serial numbers, anything where substring is what the user means and words are not a useful unit. For prose, keep climbing.

Rung two: a tsvector column Postgres maintains for you

Postgres full text search stops matching characters and starts matching words. to_tsvector normalises text into lexemes and drops stop words, to_tsquery and friends do the same to the query, and @@ asks whether they match.

SELECT to_tsvector('english', 'The operator was running through the rows');
        'oper':2 'row':7 'run':4

Seven words in, three lexemes out. "running" became "run", so a search for runs matches, which is the thing ILIKE will never do for you at any index size.

The modern way to store it is a generated column, so the database keeps it in sync and no callback can forget:

ALTER TABLE articles ADD COLUMN searchable tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;
CREATE INDEX articles_searchable_gin ON articles USING GIN (searchable);

setweight tags each lexeme with a letter, which later lets ts_rank count a title hit for more than a body hit. The PostgreSQL manual is unambiguous about which index type to reach for: "GIN indexes are the preferred text search index type." The same GIN machinery behind @> on a jsonb column, which jsonb columns in Rails measures from the other side.

The lookup, against the same 200001 rows that cost 5707 buffers under ILIKE:

 Limit (actual rows=1 loops=1)
   Buffers: shared hit=5
   ->  Bitmap Heap Scan on articles (actual rows=1 loops=1)
         Recheck Cond: (searchable @@ '''tsvector'''::tsquery)
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on articles_searchable_gin (actual rows=1 loops=1)

Five buffers.

What the migration looks like in Rails 8.1

Active Record has native support for all of this and it is not widely used. tsvector is a real column type in the PostgreSQL adapter, registered at postgresql_adapter.rb:156, and generated columns go through t.virtual:

create_table :docs do |t|
  t.string :title, null: false
  t.text :body
  t.virtual :searchable, type: :tsvector, stored: true, as: <<~SQL.squish
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  SQL
  t.timestamps
end
add_index :docs, :searchable, using: :gin

That round trips into schema.rb without structure.sql, which is the part worth knowing before you decide this is too exotic:

t.virtual "searchable", type: :tsvector, as: "(setweight(to_tsvector('english'::regconfig, (COALESCE(title, ''::character varying))::text), 'A'::\"char\") || setweight(to_tsvector('english'::regconfig, COALESCE(body, ''::text)), 'B'::\"char\"))", stored: true
t.index ["searchable"], name: "index_docs_on_searchable", using: :gin

Leave out stored: true and Rails refuses at migration time rather than at the database, from postgresql/schema_creation.rb:132:

ArgumentError: PostgreSQL versions before 18 do not support VIRTUAL (not persisted) generated columns.
Specify 'stored: true' option for 'bad'

A STORED column is the one you want here regardless, since a GIN index needs something persisted to index.

The write to a generated column that silently does nothing

Assigning to a generated column is the dead end in this post, and it costs an hour because nothing complains.

d.update!(searchable: "'nope'")

No exception. Active Record knows the column is generated, Doc.columns_hash["searchable"].virtual? is true, and it simply leaves the column out of the statement:

UPDATE "docs" SET "updated_at" = $1 WHERE "docs"."id" = $2

The in-memory attribute then reads back "'nope'" for the rest of the request, because the object was assigned and the object does not know the database ignored it. A reload returns the real value. So a spec that assigns and asserts in the same example passes, and a spec that assigns, reloads and asserts fails, and the difference between the two is one line nobody thinks of as meaningful.

The right mental model is that the column is output, not state. You write title, the database writes searchable, and every backfill task you were about to write for it does not need to exist.

Turning what the user typed into a tsquery

Four functions build a tsquery and only one of them survives arbitrary user input. to_tsquery takes operator syntax, and a search box produces operator syntax by accident:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error in tsquery: "rails & | search ("

e.cause is PG::SyntaxError. This is a 500 triggered by a user typing a parenthesis.

websearch_to_tsquery parses the same string without raising, and parses it the way a person expects a search box to behave. The manual lists the rules, and they are short: quoted text becomes a phrase with <->, the word OR becomes |, a leading dash becomes !, and "other punctuation is ignored". Run it and see:

'"full text"'       =>  'full' <-> 'text'
'search -postgres'  =>  'search' & !'postgr'
'postgres OR rails' =>  'postgr' | 'rail'
'rails & | search (' => 'rail' & 'search'

The one thing it will not do is prefix matching, since it "will not recognize tsquery operators, weight labels, or prefix-match labels in its input". So post does not match "Postgres", while to_tsquery('english', 'post:*') does. If the feature is an as-you-type dropdown rather than a search results page, that limitation is the feature, and the usual answer is to append :* to the final term yourself after escaping it. That is also the moment to reconsider whether the dropdown wants a trigram index instead.

Stop words are the other silent empty result. to_tsvector('english', 'the of and a') is the empty vector, and a query of nothing but stop words matches nothing while emitting a NOTICE nobody reads: text-search query contains only stop words or doesn't contain lexemes, ignored.

Ranking is the half that costs

Filtering with GIN is cheap. Ordering by relevance is not, and the two get written on the same line so the cost is easy to miss.

Here is a query matching 25000 of 200001 rows, with LIMIT 20 and no ordering:

 Limit (actual rows=20 loops=1)
   Buffers: shared read=15
   ->  Seq Scan on articles (actual rows=20 loops=1)
         Filter: (searchable @@ '''deploy'''::tsquery)
         Rows Removed by Filter: 140

Fifteen buffers, and note the planner did not even use the index: one row in eight matches, so reading pages until twenty turn up is cheaper. Now add relevance ordering and change nothing else:

 Limit (actual rows=20 loops=1)
   Buffers: shared hit=1997 read=9781
   ->  Sort (actual rows=20 loops=1)
         Sort Key: (ts_rank(searchable, '''deploy'''::tsquery)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on articles (actual rows=25000 loops=1)
               Recheck Cond: (searchable @@ '''deploy'''::tsquery)
               Heap Blocks: exact=11765
               Buffers: shared hit=1994 read=9781
               ->  Bitmap Index Scan on articles_searchable_gin (actual rows=25000 loops=1)
                     Buffers: shared hit=10

Fifteen buffers became 11778. The GIN index found the 25000 matches in 10 buffers and then could not help further, because an inverted index has no notion of the order you asked for. All 25000 rows came off the heap so that ts_rank could be evaluated on each one, and then twenty of them were kept.

The manual says this plainly and it deserves to be quoted rather than paraphrased: "Ranking can be expensive since it requires consulting the tsvector of each matching document, which can be I/O bound and therefore slow. Unfortunately, it is almost impossible to avoid since practical queries often result in large numbers of matches."

Two consequences for the shape of the feature. First, a broad query is the expensive one, which is backwards from the intuition that a rare term is hard to find. Second, paging through ranked results recomputes the whole sort on every page, so page 40 of a search costs what page 1 cost plus a larger offset, and the fix is not the keyset cursor from pagination without a gem, because a rank is not a static, unique, indexed column. Capping results at a few hundred is the honest answer, and it is what the dedicated engines do too.

What the index costs on the write path

Two costs, both reproducible, both measured against two copies of the same 200001 rows. One copy carries the stored tsvector column and its GIN index, the other carries neither.

Storage first. The title and body columns hold 36 MB of text. The generated tsvector column holds 45 MB, more than the text it was derived from, because setweight keeps position information per lexeme. strip() of the same vectors measures 31 MB, so roughly a third of that column is positions you are paying for and only need if you want phrase search or ts_rank_cd. The GIN index adds another 15 MB on top.

WAL next, since that is what replication and backup are actually charged for. Discard any run whose plan shows fpi= and you get steady state figures:

with tsvector column + GIN:   WAL: records=4069  bytes=634680
plain table, one btree:       WAL: records=3058  bytes=279011

Roughly 2.3 times the WAL bytes per row written, for a column nothing in your application code writes. That is the real cost of this rung, and it is charged on inserts and on any update that touches a source column, not on searches.

One knob is worth knowing about before you tune it. GIN batches new entries into a pending list by default, and turning that off with ALTER INDEX ... SET (fastupdate = off) took the same insert to records=10080 bytes=838413. Slower writes, no pending list for searches to scan linearly, and the same knob with the same shape that jsonb columns in Rails measures from the containment side.

The accent that will not go in the generated column

Searching "cafe" does not find "café", and the fix does not compose with the generated column, which is the second dead end here.

SELECT to_tsvector('english','café') @@ websearch_to_tsquery('english','cafe');
 f

The unaccent extension solves the matching half. It does not solve the storage half:

CREATE TABLE accent_test (
  id bigserial PRIMARY KEY,
  title text,
  searchable tsvector GENERATED ALWAYS AS (to_tsvector('english', unaccent(coalesce(title,'')))) STORED
);
ERROR:  generation expression is not immutable

SELECT proname, provolatile FROM pg_proc WHERE proname = 'unaccent' returns s, for stable. Stable is not immutable, a generated column requires immutable, and PostgreSQL refuses. The extension's own dictionary can be reconfigured at runtime, which is precisely why it cannot promise the same answer forever.

The workaround everyone lands on is a wrapper function declared IMMUTABLE that calls unaccent with an explicit dictionary argument, which is a promise you are making on the database's behalf: if anybody ever changes that dictionary, your stored vectors are stale and nothing recomputes them. Take it seriously or skip accents. For an English language product, skipping is defensible. For anything with French or Spanish content in it, it is not, and this is the first real argument for moving up a rung.

Rung three: what pg_search actually generates

pg_search 2.4.0 shipped on 2026-09-07 under MIT, requires Ruby 3.3 or newer, and has 51,969,952 downloads. The repository was last pushed on 2026-09-21 with 157 open issues against 1586 stars, so the gem that looked dormant between 2022 and 2024 is being worked on again: 2.3.8 in August 2026 and 2.4.0 a month later.

What it buys is real. pg_search_scope gives you a named scope, multi-column configuration, associated-model search via PgSearch.multisearch, ts_headline highlighting, trigram and dmetaphone features you can combine with full text in one scope, and the ranking already wired up.

What it does by default is the part to run before adopting:

pg_search_scope :plain_search, against: [:title, :body]

plain_search('deployments') -> 0
plain_search('deployment')  -> 25000

Zero for the plural. The reason is one line in lib/pg_search/features/tsearch.rb:

def dictionary
  Arel::Nodes.build_quoted(options[:dictionary] || :simple)
end

The default dictionary is simple, which does not stem and does not drop stop words. Passing using: { tsearch: { dictionary: "english" } } returned 25000 for the plural. Full text search without stemming is a slower ILIKE that cannot do substrings, so this default is the single most important thing to change.

The second default is the index. Without tsvector_column, the scope computes to_tsvector at query time on every row:

->  Parallel Seq Scan on articles articles_1
      Filter: ((to_tsvector('simple'::regconfig, COALESCE(title, ''::text)) || ...) @@ ...)
      Buffers: shared hit=928 read=11428

Point it at the generated column with using: { tsearch: { tsvector_column: "searchable" } } and the plan picks up Bitmap Index Scan on articles_searchable_gin. Which means the useful configuration of pg_search sits on top of the column you built at rung two rather than replacing it.

Two more behaviours worth reading before they surprise you. The gem splits the query on whitespace and joins terms with &&, so every word is required unless you pass any_word: true. And it sanitises rather than escapes: DISALLOWED_TSQUERY_CHARACTERS = /['?\\:]/ replaces apostrophes, question marks, backslashes and colons with spaces, so o'brien is searched as o brien.

Every scope also emits an INNER JOIN (SELECT ... ts_rank ... ) ON id = pg_search_id with an ORDER BY rank DESC baked in, always. There is no unranked mode, so the ranking cost from the previous section is not optional at this rung.

Rung four: a separate search service

searchkick 6.1.2 shipped on 2026-06-05 under MIT, by Andrew Kane, with 28,613,345 downloads, 6719 stars and 9 open issues, last pushed 2026-09-17. The health of the project is not the question. The question is the sentence in its README: the current version supports Elasticsearch 8 and 9 plus OpenSearch 2 and 3, and version 5.5.2 is what you use for Elasticsearch 7 or OpenSearch 1.

That sentence is the cost. Adopting searchkick means running a second stateful service, backing it up, monitoring it, and tracking its major version separately from Postgres and from Rails, with the gem's supported matrix in between. A Rails 8 application that deliberately removed Redis to have one fewer of those, which is what running Rails 8 without Redis is about, should notice it is adding one back and bigger.

What you get in exchange is the list Postgres does not have: typo tolerance out of the box, synonyms, personalised results, aggregations for faceted filtering, and reindexing without downtime. Typo tolerance in particular has no good Postgres answer. similarity('postgres', 'postgress') returns 0.727 and the % operator matches, so trigram similarity gets you a fuzzy match, but "did you mean" ranked against an analyser is a different product.

The alternatives are lighter and less settled. meilisearch-rails is at 0.16.0, released 2025-05-21, with 965179 downloads, so it is a pre-1.0 client for a service that is itself moving fast. typesense-rails is at 1.0.0.rc8, released 2026-04-26, with 23918 downloads total, which is a release candidate and a rounding error of adoption. Both services are genuinely nicer to operate than a JVM cluster. Neither has the deployment history that makes searchkick a boring choice.

Where to stop, and what would move the line

Stop at rung two. A stored tsvector generated column with a GIN index and websearch_to_tsquery is about fifteen lines of migration, no dependency, no second service, and it handles stemming, phrases, negation, OR, and weighted ranking. For the search box on a SaaS product with a few hundred thousand rows, that is the whole feature, and the plans above are the evidence rather than an opinion.

The cost of that recommendation, stated so it can be argued with: no typo tolerance, no synonyms, no facets, accents only if you are willing to declare a function immutable and mean it, and a ranking query whose cost grows with how common the search term is. You will also write the query parsing and the highlighting yourself, or add pg_search for them.

Take rung three when the scope configuration is the work: several models, multisearch, combining trigram with full text in one query, ts_headline snippets. Configure it with dictionary: "english" and tsvector_column: on day one, which makes it a nice API over the column you already have rather than a different implementation.

Take rung four when a product person asks for typo tolerance or faceted filtering by name, when search is the primary interface rather than a convenience, or when the corpus is large enough that the ranking sort above is the thing paging out your buffer cache. Not before. "We might need it later" is how a team ends up operating an Elasticsearch cluster for a search box that gets forty queries a day.

What would move this line: a GIN opclass that could return rows in rank order would delete the ranking section and most of the argument for rung four at small scale. A pg_search release that defaulted to english and to an existing tsvector column would make rung three the obvious starting point instead of a thing to configure carefully. And unaccent becoming immutable, or to_tsvector gaining accent folding, would remove the one gap that pushes non-English products up the ladder early.

What this post does not cover

The LaunchKit boilerplate this site sells has no search feature. No search gem is in its Gemfile, no tsvector column is in its schema, its only enabled extension is pg_catalog.plpgsql, and no controller in it builds a text query. Everything above came from a scratch database, pg_search's source, searchkick's README, and the PostgreSQL manual, which is the honest provenance for a post that would otherwise be tempted to point at a product.

Also absent: vector search and pgvector, which answers a different question than any rung here and belongs in its own post; the RUM index type, which does store rank information and would change the ranking section if it were packaged the way GIN is; MySQL's FULLTEXT indexes, where the operators and the plans are not these; multi-tenant search, where every query carries a tenant predicate the GIN index cannot serve and a composite btree_gin index usually can; and any millisecond timing, because buffer counts and row counts reproduce on your hardware and timings do not.

The pg_search reproductions ran against pg_search 2.4.0, which pulled activerecord 8.1.4 as a dependency. Every other Active Record result above is from 8.1.3.1, the version this site runs.