pgvector

A database is a program that stores and retrieves large amounts of data in an organized way. PostgreSQL, or Postgres for short, is one of the best-known of these programs and is available free of charge. Normally, one searches it for exact details: a name, a customer number, a date. pgvector is an extension that teaches Postgres an additional skill. It can store long lists of numbers that describe the content of a text or an image. This makes it possible to search by meaning instead of by exact match.

Similarity search without a dedicated specialized database

Modern AI applications almost always need a search by meaning. A chatbot that answers questions about company documents must first find the matching paragraphs. This gave rise to dedicated programs known as vector databases, such as Pinecone, Weaviate, or Milvus. They are specialized, but they mean an additional system that someone has to operate, secure, and pay for.

This is exactly where the appeal of pgvector lies. Very many companies already operate a Postgres database anyway. Instead of building a second system, they simply enable an extension and are done. Customer data and number lists then reside in the same database. Both can be combined in a single query, for example: find similar products, but only those under 50 euros and in stock.

For vector database providers, this was a hard blow. Many projects that would previously have automatically bought a specialized product now get by with Postgres. Major cloud providers such as Amazon, Google, and Microsoft have therefore built pgvector into their own Postgres offerings.

From number lists to neighbors in space

At the start is an embedding model. This is an AI model that translates a text into a list of numbers, often 768 or 1536 of them. This list is called a vector. Texts with similar meaning receive similar number lists, even if they don’t share a single word. You can imagine this as points in a space: content that is related in meaning lies close together.

pgvector adds a new data type called vector to Postgres. You create a column of this type and store the number lists there. In addition, the extension provides computational operators for the distance between two vectors. A search then works like this: the user’s question is likewise converted into a vector. The database then returns the ten entries with the smallest distance.

With millions of entries, it would be too slow to calculate every single distance. That’s why pgvector offers indexes named HNSW and IVFFlat. An index is a prepared structure that greatly shortens the search. The price for this is an approximation: the result is usually correct, but not guaranteed to be always correct. In practice, this is entirely sufficient, since a search hit isn’t an exact truth anyway.

pgvector in products and news

pgvector most often comes up in connection with RAG. This abbreviation stands for a method in which a language model looks up matching documents before answering. pgvector handles the lookup part of this. Companies use this to build internal assistants that search manuals, contracts, or support tickets.

The technology is also useful outside of chatbots. Online shops recommend similar items, photo apps find images with similar subjects, music services suggest related songs. In developer tools such as Supabase or Neon, pgvector can often be enabled with a single click.

A common misconception is that pgvector is itself an AI. That’s not true. The number lists are always generated by an external model, such as one from OpenAI or a freely available one. pgvector merely stores and compares them. And it has limits: with several hundred million vectors, many teams do hit limits again and switch to specialized systems.