ApsaraDB for SelectDB:HNSW

Method 1: Define the index when creating a table

The index is built automatically each time data is imported. After import completes, the ANN index is immediately available for queries.

CREATE TABLE sift_1M (
  id int NOT NULL,
  embedding array<float> NOT NULL COMMENT "",
  INDEX ann_index (embedding) USING ANN PROPERTIES(
      "index_type"="hnsw",
      "metric_type"="l2_distance",
      "dim"="128"
  )
) ENGINE=OLAP
DUPLICATE KEY(id) COMMENT "OLAP"
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
  "replication_num" = "1"
);

INSERT INTO sift_1M
SELECT *
FROM S3(
  "uri" = "https://selectdb-customers-tools-bj.oss-cn-beijing.aliyuncs.com/sift_database.tsv",
  "format" = "csv");

Method 2: Build the index after import

Create the table and import data without an index, then build the index separately. This approach is faster for large imports and gives you control over when index building happens.

Step 1: Create the table and import data.

CREATE TABLE sift_1M (
  id int NOT NULL,
  embedding array<float> NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(id) COMMENT "OLAP"
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
  "replication_num" = "1"
);

INSERT INTO sift_1M
SELECT *
FROM S3(
  "uri" = "https://selectdb-customers-tools-bj.oss-cn-beijing.aliyuncs.com/sift_database.tsv",
  "format" = "csv");

Step 2: Run full compaction before building the index.

Building the index on fully merged segments produces a more stable recall rate and reduces write amplification from repeated index rebuilds during compaction.

Step 3: Define and build the index.

CREATE INDEX registers the index definition on the table but does not build it on existing data.

CREATE INDEX idx_test_ann ON sift_1M (`embedding`) USING ANN PROPERTIES (
  "index_type"="hnsw",
  "metric_type"="l2_distance",
  "dim"="128"
);

Verify that the index size is still 0 before building:

SHOW DATA ALL FROM sift_1M

+-----------+-----------+--------------+----------+----------------+---------------+----------------+-----------------+----------------+-----------------+
| TableName | IndexName | ReplicaCount | RowCount | LocalTotalSize | LocalDataSize | LocalIndexSize | RemoteTotalSize | RemoteDataSize | RemoteIndexSize |
+-----------+-----------+--------------+----------+----------------+---------------+----------------+-----------------+----------------+-----------------+
| sift_1M   | sift_1M   | 1            | 1000000  | 170.001 MB     | 170.001 MB    | 0.000          | 0.000           | 0.000          | 0.000           |
|           | Total     | 1            |          | 170.001 MB     | 170.001 MB    | 0.000          | 0.000           | 0.000          | 0.000           |
+-----------+-----------+--------------+----------+----------------+---------------+----------------+-----------------+----------------+-----------------+

BUILD INDEX runs asynchronously and builds the index on all existing data:

BUILD INDEX idx_test_ann ON sift_1M;

Monitor build progress:

SHOW BUILD INDEX WHERE TableName = "sift_1M"

+---------------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
| JobId         | TableName | PartitionName | AlterInvertedIndexes                                                                                                               | CreateTime              | FinishTime              | TransactionId | State    | Msg  | Progress |
+---------------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
| 1763603913428 | sift_1M   | sift_1M       | [ADD INDEX idx_test_ann (`embedding`) USING ANN PROPERTIES("dim" = "128", "index_type" = "hnsw", "metric_type" = "l2_distance")],  | 2025-11-20 11:14:55.253 | 2025-11-20 11:15:10.622 | 126128        | FINISHED |      | NULL     |
+---------------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+

Drop an index

Drop an index when tuning hyperparameters, which requires testing different parameter combinations.

ALTER TABLE sift_1M DROP INDEX idx_test_ann;

Understand the hyperparameters

All three parameters trade accuracy for speed or memory.

SelectDB defaults: max_degree = 32, ef_construction = 40, ef_search = 32.

Build-phase internals

When a vector is inserted into the HNSW index:

1. Layer assignment: the vector is assigned to one or more layers using a geometric distribution — higher layers are sparser and serve as fast navigation shortcuts.

2. Candidate search: in each layer, HNSW runs a breadth-first local search using a candidate queue of length ef_construction to find the best neighbors.

3. Connection limit: the number of neighbors is capped at max_degree to keep the graph structure bounded.

Query-phase internals

1. Coarse search: starting from the top layer, a greedy search descends layer by layer to quickly reach the target region.

2. Fine search: in layer 0, HNSW uses a candidate queue of length ef_search for comprehensive neighborhood expansion.

Select hyperparameters

Different parameter combinations can achieve the same recall rate. The following table shows results from the SIFT 1M dataset (1 million 128-dimensional vectors) to illustrate the trade-off space.

For a target recall@100 > 95%, any of these combinations work:

To find the optimal combination for your dataset, test multiple parameter combinations in parallel using a multi-index table:

1. Create a table named table_multi_index with two or three vector fields but no indexes.

2. Import data using Stream Load or another import method.

3. Create indexes on all vector fields using CREATE INDEX and BUILD INDEX, with different parameters for each field.

4. Measure recall rate on each field to identify the best combination.

Segment size and recall rate

SelectDB stores data in a hierarchical structure: table → tablets → rowsets → segments. Vector indexes operate at the segment level — each segment has its own HNSW index, and the recall rate depends on how many rows that segment covers.

A given set of hyperparameters has a limited effective data range. When a segment grows beyond a certain size, recall rate degrades. The following table provides empirical values for the SIFT 1M dataset:

Segment size is controlled by write_buffer_size and vertical_compaction_max_segment_size in the BE configuration. To check how many segments your table has, run SHOW TABLETS FROM table and follow the URL in the output.

Impact of compaction

Compaction can merge smaller segments into larger ones. If the resulting segments exceed the effective range of your current hyperparameters, recall rate drops. Run full compaction before BUILD INDEX — building on fully merged segments gives a more stable recall rate and reduces write amplification from repeated index rebuilds.

Cold loading

SelectDB's ANN index is built on Faiss, Meta's open-source vector library. An HNSW index can accelerate queries only after its complete graph structure is loaded into memory. Before running high-concurrency queries, perform a cold query first to ensure that the index files for all relevant segments are loaded into memory. Skipping this step causes significantly higher latency until all files are loaded.

Memory planning

An HNSW index without quantization requires approximately 1.3x the raw memory size of the vectors it covers. Use this formula to estimate memory requirements:

Index memory ≈ num_vectors × dim × 4 bytes × 1.3

Reference figures for common configurations:

If BE nodes run low on memory, the index is read from disk rather than memory, causing a sharp drop in query performance. Size your BE nodes to hold the full index in memory.

Reduce memory with quantization

Vector quantization compresses the index to reduce memory usage, at the cost of a small reduction in recall rate. Quantization options — including the trade-offs between memory savings and recall accuracy — are covered in the vector quantization topic.