User guide for sparse vector indexes

Sparse vector indexes (SINDI)

A sparse vector stores only its non-zero elements and their positions, in {index:value} form. For example, a one-million-dimensional vector with only 100 non-zero elements needs just 100 index-value pairs, which is far cheaper to store and compute than a dense representation.

Sparse vector indexes fit the following workloads:

• Text retrieval: Models such as SPLADE and BGE-M3 produce text embeddings that are typically high-dimensional sparse vectors.

• Recommendation systems: Sparse vectors built from user-item interaction features.

• Advertising features: Sparse feature vectors built from user profiles and ad attributes.

HGraph indexes

HGraph is the dense vector index in Hologres. It uses a graph-based algorithm and targets ANN search over low-dimensional dense vectors. For more information, see Work with HGraph for vector search.

Choose an index type

Recommendations:

• Choose a sparse vector index if your data comes from sparse encoders such as SPLADE or BGE-M3, or if your vectors have extremely high dimensions but mostly zero elements, as with text embeddings and recommendation features.

• Choose an HGraph index if your data consists of low-dimensional dense vectors such as image embeddings or semantic vectors, or if you need Cosine distance, Euclidean distance, or a disk-based storage mode.

Create an index

Create a SINDI index on a sparse vector column either when you create the table or after the table exists.

Syntax:

-- Create the index when you create the table
CREATE TABLE <TABLE_NAME> (
    <COLUMN_NAME> SPARSEVECTOR,
    ...
) WITH (
    vectors = '{
    "<COLUMN_NAME>": {
        "algorithm": "Sindi",
        "distance_method": "InnerProduct",
        "builder_params": {
            "use_reorder": <TRUE|FALSE>,
            "term_id_limit": <INTEGER>,
            "doc_prune_ratio": <FLOAT>,
            "window_size": <INTEGER>
        }
    }}'
);

-- Create the index after the table exists
ALTER TABLE <TABLE_NAME> SET (
    vectors = '{
    "<COLUMN_NAME>": {
        "algorithm": "Sindi",
        "distance_method": "InnerProduct",
        "builder_params": {
            "use_reorder": <TRUE|FALSE>,
            "term_id_limit": <INTEGER>,
            "doc_prune_ratio": <FLOAT>,
            "window_size": <INTEGER>
        }
    }}'
);

Data type:

Sparse vector columns use the SPARSEVECTOR data type. A SPARSEVECTOR value is an unordered set of index-value pairs written in the text format '{index:value, index:value, ...}', where index positions are integers that start at 1 and must be smaller than the term_id_limit value, and values are floating-point numbers.

Example: '{1:0.1, 50:0.8, 200:0.3}'

Parameters:

The following example creates the index together with the table:

CREATE TABLE sparse_test (
    id INT PRIMARY KEY,
    vec SPARSEVECTOR
) WITH (
    vectors = '{
    "vec": {
        "algorithm": "Sindi",
        "distance_method": "InnerProduct",
        "builder_params": {
            "use_reorder": true,
            "term_id_limit": 1000000,
            "doc_prune_ratio": 0.4,
            "window_size": 50000
        }
    }}'
);

Modify an index

Use ALTER TABLE to change the build parameters of a sparse vector index.

-- Change the document pruning ratio
ALTER TABLE sparse_test SET (
    vectors = '{
    "vec": {
        "algorithm": "Sindi",
        "distance_method": "InnerProduct",
        "builder_params": {
            "use_reorder": true,
            "term_id_limit": 1000000,
            "doc_prune_ratio": 0.6,
            "window_size": 50000
        }
    }}'
);

New parameters take effect for indexes that are built after the change. To apply them sooner, trigger compaction manually:

VACUUM <SCHEMA_NAME>.<TABLE_NAME>;

For more information about compaction, see Compact data files.

Drop an index

Set vectors to an empty JSON object to drop the sparse vector index.

ALTER TABLE sparse_test SET (vectors = '{}');

View an index

Query the hologres.hg_table_properties system table to view the configuration of a sparse vector index.

SELECT property_value
FROM hologres.hg_table_properties
WHERE table_name = 'sparse_test'
  AND property_key = 'vectors';

Approximate search

Use approx_inner_product_distance with ORDER BY ... DESC LIMIT k to run a top-k approximate search. The query uses the SINDI index automatically.

SELECT id, approx_inner_product_distance(vec, '{1:0.1, 2:0.2, 3:0.3}') AS dist
FROM sparse_test
ORDER BY dist DESC
LIMIT 5;

Exact search

Use inner_product_distance to run an exact search with brute-force computation. This function bypasses the index, so it suits small datasets and cases that require exact results.

SELECT id, inner_product_distance(vec, '{1:0.1, 2:0.2, 3:0.3}') AS dist
FROM sparse_test
ORDER BY dist DESC
LIMIT 5;

Verify that the index is used

Run EXPLAIN ANALYZE to inspect the execution plan and confirm whether the query uses the sparse vector index.

EXPLAIN ANALYZE
SELECT product_id,
       approx_inner_product_distance(feature, '{1:0.1, 50:0.7}') AS dist
FROM product_features
ORDER BY dist DESC
LIMIT 5;

If Vector Filter appears for the sparse vector column in the execution plan, the query uses the sparse vector index.

Vector Filter: VectorCond => KNN: '5'::bigint distance_method: approx_inner_product_distance

Tune build parameters

doc_prune_ratio is the build parameter with the largest impact on query performance. Increasing it speeds up queries noticeably, at the cost of some recall.

Setting use_reorder to true improves query performance while keeping the recall rate high. Enable it for high-precision workloads.

Tune query parameters

Set the following GUC parameters to balance performance and recall at query time.

Example:

-- Set the query parameters
SET hg_vector_sindi_n_candidate = 500;
SET hg_vector_sindi_query_prune_ratio = 0.8;

-- Run the query
SELECT id, approx_inner_product_distance(vec, '{1:0.1, 2:0.2}') AS dist
FROM sparse_test
ORDER BY dist DESC
LIMIT 10;

-- Reset to the default values
RESET hg_vector_sindi_n_candidate;
RESET hg_vector_sindi_query_prune_ratio;

Recommended configurations by scenario

The following recommendations are measured on the sparse-full dataset, which contains 8.84 million vectors with 30,109 dimensions and 99.6% sparsity.