Choose an index type

OceanBase Database provides vector indexes with different algorithms. You can choose an appropriate index type based on your use case.

HNSW or IVF?

OceanBase Database provides two types of dense indexes:

• Graph-based HNSW series indexes: HNSW, HNSW_SQ, and HNSW_BQ.
• Disk-based IVF series indexes: IVF and IVF_PQ.

These two types of indexes have different strengths. HNSW series indexes typically provide higher query performance but require more memory. IVF series indexes perform well when cache is sufficient and do not require memory. However, the choice between HNSW and IVF is not solely based on memory considerations. You must also evaluate other factors such as business scenarios, data size, performance requirements, and resource constraints. The following sections compare the core differences between these two types of indexes and provide recommendations.

Core differences

Decision flowchart

Use partitioned tables?

The primary purpose of using partitioned tables is to handle large-scale data scenarios. Additionally, if the query conditions can be used as partition keys, partition pruning can enhance query performance. We recommend using partitioned tables in the following two scenarios:

• The data volume reaches tens of millions or even hundreds of millions: When the data volume is very large, partitioned tables can distribute the data across multiple partitions. Each partition independently builds an index, thereby reducing the load of a single query and improving overall query performance.
• The query conditions include a specific scalar column that can be used for partition pruning: For example, if the label field always appears in the WHERE condition, you can consider creating a partitioned table with label as the partition key. This way, partition pruning can reduce the number of partitions to be queried.

Specific recommendations are as follows:

Partitioning

When using vector indexes, the number of partitions should not be excessive. Unlike scalar indexes, vector indexes (such as HNSW) do not significantly increase the computational overhead for querying the top K results when the index size increases from 1 million to 2 million vectors under the same configuration. Therefore, if partition pruning cannot be used, an excessive number of partitions may reduce performance. Additionally, a single partition that is too large not only increases the time required for index rebuilding but also affects the efficiency of joint queries with scalar conditions.

In conclusion, we recommend that the data volume in each partition be controlled to be less than 20 million and that the partition key be selected as a field that supports partition pruning.

Algorithm selection

We recommend using the HNSW_BQ or IVF_PQ index for large-scale data. If you need to use other indexes, refer to the Memory usage section for estimation.

Memory usage

HNSW_BQ

For the HNSW_BQ index, we recommend that the tenant memory be greater than the total memory required for HNSW_BQ queries plus the memory required for the HNSW_SQ index in a single partition. The memory estimation process is as follows:

1. Use the INDEX_VECTOR_MEMORY_ADVISOR function to calculate the recommended memory values for the HNSW_BQ and HNSW_SQ indexes during construction and queries.
2. Based on the recommended memory values obtained from the above tool, calculate and determine the total memory required for the tenant.

For example, assume that you have 100 million 1024-dimensional vector data and use 10 partitions (approximately 10 million vectors per partition). The specific calculation process is as follows:

-- Specify REFINE_TYPE=SQ8 to directly and accurately obtain the recommended memory values for the HNSW_BQ index during construction and queries.
-- You do not need to separately calculate the memory required for the HNSW_SQ index. This is because the HNSW_BQ index is constructed using the SQ8 quantization algorithm by default.
-- After specifying refine_type=sq8, the function automatically includes the memory required for the SQ8 quantization vectors in the calculation.
-- The recommended memory value for the HNSW_BQ index is 74.6 GB, and the memory consumption during queries is 57.4 GB. We use the recommended memory value.
SELECT DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR('HNSW_BQ',100000000,1024,'FLOAT32','M=32,DISTANCE=COSINE,REFINE_TYPE=SQ8', 10000000);
+------------------------------------------------------------------------------------------------------------------------------+
| DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR('HNSW_BQ',100000000,1024,'FLOAT32','M=32,DISTANCE=COSINE,REFINE_TYPE=SQ8', 10000000) |
+------------------------------------------------------------------------------------------------------------------------------+
| Suggested minimum vector memory is 74.6 GB, memory consumption when providing search service is 57.4 GB                      |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set

-- By default, the vector memory occupies 50% of the tenant memory. Therefore, the total tenant memory is
SELECT 74.6/0.5;
+--------------------------------------------------------------------------------------------------------------+
| 74.6/0.5 = 149.2 GB |
+--------------------------------------------------------------------------------------------------------------+
1 row in set

-- To account for the possibility of new data not being compressed in actual environments, we recommend reserving some redundancy.
SELECT 149.2 * 1.2;
+--------------------------------------------------------------------------------------------------------------+
| 149.2 * 1.2 = 179.04 GB |
+--------------------------------------------------------------------------------------------------------------+
1 row in set

-- Therefore, we recommend that the tenant memory be configured to 179 GB.

IVF series

For the IVF and IVF_PQ indexes, we recommend that the tenant memory be greater than the memory required for constructing a single partition plus the total memory required for all partitions. For example, if you have 100 million data records and use 10 partitions, each with approximately 10 million vectors, the memory required for constructing a single partition is about 2.7 GB, and the total memory required for all partitions during queries is about 1.1 GB (110 MB per partition). Therefore, the minimum memory required is about 3 GB. We recommend reserving some redundancy and configuring 6 GB of memory. You can use the above method to estimate memory requirements for other data volume scenarios.

Construction and query parameters

We recommend setting the index construction and query parameters based on the maximum data volume in a single partition. For details, see the Index parameter recommendations section.

Performance and recall rate

