pmatseykanets/laravel-scout-postgres
A PostgreSQL full-text search driver for Laravel Scout. Index Eloquent models with native tsvector and GIN indexes, run ranked searches, and paginate results using PostgreSQL's built-in search capabilities instead of external services.
This driver replaces Algolia/Meilisearch with PostgreSQL's native full‑text search (tsvector / tsquery), so everything lives in your existing database and is backed up, migrated, and tested with it. You get ranking, stemming, and GIN‑indexed prefix/lexeme matching without an external service — at the cost of doing schema work yourself.
Minimal path:
Install and select the driver. Scout must already be installed.
composer require pmatseykanets/laravel-scout-postgres
php artisan vendor:publish --provider="PMatseykanets\ScoutPostgres\ScoutPostgresServiceProvider" --tag=config
Then in config/scout.php:
'driver' => env('SCOUT_DRIVER', 'pgsql'),
'pgsql' => [
'searchable' => 'searchable', // tsvector column name
'ts_config' => 'english', // text search configuration
],
Add the tsvector column, a GIN index, and a trigger to each searchable table. The package does not do this for you — this is the core piece of setup.
public function up(): void
{
DB::statement("ALTER TABLE posts ADD COLUMN searchable tsvector NOT NULL DEFAULT ''");
DB::statement("CREATE INDEX posts_searchable_idx ON posts USING GIN (searchable)");
// PG 11+: EXECUTE FUNCTION; PG < 11: EXECUTE PROCEDURE
DB::statement("
CREATE TRIGGER posts_searchable_update
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(searchable, 'pg_catalog.english', title, body)
");
}
Backfill existing rows once:
UPDATE posts
SET searchable = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
Add Scout's trait and search.
class Post extends Model
{
use \Laravel\Scout\Searchable;
}
Post::search('eloquent postgres')->get();
Post::search('eloquent')->where('status', 'published')->paginate(20);
Where to look first: vendor/pmatseykanets/laravel-scout-postgres/src/ — specifically PostgresEngine.php (it will tell you exactly which tsquery function is used for matching and ranking, and whether update()/delete() are no‑ops because the trigger handles them) and the service provider (to see how Scout::extend() registers the engine). The README's migration/trigger example is the canonical setup. Also read Scout's own Laravel\Scout\Builder to know which filters chain.
First use case: a posts table searchable on title and body. If that works end‑to‑end (typing a stemmed word like running matches run), you've validated config, trigger, and index together.
1. Trigger‑maintained column (default, recommended). Let PostgreSQL own the searchable column. On INSERT/UPDATE, tsvector_update_trigger re-derives it from the listed columns; on DELETE, the row (and its vector) disappears. Scout's Searchable trait still fires saved/deleted events, but there is no external index to sync — check PostgresEngine::update(); in practice you can often ignore scout:import and scout:flush entirely. This is the lowest‑maintenance setup for straightforward column sets.
2. Generated column (PG 12+, no trigger). Cleaner migrations and no trigger to forget:
ALTER TABLE posts ADD COLUMN searchable tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
setweight(to_tsvector('english', coalesce(body,'')), 'B')
) STORED;
Trade‑offs: the expression must be IMMUTABLE, so use the two‑argument to_tsvector('english', ...) form (the one‑arg form is not immutable) and coalesce every nullable column. You cannot reference other tables, so this rules out composing text from relations.
3. Weighted / multi‑field ranking. Assign setweight per field (A title, B body, C tags, D metadata) as above, then rely on the engine's ts_rank ordering, or rank manually when Scout's builder is too rigid:
Post::search($q)
->query(fn ($query) => $query->orderByRaw(
"ts_rank(searchable, websearch_to_tsquery('english', ?)) DESC", [$q]
))
->take(50)
->get();
(Builder::query() exists in recent Scout; on older versions drop to DB::table() for advanced needs.)
4. Multi‑language. With one global ts_config, the config value, the trigger's config, and the query config must be the same regconfig or stemming mismatches silently degrade recall. For truly multilingual content, add per‑language columns (searchable_en, searchable_de), one trigger or generated expression each, and choose the column in the engine/query for that locale.
5. Filter + search chaining. Because the engine only appends the @@ predicate and rank ordering to the Eloquent builder, ordinary scopes, where, and paginate compose normally. Keep orderBy calls off search results unless you intend to override relevance, and remember toSearchableArray() / searchableAs() are Scout concepts that may be irrelevant here — the database decides what's in the vector.
6. Testing. Run the suite against PostgreSQL, not SQLite; RefreshDatabase plus your migration creates the column, index, and trigger, so factory inserts populate searchable automatically. Seed a fixture, then assert Post::search('term')->count() — this catches trigger/config drift early.
7. Escape hatch. For anything the engine can't express (phrase search, websearch_to_tsquery, ts_headline snippets, custom rank normalization), write the query directly against the same column and index. The package and raw SQL happily coexist.
Engine contract has evolved (search() return types, Builder internals). Expect to fork, bump composer.json, and patch method signatures. For a new project on current Laravel, weigh this against a maintained driver.tsvector_update_trigger(...) later requires dropping and recreating the trigger; otherwise new fields are silently unindexed.body is assembled from a relation, the vector goes stale when the relation changes. Denormalize the text into the parent row, or update searchable explicitly on relation save.ts_config in config/scout.php, the trigger, and the generated expression must agree. Symptoms: searching for the exact indexed word works, but morphological variants don't.plainto_tsquery semantics. It ANDs terms and ignores operators — no quotes, no OR, no -exclusion. websearch_to_tsquery (PG 11+) gives users Google‑style syntax; phraseto_tsquery gives phrase matching. If users expect these, bypass the engine with whereRaw.the, a, and very short lexemes; a search for them returns nothing. This is PostgreSQL, not a bug in the package.plainto_tsquery('english', '') produces an empty query that matches zero rows (or, depending on engine handling, all rows). Decide explicitly and guard $q in controllers.EXPLAIN ANALYZE your search query; a missing or unused index means sequential scans. Build large indexes with CREATE INDEX CONCURRENTLY in a standalone migration (it can't run inside a transaction)._searchable_idx can collide. Name indexes explicitly.whereRaw, so trashed rows are excluded from results — but they still occupy index space. Purge if volume matters.getTotalCount() on your Scout version returns the filtered count, not a stale value; rank ordering and count(*) can diverge if you customized ordering.DB::enableQueryLog() / DB::listen() is more reliable than toSql() on the Scout builder; also log the bound tsquery to see how PostgreSQL parsed the user's input.tsquery function, custom ranking, or highlighting, register your own engine in a service provider via Scout::extend('pgsql', fn () => new YourEngine(...)) — subclassing PostgresEngine is usually less work than editing vendor code.How can I help you explore Laravel packages today?