thakurcoder | Blog & Portfolio

TL;DR

Replace slow LIKE '%term%' scans with Postgres FTS (tsvector + GIN) for orders-of-magnitude faster and more relevant search.
Store a tsvector column (generated column or trigger), index it with GIN, filter with @@, and order with ts_rank() / ts_rank_cd().
Use setweight() to boost title/headline matches. Combine FTS with pg_trgm for fuzzy/typo-tolerant results.
Example code for migrations, Eloquent scopes, raw queries, and production tips included.

Why PostgreSQL Full-Text Search matters

If you've relied on ILIKE '%keyword%' for search, you've seen it: as your table grows, search time explodes. LIKE with leading wildcards forces a sequential scan and gives no notion of relevance, stemming, or language.

PostgreSQL FTS turns that around:

Index-backed lookups (GIN/GiST) instead of full table scans.
Language-aware tokenization and stemming.
Relevance ranking using ts_rank() / ts_rank_cd().
Built-in support for boolean and phrase queries.

Real-world impact: naive LIKE queries that took hundreds or thousands of milliseconds become single-digit milliseconds with FTS and a GIN index. No separate search service required.

How FTS works (short, practical)

tsvector — the searchable representation of a document: normalized lexemes + positions.
tsquery — the structured search expression (supports Boolean operators, phrase, prefix).
@@ operator — tests whether tsvector matches tsquery.
ts_rank() / ts_rank_cd() — score functions to order results by relevance.

Example core query:

SELECT
  *,
  ts_rank(search_vector, plainto_tsquery('english', 'laravel')) AS relevance
FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'laravel')
ORDER BY relevance DESC;

Use plainto_tsquery for safe user input → builds an ANDed tsquery; use to_tsquery or phraseto_tsquery for advanced operators/phrases.

FTS vs. LIKE/ILIKE: Performance and Relevance

Traditional LIKE or ILIKE queries have fundamental limitations compared to full-text search:

Performance Comparison

Feature	LIKE/ILIKE	Full-Text Search
Index Usage	Sequential scan (with wildcards)	GIN/GiST index lookup
Query Time	Linear with table size	Logarithmic with index
Ranking	None (arbitrary order)	Relevance scoring
Language Support	None	Stemming, stopwords
Multi-column	Manual concatenation	Built-in support

Important: A LIKE '%term%' query cannot use a normal B-tree index and must perform a full table scan, while FTS uses specialized indexes for fast lookups.

Real-World Performance Impact

One benchmark showed that adding a GIN index on a tsvector column reduced search query execution time from over 200ms to about 4ms. This dramatic improvement makes FTS essential for applications with large text datasets.

Setting Up FTS in Laravel

Migration: add a `tsvector` (Postgres 12+ generated column)

Prefer generated columns if your Postgres version supports them — they're simple and reliable.

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
 
return new class extends Migration
{
    public function up(): void
    {
        DB::statement("
            ALTER TABLE posts
            ADD COLUMN search_vector tsvector
            GENERATED ALWAYS AS (
                to_tsvector('english',
                    coalesce(title, '') || ' ' || coalesce(body, '')
                )
            ) STORED
        ");
 
        DB::statement("
            CREATE INDEX posts_search_idx
            ON posts
            USING GIN (search_vector)
        ");
    }
 
    public function down(): void
    {
        DB::statement("DROP INDEX IF EXISTS posts_search_idx");
        DB::statement("ALTER TABLE posts DROP COLUMN IF EXISTS search_vector");
    }
};

Notes

For Postgres <12, create a plain tsvector column and add a BEFORE INSERT OR UPDATE trigger using tsvector_update_trigger.
Use an expression index if you don't want a stored column: CREATE INDEX ON posts USING GIN (to_tsvector(...)).

Choosing the index: GIN vs GiST

GIN: Fastest query performance for FTS; larger on disk; slower on writes; typical choice for read-heavy search.
GiST: Smaller, cheaper updates; slower queries. Useful when write throughput is critical and query latency tolerance is higher.

Default: GIN in most Laravel apps.

Querying from Laravel

Raw SQL with ranking (full control)

use App\Models\Post;
$searchTerm = 'laravel database';
 
$posts = Post::selectRaw("
        posts.*,
        ts_rank(search_vector, plainto_tsquery('english', ?)) as relevance
    ", [$searchTerm])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$searchTerm])
    ->orderBy('relevance', 'desc')
    ->limit(20)
    ->get();

This gives you control over ranking and normalization flags if you want them.

Eloquent scope (reusable)

// Post.php (Model)
public function scopeFts($query, string $q, int $limit = 20)
{
    return $query->select('posts.*')
        ->selectRaw("ts_rank(search_vector, plainto_tsquery('english', ?)) as relevance", [$q])
        ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$q])
        ->orderByDesc('relevance')
        ->limit($limit);
}
 
// Usage
$results = Post::fts($request->search)->get();

Laravel `whereFullText` (convenience)

Laravel 9+ supports whereFullText:

$posts = Post::whereFullText(['title', 'body'], $searchTerm)->get();

This is convenient but may not expose the ranking score — use raw SQL for custom ranking behaviour.

Advanced Query Examples

Boolean Search with to_tsquery

// Search for posts containing both 'laravel' AND 'postgresql'
$query = 'laravel & postgresql';
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, to_tsquery('english', ?)) as rank
    ", [$query])
    ->whereRaw("search_vector @@ to_tsquery('english', ?)", [$query])
    ->orderBy('rank', 'desc')
    ->get();

