Vector Search in the Warehouse: When You Do Not Need a Vector Database

In 2023 the answer was easy: if you were doing retrieval, you stood up Pinecone or Weaviate, wrote a sync job from the warehouse into it, and accepted the operational tax. There was no serious alternative.

That answer expired. Snowflake ships a native VECTOR type with similarity functions, BigQuery has VECTOR_SEARCH with real indexes, and pgvector reached the point where HNSW indexes on a normal Postgres box handle millions of rows without drama. Meanwhile the sync pipelines I wrote in 2023 became the thing that broke most often.

I have now run retrieval three ways in production: a dedicated store with a sync job, pgvector next to the application database, and embeddings living in Snowflake beside the tables that generated them. The third one is the one I would build again for most workloads, and the deciding factor was never search quality. It was that deleting the sync pipeline deleted an entire class of incident.

This post is the threshold analysis: what each option actually costs, where the cliffs are, and the specific signals that mean you have outgrown the warehouse. The store-by-store feature comparison is in vector databases compared; this is the “do I need one at all” question.

The sync pipeline is the real cost

Nobody chooses a dedicated vector store and gets just a vector store. You get a second system of record for a derived artifact, and everything that follows from that.

Your source of truth is a table. The embedding is derived from a text column in that table. Put the vectors somewhere else and you now own: an incremental job that finds changed rows, a delete path so tombstoned records leave the index, a backfill procedure for the day you change embedding models, a reconciliation check because the counts will disagree, and a second set of credentials, network rules, and cost lines.

My sync job was 340 lines of Python and a Airflow DAG. It broke in three memorable ways. A soft-deleted account stayed retrievable for eleven days because the delete path only handled hard deletes. An embedding model upgrade left 400,000 old-dimension vectors mixed with new ones and similarity scores went quietly nonsensical. And a backfill re-embedded 2 million chunks at full price because the idempotency key included a timestamp.

None of those are exotic. They are the standard failure modes of any derived dataset that lives outside the system that produces it, and you already know them from data contracts. Keeping the vector in the same table as its source text makes all of them structurally impossible: one row, one transaction, one lifecycle.

flowchart TD
  src["Source table"] --> emb["Embed in warehouse"]
  emb --> col["VECTOR column, same row"]
  col --> q["Query with SQL filter plus similarity"]
  src -.-> sync["Sync job"]
  sync -.-> ext["External index"]
  ext -.-> drift["Drift, deletes, backfills"]

Solid path is one lifecycle. Dotted path is a second system of record for a derived column.

Snowflake: embeddings next to the mart

Snowflake’s VECTOR(FLOAT, n) type plus VECTOR_COSINE_SIMILARITY does the job, and SNOWFLAKE.CORTEX.EMBED_TEXT_1024 means the embedding call never leaves the warehouse.

-- Build the retrieval mart and its embeddings in one statement.
create or replace table analytics.gold.doc_chunks as
select
    chunk_id,
    doc_id,
    tenant_id,
    published_at,
    chunk_text,
    snowflake.cortex.embed_text_1024('snowflake-arctic-embed-l-v2.0', chunk_text)
        as embedding,          -- VECTOR(FLOAT, 1024), inferred
    current_timestamp() as embedded_at
from analytics.silver.doc_chunks_clean
where chunk_text is not null;

-- Retrieval: metadata filter first, similarity second.
select
    chunk_id,
    doc_id,
    chunk_text,
    vector_cosine_similarity(
        embedding,
        snowflake.cortex.embed_text_1024('snowflake-arctic-embed-l-v2.0', ?)
    ) as score
from analytics.gold.doc_chunks
where tenant_id = ?                              -- hard isolation
  and published_at >= dateadd(year, -2, current_date)
qualify row_number() over (order by score desc) <= 8;

The honest caveat: Snowflake does not give you an ANN index in the way a dedicated store does. That query is a scan over the rows surviving the WHERE clause, with pruning doing the heavy lifting. Which means your clustering strategy is your index strategy — the same discipline as clustering keys and micro-partition pruning.

Cluster on the columns you filter by. With tenant_id clustering on a 12-million-row chunk table, a single-tenant query touches maybe 80,000 rows and returns on an XSMALL warehouse in around 900ms. Without clustering, the same query scanned the full table and took 7 seconds. That is the whole performance story: pruning, not vector math.

