PostgreSQL Full-Text Search in Laravel: Indexes, ts_rank, and Weighted Queries

Why PostgreSQL Full-Text Search Is Often Enough

Before reaching for Algolia, Meilisearch, or Elasticsearch, consider what PostgreSQL already provides: ranked results, stemming, stop-word filtering, multi-language dictionaries, and weighted column importance — all inside the same ACID-compliant database your app already uses.

For datasets under a few million rows with moderate query volume, a well-indexed tsvector column outperforms an external service in operational simplicity and latency.

Setting Up a Generated tsvector Column

The cleanest approach is a generated stored column — PostgreSQL maintains it automatically on insert and update.

// 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(subtitle, '')), 'B') ||
            setweight(to_tsvector('english', coalesce(body, '')), 'C')
        ) STORED
    ");
}

Weights A–D influence ts_rank scoring. Title matches outrank body matches automatically.

Now add a GIN index — the right index type for tsvector:

public function up(): void
{
    DB::statement(
        'CREATE INDEX articles_search_vector_gin ON articles USING GIN (search_vector)'
    );
}

A GiST index is an alternative but GIN is faster for read-heavy search workloads.

A Composable Eloquent Scope

Wrap the query logic in a reusable scope so callers stay expressive:

// app/Models/Scopes/FullTextSearchScope.php

namespace App\Models\Scopes;

use Illuminate\Database\Eloquent\Builder;

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

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

Usage in a controller or action:

$results = Article::search('event sourcing laravel')
    ->where('published', true)
    ->cursorPaginate(20);

The @@ operator uses the GIN index. ts_rank is computed only for matching rows, so it's cheap.

Handling Partial / Prefix Queries

plainto_tsquery doesn't support prefix matching. For autocomplete-style input, switch to to_tsquery with a :* suffix:

public function scopeSearchPrefix(Builder $query, string $term): Builder
{
    // Sanitise: strip non-word characters, append :*
    $safe = preg_replace('/[^\w\s]/u', '', $term);
    $lexemes = collect(explode(' ', trim($safe)))
        ->filter()
        ->map(fn ($w) => $w . ':*')
        ->implode(' & ');

    return $query
        ->whereRaw("search_vector @@ to_tsquery('english', ?)", [$lexemes])
        ->orderByRaw("ts_rank(search_vector, to_tsquery('english', ?)) DESC", [$lexemes]);
}

Always sanitise user input before constructing a to_tsquery expression — malformed lexemes throw a PostgreSQL error.

Highlighting Matched Terms

PostgreSQL's ts_headline returns a snippet with matches highlighted:

$results = Article::search($term)
    ->selectRaw("
        id, title, published_at,
        ts_headline(
            'english', body,
            plainto_tsquery('english', ?),
            'MaxWords=35, MinWords=15, ShortWord=3'
        ) AS snippet
    ", [$term])
    ->cursorPaginate(20);

ts_headline is CPU-intensive — only call it on the final paginated slice, never on a full table scan.

Testing the Search Scope

it('ranks title matches above body matches', function () {
    $titleMatch = Article::factory()->create([
        'title' => 'Event Sourcing in Laravel',
        'body'  => 'Some unrelated content here.',
    ]);
    $bodyMatch = Article::factory()->create([
        'title' => 'Unrelated Title',
        'body'  => 'Event sourcing is a pattern for recording state changes.',
    ]);

    $results = Article::search('event sourcing')->pluck('id');

    expect($results->first())->toBe($titleMatch->id);
});

This test requires a real PostgreSQL connection — use a dedicated test database, not SQLite.

Key Takeaways

• Use a generated stored tsvector column to keep indexing automatic and queries simple.
• A GIN index on the tsvector column is mandatory for production performance.
• Weight columns (A–D) so ts_rank naturally promotes title matches over body matches.
• Use plainto_tsquery for phrase input and to_tsquery with :* for prefix/autocomplete.
• Call ts_headline only on the paginated result set, not during filtering.
• Keep search logic in a trait-based Eloquent scope for reuse across models.