What Is pgvector? – Definition and How It Works

Learn what pgvector adds to Postgres, how its index types differ, and why one database for rows and vectors is often right.

pgvector is a Postgres extension adding a vector column type and similarity search. It lets one database hold your relational data and your embeddings.

1. The Basics

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          BIGSERIAL PRIMARY KEY,
  source_url  TEXT NOT NULL,
  heading     TEXT,
  content     TEXT NOT NULL,
  fetched_at  TIMESTAMPTZ NOT NULL,
  embedding   vector(1536)
);

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

Querying is ordinary SQL with a distance operator:

SELECT source_url, heading, content
FROM chunks
WHERE fetched_at > now() - interval '30 days'
ORDER BY embedding <=> $1
LIMIT 10;

2. The Distance Operators

The index opclass must match the operator you query with. An index built for L2 does not accelerate a cosine query — it is simply ignored, and the query silently falls back to a sequential scan. That mismatch is the most common pgvector performance bug.

3. Why This Beats a Separate Vector Database, Often

The decisive advantage is filtering with real SQL.

A standalone vector database offers metadata filters, but they are usually a restricted subset. In Postgres the filter is a WHERE clause — joins, date ranges, subqueries, permissions, anything SQL does:

SELECT c.content, s.domain
FROM chunks c
JOIN sources s ON s.id = c.source_id
WHERE s.tier = 'trusted'
  AND c.fetched_at > now() - interval '7 days'
  AND c.lang = 'en'
ORDER BY c.embedding <=> $1
LIMIT 10;

Plus: one system to operate, one backup, transactional consistency between rows and vectors, and no synchronisation job that can drift.

4. Index Types

HNSW — better recall and query speed, slower to build, more memory. The default choice.

IVFFlat — faster to build, lower memory, recall depends on lists and probes. Requires data present before building, since it trains on the distribution.

SET hnsw.ef_search = 100;          -- per-session recall/speed dial
SET ivfflat.probes  = 10;

Both are approximate. Without an index, pgvector does exact search — correct, and fine up to tens of thousands of rows.

5. Filtered Search Has a Catch

A restrictive WHERE clause combined with an ANN index can return fewer rows than requested, because the index finds candidates first and the filter then removes most of them.

Mitigations: raise ef_search, or accept a sequential scan when the filter is highly selective — which is often faster anyway, since a filter cutting the table to a few thousand rows makes exact search cheap.

Check the plan rather than assuming. EXPLAIN ANALYZE tells you whether the index was used.

6. Practical Limits

• Dimension cap on indexed vectors — very high-dimension embeddings may need reducing.
• Memory for index builds — raise maintenance_work_mem or builds crawl.
• Write amplification, since each insert updates the graph.
• Bloat from updates and deletes, needing the usual Postgres maintenance.

7. When to Use Something Else

Move to a dedicated vector database when you have tens of millions of vectors, need distributed sharding across nodes, or want built-in features Postgres lacks — managed reranking, multi-tenancy primitives, sparse-dense hybrid indexes.

Below that, one Postgres is usually the better engineering decision, and it is the one you can filter properly.

8. Summary

pgvector adds vector columns and similarity search to Postgres, with HNSW and IVFFlat indexes and cosine, L2, and inner-product operators.

Its real advantage is arbitrary SQL filtering and joins alongside similarity, plus one system to run and no sync to drift — provided the index opclass matches the operator you query with, and you check the plan on filtered queries where an ANN index can under-return.

Potential Related Glossary

• Data Structure
• Data Wrangling
• Unstructured Data
• Data Fusion