What Is pgvector? The Postgres Vector Database, Explained

pgvector is the simplest way to build a Postgres vector database: one free extension adds a vector column type and similarity search to PostgreSQL, so embeddings live in the same table as the rows they describe and obey the same filters and permissions. This guide answers what pgvector is and what it is used for, then shows how to install it, which index to pick, how much memory it needs, and what it actually does on a small machine - we loaded 100,000 vectors and timed every query.

Facts are from the official pgvector repository and its changelog: the current release is 0.8.7, published on 1 October 2026, and this page was last checked on 14 November 2026. Benchmark numbers are our own, measured on 16 October 2026.

What is pgvector?

pgvector (also written “pg vector”) is a free, open-source PostgreSQL extension that stores AI embeddings in a vector column and finds the most similar ones with an ordinary SQL query. It does exact and approximate nearest-neighbour search, with HNSW and IVFFlat indexes and six distance functions, on Postgres 13 and later. You turn it on with CREATE EXTENSION vector;.

In one line each:

• What it stores: embeddings - lists of numbers an AI model produces from text, images or audio, where similar meanings sit close together.
• What it adds to Postgres: four column types (vector, halfvec, bit, sparsevec), distance operators such as <=> for cosine, and two index types that make search fast.
• What it does not do: create embeddings. You get those from a model, such as an embedding API or an open-source model you run yourself, and insert them like any other value.
• Licence and cost: the PostgreSQL License. The extension is free; you pay only for the database machine.

What is pgvector used for?

Anything that needs “find the rows most like this one”, next to data you already keep in Postgres:

1. Retrieval for AI chatbots (RAG). Find the five document chunks closest to a user’s question and pass them to a language model as context.
2. Semantic search. Search help articles, products or tickets by meaning, so “refund not received” also finds “money not credited back”.
3. Recommendations. “Customers who viewed this also viewed” from product or content embeddings.
4. Duplicate and near-duplicate detection. Flag support tickets, listings or uploads that say the same thing in different words.
5. Image similarity. Find visually similar product photos from image embeddings.
6. Classification and anomaly checks. Label a new item by its nearest labelled neighbours, or flag one that is far from everything seen before.

The reason to do these in Postgres rather than in a separate vector database is the join: one query can filter by tenant, permissions and metadata and rank by similarity at the same time, and deleting a row deletes its embedding in the same transaction.

Does Postgres support vector databases?

Not on its own. Core PostgreSQL (version 18 is current) has no vector type or similarity index: you can keep numbers in a real[] array, but there is no distance operator or index to search them. pgvector is the extension that adds them, and it is what most managed Postgres services mean when they say they support vector search. Once it is enabled, Postgres works as a vector database for most application workloads; the comparison further down covers where a dedicated system starts to make sense.

On this page

• How pgvector works: types and operators
• How to install pgvector
• HNSW or IVFFlat
• pgvector benchmark: 100,000 vectors on a small instance
• How much memory pgvector needs
• pgvector vs Pinecone and other vector databases
• Enable pgvector on Voroa, step by step
• FAQ

How pgvector works: types and operators

An embedding model turns text or an image into a list of numbers; similar meanings produce lists that are close together. pgvector stores the list in a column and finds the nearest ones:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id bigserial PRIMARY KEY,
  document_id bigint NOT NULL,
  tenant_id bigint NOT NULL,
  content text NOT NULL,
  embedding vector(1536)
);

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

Distance operators

Match the operator to the index’s operator class, such as vector_cosine_ops for <=>, or the index will not be used.

Which vector type to use

This decides a practical question. OpenAI’s embeddings guide lists text-embedding-3-small at 1,536 dimensions, which fits vector, and text-embedding-3-large at 3,072, which is too many to index as vector. For the large model, either store halfvec(3072), or ask the API for fewer dimensions with its dimensions parameter, for example 1,024, at a small cost in accuracy.

How to install pgvector

Most people do not compile pgvector. Pick the line that matches where your database runs.

Docker. The official image already contains it. Tags follow pg<version>-<debian release>:

docker run -d --name pgvector -e POSTGRES_PASSWORD=secret -p 5432:5432 pgvector/pgvector:pg17

Linux, from source. You need the Postgres server headers (postgresql-server-dev-17 on Debian and Ubuntu):

cd /tmp
git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install   # may need sudo

Windows. Open the “x64 Native Tools Command Prompt for VS” as administrator, point PGROOT at your install and build with nmake:

set "PGROOT=C:\Program Files\PostgreSQL\17"
cd %TEMP%
git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

