Pgvector Similarity Search in Production Postgres

Dimensionality and RAM: Math Before Indexes

Vector search isn't free magic. Running pgvector similarity search on million-row tables without understanding memory trade-offs will crash your Postgres instance faster than a runaway query on an unindexed foreign key.

Modern embedding models output vectors with high dimensions. OpenAI's text-embedding-3-small outputs 1,536 dimensions, while text-embedding-3-large pushes 3,072 dimensions. Open-source models like bge-m3 output 1,024 dimensions. In Postgres, pgvector stores dimensions as 4-byte single-precision floats. A single 1,536-dimensional vector takes 6,144 bytes of disk space—roughly 6KB per row. Store one million vectors, and your raw vector column consumes 6GB before accounting for table overhead, index structures, or toast storage.

Postgres provides three distance operators in pgvector:

• <-> - Euclidean distance (L2 distance). Measures straight-line distance between two points in vector space. Useful when magnitude matters.
• <#> - Negative inner product (dot product). Fastest operator, but requires normalized vectors (length of 1.0) to output meaningful rankings.
• <=> - Cosine distance. Measures the angle between vectors, completely ignoring magnitude. Standard choice for semantic text search.

Cosine distance formula in pgvector is 1 - cosine_similarity. When querying, if you want cosine similarity scores between 0 and 1 where 1 means identical, calculate 1 - (embedding <=> query_vector) in your projection.

HNSW vs IVFFlat: Picking the Right Index

Without an index, Postgres executes a sequential scan across every row, computing the vector distance for each row on the fly. On a table of 50,000 vectors with 1,536 dimensions, exact nearest neighbor sequential scans take around 250ms. At 500,000 rows, that jumps to 2.8 seconds. You need an approximate nearest neighbor (ANN) index.

pgvector offers two index types: IVFFlat (Inverted File Flat) and HNSW (Hierarchical Navigable Small World).

IVFFlat: Low Footprint, High Maintenance

IVFFlat divides vector space into lists (clusters) using k-means clustering. When querying, Postgres searches only the vectors inside the lists nearest to your query vector.

The gotcha with IVFFlat: you must build the index after your table is populated with representative data. If you build an IVFFlat index on an empty table and insert 100,000 rows later, performance degrades completely. The k-means centroids won't reflect the new data distribution, ruining recall. You have to run REINDEX INDEX periodically as your dataset grows.

Rule of thumb for IVFFlat list counts: for tables up to 1 million rows, set lists = rows / 1000. For over 1 million rows, set lists = sqrt(rows).

HNSW: Fast Queries, Heavy Index Build

HNSW builds a multi-layer graph where nodes represent vectors and edges represent proximity. Upper layers have sparse connections for fast traversal across long distances; lower layers have dense connections for fine-grained local search.

HNSW outperforms IVFFlat in almost every production scenario for three reasons:

1. You can create an HNSW index on an empty table. It builds incrementally as rows arrive without degrading search recall.
2. Query latency is lower at high recall levels (95%+ recall at sub-10ms response times).
3. It does not require periodic rebuilding under normal write loads.

The downside? Memory. HNSW graph structures require substantial RAM. Building an HNSW index on 1,000,000 vectors of 1,536 dimensions takes roughly 1.5GB to 2GB of memory during construction and increases index disk size compared to IVFFlat.

To build HNSW safely in production, configure maintenance_work_mem higher than the default 64MB before running the CREATE INDEX query:

SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY idx_documents_embedding_hnsw 
ON documents 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

The m parameter dictates the maximum number of bidirectional links created per vector node (default 16). Higher m improves recall and search speed at the cost of index size and build time. ef_construction controls the search depth during index creation (default 64). Doubling ef_construction to 128 improves index quality, but doubles build duration.

Laravel 12 and PHP 8.3 Query Patterns

In PHP 8.3, vector embeddings are handled as packed float arrays or formatted strings matching Postgres vector syntax '[0.012,-0.043,0.089]'. When performing pgvector similarity search inside Laravel 12, use database transactions or raw sessions to tune runtime query precision with hnsw.ef_search.

