PostgreSQL Search in Laravel

External search engines add complexity. PostgreSQL full-text search provides powerful capabilities built-in. At ZIRA Software, PostgreSQL search handles millions of queries without ElasticSearch overhead.

Setup

Migration:

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->text('content');
    // Add ts_vector column for search
    $table->tsvector('searchable');
    $table->timestamps();

    // GIN index for fast search
    $table->index('searchable', 'posts_searchable_index', 'gin');
});

Model Configuration

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Post extends Model
{
    protected $fillable = ['title', 'content'];

    protected static function booted()
    {
        static::saving(function ($post) {
            // Update searchable column on save
            $post->searchable = DB::raw("
                setweight(to_tsvector('english', coalesce(\"{$post->title}\", '')), 'A') ||
                setweight(to_tsvector('english', coalesce(\"{$post->content}\", '')), 'B')
            ");
        });
    }

    public static function search($query)
    {
        return static::whereRaw("searchable @@ plainto_tsquery('english', ?)", [$query])
            ->selectRaw("*, ts_rank(searchable, plainto_tsquery('english', ?)) as rank", [$query])
            ->orderBy('rank', 'desc');
    }
}

Basic Search

$results = Post::search('laravel tutorial')->get();

Advanced Features

Weighted search:

public static function weightedSearch($query)
{
    return static::selectRaw("
        *,
        ts_rank(
            setweight(to_tsvector('english', title), 'A') ||
            setweight(to_tsvector('english', content), 'B'),
            plainto_tsquery('english', ?)
        ) as rank
    ", [$query])
    ->whereRaw("
        (to_tsvector('english', title) || to_tsvector('english', content))
        @@ plainto_tsquery('english', ?)
    ", [$query])
    ->orderBy('rank', 'desc');
}

Highlighting:

public static function searchWithHighlight($query)
{
    return static::selectRaw("
        *,
        ts_headline('english', title, plainto_tsquery('english', ?)) as highlighted_title,
        ts_headline('english', content, plainto_tsquery('english', ?)) as highlighted_content
    ", [$query, $query])
    ->whereRaw("searchable @@ plainto_tsquery('english', ?)", [$query]);
}

Performance Optimization

Trigger-based updates:

CREATE OR REPLACE FUNCTION posts_search_trigger() RETURNS trigger AS $$
begin
  new.searchable :=
    setweight(to_tsvector('english', coalesce(new.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(new.content, '')), 'B');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_searchable_update BEFORE INSERT OR UPDATE
ON posts FOR EACH ROW EXECUTE FUNCTION posts_search_trigger();

Conclusion

PostgreSQL full-text search eliminates external dependencies while providing powerful search capabilities. Perfect for Laravel applications needing search without ElasticSearch complexity.

Need search implementation? Contact ZIRA Software for database optimization.