Phrase Search

// Search for exact phrase "full text search"
$query = "'full text search'";
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, to_tsquery('english', ?)) as rank
    ", [$query])
    ->whereRaw("search_vector @@ to_tsquery('english', ?)", [$query])
    ->orderBy('rank', 'desc')
    ->get();

Prefix Matching

// Search for words starting with 'post'
$query = 'post:*';
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, to_tsquery('english', ?)) as rank
    ", [$query])
    ->whereRaw("search_vector @@ to_tsquery('english', ?)", [$query])
    ->orderBy('rank', 'desc')
    ->get();

Ranking, Weighting, and Normalization

Understanding Ranking Functions

PostgreSQL provides two main ranking functions:

ts_rank(): Basic frequency-based ranking
ts_rank_cd(): Cover density ranking that considers term proximity

// Using ts_rank for basic relevance
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?)) as rank
    ", [$search])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$search])
    ->orderBy('rank', 'desc')
    ->get();
 
// Using ts_rank_cd for proximity-aware ranking
$posts = Post::selectRaw("
        posts.*, 
        ts_rank_cd(search_vector, plainto_tsquery('english', ?)) as rank
    ", [$search])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$search])
    ->orderBy('rank', 'desc')
    ->get();

Weighted Search Columns

Give different weights to different columns for better relevance:

// In your migration, create weighted tsvector
DB::statement("
    ALTER TABLE posts
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,'')), 'B')
    ) STORED
");

The default weight array is {0.1, 0.2, 0.4, 1.0} for D, C, B, A categories respectively.

Custom Weight Arrays

// Custom weights: title=1.0, body=0.5
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?), 
                ARRAY[0.5, 1.0]) as rank
    ", [$search])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$search])
    ->orderBy('rank', 'desc')
    ->get();

Normalization Flags

Apply normalization to account for document length:

// Normalize by document length (flag 2)
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?), 
                ARRAY[0.1, 0.2, 0.4, 1.0], 2) as rank
    ", [$search])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$search])
    ->orderBy('rank', 'desc')
    ->get();

Common normalization flags:

1: Divide by log(length + 1)
2: Divide by length
32: Scale to 0-1 range (rank/(rank+1))

Advanced Search Features

Combining FTS with Fuzzy Search

For typo tolerance, combine FTS with PostgreSQL's trigram extension:

// Enable trigram extension
DB::statement("CREATE EXTENSION IF NOT EXISTS pg_trgm");
 
// Create trigram index
DB::statement("CREATE INDEX posts_title_trgm_idx ON posts USING GIN (title gin_trgm_ops)");
 
