pgvector: Storing and Searching AI Embeddings in PostgreSQL
Your app called an embeddings API and got back 1,536 numbers per document. Before you add a vector database to the stack: what pgvector stores, how its two indexes trade recall for speed, and when Postgres alone carries a semantic search feature.
Semantic search made it onto the roadmap, you called an embeddings API, and every document in your product now comes with an array of 1,536 floating-point numbers. The numbers are the easy part. The question is where to put them, and the reflex answer — spin up a dedicated vector database — adds a second stateful system to your stack before anyone has checked whether the database you already run can do the job.
Usually it can. pgvector is the PostgreSQL extension that teaches the database to store embeddings and search them by similarity, and for most applications it turns "we need a vector database" into "we need one more column". This piece covers what an embedding looks like from the database's side, what pgvector actually adds, the exact-versus-approximate trade behind its two index types, and the honest version of when a dedicated vector engine earns its place.
What an embedding is, from the database's point of view
An embedding is what you get when a model reads a piece of content — a paragraph, a support ticket, a product description — and encodes its meaning as a list of numbers. Each list is a point in a space with hundreds or thousands of dimensions, and the model's one useful promise is that similar meanings land near each other. "How do I reset my password" and "can't log into my account" become points that sit close together, even though they share almost no words.
For the database, that changes the shape of the question. Classic search asks "which rows contain this word?", a job for text indexes and `WHERE` clauses. Semantic search asks "which rows are nearest to this point?", which is a nearest neighbor query. Every feature in this family reduces to that one operation: semantic search ranks documents by distance to the query's embedding, recommendations rank items by distance to things the user liked, and retrieval-augmented generation (RAG) fetches the nearest chunks of your content and hands them to a model as context. Store the points, measure distances quickly — that's the entire requirement.
What pgvector actually adds
pgvector is an open-source extension that adds a `vector` column type to PostgreSQL, distance operators to compare vectors, and index types to make those comparisons fast at scale. Enabling it is one statement, and after that a vector column behaves like any other column: it lives in an ordinary table, next to ordinary types.
CREATE EXTENSION vector;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
embedding vector(1536)
);
-- The ten most similar documents, by cosine distance
SELECT id, title
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;The `<=>` in that query is cosine distance, the measure most embedding providers recommend. pgvector also ships `<->` for Euclidean (L2) distance and `<#>` for negative inner product (negated so that closer still sorts first), and recent releases keep adding more: as of writing, version 0.7.0 brought an L1 operator plus compact half-precision and sparse vector types. For normalized embeddings, which is what most current models return, the different measures produce the same ranking anyway, so the practical advice is short: use `<=>` unless your model's documentation says otherwise.
That's most of the contract. One thing pgvector deliberately doesn't do: it never calls an embeddings API for you. Your application computes the query's embedding, passes it as a parameter, and gets rows back ordered by similarity, with all of SQL wrapped around the result. The step-by-step wiring for a specific framework belongs in the docs; the point here is that nothing about the shape is exotic.
Exact search first: you may not need an index
The query above runs on a plain table with no index at all. Postgres computes the distance to every row and sorts — a sequential scan, exact and complete. Recall is 100% by definition: the ten nearest rows it returns are truly the ten nearest rows.
What's under-appreciated is how far that carries. Scanning tens of thousands of vectors takes milliseconds, and a few hundred thousand often still lands within an interactive budget on reasonable hardware. If your corpus is a documentation site, a product catalogue, or one tenant's data at a time, you may live in exact-search territory for a long while, with nothing to tune and nothing to rebuild. The right time to add an index is when latency tells you to, not when the architecture diagram would look more serious with one.
The order of operations
Start with the bare column and exact search, and measure. Add an index when a real query on real data is too slow. An approximate index spends recall to buy speed, and that's a trade worth making deliberately rather than by default.
HNSW vs IVFFlat: the approximate index trade
Past the point where scanning everything is too slow, you add an approximate nearest neighbor (ANN) index, and the word approximate is doing real work. An ANN index doesn't check every row; it organizes the vectors so a search can visit a promising fraction of them and skip the rest. That's what makes million-row similarity queries fast. It's also why recall drops below 100%: occasionally the true nearest neighbor sits in a region the search didn't visit. Every vector database makes the same trade. It can be tuned, but it can't be avoided.
pgvector ships two index types, and they organize the space differently.
| HNSW | IVFFlat | |
|---|---|---|
| How it works | A multi-layer graph of neighbors, walked from an entry point toward the query | Vectors clustered into lists; the search scans only the closest clusters |
| Build cost | Slower to build, more memory | Faster to build, lighter |
| Query speed vs recall | The better curve, as of writing | Good, but usually behind HNSW at equal recall |
| Empty table? | Fine — the graph grows with inserts | Needs data first: it clusters what's there at build time |
| Tuning knobs | `m`, `ef_construction` at build; `hnsw.ef_search` per query | `lists` at build; `ivfflat.probes` per query |
| Choose it when | Query speed and recall matter most — the common case | Build time or memory is the constraint, and data loads once |
-- HNSW, on cosine distance (matches the <=> operator)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- IVFFlat: build it after the table has data in it
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);If in doubt, HNSW — added in pgvector 0.5.0 — costs more up front and holds the better speed-to-recall curve at query time. IVFFlat keeps a niche where the index has to build fast or fit in less memory, with one operational caveat: because it derives its clusters from whatever data exists at build time, an index created on a nearly empty table clusters badly. Bulk-load first, then build, and rebuild after the data changes shape.
One version-sensitive limit to check before you commit to an embedding model. As of writing, the `vector` type stores up to 16,000 dimensions, but the indexes support up to 2,000 (4,000 for the half-precision `halfvec` type). A 1,536-dimension embedding — the size of OpenAI's text-embedding-3-small, as of writing — indexes without workarounds. A 3,072-dimension one needs half-precision, a model option that shortens the output, or exact search with no index. These numbers move between releases, so check the pgvector README against your model of choice.
The real advantage: vectors next to your data
Benchmarks get the attention, but the strongest argument for pgvector isn't about milliseconds. It's that the embeddings live in the same database as the rows they describe, and a separate system has to reimplement everything that follows from that.
- Filters are just SQL. "Nearest documents this user may see, in this workspace, published this year" is a `WHERE` clause and a `JOIN` bolted onto the similarity query. A dedicated engine needs its own filter syntax, fed with metadata you copied over and must keep current.
- Updates are transactions. Edit a document and its embedding in one transaction, and search never sees a row whose vector describes last week's text. Across two systems, that consistency becomes your application's problem.
- There's no sync pipeline. A second store means change capture, retries, backfills, and reconciliation — plumbing whose failure mode is stale search results nobody notices for a week.
- One operational envelope. Backups, recovery, monitoring, and residency are already answered for your database. A second stateful system answers them all again.
That last point compounds quietly. Embeddings are derived data, but they're often derived from the most sensitive content you hold, so where they're stored and how they're backed up isn't a footnote. Inside Postgres they're covered by the same point-in-time recovery that protects everything else, and they inherit whatever data-residency answer you've already given — the question where to store EU user data unpacks. A separate vector store is a second copy of that homework.
When a dedicated vector database wins
There is an honest boundary. Pinecone, Qdrant, Weaviate, Milvus and their peers are good systems built around exactly one workload, and there are cases where they earn the second system:
- Scale. As a rough order of magnitude as of writing: single-node Postgres with pgvector is comfortable through millions of vectors. When a collection heads toward hundreds of millions, engines with sharding and quantization designed in from the start justify themselves.
- Vector search is the product. If high-QPS similarity queries are the core workload rather than one feature among many, a specialized engine's tuning surface and horizontal scaling become worth operating.
- Specialized features, natively. Built-in hybrid search pipelines, multi-vector queries, aggressive quantization options. Some of this exists as Postgres extensions; if you need all of it, you're describing a dedicated engine.
Notice what's not on the list: adding semantic search to an existing product. That's the common case, and it's the one where reaching for a second database first is premature. The migration path is forgiving, too. If you start in Postgres and genuinely outgrow it, your embeddings are rows in a table, and exporting them to a specialized engine is a batch job rather than an archaeology project. Starting with two systems on day one pays the operational cost immediately, in exchange for a scaling ceiling you may never touch.
| Your situation | A reasonable default |
|---|---|
| Adding semantic search or RAG to an app whose data is in Postgres | pgvector, exact search, no index yet |
| Up to millions of vectors, latency starting to matter | pgvector with an HNSW index |
| Hundreds of millions of vectors, or search is the core product | A dedicated vector database |
| Strict recall and latency targets at high QPS | Benchmark both on your data before deciding |
How Runsite handles it
On managed PostgreSQL, pgvector is one of the pre-installed extensions, alongside PostGIS and pg_trgm, so enabling it is the same `CREATE EXTENSION vector;` from earlier — no support ticket, no custom image. The database is PostgreSQL 16 by default, and the extension is available from the free 1 GB tier up, which is enough to prototype a semantic search feature against real data before paying anything.
The operational layer a second database would have doubled stays single. Daily backups and continuous WAL archiving cover vector columns like any others, similarity queries ride the built-in PgBouncer pooled endpoint as ordinary app traffic, and the whole envelope — data, embeddings, WAL archive, backups — stays in the Frankfurt, Germany region with a signed GDPR DPA on every plan. Running the database itself is the part managed PostgreSQL takes off your plate; pgvector means the vector workload comes along without adding a new system to run.
The short version
An embedding is a point in space, and every AI-adjacent search feature reduces to finding the nearest points. pgvector teaches the database you already run to answer that query: exactly and index-free while the data is small, fast and approximate through an HNSW index once it grows. Keeping vectors next to the rows they describe buys you SQL filters, atomic updates, and one backup-and-residency story instead of two. A dedicated vector database is real infrastructure for real cases — hundreds of millions of vectors, search as the core product — and the way to find out whether you're such a case is to outgrow Postgres, not to assume in advance that you will.
If your application's data already lives in Postgres, the shortest path to vector search is a database with pgvector waiting behind a single `CREATE EXTENSION`. Spin up managed PostgreSQL on the free tier and the first similarity query is an afternoon away.