Skip to main content

Using Vector Search

Vector search is a foundational concept to systems that use machine learning and artificial intelligence. In vector search, an embedding model transforms unstructured data, such as text, into a piece of structured data, called a vector. Users can then perform operations on these vectors, rather than the unstructured data itself, to process input and generate a response.

InterSystems IRIS SQL supports the storage of vectors in the compressed and performant VECTOR type, enabling you to efficiently store vectorized data as a part of your traditional relational schemas.

Vectors and Embeddings

Vectors can be used to represent the semantic meaning of language in embeddings. These embeddings are determined by an embedding model, which is an machine learning model that maps words to a geometric space with high dimensionality. Modern embedding vectors typically range between hundreds and thousands of dimensions. Words that share similar semantic meaning occupy nearby positions in that space, while words with disparate semantic meaning occupy positions distant from each other. These spatial positions allow an application to algorithmically determine the similarity between two words, or even sentences, by performing operations on their embedding vectors.

In vector search, a user can compare an input vector with the vectors stored in the database using operations that determine the similarity between two vectors, such as the dot product. When the vectors that the search is performed on represent embeddings, a vector search enables you to algorithmically determine which pieces of text are semantically most similar to an input. As a result, vector search is a great fit for tasks that involve information retrieval.

Storing Vectors

While many platforms support vectors through data structures like lists, InterSystems IRIS® data platform supports a dedicated VECTOR type that leverages chip-level optimizations to efficiently perform operations often performed on vectors, including those that operate on multiple elements at once. There are three numeric vector types: decimal (the most precise), double, and integer (the least precise). As VECTOR is a standard SQL datatype, you can store vectors alongside other data in a relational table, converting a SQL database transparently into a hybrid vector database.

Vector data can be added to a table with INSERT statements. Additionally, you can create objects through ObjectScript with a property that is of the %Library.Vector type and store a vector in that property.

In many cases, vectors saved in a table in InterSystems SQL represent embeddings, which cannot be easily represented or added into a table by hand. Since most applications deal with a high volume of embeddings vectors, it is important to programmatically load these embeddings into a table. The following sections provide guidance on how to translate text into an embedding and load the embedding into a table as a VECTOR.

Translating Text to Embeddings

Before you can store embeddings as vectors in InterSystems IRIS, you first need to create them from a text source. In general, you can transform a piece of text into an embedding in four steps.

  1. Import a package to use to turn your text into a series of embeddings.

  2. Pre-process your text to best fit your chosen embedding model’s input specifications.

  3. Instantiate the model and convert your text to the embeddings, using your chosen package’s workflow.

  4. Convert each individual embedding to a string. This step is necessary to convert an embedding to a VECTOR at a later point.

With Embedded Python, you can execute Python code that converts text to embeddings (using whichever package you like) alongside ObjectScript code that can insert those embeddings directly into your database. For information on importing a Python package into InterSystems IRIS, see Install and Import Python Packages.

Example: Create Embeddings with Embedded Python

The following example takes an input list of sentences, passed as a built-in Python list, converts them to embeddings using the sentence_transformers package, and returns a list of the embeddings for later insertion into a table. See Inserting VECTOR-typed Data for more information on inserting the embeddings. This example assumes that the input sentences have already been pre-processed to match the embedding model’s specifications.

ClassMethod GetEmbeddingPy(sentences) [ Language = python ]
  import json
  // import the package
  import sentence_transformers

  // create the model and form the embeddings
  model = sentence_transformers.SentenceTransformer('all-MiniLM-L6-v2')
  embeddings = model.encode(sentences)

  // convert the embeddings to a string
  embeddings_list = [str(embedding.tolist()) for embedding in embeddings]
  return embeddings_list

Inserting VECTOR-typed Data

Once you have a list of strings that represent embeddings, you can insert them into your table as VECTORs either with an INSERT statement or by creating an object and storing the embedding as a property of the object. The following example demonstrates how to insert data with an INSERT.

In this example, the embedding is stored in a table alongside a unique identifier. When the embedding vectors are inserted in these examples, the assumption is that the source text is stored in another table with the same unique identifier. The shared value of the unique identifiers allows you to perform a JOIN, linking an embedding vector with the text it represents. If you choose to store the embeddings and text in the same table, the unique identifier is not necessary.


For each embedding, execute an INSERT statement that adds the embedding to the desired table. Use TO_VECTOR to convert the string representation of the embedding to a VECTOR.

In the following command, one embedding is inserted into a table called Sample.Embeddings that has two columns: one for the embedding itself and the other for a unique identifier that can be used to link the embedding with the text it is derived from (implicitly stored in a separate table with the same unique identifier). (Note that the example uses ?s as placeholders for the embedding and the unique identifier, as these are typically supplied programmatically as parameters to the statement.)

INSERT INTO Sample.Embeddings (Embedding, UID) 
   VALUES (TO_VECTOR(?,double), ?)

The following code samples use this query to insert a single embedding into the table.

 ClassMethod InsertEmbeddings(embedding As %String, uid As %Integer)
   set sc=$$$OK
   try {
     set myquery = "INSERT INTO Sample.Embeddings (Embedding, UID)" 
                  _"VALUES (TO_VECTOR(?,double), ?)"
     set tStatement = ##class(%SQL.Statement).%New()
     set rset = tStatement.%Execute(embedding, uid)
     if (rset.%SQLCODE < 0) {
       throw ##class(%Exception.SQL).CreateFromSQLCODE(rset.%SQLCODE,rset.%Message)
   catch e {
     set sc = e.AsStatus()
     return sc
 public void InsertEmbeddings(String embeddings, Integer uid) {
   try {
     // set connection parameters
     IRISDataSource ds = new IRISDataSource();
     IRISConnection conn = ds.GetConnection();
     String sql = "INSERT INTO Sample.Embeddings (Embedding, UID) " + 
                  "VALUES (TO_VECTOR(?,double), ?)";
     PreparedStatement pstmt = conn.prepareStatement(sql);
   } catch (Exception ex) {
     System.out.println("caught exception: "
             + ex.GetClass().getName() + ": " + ex.GetMessage());
 def insertEmbeddings(embeddings, uid):
   // set the connection parameters
   conn_string = "localhost:1972/USER"
   username = "_system"
   password = "SYS"
   connection = iris.connect(conn_string, username, password)
   cursor = connection.cursor()
     sql = "INSERT INTO Sample.Embeddings (Embedding, UID) " +
           "VALUES (TO_VECTOR(?,double), ?)"
     params = [embeddings,uid]
   except Exception as ex:
     if cursor:
     if connection:
 static void InsertEmbeddings(string emb, Integer uid)
    // set the connection parameters
    String host = "";
    String port = "51776";
    String username = "_SYSTEM";
    String password = "SYS";
    String namespace = "USER";

    IRISConnection conn = new IRISConnection();
    IRISConnect.ConnectionString = "Server = " + host
        + "; Port = " + port + "; Namespace = " + namespace
        + "; Password = " + password + "; User ID = " + username;


    String sql = "INSERT INTO Sample.Embeddings (Embedding, UID) " +
                 "VALUES (TO_VECTOR(?,double), ?)";
    IRISCommand cmd = new IRISCommand(sql, conn);

FeedbackOpens in a new tab