Search documentation

Search for a topic, a control, or an event type.

    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 the embeddings table and search function
    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.

    Index the small model’s vectors
    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.

    Store two vectors in one statement
    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.embedding
    Embed and store notes from a code job
    import 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,
            )

    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.

    Find the five notes closest to a question
    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.
    Step 2: index the large model’s vectors
    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';
    Step 3: search the large model
    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
    $$;
    Step 4: delete the small model’s vectors
    DELETE FROM data.note_embeddings WHERE model = 'text-embedding-3-small'
    Step 5: drop the small model’s index
    DROP INDEX data.note_embeddings_small_hnsw;