5.6.3 Updating a Vector Store

To keep up with the changes and updates in the documents in your local directory, you must update the vector embeddings loaded in the vector store table on a regular basis. This ensures that the responses generated by GenAI are up-to-date.

The following sections in this topic describe how to update a vector store:

• Before You Begin

• Appending a New File to the Vector Store

• Removing a File from the Vector Store

• Deleting and Recreating the Vector Store

• Cleaning Up

• What's Next

Before You Begin

Complete the steps to set up a vector store.

The examples in this topic use the vector store table demo_embeddings created in Ingesting Files into a Vector Store.

Appending a New File to the Vector Store

The VECTOR_STORE_LOAD routine ingests all files that are available in the specified location and appends vector embeddings to the specified vector store table. If you run the VECTOR_STORE_LOAD routine on a table that contains previously ingested files, any file ingested again into the table is assigned a new document_id while retaining the same document_name. To remove a previously ingested file from the vector store table, you need to manually delete the associated rows, as described in Removing a File from the Vector Store.

To test the steps in this topic, download and place the MySQL AI user guide PDF in the folder demo-directory that you created earlier for storing files to ingest into the vector store.

To append a new file to the vector store table, perform the following steps:

1. Check that the vector embeddings are loaded in the vector store table you want to update:

   mysql> SELECT COUNT(*) FROM VectorStoreTableName;

   Replace VectorStoreTableName with the name of the vector store table you want to update.

   For example:

   mysql> SELECT COUNT(*) FROM demo_embeddings;

   If you see a numerical value in the output, the embeddings are loaded in the table.

2. To specify vector store table to update, set the @options variable:

   mysql> SET @options = JSON_OBJECT("schema_name", "DBName", "table_name", "VectorStoreTableName", "language", "Language");

   Replace the following:

   • DBName: the name of database that contains the vector store table.

   • VectorStoreTableName: the vector store table name.

   For example:

   mysql> SET @options = JSON_OBJECT("schema_name", "demo_db", "table_name", "demo_embeddings");

3. To append a new file from the local filesystem, use the VECTOR_STORE_LOAD routine:

   mysql> CALL sys.VECTOR_STORE_LOAD("file://FilePath", @options);

   Replace FilePath with the file path. For example:

   mysql> CALL sys.VECTOR_STORE_LOAD("file:///var/lib/mysql-files/demo-directory/mysql-ai-9.4-en.pdf", @options);

   This call appends vector embeddings for the MySQL AI user guide to the demo_embeddings vector store table.

4. Verify that the new vector embeddings are appended to the vector store table:

   mysql> SELECT COUNT(*) FROM VectorStoreTableName;

   For example:

   mysql> SELECT COUNT(*) FROM demo_embeddings;

   If you see a numerical value in the output which is different than the one you saw in step 1, then the vector store table is successfully updated.

Removing a File from the Vector Store

To remove a previously ingested file from the vector store table, use the DELETE statement:

 mysql> DELETE FROM VectorStoreTableName WHERE document_name = "Filename" and document_id = DocumentID;

For example:

 mysql> DELETE FROM demo_embeddings WHERE document_name = "/var/lib/mysql-files/demo-directory/heatwave-en.pdf" AND document_id = 0;

This removes the vector embeddings and all rows and columns associated with MySQL HeatWave user guide from the demo_embeddings vector store table.

Deleting and Recreating the Vector Store

To delete and recreate the vector store table and vector embeddings, perform the following steps:

1. Delete the vector store table:

   mysql> DROP TABLE VectorStoreTableName;

2. To create new embeddings for the updated documents, repeat the steps to set up a vector store.

Cleaning Up

If you created a new database for testing the steps in this topic, delete the database to free up space:

mysql> DROP DATABASE demo_db;

What's Next

• Learn how to Generate Vector Embeddings.

• Learn how to Perform Vector Search With Retrieval-Augmented Generation.