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
vectordata 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-vssprovides vector search for lightweight applications - Elasticsearch: Supports dense vectors natively with
dense_vectorfields
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.