Managed Postgres. Nothing to install: enable it from the provider’s console, then run CREATE EXTENSION vector; if the console does not do that for you. The Voroa steps, with screenshots, are further down.

Then check it worked:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

HNSW or IVFFlat

HNSW builds a layered graph in which every vector links to its closest neighbours. A search enters at the top layer, jumps towards the query, and drops a layer each time it gets close, so it touches only a small part of the data. That is why it is fast and accurate, and also why it uses more memory and takes longer to build: every insert has to find its place in the graph.

IVFFlat splits the vectors into groups around centre points and searches only the groups nearest to the query. It builds in a fraction of the time and needs less memory, which makes it a fair choice for a large table you rebuild often or a machine with little RAM, as long as you accept tuning probes to get recall up.

Start with HNSW. Raise hnsw.ef_search per query when you need better recall, at some cost in speed:

SET hnsw.ef_search = 100;

For IVFFlat, pgvector’s guidance is lists of about rows / 1,000 up to a million rows, and the square root of the row count above that; then raise ivfflat.probes until recall is good enough.

pgvector benchmark: 100,000 vectors on a small instance

Most guides stop at “HNSW is faster”. We wanted the numbers on the kind of machine a small team starts with, so we created a free Postgres 17 database with pgvector 0.8.0 on a 0.25 compute-unit instance (Neon’s free tier, with 64 MB of maintenance_work_mem), loaded 100,000 vectors of 384 dimensions - the size of common open-source text embedding models - and timed top-10 cosine queries.

Times are server-side (EXPLAIN ANALYZE), so the network is not in them. Recall is checked against an exact scan for 50 queries.

The index is the whole game. The same query went from 61 ms to a third of a millisecond, about 180 times faster. Without an index Postgres compares the query with every row; with HNSW it walks a graph and looks at a few hundred candidates instead of a hundred thousand.

Raise ef_search to 100 for production. The default of 40 returned 91% of the true nearest neighbours. At 100 it returned 99% and still answered in about half a millisecond. Past 100 we paid three times the time for no extra recall, so 100 is a sensible starting point for most apps.

halfvec cut the index by 43% for a 1.6-point recall loss. On a small machine that is often the difference between an index that stays in memory and one that keeps being read from disk, which is where the slow queries come from.

Your data changes everything, so test on it. We first ran the same benchmark on purely random vectors. The build took 386 seconds instead of 89, and recall at the default setting was 7.6%. Random vectors have no real neighbours, while real embeddings cluster around topics. Benchmark with a sample of your own embeddings, not with synthetic noise, or the result will mislead you in either direction.

Our vectors were synthetic but clustered the way embeddings are (1,000 groups of related points), and the queries were new points near those groups. A different dataset will give different absolute numbers; the ratios are what to take away.

Filtered search, and the catch

Real queries filter: only this customer’s documents, only this category. With an approximate index, Postgres first finds the nearest candidates, then applies the filter, so a selective filter can return fewer rows than you asked for. pgvector 0.8 added iterative index scans, which keep searching until enough rows pass the filter:

SET hnsw.iterative_scan = relaxed_order;

For filters that remove most rows, such as one tenant out of thousands, an ordinary B-tree index on the filter column, or a partial index per large tenant, often helps more.

A retrieval query for a chatbot

The core of retrieval-augmented generation is one query: the closest chunks the user is allowed to see.

SELECT c.content, d.title
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE c.tenant_id = $2
ORDER BY c.embedding <=> $1
LIMIT 5;

The join and the tenant_id filter are why keeping vectors in Postgres is attractive: permissions, metadata and similarity are answered together, with no second system to keep in sync.

From Python, the pgvector package registers the type with psycopg, SQLAlchemy and Django, and LangChain’s Postgres vector store uses pgvector underneath, so the same table works from a framework.

Common pgvector mistakes

The query does not use the index. The ORDER BY has to be the bare distance expression with the same operator as the index, followed by a LIMIT. An index built with vector_cosine_ops is ignored by a query that orders by <->, and so is one that sorts by 1 - (embedding <=> $1). Run EXPLAIN once on every new query and look for the index name in the plan.

Statistics were never collected. After a bulk load, run ANALYZE on the table. Without it the planner can misjudge how many rows a filter keeps and choose a full scan for a query that should have used the index, which looks like pgvector being slow when it is the plan that is wrong.

The dimension does not match. A vector(1536) column rejects a 1,024-number embedding with an error, and switching embedding models later means re-embedding every row. Record the model and its dimension next to the data, and treat a model change as a migration rather than a config change.

