Skip to main content

Semantic search

This topic describes how to use semantic search in seekdb.

tip

This feature is experimental in the current version and is not recommended for use in production environments.

Overview​

Semantic search leverages seekdb's built-in embedding capabilities to greatly simplify the vector index usage process. It makes the vector concept transparent to users: you can directly write raw data (such as text) that needs to be stored, and seekdb will automatically convert it to vectors and build indexes internally. During retrieval, you only need to provide the raw query content, and seekdb will also automatically perform embedding and retrieve the vector index, significantly improving ease of use.

Considering the performance overhead of embedding models, semantic search provides two embedding modes for users to choose from:

  • Synchronous mode: Embedding and indexing are performed immediately after data is written, ensuring real-time data visibility.
  • Asynchronous mode: Background tasks perform data embedding and indexing in batches, which can significantly improve write performance, with delayed data visibility. You can flexibly set the trigger cycle of background tasks based on your requirements for data visibility.

In addition, tables with semantic indexes can also perform brute-force search. Brute-force search refers to performing a search using a full table scan to obtain the exact results of the n nearest rows.

Feature support​

tip

This feature currently supports only HNSW/HNSW_BQ indexes.

The syntax for updating, deleting, and searching semantic indexes, as well as memory management, is exactly the same as that for HNSW series indexes. It supports index monitoring and maintenance processes, and in asynchronous mode, incremental refreshes will additionally trigger data embedding.

The supported features are as follows:

ModuleFeatureDescription
DDLCreate a hybrid vector index during table creationYou can create a hybrid vector index on a VARCHAR column when creating a table
DDLCreate a hybrid vector index after table creationSupports creating a hybrid vector index on a VARCHAR column of an existing table
Retrievalsemantic_distance functionPass raw data through this function for vector retrieval
Retrievalsemantic_vector_distance functionPass vectors through this function for retrieval. There are two usage modes:
  • When the SQL statement includes the APPROXIMATE/APPROX clause, vector index retrieval is used.
  • When the APPROXIMATE/APPROX clause is not included, brute-force search using a full table scan is performed.
DBMS_VECTORREFRESH_INDEXThe usage is the same as that for regular vector indexes. Performs incremental index refresh and embedding in asynchronous mode
DBMS_VECTORREBUILD_INDEXThe usage is the same as that for regular vector indexes. Performs full index rebuild

Some usage notes are as follows:

  • In synchronous mode, write performance may be affected by embedding performance. In asynchronous mode, data visibility will be delayed.
  • For repeated retrieval scenarios, it is recommended to use AI Function Service to pre-obtain query vectors to avoid embedding for each retrieval.

Prerequisites​

Register an embedding model and endpoint​

Before using hybrid vector indexes, you must register an embedding model and endpoint. The following is a registration example:

CALL DBMS_AI_SERVICE.DROP_AI_MODEL ('ob_embed');
CALL DBMS_AI_SERVICE.DROP_AI_MODEL_ENDPOINT ('ob_embed_endpoint');

CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_embed', '{
    "type": "dense_embedding",
    "model_name": "BAAI/bge-m3"
}');

CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT (
'ob_embed_endpoint', '{
    "ai_model_name": "ob_embed",
    "url": "https://api.siliconflow.cn/v1/embeddings",
    "access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "provider": "siliconflow"
}');
info

Replace access_key with your actual API Key. The BAAI/bge-m3 model has a vector dimension of 1024, so you need to use dim=1024 when creating a hybrid vector index.

You can enable semantic search by setting the parameter _enable_semantic_index to true. By default, this parameter is set to false:

ALTER SYSTEM SET _enable_semantic_index = true;

Creation syntax and description​

Creation​

Hybrid vector indexes support two creation methods: creation during table creation and creation after table creation. When creating an index, note the following:

  • The index must be created on a column of the VARCHAR type.
  • The model and sync_mode parameters are not supported for regular vector indexes.
  • The parameters and descriptions for an index created after table creation are the same as those for an index created during table creation.

You can use the CREATE TABLE statement to create a hybrid vector index. Through index parameters, background tasks can be initiated synchronously or asynchronously. In synchronous mode, VARCHAR data is automatically converted to vector data when data is inserted. In asynchronous mode, data conversion is performed periodically or manually.

Syntax:

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 VARCHAR, -- Text column
    ...,
    VECTOR INDEX index_name (column_name2) WITH (param1=value1, param2=value2, ...)
);

The param parameter description is as follows:

ParameterDefault valueValue rangeRequiredDescriptionRemarks
distancel2/inner_product/cosineYesSpecifies the vector distance algorithm type.l2 indicates Euclidean distance, inner_product indicates inner product distance, and cosine indicates cosine distance.
typeCurrently supports hnsw / hnsw_bqYesSpecifies the index algorithm type.
libvsagvsagNoSpecifies the vector index library type.Currently, only the VSAG vector library is supported.
modelRegistered model nameYesSpecifies the large language model name used for embedding.The model must be registered using AI Function Service before creating the index.:::tip Regular vector indexes do not support setting this parameter.:::
dimPositive integer, maximum 4096YesSpecifies the vector dimension after embedding.Must match the dimension provided by the model.
sync_modeasyncimmediate / manual / asyncNoSpecifies the data and index synchronization mode.immediate indicates synchronous mode, manual indicates manual mode, and async indicates asynchronous mode.:::tip Regular vector indexes do not support setting this parameter.:::
sync_interval10sTime interval, such as 10s, 1h, 1d, etc.NoSets the trigger cycle of background tasks in asynchronous mode.The numeric part must be positive. Units supported include seconds (s), hours (h), days (d), etc.

The usage of other vector index parameters (such as m, ef_construction, ef_search, etc.) is the same as that for regular HNSW/HNSW_BQ indexes. For details, see the HNSW series index documentation at the end of this topic.

Hybrid vector indexes support two search methods:

  • Text search
  • Vector search
    • You can specify the APPROXIMATE/APPROX clause to perform nearest neighbor search using the vector index.
    • You can also omit the APPROXIMATE/APPROX clause to perform brute-force search by scanning the entire table.

Use the semantic_distance expression to perform vector search by passing in the original text.

Syntax:

SELECT ... FROM table_name 
ORDER BY semantic_distance(column_name, 'query_text') [APPROXIMATE|APPROX] 
LIMIT n;

where:

  • column_name: the text column specified when the hybrid vector index was created.
  • query_text: the original text to search for.
  • n: the number of result rows to return.

For more information about the APPROXIMATE/APPROX clause, see the HNSW series index documentation at the end of this topic.

Create, update, search, and delete examples​

DML operations (INSERT, UPDATE, DELETE) for hybrid vector indexes are exactly the same as those for regular vector indexes. When inserting or updating data of the VARCHAR type, the system synchronously or asynchronously performs vector embedding based on the sync_mode parameter setting.

Create during table creation​

Create the vector_idx index when creating the test table items:

-- Assume that the ob_embed model has been created previously (please refer to the "Prerequisites" section to register the model)
CREATE TABLE items (
    id BIGINT PRIMARY KEY, 
    doc VARCHAR(100),
    VECTOR INDEX vector_idx(doc)
    WITH (distance=l2, lib=vsag, type=hnsw, model=ob_embed, dim=1024, sync_mode=async, sync_interval=10s)
);

Insert a row of data into the test table items. The system will automatically perform embedding:

INSERT INTO items(id, doc) VALUES(1, 'Rose');

Create after table creation​

After creating the test table items, use the CREATE VECTOR INDEX statement to create the vector_idx index:

CREATE TABLE items (
    id BIGINT PRIMARY KEY, 
    doc VARCHAR(100)
);

-- Assume that the ob_embed model has been created previously (please refer to the "Prerequisites" section to register the model)
CREATE VECTOR INDEX vector_idx 
ON items (doc) 
WITH (distance=l2, lib=vsag, type=hnsw, model=ob_embed, dim=1024, sync_mode=async, sync_interval=10s);

Insert a row of data into the test table items. The system will automatically perform embedding:

INSERT INTO items(id, doc) VALUES(1, 'Rose');

Update​

When updating data of the VARCHAR type, the system will re-perform embedding:

  • Synchronous mode: Re-embedding is performed immediately after update.
  • Asynchronous mode: Re-embedding is performed by background tasks at the next trigger cycle after update.

Usage example:

UPDATE items SET doc = 'Lily' WHERE id = 1;

Delete​

The delete operation is the same as that for regular vector indexes. You can directly delete the data.

Usage example:

DELETE FROM items WHERE id = 1;

Retrieval​

-- Assume that the ob_embed model has been created previously
CREATE TABLE items (
    id INT PRIMARY KEY, 
    doc varchar(100),
    VECTOR INDEX vector_idx(doc)
    WITH (distance=l2, lib=vsag, type=hnsw, model=ob_embed, dim=1024, sync_mode=immediate)
);

INSERT INTO items(id, doc) VALUES(1, 'Rose');
INSERT INTO items(id, doc) VALUES(2, 'Sunflower');
INSERT INTO items(id, doc) VALUES(3, 'Rose');
INSERT INTO items(id, doc) VALUES(4, 'Sunflower');
INSERT INTO items(id, doc) VALUES(5, 'Rose');

-- Retrieve using raw text
SELECT id, doc FROM items
ORDER BY semantic_distance(doc, 'Sunflower') 
APPROXIMATE LIMIT 3;

The return result is as follows:

+----+-----------+
| id | doc       |
+----+-----------+
|  2 | Sunflower |
|  4 | Sunflower |
|  5 | Rose      |
+----+-----------+
3 rows in set

Contents

Edit this page