PostgreSQL Full‑Text Search for SaaS Apps in Laravel

Why FTS beats ‘CTRL+F in the database’

A search box that returns noise frustrates users quickly. The usual first attempt is WHERE title LIKE '%query%'. It works on a small table, but a leading wildcard can’t use a normal index, so every search scans the table, and the results aren’t ranked or aware of word forms ("invoice" won’t match "invoices"). PostgreSQL’s full‑text search (FTS) addresses both without adding a new service.

The minimal schema

Say you have a posts table with title and body. Add a search tsvector column, populate it, and keep it fresh with a trigger. In Laravel, put this SQL in a migration with DB::unprepared() (and drop the trigger, function, index and column in down()).

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.

On PostgreSQL 12 or later you can skip the trigger and use a stored generated column instead:

ALTER TABLE posts ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')),  'B')
  ) STORED;

The GIN index is the same either way.

Laravel query example

// app/Models/Post.php
use Illuminate\Database\Eloquent\Builder;

public function scopeSearch(Builder $query, string $term): Builder
{
    $term = trim($term);

    if ($term === '') {
        return $query->whereRaw('false');
    }

    return $query
        ->select('posts.*')
        ->selectRaw("ts_rank(search, websearch_to_tsquery('english', ?)) AS rank", [$term])
        ->whereRaw("search @@ websearch_to_tsquery('english', ?)", [$term])
        ->orderByDesc('rank')
        ->orderByDesc('created_at');
}

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

Use websearch_to_tsquery (PostgreSQL 11+) or plainto_tsquery for end‑user input. to_tsquery expects its own operator syntax, so passing raw input straight into it raises a syntax error as soon as someone types a quote, colon or stray &. websearch_to_tsquery also understands quoted phrases, or and -exclusions.

Result highlighting

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

Run it with DB::select() and a bound parameter to get snippets without shipping a separate highlighter. ts_headline works on the raw text and is relatively expensive, so apply it only to the rows you display. The <mark> tags are inserted around text from your database, so escape or sanitize the body before rendering the snippet as HTML.

Common gotchas

• Don’t compute to_tsvector(title || ' ' || body) in the WHERE clause; unless it exactly matches an expression index, Postgres can’t use your GIN index.
• 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? Add a secondary sort (the scope above uses 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 gives you ranked search with no extra infrastructure, one migration away.

Site search helps people who are already on your site; to be found in the first place, see the SaaS SEO checklist.