How to build RAG search in Laravel with PostgreSQL and pgvector

1. 1

   Laravel app on PostgreSQL

   DB_CONNECTION=pgsql in .env.

2. 2

   pgvector installed

   Managed Postgres often has it; or use the pgvector/pgvector Docker image.

3. 3

   An embeddings API key

   This guide uses OpenAI; any provider works.

4. 4

   A queue worker

   Embedding is slow; never do it in the request.

docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_PASSWORD=secret \
  pgvector/pgvector:pg17

public function up(): void
{
    DB::statement('CREATE EXTENSION IF NOT EXISTS vector');

    Schema::create('chunks', function (Blueprint $table) {
        $table->id();
        $table->foreignId('document_id')->constrained()->cascadeOnDelete();
        $table->text('content');
        $table->timestamps();
    });

    // 1536 = dimensions of text-embedding-3-small
    DB::statement('ALTER TABLE chunks ADD COLUMN embedding vector(1536)');
    DB::statement("ALTER TABLE chunks ADD COLUMN tsv tsvector
        GENERATED ALWAYS AS (to_tsvector('english', content)) STORED");

    DB::statement('CREATE INDEX chunks_embedding_idx ON chunks USING hnsw (embedding vector_cosine_ops)');
    DB::statement('CREATE INDEX chunks_tsv_idx ON chunks USING gin (tsv)');
}

// app/Jobs/EmbedDocument.php
public function handle(): void
{
    $chunks = collect(preg_split('/\n\s*\n/', $this->document->text))
        ->map(fn ($p) => trim($p))->filter()
        ->chunkWhile(fn ($p, $k, $c) => mb_strlen($c->implode("\n\n")) < 3000)
        ->map(fn ($c) => $c->implode("\n\n"));

    foreach ($chunks->chunk(100) as $batch) {
        $res = Http::withToken(config('services.openai.key'))
            ->post('https://api.openai.com/v1/embeddings', [
                'model' => 'text-embedding-3-small',
                'input' => $batch->values()->all(),
            ])->throw()->json('data');

        foreach ($batch->values() as $i => $text) {
            DB::insert('INSERT INTO chunks (document_id, content, embedding, created_at, updated_at)
                        VALUES (?, ?, ?::vector, now(), now())', [
                $this->document->id, $text, '[' . implode(',', $res[$i]['embedding']) . ']',
            ]);
        }
    }
}

Vector search finds passages with the same meaning; full-text search catches exact terms (names, codes). RRF merges both ranked lists: each passage scores the sum of 1 / (60 + rank).

$sql = <<<'SQL'
WITH vec AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> ?::vector) AS r
  FROM chunks ORDER BY embedding <=> ?::vector LIMIT 50
), fts AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS r
  FROM chunks, plainto_tsquery('english', ?) q
  WHERE tsv @@ q ORDER BY ts_rank_cd(tsv, q) DESC LIMIT 50
)
SELECT c.id, c.content,
       COALESCE(1.0 / (60 + vec.r), 0) + COALESCE(1.0 / (60 + fts.r), 0) AS score
FROM chunks c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE vec.id IS NOT NULL OR fts.id IS NOT NULL
ORDER BY score DESC LIMIT 8
SQL;

$v = '[' . implode(',', $questionEmbedding) . ']';
$hits = DB::select($sql, [$v, $v, $question]);

$context = collect($hits)->values()
    ->map(fn ($h, $i) => '[' . ($i + 1) . '] ' . $h->content)->implode("\n\n");

$answer = Http::withToken(config('services.openai.key'))
    ->post('https://api.openai.com/v1/chat/completions', [
        'model' => 'gpt-4o-mini',
        'messages' => [
            ['role' => 'system', 'content' =>
                'Answer ONLY from the numbered sources. Cite like [1]. '
                . 'If the sources do not contain the answer, say you do not know.'],
            ['role' => 'user', 'content' => "Sources:\n$context\n\nQuestion: $question"],
        ],
    ])->throw()->json('choices.0.message.content');

Return the passages with the answer so the UI can link each [n] to its source document.

1. type "vector" does not exist

   Run CREATE EXTENSION vector in that database (needs privileges).

2. Dimension mismatch error

   The column size must equal the model output (1536 here).

3. Search is slow

   Check the HNSW index is used (EXPLAIN) and the opclass matches <=> (cosine).

4. Answers make things up

   Tighten the prompt, lower temperature, and refuse when no passages score well.

1. pgvector enabled

   HNSW + GIN indexes created.

2. Embedding runs in a queue

   Batched and retried on failure.

3. Hybrid search returns sensible hits

   Tested with real questions.

4. Every answer cites sources

   And says "I don't know" when it should.