Do you need a vector database, or is Postgres with pgvector enough?

First question: do you need vector search at all?

Choose storage from the shape of the workload, not from what is fashionable. The question to ask is whether the system needs approximate similarity search, finding records close in meaning to a query, rather than exact lookup by key, field or keyword.

Often it does not. In the book's worked product the knowledge a language model draws on is small and structured: disposal rules keyed by label, region settings and short notes on special cases. Ordinary queries retrieve it, so version one has no vector database; adding one would mean generating embeddings, maintaining an index and running another store, all complexity without a clear benefit. The LLM reliability guide shows that retrieve-then-generate pattern.

Exact search costs time in proportion to the number of vectors, so doubling the collection doubles the work per query. That is fine for a small collection queried now and then, but repeated in real time it adds up: in one of the author's production deployments, on-device search over more than two thousand embeddings, run every second, drained the battery until the brute-force scan was replaced by an HNSW index. Climb only as far as the workload needs:

1. No vectors: structured queries and full-text search, when the data is structured or users search by keyword.
2. Exact search without an index: a brute-force scan, in memory or in Postgres, which is accurate and fine for small collections.
3. pgvector with an approximate index, when the collection or the query rate makes a full scan too slow.
4. A dedicated vector database, when one Postgres server can no longer meet your scale, latency or isolation needs.

Why pgvector is the right default for most teams

pgvector is an open-source PostgreSQL extension that adds a vector column type, distance operators (Euclidean, inner product and cosine, among others) and two approximate indexes, HNSW and IVFFlat. Most managed Postgres services offer it, so adopting it is often a single CREATE EXTENSION statement.

Its strength is everything around the vectors. Embeddings sit in the same rows, or the same transaction, as the data they describe, so a filter on tenant, permissions or date is a WHERE clause, a join to the source table is a join, and deleting a record deletes its embedding. Backups, replication, access control and monitoring are the ones you already run, and in the reference architecture retrieval becomes one more read path on a database you already operate.

The costs are real too. A vector index competes with your transactional workload for memory and CPU, an HNSW index performs best when it fits in memory, and a single Postgres primary scales up rather than out. Read replicas can spread query load, but every replica holds the whole index, so very large collections or very high query rates eventually need more than one server can give.

The decision table

Read across each row. If most of your answers sit in the middle column, stay on Postgres; if several sit in the right column and tuning has not fixed them, plan a move.

HNSW or IVFFlat, and the settings that matter

Both are approximate nearest-neighbour indexes: they skip most of the collection to answer quickly, trading a little recall for a lot of speed. Exact search, with no index, remains the right choice for small collections or when every answer must be perfect.

HNSW builds a graph in which each vector links to a few neighbours, and a search walks from a coarse layer down to finer ones. It gives fast queries with high recall at the cost of extra memory and slower builds, it can be created on an empty table, and it is the usual choice for real-time semantic retrieval. In pgvector its settings are m and ef_construction at build time and hnsw.ef_search at query time; raise ef_search for better recall at some cost in speed.

IVFFlat clusters the vectors into lists and searches only the lists nearest the query. It builds faster and uses less memory, which helps at large scale or under memory pressure, but it must be built after representative data is loaded, because the clusters are learned from that data. Its settings are lists at build time and ivfflat.probes at query time; pgvector's documentation suggests starting with lists at the row count divided by 1,000 (for up to a million rows) and probes at the square root of lists.

Two habits apply to both. If you index a compressed form of the vectors, such as half precision or binary, retrieve a generous top k from the index and re-rank those candidates with the full vectors. And watch filtered queries: the index finds nearest neighbours first and the WHERE clause removes rows afterwards, so a selective filter can return fewer results than you asked for. Raise ef_search, use the iterative index scans in recent pgvector versions, or add partial indexes or partitions for common filters such as tenant.

Keeping embeddings in sync with the source data

An embedding is derived data, like a cache: it should serve the source of truth and never become a second, conflicting version of it. This is where pgvector is simplest, because the source row and its embedding can share a transaction and a backup. Whichever store you use, three rules keep it honest.

• Store the embedding model's name and version with every vector. Vectors from different models are not comparable, so a model change means re-embedding everything and switching over in one step.
• Re-embed on change, asynchronously. Write the source change and an outbox row in one transaction, and let a worker create the new embedding. In pgvector the worker updates the same row; for an external store it upserts by source ID, which makes retries safe.
• Propagate deletes explicitly, and run a periodic reconciliation that compares source IDs and versions with the index, because pipelines do miss events.

When and how to migrate

Move when your own measurements say so, not when a benchmark post does. Load a realistic copy of your embeddings, build the index, run your real query mix with its filters at the concurrency you expect, and record recall against exact search, p95 latency and throughput. It is time to move if the index no longer fits in memory on the largest server you are willing to run, if vector queries slow the transactional workload, or if you cannot meet your recall and latency targets after tuning.

The first step is to separate the load, and that may not need a new product: a read replica or a separate Postgres instance for retrieval is often enough. If you do adopt a dedicated engine, put retrieval behind one interface in the backend, backfill the new store from the source of truth, and run it in shadow beside the old one, comparing results on live queries before you switch; shadow, canary and A/B deployment describes the method. Keep Postgres as the source of truth either way. The vector store is an index you can rebuild.

Questions