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

Why PostgreSQL Full-Text Search Is Underused in Laravel

Most Laravel projects reach for Algolia or Meilisearch the moment a client says "search". Both are excellent, but they add operational cost, sync complexity, and eventual-consistency headaches. PostgreSQL's built-in full-text search handles millions of rows with sub-10ms queries when set up correctly. This article shows you the exact migration, model wiring, and query patterns to make it production-ready.

Setting Up the tsvector Column

Store a pre-computed search vector alongside your data. A generated column keeps it in sync automatically — no triggers, no observers.

// database/migrations/2024_06_01_000000_add_search_vector_to_articles.php
public function up(): void
{
    DB::statement("
        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
    ");

    DB::statement(
        'CREATE INDEX articles_search_vector_gin ON articles USING GIN (search_vector)'
    );
}

The GENERATED ALWAYS AS … STORED syntax (PostgreSQL 12+) means the column is recomputed on every INSERT or UPDATE with no application-level code. setweight assigns priority: title matches outrank body matches during ranking.

Querying from Eloquent

Wrap the raw SQL in a clean local scope so callers never see the plumbing.

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

public function scopeSearch(Builder $query, string $term): Builder
{
    $tsQuery = 'plainto_tsquery(\'english\', ?)';

    return $query
        ->whereRaw("search_vector @@ {$tsQuery}", [$term])
        ->selectRaw(
            "*, ts_rank(search_vector, {$tsQuery}) AS rank",
            [$term]
        )
        ->orderByDesc('rank');
}

Usage is clean:

$results = Article::search('event sourcing laravel')
    ->with('author')
    ->paginate(20);

plainto_tsquery tokenises a plain string safely — no need to sanitise operators. Use phraseto_tsquery when word order matters (e.g. "event sourcing" as a phrase).

Phrase Queries and Prefix Matching

// Exact phrase
DB::raw("search_vector @@ phraseto_tsquery('english', ?)")

// Prefix (autocomplete-style) — note the :* operator
DB::raw("search_vector @@ to_tsquery('english', ? || ':*')")

Prefix matching is useful for live search inputs. Combine it with a LIMIT 10 and a covering index on (search_vector, id, title) to avoid a heap fetch.

Multilingual Configuration

PostgreSQL ships with text-search configurations for dozens of languages. Store the user's locale and pass the matching configuration name:

public function scopeSearch(Builder $query, string $term, string $lang = 'english'): Builder
{
    // Allowlist to prevent SQL injection via the config name
    $allowed = ['english', 'french', 'german', 'spanish', 'portuguese'];
    $config  = in_array($lang, $allowed, true) ? $lang : 'english';

    return $query
        ->whereRaw(
            "search_vector @@ plainto_tsquery('{$config}', ?)",
            [$term]
        );
}

For truly multilingual content in a single table, store multiple vectors — one per language — and query the appropriate column based on the request locale.

Highlighting Snippets

Return highlighted excerpts without a second round-trip:

->selectRaw(
    "ts_headline(
        'english',
        body,
        plainto_tsquery('english', ?),
        'MaxWords=35, MinWords=15, StartSel=<mark>, StopSel=</mark>'
    ) AS excerpt",
    [$term]
)

Bind the result to a virtual attribute on the model:

protected $appends = ['excerpt'];

public function getExcerptAttribute(): ?string
{
    return $this->attributes['excerpt'] ?? null;
}

Key Takeaways

• Generated stored columns keep tsvector in sync with zero application code.
• GIN indexes make @@ queries fast even on millions of rows.
• ts_rank gives relevance ordering; setweight lets you tune title vs. body priority.
• plainto_tsquery is safe for user input; to_tsquery with :* enables prefix search.
• ts_headline returns highlighted snippets in the same query, avoiding extra round-trips.
• Allowlist language config names before interpolating them into SQL.