Inner product on unnormalised vectors. The <#> operator is only a stand-in for cosine when every vector has length 1. Most hosted embedding APIs return normalised vectors; many local models do not. If you are unsure, use cosine.

Building the index on a busy table. A plain CREATE INDEX blocks writes until it finishes, which on a large table can be many minutes. Use CREATE INDEX CONCURRENTLY in production, and load data before building an IVFFlat index, because it learns its groups from the rows that exist at build time.

How much memory pgvector needs

Vector search is fast only when the index sits in memory. In our benchmark the HNSW index was 1.3 times the raw vector data, and table plus index came to about 350 MB per 100,000 vectors of 384 dimensions. Raw vector sizes for common embedding sizes:

Add roughly the same again for the HNSW index, then leave room for the rest of your database. As a starting point on Voroa’s sizes:

These are estimates scaled from our measurement; measure your own index with SELECT pg_size_pretty(pg_relation_size('your_index')); before you size up. HNSW builds are much faster when the graph fits in maintenance_work_mem - Postgres prints a notice when it does not - and CREATE INDEX CONCURRENTLY keeps a live table writable while it builds.

pgvector vs Pinecone and other vector databases

pgvector covers what most teams build: documentation search, support ticket matching, product recommendations and chatbot retrieval. A dedicated vector database such as Pinecone, Milvus or Weaviate starts to earn its place in three situations: when you store tens of millions of vectors and the index no longer fits in one machine’s memory, when search must stay equally fast while the data changes constantly at high volume, or when you need vector search with no relational data at all.

Below that, a second system mainly adds another bill, another network hop on every query and another copy of your data to keep in sync with the rows it describes. With pgvector, deleting a document deletes its embedding in the same transaction; with two systems, that is a job you have to build and watch.

Enable pgvector on Voroa, step by step

Voroa’s managed PostgreSQL runs in Mumbai and offers pgvector as Vector Search on paid sizes. Every new database is built to support it. Only some older databases first created on the free plan are not, and upgrading alone does not change that; if Vector Search is not listed on your paid database, contact support. The full rules are in PostgreSQL extensions on Voroa, and sizes are on the pricing page.

Step 1. Create a Postgres database on a paid size. In the dashboard choose New, then Postgres, and pick a paid instance type with enough RAM for your index (the table above).

Step 2. Open Settings and find Extensions. On the database, go to the Settings tab and scroll to Extensions. Vector Search is listed with the other extensions.

Step 3. Select Enable next to Vector Search. The status changes to Pending while Voroa installs it, with no database restart, and to Enabled once it has checked the extension is really there.

Step 4. Create your table and index. Connect with the DATABASE_URL from the Connect tab (the details are in PostgreSQL connection details) and run the CREATE TABLE and CREATE INDEX statements from the top of this page.

Coming from Supabase, which also ships pgvector? See moving off Supabase; existing data, vector columns included, comes in through the database import. Just trying things out first? Our comparison of free Postgres databases covers the free tiers, including the limits that matter once an index is in memory.

FAQ

Is pgvector free?

Yes. pgvector is open source under the PostgreSQL License. You pay only for the machine your database runs on.

Can pgvector handle 10 million vectors?

Yes, if the machine has the memory. Ten million 1,536-dimension embeddings are about 61 GB as vector or 31 GB as halfvec before the index, and an HNSW index adds roughly the same again, so plan for a large instance or shorter embeddings (10 million at 384 dimensions is about 15 GB). Build the index with plenty of maintenance_work_mem, use CREATE INDEX CONCURRENTLY, and test recall with your filters. When one machine’s memory is no longer enough, a dedicated vector database is worth comparing.

Is pgvector the same as pg_vector or pg vector?

Yes. The project is called pgvector, people often write it “pg vector” or “pg_vector”, and the extension you create inside Postgres is named vector.

Is pgvector good enough for production?

Yes, for most AI features. Plan the memory, use an HNSW index, set hnsw.ef_search to about 100, and test recall on your own data with your real filters.

Does pgvector work with Prisma or Django?

Yes. Prisma treats vector columns as an unsupported type and queries them with raw SQL. Django has the official pgvector Python package, with a field type and distance functions.

What embedding size should I use?

The smallest that is accurate enough on your data. Smaller embeddings need less memory and search faster; test a shortened embedding before paying for the largest.

Why does my filtered vector query return fewer results than LIMIT?

The index found the nearest candidates before the filter removed most of them. Turn on iterative index scans, or add an index on the filter column.

Add vector search next to your data

Create a paid PostgreSQL database in Mumbai, turn on Vector Search, and keep your embeddings in the same database as your app. For the wider choice of Postgres hosts, read Neon vs Supabase.