pgvector-search

pgvector Search for Laravel

Vector similarity search using PostgreSQL and pgvector.

When to Use

• Semantic search
• RAG (Retrieval-Augmented Generation)
• Similar content recommendations
• Document matching

---

1. Installation

PostgreSQL Extension

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

Migration

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

return new class extends Migration
{
    public function up(): void
    {
        // Enable pgvector
        DB::statement('CREATE EXTENSION IF NOT EXISTS vector');
        
        Schema::create('documents', function (Blueprint $table) {
            $table->id();
            $table->text('content');
            $table->json('metadata')->nullable();
            $table->timestamps();
        });
        
        // Add vector column (1536 dimensions for OpenAI embeddings)
        DB::statement('ALTER TABLE documents ADD COLUMN embedding vector(1536)');
        
        // Create index for fast similarity search
        DB::statement('CREATE INDEX documents_embedding_idx ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100)');
    }
    
    public function down(): void
    {
        Schema::dropIfExists('documents');
    }
};

---

2. Model

namespace App\Models;

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

class Document extends Model
{
    protected $fillable = ['content', 'metadata', 'embedding'];
    
    protected $casts = [
        'metadata' => 'array',
    ];
    
    /**
     * Set embedding from array
     */
    public function setEmbeddingAttribute(array $value): void
    {
        $this->attributes['embedding'] = '[' . implode(',', $value) . ']';
    }
    
    /**
     * Get embedding as array
     */
    public function getEmbeddingAttribute($value): ?array
    {
        if (!$value) {
            return null;
        }
        
        return json_decode($value);
    }
    
    /**
     * Find similar documents
     */
    public static function similarTo(array $embedding, int $limit = 5, float $threshold = 0.7): Collection
    {
        $vector = '[' . implode(',', $embedding) . ']';
        
        return static::select('*')
            ->selectRaw('1 - (embedding <=> ?) as similarity', [$vector])
            ->whereRaw('1 - (embedding <=> ?) > ?', [$vector, $threshold])
            ->orderByRaw('embedding <=> ?', [$vector])
            ->limit($limit)
            ->get();
    }
    
    /**
     * Search by text (requires embedding service)
     */
    public static function search(string $query, int $limit = 5): Collection
    {
        $openai = app(\App\Services\OpenAIClient::class);
        $embedding = $openai->embed($query);
        
        return static::similarTo($embedding, $limit);
    }
}

---

3. Repository Pattern

namespace App\Repositories;

use App\Models\Document;
use App\Services\OpenAIClient;
use Illuminate\Support\Collection;

class VectorSearchRepository
{
    public function __construct(
        private OpenAIClient $openai,
    ) {}
    
    /**
     * Store document with embedding
     */
    public function store(string $content, array $metadata = []): Document
    {
        $embedding = $this->openai->embed($content);
        
        return Document::create([
            'content' => $content,
            'metadata' => $metadata,
            'embedding' => $embedding,
        ]);
    }
    
    /**
     * Bulk store documents
     */
    public function storeMany(array $documents): void
    {
        $contents = array_column($documents, 'content');
        $embeddings = $this->openai->embeddings($contents);
        
        foreach ($documents as $i => $doc) {
            Document::create([
                'content' => $doc['content'],
                'metadata' => $doc['metadata'] ?? [],
                'embedding' => $embeddings[$i]['embedding'],
            ]);
        }
    }
    
    /**
     * Semantic search
     */
    public function search(
        string $query,
        int $limit = 5,
        array $filters = [],
    ): Collection {
        $embedding = $this->openai->embed($query);
        $vector = '[' . implode(',', $embedding) . ']';
        
        $query = Document::select('*')
            ->selectRaw('1 - (embedding <=> ?) as similarity', [$vector])
            ->orderByRaw('embedding <=> ?', [$vector])
            ->limit($limit);
        
        // Apply metadata filters
        foreach ($filters as $key => $value) {
            $query->whereRaw("metadata->>? = ?", [$key, $value]);
        }
        
        return $query->get();
    }
    
    /**
     * Hybrid search (semantic + keyword)
     */
    public function hybridSearch(
        string $query,
        int $limit = 5,
        float $semanticWeight = 0.7,
    ): Collection {
        $embedding = $this->openai->embed($query);
        $vector = '[' . implode(',', $embedding) . ']';
        
        // Combine semantic similarity with text search
        return Document::select('*')
            ->selectRaw(
                '(? * (1 - (embedding <=> ?))) + (? * ts_rank(to_tsvector(content), plainto_tsquery(?))) as score',
                [$semanticWeight, $vector, 1 - $semanticWeight, $query]
            )
            ->orderByDesc('score')
            ->limit($limit)
            ->get();
    }
}

---

4. Distance Operators

Operator	Name	Use Case
<=>	Cosine distance	Most common, normalized
<->	L2 distance	Euclidean distance
<#>	Inner product	Dot product

// Cosine similarity (1 - cosine distance)
->selectRaw('1 - (embedding <=> ?) as similarity', [$vector])

// L2 distance (lower is more similar)
->orderByRaw('embedding <-> ?', [$vector])

---

5. Indexing Strategies

IVFFlat (Approximate, Fast)

-- Good for large datasets
CREATE INDEX ON documents 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

HNSW (More Accurate, More Memory)

-- Better recall, slower build
CREATE INDEX ON documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Index Selection

Dataset Size	Index Type	Lists/M
< 100K	IVFFlat	100
100K - 1M	IVFFlat	1000
> 1M	HNSW	m=16

---

6. RAG Integration

class RAGService
{
    public function __construct(
        private VectorSearchRepository $vectorSearch,
        private OpenAIClient $openai,
    ) {}
    
    public function query(string $question): string
    {
        // 1. Retrieve relevant documents
        $documents = $this->vectorSearch->search($question, limit: 5);
        
        // 2. Build context
        $context = $documents
            ->pluck('content')
            ->join("\n\n---\n\n");
        
        // 3. Generate answer
        return $this->openai->prompt(
            prompt: "Based on this context:\n\n{$context}\n\nAnswer: {$question}",
            systemPrompt: 'Answer based only on the provided context. Say "I don\'t know" if the answer is not in the context.',
        );
    }
}

---

7. Performance Tips

Batch Embeddings

// Bad: N API calls
foreach ($texts as $text) {
    $embedding = $openai->embed($text);
}

// Good: 1 API call
$embeddings = $openai->embeddings($texts);

Use Approximate Search

// Set probes for IVFFlat (higher = more accurate, slower)
DB::statement('SET ivfflat.probes = 10');

---

Remember: Always create an index on the vector column. Use IVFFlat for most cases, HNSW for higher accuracy needs.