Cost is warehouse seconds. An XSMALL at roughly $2/hour, auto-suspend at 60 seconds, serving a few hundred retrievals a day of a few hundred milliseconds each, runs me about $40/month including the idle tail. A managed vector store with the same corpus quoted $70/month before I counted the engineer-hours on the sync job. The broader cost framing is in the Snowflake cost playbook; retrieval is just another workload to isolate and tag.

BigQuery: real indexes, real caveats

BigQuery is the one with an actual ANN index, and it changes the scaling story.

-- IVF index. TreeAH is the alternative for very large corpora.
create vector index chunk_idx
on `proj.rag.doc_chunks`(embedding)
storing (tenant_id, doc_id, published_at)
options (index_type = 'IVF', distance_type = 'COSINE');

select
    base.chunk_id,
    base.chunk_text,
    distance
from vector_search(
    table `proj.rag.doc_chunks`, 'embedding',
    (select embedding from `proj.rag.query_embedding` where qid = @qid),
    top_k => 8,
    distance_type => 'COSINE',
    options => '{"fraction_lists_to_search": 0.02}'
)
where base.tenant_id = @tenant;   -- post-filter: see the warning below

Two things will bite you. First, the index needs at least 5,000 rows before BigQuery builds it, and index refresh after large writes is asynchronous — freshly loaded rows are searched by brute force until the rebuild lands. For a nightly batch corpus that is fine. For a corpus updated every ten minutes it is a correctness-adjacent surprise.

Second, that WHERE clause runs after the top-k. Ask for 8 and filter by tenant and you may get zero. Use STORING columns and push the predicate into the VECTOR_SEARCH call itself, or over-fetch substantially — I use top_k => 200 and filter down when I cannot push the predicate. Getting this wrong is the number one bug I have seen in BigQuery RAG code, and it fails silently as “the bot does not know about our recent docs.”

pgvector: the one people underrate

If your application already runs on Postgres, this is very likely your answer, and the bar is higher than the discourse suggests.

create extension if not exists vector;

alter table doc_chunks add column embedding vector(1024);

-- HNSW: slower to build, much faster and more accurate to query
-- than IVFFlat. m and ef_construction are the build-time tradeoff.
create index on doc_chunks
using hnsw (embedding vector_cosine_ops)
with (m = 16, ef_construction = 64);

-- Partial index per hot tenant beats one giant index when the
-- filter is highly selective.
create index on doc_chunks
using hnsw (embedding vector_cosine_ops)
where tenant_id = 'acme';

set hnsw.ef_search = 100;   -- recall/latency dial, per session

select chunk_id, chunk_text, embedding <=> $1 as distance
from doc_chunks
where tenant_id = $2
order by embedding <=> $1
limit 8;

Numbers from a live system: 3.1 million chunks at 1024 dimensions on a single 16-vCPU, 64 GB managed Postgres instance. Index build took 22 minutes. p50 query latency 11ms, p95 38ms, recall@8 around 0.96 at ef_search = 100. Instance cost about $380/month, and it was already running the application database.

The constraint to plan around is memory. HNSW wants the index resident; 3 million 1024-dimension vectors is roughly 12 GB of raw float data plus graph overhead. Once the index exceeds RAM, p99 falls off a cliff in a way that looks like a random incident. Halve your dimensions with Matryoshka-style truncation before you double your instance size — at 512 dimensions I lost about 1.5 points of recall and half the memory.

The thresholds I actually use

Signal	Warehouse or pgvector	Dedicated store
Corpus size	Under ~5M vectors	Tens of millions and growing
Freshness	Batch, minutes to hours	Sub-second write-to-searchable
Latency budget	100ms to 2s acceptable	Sub-20ms p99 on user path
Filtering	Complex, multi-column, joins	Simple tag equality
QPS	Under ~50 sustained	Hundreds sustained
Team	Data engineers, SQL-first	Dedicated platform owners

The two rows that decide it in practice are freshness and latency. If your content is rebuilt nightly and a human is waiting on an LLM response anyway — which adds 1 to 3 seconds regardless — the 300ms you save with a specialized index is invisible. If you are doing real-time personalization inside a page render, it is everything.

Corpus size is the row people over-weight. Five million chunks is a lot of documentation. Most internal RAG systems I have measured sit between 100,000 and 2 million chunks and will never leave that range.

Hybrid search is where SQL wins

Pure vector search is worse than teams expect at exact-match terms: error codes, SKUs, invoice numbers, person names. The fix is hybrid — combine semantic similarity with lexical matching — and doing that in SQL is genuinely pleasant compared to gluing two systems together.

