PostgreSQL Full-Text Search in Laravel: Indexes, Ranking, and Multilingual Queries

Why Reach for PostgreSQL FTS Before a Search Service

Algolia and Meilisearch are excellent, but they add operational cost, sync lag, and a network hop. For most SaaS products under a few million rows, PostgreSQL's native full-text search is fast enough, cheaper, and transactionally consistent with your writes.

This article covers the practical path: stored tsvector columns, GIN indexes, ranked results, and a clean Eloquent integration — including multilingual dictionary selection.

Storing a tsvector Column

Computing the vector on every query is wasteful. Store it as a generated column (PostgreSQL 12+) or maintain it via a trigger. The generated column approach is cleaner:

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

CREATE INDEX articles_search_vector_gin
  ON articles USING GIN (search_vector);

Weighting title as A and body as B lets ts_rank surface title matches above body-only matches automatically.

Querying from Eloquent

Laravel's query builder doesn't have a first-class FTS method, but raw expressions compose cleanly:

use Illuminate\Support\Facades\DB;

$query = 'laravel performance';

$articles = Article::query()
    ->whereRaw(
        "search_vector @@ plainto_tsquery('english', ?)",
        [$query]
    )
    ->orderByRaw(
        "ts_rank(search_vector, plainto_tsquery('english', ?)) DESC",
        [$query]
    )
    ->paginate(20);

plainto_tsquery is forgiving with user input — it ignores operators and treats the string as an AND of lexemes. Use websearch_to_tsquery (PostgreSQL 11+) when you want Google-style syntax ("exact phrase" OR term -exclude).

Encapsulating the Logic in a Scope

Avoid scattering raw SQL across controllers. A dedicated scope keeps things testable:

// app/Models/Scopes/FullTextSearchScope.php
namespace App\Models\Scopes;

use Illuminate\Database\Eloquent\Builder;

trait FullTextSearchable
{
    public function scopeSearch(Builder $query, string $term, string $lang = 'english'): Builder
    {
        $tsQuery = "websearch_to_tsquery('{$lang}', ?)";

        return $query
            ->whereRaw("search_vector @@ {$tsQuery}", [$term])
            ->orderByRaw("ts_rank(search_vector, {$tsQuery}) DESC", [$term]);
    }
}

// Usage
Article::search($request->input('q'))->paginate(20);

Security note: The $lang parameter is interpolated directly. Validate it against an allowlist (['english', 'french', 'german']) before passing it in.

Multilingual Dictionaries

PostgreSQL ships with dictionaries for dozens of languages. Store the user's preferred language on the tenant or user record and pass it through:

$lang = in_array($user->locale, ['english','french','german','spanish'])
    ? $user->locale
    : 'simple'; // 'simple' = no stemming, safe fallback

Article::search($request->q, $lang)->paginate(20);

The simple dictionary skips stemming entirely — useful for proper nouns, product codes, or when you can't determine the language.

Keeping the Vector Fresh on Updates

Generated columns update automatically on INSERT and UPDATE. If you're on PostgreSQL < 12 or need cross-table data in the vector, use a trigger or a queued observer:

// app/Observers/ArticleObserver.php
public function saved(Article $article): void
{
    DB::statement(
        "UPDATE articles
         SET search_vector =
           setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
           setweight(to_tsvector('english', coalesce(body,'')), 'B')
         WHERE id = ?",
        [$article->id]
    );
}

For high-write tables, dispatch this as a queued job to avoid blocking the HTTP response.

Trigram Fuzzy Matching as a Complement

FTS won't match partial words or typos. Add pg_trgm for fuzzy prefix search:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

->orWhereRaw('title % ?', [$query]) // similarity threshold default 0.3

Combine both: FTS for ranked semantic matches, trigram for autocomplete and typo tolerance.

Key Takeaways

• Store tsvector as a generated column and index it with GIN — never compute it per query.
• Use websearch_to_tsquery for user-facing input; it handles operators and is injection-safe.
• Weight columns (A–D) so ts_rank reflects content importance, not just frequency.
• Validate language dictionary names against an allowlist before interpolating into SQL.
• Layer pg_trgm on top for fuzzy/prefix matching without a separate search service.