Docs / Databases / Semantic search with embeddings
Semantic search with embeddings
Turn text into vectors from an app or a code job, store each vector with its model, search by meaning and change models while search keeps working.
Create embeddings
An embedding is a vector, a list of numbers that represents what a text means. Texts with similar meanings get vectors that lie close together, so a search can find notes that answer a question without sharing its words. OAIZ creates embeddings with OpenAI text-embedding-3-small, which returns 1536 numbers by default, or text-embedding-3-large, which returns 3072. Set dimensions to request fewer. Each call turns a list of texts into one vector per text, in input order. Calls run on the account an organisation administrator chooses for Embeddings in Feature Model Mapping, under Models: OAIZ’s account or an organisation OpenAI key. The call still names its own model.
- Texts per call
- Up to 2,048. Each text must contain more than whitespace and hold at most 8,192 tokens.
- Tokens per call
- At most 300,000 across all texts.
- Values per call
- Texts multiplied by dimensions, at most 1,000,000. At 1536 dimensions, send at most 651 texts per call.
Vectors are comparable only when one model produced them at the same dimensions. Store the exact model the response names beside each vector, and search with query vectors from that model.
Store vectors by model
This Owner migration installs pgvector and creates note_embeddings beside an existing notes table with id uuid PRIMARY KEY and title text. It also creates the search_notes function. The embedding column has no fixed length and the model column names each row’s model, so vectors from two models can share the table while you change models. search_notes compares the stored vectors of one model at 1536 dimensions.
CREATE EXTENSION vector;
CREATE TABLE data.note_embeddings (
note_id uuid NOT NULL REFERENCES data.notes (id) ON DELETE CASCADE,
model text NOT NULL,
embedding vector NOT NULL,
PRIMARY KEY (note_id, model)
);
CREATE FUNCTION data.search_notes(query_embedding vector(1536), match_count integer)
RETURNS TABLE (id uuid, title text, distance double precision)
LANGUAGE sql STABLE
SET search_path = data, extensions, pg_temp AS $$
SELECT n.id, n.title, e.embedding::vector(1536) <=> query_embedding
FROM data.note_embeddings AS e
JOIN data.notes AS n ON n.id = e.note_id
WHERE e.model = 'text-embedding-3-small'
ORDER BY e.embedding::vector(1536) <=> query_embedding
LIMIT match_count
$$;Give each model its own named HNSW index on the column cast to its dimensions, limited to that model’s rows. Build it with CREATE INDEX CONCURRENTLY in a migration of its own. An HNSW index on vector holds at most 2,000 dimensions, and one on halfvec at most 4,000. text-embedding-3-large returns 3072 by default, so request 2,000 or fewer, such as 1536, or index a halfvec cast instead.
CREATE INDEX CONCURRENTLY note_embeddings_small_hnsw ON data.note_embeddings
USING hnsw ((embedding::vector(1536)) vector_cosine_ops)
WHERE model = 'text-embedding-3-small';A code job embeds texts with LLMClient.embed and writes the vectors with storage_execute, which needs the database in the job’s storageTools. Pass each vector as JSON text and cast its placeholder with ::vector. This statement stores two vectors, or replaces them for the same note and model. Bind $1 through $3 to the first note’s ID, model and vector, then $4 through $6 to the second. Write a modest batch per statement, such as 100 vectors, to stay well within the 8 MiB parameter limit.
INSERT INTO data.note_embeddings (note_id, model, embedding)
VALUES ($1, $2, $3::vector), ($4, $5, $6::vector)
ON CONFLICT (note_id, model) DO UPDATE SET embedding = EXCLUDED.embeddingimport json
from oaiz.llm import LLMClient
from oaiz.tools.native import storage_execute
DATABASE_ID = "3f2a9c1e-7b4d-4e8a-9c6f-1a2b3c4d5e6f"
def store_embeddings(notes: list[dict], model: str = "text-embedding-3-small") -> None:
client = LLMClient()
for start in range(0, len(notes), 100):
batch = notes[start : start + 100]
result = client.embed(model=model, input=[note["title"] for note in batch], dimensions=1536)
values, params = [], []
for note, vector in zip(batch, result.embeddings):
n = len(params)
values.append(f"(${n + 1}, ${n + 2}, ${n + 3}::vector)")
params += [note["id"], result.model, json.dumps(vector)]
storage_execute(
DATABASE_ID,
"INSERT INTO data.note_embeddings (note_id, model, embedding) VALUES "
+ ", ".join(values)
+ " ON CONFLICT (note_id, model) DO UPDATE SET embedding = EXCLUDED.embedding",
params,
)Search by meaning
Embed the search text with the model search_notes compares, then call the function with rpc(). It returns the closest notes first, with their cosine distance. The function only reads, so View access is enough. This example uses the client from Query from TypeScript.
const { data: query, error: embedError } = await db.embeddings.create({
model: 'text-embedding-3-small',
input: ['How do I reset a password'],
})
if (embedError) throw new Error(embedError.message)
const { data: notes, error } = await db.rpc('search_notes', {
query_embedding: JSON.stringify(query.embeddings[0]),
match_count: 5,
})
if (error) throw new Error(error.message)
console.log(notes)Change the embedding model
Move to a new model in five steps. Search answers from the old model until step 3 and from the new model after it. These steps move from text-embedding-3-small to text-embedding-3-large at 1536 dimensions.
- 01Store the new vectors
- First store each new or changed note’s vector with both models, and keep doing so until step 3. Then embed every note again with the new model and store the rows with the same statement, bound to the new model name. Rows of both models now share the table, and no note drops out of search after the switch.
- 02Index the new model
- Build the new model’s index in a migration of its own.
- 03Switch the search
- Replace search_notes so it compares the new model’s rows. At the same time, release the app change that embeds search text, and new or changed notes, with only the new model and dimensions.
- 04Delete the old vectors
- Delete the old model’s rows in an execute call with Edit access.
- 05Drop the old index
- Drop the old index with DROP INDEX in a migration. DROP INDEX CONCURRENTLY is refused.
CREATE INDEX CONCURRENTLY note_embeddings_large_hnsw ON data.note_embeddings
USING hnsw ((embedding::vector(1536)) vector_cosine_ops)
WHERE model = 'text-embedding-3-large';CREATE OR REPLACE FUNCTION data.search_notes(query_embedding vector(1536), match_count integer)
RETURNS TABLE (id uuid, title text, distance double precision)
LANGUAGE sql STABLE
SET search_path = data, extensions, pg_temp AS $$
SELECT n.id, n.title, e.embedding::vector(1536) <=> query_embedding
FROM data.note_embeddings AS e
JOIN data.notes AS n ON n.id = e.note_id
WHERE e.model = 'text-embedding-3-large'
ORDER BY e.embedding::vector(1536) <=> query_embedding
LIMIT match_count
$$;DELETE FROM data.note_embeddings WHERE model = 'text-embedding-3-small'DROP INDEX data.note_embeddings_small_hnsw;