Vector Database Best Practices Distilled by OceanBase over Three Years

Posted on In Views: Word count in article: 2.9k Reading time ≈ 10 mins.
Two OceanBase vector database experts distill three years of hands-on PoC experience, systematically covering vector index selection, memory and disk resource estimation, partition design, index parameter configuration, hybrid query tuning, performance validation, and common performance troubleshooting methods.

Prologue

In the AI era, all kinds of AI Infra depend on the storage and retrieval of vector data. And when running a PoC for a vector scenario, you have to recompute the memory, re-pick the index, and re-tune the parameters every single time…

OceanBase has two experts who’d rather remain anonymous — Xufeng and Gehao — who over the past three years have supported countless vector database PoCs (Proofs of Concept) for AI scenarios, with extraordinarily rich experience in vector database operations and tuning.

This article is the first time they’ve taken out the vector database operations experience they’ve kept under wraps to share with everyone on the community WeChat account. It covers every aspect you need to consider when using a vector database.

(This article is worth bookmarking for when you need it.)

Cover image for OceanBase's three years of vector database PoC experience

You’re welcome to follow the OceanBase community WeChat account “Lao Ji’s Tech Talk.”

Entry point to follow the OceanBase community WeChat account "Lao Ji's Tech Talk"

This article covers: vector index selection, memory and CPU planning, disk space estimation, partition design, index parameter configuration, hybrid query tuning, performance validation methods and measured data, common performance troubleshooting, and more. It applies to vector database PoC evaluation, vector index type selection, tenant resource planning, and query performance tuning.

The recommended prerequisite reading for this article is “An Introductory Look at Vector Databases”.

Part One: Vector Build & Design Practices

[Selection & Planning] This part makes clear: which index to choose for which scenario, how to compute memory/CPU/disk, how to build tables, and how to configure parameters.

1. Index Selection

The conclusion first: selection isn’t based on intuition, but on two data points — data scale and memory budget.

The quick decision tree is as follows:

A vector index selection decision tree based on data scale and memory budget

The decision logic in the diagram above, briefly: data scale < 10 million → no partitioning; 10 million ~ 500 million and > 500 million both get partitioned (10 million per partition). For < 500 million with ample memory, choose HNSW or HNSW_SQ; with limited memory, choose HNSW_BQ; for > 500 million, always choose HNSW_BQ. When memory is extremely low, choose IVF by dimension — for dimension < 384 choose IVF_FLAT, for ≥ 384 choose IVF_PQ.

Note: HNSW_BQ requires dimension ≥ 384, and IVF_PQ requires dimension ≥ 128. The 10-million-vectors-per-partition figure is not a hard requirement; for large data volumes, it’s generally recommended to keep a single partition between 5 million and 20 million vectors, and a single partition should never exceed 50 million vectors.

Case Scenario Recommended Approach Detailed Section
384-dim, hundreds of millions, geohash filtering HNSW_SQ or HNSW_BQ, partitioned by geohash Chapters 4 and 6
1024-dim, billions, multi-dimensional filtering HNSW_BQ, skill_id as level-1 partition, doc_id as level-2 partition Chapter 4
768-dim, tens of millions to hundreds of millions HNSW_SQ or HNSW_BQ Chapter 1
Any dimension, billions, append-only (no deletes) IVF_PQ Chapters 1 and 2

1.1. The HNSW Family

HNSW-family indexes are in-memory indexes. During queries they must stay resident in memory — note that this is not a cache, and cannot be temporarily swapped out the way a KV Cache can. During a rebuild, there’s a window where both the old and new indexes exist in memory.

Type Memory (relative to HNSW) Recall Query Performance Applicable Scenario
HNSW_SQ 1/4 ~ 1/3 Slightly below HNSW Highest First choice at the ten-million scale; the sweet spot between performance and memory
HNSW_BQ 1/20 Below SQ; needs refine compensation Medium-high Hundreds of millions and above; the only choice when memory is limited

