A Guide to Using the OceanBase Vector Database

Posted on In Views: Word count in article: 6k Reading time ≈ 22 mins.
The official guide to using the OceanBase vector database: a detailed walkthrough of vector index types such as HNSW, HNSW_SQ, HNSW_BQ, IVF, and IVF_PQ, covering type selection, parameter tuning, memory estimation, and partitioned-table practice, with performance optimization recommendations spanning data volumes from millions to hundreds of millions.

The AI era has arrived, and knowing how to make good use of the “vector database”—the data foundation of the AI era—has become essential knowledge for today’s DBAs and AI application developers.

To help everyone better use the OceanBase vector database, Lao Ji invited Gehao, the senior technical expert in charge of R&D for OceanBase’s vector capabilities, along with a group of R&D engineers, to jointly write this “Guide to Using the OceanBase Vector Database.”

We recommend bookmarking this article first, to have it on hand when needed.

This article is packed with practical insights, aiming to provide guidance on performance optimization of OceanBase’s vector capabilities for users who already have a basic understanding of vector databases and vector indexes.

Note:

The prerequisite knowledge for reading this article: A Gentle Introduction to Vector Databases.

Friends who are not yet familiar with vector databases can read this article first.

When the vector data volume is below one million, we recommend using the OceanBase database’s default parameter configuration.

When the data volume exceeds one million and you have higher performance needs, we strongly recommend carefully reading the content of this article.

Vector Index Basics

Creating a Vector Index

OceanBase supports creating an index along with table creation, as well as creating an index after the table is created.

1
2
3
4
5
6
7
8
-- Create a vector index along with the table
CREATE TABLE test(
  id bigint(20) NOT NULL,
  label varchar(256) NOT NULL,
  embedding VECTOR(768) DEFAULT NULL,
  PRIMARY KEY (id),
  VECTOR INDEX vec_idx (embedding) WITH (DISTANCE=COSINE, TYPE=HNSW)
);
1
2
3
4
5
6
7
8
9
-- Create the index after writing data
CREATE TABLE test(
  id bigint(20) NOT NULL,
  label varchar(256) NOT NULL,
  embedding VECTOR(768) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE VECTOR INDEX vec_idx ON test(embedding) WITH (DISTANCE=COSINE, TYPE=HNSW);

If you already have a large amount of data—say, in the millions or more—it’s recommended to create the table first, import all the data, and then create the index using multiple concurrent threads. After the index is created, newly added or modified vectors can be queried immediately, but write performance will be affected to some degree. This is related to OceanBase’s current incremental indexing strategy.

Because vector index construction requires a large number of floating-point computations, many industry implementations adopt an asynchronous mode: the data is written first without immediately creating the index, and at query time it either brute-force-searches the incremental data, or waits until the asynchronous incremental index has been built and loaded into memory before it can be queried. With this approach, incremental data cannot be queried in real time, or the impact on query performance is significant.

In most user scenarios, the demand for adding or modifying data is to write a batch of data every day or at regular intervals; although the TPS of real-time DML is not high, users all want writes to be immediately visible. Therefore, OceanBase prioritized supporting the synchronous mode: newly added data immediately enters the incremental index upon writing and can be queried right away through the vector index. To ensure good write performance, the incremental portion is not currently quantized or compressed. When the incremental data of an HNSW index reaches 20% of the existing data, a background task automatically rebuilds the index, quantizing the incremental data and merging the incremental and original data together to compress the index’s memory footprint and improve performance. IVF indexes don’t need a rebuild to restore performance, but if a lot of data is added, the clustering characteristics may change, so we recommend proactively rebuilding when the added data reaches 30%.

Building the Index Concurrently

The method for building a vector index in parallel is the same as for building other indexes in parallel: it must be specified via a parallel hint. When building the index, if the business load is not high, it’s recommended to set the parallelism to twice the number of tenant CPUs.

1
2
3
CREATE /*+ PARALLEL(16) */ VECTOR INDEX vec_idx
ON test(embedding)
WITH (distance=cosine, type=HNSW);

Note: When the data volume reaches tens of millions or more, it’s recommended to set alter system set _px_object_sampling = 5000; to improve the accuracy of the sampling results, so that the load across threads during construction is as even as possible, increasing the build speed.

Index Parameters When Creating the Index

When creating a vector index, in addition to specifying the index build parameters m, ef_construction, and nlist, you can also specify the default query parameters ef_search and nprobes. The query parameters are used as the defaults at query time.

1
2
3
4
5
6
7
CREATE /*+ PARALLEL(16) */ VECTOR INDEX vec_idx
ON test(embedding)
WITH (distance=cosine,
      type=HNSW,
      lib=vsag,
      m=16,
      ef_construction=200);

The specific role of each parameter is explained in Section 3.

If you need to modify the query parameters at query time, please refer to the relevant content in Section 1.2.

IVF/IVFPQ indexes are disk-based (table-based) indexes, so the storage encoding strategy affects index performance. It’s recommended to enable CSEncoding encoding when using IVF indexes; you need to specify ROW_FORMAT when creating the table and set BLOCK_SIZE.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE test(
  id bigint(20) NOT NULL,
  label varchar(256) NOT NULL,
  embedding VECTOR(768) DEFAULT NULL,
  PRIMARY KEY (id)
) ROW_FORMAT = COMPRESSED;

create /*+ parallel(16) */ VECTOR INDEX vec_idx
ON test(embedding) with (
  distance=cosine, type=IVF_PQ, lib=OB,
  M=384, SAMPLE_PER_NLIST=256, NBITS=8, NLIST=1000)
block_size=1048576;

Querying with a Vector Index

The method for querying with a vector index differs from that of other indexes. Other indexes specify the search range through a where condition, and data meeting the condition is precisely filtered out. The purpose of a vector index, however, is to perform relevance-based approximate search, sorted by vector distance from smallest to largest; it has no exact-filtering semantics (although, by tuning the index build and query parameters, you can push recall above 99%). Therefore, when querying with the index, you must use the following syntax:

1
2
3
4
5
select
  id, label,
  cosine_distance(embedding, @query_vector) as distance
from test
order by distance approximate limit 10;

Note that if you don’t specify the approximate keyword APPROXIMATE or APPROX, the vector index will not be used; instead, the table data will be scanned and computed exactly.

Creating Scalar Indexes for Filter Condition Fields

In real business, a pure vector query may not meet the need—for example, when you need to query data meeting certain specific conditions and sort by vector similarity. Such scenarios require hybrid scalar and vector search. In past systems, due to limited database capabilities, vector and scalar queries had to be performed separately, which had two kinds of impact:

  • Performance degradation;
  • A possible drop in recall.

For example, performing the scalar query first and then computing distances and sorting the resulting vectors may cause a large number of redundant vector computations and poor performance. If you do the vector search first, the first vector query may not return enough data to satisfy the scalar condition, causing data to be missed and lowering recall. To solve the recall-drop problem, an external iterative query is often introduced; but external iterative queries cannot use a lower-cost execution plan and cannot push down filters within the query, so they unintentionally scan more data and also perform poorly.

Starting from version 4.3.5, OceanBase implements hybrid scalar and vector search and supports adaptive algorithm selection during execution. The optimizer can produce different execution plans and, based on the filtering power of the scalar and the vector respectively, compute the physical cost of each plan to choose the optimal one—thereby ensuring the highest possible performance and recall. For example, when the scalar condition has good filtering power, the pre-filtering algorithm is selected automatically.

The way to use a scalar filter condition is to include the scalar filter condition directly in the vector index query. OceanBase has implemented multiple query algorithms for hybrid scalar and vector search—such as pre-filtering and iterative filtering algorithms—to ensure the highest possible performance and recall. On HNSW / HNSWSQ / HNSWBQ indexes, it also implements adaptive algorithm selection at execution time (adaptive selection for IVF / IVFPQ will be available in the next version). This is to handle the discrepancy between cost estimation and actual data: during query execution, OceanBase also uses runtime statistics to correct the subsequent execution flow.

1
2
3
4
select id, label, cosine_distance(embedding, @query_vector) as distance
from test
where id < 1000 and label='red'
order by distance approximate limit 10;

If the scalar condition is highly selective, OceanBase chooses the pre-filtering algorithm and leverages the best scalar index. Therefore, creating an index on the scalar field can accelerate query performance. For example, in the query above, suppose label=’red’ filters out most of the data and is more selective than id < 1000; you can create a scalar index on the label column.

1
CREATE INDEX label_idx on test(label);

After creating the scalar index, you don’t need to make any changes to the query statement.

Specifying Index Query Strategy Priority via Hints

For HNSW / HNSWSQ / HNSWBQ indexes, a hint can specify the priority of the algorithm to use. This can be used to improve performance in some hybrid scalar-and-vector query scenarios. OceanBase has a plan cache capability, so SQL of the same pattern hits the same execution plan. For vector indexes, different execution strategies need to be selected based on the filtering power of the scalar condition. If you already know that for a certain class of query, the scalar condition is highly selective in most cases, you can specify that the vector search prefer the pre-filtering algorithm; otherwise, use iterative filtering.

The hint syntax is the same as an index hint, in the form /*+index(table_name index_name)*/. When using the approximate keyword to specify a vector index query, if the index name specified by the index hint is a scalar index, then pre-filtering is preferred; if the index name specified by the index hint is the vector index itself, then iterative filtering is preferred.

1
2
3
4
5
6
select /*+index(test label_idx)*/
  id, label,
  cosine_distance(embedding, @query_vector) as distance
from test
where id < 1000 and label='red'
order by distance approximate limit 10;

Through explain, you can observe that the execution plan will show an adaptive query (pre-filtering):

A Guide to Using the OceanBase Vector Database

1
2
3
4
select /*+index(test vec_idx)*/ id, label, cosine_distance(embedding, @query_vector) as distance
from test
where id < 1000 and label='red'
order by distance approximate limit 10;

Through explain, you can observe that the execution plan will show an adaptive query (iterative):

A Guide to Using the OceanBase Vector Database

For the IVF/IVFPQ algorithms, you can likewise specify a hint, but once the hint is set, the execution method no longer changes. Adaptive algorithm selection for IVF/IVFPQ will be provided in 435BP5.

Adjusting Query Parameters via PARAMETERS

When querying with HNSW / HNSWSQ / HNSWBQ indexes, you can make statement-level adjustments via PARAMETERS. The currently supported statement-level query parameters include ef_search and refine_k (HNSWBQ only).

1
2
3
4
5
6
7
select
  id, label,
  cosine_distance(embedding, @query_vector) as distance
from test
where id < 1000 and label='red'
order by distance approximate limit 10
PARAMETERS (ef_search=200);

Besides specifying query parameters when creating the index and setting statement-level query parameters via PARAMETERS, you can also set them via session variables—for example, for an HNSW index you can set ob_hnsw_ef_search = 100, and for an IVF index you can set ob_ivf_nprobes = 10.

The priority of the parameters, from highest to lowest, is: PARAMETERS > session variables > parameters set when creating the index.

The role of each parameter is described in detail in Section 3 of this article.

OceanBase vector indexes provide memory-related configuration parameters, primarily ob_vector_memory_limit_percentage and load_vector_index_on_follower. In most cases, the default values are sufficient.

ob_vector_memory_limit_percentage

This configuration item controls the percentage of the tenant’s total memory that vector indexes can use.

Before version 4.3.5BP3, this configuration item had to be manually set by the user to a value greater than 0, otherwise the vector index feature couldn’t be used; the recommended value was 30%. Starting from 4.3.5 BP3, the default value of this configuration item is 0, indicating adaptive mode, and unless there is a special need, users no longer need to pay attention to this configuration item. The adaptive strategy is: when the tenant’s actual memory is 8 GB or less, the vector index uses at most 40% of the tenant’s memory; when the tenant’s actual memory is more than 8 GB, the vector index uses at most 50% of the tenant’s memory. Reserving enough memory for the tenant ensures the stability of its query workload or DML.

1
ALTER SYSTEM SET ob_vector_memory_limit_percentage = 60;

load_vector_index_on_follower

This configuration item is only available starting from 4.3.5 BP3. It specifies whether the follower automatically synchronizes and loads the in-memory vector index. The default value is true, meaning the follower also loads the vector index into memory. After this configuration item is disabled, the vector index on the follower will not be automatically loaded into memory. If weak-consistency reads are not needed, you can disable this configuration item to reduce the memory consumed by vector indexes.

1
ALTER SYSTEM SET load_vector_index_on_follower = false;

This configuration item is not synchronized to the standby database; in a primary-standby scenario, it must be set separately on both the primary and the standby.

Index Type Selection

OceanBase provides multiple vector index algorithms, and users can choose the appropriate index based on different usage scenarios.

First, indexes fall into two major categories:

  1. The graph-based HNSW index and its quantized indexes HNSW_SQ and HNSW_BQ;
  2. The disk-based IVF index and its quantized index IVF_PQ.

Graph indexes need to reside permanently in memory, but their performance is higher than disk indexes. Disk indexes can also provide good performance when the cache is sufficient, and in extreme cases can be completely independent of resident memory. In OceanBase, you can estimate memory usage with the following SQL.

1
2
3
4
5
6
7
8
SELECT dbms_vector.index_vector_memory_advisor('HNSW', 1000000, 768, 'FLOAT32', 'M=16, DISTANCE=COSINE');
+---------------------------------------------------------+
| dbms_vector.index_vector_memory_advisor                 |
|   ('HNSW',1000000,768,'FLOAT32','M=16,DISTANCE=COSINE') |
+---------------------------------------------------------+
| Suggested minimum vector memory is 7.3 GB               |
+---------------------------------------------------------+
1 row in set (0.004 sec)

For detailed usage, please refer to:

  1. INDEX_VECTOR_MEMORY_ESTIMATE[1]
  2. INDEX_VECTOR_MEMORY_ADVISOR[2]

Below are recommendations for choosing an index type.

HNSW for High-Performance Scenarios

In-memory graph indexes perform significantly better than disk indexes, but because they must reside in memory, their memory cost is high, and the total data volume needs to be dynamically balanced—if the data grows significantly, scale-out is required.

  1. When memory is sufficient (typically a data volume in the millions) and you require the highest recall and high performance, use the HNSW index.
  2. When memory is fairly sufficient (typically a data volume of millions to tens of millions) and you require high recall and high performance, use the HNSW_SQ index, which uses about 1/4 to 1/3 of the memory of the HNSW index.
  3. When memory is relatively limited compared to the data volume (typically a data volume of tens of millions to hundreds of millions) and you require high recall and relatively high performance, use the HNSW_BQ index, which uses about 1/30 of the memory of the HNSW index.

Among these indexes, HNSW_SQ has the highest performance and HNSW has the highest recall, but under standard datasets all of them can reach 99% recall by tuning parameters. The specific role of each parameter is described in the next section.

IVF for High-Capacity Scenarios

Disk indexes are used for high-capacity scenarios and can be made entirely independent of resident memory. For scenarios with very large data volumes or append-only scenarios (insert-only, no deletes), if performance can meet the need, disk indexes are recommended.

  1. The IVF index builds relatively faster, but its memory consumption during construction is relatively higher; compared with the IVF_PQ index, its queries are a bit slower and its recall is higher.
  2. The IVF_PQ index builds a bit slower, but its memory consumption during construction is relatively lower; its query performance is higher than IVF’s, and its recall is slightly lower.

Note that high-compression-rate quantization algorithms, such as HNSW_BQ and IVF_PQ, may have a lower recall ceiling for low-dimensional vectors. It’s recommended to use HNSW_BQ on vectors of 512 dimensions or higher, and IVF_PQ on vectors of 128 dimensions or higher.

Parameter Recommendations

Scenario Index type Parameter recommendations
Highest recall
(most memory used)		 HNSW Million-scale: m = 16, ef_construct = 200, ef_search = 100
Highest performance
(less memory used)		 HNSWSQ Million-scale: m = 16, ef_construct = 200, ef_search = 100
Ten-million-scale: m = 32, ef_construct = 400, ef_search = 350
Best value
(low memory use, good performance)		 HNSWBQ Million-scale: m = 16, ef_construct = 200, ef_search = 100
Ten-million-scale: m = 32, ef_construct = 400, ef_search = 1000, refine_k=10
Hundred-million-scale: use a partitioned table, m = 32, ef_construct = 400, ef_search = 1000, refine_k=10
Low cost
(minimal memory use)		 IVFPQ Million-scale: nlist=1000, m=vector dimension/2, nprobes = 20
Ten-million-scale: nlist=3000, m=vector dimension / 2, nprobes = 20
Hundred-million-scale: use a partitioned table, nlist=3000, m=vector dimension / 2, nprobes = 20

If the current data volume is small—say, a few million—but will eventually reach tens of millions, you can configure based on the final data volume. A detailed explanation of the parameters and their tuning is presented in the following sections.

Index Parameter Descriptions and Tuning

Description of HNSW and Its Quantization Algorithm Parameters

Parameter Default Range Required Description Notes
distance / l2 / inner_product / cosine Yes Specifies the vector distance algorithm type. l2 means Euclidean distance, inner_product means inner-product distance, cosine means cosine distance.
type / hnsw / hnsw_sq / hnsw_bq Yes Specifies the index type.
m 16 [5,64] No The maximum number of neighbors per node. A larger value makes index construction slower and query performance better.
ef_construction 200 [5,1000] No The candidate set size when building the index. A larger value makes index construction slower and the index quality better. ef_construction
must be greater than m.
ef_search 64 [1,16000] No The candidate set size at query time. A larger value makes queries slower and recall higher.
refine_k 4.0 [1.0,1000.0] No Used only for the HNSW_BQ index; this parameter is a floating-point type, used to adjust the reranking ratio of the quantized vector index. This parameter can be set when creating the index or specified at query time:
if not set at query time, the value set at index creation is used;
if set at query time, the value set at query time is used.
refine_type sq8 sq8/fp32 No Used only for the HNSW_BQ index, to set the build precision of the quantized vector index. This value improves efficiency by reducing memory overhead and build time during index construction, but may affect recall.
If the cluster is upgraded from an old version to V4.3.5 BP3, the default value of this parameter is fp32.
bq_bits_query 32 4/32 No Used only for the HNSW_BQ index, to set the query precision of the quantized vector index, in bits. This value improves efficiency by reducing memory overhead and build time during index construction, but may affect recall.

Tuning HNSW-Class Index Parameters

Different index types correspond to different memory costs, performance, and recall metrics. Under different data volumes, the recommended index build and query parameters also differ. This section gives the recommended configurations for HNSW / HNSWSQ / HNSWBQ indexes on 768-dimensional vectors under million- and ten-million-scale data volumes, along with test results in the same environment for reference. For hundred-million-scale vector data, please refer to the later sections of this article to choose the IVFPQ index, or the HNSWBQ index on a partitioned table. You can configure the index parameters according to the final data volume.

Million-Scale Data Volume
  1. Build parameters

m = 16, ef_construct = 200; the HNSWBQ index uses default values for its other parameters.

  1. Memory usage
Index type Recommended tenant memory Description
HNSW 15GB The recommended vector index memory size is 7.3GB (starting from 435bp3, when tenant memory is greater than 8GB, the vector index uses at most 50% of tenant memory by default; when tenant memory is 8GB or less, the vector index uses at most 40% of tenant memory by default, so about 15GB of tenant memory is needed)
HNSWSQ 6GB The recommended vector index memory size is 2.1GB
HNSWBQ 6GB The HNSWBQ index needs to use high-precision vectors during construction. Starting from 435bp3, HNSWBQ uses HNSWSQ by default as the cache during index construction, so for non-partitioned tables, the memory HNSWBQ requires is the same as HNSWSQ; after construction completes, the HNSWBQ index occupies only 405MB of memory

In a partitioned-table scenario, OB controls the number of partitions built concurrently based on the tenant’s memory size. Therefore, for the HNSWBQ index, you can configure the tenant memory as the HNSWBQ index’s query-time footprint + the single-partition SQ index footprint. For details, please refer to Section 4 of this article.

  1. Recall

Increasing ef_search and refine_k (HNSWBQ only) can improve recall through more vector computation, but will correspondingly reduce query performance. Under different TopN values, you can set the parameters to the recommended values in the table below; if you need to further improve recall, you can set the parameter values larger. Note that recall is directly related to data characteristics; the table below gives the recommended values for achieving about 0.95 recall on a 768-dimensional standard dataset.

TopN ef_search refine_k (HNSWBQ only)
Top10 64 4
Top100 240 4
Top1000 1500 4

It should be noted that the maximum recall of the various index algorithms differs. Under the recommended build parameters in this section, setting ef_search to 1000 drops QPS to 1/3 of what it is at 0.95 recall, but only HNSW can reach a recall above 0.99. The BQ index can further improve recall by increasing refine_k, but performance will degrade further.

  • Using the HNSW index, recall is 0.991 (ef_search=1000)
  • Using the HNSWSQ index, recall is 0.9786 (ef_search=1000)
  • Using the HNSWBQ index, recall is 0.9897 (ef_search=1000, refine k=10)
  1. Performance comparison under the same parameters

Performance test comparison in the same local environment: (retrieving Top 100, ef_search = 240, with refine_k = 4 additionally set for HNSWBQ)

You can see that for performance, HNSWSQ > HNSW > HNSWBQ.

For baseline recall (without filter), HNSW > HNSWSQ > HNSWBQ.

With a filter condition, HNSW’s recall is overall slightly higher than HNSWSQ’s; because HNSWBQ does more internal vector queries and refine under the filter scenario, although its Recall looks higher, its performance degradation is also greater than the other two algorithms.

Dataset QPS (HNSW) Recall (HNSW) QPS (HNSWSQ) Recall (HNSWSQ) QPS (HNSWBQ) Recall (HNSWBQ)
768D1M 3475.44 0.9499 5599.11 0.9468 3113.23 0.9278
768D1M1P 2859.95 0.9497 4177.61 0.9467 1428.73 0.9713
768D1M10P 2830.13 0.9456 4125.00 0.9428 1405.22 0.9699
768D1M15P 2810.78 0.9430 4038.47 0.9407 1363.12 0.9689
768D1M30P 2708.79 0.9326 3869.48 0.9324 1265.75 0.9648
768D1M50P 2550.85 0.9101 3538.30 0.9141 897.87 0.9779
768D1M70P 1130.01 0.9206 1302.21 0.9304 1178.47 0.9115
768D1M90P 902.03 0.9294 1227.79 0.9388 1428.38 0.8943
768D1M99P 3589.15 1.0000 5477.67 0.9894 2542.60 0.9981
Ten-Million-Scale Data Volume
  1. Build parameters

m = 32, ef_construct = 400; the HNSWBQ index uses default values for its other parameters.

  1. Memory usage
Index type Recommended tenant memory Description
HNSW 160GB The recommended vector index memory size is 76.3GB
HNSWSQ 48GB The recommended vector index memory size is 22.6GB
HNSWBQ 48GB Starting from 435bp3, HNSWBQ uses HNSWSQ by default as the cache during index construction, so for non-partitioned tables, the memory HNSWBQ requires is the same as HNSWSQ; after construction completes, the HNSWBQ index occupies only 5.4GB of memory
  1. Recall

Increasing ef_search and refine_k (HNSWBQ only) can improve recall through more vector computation, but will correspondingly reduce query performance. Under different TopN values, you can set the parameters to the recommended values in the table below; if you need to further improve recall, you can set the parameter values larger.

Note that recall is directly related to data characteristics; the table below gives the recommended values for achieving about 0.95 recall on a 768-dimensional standard dataset.

TopN ef_search refine_k (HNSWBQ only)
Top10 100 4
Top100 (HNSW / HNSWSQ) 350 -
Top100 (HNSWBQ) 1000 10

HNSWBQ index performance reference, with the same test machine as the earlier million-scale data volume (retrieving Top 100, HNSW ef_search = 350, HNSW BQ ef_search = 1000, refine_k = 10)

Dataset QPS (HNSW) Recall (HNSW) QPS (HNSWBQ) Recall (HNSWBQ)
768D1M 2637.47 0.9574 856.7874 0.9531
768D1M1P 2345.49 0.9569 523.6981 0.9514
768D1M10P 2341.62 0.9530 522.3097 0.9514
768D1M30P 2344.4467 0.9438 521.3001 0.9516
768D1M50P 1614.0711 0.9488 510.069 0.953
768D1M70P 764.9825 0.9691 272.1819 0.9532
768D1M90P 350.2265 0.977 105.4262 0.98
Hundred-Million-Scale Data Volume

If the final data volume will exceed one hundred million, it’s recommended to use the HNSWSQ or HNSWBQ index in combination with a partitioned table, or to use the IVF PQ index.

Description of IVF and Its Quantization Algorithm Parameters

Parameter Default Range Required Description Notes
distance / l2 / inner_product / cosine Yes Specifies the vector distance algorithm type. l2 means Euclidean distance, inner_product means inner-product distance, cosine means cosine distance.
type / ivf_flat/ivf_pq Yes Specifies the IVF index type.
nlist 128 [1, 65536] Yes The number of cluster centers. The recommended value is sqrt(partition data volume).
sample_per_nlist 256 [1, unit64_max] No The amount of sampled data per cluster center, used in post-build indexing. The default value is generally sufficient.
nbits 8 [1, 24] No Used only when creating an IVF_PQ index, to specify the quantization bit count. The recommended value is 8, with a recommended range of [8, 10]. A larger value means higher quantization precision and higher query accuracy, while query performance is affected.
m / [1, 65536] Yes Used only when creating an IVF_PQ index, to specify the quantized vector dimension. The recommended value is dim / 2, with a recommended range of [dim / 8, dim]. A larger value makes index construction slower and query accuracy higher, while query performance is affected.

Tuning IVF-Class Index Parameters

Ten-Million Data Volume
  1. Build parameters
  • IVF_FLAT
    • NLIST = 3000
  • IVF_PQ
    • NLIST = 3000, M = dim / 2

To balance the number of cluster centers with the amount of data per cluster center, it’s generally recommended to use sqrt(data volume) as the value of NLIST. In the IVFPQ scenario, set M to half the vector dimension (dim).

In a multi-partition-table scenario, because IVF indexes are currently all local indexes, each partition builds its own IVF index, so it’s recommended to estimate the NLIST value based on the average data volume. For example, in a 10-million, 768-dimensional scenario with 10 partitions, each partition averages one million records, so you should set the NLIST value based on sqrt(1M) = 1000.

  1. Memory usage

IVF indexes have relatively low memory requirements. Taking a 10-million, 768-dimensional scenario as an example, with the recommended parameters, the memory overhead can be referenced in the table below:

index_type Index parameters Memory overhead (build overhead / resident overhead)
IVF_FLAT distance=l2, nlist=3000 2.7 G / 10.5M
IVF_PQ distance=l2, nlist=3000, m=384 4.0 G / 1.3 G
IVF_PQ distance=cosine, nlist=3000, m=384 2.7 G / 11.4 M

In the table, the build-overhead portion of the memory overhead refers to memory occupied only during index construction, which is released once construction completes. Resident memory refers to the memory size the IVF vector index occupies continuously after construction completes.

For IVFPQ, in the case of distance = l2, extra memory is needed to cache precomputed results, which uses more resident memory than the distance = ip / cos cases, so distance = ip / cos is generally more recommended.

  1. Recall

For IVF-class index queries, recall and performance are determined by the value of nprobes. The larger nprobes is, the more cluster centers IVF searches and the more data it computes, the higher the recall, and correspondingly the lower the performance.

Under different TopN values, you can set the parameter to the recommended values in the table below, with an expected recall of about 0.9; if you need to further improve recall, you can set the parameter value larger. Note that recall is directly related to data characteristics; the table below gives only the recommended values for a standard dataset.

TopN nprobes
Top10 1
Top100 20
Top1000 90
Top10000 300
Hundred-Million-Scale Data Volume

Starting from hundred-million-scale data volumes, it’s recommended to consider using a partitioned-table scenario for IVF-class indexes. As the data volume increases and NLIST grows, the query overhead of a single IVF index becomes larger and larger; splitting into multiple partitions, with multiple small-data-volume IVF indexes, can improve performance and recall through parallel querying.

  1. Build parameters
  • IVF_FLAT
    • NLIST = 3000
  • IVF_PQ
    • NLIST = 3000, M = dim / 2

IVF indexes are local indexes, and each partition builds an IVF index, so in a multi-partition-table scenario, you set parameters based on the partition data volume. For example, in a 100-million, 10-partition scenario, each partition averages 10-million records, so the parameters here are the same as for the ten-million-scale data volume—set the build parameters based on the partition data volume.

  1. Memory usage

For the memory usage of multiple IVF partitions, since IVF indexes are local indexes and each partition builds an IVF index, the actual resident overhead must be multiplied by the number of partitions. For example, for IVF_FLAT in the table below, the estimated resident memory is 10.5M, and since there are 10 partitions, the actual memory usage is 10.5 * 10 = 105 M.

index_type Index parameters Memory overhead (build overhead / resident overhead)
IVF_FLAT distance=l2, nlist=3000 2.7 G / 10.5 * 10 M
IVF_PQ distance=l2, nlist=3000, m=384 4.0 G / 1.3 * 10 G
IVF_PQ distance=cosine, nlist=3000, m=384 2.7 G / 11.4 * 10 M
  1. Recall

For IVF-class index queries, recall and performance are determined by the value of nprobes. The larger nprobes is, the more cluster centers IVF searches and the more data it computes, the higher the recall, and correspondingly the lower the performance.

In a partitioned-table scenario, because each partition is a separate IVF index, if a query lands on multiple partitions, each partition actually performs its own IVF index query and returns TopN records, after which the results from all partitions are aggregated and reranked once more. So the actual accuracy is higher than in the single-partition scenario, and correspondingly a lower nprobes can achieve the same recall as a single-partition table.

Under different TopN values, you can set the parameter to the recommended values in the table below, with an expected recall of about 0.9; if you need to further improve recall, you can set the parameter value larger. Note that recall is directly related to data characteristics; the table below gives only the recommended values for a standard dataset.

TopN nprobes
Top10 1
Top100 10
Top1000 45
Top10000 150

Using Partitioned Tables

The main purpose of using a partitioned table is to handle large-data-volume scenarios; secondarily, if a query condition can serve as the partition key, partition pruning can improve query performance. A partitioned table is recommended in the following two scenarios:

  1. The data volume reaches tens of millions or hundreds of millions or more.
  2. There is a clear scalar column in the query condition that can be used for partition pruning. For example, in the earlier example, if the label field always appears in the where condition, you can consider using label as the key to create a partitioned table.

Usage Recommendations

  1. Partition division

When using vector indexes, more partitions is not always better. Vector indexes differ from ordinary scalar indexes. Take the HNSW index as an example: under the same configuration parameters, querying TopK on an HNSW index containing 1 million vectors and querying TopK on an HNSW index containing 2 million vectors require fairly similar computation costs. Therefore, when partition pruning is not possible, the vector index performance of a partitioned table may even be worse than that of a non-partitioned table. And an overly large single partition will make index rebuilds slower and also affect performance to some degree in hybrid queries with scalars. Therefore, when using a partitioned table, it’s recommended to keep the data volume within a single partition below 20 million, and to prefer choosing a column that can be used for partition pruning as the partition key.

  1. Algorithm selection

For large data volumes, it’s recommended to choose the HNSWBQ or IVFPQ index. If you need to use other indexes, please estimate based on the memory usage below.

  1. Memory usage

For the HNSW index and the HNSWBQ index, the tenant memory needs to be greater than the HNSWBQ index’s query-time footprint + the single-partition SQ index footprint. For example, for 100 million records using 10 partitions, each partition holds roughly 10 million vectors. A single partition needs 48GB of build memory, and at query time the ten partitions’ HNSWBQ occupies 54GB of memory, so the tenant needs at least 102GB. Considering that some incremental data is not quantized and compressed in real time, it’s recommended to configure the tenant memory to 128GB. Other data volumes can be estimated this way.

For the IVF_FLAT and IVFPQ indexes, the tenant memory needs to be greater than the memory needed to build a single partition + the single-partition resident memory * the number of partitions. For example, for 100 million records using 10 partitions, each partition holds roughly 10 million vectors. A single partition needs 2.7GB of build memory, and at query time the ten partitions’ IVFPQ occupies 110M of memory, so the tenant needs at least 3G of memory for the vector index. In this scenario, it’s recommended to configure the tenant memory to 6G. Other data volumes can be estimated this way.

  1. Build and query parameters

Set the index build and query parameters according to the maximum data volume within a single partition. For the HNSW / HNSWSQ / HNSWBQ indexes, please refer to Section 3.1.1; for the IVF / IVFPQ indexes, please refer to Section 3.2.1. For example, for the HNSWBQ index with 100 million vectors using 10 partitions, you can set m = 32, ef_construct = 400, and for querying Top100, set the query parameters to ef_search=1000, refine_k = 10.

  1. Performance and recall

If the query can be pruned to a single partition, the performance and recall are consistent with the single-partition case; please refer to the single-partition situation described earlier.

If it cannot be fully pruned to a single partition, then QPS can be estimated based on the number of partitions on a single observer node. For example, if an observer has 3 partitions, then QPS is roughly 1/3 of the single-partition performance, and because more candidate results are queried, recall will be higher than in the single-partition case.

References

[1]

INDEX_VECTOR_MEMORY_ESTIMATE: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003532842

[2]

INDEX_VECTOR_MEMORY_ADVISOR: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003532843

Finally, we recommend the WeChat official account “Lao Ji’s Tech Talk,” run by Lao Ji, the OceanBase open-source lead. It continuously publishes a variety of technical content related to #Databases, #AI, and #Tech Architecture. Friends who are interested are welcome to follow!

“Lao Ji’s Tech Talk” not only hopes to keep bringing you valuable technical sharing, but also hopes to contribute to the open-source community together with everyone. If you appreciate the OceanBase open-source community, please light up a little star ✨! Every Star you give is the motivation behind our efforts.