Last month we shipped a semantic search feature for a client's knowledge base -- 137,000 records, each with 1536-dimensional embeddings from OpenAI's text-embedding-3-small model, all running on Supabase's managed Postgres with pgvector. The marketing copy makes it sound easy. It wasn't. But the results were genuinely impressive once we got past the sharp edges, and I want to share the actual numbers and implementation details because most tutorials stop at 500 demo records and call it a day.

This isn't a "hello world" pgvector tutorial. If you need that, the Supabase docs are solid. This is about what happens when you push pgvector into production with real data, real users, and real latency budgets.

Why Semantic Search Over Full-Text Search

Our client runs a technical documentation platform. Users weren't finding what they needed with keyword search. Someone searching for "how to fix memory leak in Node" wouldn't find an article titled "Debugging Heap Allocation Issues in Server-Side JavaScript." Same concept, different words. Classic keyword search failure.

Semantic search fixes this by comparing the meaning of queries against the meaning of documents. You convert text into high-dimensional vectors (embeddings), store them, and find the closest matches using distance functions. The math is beautiful, but the engineering is where things get interesting.

We evaluated dedicated vector databases -- Pinecone, Weaviate, Qdrant -- but the client was already running Supabase for auth, storage, and their primary database. Adding another managed service meant more infrastructure, more billing, more latency from cross-service calls. pgvector let us keep everything in one place. For 137K records, that turned out to be the right call.

The Architecture

Here's what we built:

User Query → Next.js API Route → OpenAI Embedding API → Supabase RPC (pgvector similarity search) → Ranked Results → Client

The Next.js frontend is deployed on Vercel (we do a lot of Next.js development and this stack is one we trust). The embedding generation happens server-side in an API route. We call Supabase via their JS client using an RPC function that wraps the pgvector similarity query.

For the ingestion pipeline, we have a separate process:

Content Update → Webhook → Edge Function → Chunk Text → OpenAI Embedding API → Upsert to Supabase

Nothing exotic. The magic is in the details.

Setting Up pgvector in Supabase

Supabase ships with pgvector pre-installed on all projects since late 2023. You just need to enable the extension:

CREATE EXTENSION IF NOT EXISTS vector;

Here's our actual table schema (simplified slightly):

CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  chunk_index INTEGER NOT NULL,
  parent_document_id UUID REFERENCES documents(id),
  embedding vector(1536),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

A few notes on schema decisions:

  • vector(1536): This matches OpenAI's text-embedding-3-small output dimensions. If you're using text-embedding-3-large, you might want 3072 dimensions, but we found 1536 was the sweet spot for our accuracy-vs-performance tradeoff.
  • chunk_index and parent_document_id: Documents longer than ~500 tokens get chunked. We track chunks so we can reconstruct context in the UI.
  • metadata JSONB: Category tags, author info, date ranges. This becomes critical for filtered searches (more on that later).

The similarity search function:

CREATE OR REPLACE FUNCTION match_documents(
  query_embedding vector(1536),
  match_threshold FLOAT DEFAULT 0.78,
  match_count INT DEFAULT 10,
  filter_metadata JSONB DEFAULT '{}'
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  content TEXT,
  similarity FLOAT,
  metadata JSONB
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    d.id,
    d.title,
    d.content,
    1 - (d.embedding <=> query_embedding) AS similarity,
    d.metadata
  FROM documents d
  WHERE
    1 - (d.embedding <=> query_embedding) > match_threshold
    AND (
      filter_metadata = '{}' 
      OR d.metadata @> filter_metadata
    )
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

The <=> operator is cosine distance. We convert it to similarity (1 - distance) for the threshold check and return value. The @> operator does JSONB containment matching for filtered queries.

Embedding Pipeline Design

This is where most tutorials gloss over the hard parts. Generating embeddings for 137K records isn't just "call the API in a loop."

Chunking Strategy

We settled on ~400 token chunks with 50 token overlap. Why?

  • OpenAI's embedding models handle up to 8191 tokens, but embedding quality degrades on longer texts. The model has to compress more meaning into the same 1536 dimensions.
  • 400 tokens is roughly a solid paragraph. Enough context to be meaningful, small enough to be specific.
  • The 50-token overlap prevents edge cases where a key concept spans a chunk boundary.

We used LangChain's RecursiveCharacterTextSplitter for chunking, configured to respect paragraph and sentence boundaries:

import { RecursiveCharacterTextSplitter } from 'langchain/text_splitter';

const splitter = new RecursiveCharacterTextSplitter({
  chunkSize: 1600, // ~400 tokens
  chunkOverlap: 200, // ~50 tokens
  separators: ['\n\n', '\n', '. ', ' ', ''],
});

const chunks = await splitter.splitText(document.content);

Batch Embedding Generation

OpenAI's embedding API accepts batches of up to 2048 inputs. We process in batches of 500 with exponential backoff:

async function generateEmbeddings(
  texts: string[],
  batchSize = 500
): Promise<number[][]> {
  const embeddings: number[][] = [];

  for (let i = 0; i < texts.length; i += batchSize) {
    const batch = texts.slice(i, i + batchSize);

    const response = await openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: batch,
    });

    embeddings.push(
      ...response.data.map((d) => d.embedding)
    );

    // Rate limit courtesy
    if (i + batchSize < texts.length) {
      await sleep(200);
    }
  }

  return embeddings;
}

