Published on

What is pgvector? A Guide to Vector Search in PostgreSQL

pgvector is an open-source extension for PostgreSQL that allows you to store, query, and index vector embeddings (mathematical representations of data) directly within your database. By adding this extension, you can perform high-speed similarity searches—such as finding images or text documents that are contextually related—in less than 20 milliseconds for most standard datasets. It is the industry standard for building AI-powered applications that require long-term memory or semantic search (searching by meaning rather than keywords) using tools like Claude Opus 4.5 or GPT-5.

Why is pgvector important for AI applications?

Most traditional databases search for exact matches, like finding a specific username or an order ID. However, modern AI models like Claude Sonnet 4 represent information as "vectors" (long lists of numbers that capture the meaning of a word, image, or sound).

If you want to build a chatbot that "remembers" your company's documents, you need a way to store these number lists and find the ones most relevant to a user's question. Without pgvector, you would have to use a completely separate "vector database," which adds complexity to your tech stack.

By using pgvector, you keep all your regular user data and your AI data in one place. This makes your application easier to maintain and much faster to develop.

What are the prerequisites for getting started?

Before you can use pgvector, you need a environment where you can run PostgreSQL. Don't worry if you've never managed a database before; many modern services pre-install this for you.

  • PostgreSQL 12+: You'll need a relatively recent version of Postgres (the world's most popular open-source relational database).
  • pgvector Extension: This must be installed on your server. If you use a cloud provider like Supabase, AWS RDS, or Neon, it is usually available by default.
  • Development Environment: You should have a tool like psql (a command-line tool for interacting with Postgres) or a visual editor like DBeaver.

How do you enable pgvector in your database?

Even if the software is installed on your server, it isn't active in your specific database by default. You have to "turn it on" for every new database where you plan to use it.

Step 1: Connect to your database. Open your terminal or database tool and log in to your PostgreSQL instance.

Step 2: Run the extension command. Type the following command and press enter:

-- This command enables the vector functionality
CREATE EXTENSION IF NOT EXISTS vector;

Step 3: Verify the installation. You can check if it worked by running:

-- This lists all active extensions
SELECT extname FROM pg_extension;

What you should see: You should see "vector" in the list of results. This means your database now understands how to handle high-dimensional AI data.

How do you create a table for vector data?

Now that the extension is active, you can create tables that store embeddings (the numeric lists generated by AI models). Each AI model has a specific "dimension" size (the number of digits in its list). For example, many modern embedding models use 1536 dimensions.

Step 1: Define your table structure. You will create a table that has a standard ID, some text content, and a special vector column.

CREATE TABLE documents (
  id serial PRIMARY KEY,
  content text,
  -- We use 3 for this example, but real models use 1536 or more
  embedding vector(3) 
);

Step 2: Add some sample data. In a real app, an AI model would generate these numbers. For now, we can add them manually to see how it looks.

INSERT INTO documents (content, embedding) VALUES 
('The cat is on the mat', '[1, 2, 3]'),
('Dogs love playing fetch', '[4, 5, 6]'),
('The feline is resting', '[1.1, 2.1, 3.1]');

What you should see: PostgreSQL will confirm that three rows have been inserted. It treats the vector data just like any other piece of information.

The "magic" of pgvector is finding items that are "close" to each other in mathematical space. This is how a search for "feline" can find a result about "cats" even if the words are different.

Step 1: Use the distance operator. The <-> symbol in pgvector calculates the Euclidean distance (the straight-line distance between two points) between vectors.

-- Find the 2 items most similar to the vector [1, 2, 2]
SELECT content FROM documents 
ORDER BY embedding <-> '[1, 2, 2]' 
LIMIT 2;

Step 2: Understand the results. The database calculates which vectors have the smallest "distance" from your search query and returns them first.

What you should see: You should see "The cat is on the mat" and "The feline is resting" at the top of your results. Because their numbers are closer to [1, 2, 2] than the dog example is, the database knows they are more relevant.

What are indexes and why do you need them?

As your database grows to thousands or millions of rows, calculating the distance for every single item becomes slow. This is where an index (a physical structure that speeds up data retrieval) comes in.

In our experience, choosing the right index is the most important step for scaling an AI product to thousands of users.

  1. HNSW (Hierarchical Navigable Small Worlds): This is the most popular index in 2026. It creates a multi-layered graph that allows the database to "jump" quickly to the right neighborhood of data.
  2. IVFFlat (Inverted File with Flat Compression): This divides vectors into clusters. It's faster to build than HNSW but usually slightly less accurate.

To create an HNSW index, you would run:

-- This creates a high-performance index for faster searching
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops);

What you should see: The command might take a few seconds if you have lots of data. Once finished, your searches will remain lightning-fast even as you add more documents.

What are some common gotchas for beginners?

It is normal to feel a bit overwhelmed when first working with vectors. Here are a few things to keep in mind:

  • Dimension Mismatch: If you create a column with vector(1536) but try to insert a list of 512 numbers, the database will throw an error. You must always match the dimensions of your AI model.
  • Distance Metrics: Different AI models prefer different ways of measuring "closeness." While we used <-> (Euclidean), some models prefer <#> (Inner Product) or <=> (Cosine Distance). Check your AI model's documentation to see which one to use.
  • Memory Limits: Large indexes like HNSW live in your server's RAM (Random Access Memory). If your database server has very little RAM, your performance might drop as your index grows.

Next Steps

Now that you understand the basics of pgvector, you are ready to start building. You can try connecting a Python script using a library like psycopg2 or a Node.js app using drizzle-orm to automate the process of sending text to Claude or GPT-5 and saving the resulting vectors into your database.

To dive deeper into advanced configurations and performance tuning, check out the official pgvector documentation.

Read the Pgvector Documentation