Production RAG Vector Search with PostgreSQL (pgvector) and Laravel: The Zero-Subscription Architecture

As SaaS applications evolve to incorporate generative AI, engineering teams inevitably face the Retrieval-Augmented Generation (RAG) dilemma. The default advice from AI influencers is to add another managed cloud dependency: pay $100 to $500/month for Pinecone, Weaviate, or Qdrant, and deal with distributed two-phase transactions between your primary SQL database and your external vector index.

At Tech AKM, we build custom AI systems that optimize for architectural simplicity, ACID transactional safety, and zero cloud subscription tax.

By leveraging PostgreSQL 16 with the pgvector extension directly within your Laravel backend, you can store relational business entities and multi-dimensional vector embeddings in the exact same database. You get atomic transactions, instant JOIN queries, and sub-50ms cosine similarity searches for zero additional infrastructure cost.

1. Why pgvector Wins Over Dedicated Vector Databases

For 99% of SaaS applications handling under 5 million document chunks, a standalone vector database creates unnecessary operational overhead:

2. RAG Pipeline Architecture

graph TD
    UserQuery[User Query / Prompt] --> EmbedAPI[Embedding Generator: text-embedding-3-small or Gemini]
    EmbedAPI --> VectorQuery[Vector Array: 1536 float dimensions]
    
    subgraph PostgreSQL Database
        VectorQuery --> HNSW[HNSW Cosine Distance Index]
        HNSW --> TenantFilter[Tenant & Permission Filter: organization_id]
        TenantFilter --> TopK[Top-K Ranked Context Chunks]
    end
    
    TopK --> PromptContext[Injected System Prompt + Grounding Context]
    PromptContext --> LLM[Inference Model: Gemini 1.5 Flash / GPT-4o]
    LLM --> StreamOut[Streamed Markdown Answer to User]

3. Database Migration & Schema Design

First, ensure your PostgreSQL server has pgvector enabled. In Laravel, you can enable the extension directly inside a standard database migration.

Step 1: Enable Extension and Create Embeddings Table

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // Enable pgvector extension
        DB::statement('CREATE EXTENSION IF NOT EXISTS vector;');

        Schema::create('document_embeddings', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->foreignUuid('tenant_id')->constrained()->cascadeOnDelete();
            $table->foreignUuid('document_id')->constrained()->cascadeOnDelete();
            $table->integer('chunk_index');
            $table->text('chunk_text');
            $table->jsonb('metadata')->nullable();
            $table->timestamps();
        });

        // Add 1536-dimensional vector column (compatible with OpenAI and Gemini embeddings)
        DB::statement('ALTER TABLE document_embeddings ADD COLUMN embedding vector(1536);');

        // Create Hierarchical Navigable Small World (HNSW) cosine index for sub-50ms search
        DB::statement('CREATE INDEX document_embeddings_hnsw_idx ON document_embeddings USING hnsw (embedding vector_cosine_ops);');
    }

    public function down(): void
    {
        Schema::dropIfExists('document_embeddings');
    }
};

4. Chunking and Vector Ingestion Service

High-quality retrieval depends entirely on recursive chunking strategy. Never embed raw multi-page PDFs in single blocks; split by markdown headings and sentence boundaries with 10% token overlap.

namespace App\Services\AI;

use App\Models\DocumentEmbedding;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Str;

class RagEmbeddingService
{
    /**
     * Ingest and vectorize a document chunk.
     */
    public function storeChunk(string $tenantId, string $documentId, string $chunkText, int $index, array $meta = []): DocumentEmbedding
    {
        $embeddingVector = $this->generateEmbedding($chunkText);
        $vectorLiteral = '[' . implode(',', $embeddingVector) . ']';

        $embedding = new DocumentEmbedding([
            'id' => (string) Str::uuid(),
            'tenant_id' => $tenantId,
            'document_id' => $documentId,
            'chunk_index' => $index,
            'chunk_text' => $chunkText,
            'metadata' => $meta,
        ]);

        $embedding->save();

        // Write raw vector using parameterized PostgreSQL query
        \DB::statement(
            'UPDATE document_embeddings SET embedding = ?::vector WHERE id = ?',
            [$vectorLiteral, $embedding->id]
        );

        return $embedding;
    }

    /**
     * Call embedding API with retry backoff.
     */
    public function generateEmbedding(string $text): array
    {
        $response = Http::withToken(config('services.openai.key'))
            ->post('https://api.openai.com/v1/embeddings', [
                'model' => 'text-embedding-3-small',
                'input' => $text,
            ])
            ->throw();

        return $response->json('data.0.embedding');
    }
}

5. Sub-50ms Hybrid Vector Search Query

To perform retrieval, calculate cosine distance <=> or inner product <#> directly in raw SQL, ensuring you always filter by tenant_id first for security and index isolation:

namespace App\Services\AI;

use Illuminate\Support\Facades\DB;

class VectorSearchService
{
    /**
     * Retrieve Top-K semantic matches for a user query.
     */
    public function search(string $tenantId, array $queryEmbedding, int $limit = 5): array
    {
        $vectorLiteral = '[' . implode(',', $queryEmbedding) . ']';

        return DB::select(
            "SELECT id, document_id, chunk_text, metadata,
                    1 - (embedding <=> ?::vector) AS similarity_score
             FROM document_embeddings
             WHERE tenant_id = ?
             ORDER BY embedding <=> ?::vector
             LIMIT ?",
            [$vectorLiteral, $tenantId, $vectorLiteral, $limit]
        );
    }
}

6. Production Best Practices for Enterprise RAG

1. Partition by Tenant: When scaling past 10 million embeddings, use PostgreSQL declarative table partitioning by tenant_id to keep index sizes small enough to fit entirely inside RAM.
2. Re-Ranking for Accuracy: Vector similarity retrieves semantic candidates, but a lightweight cross-encoder re-ranker (like Cohere Rerank or a Flash LLM prompt) boosts retrieval precision by 35%.
3. Observability: Log vector cosine scores for every query. If all retrieved chunks score below 0.65 similarity, instruct the LLM to gracefully state that it lacks sufficient context rather than hallucinating.

Build Your Custom RAG Architecture with Tech AKM

Implementing production-grade vector search requires precise tuning of token chunking, cosine distance thresholds, and asynchronous background ingestion.

At Tech AKM, our senior engineering pods build, benchmark, and deploy enterprise RAG pipelines, autonomous AI agents, and custom SaaS MVPs within 4 to 6 weeks.

• Explore Our AI & Microservices Engineering
• Test Your Database Queries in Our Live AI Sandbox
• Schedule a 30-Minute Architecture Review