The initial embedding of all 137K records took about 45 minutes and cost roughly $2.80 with text-embedding-3-small at $0.020 per 1M tokens. Not bad.

Upsert Strategy

We batch insert into Supabase using their JS client. One thing that bit us: Supabase's default insert() has a payload size limit. We chunk our inserts into groups of 100 rows:

for (let i = 0; i < rows.length; i += 100) {
  const batch = rows.slice(i, i + 100);
  const { error } = await supabase
    .from('documents')
    .upsert(batch, { onConflict: 'id' });

  if (error) throw error;
}

Indexing Strategies That Actually Matter

This is the section that'll save you hours of debugging. Without an index, pgvector does a sequential scan -- it compares your query vector against every single row. At 137K records, that's actually... kind of fine? But "kind of fine" isn't production-ready.

IVFFlat vs HNSW

pgvector supports two index types:

Feature IVFFlat HNSW
Build time (137K records) ~2 minutes ~8 minutes
Query speed (p50) 18ms 11ms
Query speed (p95) 45ms 22ms
Recall @ 10 0.92 0.98
Index size on disk ~850 MB ~1.2 GB
Supports concurrent inserts No (requires reindex) Yes
Available since pgvector 0.4 pgvector 0.5

We went with HNSW. The recall difference matters -- at 0.92, IVFFlat was occasionally missing relevant results that users expected to see. HNSW's 0.98 recall meant virtually no misses. The trade-off is higher memory usage and slower index builds, but for our scale, that was fine.

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

The m and ef_construction parameters matter more than you'd think:

  • m = 16: Number of connections per node in the graph. Higher = better recall, more memory. 16 is a good default for our dimensionality.
  • ef_construction = 128: Controls index build quality. Higher = slower builds, better recall. We tested 64, 128, and 256. The jump from 64 to 128 improved recall noticeably; 128 to 256 was marginal.

At query time, you also want to set ef_search:

SET hnsw.ef_search = 100;

We set this in our RPC function. Default is 40, which gave us worse recall on edge cases. 100 added about 3ms to queries but brought recall from 0.95 to 0.98.

Query Performance: Real Numbers

Here's what everyone actually wants to know. These numbers are from our production Supabase instance (Pro plan, 4GB RAM, us-east-1) measured over a week of real traffic.

Raw Similarity Search (no filters)

Metric Without Index IVFFlat HNSW
p50 latency 82ms 18ms 11ms
p95 latency 145ms 45ms 22ms
p99 latency 210ms 78ms 38ms
Queries/sec sustained ~12 ~55 ~85

With JSONB Metadata Filter

Filtering by category (about 15K records in the target category):

Metric HNSW only HNSW + GIN on metadata
p50 latency 28ms 14ms
p95 latency 52ms 26ms

Adding a GIN index on the metadata column was a no-brainer:

CREATE INDEX idx_documents_metadata ON documents USING gin(metadata);

End-to-End Latency

From user keystroke to rendered results (including embedding the query via OpenAI):

Step Duration
Network to API route ~15ms
OpenAI embedding generation ~120ms
Supabase RPC call ~25ms
pgvector query execution ~14ms
Result serialization + response ~8ms
Network to client ~15ms
Total ~197ms

