Develop bandheart.com

25. Full-text search. Laravel Scout. PGroonga. Postgres extensions. Eloquent filters

Video version
(Leave your feedback on YouTube)

Full-text search is a vast topic. A complete analysis of algorithms, ready-made solutions, and their optimization is impossible to cover even in a series of articles.
In this article, I review and explain my choice for a specific situation. I reserve the right to make mistakes and change this choice in the future.

TL;DR

  • Standard non-SQL search engines are fast and efficient. But remember that you will lose SQL control.
  • And for a fully positive experience, you will have to build two architectures: relational and document-based, and also think about their synchronization.
  • This is possible, but very EXPENSIVE.
  • My choice for full-text search is PGroonga.
  • For production, I recommend using hosted DB services. But be prepared that they may not support the necessary extensions.
  • Postgres full-text search works well but has issues with setting up dictionaries on hosted services.
  • Eloquent filters.
  • If you want to guide the Postgres query planner — pg_hint_plan.
  • Explain visualizer

PGroonga

Documentation.

Use either the ready-made solution — Docker image,
or install it manually — Instructions.

Be careful when using similar search v2 — in case of a non-Index Scan, queries crash with a critical error!

Any extensions need to be enabled manually.

Migration
<?php

use Illuminate\Database\Migrations\Migration;

return new class extends Migration
{
    public function up(): void
    {
        DB::unprepared('CREATE EXTENSION IF NOT EXISTS pgroonga');
    }
};

My example of indexes:

Migration
<?php

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

return new class() extends Migration {
    public function up(): void
    {
        Schema::create('bands', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('slug')->unique();
            $table->typeColumn('text[]', 'synonyms')->nullable();
            $table->jsonb('description')->nullable();
        });

        DB::unprepared(
            "
                CREATE INDEX pgroonga_band_name ON bands
                USING pgroonga (
                    name pgroonga_varchar_full_text_search_ops_v2,
                    synonyms pgroonga_text_array_full_text_search_ops_v2
                )
                WITH (normalizers='NormalizerNFKC100')"
        );

        DB::unprepared(
            "
                CREATE INDEX pgroonga_band_description ON bands
                USING pgroonga (
                    description pgroonga_jsonb_full_text_search_ops_v2
                )
                WITH (normalizers='NormalizerNFKC100')"
        );
    }
};

Search example

Modules/Band/ModelFilters/BandFilter.php
 public function search(string $search): void
    {
        $this->where(function (Builder $query) use ($search) {
            $query
                ->whereRaw(
                    "bands.name &@~
                (?, ARRAY[3], 'pgroonga_band_name')::pgroonga_full_text_search_condition",
                    [$search]
                )
                ->orWhereRaw("bands.synonyms &@~
                (?, array_fill(2, array[100]), 'pgroonga_band_name')::pgroonga_full_text_search_condition", [$search])
                ->orWhereRaw('description &@~ ?', [$search]);
        });
    }

Help to survive