with semantic as (
    select chunk_id,
           row_number() over (order by embedding <=> $1) as rank
    from doc_chunks
    where tenant_id = $3
    order by embedding <=> $1
    limit 60
),
lexical as (
    select chunk_id,
           row_number() over (
               order by ts_rank_cd(search_tsv, plainto_tsquery($2)) desc
           ) as rank
    from doc_chunks
    where tenant_id = $3
      and search_tsv @@ plainto_tsquery($2)
    limit 60
)
-- Reciprocal rank fusion. k = 60 is the standard constant and I have
-- never found a better value by tuning it.
select coalesce(s.chunk_id, l.chunk_id) as chunk_id,
       coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + l.rank), 0) as score
from semantic s
full outer join lexical l on s.chunk_id = l.chunk_id
order by score desc
limit 8;

That single query replaced a service that called two systems and merged results in application code. Hybrid retrieval lifted answer accuracy on my golden set by 9 points, mostly on questions containing product identifiers. The retrieval quality fundamentals — chunking, reranking, evaluation — are in RAG pipelines for data engineers.

Where teams get this wrong

Benchmarking on QPS they will never see. Vendor charts compare 10,000 QPS. Your internal assistant peaks at 4 QPS at 9am. Choose for your actual load, then re-measure in a year.

Filtering after the top-k. BigQuery’s post-filter and the equivalent pattern in every store returns fewer rows than requested, or none. Push the predicate down, use partial indexes, or over-fetch by 20x.

Ignoring the index memory ceiling. pgvector is excellent right up until the HNSW graph does not fit in RAM, at which point latency becomes bimodal and you will spend a day blaming the network.

Re-embedding everything on every run. Key the embedding job on a content hash, not on a batch timestamp. My 2-million-chunk accidental backfill cost $180 and taught me this permanently.

Storing the vector away from the text. If the chunk text lives in one system and the embedding in another, you will eventually retrieve an ID whose text has changed. Same row, same transaction, always.

Forgetting tenant isolation is a security boundary. A similarity search with a missing WHERE tenant_id returns other customers’ data and looks like a relevance bug. Enforce it in a view or row-level security, not in application code.

FAQ

Is warehouse vector search actually slower?

Yes, by roughly an order of magnitude in isolation — hundreds of milliseconds versus tens. It is almost always irrelevant, because the LLM generation step that follows takes 1 to 3 seconds. Measure the end-to-end user-perceived latency before you optimize the retrieval leg.

What about Iceberg tables and open formats?

Vector columns in open table formats are still immature; Snowflake’s VECTOR type does not round-trip cleanly to Iceberg today. If open formats are a hard requirement, store embeddings as an array of floats and accept that you are doing brute-force similarity in compute. That works fine up to a few hundred thousand rows.

How do I handle an embedding model upgrade?

Add a new column rather than mutating the old one, backfill it, evaluate both against your golden set, then cut over reads and drop the old column. Doing this in one table with two columns is a twenty-minute change. Doing it across a sync boundary is a project.

Does DuckDB work for this?

For local development and for corpora under a few hundred thousand chunks, surprisingly well — the vss extension gives you HNSW and the whole thing is a file. I use it for prototyping the retrieval mart before it goes to the warehouse; see DuckDB for local pipelines.

When would you still reach for a dedicated store?

Sub-20ms p99 on a page render, write-to-searchable in under a second, or a corpus past a few tens of millions of vectors with sustained triple-digit QPS. Also when the team owning retrieval is not the team owning the warehouse, because organizational boundaries are real architecture constraints.

Do I need a reranker?

More often than you need a different vector store. A cross-encoder rerank over the top 50 candidates improved my answer quality more than any index change I have made, and it is orthogonal to where the vectors live.

What this means for your pipelines

The interesting shift is not that warehouses added a vector type. It is that retrieval stopped being a separate system and became a column. Once the embedding is a column, everything you already do to data applies: it is tested, it has lineage, it is covered by your access model, it gets backfilled by the same tooling, and it appears in the same cost report.

That reframing changes what you should build first. Do not start by choosing a store. Start by building the curated, prose-shaped mart that is worth embedding — one row per meaningful unit, denormalized, written for a reader. That table is the asset. Where its vectors live is a deployment detail you can change in an afternoon, and the pattern for building it is the same one in RAG over warehouse data.

My default in 2026: if the vectors already have a home in a database you operate, keep them there until a measured latency or scale number forces a move. The dedicated store is a real answer to a real problem, but it is a problem most teams have not got yet, and the sync pipeline you write to reach it is a problem you get immediately.