// Combined FTS and fuzzy search
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?)) as fts_rank,
        similarity(title, ?) as similarity_score
    ", [$search, $search])
    ->whereRaw("
        (search_vector @@ plainto_tsquery('english', ?) OR title % ?)
    ", [$search, $search])
    ->orderByRaw("GREATEST(ts_rank(search_vector, plainto_tsquery('english', ?)), similarity(title, ?)) DESC", [$search, $search])
    ->get();

Multi-Language Support

// Search in multiple languages
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?)) as rank
    ", [$search])
    ->whereRaw("
        search_vector @@ plainto_tsquery('english', ?) OR
        search_vector @@ plainto_tsquery('spanish', ?) OR
        search_vector @@ plainto_tsquery('french', ?)
    ", [$search, $search, $search])
    ->orderBy('rank', 'desc')
    ->get();

Search with Filters

// Combine FTS with other filters
$posts = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?)) as rank
    ", [$search])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$search])
    ->where('published', true)
    ->where('created_at', '>=', now()->subMonths(6))
    ->orderBy('rank', 'desc')
    ->get();

Performance Optimization

Index Strategy

Choose the right index type for your workload:

-- GIN index (recommended for most cases)
CREATE INDEX posts_search_gin_idx ON posts USING GIN (search_vector);
 
-- GiST index (smaller, but slower for lookups)
CREATE INDEX posts_search_gist_idx ON posts USING GiST (search_vector);

GIN vs GiST:

GIN: Faster lookups, larger size, slower updates
GiST: Smaller size, faster updates, slower lookups

Query Optimization

// Use EXPLAIN ANALYZE to check query performance
$query = Post::selectRaw("
        posts.*, 
        ts_rank(search_vector, plainto_tsquery('english', ?)) as rank
    ", [$search])
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$search])
    ->orderBy('rank', 'desc');
 
// Check the execution plan
DB::enableQueryLog();
$posts = $query->get();
$queries = DB::getQueryLog();

Caching Search Results

<?php
 
namespace App\Services;
 
use Illuminate\Support\Facades\Cache;
use App\Models\Post;
 
class SearchService
{
    public function search(string $query, int $limit = 20)
    {
        $cacheKey = "search:{$query}:{$limit}";
        
        return Cache::remember($cacheKey, 300, function () use ($query, $limit) {
            return Post::selectRaw("
                    posts.*, 
                    ts_rank(search_vector, plainto_tsquery('english', ?)) as rank
                ", [$query])
                ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$query])
                ->orderBy('rank', 'desc')
                ->limit($limit)
                ->get();
        });
    }
}

Production Best Practices

Monitoring and Maintenance

