~/tools / Embeddings and search (RAG) / pgvector SQL Builder

pgvector SQL Builder — Write pgvector SQL: table, HNSW/IVFFlat index and nearest-neighbor query

Write the SQL to store and search embeddings in PostgreSQL with pgvector: a table with a vector(n) or halfvec(n) column, an HNSW or IVFFlat index with the operator class that matches your distance, and a nearest-neighbor query with a readable score. It checks the limits given in the pgvector documentation and estimates how much space the vectors take. Nothing leaves your browser.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text NOT NULL,
  embedding vector(384) NOT NULL
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

PREPARE search_documents(vector(384)) AS
  SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity
  FROM documents
  ORDER BY embedding <=> $1
  LIMIT 5;

-- EXECUTE search_documents('[0.0123, -0.0456, ...]');

pgvector is an open-source extension that adds vector similarity search to PostgreSQL, so the embeddings you make with Ollama can be stored in the same database as the rest of your data. A few details are easy to get wrong. The column must have the same number of dimensions as your embedding model. Each distance has its own operator: <=> for cosine distance, <-> for L2 distance, <#> for the negative inner product and <+> for L1 distance. An approximate index is only used when it was built with the operator class for the same distance, such as vector_cosine_ops for <=>, and when the query sorts by the operator itself in ascending order with a LIMIT. This tool writes the three parts from one set of choices: CREATE EXTENSION and CREATE TABLE, CREATE INDEX, and a PREPARE statement that returns the nearest rows with a readable score (cosine similarity is 1 minus the cosine distance, and the inner product is <#> multiplied by -1). It applies the limits in the pgvector README and source code for version 0.8.7: up to 16,000 dimensions in a column, indexes up to 2,000 dimensions for vector and 4,000 for halfvec, no L1 operator class for IVFFlat, HNSW m from 2 to 100, and ef_construction from 4 to 1,000 and at least 2 × m. With HNSW, a search returns at most hnsw.ef_search rows (40 by default), so the tool warns when LIMIT is larger. For IVFFlat it fills in lists and probes from the README's starting points: rows / 1000 for up to 1M rows, sqrt(rows) above that, and sqrt(lists) for probes. The generated SQL was run on pgvector 0.8.7 with PostgreSQL 16 using real 384-dimension vectors from Ollama, and EXPLAIN showed the index being used.

The core logic of this tool is open source under the MIT License. github.com/seigo5555/toolkitbox-lib/src/pgvector-sql.ts