How do you store and search vector embeddings in PostgreSQL using pgvector?

Retrieval-Augmented Generation (RAG) relies heavily on vector databases to match semantically similar items. Rather than deploying specialized standalone vector engines, developers can turn existing relational databases into hybrid search engines using the pgvector extension.

Semantic Search in PostgreSQL

pgvector introduces a native vector data type along with specialized indexing algorithms like HNSW (Hierarchical Navigable Small World) to process million-dimensional similarity queries in milliseconds.

Creating Vector Tables and Querying Cosine Distance

The following SQL statements demonstrate table creation, HNSW index configuration, and semantic vector embeddings retrieval:

-- Enable the pgvector extension inside PostgreSQL database
CREATE EXTENSION IF NOT EXISTS vector;

-- Create documents table storing raw text alongside 384-dimensional vector embeddings
CREATE TABLE document_embeddings (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(384)
);

-- Build an HNSW index using cosine distance operators for rapid search queries
CREATE INDEX ON document_embeddings 
USING hnsw (embedding vector_cosine_ops);

-- Find top 3 most semantically relevant documents using vector distance (<=> operator)
SELECT id, content, 1 - (embedding <=> '[0.012, -0.043, 0.089, ...]'::vector) AS similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.012, -0.043, 0.089, ...]'::vector ASC
LIMIT 3;

Using PostgreSQL for cosine similarity search avoids data synchronization bottlenecks between distinct application datastores.