If you are building a RAG app and you already run PostgreSQL, you do not need to set up a new database to store embeddings. You can install pgvector on PostgreSQL and get vector search working in minutes, right inside the database you already know.
This guide shows you how to install the extension, create vector-enabled tables, insert embeddings, run similarity searches, and add indexes for performance.
What is pgvector and Why Use It?
pgvector is an open-source PostgreSQL extension that adds a native vector data type and similarity search operators to your database. Instead of sending your embeddings to a separate vector database service, you store them as a column right next to your other data, documents, users, products, whatever you have.
For most RAG workloads at small-to-medium scale, this is the simplest and most cost-effective method. You get ACID transactions, joins, your existing backup and monitoring stack, and no extra system to operate.
The pgvector project supports exact and nearest neighbor search, L2 distance, cosine distance, inner product, and more.
What You Need for pgvector Setup
Before you start, make sure you have:
A Linux VPS running Ubuntu 22.04 or newer. This guide uses Ubuntu; you can easily buy a reliable Linux VPS from PerLod if you need one.
PostgreSQL 14, 15, 16, or 17 is already installed.
sudo access on the server.
Basic familiarity with psql.
To check your PostgreSQL version, you can run:
Bash
psql--version
Method 1: Install pgvector on PostgreSQL via APT Repository
The easiest way to install pgvector on PostgreSQL on Ubuntu is through the official PGDG apt repository. The package name follows the pattern:
Now that the extension is active, you can create a table with a vector column. The number in parentheses is the dimension count of your embeddings; this must match the output size of your embedding model.
For example, text-embedding-3-small from OpenAI outputs 1536 dimensions. all-MiniLM-L6-v2 outputs 384 dimensions.
SQL
1CREATETABLE documents (2 id BIGSERIAL PRIMARYKEY,3 content TEXT NOTNULL,4 source TEXT,5 embedding vector(1536)6);
If you already have a table and want to add a vector column, you can use:
Dimension limit note: For HNSW and IVFFlat indexes, the maximum supported dimensions are 2000 for vector (full precision) and 4000 for halfvec (half precision). Most popular embedding models used for RAG, like text-embedding-3-small at 1536 dimensions, fit within this limit without any issue.
Insert Embeddings into the Database
You can insert rows with embeddings using standard SQL. Vectors are written as a string of comma-separated floats inside square brackets:
SQL
1INSERTINTO documents (content, source, embedding)2VALUES3('PostgreSQL is a powerful relational database.','docs','[0.12, 0.45, 0.78, ...]'),4('pgvector adds vector search to Postgres.','blog','[0.33, 0.21, 0.66, ...]');
In a real RAG pipeline, you must generate these embeddings from a model, like OpenAI or a local sentence-transformers model, and pass them in from your application code. Here is a simple Python example using psycopg2:
Python
1import psycopg22import openai34conn = psycopg2.connect(5 host="localhost",6 port=5432,7 user="raguser",8 password="securepassword",9 dbname="ragdb"10)11cur = conn.cursor()1213# Generate an embedding with OpenAI14response = openai.embeddings.create(15 model="text-embedding-3-small",16 input="PostgreSQL is a powerful relational database."17)18embedding = response.data[0].embedding # list of 1536 floats1920# Insert into the table21cur.execute(22"INSERT INTO documents (content, source, embedding) VALUES (%s, %s, %s)",23("PostgreSQL is a powerful relational database.","docs", embedding)24)25conn.commit()26cur.close()27conn.close()
Run a Similarity Search
pgvector adds three distance operators you can use directly in SQL, including:
This kind of combined metadata with vector query is one of the biggest advantages of keeping everything in PostgreSQL; you do it in a single SQL query.
Index Your Vectors for Faster Queries
Without an index, every search scans the entire table. This is fine for development or small datasets, but for production, you need an index.
pgvector supports two index types:
HNSW (Hierarchical Navigable Small World): Recommended
HNSW is generally preferred for most production use cases. It builds a graph structure that allows fast and accurate nearest-neighbor searches. Build time is slower than IVFFlat, but query performance is better.
SQL
1-- For cosine distance (most common for RAG)2CREATEINDEXON documents3USING hnsw (embedding vector_cosine_ops);45-- For L2 distance6CREATEINDEXON documents7USING hnsw (embedding vector_l2_ops);89-- For inner product10CREATEINDEXON documents11USING hnsw (embedding vector_ip_ops);
m: number of connections per layer. The default is 16, and the higher number has better recall but with more memory.
ef_construction: search width during index build. The default is 64, and the higher value has better recall but with slower build.
Set ef_search at Query Time in HNSW: To improve recall during queries, you can set this session variable:
SQL
SET hnsw.ef_search =100;-- default is 40
IVFFlat: Faster to Build, Good for Large Datasets
IVFFlat divides data into clusters and searches within them. Build time is faster than HNSW, and it has a smaller memory footprint.
SQL
1-- Build the index AFTER inserting data (not before)2CREATEINDEXON documents3USING ivfflat (embedding vector_cosine_ops)4WITH(lists =100);
The lists value controls the number of clusters. A common rule is to use sqrt(number of rows) as a starting point.
Important Note: For IVFFlat, always insert your data first and then build the index. Building on an empty table produces a poor-quality index.
Build a Basic Retrieval Pipeline with pgvector
Here is how everything fits together in a real RAG pipeline. The idea is simple:
A user asks a question.
You embed that question using your embedding model.
You query the database for the most similar document chunks.
You pass those chunks as context to your LLM.
Python
1import psycopg22import openai34defget_relevant_context(question: str, top_k:int=5)-> list[str]:5 conn = psycopg2.connect(6 host="localhost", port=5432,7 user="raguser", password="securepassword", dbname="ragdb"8)9 cur = conn.cursor()1011# Embed the user's question12 response = openai.embeddings.create(13 model="text-embedding-3-small",14 input=question15)16 query_embedding = response.data[0].embedding1718# Run similarity search19 cur.execute("""20 SELECT content21 FROM documents22 ORDER BY embedding <=> %s::vector23 LIMIT %s24 """,(query_embedding, top_k))2526 rows = cur.fetchall()27 cur.close()28 conn.close()29return[row[0]for row in rows]3031# Use it32context_chunks =get_relevant_context("How does pgvector work?")33context_text ="\n\n".join(context_chunks)3435# Send to LLM36answer = openai.chat.completions.create(37 model="gpt-4o-mini",38 messages=[39{"role":"system","content":"Answer based on the context below.\n\n"+ context_text},40{"role":"user","content":"How does pgvector work?"}41]42)43print(answer.choices[0].message.content)
When Is pgvector Enough and When It's Not
For most teams, pgvector at first is enough. It works well when:
Your dataset is in the single-digit millions of vectors.
You want to store vectors alongside your relational data, such as documents, users, and metadata, in the same tables.
You already run PostgreSQL and do not want to operate another system.
You care about transactional consistency between your data and embeddings.
You need SQL joins and filters mixed with vector similarity in one query.
You should think about a dedicated vector DB when:
You are at hundreds of millions or billions of vectors with strict latency requirements.
You need a very high query-per-second throughput across a distributed cluster.
You need specialized features like multi-tenant isolation, hybrid BM25+vector ranking, or advanced hardware acceleration.
If you are unsure which vector database method fits your workload, check out this guide on the Best Vector Database for RAG.
Verify Your pgvector Setup
Run these quick checks to confirm everything is working:
SQL
1-- Check extension is installed2SELECT extname, extversion FROM pg_extension WHERE extname ='vector';34-- Check your table structure5\d documents67-- Count rows8SELECTCOUNT(*)FROM documents;910-- Run a test similarity query11SELECT id, content12FROM documents13ORDERBY embedding <=>'[0.1, 0.2, 0.3, ...]'14LIMIT3;
Conclusion
Setting up pgvector on PostgreSQL is one of the quickest ways when building a self-hosted RAG stack. You skip the overhead of a new database system and keep everything in one place. Once you install pgvector on PostgreSQL, you can create a vector column, insert embeddings from your model, and run similarity searches directly in SQL.
Just remember that the extension name in SQL is vector, not pgvector.
For the actual server to host your PostgreSQL and RAG stack, PerLod's AI-ready server hosting plans are designed for exactly this kind of workload.
CREATE EXTENSION vector; This is the correct command. The project is called pgvector, but the extension name is vector.
pgvector supports PostgreSQL 12 and above.
No, but without an index, every query does a full table scan. For more than a few thousand rows, add an HNSW index.
We use cookies to run PerLod and to understand how the site is used. You can accept everything, or open Manage to turn individual categories off. Cookie policy.