Course contentsShow
← Machine Learning and Deep Learning
Lesson 1967 of 3,53842. Retrieval-Augmented Generation: FoundationsFree lesson

Embedding Traditional Databases: pgvector and Extensions

Adding vector search capabilities to PostgreSQL and other relational databases.

Embedding Traditional Databases: pgvector and Extensions

What you'll learn: How to add vector search capabilities to PostgreSQL and other relational databases without migrating to specialized vector databases.

Why Extend Traditional Databases?

Not every organization wants to adopt a new database infrastructure for RAG. If you already have PostgreSQL, MySQL, or another relational database managing your data, you can add vector search capabilities through extensions rather than migrating everything to a dedicated vector database.

This approach lets you keep your relational data (users, transactions, metadata) alongside vector embeddings in one system, simplifying architecture and queries.

pgvector: Vectors in PostgreSQL

pgvector is the most popular PostgreSQL extension for vector operations. It adds:

  • A new vector data type for storing embeddings
  • Similarity search functions (cosine distance, L2 distance, inner product)
  • Index types like IVFFlat and HNSW for approximate nearest neighbor search

You can store document embeddings in a column alongside text, timestamps, and foreign keys, then run vector similarity searches using SQL:

SELECT id, content, embedding <=> query_vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

The <=> operator performs cosine distance similarity search.

Other Database Extensions

  • MySQL: No native vector extension yet, but third-party solutions exist
  • SQLite: sqlite-vss provides vector search for lightweight applications
  • Elasticsearch: Supports dense vectors natively with dense_vector fields

Trade-offs

Traditional databases with vector extensions are convenient but may not match specialized vector databases in raw performance at massive scale. They excel when you need transactional consistency, complex joins with metadata, or want to avoid infrastructure sprawl.

Key Takeaway: Extensions like pgvector let you add vector search to existing relational databases, combining RAG capabilities with traditional queries in a single system—ideal for smaller deployments or when operational simplicity matters more than maximum vector search performance.