If memory cost allows, HNSW_SQ is the best choice for most scenarios. HNSW_BQ’s extreme quantization (RapidQ) makes the index itself tiny, but queries have to fetch the original vectors from disk to re-rank, so disk performance has some impact on its query performance — the larger the TopK, the more noticeable the impact.

Imagery of HNSW-family in-memory index characteristics and applicable scenarios

1.2. The IVF Family

IVF indexes stay resident on disk with extremely low memory usage, making them suitable for scenarios with an extremely limited memory budget.

Type Query Performance Build Speed Recall Applicable Scenario
IVF_FLAT Slower Fast High Tight memory but moderate dimension (<384)
IVF_PQ Faster than FLAT Slow Slightly lower High dimension (≥384), extremely tight memory

If you’re going to compare performance across several different vector databases, you absolutely must distinguish index types: the RT of a disk index is typically several times higher than that of an in-memory index — this is the same across all vendors. You can’t compare performance simply by index name; you have to compare based on the actual in-memory / on-disk index type.

Imagery comparing IVF on-disk index versus in-memory index performance

Note: Before OceanBase version 4.6.0, IVF-family indexes did not support partitioned heap tables. When IVF_PQ uses the l2 distance, it needs to additionally cache precomputed results, so for large-scale scenarios prefer the cosine distance. For IVF-family indexes, writing data after the index is created with the table is equivalent to a brute-force search; you should rebuild the IVF index after the data is fully written.

2. Resource Planning

The thing PoCs dread most when proposing a plan is being asked back, “Is the memory enough? How many machines do we buy?” — this chapter gives you the most intuitive calculation.

Imagery for vector database memory and CPU resource planning

2.1. Memory Estimation and Planning

OceanBase ships a vector index memory estimation function dbms_vector.index_vector_memory_advisor, which can compute the required vector memory based on index type, data scale, and parameters:

1
2
3
4
5
6
-- 100 million 384-dim vectors, at most 10 million per partition
SELECT dbms_vector.index_vector_memory_advisor(
  'HNSW_BQ', 100000000, 384, 'FLOAT32',
  'M=32, TYPE=HNSW_BQ, ef_construction=400, distance=cosine, refine_type=SQ8',
  10000000
);

The parameters, in order, are: index type, total data volume, dimension, data type, index parameters, and maximum rows per partition.

1024-dim (the most common — text-embedding-3-large, bge-large, etc.):

Data Scale Recommended Index Partitions Vector Memory (single replica)
1 million HNSW_SQ No partitioning 2.7 GB
5 million HNSW_SQ No partitioning 14.2 GB
10 million HNSW_SQ No partitioning 28.4 GB
30 million HNSW_BQ 3 partitions 34.4 GB build, 17.2 GB runtime
100 million HNSW_BQ 10 partitions 74.6 GB build, 57.4 GB runtime
1 billion HNSW_BQ 100 partitions 591.2 GB build, 574.0 GB runtime
1 billion IVF_PQ 100 partitions 6.3 GB build, 1.9 GB runtime

768-dim (text-embedding-3-small, bge-base, etc.):

Data Scale Recommended Index Partitions Vector Memory (single replica)
10 million HNSW_SQ No partitioning 22.6 GB
100 million HNSW_BQ 10 partitions 67.8 GB build, 53.8 GB runtime
1 billion HNSW_BQ 100 partitions 552.2 GB build, 538.2 GB runtime
1 billion IVF_PQ 100 partitions 4.8 GB build, 1.4 GB runtime

For the same 1 billion vectors at 10 million per partition: HNSW_BQ peaks at 591.2 GB during build, while IVF_PQ uses 1.9 GB at runtime — a memory gap of roughly 300x. This is why the memory budget determines index selection.

Imagery comparing the memory footprint gap between HNSW_BQ and IVF_PQ

Note: The vector memory estimation function computes the memory usage for a single replica. Tenant memory = vector memory ÷ ob_vector_memory_limit_percentage (default 50%).

