Investigate search with dense and sparse embedding

[svenseeberg]

Investigate if OpenSearch is an option for combined sparse and dense vector search. txtai is another option.

Alternatively, we can use PostgresSQL with pg_vector. A very simple SQL setup can look like this:

CREATE DATABASE document_embeddings;
\c document_embeddings
CREATE TABLE document_chunks (id SERIAL PRIMARY KEY, title TEXT NOT NULL, content TEXT NOT NULL, url VARCHAR(512), embedding vector(384), sparse_embedding vector(1024));
CREATE TABLE documents (id SERIAL PRIMARY KEY, title TEXT NOT NULL, content TEXT NOT NULL, url VARCHAR(512));
CREATE INDEX ON document_chunks USING hnsw (embedding vector_l2_ops);
CREATE INDEX ON document_chunks USING hnsw (sparse_embedding vector_l2_ops);
CREATE INDEX idx_documents_url_btree ON documents (url);
CREATE INDEX idx_chunks_url_btree ON document_chunks (url);
CREATE USER document_embeddings WITH ENCRYPTED PASSWORD 'CHANGEME';
GRANT ALL PRIVILEGES ON DATABASE document_embeddings TO document_embeddings;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO document_embeddings;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO document_embeddings;

And a retrieval can then be done with the following query:

SELECT *, ({a} * distance + (1 - {a}) * sparse_distance) AS total_distance FROM (SELECT d.url, d.title, d.content, MIN(c.embedding <-> '{embedding}') AS distance, MIN(c.sparse_embedding <-> '{sparse_vector}') AS sparse_distance FROM document_chunks c LEFT JOIN documents d ON c.url=d.url GROUP BY d.url, d.title, d.content) ORDER BY total_distance ASC LIMIT 10;"