Message Search Performance

Why message content search (SearchService#filter_messages) is hard to make predictably fast, what we do today, and the industry-standard options for a durable fix. Context: messages is a single multi-tenant table (tens of millions of rows on prod), searched per-account by substring, newest first.

The query

“Find the 11 newest messages containing a substring, in one account, within the last 3 months” (MESSAGE_SEARCH_WINDOW):

SELECT messages.* FROM messages
WHERE account_id = ?
  AND conversation_id IN (SELECT id FROM conversations
                          WHERE account_id = ? AND archived = ? AND created_at >= ?)
  AND content ILIKE '%term%'
  AND created_at >= ?
ORDER BY created_at DESC LIMIT 11 OFFSET ?;

Why no single plan is predictable

Postgres has two possible strategies, and each one’s cost is proportional to the wrong thing — neither is bounded by the 11 rows we actually want:

Plan Cost proportional to Fast for Pathological for
Trigram gather (index_messages_on_content, gather all matches then sort) Total matches of the term, across all accounts Rare terms (xk7q…: ms) Common terms (hel: ~1.9M matches → lossy bitmaps, 78–140s)
Recency walk (index_messages_on_created_at DESC, stop at 11 matches) Rows scanned until 11 matches appear Common terms (ms) Rare terms (scans the whole 3-month window, 124s)

The regimes want opposite plans, and the planner cannot tell them apart: %term% selectivity is unestimable. Any static query shape is therefore slow in one regime. Predictable = making the work proportional to results returned. There are three levers: scope the index to the tenant, bound the search space by time, or store the sort order inside the search index so the scan can stop early.

Related trap (tried and rejected): an unordered inner LIMIT on the gather returns an arbitrary (physically oldest-biased) subset before the sort — wrong results, silently. A bound must be on time, newest-first (matching the sort order), never on an arbitrary row count.

What we do today (adaptive two-plan fallback)

SearchService#filter_messages runs each plan only in the regime where it is fast:

  1. Plan 1 (rare terms): the trigram plan, kept honest by an OFFSET 0 optimization fence, inside SET LOCAL statement_timeout (MESSAGE_SEARCH_TIMEOUT_MS). Milliseconds in its happy regime.
  2. On ActiveRecord::QueryCanceled → Plan 2 (common terms): the same filters unfenced, so the planner walks created_at DESC and stops at the LIMIT. Milliseconds exactly when Plan 1 blows up.
  3. Queries shorter than MIN_TRIGRAM_QUERY_LENGTH (3 chars — the minimum to form a trigram) skip Plan 1 entirely.

Worst case: timeout + fast walk, instead of 140s. Residual gap: Plan 2 is uncapped, so a mid-frequency term slow on both plans is possible (rare); index_messages_on_created_at is global, so tiny accounts whose matches are sparse among other tenants’ recent rows can also make the walk long.

Industry-standard options (effort ladder)

Tier 1 — dedicated search engine (the standard at scale)

Elasticsearch/OpenSearch (or Meilisearch/Typesense) with messages indexed per tenant. What Zendesk/Intercom/Slack-class products do. Predictable for a structural reason: documents are routed by tenant (a search touches only that account’s postings) and index-time sorting by recency lets the engine scan newest-first and early-terminate after filling top-N — bounded work for rare and common terms. Cost: sync pipeline (Sidekiq/CDC → index), eventual consistency (~seconds), one more piece of infra.

Tier 2 — Postgres-native

  1. btree_gin (account_id, content gin_trgm_ops) — recommended next step. Keeps substring semantics; scopes the trigram bitmap to one account (hel: 1.9M candidates → only that account’s). One migration (enable btree_gin extension + CREATE INDEX CONCURRENTLY). The timeout fallback stays as a safety net for common terms on huge accounts.
  2. Full-text search (tsvector + GIN, e.g. pg_search) — word-level matching. Caveat: FTS does not solve top-N-recent by itself — a GIN tsvector query still builds the full match set before sorting. Buys smaller match sets and stemming; loses mid-word matches.
  3. RUM index (extension) — GIN variant storing the timestamp in the index posting, so content @@ query ORDER BY created_at DESC LIMIT n early-terminates inside the index. Purpose-built for “search + order by time”; third-party and less battle-tested.
  4. Time-bucket iteration (query logic only, no new index) — search the newest week first, expand to older buckets only while results < N. Common terms finish in bucket 1; rare terms pay a handful of small queries. The correct “bounded search” when infra cannot change.

Tier 3 — adaptive plan steering (current state)

Correct and cheap, but steers around the problem rather than removing it.

Recommendation

Short term: btree_gin composite index — removes the pathological regime with one migration, all semantics preserved. Roadmap: an external search engine only if message search becomes a product priority; it is the only option predictably fast at any scale and term frequency.

Universal principle behind every tier: align the index’s physical order with the query’s sort order (ES index-sorting, RUM timestamp postings, and time buckets are the same idea at different layers — scan in the order you return, so you can stop at N). And avoid deep OFFSET pagination — page 40 forces producing 440 rows in even the best design; engines pair early-termination with cursor (“search after”) pagination for the same reason (we clamp via MAX_SEARCH_OFFSET).