How to use the pg_vector plugin in PostgreSQL

You can use PostgreSQL with the pg_vector extension to store and search high-dimensional vectors, which is essential for tasks like vector similarity and retrieval-augmented generation (RAG). Many businesses rely on PostgreSQL pg_vector RAG features for recommendation systems and AI-powered tools. Industries such as e-commerce and fraud detection benefit from these capabilities as well. With PostgreSQL pg_vector RAG, you can perform efficient nearest-neighbor searches and manage embeddings directly within your database. To get started, install pg_vector in PostgreSQL, enable the extension, and then set up vector columns for your data.

Install and Enable pgvector in PostgreSQL

You have to add the pgvector extension before using your database for vector similarity search or retrieval-augmented generation. This part shows you how to install and turn on pgvector on different systems.

Install with Package Manager or Source

You can get pgvector with a package manager or by building it from source. The way you pick depends on your computer and setup.

On macOS, Homebrew helps you install pgvector:

1

brew install pgvector

After you finish, connect to your PostgreSQL server:

1

psql postgres

Then make the extension:

1

create extension vector;

If you want to build pgvector from source, you need some things first. You must have PostgreSQL already installed. You also need to turn on some extensions and make a table for your vector database. Here are the steps:

You can use Docker to set up your vector database fast. The ankane/pgvector image has PostgreSQL and pgvector already set up. This way saves time and skips hard setup steps. If you use Docker Compose, you can add both PostgreSQL and pgvector at once. This makes starting your vector database simple.

Enable Extension in Database

After installing pgvector, you must turn on the pgvector extension in your database. Do these steps:

1. Change to the postgres user:

1

sudo su - postgres

2. Open the PostgreSQL command line:

1

psql

3. Make the pgvector extension:

1

CREATE EXTENSION vector;

4. Check if the extension is on:

1

SELECT * FROM pg_extension WHERE extname = 'vector';

If you see a row with vector, it is working.

You must turn on the pgvector extension before making vector columns or using vector similarity search. This step is needed to build a vector database.

Use pgvector in Cloud Environments

Many cloud companies let you use pgvector. You can use pgvector in services like Aiven and Azure Database for PostgreSQL. These services help you build a vector database with AI tools.

To turn on pgvector in Aiven for PostgreSQL:

1. Connect to your Aiven PostgreSQL service with a client like psql.

2. Connect to your database:

1

\c database-name

3. Turn on the pgvector extension:

1

CREATE EXTENSION vector;

4. Make a table for vector embeddings:

1

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));

5. Add embeddings:

1

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

6. Run a similarity search:

1

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

7. Add an index for better speed:

1

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

Azure Database for PostgreSQL also lets you use vector search. You can store embeddings and use AI tools for your apps. These cloud services make it simple to set up a vector database and use smart search tools.

Tip: Cloud services change often. Look at the docs for the newest steps and features.

Now you know how to add and turn on pgvector on your system. You can use your vector database for similarity search and AI jobs.

Create Vector Columns and Tables

Define Vector Data Types

You must pick the right vector data type first. The pgvector extension lets you save vectors with a set number of dimensions. You can choose the size based on your model or what you need. Many AI models use 768 or 1536 dimensions.

Here are some good tips for picking vector data types:

• Normalize vectors if you use cosine similarity. This helps you get better results.

• Use HNSW indexing for searches that need high recall. IVFFlat indexing is good for both speed and accuracy.

• If you have huge datasets, like over 100 million vectors, use pgvector with a special vector database.

• Run VACUUM and ANALYZE after adding lots of data. This keeps your database running well.

Tip: Always make sure your vector dimension matches your embedding model’s output.

Create Table with Vector Column

You can make a table with a vector column using SQL or tools like SQLAlchemy. The vector column holds your embeddings.

Here is a code example that shows how to make a table with a vector column using SQLAlchemy and pgvector:

CREATE EXTENSION IF NOT EXISTS vector;

from sqlalchemy import MetaData, Table, Column, BigInteger from pgvector.sqlalchemy import Vector, HALFVEC

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

def create_table(engine, table_name):
    metadata = MetaData()
     table_ref = Table(
         table_name,
         metadata,
         Column(
             "id",
            BigInteger,
             primary_key=True,
             autoincrement=True,
         ),
         Column(
             "embedding",  # the column name
             HALFVEC(768),  # the column type
             nullable=True,
         )
     )
     metadata.create_all(engine)
     return table_ref

You can also use regular SQL to make a table:

1
2
3
4

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding vector(768)
);

Timescale Hypertable for Vectors

TimescaleDB helps you work with time-series data and vectors. You can save embeddings and search for similar things over time.

To find products like another item, use a similarity function such as L2 distance:

1
2
3

SELECT * FROM products
ORDER BY embedding <-> '[0.22, 0.57, 0.75, ...]'
LIMIT 5;

• Timescale Vector makes hybrid time-based vector search better.

• It uses automatic time-based splitting and indexing of hypertables.

