Vector database guide: how they work and when Postgres is enough

A vector database stores embeddings (lists of numbers that capture meaning) and finds the ones most similar to a query, which is the core of semantic search and RAG. This guide explains how vector search works, compares pgvector with dedicated options like Pinecone and Qdrant, and shows when Postgres is enough.

What is a vector database?

A vector database is a database built to store embeddings and quickly find the ones closest to a query embedding. An embedding is a list of numbers (often hundreds or thousands long) that an AI model produces from text, images or code, arranged so that similar meanings end up close together.

That makes search by meaning possible. A query for "how do I get my money back" finds the document titled "Refund policy" even though they share no words. This nearest-neighbor search is the retrieval step in most retrieval augmented generation systems, semantic search boxes, recommendation features and duplicate detection.

The term covers two kinds of products:

• Dedicated vector databases such as Pinecone, Qdrant, Weaviate, Milvus and Chroma, built around vector search.
• Vector search inside an existing database, most commonly Postgres with the pgvector extension, but also Elasticsearch, OpenSearch, MongoDB and others.

If embeddings are new to you, start with what embeddings are; this guide assumes you know roughly what they do.

How does vector search work?

Vector search turns the query into an embedding with the same model used for the documents, then returns the stored vectors with the smallest distance to it. The two choices that matter are the distance metric and the index.

Distance metrics. Most text embedding models are designed for cosine distance. pgvector supports these operators (pgvector):

Indexes. Comparing a query with every stored vector (exact search) is accurate but slows down linearly as data grows. Approximate nearest neighbor (ANN) indexes trade a little accuracy for a lot of speed:

• HNSW builds a layered graph of neighbors and walks it toward the query. It gives the best speed and recall trade-off for most workloads, at the cost of memory and slower builds.
• IVFFlat groups vectors into lists around centroids and searches only the closest lists. It builds faster and uses less memory, with lower recall at the same speed.

The accuracy you give up is called recall: the share of the true nearest neighbors the index actually returns. An index with 95% recall misses one in twenty of the best matches, which matters more for some apps than others.

pgvector vs dedicated vector databases: which should you use?

If you already run Postgres, start with pgvector; move to a dedicated vector database when scale, filtering or operations demand it. The table compares the main options at the time of writing (October 2026).

The strongest argument for pgvector is that your vectors live next to the data they describe. You can filter by tenant_id, join to documents, delete a user's data in one transaction and back everything up together. The strongest argument for a dedicated database is that vector search is its only job, so filtering, scaling and tuning are built in.

How do you set up vector search in Postgres with pgvector?

Setting up pgvector takes four steps: enable the extension, add a vector column, store embeddings and create an index. The SQL below assumes 1,536-dimension embeddings and cosine distance.

1. Enable the extension once per database:

   CREATE EXTENSION IF NOT EXISTS vector;

2. Create a table with a vector column sized to your embedding model:

   CREATE TABLE chunks (  id          bigserial PRIMARY KEY,  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,  tenant_id   bigint NOT NULL,  content     text   NOT NULL,  model       text   NOT NULL,  embedding   vector(1536) NOT NULL);

3. Insert embeddings produced by your embedding model in application code, storing the model name with each row so you can re-embed later.

4. Create an HNSW index with the operator class that matches your distance:

   CREATE INDEX chunks_embedding_hnsw  ON chunks USING hnsw (embedding vector_cosine_ops)  WITH (m = 16, ef_construction = 64);

Then query the nearest chunks:

SET hnsw.ef_search = 100;SELECT id, content, embedding <=> $1 AS distanceFROM chunksWHERE tenant_id = $2ORDER BY embedding <=> $1LIMIT 10;

m = 16 and ef_construction = 64 are pgvector's defaults. hnsw.ef_search (default 40) controls how many candidates the search considers: raise it for better recall, lower it for speed.

How do you choose an index and tune recall?

Choose HNSW by default, and IVFFlat when build time or memory is the constraint. Then tune one query-time setting while measuring recall on real queries.

Why filtered queries return too few rows

