How to Use pgvector for Similarity Search on Heroku Postgres

Summary

Heroku Postgres now supports pgvector on all Standard-tier or higher databases running Postgres 15, adding vector storage and functions for similarity search and machine-learning applications. Older Postgres versions require an upgrade to Postgres 15. The workflow generates embeddings, adds a VECTOR(100) column, and stores values using SQL or Python with psycopg, pgvector-python, and Wikipedia2Vec’s pretrained 100-dimensional model. Queries can rank vectors with Euclidean, cosine, or inner-product distance; the example uses <-> to return five animals nearest to shark, producing crocodile, dolphin, whale, turtle, and alligator. Results depend on the embedding model and corpus, while indexes introduce approximate nearest-neighbor search; pgvector supports IVFFlat and HNSW, with IVFFlat requiring table data for better recall.

Takeaways

1. 1

   The example stores Wikipedia2Vec word embeddings in a VECTOR(100) column and uses Python with psycopg and pgvector-python to insert generated vectors.

2. 2

   pgvector provides Euclidean distance (<->), cosine distance (<=>), and inner product (<#>) functions; the appropriate choice depends on the data and use case.

3. 3

   Adding a vector index changes searches from exact to approximate nearest-neighbor search, which can affect results; IVFFlat and HNSW are the supported index types.