Free tools Windows power users keep installed
One-click scans. No signup required.
To add semantic search to PostgreSQL, generate an embedding for every document or text chunk and for each incoming query using the same model and compatible dimensions. Store each document’s vector with its text and identifiers, then use pgvector to rank rows by vector distance. Start with exact search; add an approximate index such as HNSW or IVFFlat only after measurements show that exact search is too slow for your workload.
What embeddings and pgvector do
An embedding is a vector—a list of floating-point numbers—produced from text by an embedding model. Distances between vectors act as a signal of relatedness: semantic search retrieves text whose vector is near the query vector. That signal is not a guarantee that the nearest result is correct, so relevance still needs evaluation against your actual content and queries.
As an Amazon Associate I earn from qualifying purchases.
PostgreSQL does not provide vector storage and nearest-neighbor operators by default. The pgvector extension adds a vector type, distance operators, and optional indexes so you can keep embeddings alongside relational data.
Choose a model and match its dimensions
Use the same embedding model and compatible settings for the stored text and incoming queries. If you change models or embedding dimensions, do not treat vectors from the old and new model spaces as interchangeable; plan to regenerate and migrate the collection.
#1 Best Overall
For the OpenAI API, the current embeddings guide documents default output widths of 1,536 dimensions for text-embedding-3-small and 3,072 for text-embedding-3-large. It also supports a dimensions parameter to request a reduced width. The guide lists a maximum input length of 8,192 tokens for both models. These are provider-specific specifications, not general properties of embedding models. See the OpenAI Embeddings API guide for current details.
Send the text and chosen model to the embeddings endpoint, then use the returned vector. Keep model name and dimension configuration in application or collection metadata so ingestion and query code cannot silently drift apart.
Enable pgvector and store vectors with their text
Enable the extension in the database:
CREATE EXTENSION vector;Then create a table with a stable document or chunk identifier, the source text, any metadata needed for filtering or provenance, and a vector column whose width matches the model configuration. For example, if the selected configuration returns 1,536 values:
Rank #2
CREATE TABLE documents (
id bigserial PRIMARY KEY,
document_id text NOT NULL,
content text NOT NULL,
embedding vector(1536) NOT NULL,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);The width in vector(1536) is only appropriate when the vectors being stored have that width. The OpenAI Cookbook Supabase example likewise pairs content text not null with embedding vector(1536) not null; adjust the example if your model or requested dimensions differ.
Store chunks rather than whole documents when that gives more useful retrieval granularity, and preserve the parent document ID so a result can be traced back to its source. Text, stable identifiers, and retrieval metadata can live in one row or in a reliably linked schema. pgvector permits an unconstrained vector column for mixed widths, but a single index can cover only rows with the same dimensions; use expression or partial indexes for distinct dimension or model groups when needed.
Generate query vectors and retrieve nearest rows
At search time, embed the user’s query using the matching model and dimensions. Then order candidates with the distance operator that fits the similarity behavior you intend to use:
<=>computes cosine distance.<->computes L2 (Euclidean) distance.<#>returns negative inner product. It is negative because PostgreSQL index scans use ascending operator order.
A simple cosine-distance query looks like this, where $1 is the query vector and $2 is the result limit:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchSELECT document_id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT $2;Use the same operator in both the ordering expression and the matching index definition. Choose the metric based on your model’s intended similarity behavior and vector normalization; do not assume one distance measure is universally best. pgvector recommends inner product for normalized vectors as a performance choice.
Start with exact search, then assess indexes
pgvector performs exact nearest-neighbor search by default, which provides perfect recall. It is the right baseline for correctness and may be sufficient for a given dataset and latency target. Measure query latency and result quality with representative data and queries before adding approximation.
HNSW and IVFFlat trade some recall for speed and can return different results from exact search. Compare them against exact results, including index build time, memory or storage use, write and update costs, and behavior under your real metadata filters.
HNSW
HNSW is a graph-based approximate index with a favorable speed/recall tradeoff, but it takes longer to build and uses more memory. It can be created before loading data because it does not require a training step. The m setting controls graph connections, while ef_construction controls the candidate list during construction. Increasing construction effort can improve recall at the cost of build time and insert cost. At query time, hnsw.ef_search sets the candidate list size.
CREATE INDEX documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops);This example uses cosine distance; select the operator class matching the metric used by your query. The Google Cloud SQL guide documents HNSW parameters and defaults for its Cloud SQL context. Do not assume those defaults apply to every pgvector version or managed PostgreSQL service; check the version and service you deploy.
IVFFlat
IVFFlat partitions vectors into lists and needs a training step, so pgvector’s guidance is to create this index after loading data. Its lists setting controls the index structure, while ivfflat.probes controls how many lists are searched; more probes generally use more work and improve recall. Measure the tradeoff on your data rather than copying a universal setting.
Validate approximate results
Use the same representative queries to compare approximate results with exact search. Track recall as well as latency, and repeat the comparison after changing index parameters, data volume, or query filters. An index that is fast on unfiltered queries may not return enough useful rows when application filters are applied.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Test filtered retrieval separately
With approximate indexes, metadata filters may be applied after the index scan. A selective filter can therefore leave too few rows in the result even when nearby matching vectors exist. pgvector documents iterative index scans as one mitigation. Benchmark result counts and recall under the filters your application actually uses, not just on unfiltered searches.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Combine semantic and full-text search when literal matches matter
Vector similarity can miss an exact identifier, quoted phrase, or rare proper noun. PostgreSQL full-text search uses tsvector and tsquery, with GIN and GiST index support; PostgreSQL identifies GIN as the preferred full-text index type in its PostgreSQL 16 full-text index documentation.
For content where both conceptual relevance and exact terms matter, combine vector retrieval with full-text retrieval, then merge rankings with reciprocal rank fusion or rerank candidates with a cross-encoder. This adds complexity and, for a cross-encoder, reranking work; choose it when literal-term coverage or ranking quality justifies that cost.
Quick Recap
Make ingestion and deployment reliable
- Run extension and schema changes through database migrations rather than ad hoc production edits.
- Keep API keys out of source code. Load them from environment variables or a secret-management system.
- If a Supabase-generated REST API exposes the table, configure row-level security (RLS) and policies deliberately. The Cookbook example enables RLS to prevent unauthorized access through the generated API.
- When the embedding model or dimensions change, plan how to regenerate vectors and switch queries to the corresponding collection rather than mixing unrelated vector spaces.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
