OceanBase AI Database provides vector indexes with various algorithms. You can select the appropriate index type based on your usage scenario.
HNSW or IVF?
Dense indexes in OceanBase AI Database are divided into two main categories:
- Graph-based HNSW series indexes: HNSW, HNSW_SQ, HNSW_BQ.
- Disk-based IVF series indexes: IVF, IVF_PQ.
Each index type has its own strengths. HNSW series indexes typically provide higher query performance but require more resident memory; whereas IVF series indexes perform well with sufficient cache and can operate without relying on resident memory. However, the choice between HNSW and IVF is not based solely on memory considerations. Multiple dimensions such as business scenarios, data scale, performance metrics, and resource constraints need to be comprehensively evaluated. The following section details their core differences and provides selection recommendations.
Core difference comparison
Comparison dimension |
HNSW series |
IVF series |
|---|---|---|
| Storage method | In-memory graph-based index | Disk-based index |
| Memory usage | Requires complete loading into memory, resulting in high memory consumption | Can operate without resident memory, resulting in low memory consumption |
| Query performance (QPS) | Very high, millisecond-level response | High, approaching HNSW when cache is sufficient |
| Recall | High, up to 99% | High, slightly lower than HNSW, and can be optimized by parameters |
| Build speed | Slow, requires constructing a graph structure | Fast, based on clustering algorithms |
| Applicable data volume | Millions to hundreds of millions | Millions to billions |
| Cost | High memory cost | Low storage cost, suitable for large-scale data |
| Real-time | Supports real-time DML operations | Supports real-time DML operations |
Use partitioned tables?
The primary purpose of using partitioned tables is to handle large data volumes, and secondly, if query conditions can be used as the partitioning key, query performance can be improved through partition pruning. Partitioned tables are recommended in the following two scenarios:
- Data volume reaches tens of millions or hundreds of millions: When the data volume is very large, using partitioned tables allows data to be distributed across multiple partitions, with each partition independently building indexes. This reduces the load per query and improves overall query performance.
- Query conditions contain specific scalar columns that can be used for partition pruning: For example, if the
labelfield always appears in theWHEREcondition, then consider creating a partitioned table withlabelas the partitioning key. By doing so, partition pruning can reduce the number of partitions that need to be queried.
Specific usage recommendations are as follows:
Partitioning
When using vector indexes, the number of partitions should not be too large. Unlike scalar indexes, vector indexes (such as HNSW) do not significantly increase computational overhead for querying the TopK when the index size increases from 1 million vectors to 2 million vectors under the same configuration. Therefore, if partition pruning cannot be utilized, too many partitions may actually degrade performance. Additionally, a single partition being too large not only increases the time required for index rebuilding but also affects efficiency when jointly querying with scalar conditions.
In summary, it is recommended to keep the data volume within each partition below 20 million and preferentially choose fields that support partition pruning as the partitioning key.
Algorithm selection
For large datasets, it is recommended to choose the HNSW_BQ or IVF_PQ index. If you need to use other indexes, refer to the Memory usage section below for estimation.
Memory usage
HNSW_BQ
For the HNSW_BQ index, it is recommended that memory > total memory usage during HNSW_BQ query + memory usage of a single partition's HNSW_SQ index. The memory estimation process is as follows:
- First, use the
INDEX_VECTOR_MEMORY_ADVISORfunction to calculate the recommended memory values for building and querying the HNSW_BQ and HNSW_SQ indexes. - Based on the recommended memory values obtained from the preceding tool, calculate and determine the total required memory.
For example, assume you have 100 million 1024-dimensional vector data points, divided into 10 partitions (each with approximately 10 million vectors). Following the above process, the specific calculation is as follows:
-- By specifying REFINE_TYPE=SQ8, you can directly obtain the recommended memory values for the HNSW_BQ index during construction and query execution.
-- There is no need to calculate the index usage of HNSW_SQ separately. This is because HNSW_BQ uses the SQ8 quantization algorithm by default during construction.
-- After specifying refine_type=sq8, the function automatically includes the memory required to store the SQ8 quantized vectors needed during index construction.
-- The recommended memory value for the HNSW_BQ index is 74.6 GB, and the memory usage during queries is 57.4 GB. We will 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
-- The default memory usage for vector internal storage is 50%. Therefore, the total memory is
SELECT 74.6/0.5;
+--------------------------------------------------------------------------------------------------------------+
| 74.6/0.5 = 149.2 GB |
+--------------------------------------------------------------------------------------------------------------+
1 row in set
-- Considering that newly written data may not be compressed promptly in practical environments, it is recommended to appropriately reserve redundancy.
SELECT 149.2 * 1.2;
+--------------------------------------------------------------------------------------------------------------+
| 149.2 * 1.2 = 179.04 GB |
+--------------------------------------------------------------------------------------------------------------+
1 row in set
-- Therefore, it is recommended to configure the memory to 179 GB.
IVF series
For the IVF and IVF_PQ indexes, it is recommended that memory > memory for building a single partition + total resident memory for all partitions. For instance, for 100 million data points in 10 partitions, each with about 10 million vectors, building a single partition requires approximately 2.7 GB of memory. During query time, the combined resident memory for the IVF_PQ indexes across all 10 partitions is about 1.1 GB (110 MB/partition). Therefore, a minimum of about 3 GB of memory should be configured. It is recommended to allocate an appropriate margin above this, with a suggested configuration of 6 GB of memory. Other data volume scenarios can be estimated using the method above.
Construction and query parameters
Index construction and query parameters are recommended to be set based on the maximum data volume of a single partition. For details, see the Index parameter recommendations section below.
Performance and recall
- If query conditions can be narrowed down to a single partition: performance and recall will be consistent with the single-partition scenario. For details, see the sections describing different data volumes below.
- If conditions cannot be narrowed down to a single partition: QPS can be estimated proportionally based on the number of partitions on a single OBServer node (for example, if a node contains 3 partitions, QPS is approximately 1/3 of a single-partition performance). Since cross-partition queries merge more candidate results, the actual recall rate is usually higher than in a single-partition scenario.
Index parameter recommendations
HNSW series
The recommended index construction and query parameters vary for indexes of the same series under different data volumes. This section provides suggested configurations for 768-dimensional vectors with millions or tens of millions of data points, using the HNSW, HNSW_SQ, and HNSW_BQ indexes for reference. For vector data at the hundred-million level, refer to later chapters of this topic to choose the IVF_PQ index or the HNSW_BQ index under a partitioned table.
Note
- For scenarios where data volume is expected to grow, it is recommended to set parameters based on the final data volume.
- HNSW_BQ uses a high-compression quantization algorithm, which may result in a lower upper bound on recall for low-dimensional vectors. To avoid performance loss, it is recommended to use HNSW_BQ for vectors of 512 dimensions or higher.
Scenario |
Index type |
Parameter recommendations |
|---|---|---|
| Maximum recall (maximum memory usage) |
HNSW | For millions of data: m = 16, ef_construction = 200, ef_search = 100, and other parameters use the default values. |
| Maximum performance (low memory usage) |
HNSW_SQ | For 1 million data records: m = 16, ef_construction = 200, ef_search = 100. Use the default values for other parameters. For 10 million data records: m = 32, ef_construction = 400, ef_search = 350. Use the default values for other parameters. |
| Best cost-performance (low memory usage, high performance) |
HNSW_BQ | For datasets of millions of records: m = 16, ef_construction = 200, ef_search = 100. Use default values for other parameters. For datasets of tens of millions of records: m = 32, ef_construction = 400, ef_search = 1000, refine_k = 10. Use default values for other parameters. For datasets of hundreds of millions of records: Use partitioned tables. m = 32, ef_construction = 400, ef_search = 1000, refine_k = 10. Use default values for other parameters. |
Detailed explanation for millions of data points
Using 1 million 768-dimensional vector data records as an example (with the construction parameters from the table above), additional details on memory usage and recall tuning are provided:
Memory usage:
Index type |
Recommended memory |
Description |
|---|---|---|
| HNSW | 15 GB | We recommend setting the vector index memory size to 7.3 GB. When the system memory is greater than 8 GB, the vector index uses at most 50% of the memory by default. When the system memory is 8 GB or less, the vector index uses at most 40% of the memory by default. Therefore, approximately 15 GB of memory is required. |
| HNSW_SQ | 6 GB | The recommended value for the vector index memory size is 2.1 GB. |
| HNSW_BQ | 6 GB | The HNSW_BQ index requires high-precision vectors during construction, so it consumes the same memory as HNSW_SQ, which is 6 GB. By default, the HNSW_BQ index uses the SQ8 quantization method. After the index is built, its memory usage drops significantly to about 405 MB. The preceding description applies to non-partitioned tables. If you use a partitioned table, OceanBase AI Database dynamically adjusts the number of partitions that can be built simultaneously based on the allocated memory. For practical configuration, it is recommended to ensure the memory includes at least the memory usage for querying HNSW_BQ, plus the memory required by HNSW_SQ for building a single partition, with some redundancy. For the specific calculation method, refer to the Memory usage section above. |
Recall tuning:
By adjusting the ef_search and refine_k (for HNSW_BQ only) parameters, you can increase recall through more vector computation, but this will correspondingly reduce query performance. Under different TopN settings, you can set the parameters to the recommended values in the following table. If further recall improvement is needed, you can set the parameter values larger.
Note
Recall is directly related to data characteristics. The following table shows the recommended values for achieving a recall of about 0.95 on a standard 768-dimensional dataset.
TopN |
ef_search |
refine_k (HNSW_BQ only) |
|---|---|---|
| Top10 | 64 | 4 |
| Top100 | 240 | 4 |
Explanation of maximum recall:
The maximum recall varies among the index algorithms. With the recommended construction parameters set in this section, increasing ef_search to 1000 may improve query recall, but QPS will drop to one-third of what it was at a 0.95 recall. Furthermore, increasing ef_search does not guarantee unlimited recall improvement for all index types; only HNSW can achieve a recall above 0.99. The HNSW_BQ index can further increase recall by increasing refine_k, but performance will decline even more.
Maximum recall reference values (ef_search = 1000):
- HNSW index: Recall 0.991
- HNSW_SQ index: Recall 0.9786
- HNSW_BQ index: Recall 0.9897 (ef_search = 1000, refine_k = 10)
Detailed explanation for tens of millions of data points
Using 10 million 768-dimensional vector data records as an example (with the construction parameters from the table above), additional details on memory usage and recall tuning are provided:
Memory usage:
Index type |
Recommended memory |
Description |
|---|---|---|
| HNSW | 160 GB | The recommended value for the vector index memory size is 76.3 GB. |
| HNSW_SQ | 48 GB | The recommended value for vector index memory size is 22.6 GB. |
| HNSW_BQ | 48 GB | The HNSW_BQ index requires high-precision vectors during construction. By default, HNSW_BQ uses HNSW_SQ as the cache for index construction. Therefore, for non-partitioned tables, the memory required by HNSW_BQ is the same as that required by HNSW_SQ. After construction is complete, the HNSW_BQ index occupies only about 5.4 GB of memory. |
Recall tuning:
By adjusting the ef_search and refine_k (only for HNSW_BQ) parameters, you can increase recall through more vector computations, but this will correspondingly reduce query performance. For different TopN values, you can set the parameters to the recommended values in the following table. If you need to further improve recall, you can set the parameter values larger.
Notice
Recall is directly related to data characteristics. The following table provides recommended values for achieving a recall of around 0.95 on a standard 768-dimensional dataset.
TopN |
ef_search |
refine_k (HNSW_BQ only) |
|---|---|---|
| Top10 | 100 | 4 |
| Top100(HNSW/HNSW_SQ) | 350 | - |
| Top100(HNSW_BQ) | 1000 | 10 |
IVF series
Scenario |
Index type |
Parameter recommendations |
|---|---|---|
| Low-dimensional (384 dimensions and below) |
IVF | 10 million records: Use partitioned tables, nlist=3000 100 million records: Use partitioned tables, nlist=3000 |
| Low cost (Minimal memory usage) |
IVF_PQ | For 1 million data records: nlist = 1000, m = vector dimensionality / 2 For 10 million data records: nlist = 3000, m = vector dimensionality / 2 For hundreds of millions of data records: Use a partitioned table, nlist = 3000, m = vector dimensionality / 2 |
To balance the number of cluster centers and the amount of data each center contains, it is generally recommended to set nlist to the square root of the data volume. For example, for 10 million data points, it is recommended to set nlist to around 3000. When using IVF_PQ, it is recommended to set the m parameter to half of the vector dimension (dim).
Notice
- IVF_PQ is a quantization algorithm with high compression ratio, and its recall upper limit may be lower for low-dimensional vectors. To avoid performance loss, it is recommended to use IVF_PQ on vectors of 128 dimensions or higher.
- For scenarios where data volume is expected to grow, it is recommended to set parameters based on the final data volume.
Ten-million-level data volume (combined with partitioned tables)
Taking 10 million 768-dimensional vector data records as an example (using the construction parameters from the previous table), the following table further explains memory usage and recall tuning:
Memory usage:
Index type |
Index parameters |
Memory usage (construction cost/resident cost) |
|---|---|---|
| IVF | distance=l2, nlist=3000 | 2.7 GB/ 10.5 MB |
| IVF_PQ | distance=l2, nlist=3000, m=384 | 4.0 GB/ 1.3 GB |
| IVF_PQ | distance=cosine, nlist=3000, m=384 | 2.7 GB/ 11.4 MB |
In the table, "Construction overhead" refers to the temporary memory occupied during index creation, which is released after construction is complete. "Resident overhead" refers to the memory continuously occupied by the IVF vector index after construction is complete.
For IVF_PQ indexes, if distance = l2 is chosen, more resident memory will be occupied due to the need to store additional precomputed results. In contrast, using distance = inner_product or cosine typically results in lower resident memory consumption. Therefore, in practical applications, it is generally recommended to prioritize choosing inner_product or cosine as the distance type to optimize memory resources.
Recall tuning:
By adjusting the nprobes parameter, you can increase recall through more vector computations, but this will correspondingly reduce query performance. For different TopN values, you can set the parameter to the recommended values in the following table. If you need to further improve recall, you can set the parameter values larger.
Notice
Recall is directly related to data characteristics. The following table provides recommended values for achieving a recall of around 0.95 on a standard 768-dimensional dataset.
TopN |
nprobes |
|---|---|
| Top10 | 1 |
| Top100 | 20 |
Data volume in hundreds of millions (using partitioned tables)
When the vector data volume reaches hundreds of millions or more, it is strongly recommended to use partitioned tables combined with IVF indexes. As the data scale and the nlist parameter increase, the query overhead for a single IVF index also increases significantly. By splitting the data into multiple partitions and building smaller-scale IVF indexes for each partition, you can effectively reduce the load per query and further improve overall query performance and recall through parallel queries across partitions.
For multi-partitioned table scenarios, since IVF indexes are local indexes and an IVF index is built independently for each partition, it is recommended to calculate the value of nlist based on the average data volume per partition. For example, for 100 million 768-dimensional vectors divided into 10 partitions, each partition contains approximately 10 million data points, and nlist is recommended to be set to sqrt(10 million) = 3162.
Using 100 million 768-dimensional vector data as an example (with the construction parameters from the table above), additional information on memory usage and recall tuning is provided:
Memory usage:
For multi-partitioned scenarios, since IVF indexes are local indexes and each partition independently builds and maintains an IVF index, the total resident memory required must be summed across all partitions. For example, if a single IVF index requires 10.5 MB of resident memory as shown in the table below, then for 10 partitions, the total resident memory would be approximately 10.5 × 10 = 105 MB.
Index type |
Index parameters |
Memory usage (construction cost/resident cost) |
|---|---|---|
| IVF | distance=l2, nlist=3000 | 2.7 GB/ 10.5 * 10 MB |
| IVF_PQ | distance=l2, nlist=3000, m=384 | 4.0 GB/ 1.3 * 10 GB |
| IVF_PQ | distance=cosine, nlist=3000, m=384 | 2.7 GB/ 11.4 * 10 MB |
In the table, Construction overhead refers to the temporary memory occupied during index creation, which is released after construction is complete; Resident overhead refers to the memory continuously occupied by the IVF vector index after construction is completed.
Recall tuning:
By adjusting the nprobes parameter, you can increase recall by performing more vector calculations, but this will correspondingly reduce query performance. For different TopN values, you can set the parameter to the recommended values in the following table. If you need to further improve recall, you can set the parameter value higher.
In partitioned table scenarios, since each partition independently executes IVF index queries, if a user's query involves multiple partitions, the TopN results are retrieved from each partition separately, and then all partition results are aggregated and re-ranked. This not only improves the overall search accuracy but also typically results in a higher actual recall than in single-partition scenarios. Therefore, in multi-partitioned tables, you can appropriately reduce the nprobes parameter setting to achieve a recall comparable to that of a single-partition table.
Notice
Recall is directly related to data characteristics. The following table provides recommended values for achieving a recall of around 0.95 on a standard 768-dimensional dataset.
TopN |
nprobes |
|---|---|
| Top10 | 1 |
| Top100 | 10 |
References
- For detailed parameter descriptions and tuning, see HNSW series indexes and IVF series indexes.