Hologres:Work with HGraph for vector search

Create an index

Create a vector index when you create a table. Vectors in Hologres are stored as float4 arrays. The vector dimension corresponds to the length of the one-dimensional array (array_length).

CREATE TABLE <TABLE_NAME> (
    <VECTOR_COLUMN_NAME> float4[] CHECK (array_ndims(<VECTOR_COLUMN_NAME>) = 1 AND array_length(<VECTOR_COLUMN_NAME>, 1) = <DIM>)
)
WITH (
    vectors = '{
    "<VECTOR_COLUMN_NAME>": {
        "algorithm": "<ALGORITHM>",
        "distance_method": "<DISTANCE_METHOD>",
        "builder_params": {
            "<BUILDER_PARAMETERS_NAME>": <VALUE>
            [, ...]
        }
    }
    [ , "<VECTOR_COLUMN_NAME_2>": { ... } ]
  }'
);

Table parameters

The vectors parameter accepts a JSON-formatted string. At the top level, only one vector_column_name key is allowed to specify the column for which to build the index. The value of that key is a JSON object with the following fields:

Distance methods and required sort order

Builder parameters

Modify an index

ALTER TABLE <TABLE_NAME>
SET (
    vectors = '{
    "<VECTOR_COLUMN_NAME>": {
        "algorithm": "<ALGORITHM>",
        "distance_method": "<DISTANCE_METHOD>",
        "builder_params": {
            "<BUILDER_PARAMETERS_NAME>": <VALUE>
            [, ...]
        }
    }
  }'
);

Delete an index

-- Delete vector indexes for all columns in the table
ALTER TABLE <TABLE_NAME>
SET (
    vectors = '{}'
);

-- If col1 and col2 both have vector indexes and you need to delete only the index for col2,
-- use ALTER TABLE to retain only the index for col1.
ALTER TABLE <TABLE_NAME>
SET (
    vectors = '{
    "col1": { ... }
  }'
);

View an index

Query the hologres.hg_table_properties system table to view created vector indexes.

SELECT
    *
FROM
    hologres.hg_table_properties
WHERE
    table_name = '<TABLE_NAME>'
    AND property_key = 'vectors';

Approximate vs. exact retrieval

Hologres supports both approximate and exact vector retrieval. Only approximate retrieval functions can use the vector index to accelerate queries. The approximate function used in a query must match the distance_method of the index and respect the required sort order—otherwise the index is not used.

Vector distance functions

Distance functions do not support all-constant input parameters.

Verify that the index is used

Run EXPLAIN or EXPLAIN ANALYZE on your query to check whether the execution plan uses the vector index. If the plan contains Vector Filter, the index is active.

Example query:

SELECT
    id,
    approx_euclidean_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM
    feature_tb
ORDER BY
    distance
LIMIT 40;

Example execution plan (index is used):

Limit  (cost=0.00..182.75 rows=40 width=12)
  ->  Sort  (cost=0.00..182.75 rows=160 width=12)
        Sort Key: (VectorDistanceRef)
        ->  Gather  (cost=0.00..181.95 rows=160 width=12)
              ->  Limit  (cost=0.00..181.94 rows=160 width=12)
                    ->  Sort  (cost=0.00..181.94 rows=40000 width=12)
                          Sort Key: (VectorDistanceRef)
                          ->  Local Gather  (cost=0.00..91.53 rows=40000 width=12)
                                ->  Limit  (cost=0.00..91.53 rows=40000 width=12)
                                      ->  Sort  (cost=0.00..91.53 rows=40000 width=12)
                                            Sort Key: (VectorDistanceRef)
                                            ->  Project  (cost=0.00..1.12 rows=40000 width=12)
                                                  ->  Index Scan using Clustering_index on feature_tb  (cost=0.00..1.00 rows=40000 width=8)
                                                        Vector Filter: VectorCond => KNN: '40'::bigint distance_method: approx_euclidean_distance search_params: {NULL} args: {feature'{0.100000001,0.200000003,0.300000012,0.400000006}'::real[]}
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

When to use a vector index

For small data volumes—for example, tens of thousands of records—or when your instance has ample compute resources, skip the vector index and use brute-force (exact) computation. Add a vector index only when exact retrieval cannot meet your latency, throughput, or scalability requirements.

Keep in mind that vector indexes are inherently approximate:

• The recall rate cannot reach 100%.

• A query with LIMIT 1000 may return fewer than 1,000 results.

Choose a quantization method

base_quantization_type controls the trade-off between memory usage, query speed, and recall accuracy. Supported values are: sq8, sq8_uniform, fp16, fp32, and rabitq. Higher-precision quantization types use more memory but yield higher recall accuracy; lower-precision types use less memory and improve query speed at the cost of some accuracy.

For latency-sensitive workloads with full in-memory indexes, use sq8_uniform or rabitq. For large datasets with hybrid memory-disk indexes, use rabitq. When using rabitq, enabling use_reorder: true with precise_quantization_type: fp32 can improve recall accuracy.

Choose an index configuration for your workload

For a single table, single column, 768-dimensional vector index, use the following configurations as a starting point.

For multiple vector columns on the same table, reduce the per-shard row limits proportionally. Vector dimension size also affects these limits.

Full in-memory index example

