pgvector: Postgres for AI

This cover frames the whole post around a single reframing: you probably do not need a separate, specialized vector database to ship semantic search and retrieval-augmented generation. The Postgres instance already humming under your application can do the job, because of an extension called pgvector.

The goal of this first post is orientation, not depth. Before we argue why this matters or dissect how the index math works, you need a clear picture of what pgvector actually is and where it fits.

pgvector is distributed as a standard Postgres extension, which means it is loaded into an existing database rather than run as its own service. The moment you enable it, Postgres learns a new column type called `vector` and a family of operators for comparing vectors.

The practical consequence is that an embedding becomes just another column on a table, sitting right next to the text, the foreign keys, and the timestamps it belongs with. There is no new server to deploy, no new protocol to learn, and no second copy of your data to keep in sync.

An embedding is the bridge between unstructured content and math. A model reads a chunk of text (or an image, or audio) and emits a fixed-length list of floating-point numbers — for example 1536 of them for OpenAI's text-embedding-3-small. Crucially, content with similar meaning produces vectors that sit close together in that high-dimensional space.

pgvector does not create these numbers and has no idea what they mean. Its entire responsibility is to store those arrays efficiently and to measure how far apart any two of them are. Understanding this division of labor early prevents a lot of confusion later.

This snippet is the entire on-ramp. `CREATE EXTENSION IF NOT EXISTS vector;` is run once per database and is idempotent, so it is safe to include in migrations. After that, the `vector(1536)` column type is available like any built-in type.

The dimension you declare — 1536 here — must match the output size of whatever embedding model you use. Declaring it wrong is a setup error you want to catch immediately, which is why pinning the dimension in the schema is good practice rather than leaving it loose.

Distance is the core operation, and pgvector exposes it through three infix operators so it reads naturally in SQL. `<->` computes L2 (Euclidean) distance, `<=>` computes cosine distance, and `<#>` computes the negative inner product. Each corresponds to a similarity notion, and each pairs with a specific index operator class later on.

Which one you pick is dictated by how your embedding model was trained, not by preference. For most modern text embeddings, cosine distance via `<=>` is the safe default, which is why you will see it throughout the rest of this series.

Here is the query that does the actual work: take a query vector, order every candidate row by its distance to that vector, and keep the closest few. This single pattern — `ORDER BY embedding <=> $query LIMIT k` — is the heart of semantic search in pgvector.

Notice there is nothing exotic about it. It is ordinary SQL, which means it composes with WHERE clauses, JOINs, and everything else the language offers. That composability is exactly what makes pgvector powerful, and it is the thread we pull on in the next post.

This pipeline shows where pgvector lives inside a retrieval-augmented generation system. Documents are chunked into passages, each passage is embedded by a model, the vectors are stored in a pgvector column, a user query is embedded and matched against them, and the top results are fed to an LLM as grounding context.

pgvector owns only the Store and Search stages. The chunking strategy, the choice of embedding model, and the final LLM prompt all live outside it. Seeing the whole pipeline makes it obvious why pgvector is a component, not a complete solution.

The quiet superpower of pgvector is inheritance. Because your vectors are rows in a real Postgres database, they automatically gain everything Postgres already provides: ACID transactions, foreign keys, joins, row-level security, point-in-time backups, and streaming replication.

With a standalone vector store you would have to reproduce or reconcile each of those guarantees separately, and the seams between the two systems become a permanent source of bugs. With pgvector there are no seams, because there is only one system.

This comparison draws the boundary lines so you do not over- or under-claim what pgvector gives you. It IS a column type, a set of operators, supporting indexes, and SQL-native search. It is NOT a separate server, an embedding model, a drop-in replacement for full-text search, and it is not free of tuning work.

Keeping both columns in mind protects you from two opposite mistakes: treating pgvector as a magic box that solves AI for you, and dismissing it as 'just storage' when in fact it brings serious indexing capability.

These five bullets are the compressed mental model worth memorizing. Embeddings are arrays of floats; pgvector stores them in a column; operators measure distance between them; ordering by that distance and limiting the count is what 'search' means here; and because it is all Postgres, every SQL tool you know still applies.

If you internalize only this slide, you can reason about the rest of the topic correctly. Everything that follows — indexes, tuning, pitfalls — is just elaboration on these five facts.

The single most common beginner misconception is that installing pgvector somehow gives Postgres the ability to understand text. It does not. The intelligence lives entirely in the embedding model, which is an external service or library you call yourself.

The workflow is always: text goes into a model, the model returns a vector, and you hand that vector to Postgres. pgvector compares vectors; it never interprets meaning. Getting this straight now saves you from architectural confusion when you start wiring real systems together.

That closes the concept post. You now know what pgvector is, what it stores, how a similarity query looks, and where it sits in a RAG stack.

The next post tackles the obvious follow-up question: given that dedicated vector databases exist, why would a team deliberately keep their embeddings inside Postgres? The answer is mostly about consistency and operational cost.