Approximate indexes in pgvector apply WHERE filters after the index scan. With hnsw.ef_search at 40 and a filter that matches 10% of rows, you get about 4 matching rows on average, even if you asked for 10. pgvector's README suggests these fixes:

• Iterative index scans (pgvector 0.8.0 and later): SET hnsw.iterative_scan = relaxed_order; keeps scanning until enough rows match.
• Partial indexes when a filter has a few distinct values, for example one index per document type.
• Partitioning when a filter has many distinct values, such as per-tenant partitions.
• A regular B-tree index on the filter column when the filter matches a small share of rows; Postgres can then do an exact search over the few matching rows.

This filtered-search behavior is the most common reason teams outgrow plain pgvector, and the area where dedicated databases like Qdrant put the most work.

When is Postgres enough, and when do you need a dedicated vector database?

Postgres with pgvector is enough for most products: up to a few million vectors, moderate query volume and filters that pgvector's techniques can handle. Consider a dedicated vector database when one of these is true:

• You have tens or hundreds of millions of vectors and the HNSW index no longer fits comfortably in memory.
• Most queries combine vector search with selective, varied filters and you can't fix recall with partial indexes or partitioning.
• Vector search traffic is heavy enough to slow down your transactional workload.
• You want vector search fully managed and separate from your application database.

A quick sizing estimate helps. A 1,536-dimension vector stores 4 bytes per dimension, about 6 KB per row before index overhead, so one million chunks is roughly 6 GB of raw vectors. The same data as halfvec is about 3 GB. The HNSW index adds more on top, and it performs best when it fits in RAM.

For many teams the deciding factor is simpler: if your data is in Postgres already, adding a second database means syncing, a second backup strategy and a second place for user data deletion requests. Choose that complexity only when you need it.

How much does a vector database cost?

pgvector costs nothing beyond the Postgres you already pay for, though you may need more memory. Managed vector databases charge for storage and for reads and writes. At the time of writing:

Sources: Pinecone pricing and Qdrant pricing.

Don't forget embedding costs. Every document is embedded once (and again whenever you change models), and every query is embedded at search time. For most apps that cost is small compared with the LLM calls that use the retrieved context, but re-embedding a large corpus is a real line item.

What mistakes do teams make with vector databases?

Most vector search problems come from retrieval design, not the database. These are the ones that show up most often:

• No evaluation set. Write 30 to 50 real questions with the chunks that should answer them, and measure how often those chunks appear in the top results. Without it you can't tell whether a change helped.
• Pure vector search for exact terms. Embeddings are weak at part numbers, error codes and names. Combine vector search with keyword search (Postgres full-text search or BM25) and merge the results.
• Bad chunking. Chunks that split a table or a procedure in half retrieve badly whatever the database. Chunk on headings and paragraphs, with some overlap.
• Mixing embedding models. Vectors from different models are not comparable. Store the model name and re-embed everything when you switch.
• Ignoring tenancy and deletion. Put tenant_id filters in every query and make sure deleting a document deletes its chunks.

If you are deciding whether you need retrieval at all, see fine-tuning vs RAG. If the goal is a support or website assistant, our guide to adding an AI chatbot to your website covers grounding on your content end to end.

How to pick a vector database in 30 minutes

Answer these questions in order and stop at the first clear answer.

1. Is your data already in Postgres? Start with pgvector and an HNSW index.
2. Fewer than a few million vectors? pgvector, with halfvec if memory is tight.
3. Heavy, selective filters on most queries? Try iterative scans and partial indexes first; if recall is still poor, evaluate Qdrant or another dedicated engine.
4. No database team and you want zero operations? A managed service such as Pinecone or Qdrant Cloud.
5. Prototype on a laptop? Chroma or pgvector in a local Postgres container.

Whatever you choose, build the evaluation set first. A coding agent can write the ingestion pipeline, the pgvector schema and the evaluation script in an afternoon; if you want it to keep working while you are offline, a hosted box such as Orkio with its Data and reports kit is one way to run that unattended.

Frequently asked questions