The hnsw.ef_search session variable controls how many dynamic candidate vectors HNSW evaluates during a query. Default is 40. Increasing ef_search raises recall accuracy but increases latency. Set this per-transaction based on your endpoint requirements.

Here is a production repository class in PHP 8.3 written for Laravel 12:

<?php

namespace App\Repositories;

use Illuminate\Support\Facades\DB;
use InvalidArgumentException;

class VectorSearchRepository
{
    /**
     * Perform vector search using cosine similarity.
     * 
     * @param float[] $embedding
     * @param int $limit
     * @param float $minSimilarity
     * @return array<int, object>
     */
    public function search(array $embedding, int $limit = 10, float $minSimilarity = 0.70): array
    {
        if (empty($embedding)) {
            throw new InvalidArgumentException('Embedding vector cannot be empty.');
        }

        $vectorString = '[' . implode(',', array_map('floatval', $embedding)) . ']';

        return DB::transaction(function () use ($vectorString, $limit, $minSimilarity) {
            // Boost candidate search depth for this query session
            DB::statement('SET LOCAL hnsw.ef_search = 100');

            return DB::select('
                SELECT 
                    id,
                    title,
                    content,
                    1 - (embedding <=> ?::vector) AS similarity_score
                FROM knowledge_articles
                WHERE 1 - (embedding <=> ?::vector) >= ?
                ORDER BY embedding <=> ?::vector ASC
                LIMIT ?
            ', [$vectorString, $vectorString, $minSimilarity, $vectorString, $limit]);
        });
    }
}

Notice the usage of SET LOCAL. Using SET LOCAL inside a Postgres transaction scopes the configuration change exclusively to that transaction block. It prevents connection pool pollution when using persistent database connections like PgBouncer or Swoole/Octane workers in PHP 8.3.

Next.js 16 Server Components and Route Handlers

When running vector searches from Next.js 16 React Server Components or Route Handlers, execute vector queries directly on Node.js using standard database pools like pg or postgres.js. Avoid client-side vector calculations entirely—embeddings must remain on the server to protect API keys and reduce payload sizes.

Below is a Next.js 16 API Route Handler (App Router) fetching embeddings and executing pgvector similarity search against Postgres:

import { NextResponse } from 'next/server';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

export async function POST(request: Request) {
  try {
    const { queryEmbedding } = await request.json();

    if (!Array.isArray(queryEmbedding) || queryEmbedding.length === 0) {
      return NextResponse.json({ error: 'Invalid vector input' }, { status: 400 });
    }

    const vectorString = JSON.stringify(queryEmbedding);
    const client = await pool.connect();

    try {
      await client.query('BEGIN');
      await client.query('SET LOCAL hnsw.ef_search = 80');

      const searchQuery = `
        SELECT 
          id, 
          heading, 
          body_text, 
          1 - (embedding <=> $1::vector) AS score
        FROM documentation_chunks
        ORDER BY embedding <=> $1::vector ASC
        LIMIT $2;
      `;

      const result = await client.query(searchQuery, [vectorString, 10]);
      await client.query('COMMIT');

      return NextResponse.json({ data: result.rows });
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to process vector search query' },
      { status: 500 }
    );
  }
}

Production Gotchas That Will Bite You

Implementing pgvector similarity search in real-world systems comes with sharp edges that don't show up in quick tutorial benchmarks.

1. Memory Exhaustion During HNSW Build

Building HNSW indexes on tables with hundreds of thousands of rows will crash Postgres if maintenance_work_mem is too low. Postgres defaults to 64MB. When HNSW runs out of maintenance memory, it falls back to spilling temp files to disk, dragging index creation time from 4 minutes to 3 hours, or failing outright with out of memory errors. Set maintenance_work_mem to 1GB or 2GB before running your migration.

2. Connection Pooler Prepared Statements

If you use PgBouncer in transaction pooling mode, standard prepared statements with custom types like ::vector can throw errors like ERROR: prepared statement