CREATE TABLE feature_tb (
    id bigint NOT NULL,
    feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    table_group = 'test_tg_shard_4',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "sq8_uniform",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb": 4096
        }
    }
    }'
);

Hybrid memory-disk index example

CREATE TABLE feature_tb (
    id bigint NOT NULL,
    feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    table_group = 'test_tg_shard_4',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "rabitq",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "precise_io_type": "reader_io",
            "use_reorder": true,
            "max_total_size_to_merge_mb": 4096
        }
    }
    }'
);

Tune recall rate

The following index parameters achieve a default recall rate above 95% on the VectorDBBench dataset:

• base_quantization_type: rabitq or sq8_uniform

• precise_quantization_type: fp32

• max_degree: 64

• ef_construction: 400

At query time, the Grand Unified Configuration (GUC) parameter hg_vector_ef_search controls the candidate list size during retrieval. The default value is 80, which balances accuracy and resource usage.

To push recall above 99%, increase hg_vector_ef_search while keeping index parameters unchanged:

SET hg_vector_ef_search = 400;

To achieve 99.5%–99.7% recall, adjust both the index build parameters and the query parameter together. This increases query latency, query resource usage, index build time, and index build resource usage:

• max_degree: 96

• ef_construction: 500 or 600

• hg_vector_ef_search: 500 or 600

Set an appropriate shard count

More shards produce more manifest files, which reduces approximate query throughput. Follow these guidelines:

1. Size compute resources to your data. For 768-dimensional vectors: For other vector dimensions, see Recommended instance types for vector computing.

   • Full in-memory index: up to 5 million vectors per worker.

   • Hybrid memory-disk index: up to 100 million vectors per worker.

2. Set shard count equal to the number of workers. For a 64 CU instance, set shard_count to 4.

-- Create a table group with shard count = 4
CALL HG_CREATE_TABLE_GROUP ('test_tg_shard_4', 4);

CREATE TABLE feature_tb (
    id bigint NOT NULL,
    feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    table_group = 'test_tg_shard_4',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "sq8_uniform",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb": 4096
        }
    }
    }'
);

Vector and scalar hybrid queries

For queries that combine vector retrieval with scalar filter conditions, the typical patterns are:

Filter by a string column (e.g., organization or group ID)

A common use case is retrieving vectors within a specific group—for example, facial recognition data for students in a particular class.

SELECT <distance_function>(feature, '{1,2,3,4}') AS d
FROM feature_tb
WHERE uuid = 'x'
ORDER BY d
LIMIT 10;

Optimize by:

• Setting uuid as the distribution key. This ensures all data for the same filter value lands in the same shard, so the query touches only one shard.

• Setting uuid as the clustering key. This sorts data within files by uuid, reducing the scan range.

Filter by a time field

SELECT <distance_function>(feature, '{1,2,3,4}') AS d
FROM feature_tb
WHERE time_field BETWEEN '2020-08-30 00:00:00' AND '2020-08-30 12:00:00'
ORDER BY d
LIMIT 10;

Set time_field as the segment key. This lets Hologres quickly locate the relevant data files by time range.

Recommended table structure for hybrid queries

-- Remove time_field and its segment_key if you do not filter by time.
CREATE TABLE feature_tb (
    time_field timestamptz NOT NULL,
    uuid text NOT NULL,
    feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    distribution_key = 'uuid',
    segment_key = 'time_field',
    clustering_key = 'uuid',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "sq8_uniform",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb": 4096
        }
    }
    }'
);

Rebuild indexes using Serverless resources

Some property changes trigger compaction and index rebuilding, which can spike CPU usage. Handle these changes as follows:

Changes to `bitmap_columns`, `dictionary_encoding_columns`, or vector indexes

Use the REBUILD syntax with Serverless Computing resources instead of ALTER TABLE ... SET. For more information, see REBUILD.

ASYNC REBUILD TABLE <table_name>
WITH (
    rebuild_guc_hg_computing_resource = 'serverless'
)
SET (
    bitmap_columns = '<col1>,<col2>',
    dictionary_encoding_columns = '<col1>:on,<col2>:off',
    vectors = '{
    "<col_vector>": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "rabitq",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb": 4096
        }
    }
    }'
);

Changes to columnar storage for JSON data or full-text index columns

The REBUILD syntax is not yet supported for these changes. Use a temporary table instead:

BEGIN;
-- Clean up any existing temporary table
DROP TABLE IF EXISTS <table_new>;
-- Create a temporary table with the same structure
SET hg_experimental_enable_create_table_like_properties = on;
CALL HG_CREATE_TABLE_LIKE ('<table_new>', 'select * from <table>');
COMMIT;

-- Apply the new column properties to the temporary table
ALTER TABLE <table_new> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);
CREATE INDEX <idx_name> ON <table_new> USING FULLTEXT (column_name);

-- Insert data using Serverless resources (index building completes synchronously)
SET hg_computing_resource = 'serverless';
INSERT INTO <table_new> SELECT * FROM <table>;
ANALYZE <table_new>;

BEGIN;
-- Replace the original table with the temporary table
DROP TABLE IF EXISTS <table>;
ALTER TABLE <table_new> RENAME TO <table>;
COMMIT;

Other property changes (e.g., `distribution_key`, `clustering_key`, `segment_key`, storage format)

Use the REBUILD syntax with Serverless Computing resources.