Blog
Getting Started with Pgvector: A Guide for Developers Exploring Vector Databases

Getting Started with Pgvector: A Guide for Developers Exploring Vector Databases

Jul 01, 202411 min read

With recent advancements in generative AI and large language models, the need for more efficient data storage and retrieval systems has become crucial. A new type of database called vector databases has emerged, gaining widespread popularity in the AI community.

Vector databases excel at storing and querying high-dimensional data called vector embeddings, making them ideal for AI applications that deal with complex data representations. Unlike traditional databases, vector databases can efficiently perform similarity searches and handle unstructured data, which is essential for many modern AI tasks.

Still, traditional relational databases like MySQL and PostgreSQL have long been the go-to choice for developers, and old habits die hard. Many developers who choose these tools have sought to embark on vector search with their trusted systems. For PostgreSQL fans, their postgres vector database of choice is called Pgvector.

In this article, we'll explore Pgvector, the extension of PostgreSQL that brings vector index capabilities to this widely-used relational database. We'll look into how Pgvector can do a vector similarity approximate nearest neighbor search by focusing on how to create indexes, triggering an approximate nearest neighbor search, Pgvector's advantages, and its limitations. Additionally, we'll compare Pgvector with specialized vector databases, highlighting where each solution might be most appropriate.

If you're a developer familiar with traditional databases and looking to adapt to the evolving landscape of AI-driven data management, this guide will provide you with the knowledge to get started with Pgvector.

What is Pgvector?

AI and machine learning are now used in many industries, including technology, medicine, and automobiles. In these fields, data is often represented as vector embeddings - numbers that capture features of images or texts. Similar vector embeddings are close to each other in mathematical space.

Pgvector is an extension of PostgreSQL, a popular open-source database system. It adds the ability to store and search vector embeddings alongside regular data. Many people call this their "postgres vector database."

Working with Pgvector is just like using a standard SQL database. The commands for creating vector columns, making new tables with vectors, and finding similar vectors are familiar to those who know SQL.

Pgvector is useful for prototyping AI applications, recommendation systems, retrieval augmented generation, and projects that use complex data. It offers an efficient way to store vector values without needing extensive knowledge of vector storage.

Key features of Pgvector include:

Vector Storage: It allows storing vector embeddings alongside regular data in PostgreSQL.
Similarity Search: Pgvector supports both exact and approximate nearest neighbor search, which is crucial for finding similar items in AI applications.
Versatility: It works with different types of vectors (single-precision, half-precision, binary, and sparse) and supports various similarity measures like L2 distance, inner product, cosine distance, L1 distance, Hamming distance, and Jaccard distance.
Language Support: It can be used with any programming language that can connect to PostgreSQL.
Database Features: Pgvector maintains PostgreSQL's standard features like data consistency and recovery.
SQL Integration: Users familiar with SQL can easily work with Pgvector, as it uses similar commands for creating vector columns and performing searches.

It also provides the usual database features like data consistency and recovery.

Pgvector offers two types of indexing allowing users to balance between search speed and accuracy:

HNSW: Creates a multi-layer graph. It's faster for queries but slower to set up and uses more memory.
IVFFlat: Divides vectors into groups. It's quicker to set up and uses less memory but isn't as fast for queries as HNSW.

These indexing options allow users to balance between search speed and accuracy based on their needs.

The extension's seamless integration with PostgreSQL allows users to leverage familiar SQL syntax while performing advanced vector operations. This makes Pgvector a powerful tool for bridging traditional relational databases with modern AI and machine learning needs.

Setting up Pgvector

Now, let’s get started by first setting up Pgvector to integrate it with PostgreSQL. Make sure you have PostgreSQL installed in your system. For Mac, you can easily install it via Homebrew:

brew install postgresql

You can quickly check if you have PostgreSQL installed by running the following command:

psql --version

Then, it should give you the version of PostgreSQL installed on your system as shown below:

You’ll also need to install make. You can easily install it using Homebrew by running the following command:

brew install make

Then, it should install make in your system as shown below:

Before you dive into the world of vector databases, you’ll need to set up Pgvector and integrate it with PostgreSQL. Let’s walk through the necessary steps.

Clone the Pgvector repo.

cd /tmp && git clone --branch v0.4.4 https://github.com/pgvector/pgvector.git

Head into this directory and run the following make commands:

cd pgvector && make && make install

Integrating Pgvector with Postgres

Open the PostgreSQL command-line interface (psql) using the following command. This step will start Postgres on your command line so you can run Postgres commands directly on your terminal:

psql

You can create a user to use Postgres using the following command:

CREATE USER <user> WITH PASSWORD <password>

Then, log in to that user using the credentials you created in the above command. Or, you can also log in to Postgres as a superuser:

psql -U postgres

Now, let’s create a new database to work with the following command:

create database vectordb;

Let’s select this database:

/c vectordb;

Then, we’ll enable the Pgvector extension for our vectordb database:

create extension pgvector;

You only need to perform this step once for every database you want to use with Pgvector.

Pgvector Example

Let’s create two vector columns, id and embedding, in a table called vectors. The table and columns store the vector data in PostgreSQL.

CREATE TABLE vectors (
    id SERIAL PRIMARY KEY,
    embedding float4[] -- The vector column
);

We can now insert some vector data into our vectors table:

INSERT INTO vectors (embedding) VALUES
    ('{1.2, 0.8, -2.1}'),
    ('{-0.7, 2.4, 3.6}');

To view the table, we can simply run the SELECT * query on our vectors table.

SELECT * FROM vectors;

Using Pgvector for vector similarity searching

We’ve discussed how vector databases can be beneficial for performing similarity searches. Here’s how to write a simple similarity search query to find vectors similar to a given query vector.

SELECT * FROM vectors
WHERE pgvector_cosine(embedding, '{0.9, -0.3, 1.8}') > 0.8;

We use the regular SELECT * query with a WHERE clause. Then, we use the pgvector_cosine function to specify that we want to retrieve rows where the cosine similarity between the embedding vector column and the given query vector {0.9, -0.3, 1.8} is greater than 0.8.

Pgvector index and limitations

While Pgvector is a great way to both store vector, and search vectors, it has some obvious disadvantages.

Pgvector has scalability issues when it comes to dealing with high-dimensional vectors. Storing vector data may also introduce additional storage and indexing overheads. It is also important to consider the space your vector data needs and how it could affect query performance.

Moreover, Pgvector only supports two types of indexed, HNSW and IVFFlat. This limitation affects the properties of the vectors stored as well as the size of the datasets you store. It also means that there is no default storage optimization with a Pgvector index.

Let's remember, Pgvector is a PostgreSQL extension that enables the storage and search of vector embeddings. However, it has limited abilities and performance. Fortunately, many dedicated vector embeddings databases like Milvus are available today that do a far better job due to improved indices or algorithms.

Dedicated vector databases

Now that we’ve explored Pgvector and its applications and disadvantages, let’s introduce the concept of dedicated vector databases.

Unlike Pgvector, a vector search plugin on top of a traditional database, dedicated vector databases like Milvus and Zilliz are purpose-built from the ground up for storing and querying millions or even billions of high-dimensional vector data with almost real-time responses. They leverage advanced indexing techniques to handle similarity searches efficiently, offer superior performance for similarity-based operations, handle large-scale vector data, and provide powerful APIs for AI and machine learning applications.

Introduction to vector databases like Milvus/Zilliz

A dedicated vector database like Milvus caters to a wide range of use cases, including image and video similarity retrieval, natural language processing, recommendation systems, and more. Its versatility makes it suitable for diverse AI-related projects. Let’s understand Milvus and how to leverage it in the cloud using Zilliz Cloud.

Milvus: an open-source vector database

Milvus is an open-source vector database that provides a robust solution for managing and querying billions of high-dimensional vectors. It offers numerous exciting features such as GPU index, Arm64, range search, upsert, and CDC, which ensure optimal performance and user experience for building AI and machine learning applications. Check out the latest Milvus 2.4 release blog for more information on these features.

Zilliz Cloud: a fully managed service enabling Milvus instances in the cloud

Zilliz Cloud operates as a cloud-based service that brings Milvus instances into the realm of software as a service (SaaS). It simplifies the deployment and management of Milvus databases by offering cloud infrastructure, scalability, and operational support. Zilliz ensures that developers can harness the capabilities of Milvus without the complexity of setting up and maintaining their infrastructure. It is just like using Amazon RDS for PostgreSQL in the cloud.

Zilliz Cloud offers a free tier, giving every developer equal access to this vector database service. The free tier offers up to two collections, each accommodating up to 500,000 vectors with 768 dimensions and even more on a smaller scale. This generous capacity allows for significant data handling capabilities without requiring infrastructure investments.

How to choose between Milvus and Zilliz

If you wish complete control over your database, you can opt for self-hosted Milvus instances. However, you must deploy and manage your infrastructure according to your needs and use cases.

On the other hand, if you prefer using a cloud-based vector database, you can use Zilliz Cloud. Zilliz lets you focus on building your application by leveraging Milvus in the cloud without worrying about maintaining the infrastructure.

Milvus and Zilliz empower developers with efficient vector data management, but they cater to diverse deployment preferences. Whether you lean toward self-hosted flexibility or cloud-based simplicity, the Milvus-Zilliz collaboration provides options aligned with your project’s demands.

Pgvector vs. Milvus and Zilliz