The OpenAI embedding call dominates. We considered self-hosting an embedding model to cut this down, but the operational overhead wasn't worth it for our use case. 200ms total is well within the "feels instant" threshold for search.

One Optimization That Mattered

We cache frequently-used query embeddings in a Redis layer. For our use case, about 30% of searches are repeated queries (people search for the same things). This drops the effective p50 end-to-end latency to ~65ms for cache hits by skipping the OpenAI call entirely.

Hybrid Search: Combining Semantic and Full-Text

Pure semantic search has a weakness: it can miss exact matches. If someone searches for an error code like "ERR_HTTP2_PROTOCOL_ERROR", semantic search might return vaguely related HTTP articles instead of the exact document about that specific error.

We implemented hybrid search using Supabase's built-in full-text search combined with pgvector:

CREATE OR REPLACE FUNCTION hybrid_search(
  query_text TEXT,
  query_embedding vector(1536),
  match_count INT DEFAULT 10,
  semantic_weight FLOAT DEFAULT 0.7,
  fulltext_weight FLOAT DEFAULT 0.3
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  content TEXT,
  combined_score FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH semantic AS (
    SELECT
      d.id,
      d.title,
      d.content,
      1 - (d.embedding <=> query_embedding) AS score
    FROM documents d
    ORDER BY d.embedding <=> query_embedding
    LIMIT match_count * 2
  ),
  fulltext AS (
    SELECT
      d.id,
      d.title,
      d.content,
      ts_rank(
        to_tsvector('english', d.content),
        plainto_tsquery('english', query_text)
      ) AS score
    FROM documents d
    WHERE
      to_tsvector('english', d.content) @@ plainto_tsquery('english', query_text)
    LIMIT match_count * 2
  )
  SELECT
    COALESCE(s.id, f.id) AS id,
    COALESCE(s.title, f.title) AS title,
    COALESCE(s.content, f.content) AS content,
    (COALESCE(s.score, 0) * semantic_weight +
     COALESCE(f.score, 0) * fulltext_weight) AS combined_score
  FROM semantic s
  FULL OUTER JOIN fulltext f ON s.id = f.id
  ORDER BY combined_score DESC
  LIMIT match_count;
END;
$$;

The 70/30 weighting between semantic and full-text came from testing. We ran 200 hand-labeled query-document pairs and tuned the weights to maximize NDCG@10. Your optimal weights will differ.

Don't forget the full-text search index:

CREATE INDEX idx_documents_fts ON documents
USING gin(to_tsvector('english', content));

Production Gotchas and Lessons Learned

Here's the stuff that isn't in the docs.

1. HNSW Index Build Locks the Table

Building the HNSW index on 137K records took about 8 minutes. During that time, inserts were blocked. We didn't realize this until we ran it on the production database and content updates started queuing. Use CREATE INDEX CONCURRENTLY in production:

CREATE INDEX CONCURRENTLY ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

This takes longer (about 12 minutes for us) but doesn't lock the table.

2. Supabase Connection Pooling and pgvector

If you're using Supabase's connection pooler (PgBouncer) in transaction mode -- which is the default for serverless -- you can't use SET hnsw.ef_search because SET commands don't persist across pooled connections. Instead, set it per-query:

SELECT set_config('hnsw.ef_search', '100', true);

Or set it as a Postgres parameter at the database level through the Supabase dashboard.

3. Embedding Drift Is Real

OpenAI updated their embedding model in early 2024. If you regenerate embeddings for new documents but don't re-embed existing ones, you'll get inconsistent similarity scores. We now store the model version in our metadata column and have a migration script to re-embed all records when we change models.

4. The 0.78 Similarity Threshold

We landed on 0.78 as our default threshold after analyzing user feedback on result relevance. Below 0.75, too much noise. Above 0.82, too many false negatives. This is specific to our data and text-embedding-3-small -- you'll need to tune this yourself.

5. Memory Pressure on Smaller Plans

The HNSW index for 137K × 1536-dimensional vectors uses about 1.2GB of memory when fully loaded. On Supabase's Pro plan (4GB RAM), this was fine. On the free tier or the Small compute add-on (1GB), you'll hit issues. Monitor pg_stat_activity and your RAM usage in the Supabase dashboard.

6. Row-Level Security (RLS) and Performance

Supabase encourages RLS for security. We found that RLS policies on our documents table added ~8ms to pgvector queries because Postgres has to evaluate the policy for each candidate row. For our search function, we use a service role key and handle authorization in the API layer instead. Not ideal from a security purist perspective, but the performance difference was meaningful.

Cost Breakdown

Real numbers for a month of production usage:

Item Monthly Cost
Supabase Pro plan $25
Supabase compute add-on (4GB) $50
OpenAI embeddings (queries) ~$4.50
OpenAI embeddings (new content) ~$0.80
Redis caching (Upstash) ~$5
Total ~$85.30

Compare this to Pinecone's Standard plan at $70/mo for a similar pod configuration, plus you'd still need your primary database. Keeping everything in Supabase saved us roughly $40-60/month and eliminated cross-service latency.

For what it's worth, we've built similar setups for clients where the data volume pushed past 500K records. At that point, you start needing to think about Supabase's larger compute tiers or potentially moving to a dedicated vector database. But for the sub-200K range, pgvector on Supabase is hard to beat on cost-effectiveness.

If you're working on a project that needs this kind of implementation, our team handles the full stack -- from headless CMS development to custom search infrastructure. Check out our pricing page or get in touch if you want to talk specifics.

FAQ

How many records can pgvector handle in Supabase before performance degrades?

From our testing and community reports, pgvector with HNSW indexes handles up to ~500K records comfortably on Supabase's Pro plan with 4-8GB compute. Beyond that, query latency starts climbing noticeably -- p95 can exceed 100ms at 1M+ records with 1536 dimensions. At that point, you should consider partitioning your data, reducing dimensions (OpenAI's text-embedding-3-small supports dimension reduction via the dimensions parameter), or evaluating a dedicated vector database.