HNSW Memory in Detail

Index Type Build-time Memory Runtime Resident Notes
HNSW 76.3 GB 76.3 GB Fully resident, never released
HNSW_SQ 22.6 GB 22.6 GB Resident after quantization, about 1/3 of HNSW
HNSW_BQ 22.6 GB 5.4 GB Needs an SQ cache during build; only the BQ index remains afterward

IVF Memory in Detail

Index Type Index Parameters Build-time Memory Runtime Resident
IVF_FLAT nlist=3000 3.4 GB 13.2 MB
IVF_PQ (cosine) nlist=3000, m=384 3.4 GB 14.3 MB
IVF_PQ (l2) nlist=3000, m=384 5.0 GB 1.7 GB

Under the l2 distance, IVF_PQ’s resident memory is 120x that of cosine — because l2 needs to additionally cache precomputed results.

2.2. The Impact of CPU and NUMA on Vector Queries

Compared with ordinary SQL queries, the bottleneck of vector search lies mainly in memory bandwidth, as well as the SIMD instruction set the CPU supports. More cores isn’t always better: especially beyond 64 cores, the memory bandwidth per core shrinks and L3 cache contention gets fierce. More cores isn’t always better. The bottleneck of vector search is memory bandwidth and SIMD instructions; beyond 64 cores, cross-NUMA access and L3 cache contention can cause performance to drop rather than rise.

2.3. Disk Space and Query Performance

Index Type Disk Estimate
HNSW ≈ original vector size × 1.2
HNSW_SQ ≈ original vector size × 1.2 / 3
HNSW_BQ ≈ original vector size × 1.2 / 20
IVF_FLAT ≈ original vector size
IVF_PQ ≈ original vector size / 8

Formula for the original vector size: rows × dimension × 4 bytes, e.g., 100 million 384-dim float32 = 144 GB.

Overall, the degree to which each index algorithm is affected by disk performance: HNSW_SQ < HNSW_BQ < IVF/IVF_PQ.

3. Important Configuration Items

Three parameters — tuning them or not can be the difference between doubling QPS or not.

1
2
3
4
5
6
7
8
-- 1. Parallel-build sampling precision (5000 at the ten-million scale, 10000 at the hundred-million scale, 100000 at the billion scale and above)
ALTER SYSTEM SET _px_object_sampling = 10000;

-- 2. Vector index memory percentage (default adaptive 50%; if memory is short, manually raise to 60%)
ALTER SYSTEM SET ob_vector_memory_limit_percentage = 50;

-- 3. Query strategy (default LATENCY_FIRST since 4.6.0; default RECALL_FIRST on older versions)
ALTER SYSTEM SET ob_vector_search_strategy = 'LATENCY_FIRST';

4. Schema and Partition Design

Partition if both of the following hold: data volume is at the ten-million scale or above, and the query condition contains a clear scalar column that can be used to prune partitions. Target per-partition: 5 million ~ 20 million rows. Conclusion: as long as the 5–20 million constraint is met, fewer partitions is better.

Total Data Volume Number of Partitions Per-partition Volume
50 million 5-10 5-10 million
100 million 10-20 5-10 million
450 million 25-45 10-18 million
1 billion 50-100 10-20 million

Level-2 partitioning (two-dimensional filtering, e.g., skill_id + doc_id):

1
2
3
4
5
6
CREATE TABLE iop_knowledge (
  _id varchar(200), doc_id varchar(100), skill_id varchar(100),
  search_vec vector(1024),
  UNIQUE KEY idx_id (_id, skill_id, doc_id) LOCAL
) ORGANIZATION HEAP partition by key(skill_id) partitions 30
subpartition by key(doc_id) subpartitions 4;

“Sparse” Vector Columns

Measured: a main table with 900 million rows where only 26 million rows had a value in the 768-dim column. Querying on the large table took 21ms of RT; after splitting it into a small table, it dropped to under 3ms. A query on the 900-million-row main table took 21ms; after splitting the non-null 26 million rows into a dedicated small table, it dropped to 3ms — a 7x latency reduction. When a vector column is sparse, the hidden cost of scanning a large partitioned table is far higher than you’d imagine.

