Is full‑text search better than LIKE queries?

Yes — it’s faster with proper indexes and returns ranked, linguistically aware results (stemming, stop‑words). Use it once your content outgrows naive queries.

Should I use ElasticSearch instead?

Only if you need fuzzy matching at scale, aggregations, or cross‑field analytics. For most early/mid‑stage SaaS, Postgres FTS is simpler and good enough.

How do I avoid slow queries?

Create a tsvector column, keep it updated with a trigger, and index it with GIN. Avoid calculating tsvector on the fly in WHERE clauses.

Why FTS beats ‘CTRL+F in the database’

Users forgive a lot — except a search box that returns noise. My first attempt was WHERE title LIKE '%query%', which worked until we had 30k records and queries started timing out. Switching to PostgreSQL’s full‑text search (FTS) got us <30ms responses and better relevance without adding a new service.

The minimal schema

Let’s say you have a posts table with title and body. We’ll add a search tsvector column, populate it, and keep it fresh with a trigger.

ALTER TABLE posts ADD COLUMN search tsvector;

-- Initial backfill
UPDATE posts
SET search =
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')),  'B');

-- GIN index for fast search
CREATE INDEX posts_search_gin ON posts USING GIN (search);

-- Trigger to keep it updated
CREATE FUNCTION posts_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search :=
    setweight(to_tsvector('english', coalesce(NEW.title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body,'')),  'B');
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_update BEFORE INSERT OR UPDATE
  ON posts FOR EACH ROW EXECUTE FUNCTION posts_search_update();

Weights A/B bias titles over bodies. Use ‘english’ unless your content is predominantly in another language.

Laravel query example

// App\Models\Post.php
public function scopeSearch($query, string $term)
{
    $tsquery = str_replace(' ', ' & ', trim($term));
    return $query
        ->whereRaw("search @@ to_tsquery('english', ?)", [$tsquery])
        ->selectRaw("posts.*, ts_rank(search, to_tsquery('english', ?)) AS rank", [$tsquery])
        ->orderByDesc('rank');
}

// Usage
$posts = Post::search(request('q', ''))->limit(20)->get();

If you prefer phrase‑like behavior, plainto_tsquery is more forgiving than to_tsquery for end‑user input.

Result highlighting (nice DX win)

SELECT id, title,
  ts_headline('english', body, to_tsquery('english', $1),
    'StartSel=<mark>,StopSel=</mark>,MaxFragments=2,ShortWord=2') AS snippet
FROM posts
WHERE search @@ to_tsquery('english', $1)
ORDER BY ts_rank(search, to_tsquery('english', $1)) DESC
LIMIT 20;

Wrap that in a read‑only query on the Laravel side for a great UI snippet without shipping a separate highlighter.

Practical gotchas I hit

Don’t compute to_tsvector(title || ' ' || body) in the WHERE clause — it kills indexes.
If queries are still slow, check your stats target and VACUUM (ANALYZE) frequency.
Multi‑language content? Add multiple language vectors and combine them, or use simple config if your copy is heavy on code/identifiers.
Rank ties? Secondary sort by created_at to keep results stable.

When to upgrade to a search service

Switch when you need:

Synonyms and typo tolerance at scale
Faceted filtering across many dimensions
Complex relevance tuning per user segment

Until then, Postgres FTS is a gift: no extra infra, strong relevance, and one migration away.

If you’re working on broader growth foundations, pair this with clean navigation and great content. Our SEO checklist is a solid companion.

Make Search Useful: PostgreSQL Full‑Text Search for SaaS (with Laravel)