Add semantic search with pgvector

The Goal

Build semantic search without splitting your data model on day one.

The first version should answer one question: can users find better results with embeddings than with plain keyword search?

Store Chunks, Not Mystery Vectors

create extension if not exists vector;

create table document_chunks (
  id bigserial primary key,
  document_id bigint not null,
  account_id bigint not null,
  body text not null,
  embedding_model text not null,
  embedding vector(1536) not null,
  created_at timestamptz not null default now()
);

Keep the source text. Keep the source ID. Keep the account or permission boundary.

If the vector search result cannot explain what record it came from, the feature will be hard to debug.

Start With Exact Search

For a small dataset, exact search is easier to reason about.

select document_id, body
from document_chunks
where account_id = 42
order by embedding <=> $1
limit 8;

The account_id filter matters. Do not search every customer’s data and filter later in application code.

Add An Index When You Need It

When exact search gets slow, add an approximate index.

create index document_chunks_embedding_hnsw
  on document_chunks
  using hnsw (embedding vector_cosine_ops);

After adding the index, test recall. Do the right results still show up? If not, the index is fast but not useful.

Improve Quality Before Infrastructure

Most early search quality problems come from the content, not the database.

Check these first:

Are chunks too large?
Are chunks too small?
Is important context missing?
Are old embeddings still in the table?
Are permissions filtering out the result users expect?

Changing chunking often helps more than changing infrastructure.

Keep Model Versions

Store the model name or version with each embedding. If you change models, rebuild or separate the old vectors.

Mixed embeddings are a quiet source of bad search results.

When To Move On

Stay with pgvector while search is part of the product, not the whole product.

Move to dedicated search infrastructure when search needs its own team, heavy ranking work, large index operations, or scale controls that do not fit your Postgres setup.

References

pgvector