Integration guide
PostgreSQL / pgvector Integration
Postgres plus pgvector gives AI apps a familiar SQL database with embedding storage and similarity search.
Recommended path
PostgreSQL / pgvector local AI quick start
- 1. Enable pgvector and create the matching columnEnable the vector extension, choose the embedding model first, and create a vector or halfvec column with the model's exact output dimension.
- 2. Generate and insert test embeddingsEmbed a small known corpus with the same local model you will use for queries, insert finite vectors with their metadata, and verify the chosen distance operator by inspection.
- 3. Add an index only after correctness is clearCreate HNSW or IVFFlat within the type's indexed-dimension limit, compare approximate results with exact search, and then feed retrieved text to a separate generation model.
Best for
Teams that want to keep RAG data, metadata, and application records in Postgres.
Model support
pgvector is storage and search, not inference: it adds vector, halfvec, bit and sparsevec types plus distance operators for L2, inner product, cosine, L1, Hamming and Jaccard, and two index types, HNSW and IVFFlat. The embeddings come from whatever model you run.
Choose models by role
Index and query embeddings
Multilingual E5 Large
Use the same embedding model and preprocessing for stored rows and queries. The database can compare vectors but cannot repair a model or dimension mismatch.
Open the model profile →Generate from retrieved context
Instruction-following chat model
Use a separate generation model after retrieval. pgvector never interprets the returned text or produces the final response.
How to use this integration
- Keep embeddings in the same database as your application data, inside one transaction and one backup
- Choose a distance operator that matches how your embedding model was trained
- Serve retrieval for a fully local RAG stack with no external vector service
- Tune the recall and speed trade-off explicitly through index parameters
Connecting a local model
Generate embeddings locally, for example with Ollama's /api/embed or a sentence-transformers model, then insert them into a vector column and add an HNSW or IVFFlat index sized to your dimension count.
Tradeoffs
The trap is that the storable and indexable dimension limits are different numbers. vector and halfvec store up to 16,000 dimensions, while HNSW and IVFFlat index vector up to 2,000 dimensions and halfvec up to 4,000. A larger embedding can therefore be inserted and still be ineligible for the intended index. NULL vectors are not indexed, nor are zero vectors under cosine distance, all elements must be finite, and indexes build far faster when the graph fits in maintenance_work_mem. Both index types are approximate and trade recall for speed.