Introduction

Modern AI applications struggle with “memory”—the ability to retrieve relevant context from massive datasets without retraining a model. This is where pgvector comes in, an open-source extension for PostgreSQL that enables efficient vector similarity search. By allowing developers to store embeddings alongside relational data, pgvector eliminates the need for a separate, specialized vector database for most use cases, effectively turning PostgreSQL into a powerful tool for AI memory.

What Is pgvector?

pgvector is a PostgreSQL extension that adds support for storing, indexing, and querying vector embeddings natively within the database. It provides a dedicated vector data type, specialized operators for distance calculations, and indexing strategies to handle high-dimensional data at scale.

Maintained as an open-source project under the MIT license, pgvector is designed to be lightweight and compatible with any programming language that has a PostgreSQL client. It leverages the existing PostgreSQL ecosystem, meaning users benefit from ACID compliance, point-in-time recovery, and the full power of SQL JOINs while performing semantic searches.

Why pgvector Matters

Before pgvector, developers building Retrieval-Augmented Generation (RAG) or recommendation systems had to manage two separate data stores: a relational database for application state and a specialized vector database (like Pinecone or Milvus) for embeddings. This “split-brain” architecture introduced significant operational overhead, data synchronization issues, and increased costs.

pgvector solves this by unifying the data stack. By bringing vector search into the same system that holds application data, it simplifies the infrastructure and ensures that vector queries can be combined with traditional metadata filtering in a single SQL statement. This unification is critical for production AI systems where context retrieval must be fast, reliable, and consistent with the rest of the la data.

With the release of version 0.8.0 and beyond, pgvector has evolved from a simple tool for small datasets to a production-ready solution capable of handling millions of vectors with high recall and sub-millisecond latency, making it the default choice for teams already using PostgreSQL.

Key Features

• Vector Storage: Introduces a native vector data type to store high-dimensional embeddings as columns in PostgreSQL tables.
• Exact Nearest Neighbor Search: Provides operators for calculating exact distances between vectors using L2 distance, inner product, and cosine distance.
• Approximate Nearest Neighbor (ANN) Search: Implements HNSW (Hierarchical Navigable Small World) and IVFFlat indexing to provide fast, approximate searches over millions of rows.
• Multiple Distance Metrics: Supports L2 distance, inner product, cosine distance, L1 distance, Hamming distance, and Jaccard distance to suit different embedding models.
• Vector Quantization: Offers half-precision, binary, and sparse vectors to reduce memory footprint and increase search speed for extremely large datasets.
• Iterative Index Scans: Prevents “overfiltering” by continuing to search the index until a configurable threshold is met, ensuring higher recall when combined with WHERE clauses.
• ACID Compliance: Inherits all PostgreSQL’s reliability features, including transactions, point-in-time recovery, and full backup capabilities.
• Language Agnostic: Works with any language that can connect to PostgreSQL, with dedicated support libraries for Go, Python, and R.

How pgvector Compares

When choosing between pgvector and a dedicated vector database, the primary tradeoff is between simplicity and extreme scale. pgvector is the ideal choice for the vast majority of AI applications because it removes the need to manage a new piece of infrastructure. For teams already on PostgreSQL, the cost of adding pgvector is nearly zero in terms of operational overhead.

However, if your dataset grows to billions of vectors or requires specialized, ultra-high-throughput search capabilities that exceed the limits of a single PostgreSQL instance, a dedicated solution like Milvus or Pinecone becomes necessary. For most RAG applications, pgvector provides more than enough performance while offering the massive advantage of keeping your metadata and embeddings in one place.

Getting Started: Installation

pgvector can be installed via several methods depending on your operating system and deployment environment.

Linux and macOS

You can compile and install the extension directly from the source. This supports PostgreSQL 13 and above.

cd /tmp
git clone --branch v0.8.5 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # may need sudo

Windows

Ensure C++ support in Visual Studio is installed and use the x64 Native Tools Command Prompt for VS as administrator.

set "PGROOT=C:\Program Files\PostgreSQL\18"
cd %TEMP%
git clone --branch v0.8.5 https://github.com/pgvector/pgvector.git
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

Docker

The fastest way to get started is using the official pgvector Docker image, which comes with the extension pre-installed.

docker pull pgvector/pgvector:pg17
docker run --name pgvector-db -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d pgvector/pgvector:pg17