5. Index Creation and Parameter Configuration

It’s strongly recommended to create the index after the full data has been imported. Set the degree of parallelism to 2x the tenant’s CPU.

1
2
3
4
5
6
7
8
9
10
11
12
-- HNSW_BQ
CREATE /*+ PARALLEL(64) */ VECTOR INDEX idx_vec ON htl_image_recall(picturevector)
WITH (distance=cosine, type=hnsw_bq, m=32, ef_construction=400);

-- HNSW_SQ
CREATE /*+ PARALLEL(64) */ VECTOR INDEX idx_vec ON htl_image_recall(picturevector)
WITH (distance=cosine, type=hnsw_sq, m=32, ef_construction=400);

-- IVF_PQ
CREATE /*+ PARALLEL(64) */ VECTOR INDEX idx_vec ON htl_image_recall(picturevector)
WITH (distance=cosine, type=ivf_pq, lib=OB, m=192, nlist=3000, nbits=8,
      sample_per_nlist=256) BLOCK_SIZE=1048576;

HNSW-Family Parameters

Parameter Default Range Purpose
distance required l2 / cosine / inner_product Most embeddings use cosine
type required hnsw / hnsw_sq / hnsw_bq —
m 16 [5, 64] Max neighbors per node
ef_construction 200 [5, 1000] Candidate-set size during build
ef_search 64 [1, 16000] Candidate-set size during query
refine_k 4.0 [1.0, 1000.0] BQ only; re-ranking ratio
refine_type sq8 sq8 / fp32 BQ only

Million scale: HNSW_SQ(m=16, ef_construction=200, ef_search=240); HNSW_BQ(m=16, ef_construction=200, ef_search=240, refine_k=4)

Ten-million scale: HNSW_SQ(m=32, ef_construction=400, ef_search=350); HNSW_BQ(m=32, ef_construction=400, ef_search=1000, refine_k=10); IVF_PQ(nlist=3000, m=dim/2, nbits=8, nprobes=20)

Hundred-million scale (partitioned table): set parameters based on the maximum data volume of a single partition.

Incremental and Rebuild

Vectors written incrementally after the index is created are immediately queryable, but the incremental portion is not quantization-compressed and consumes extra memory. HNSW-family indexes automatically trigger a background rebuild when the increment reaches 20%; for IVF, after new additions exceed 30%, you need to manually run CALL dbms_vector.rebuild_index().

6. Querying and Tuning

Without scalar filtering:

1
2
SELECT id, cosine_distance(embedding, @query_vector) AS distance
FROM htl_image_recall ORDER BY distance APPROXIMATE LIMIT 100;

APPROXIMATE is mandatory (the shorthand APPROX also works).

Hybrid query (geohash + vector):

1
2
3
4
SELECT id, picturename, cosine_distance(picturevector, @query_vector) AS distance
FROM htl_image_recall
WHERE geohash IN ('gcq2j', 'u10kk', 'wvkut')
ORDER BY distance APPROXIMATE LIMIT 100;

A diagram of geohash scalar filtering combined with a vector hybrid query

The Recall-vs-Latency Trade-off

ef_search (HNSW family) and nprobes (IVF) are the core knobs.

HNSW family (768-dim, ten-million scale, target Recall ≈ 0.95): Top10 ef_search=100; Top100 (HNSW_SQ) ef_search=350; Top100 (HNSW_BQ) ef_search=1000, refine_k=10

IVF single partition (ten-million scale): Top10 nprobes=1; Top100 nprobes=20; Top1000 nprobes=90

7. Performance Validation

Four core metrics: QPS, average RT, P95/P99 RT, and recall.

Imagery for QPS, RT, and recall performance validation metrics

Recall testing: prepare 100+ query vectors, run both an exact search and an approximate search, and compare the results:

