PostgreSQL Full-Text Search in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) PostgreSQL Full-Text Search in Laravel: Indexes, ts\_rank, and Weighted Queries On this page 1. [ Why PostgreSQL Full-Text Search Is Often Enough ](#why-postgresql-full-text-search-is-often-enough) 2. [ Setting Up a Generated tsvector Column ](#setting-up-a-generated-codetsvectorcode-column) 3. [ A Composable Eloquent Scope ](#a-composable-eloquent-scope) 4. [ Handling Partial / Prefix Queries ](#handling-partial-prefix-queries) 5. [ Highlighting Matched Terms ](#highlighting-matched-terms) 6. [ Testing the Search Scope ](#testing-the-search-scope) 7. [ Key Takeaways ](#key-takeaways) ![PostgreSQL Full-Text Search in Laravel: Indexes, ts_rank, and Weighted Queries](https://cdn.msaied.com/421/d1aeb29c36e4b62b103b6a2e0a534590.png) #laravel #postgresql #full-text-search #eloquent #performance PostgreSQL Full-Text Search in Laravel: Indexes, ts\_rank, and Weighted Queries ================================================================================= 13 Jul 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why PostgreSQL Full-Text Search Is Often Enough ](#why-postgresql-full-text-search-is-often-enough) 2. [ 02 Setting Up a Generated tsvector Column ](#setting-up-a-generated-codetsvectorcode-column) 3. [ 03 A Composable Eloquent Scope ](#a-composable-eloquent-scope) 4. [ 04 Handling Partial / Prefix Queries ](#handling-partial-prefix-queries) 5. [ 05 Highlighting Matched Terms ](#highlighting-matched-terms) 6. [ 06 Testing the Search Scope ](#testing-the-search-scope) 7. [ 07 Key Takeaways ](#key-takeaways) 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. ```php // 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`: ```php 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: ```php // 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: ```php $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: ```php 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: ```php $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 ------------------------ ```php 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-full-text-search-in-laravel-indexes-ts-rank-and-weighted-queries&text=PostgreSQL+Full-Text+Search+in+Laravel%3A+Indexes%2C+ts_rank%2C+and+Weighted+Queries) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-full-text-search-in-laravel-indexes-ts-rank-and-weighted-queries) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I use PostgreSQL full-text search with Laravel's SQLite test database? No. tsvector, GIN indexes, and ts_rank are PostgreSQL-specific. For tests that exercise full-text search, connect to a real PostgreSQL instance — a Docker service in CI works well. Q02 When should I choose an external search engine over PostgreSQL FTS? Consider Meilisearch or Elasticsearch when you need typo-tolerance, faceted filtering, synonyms, or real-time index replication across services. For straightforward ranked keyword search on a single database, PostgreSQL FTS is simpler and avoids an extra infrastructure dependency. Q03 Does the generated tsvector column update automatically when I update a row? Yes. A GENERATED ALWAYS AS ... STORED column is recomputed by PostgreSQL on every INSERT and UPDATE, so you never need application-level triggers or observers to keep it in sync. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![New in Laravel 13: First-Party Image Manipulation](https://cdn.msaied.com/427/504aed7f9f1c4a1cd411c7c4a4b6bc7c.png) Laravel 13 Image Manipulation PHP ### New in Laravel 13: First-Party Image Manipulation Laravel 13 introduces a first-party image manipulation feature, removing the need for third-party packages for... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Jul 2026 3 min read Read ](https://msaied.com/articles/new-in-laravel-13-first-party-image-manipulation) [ ![Filament v4 Testing with Pest: Resources, Actions, and Form Assertions](https://cdn.msaied.com/426/a81430a378b2849b6fbecb8235b9119f.png) filament pest testing ### Filament v4 Testing with Pest: Resources, Actions, and Form Assertions A practical guide to testing Filament v4 panels using Pest — covering resource page assertions, table action d... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Jul 2026 3 min read Read ](https://msaied.com/articles/filament-v4-testing-with-pest-resources-actions-and-form-assertions) [ ![Laravel Legacy Bridge: Carry Authenticated Sessions from a Legacy PHP App into Laravel](https://cdn.msaied.com/429/5abced4ee695d6c03e3ae41bbf2fe4bb.png) Laravel PHP Session Management ### Laravel Legacy Bridge: Carry Authenticated Sessions from a Legacy PHP App into Laravel Laravel Legacy Bridge is a package that reads a legacy session cookie, decodes the payload from the legacy dat... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-legacy-bridge-carry-authenticated-sessions-from-a-legacy-php-app-into-laravel) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)