How to Use pgvector

Once the extension is installed on your server, you must enable it within each specific database where you intend to use it.

CREATE EXTENSION vector;

After enabling the extension, you can create a table with a vector column. The dimension of the vector must match the output of your embedding model (e.g., 1536 for OpenAI’s text-embedding-3-small).

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

Inserting data is as simple as passing a vector as a string representation of an array.

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

To perform a similarity search, use the distance operators. For example, the <-> operator calculates L2 distance (Euclidean distance), and the <=> operator calculates cosine distance.

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

Code Examples

Below are examples of how to implement vector search in a real-world scenario, such as a semantic search engine for documents.

Example 1: Basic Semantic Search

This example shows how to create a table for documents and retrieve the most semantically similar items based on a query vector.

CREATE TABLE documents (id SERIAL PRIMARY KEY, content TEXT, embedding vector(1536));

-- Find the 5 most similar documents to a query vector
SELECT content FROM documents 
ORDER BY embedding <=> '[0.1, 0.2, ...]' 
LIMIT 5;

The <=> operator is used here because cosine similarity is the standard for most text embeddings.

Example 2: Hybrid Search with Metadata Filtering

This is where pgvector shines. You can combine vector search with traditional SQL filters in a single query.

SELECT content FROM documents 
WHERE category = 'technical' AND created_at > '2024-01-01'
ORDER BY embedding <-> '[0.1, 0.2, ...]' 
LIMIT 5;

In this query, PostgreSQL will use a B-tree index on category and created_at to filter the rows first, then perform the vector similarity search on the remaining subset.

Example 3: Adding an Approximate Index for Scale

For datasets larger than a few thousand rows, linear scans are too slow. You can add an HNSW index to achieve sub-millisecond search speeds.

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- The query remains the same, but PostgreSQL will now use the HNSW index
SELECT content FROM documents 
ORDER BY embedding <=> '[0.1, 0.2, ...]' 
LIMIT 5;

HNSW is generally preferred over IVFFlat for better recall and faster query performance at scale.

Real-World Use Cases

pgvector is widely used to power the “memory” of AI agents and LLM-powered applications.

• Retrieval-Augmented Generation (RAG): Developers use pgvector to store document embeddings. When a user asks a question, the system retrieves the most relevant document chunks from pgvector and feeds them into the LLM prompt as context, reducing hallucinations.
• Recommendation Systems: By storing embeddings of users and products, a system can find the “nearest neighbors” of a user’s profile vector to suggest products that are semantically similar to their preferences.
• Anomaly Detection: In security or financial applications, pgvector can be used to store embeddings of “normal” behavior. Any new data point that is significantly distant from all existing clusters in the vector space is flagged as an anomaly.
• Semantic Search: Replacing traditional keyword search (BM25) with vector search allows users to find documents by meaning. For example, a search for “how to fix a leak” might return documents about “plumbing repair” even if the exact words don’t match.

Contributing to pgvector

pgvector is an open-source project and encourages contributions from the community. You can help improve the project by reporting bugs, submitting pull requests, or improving the documentation.

The project follows standard GitHub flow: fork the repository, create a feature branch, and submit a pull request. There is no separate CONTRIBUTING.md file, but the project maintainers welcome any improvements to the indexing algorithms or performance optimizations.

Community and Support

The primary hub for pgvector activity is the GitHub repository, where issues and discussions are used for troubleshooting and bug reports. Because it is a PostgreSQL extension, it is also frequently discussed in the PostgreSQL community forums and mailing lists.

Since pgvector has become a standard for vector search in Postgres, it is supported by almost every major hosted PostgreSQL provider, including AWS Aurora, Azure Database for PostgreSQL, and Supabase, which provides extensive documentation on using pgvector in their cloud environment.

Conclusion

pgvector is the most pragmatic choice for developers who want to integrate AI capabilities into their existing PostgreSQL infrastructure. By unifying relational data and vector embeddings in a single store, it removes the operational complexity of managing a separate vector database.

While dedicated vector databases may be required for extreme scale (billions of vectors), pgvector is more than sufficient for the vast majority of production AI applications. It is a powerful, reliable, and cost-effective way to build semantic search, RAG systems, and recommendation engines.

Star the repo, try the quickstart, and start building your AI memory today.

Resources

• pgvector GitHub Repository
• pgvector Changelog
• Supabase pgvector Guide