1
2
3
4
5
6
7
-- Exact search (ground truth)
SELECT /*+ PARALLEL(32) */ id, cosine_distance(picturevector, @query_vector) AS distance
FROM htl_image_recall WHERE geohash IN ('gcq2j', 'u10kk') ORDER BY distance LIMIT 100;

-- Approximate search
SELECT id, cosine_distance(picturevector, @query_vector) AS distance
FROM htl_image_recall WHERE geohash IN ('gcq2j', 'u10kk') ORDER BY distance APPROXIMATE LIMIT 100;

During load testing, it’s best to do a major freeze and warm-up to reduce the impact of table lookups and disk reads on query performance.

Measured Performance Reference:

Million scale, 768-dim (m=16, ef_construction=200, Top100, ef_search=240):

Index Type QPS Recall Memory
HNSW 3475 0.9499 7.3 GB
HNSW_SQ 5599 0.9468 2.1 GB
HNSW_BQ (refine_k=4) 3113 0.9278 0.4 GB

Ten-million scale, 768-dim (m=32, ef_construction=400, Top100):

Index Type QPS Recall ef_search
HNSW 2637 0.9574 350
HNSW_BQ (refine_k=10) 857 0.9531 1000

450 million, 384-dim hybrid query (HNSW_SQ, m=32, 45 partitions, 20 concurrent):

Number of geohash filters QPS Average RT
10 810 24ms
20 717 28ms
40 488 40ms

8. Vector Query Performance Troubleshooting Handbook

When problems arise, don’t randomly tweak parameters. Follow this table’s “symptom → cause → action” flow, and 90% of issues can be pinned down in 10 minutes.

Imagery for the vector query performance troubleshooting flow

Symptom Most Likely Cause Action
Second-level latency No partition pruning EXPLAIN and check the partitions field
Second-level latency APPROXIMATE not added EXPLAIN to confirm whether the vector index was used
P99 >> P50 Some partition indexes not loaded into memory Check GV$OB_VECTOR_MEMORY
RT too high Many NULLs in the vector column, large-table scan overhead Split non-null rows into a small table; measured RT dropped from 21ms to 3ms
Low recall ef_search / nprobes too small Gradually increase ef_search or nprobes
Slow hybrid query Scalar field not indexed Build a scalar index
Slow hybrid query Auto strategy chose wrong Specify manually with a hint

After OceanBase 4.3.5 BP3, the GV$OB_VECTOR_MEMORY view is available:

1
2
3
4
5
6
7
SELECT b.zone, a.svr_ip, a.svr_port, a.tenant_id,
  ROUND(a.vector_mem_hold/1024/1024/1024,2) AS hold_gb,
  ROUND(a.vector_mem_used/1024/1024/1024,2) AS used_gb,
  ROUND(a.vector_mem_limit/1024/1024/1024,2) AS limit_gb
FROM GV$OB_VECTOR_MEMORY a JOIN gv$ob_units b
  ON a.tenant_id = b.tenant_id AND a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port
ORDER BY b.zone, used_gb DESC;

10. Vector Index Creation

Use __all_virtual_ddl_diagnose_info to confirm the index creation status, and gv$session_longops to view in-progress index creation. Use the real_parallelism keyword to confirm the degree of parallelism used when creating the vector index.

Example of collecting a traceid:

1
obdiag gather log --from='2026-03-16 21:00:00' --to='2026-03-17 17:00:00' --scope=all --grep='YB420A80D369-000649E8EDEED23D-0-0'

Final Thoughts

This guide is a distillation of experience from multiple real PoC projects. If this guide helped you, please forward it to colleagues and friends who also need “vector search”~

Closing imagery for the vector database best practices guide

Finally, here are the first two articles in the PoC experience series:

Cover of the first article in the PoC experience series

Cover of the second article in the PoC experience series

Add the OB community assistant to join the technical discussion group

Entry point to the OB community assistant technical discussion group

Generated QR code