pgvector

// Overview

pgvector is an open-source extension that adds a vector data type and similarity search to PostgreSQL. It lets you store embeddings in the same database as the rest of your application data and query them with regular SQL, so you don't need to run a separate vector store alongside Postgres.

It is aimed at developers who already use Postgres and want to add semantic search, recommendations, or retrieval for AI applications. Because vectors live in normal tables, you keep JOINs, transactions, ACID guarantees, point-in-time recovery, and the rest of Postgres on top of your similarity search.

As a vector database option, pgvector supports both exact and approximate nearest neighbor search, single-precision, half-precision, binary, and sparse vectors, and several distance functions including L2, inner product, cosine, L1, Hamming, and Jaccard. It works from any language that has a Postgres client.

// What it does

• Adds a native vector column type to Postgres tables, alongside halfvec, bit (binary), and sparsevec types
• Exact nearest neighbor search for perfect recall, plus approximate search via HNSW and IVFFlat indexes
• Distance operators for L2 (<->), inner product (<#>), cosine (<=>), L1 (<+>), Hamming (<~>), and Jaccard (<%>)
• Full Postgres feature set: ACID compliance, JOINs, point-in-time recovery, and standard SQL queries
• Works with any language that has a Postgres client; supports Postgres 13 and later
• Available via Docker, Homebrew, APT, Yum, conda-forge, and many hosted Postgres providers

// Getting started

Install the extension into a Postgres 13+ instance, enable it once per database, then create a vector column and run a nearest-neighbor query.

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

CREATE EXTENSION vector;

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

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

Commands and code are distilled from the project's own documentation — always check the official repo for the latest.

// When to use it

• Add semantic search or retrieval (RAG) to an app that already runs on Postgres, without standing up a separate vector database
• Store text or image embeddings next to their source rows so you can filter, JOIN, and rank in a single SQL query
• Build recommendation or similar-item features by finding the nearest vectors to a given row
• Scale to large vector sets using HNSW or IVFFlat indexes for fast approximate nearest neighbor search

// How pgvector compares

pgvector alongside other open-source vector databases tools AI/TLDR tracks, ranked by GitHub stars.