Hybrid Search with Postgres and Ecto
Hybrid Search with Postgres and Ecto
Loosely based on:
If available in Hex, the package can be installed
by adding vecto
to your list of dependencies in mix.exs
:
def deps do
[
{:vecto, "~> 0.1.2"}
]
end
In your migrations (assuming you're using Ecto migrations):
# set up pgvector
execute "CREATE EXTENSION IF NOT EXISTS vector"
# on your table
## vector column
add :embedding, :vector, size: 384 # depends on your embedding model
## generated full text column, could be combination of several text columns
execute("""
ALTER TABLE post
ADD COLUMN content tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
""")
execute """
CREATE INDEX content_tsvector_idx ON post USING GIN (content)
"""
execute """
CREATE INDEX embedding_index ON post USING hnsw (embedding vector_cosine_ops);
"""
HNSW index has limitation of 2000 dimensions so in case you use OpenAI's API embeddings you need to reduce the dimensionality of the embeddings. You can do it in API call, for example:
OpenAI.embeddings(model: "text-embedding-3-large", input: text, dimensions: 2_000)
Another important information is that HNSW index limit results with hnsw.ef_search
parameter. Default value can be low for some cases, so you can increase it in the query:
In your migrations:
# for current transaction (not persistent)
execute """
SET hnsw.ef_search = 100;
"""
# for session (not persistent)
execute """
SET LOCAL hnsw.ef_search = 100;
"""
# alter server configuration (you have to have permissions)
execute """
ALTER SYSTEM SET hnsw.ef_search = 100;
"""
# for database (persistent)
execute """
ALTER DATABASE your_database_name SET hnsw.ef_search = 100;
"""
Blog
|> Vecto.hybrid_search(Pgvector.new([0.11, 0.03, -0.02, ...]), "what is a cat")
|> where([b], b.user_id == ^23)
|> Repo.all()
You can also use the keyword_search and semantic_search functions as stand-alone queries
Docs can be found at https://hexdocs.pm/vecto