Building a Functional Full-Text Search in Laravel with PostgreSQL

Creating a powerful and flexible search system in your Laravel app backed by PostgreSQL can dramatically improve user experience. This guide walks through how to implement full-text search using PostgreSQL's tsvector and GIN indexes for fast, relevant, typo-tolerant queries.

1. Add Search-Specific Fields to Your Table

In your main model's table (e.g., properties), add a search_vector column and a human-readable search_text column.

$table->text('search_text')->nullable();
DB::statement('ALTER TABLE properties ADD COLUMN search_vector tsvector');

2. Create a Trigger and Function to Maintain search_vector

Use a database-level trigger to automatically update the search_vector whenever data changes.

CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    to_tsvector('english',
      coalesce(NEW.title, '') || ' ' ||
      coalesce(NEW.description, '') || ' ' ||
      coalesce(NEW.address_line, '') || ' ' ||
      coalesce(NEW.locality, '') || ' ' ||
      coalesce(NEW.city, '') || ' ' ||
      coalesce(NEW.owner_name, '') || ' ' ||
      coalesce(NEW.search_text, '')
    );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER search_vector_trigger
BEFORE INSERT OR UPDATE ON properties
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

3. Add a GIN Index for Fast Lookup

CREATE INDEX properties_search_vector_idx
ON properties USING GIN (search_vector);

This allows you to query using @@ (text match operator) efficiently.

4. Normalize the Search Query (PHP Side)

Create a helper function to convert slang, currency shortforms, and synonyms.

function normalizeSearchQuery(string $query): string {
    $query = strtolower($query);

    $query = preg_replace_callback('/(\d+(?:\.\d+)?)k/', fn($m) => $m[1] * 1000, $query);
    $query = str_replace(['rs.', 'rs', '₹'], '', $query);

    $map = [
        'rooms near lpu' => 'pg apartment near lpu',
        'flat' => 'pg apartment',
        'hostel' => 'pg hostel',
        'coliving' => 'co-living',
        'less than' => 'under',
        'greater than' => 'above',
        'at ' => '',
        'in ' => '',
    ];

    foreach ($map as $k => $v) $query = str_replace($k, $v, $query);

    return trim(preg_replace('/\s+/', ' ', $query));
}

5. Update Your Laravel Controller

public function index(Request $request)
{
    $query = normalizeSearchQuery($request->input('q', ''));

    $results = Property::whereRaw(
        "search_vector @@ plainto_tsquery('english', ?)",
        [$query]
    )->paginate(10);

    return PropertyResource::collection($results);
}

6. Generating search_text

In your Property model, use an observer or hook:

protected static function booted() {
    static::saving(function ($property) {
        $property->search_text = "{$property->title} {$property->locality} {$property->city} with {$property->amenities} ₹{$property->price_per_month}/month";
    });
}

Debugging TipsCheck if Trigger Exists

SELECT tgname FROM pg_trigger WHERE tgrelid = 'properties'::regclass;

Check if Function Includes All Fields

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'update_search_vector';

Force Trigger to Re-run

UPDATE properties SET title = title WHERE id = 1;

Check Current Search Vector Output

SELECT search_vector FROM properties WHERE id = 1;

Check Index

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'properties';

Final Notes

• Keep search_text focused and meaningful
• Always GIN-index your tsvector
• Normalize queries before search
• You can add fallback similarity() or trigram indexes later for typo-tolerant search
• You can cache heavy queries with hash+result mappings

With this setup, you're ready to support fast, typo-tolerant, flexible search that works well for real-world user queries.