Is pgvector accurate enough for production semantic search?

With HNSW indexes and proper tuning (ef_search = 100, m = 16), we measured 0.98 recall@10 against exact brute-force results on our 137K dataset. That means 98% of the time, the approximate nearest neighbor search returns the same top-10 results as an exact scan. For most applications, that's more than sufficient.

What embedding model should I use with pgvector and Supabase?

As of mid-2025, OpenAI's text-embedding-3-small (1536 dimensions) offers the best performance-per-dollar for most use cases. text-embedding-3-large (3072 dimensions) gives slightly better accuracy but doubles your storage and slows queries. Cohere's embed-v3 and Google's text-embedding-004 are solid alternatives if you're not locked into OpenAI. We've had good results with all three.

How do I update embeddings when content changes?

We use a webhook-triggered Supabase Edge Function. When a document is updated, the function re-chunks the content, generates new embeddings, and upserts the rows. The HNSW index updates automatically on insert/update -- no manual reindexing required. Budget about 200ms per document for the embedding API call.

Can I use pgvector with Supabase's Row-Level Security?

Yes, but expect a performance hit. In our testing, RLS added ~8ms per query because the security policies are evaluated per-row during the index scan. If search latency is critical, consider running search queries with a service role and handling authorization in your application layer.

How does pgvector compare to Pinecone for semantic search?

For under 200K records, pgvector on Supabase is competitive on query speed (sub-25ms p95 vs Pinecone's ~20ms) and significantly cheaper if you're already using Supabase. Pinecone pulls ahead at scale (1M+ records) and offers better out-of-the-box features like namespace management and automatic scaling. The biggest advantage of pgvector is keeping your vectors alongside your relational data -- no cross-service joins or sync logic.

What's the best chunking strategy for semantic search?

For documentation and knowledge base content, we've found 300-500 token chunks with 10-15% overlap to be optimal. Smaller chunks are more precise but lose context. Larger chunks capture more context but dilute the embedding. Always split on natural boundaries (paragraphs, then sentences) rather than at arbitrary token counts. Test with your actual data -- the "best" strategy depends heavily on your content type.

Do I need a dedicated vector database or is pgvector enough?

For most applications under 500K vectors, pgvector is enough. You should consider a dedicated solution (Pinecone, Weaviate, Qdrant) when you need: multi-tenancy with strong isolation, vectors exceeding 1M records, sub-5ms query latency requirements, or built-in vector-specific features like automatic reranking. For a typical SaaS product, startup, or content platform, pgvector on Supabase will serve you well and keep your architecture simple.