Stop Paying for Algolia: PostgreSQL Full-Text Search in Laravel

The Premature Optimization Trap

When building a B2B SaaS platform at Smart Tech Devs, a fast and intelligent search bar is a core requirement. A user types "invoice marketing" and expects the dashboard to instantly return all related documents, even if they misspelled a word or used different tenses. The immediate reflex for many developers is to reach for a dedicated search engine like Elasticsearch, Algolia, or Meilisearch.

While these tools are incredible, they introduce massive architectural complexity. You now have to sync your primary database with an external service, handle failed job queues when syncing drops, and manage expensive monthly subscription fees. If your platform has under 10 million rows, setting up an external search cluster is premature optimization. You already have a world-class search engine sitting right inside your database: PostgreSQL Full-Text Search (FTS).

How PostgreSQL FTS Works

Standard SQL LIKE '%query%' is horribly slow because it cannot use indexes and performs a full table scan. It also doesn't understand language—it just looks for exact string matches.

PostgreSQL FTS works differently. It parses your text into a tsvector (a sorted list of distinct words, normalized to their roots—so "running" becomes "run"). When a user searches, it converts their query into a tsquery. Because we can attach a special GIN Index to our tsvector column, the database finds matches in milliseconds.

Step 1: The Database Migration

Let's architect a highly performant search index for an articles table. We use a Generated Column so PostgreSQL automatically updates our search index whenever the row is inserted or updated.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class CreateArticlesTable extends Migration
{
    public function up(): void
    {
        Schema::create('articles', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('content');
            $table->timestamps();
        });

        // 1. Add a generated 'tsvector' column combining title and content.
        // We give the 'title' more weight (A) than the 'content' (B) for ranking.
        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(content, '')), 'B')
            ) STORED;
        ");

        // 2. Add a GIN index to make the search blazingly fast
        DB::statement("CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);");
    }
}

Step 2: Querying the FTS Index in Laravel

Now, we can query this optimized column using Laravel's whereRaw method, utilizing PostgreSQL's native @@ match operator and websearch_to_tsquery, which allows users to type naturally just like they would on Google.

namespace App\Http\Controllers;

use App\Models\Article;
use Illuminate\Http\Request;

class SearchController extends Controller
{
    public function index(Request $request)
    {
        $query = $request->input('q');

        if (!$query) {
            return response()->json([]);
        }

        // Search the generated vector column and order by relevance (Rank)
        $results = Article::query()
            ->select('id', 'title')
            ->whereRaw("search_vector @@ websearch_to_tsquery('english', ?)", [$query])
            ->orderByRaw("ts_rank(search_vector, websearch_to_tsquery('english', ?)) DESC", [$query])
            ->limit(20)
            ->get();

        return response()->json($results);
    }
}

The Engineering ROI

By leveraging PostgreSQL's native FTS, you keep your architecture centralized. There are no webhooks to manage, no external synchronization queues, and zero monthly API costs. You gain typo-tolerance, root-word stemming, and ranking algorithms entirely for free, scaling comfortably to millions of rows before you ever need to look at external solutions.