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 trends in technology about generative AI and large-language models, more efficient ways to store and query data have emerged. Traditional databases like MySQL and PostgreSQL were the ideal choice for developers for years. However, lately, a new type of database called a vector database has gained widespread popularity in the community.

With the pgvector extension, developers can efficiently store vector data in PostgreSQL, enabling advanced data management capabilities.

Vector databases are much different than traditional databases, especially regarding their use cases. So, in this roundup, we’ll explore Pgvector, an extension of PostgreSQL that allows you to use vector storage with a PostgreSQL database. We’ll also explore and understand the limitations of Pgvector, how it differs from specialized vector databases, and how and where you can use vector databases for your applications.

If you are a developer familiar with traditional databases and seeking new solutions, this guide will provide the knowledge you need to get started with Pgvector. It will also help you explore other dedicated vector databases as an alternative to Pgvector.

AI and machine learning are now widely used across various industries, including technology, IT, medicine, and automobiles. In these fields, data is represented as vectors containing numerical values that capture properties and features of unstructured data such as images or texts. Machine learning algorithms use these vectors as input to learn patterns and relationships within the data.

Pgvector is an open-source extension of PostgreSQL, a traditional open-source relational database. It supports storing and searching vectors from natural language processing or deep learning models on top of PostgreSQL. Or, you can simply see Pgvector as a vector database extension of PostgreSQL.

One of the best things about working with Pgvector is that it feels similar to working with a traditional SQL database like PostgreSQL. The syntax for vector operations, such as creating vector columns, creating a new table with vector columns, and getting the nearest L2 neighbors, is the same.

Whether you are working on AI applications, recommendation systems, or any other project that involves high-dimensional data, understanding the Pgvector database – and other vector databases – can broaden your horizons in database management. It also allows for efficient storage of vector values without requiring extensive vector storage and database knowledge.

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.

Introduction to Vector Databases

What is a Vector Database?

A vector database is a specialized type of database designed to store, manage, and query high-dimensional vector data efficiently. Unlike traditional databases, which are optimized for structured data like text and numbers, vector databases are tailored for handling complex data types used in machine learning and natural language processing. These databases excel in performing similarity searches, where the goal is to find vectors that are similar to a given query vector based on specific distance metrics.

Vector databases are crucial in applications that require the storage and retrieval of vector data, such as image recognition, recommendation systems, and semantic search. By leveraging advanced indexing techniques, vector databases can quickly and accurately retrieve relevant vectors, making them indispensable for developers working with AI and machine learning models.

Benefits of Vector Databases for Developers

Vector databases offer several significant advantages for developers:

Efficient Storage and Retrieval: They are optimized to store and retrieve high-dimensional vector data efficiently, ensuring that even large datasets can be managed effectively.
Fast and Accurate Similarity Searches: Vector databases support rapid similarity searches, allowing developers to find the most relevant vectors quickly. This is essential for applications like recommendation systems and image recognition.
Support for Various Distance Metrics and Algorithms: They provide flexibility in choosing different distance metrics (e.g., cosine similarity, Euclidean distance) and algorithms to suit specific application needs.
Scalability and Flexibility: Vector databases can handle large-scale datasets, making them suitable for applications that require the management of millions or even billions of vectors.
Integration with Popular Programming Languages and Frameworks: They offer seamless integration with various programming languages and frameworks, enabling developers to incorporate vector database functionalities into their existing workflows easily.

By leveraging these benefits, developers can build more efficient and scalable applications that require advanced vector data management and similarity search capabilities.

Understanding Vector Data

What is Vector Data?

Vector data is a type of data represented as a list of numbers, known as a vector. Each number in the vector corresponds to a specific feature or attribute of the data being represented. Vectors are versatile and can be used to represent a wide range of data types, including text, images, audio, and more. In the context of machine learning and natural language processing, vectors often represent the semantic meaning of words, phrases, or entire documents.

One of the key characteristics of vector data is its high dimensionality. Vectors can have thousands or even millions of dimensions, capturing intricate details and relationships within the data. This high dimensionality poses challenges for traditional databases, which are not optimized for storing and querying such complex data structures.

Vector databases address these challenges by providing efficient storage and retrieval mechanisms specifically designed for high-dimensional vector data. They enable developers to perform vector similarity searches, where the goal is to find vectors that are similar to a given query vector based on specific criteria. This capability is essential for applications like image recognition, recommendation systems, and natural language processing, where understanding and leveraging the relationships between data points is crucial.

By using vector databases, developers can efficiently store and query vector data, unlocking new possibilities for building advanced AI and machine learning applications.

Pgvector index and limitations

While Pgvector is a great way to store 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 one type of index called IVFFlat. This limitation affects the properties of the vectors as well as the size of the datasets you store. It also means that there is no default storage optimization with a Pgvector index.

In summary, 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 databases like Milvus are available today that do a far better job due to improved indices or algorithms.

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.

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 .

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.3 release blog for more information on these features.

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.

When upgrading to Postgres 12, ensure that the extension vector is properly managed to maintain compatibility and functionality.

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: A Comparison of Vector Databases

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

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 for Vector Similarity Search

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.

Milvus leverages approximate nearest neighbor search to enhance query performance, offering faster response times by sacrificing some accuracy compared to exact nearest neighbor search.

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 in Vector Embeddings

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.

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 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 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: January 23, 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.

Content

Start Free, Scale Easily

Try the fully-managed vector database built for your GenAI applications.

Try Zilliz Cloud for Free

Share this article

Keep Reading

Making Sense of the Vector Database Landscape
Compare top vector database vendors, run benchmarks, and choose the right solution for your AI-driven applications. Download the guide now!
Read Now
Elasticsearch Was Great, But Vector Databases Are the Future
Purpose-built vector databases outperform dual-system setups by unifying Sparse-BM25 and semantic search in a single, efficient implementation.
Read Now
How Metadata Lakes Empower Next-Gen AI/ML Applications
Metadata lakes are centralized repositories that store metadata from various sources, connecting data silos and addressing various challenges in RAG.
Read Now