How to Install pgvector on PostgreSQL for a Self-Hosted RAG Stack

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.

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.

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:

The easiest way to install pgvector on PostgreSQL on Ubuntu is through the official PGDG apt repository. The package name follows the pattern:

To install pgvector on PostgreSQL, you can use:

If you are not sure which PGDG repo is set up on your server, run the setup script first:

Then install the pgvector package for your version.

Once the package is installed, you need to enable the extension inside each database where you want to use it.

First, connect as the postgres superuser:

Then connect to your target database, replace mydb with your actual database name:

Now enable the extension:

Verify it is active:

You should see the vector in the list. You can also check the version:

This is the most common issue everyone makes for the first time. The project is called pgvector, but the SQL extension name is vector, not pgvector.

If you run this:

You will get an error like:

The correct command is always like this:

The same rule applies when using CREATE EXTENSION IF NOT EXISTS:

If you prefer using Docker, the official pgvector Docker image is the quickest way. It includes PostgreSQL with pgvector already compiled and ready:

Connect to it with the command below:

Then enable the extension as shown above:

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.

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.

You can insert rows with embeddings using standard SQL. Vectors are written as a string of comma-separated floats inside square brackets:

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:

pgvector adds three distance operators you can use directly in SQL, including:

Cosine similarity search, which is best for RAG:

L2 distance search:

Filter by metadata and then rank by similarity:

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.

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 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.

Also, you can tune HNSW parameters:

• 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:

IVFFlat divides data into clusters and searches within them. Build time is faster than HNSW, and it has a smaller memory footprint.

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.

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.

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.

Run these quick checks to confirm everything is working:

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.