• You can get recent embeddings and limit searches by time.

To set up a hypertable for vectors, do these steps:

1. Add vector columns to your PostgreSQL tables.

2. Make an embedding table with a vector column:

1
2
3
4
5
6

CREATE TABLE products (
        id bigserial PRIMARY KEY,
        name text,
        description text,
        embedding vector(768)
    );

3. Add vector data using a vectorizer tool.

Note: TimescaleDB helps you scale vector searches and handle data well.

Insert and Manage Vector Data

Insert Vectors with SQL

You can put vector data into your PostgreSQL tables with SQL. First, get your embeddings ready. If you have lots of vectors, add them in groups. Batch inserts make things faster and easier. You can use Python with the psycopg2 library. The execute_values function lets you add many vectors at once.

Here is a simple SQL example:

1

INSERT INTO items (embedding) VALUES ('[0.12, 0.34, 0.56]');

If you want to add more vectors, make a list. Use batch insert to add them all together. This way helps you handle big datasets and keeps your database quick. After you finish, save your changes by committing.

Tip: Batch inserts are best when you have lots of embeddings. This way makes each step faster.

Update and Delete Vector Entries

Sometimes you need to change or remove vector data. Here are steps to update or delete entries:

• To update, find the vector by its ID or metadata.

• Make a new embedding for your changed document.

• Use the UPDATE command to swap the old vector.

1

UPDATE items SET embedding = '[1, 2, 3]' WHERE id = 1;

• To delete, use the document’s ID and run the DELETE command.

1

DELETE FROM items WHERE id = 1;

Note: Updating many vectors at once can slow things down. Plan your updates so your database stays fast.

You can keep your vector store neat by updating and deleting entries. This helps you have good data for searches and AI jobs.

Vector Similarity Search in PostgreSQL

Image Source: pexels

PostgreSQL can do vector similarity searches with pgvector. This helps you find items that are alike in meaning or features. You can use these searches for things like semantic search and recommendations. They also help with retrieval-augmented generation (RAG) tasks. You will learn how to find nearest neighbors, use indexes for speed, and use vector functions to check similarity.

Exact Nearest Neighbor Search

Start with exact nearest neighbor search. This method checks every row to find the closest vectors. You use a distance metric like Euclidean, inner product, or cosine distance. Cosine distance is good for semantic search because it shows how similar two vectors are.

To run an exact search, use a query like this:

1
2
3

SELECT * FROM items
ORDER BY embedding <-> '[0.12, 0.34, 0.56]'
LIMIT 5;

This query sorts items by how close their embedding is to your target vector. The <-> operator finds similarity with your chosen metric.

Tip: Exact searches are best for small tables. If your table has less than 10,000 rows, you get fast results. For bigger tables, searches slow down because PostgreSQL checks every row.

Here is a table that shows the main differences between exact and approximate searches:

You get perfect recall with exact search, but it gets slower as your data grows. For tables with 10,000 rows, results come in about 36 milliseconds. If your table has 50,000 rows or more, you need indexes to keep searches quick.

Approximate Search with Indexes

You can make searches faster by using approximate nearest neighbor (ANN) methods. These use special indexes to find similar vectors quickly. You lose a little accuracy but get much faster results, which helps with lots of data.

pgvector supports two main index types for ANN:

You can make an IVFFlat index like this:

1

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

You can change the number of lists and probes to balance speed and recall. More lists and probes give better accuracy but slower searches. Fewer lists and probes make searches faster but less accurate.

Note: Use HNSW for very big tables or high-dimensional vectors. IVFFlat works well for most cases and is easy to set up.

Approximate search helps you scale semantic search and vector similarity search in PostgreSQL pg_vector RAG apps. You can handle millions of vectors and still get results fast.

Use Vector Functions

You can use vector functions in pgvector to check how similar vectors are. These functions let you do semantic search and similarity search with different metrics.

Some common vector functions and features are:

• You can store vectors from word or sentence embeddings, like BERT or Word2Vec.

• You can pick a similarity metric, like Euclidean distance or cosine similarity, to see how close two vectors are.

• You can use fast indexing structures, like KD-trees or HNSW, to make searches quicker.

• You can choose the best distance metric and indexing method for your needs.

To check similarity, use a query like this:

1
2
3
4

SELECT id, embedding
FROM items
ORDER BY embedding <-> '[0.12, 0.34, 0.56]'
LIMIT 5;

This query finds the top 5 items most like your target vector. You can use this for semantic search, recommendations, or RAG tasks.

Tip: Try different distance metrics to see what works best for your data. Cosine similarity often gives good results for semantic search.

You can use pgvector to run fast similarity searches, store embeddings, and build smart apps. PostgreSQL pg_vector RAG features help you scale your vector similarity work and support advanced AI tools.

Optimize Performance with Indexing

Create Indexes for Vectors

You can make vector searches faster by adding indexes. Indexes help PostgreSQL find similar vectors quickly. This is important when your table gets big. Pgvector has two main index types:

• IVFFlat: This index puts similar vectors in groups. It looks at nearby vectors to speed up searches. IVFFlat works for most datasets. It balances speed, accuracy, and storage.

• HNSW: This index uses a graph. It is good for fast searches and high recall. HNSW works best with high-dimensional data. It helps when your data changes a lot or you have many vectors.

To make an IVFFlat index, use:

1

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

To make an HNSW index, use:

1

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 200);

You can also add indexes to columns you filter by often, like category or status. This makes searches faster after finding similar vectors.

Tip: Change the number of lists for IVFFlat or the ef_search value for HNSW to get the best speed and accuracy.

Query Optimization Tips

You can make searches even quicker by using some smart steps:

• Change index settings like lists for IVFFlat to fit your data size.

• Use EXPLAIN ANALYZE to check how your queries run. This helps you find slow parts.

• Split big tables into smaller pieces. This helps PostgreSQL search faster and makes it easier to take care of your data.

• Make sure your server has enough RAM. At least 16GB is good for most jobs. NVMe SSDs and multi-core CPUs also help.

• Run VACUUM and ANALYZE often. These keep your database healthy and your searches quick.

• Clean and normalize your vector data. This removes noise and puts data on the same scale. It helps you get better search results.

Note: Doing regular maintenance, like vacuuming and reindexing, keeps your database working well as your data grows.

If you follow these steps, you can work with millions of vectors. Your searches will stay fast and accurate.

Use pg_vector for RAG Applications

Integrate with Retrieval-Augmented Generation

You can make strong RAG systems with postgresql pg_vector rag features. These systems help natural language tasks work better. They mix finding information with making new text. RAG pipelines use vector embeddings to get useful info and add more details. Here are steps to set up a RAG workflow:

1. Collect documents for your database. You can use text, pictures, or data from computer vision.

2. Index your documents in PostgreSQL with pgvector rag. Put vector embeddings in a table.

3. Get a user query. This can be a question or a prompt.

4. Search your vector database for the best embeddings.

5. Send the top results and the query to your language model.

6. Get an answer that uses both the found data and the model’s output.

RAG apps use postgresql pg_vector rag to make generated text clearer and more useful. You can use this for answering questions, making summaries, and computer vision jobs.

Store and Search Embeddings

You need to save and search embeddings for your RAG system. First, turn on pgvector in your PostgreSQL setup. Make a table with a vector column, like vector(384), for your embeddings. Create embeddings with your machine learning model. Put these embeddings into your table.

You can search for similar documents using vector embeddings. Run a query to find the closest matches to your input. This helps you get the best context for natural language and computer vision tasks.

1
2
3
4
5
6
7
8

CREATE TABLE docs (
    id bigserial PRIMARY KEY,
    content text,
    embedding vector(384)
);

INSERT INTO docs (content, embedding) VALUES ('Sample text', '[0.12, 0.34, 0.56, ...]');
SELECT * FROM docs ORDER BY embedding <-> '[0.11, 0.33, 0.55, ...]' LIMIT 5;

Tip: Always match your vector embedding size to your model’s output. This keeps searches quick and correct.

You can use postgresql pg_vector rag to store millions of embeddings. This lets you grow your natural language and computer vision projects easily.

Disable or Remove pgvector

If you do not need vector search, you can turn off or take out the pgvector extension. This keeps your database neat and free from things you do not use. Always be careful so you do not lose data or make mistakes.

Drop Extension Safely

You can take out the pgvector extension by doing these steps:

1. Look for tables or columns that use the vector data type. Change or delete them before you drop the extension.

2. Make a backup of your database. This keeps your data safe if you need it later.

3. Use this SQL command to drop the extension:

1

DROP EXTENSION vector;

4. Check if the extension is gone by running:

1

SELECT * FROM pg_extension WHERE extname = 'vector';

If you do not see anything, the extension is gone.

Tip: Always make a backup before you change your database. This helps you get your data back if something goes wrong.

Clean Up Vector Data

After you remove pgvector, clean up any vector data left behind. This keeps your database tidy and stops problems.

• Delete tables with vector columns. Use the DROP TABLE command to do this.

• Change tables that have vector columns. You can remove the column or change its type.

• Take out indexes for vector columns. Use the DROP INDEX command for this.

Here is a checklist to help you clean up:

Note: Cleaning up old data helps your database work faster and saves space.

If you follow these steps, you can safely turn off or remove pgvector from PostgreSQL. Your database will stay clean and ready for new work.

You can get pgvector working in PostgreSQL by doing a few steps. First, install what you need and copy the pgvector files. Next, connect to your database and make the extension. Then, add a vector column and put in your data. After that, you can search for similar items.

Pgvector makes things easier to use and gives you many ways to search. It also helps save money. If you want to make it even better, try using cloud services or manage groups of servers. You can also use Terraform to help set things up. Always check if everything works well and is safe before you make your system bigger.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL