pgvector in PostgreSQL: What It Is and How to Use It

Introduction to pgvector

As artificial intelligence (AI) continues to evolve, handling vector data efficiently has become increasingly important. Vector data is critical in machine learning, recommendation systems, and various AI-related fields where embedding and similarity search are common operations. PostgreSQL, a widely-used open-source relational database, now offers a way to manage vector data using an extension called pgvector.

In this blog post, we'll explore what pgvector is, its applications, how to install it, and how to use it in PostgreSQL. Let’s dive into the world of vectors and how you can manage them directly within PostgreSQL.

What is pgvector?

pgvector is an open-source PostgreSQL extension designed to store and query vector data efficiently. Vectors, often used in machine learning models and recommendation engines, can represent various types of embeddings — text, images, and more. These vectors need to be stored in a database and used for similarity searches, which is where pgvector comes into play.

With pgvector, PostgreSQL users can directly store vector representations of data and run similarity searches, significantly improving the workflow for AI and ML-based applications.

Use Cases of pgvector

pgvector is useful in many applications that require vector-based data storage and retrieval. Some common use cases include:

• Recommendation Systems: Storing embeddings from recommendation algorithms to find similar items (e.g., similar products, songs, or movies).
• Natural Language Processing (NLP): Handling text embeddings for sentence or document similarity search in chatbots, search engines, or content analysis.
• Image Recognition: Storing image embeddings for image similarity search in e-commerce platforms or image search engines.
• Search Engines: Storing vector representations of documents for fast and efficient similarity queries.

How to Install pgvector on PostgreSQL

Installing on Ubuntu

To install pgvector on Ubuntu, you can follow these steps:

1. Ensure PostgreSQL is installed: First, make sure you have PostgreSQL installed on your system. If not, you can install it using the following commands:

sudo apt update sudo apt install postgresql postgresql-contrib

2. Install pgvector extension: After installing PostgreSQL, install pgvector by running the following command:

sudo apt install postgresql-pgvector

 

3. Create the pgvector extension in your database: Once pgvector is installed, you need to create the extension in your database:This will enable pgvector for your database.

CREATE EXTENSION IF NOT EXISTS vector;

How to Use pgvector in PostgreSQL

Now that you have pgvector installed, let’s look at how you can start using it to store and query vector data.

1. Create a Table with a Vector Column

You can create a table that stores vector data using the vector data type. For example, let’s create a table that stores product embeddings for a recommendation system:

CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100), embedding vector(3) -- 3-dimensional vector );

 

In this example, embedding is a 3-dimensional vector that stores product embeddings.

2. Insert Vector Data

Next, let’s insert some vector data into the table:

INSERT INTO products (name, embedding) VALUES ('Product A', '[0.1, 0.2, 0.3]'), ('Product B', '[0.2, 0.3, 0.4]'), ('Product C', '[0.3, 0.4, 0.5]');

 

Here, we are inserting three products with their corresponding embeddings.

3. Perform a Similarity Search

One of the key features of pgvector is its ability to perform similarity searches. For example, let’s find the product that is most similar to a given vector using the Euclidean distance:

SELECT id, name, embedding FROM products ORDER BY embedding <-> '[0.15, 0.25, 0.35]' LIMIT 1;

 

The <-> operator performs the Euclidean distance calculation, and the query returns the product with the closest embedding to the vector [0.15, 0.25, 0.35].

4. Other Distance Metrics

Besides Euclidean distance, pgvector supports other distance metrics for similarity search:

• Cosine Distance:

SELECT id, name, embedding FROM products ORDER BY embedding <=> '[0.15, 0.25, 0.35]' LIMIT 1;

Here, <=> performs the cosine similarity search.

• Inner Product:

SELECT id, name, embedding
FROM products
ORDER BY embedding <#> '[0.15, 0.25, 0.35]'
LIMIT 1;

The <#> operator performs the inner product search.

Practical Example: Text Embedding Search

Let’s consider a practical example where we store text embeddings and perform a similarity search.

1. Create a Table for Text Embeddings:This table stores text embeddings as 5-dimensional vectors.

CREATE TABLE texts ( id SERIAL PRIMARY KEY, content TEXT, embedding vector(5) );

 

2. Insert Data:

INSERT INTO texts (content, embedding) VALUES ('Text A', '[0.1, 0.2, 0.3, 0.4, 0.5]'), ('Text B', '[0.2, 0.3, 0.4, 0.5, 0.6]');

 

3. Perform Similarity Search:This query finds the text with the closest embedding to the input vector.

SELECT content, embedding FROM texts ORDER BY embedding <-> '[0.15, 0.25, 0.35, 0.45, 0.55]' LIMIT 1;

 

Conclusion

pgvector is a powerful extension for PostgreSQL that allows you to store and query vector data efficiently. Its use cases span across various fields, including AI, machine learning, recommendation systems, and more. By integrating pgvector into your PostgreSQL setup, you can enhance your ability to handle embedding data and perform similarity searches directly within your database.

Now that you know what pgvector is and how to use it, you can start leveraging vector data in your projects with ease. Whether you’re building a recommendation engine or storing text embeddings for NLP tasks, pgvector can provide the tools you need to succeed.