// Check index usage
$indexStats = DB::select("
    SELECT 
        schemaname,
        tablename,
        indexname,
        idx_scan,
        idx_tup_read,
        idx_tup_fetch
    FROM pg_stat_user_indexes 
    WHERE indexname = 'posts_search_idx'
");
 
// Monitor query performance
$slowQueries = DB::select("
    SELECT 
        query,
        calls,
        total_time,
        mean_time
    FROM pg_stat_statements 
    WHERE query LIKE '%ts_rank%'
    ORDER BY mean_time DESC
    LIMIT 10
");

Error Handling

<?php
 
namespace App\Services;
 
use Illuminate\Support\Facades\Log;
use App\Models\Post;
 
class SearchService
{
    public function search(string $query, int $limit = 20)
    {
        try {
            // Validate search query
            if (strlen($query) < 2) {
                return collect();
            }
 
            // Escape special characters for tsquery
            $escapedQuery = preg_replace('/[^\w\s]/', ' ', $query);
            
            return Post::selectRaw("
                    posts.*, 
                    ts_rank(search_vector, plainto_tsquery('english', ?)) as rank
                ", [$escapedQuery])
                ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$escapedQuery])
                ->orderBy('rank', 'desc')
                ->limit($limit)
                ->get();
                
        } catch (\Exception $e) {
            Log::error('Search failed', [
                'query' => $query,
                'error' => $e->getMessage()
            ]);
            
            // Fallback to simple LIKE search
            return Post::where('title', 'ILIKE', "%{$query}%")
                ->orWhere('body', 'ILIKE', "%{$query}%")
                ->limit($limit)
                ->get();
        }
    }
}

Security Considerations

// Validate and sanitize user input
public function search(Request $request)
{
    // Validate input at the top
    $validated = $request->validate([
        'q' => 'required|string|min:2|max:100|regex:/^[a-zA-Z0-9\s\-]+$/'
    ]);
    
    $query = trim($validated['q']);
    
    // Additional sanitization for extra safety
    $query = preg_replace('/\s+/', ' ', $query); // Normalize whitespace
    
    return $this->searchService->search($query);
}

Benchmark expectations (realistic numbers)

Benchmarks depend on data and hardware, but expect:

Rows	`LIKE '%term%'`	FTS + GIN	Speedup (approx)
10K	180 ms	4 ms	45x
100K	1,800 ms	6 ms	300x
1M	18,000 ms	8 ms	2,250x

Takeaway:

FTS with a GIN index scales orders of magnitude better.
GiST provides smaller index footprint but is slower for reads.
For fuzzy substring matches on short strings, pg_trgm + GIN makes ILIKE '%term%' usable.

Production checklist & best practices

Use parameterized queries — never interpolate raw user input into to_tsquery/plainto_tsquery.
Choose generated columns (Postgres ≥12) or reliable triggers (older versions) to keep tsvector up to date.
Index the tsvector with GIN by default; consider GiST if writes are extremely heavy.
Weight title, headers, or metadata higher using setweight.
Normalize ranks if long documents unfairly dominate (experiment with flags 1, 2, 32).
Combine with pg_trgm for fuzzy/typo tolerance on short fields (title, names, SKUs).
Use EXPLAIN ANALYZE — confirm Bitmap Index Scan / Index Scan is used; avoid Seq Scan.
Backfill tsvector after migration or use UPDATE to populate generated column for existing rows.
Reindex CONCURRENTLY during maintenance when rebuilding indexes.
Cache top/expensive search results for short TTLs (5–15 min).
Track search terms and tune weights based on real user data.

Common debugging steps

If you see Seq Scan on search: ANALYZE table; and confirm index exists and is valid.
If ranking looks wrong: inspect to_tsvector(...) and plainto_tsquery(...) to verify tokenization and stemming.
If special characters break queries: sanitize or escape user input; use plainto_tsquery() for user text rather than to_tsquery() unless you explicitly parse operators.

Example quick debug:

SELECT id, title,
  ts_rank(search_vector, plainto_tsquery('english', 'laravel post')) AS rank
FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'laravel post')
ORDER BY rank DESC LIMIT 10;

When to use Postgres FTS — and when not

Use it when:

You need language-aware relevance for content (blogs, docs, product descriptions).
You want to avoid running and maintaining separate search infra.
Your dataset fits a single-node RDBMS or moderate scale (up to ~10M documents depending on workload).

Consider alternatives when:

You need horizontal scaling across many nodes at extreme volume (100M+ docs).
You need specialized features like advanced semantic search, complex faceting at massive scale, or rich analytics — then Elasticsearch/Meilisearch/Algolia might be appropriate.

Troubleshooting Common Issues

Low Relevance Scores

// Debug ranking by examining raw scores
$debugResults = DB::select("
    SELECT 
        id,
        title,
        ts_rank(search_vector, plainto_tsquery('english', ?)) as rank,
        to_tsvector('english', title || ' ' || body) as vector
    FROM posts 
    WHERE search_vector @@ plainto_tsquery('english', ?)
    ORDER BY rank DESC
    LIMIT 10
", [$search, $search]);

Index Not Being Used

-- Check if index is being used
EXPLAIN ANALYZE 
SELECT * FROM posts 
WHERE search_vector @@ plainto_tsquery('english', 'search term');
 
-- Look for "Bitmap Index Scan" in the output

Slow Updates

// For high-write scenarios, consider using GiST index
DB::statement("DROP INDEX posts_search_gin_idx");
DB::statement("CREATE INDEX posts_search_gist_idx ON posts USING GiST (search_vector)");

Conclusion (from a Laravel dev with 6 years on the backend)

I implement Postgres FTS in most Laravel apps that need good, reliable search without the operational burden of a search cluster. It's fast, language-aware, and flexible: with tsvector + GIN + ts_rank you get relevance ranking that users expect. For fuzziness, add pg_trgm. For heavy write loads, choose indexes and maintenance strategies that fit your workload.

Start small: add a generated tsvector, create the GIN index, use a simple plainto_tsquery() flow, then iterate — tune weights, normalize ranks, and introduce trigrams only where necessary. In practice, this approach delivers production-grade search in hours, not weeks.