Now, let’s compare Pgvector with Milvus/Zilliz regarding ease of use, performance, and flexibility.

Ease of use

Pgvector seamlessly integrates with PostgreSQL, which is familiar to developers who already use the relational database. However, Milvus and Zilliz require additional setups to install their SDKs and APIs. The good news is that once you set up Milvus/Zilliz, creating a large-scale similarity search service takes less than a minute. In addition, simple and intuitive SDKs are available for various programming languages.

Apart from installation, there is a bit of a learning curve when using them. Pgvector seems easier to use because of its familiarity with PostgreSQL.

Performance and scalability analysis

One major limitation of Pgvector is its limited indexing capabilities. For complex similarity searches, Milvus outperforms Pgvector due to its optimized indexing mechanisms, as demonstrated in this benchmark.

Pgvector and Milvus are powerful vector search stacks engineered to handle large-scale vector data efficiently. However, Milvus/Zilliz is more scalable and can handle datasets with billions of vector embeddings.

Feature sets and flexibility

While Pgvector brings vector capabilities to PostgreSQL, Milvus/Zilliz is a purpose-built vector database with specialized features tailored for AI applications. They’re more feature-rich and can be more helpful for custom vector database use cases.

Pgvector Benchmarking vs. Milvus or Zilliz

VectorDBBench is an open-source benchmarking tool for vector databases. It compares mainstream vector databases and cloud services available in the market and provides unbiased benchmark results regarding queries per second (QPS), queries per dollar (QP$), and P99 latency.

For example, you can leverage VectorDBBench to benchmark Pgvector vs. Milvus or Zilliz. According to the benchmarking results, Milvus and Zilliz outperform Pgvector regarding QPS, speed and latency.

Note: This is a 1-100 score based on each system's performance in different cases according to a specific rule. A higher score denotes better performance.

Note: This is a >1 score based on each system's performance in different cases according to a specific rule. A lower score denotes better performance.

With VectorDBBench, you can quickly understand which database performs better in terms of various metrics. You can also determine which database best suits your specific needs.

PGvector vs other purpose built vector databases

While Pgvector offers advantages as a PostgreSQL extension for vector operations, it's important to understand how it compares to other purpose-built vector databases in the market. As vector search solutions continue to expand, developers have more options beyond Pgvector and Milvus.

Benefits of Choosing Pgvector:

Enhancing PostgreSQL's Native Capabilities: Pgvector extends PostgreSQL's functionality by enabling efficient storage, retrieval, and querying of vector data alongside traditional relational data. This makes it a versatile platform for handling diverse types of data, and complex queries.

Storage and Compute Separation: Pgvector allows for effective separation of storage and compute processes. By storing vectors separately from other application data, it ensures optimized performance and resource utilization, leading to streamlined query processing and scalability.

Challenges with choosing a postgres vector database:

Limitation in In-process running capabilities: Pgvector may not match the performance levels of dedicated vector databases designed for very large-scale applications requiring intricate query capabilities. It has some limitations in in-process running capabilities compared to specialized vector databases.

Comparison with Other Technologies: Pgvector excels in situations with a moderate number of vectors (below 100K) where vector data serves as an auxiliary aspect. However, it may not offer the same level of performance in extensive or highly specialized applications as some purpose-built vector databases known for scalability and complex query functionalities.

Don't get me wrong, Pgvector is a solid choice for projects that already use PostgreSQL and need to add vector capabilities without switching to a completely new database system. However, for very large-scale or specialized vector operations, purpose-built vector databases might offer better performance and more advanced features.

For a more detailed view of the comparisons of vector databases and Pgvector, check out this comparison page.

Conclusion

Pgvector opens up new possibilities for storing and querying vector data within PostgreSQL. If you’re already familiar with PostgreSQL and want to explore vector databases, Pgvector is an excellent starting point. However, for AI applications with millions or even billions of vector similarity searches, Pgvector performance may not be enough. Milvus and Zilliz offer specialized capabilities that optimize performance. Consider your project’s requirements and explore these vector databases to unlock the full potential of vector storage in your applications.

This post was written by Siddhant Varma. Siddhant is a full-stack JavaScript developer with expertise in front-end engineering. He’s worked with scaling multiple startups in India and has experience building products in the Ed-Tech and healthcare industries. Siddhant has a passion for teaching and a knack for writing. He's also taught programming to many graduates, helping them become better future developers.

Last updated: July 1, 2024

Siddhant Varma
Siddhant is a full-stack JavaScript developer with expertise in front-end engineering. He’s worked with scaling multiple startups in India and has experience building products in the Ed-Tech and healthcare industries. Siddhant has a passion for teaching and a knack for writing. He's also taught programming to many graduates, helping them become better future developers.