• If the query conditions can prune to a single partition: the performance and recall rate are the same as in the single-partition scenario. For details, see the section describing different data volume scenarios.
• If the query conditions cannot prune to a single partition: QPS can be estimated proportionally based on the number of partitions on a single OBServer node (for example, if a single node contains 3 partitions, the QPS is approximately one-third of the performance in a single partition). Since cross-partition queries merge more candidate results, the actual recall rate is typically higher than in the single-partition scenario.

Index parameter recommendations

HNSW series

The recommended index construction and query parameters vary depending on the data volume within the same series. This section provides suggested configurations for HNSW, HNSW_SQ, and HNSW_BQ indexes for 768-dimensional vectors with 1 million and 10 million data points. For 100 million vectors, refer to the subsequent sections of this topic for IVF_PQ or HNSW_BQ indexes under partitioned tables.

Details for 1 million data points

The following table describes the memory usage and recall rate optimization for 1 million 768-dimensional vectors (using the parameters in the preceding table).

Memory usage:

Recall rate optimization:

You can improve the recall rate by increasing the number of vector calculations. However, this will reduce query performance. You can set the parameters to the values in the following table for different TopN values. If you need to further improve the recall rate, you can increase the parameter values.

Maximum recall rate:

The maximum recall rates of different index algorithms vary. In the preceding table, the ef_search parameter is set to 1000. In this case, the recall rate may be improved, but the QPS will drop to one-third of that at a recall rate of 0.95. Increasing the ef_search parameter does not improve the recall rates of all types of indexes. Only HNSW indexes can achieve a recall rate of more than 0.99. You can increase the refine_k parameter to further improve the recall rate of HNSW_BQ indexes. However, this will further reduce the query performance.

Maximum recall rate (ef_search = 1000):

• HNSW index: Recall rate of 0.991
• HNSW_SQ index: Recall rate of 0.9786
• HNSW_BQ index: Recall rate of 0.9897 (ef_search = 1000, refine_k = 10)

Details for 10 million data points

The following table describes the memory usage and recall rate optimization for 10 million 768-dimensional vectors (using the parameters in the preceding table).

Memory usage:

Recall rate optimization:

You can improve the recall rate by increasing the number of vector calculations. However, this will reduce query performance. You can set the parameters to the values in the following table for different TopN values. If you need to further improve the recall rate, you can increase the parameter values.

IVF series

To balance the number of cluster centers and the amount of data per center, we recommend setting nlist to the square root of the data volume. For example, for 10 million data records, we recommend setting nlist to approximately 3000. When using IVF_PQ, we recommend setting the m parameter to half of the vector dimension (dim).

10 million data records (with partitioned tables)

For 10 million 768-dimensional vector data records (using the parameters in the table above), we provide additional details on memory usage and recall optimization:

Memory usage:

In the table, Build Time refers to the temporary memory used during index creation, which is released after the index is built. Resident Time refers to the memory continuously occupied by the IVF vector index after the index is built.

For IVF_PQ indexes, if you choose distance = l2, more resident memory will be occupied due to the need to store additional precomputed results. In contrast, using distance = inner_product or cosine consumes less resident memory. Therefore, in practical applications, we recommend prioritizing inner_product or cosine as the distance type to optimize memory resources.

Recall optimization:

By adjusting the nprobes parameter, you can increase the number of vector calculations to improve recall, but this will reduce query performance. You can set the parameter to the recommended values in the table below for different TopN scenarios. If you need to further improve recall, you can set the parameter value to a larger value.

100 million data records (with partitioned tables)

When the vector data volume reaches 100 million or more, we strongly recommend using partitioned tables with IVF-type indexes. As the data scale and nlist parameter increase, the query overhead of a single IVF index will significantly increase. By splitting the data into multiple partitions and building smaller-scale IVF indexes for each partition, you can effectively reduce the query load and further improve overall query performance and recall through parallel queries across partitions.

In a multi-partitioned table scenario, since IVF indexes are local indexes, each partition will independently build its own IVF index. Therefore, we recommend calculating the nlist value based on the average data volume per partition. For example, for 100 million 768-dimensional vectors split into 10 partitions, each partition contains approximately 10 million data records, and we recommend setting nlist to sqrt(10 million) = 3162.

For 100 million 768-dimensional vector data records (using the parameters in the table above), we provide additional details on memory usage and recall optimization:

Memory usage:

In a multi-partitioned scenario, since each partition independently builds and maintains its own IVF index, the total resident memory usage must be summed across all partitions. For example, if a single IVF index has a resident memory usage of 10.5 MB and there are 10 partitions, the total resident memory usage would be approximately 10.5 × 10 = 105 MB.

In the table, Build Time refers to the temporary memory used during index creation, which is released after the index is built. Resident Time refers to the memory continuously occupied by the IVF vector index after the index is built.

Recall optimization:

By adjusting the nprobes parameter, you can increase the number of vector calculations to improve recall, but this will reduce query performance. You can set the parameter to the recommended values in the table below for different TopN scenarios. If you need to further improve recall, you can set the parameter value to a larger value.

In a partitioned table scenario, since each partition independently executes an IVF index query, when a query involves multiple partitions, the system will retrieve TopN results from each partition and then aggregate and re-sort all the results. This not only improves the overall search accuracy but also typically results in a higher actual recall rate compared to a single-partition scenario. Therefore, in a multi-partitioned table, you can appropriately reduce the nprobes parameter to achieve a recall rate comparable to that of a single-partitioned table.

References

• For more information about parameters and tuning, see HNSW index and IVF index.