Hologres:Work with Proxima for vector search

Step 1: Install the Proxima extension

A superuser must install the Proxima extension before any other steps. The extension is installed per database — run this once per database:

CREATE EXTENSION proxima;

To uninstall the extension:

DROP EXTENSION proxima;

Step 2: Create a vector table and index

Vectors in Hologres are stored as FLOAT4 arrays. Vector indexes are supported only on column-oriented tables and hybrid row-column tables — not on row-oriented tables.

Choose the approach that matches your data loading pattern:

Option A: Create the index before importing data (real-time ingestion)

-- Single vector column
BEGIN;
CREATE TABLE feature_tb (
    id BIGINT,
    feature_col FLOAT4[] CHECK(array_ndims(feature_col) = 1 AND array_length(feature_col, 1) = <dimension>)
);
CALL set_table_property(
    'feature_tb',
    'proxima_vectors',
    '{"<feature_col>":{"algorithm":"Graph",
      "distance_method":"<SquaredEuclidean|Euclidean|InnerProduct>",
      "builder_params":{"min_flush_proxima_row_count": 1000,
                        "min_compaction_proxima_row_count": 1000,
                        "max_total_size_to_merge_mb": 2000}}}');
COMMIT;

Option B: Import data first, then build the index (batch analytics)

Starting from Hologres V2.0.11, importing data before creating the index avoids building the index during compaction, which reduces total index creation time.

-- Create the table (no index yet)
BEGIN;
CREATE TABLE feature_tb (
    id BIGINT,
    feature_col FLOAT4[] CHECK(array_ndims(feature_col) = 1 AND array_length(feature_col, 1) = <dimension>)
);
COMMIT;

-- (Optional) Use Serverless Computing for large-volume batch imports
SET hg_computing_resource = 'serverless';

-- Import data
INSERT INTO feature_tb ...;
VACUUM feature_tb;

-- Build the index after data is loaded
CALL set_table_property(
    'feature_tb',
    'proxima_vectors',
    '{"<feature_col>":{"algorithm":"Graph",
      "distance_method":"<SquaredEuclidean|Euclidean|InnerProduct>",
      "builder_params":{"min_flush_proxima_row_count": 1000,
                        "min_compaction_proxima_row_count": 1000,
                        "max_total_size_to_merge_mb": 2000}}}');

-- Reset to avoid routing unrelated queries to serverless resources
RESET hg_computing_resource;

Starting from Hologres V2.1.17, Serverless Computing is available for batch importing or querying large volumes of vector data. It uses additional serverless resources instead of your instance's resources, so you don't need to reserve extra compute capacity. You are charged only for the tasks you run. For details, see Serverless Computing overview and Use Serverless Computing.

Step 3: Import vector data

Import data using batch or real-time mode. After a batch import, run VACUUM and then ANALYZE to optimize query performance:

-- Compact backend files for more efficient queries
VACUUM <table_name>;

-- Collect statistics for the Query Optimizer (QO)
ANALYZE <table_name>;

• VACUUM compacts backend files into larger files. It consumes CPU resources and runtime scales with data volume. Wait for a running VACUUM to finish before starting another.

• ANALYZE collects performance statistics that the Query Optimizer (QO) uses to generate better execution plans.

Step 4: Query vector data

Hologres supports two query modes:

The vector index works only in single-table queries. Avoid JOIN operations when querying vectors.

-- Top K by squared Euclidean distance (requires distance_method: SquaredEuclidean)
SELECT pm_approx_squared_euclidean_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM feature_tb
ORDER BY distance ASC
LIMIT 10;

-- Top K by Euclidean distance (requires distance_method: Euclidean)
SELECT pm_approx_euclidean_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM feature_tb
ORDER BY distance ASC
LIMIT 10;

-- Top K by inner product distance (requires distance_method: InnerProduct)
SELECT pm_approx_inner_product_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM feature_tb
ORDER BY distance DESC
LIMIT 10;

-- Top K by squared Euclidean distance (full scan)
SELECT pm_squared_euclidean_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM feature_tb
ORDER BY distance ASC
LIMIT 10;

-- Top K by Euclidean distance (full scan)
SELECT pm_euclidean_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM feature_tb
ORDER BY distance ASC
LIMIT 10;

-- Top K by inner product distance (full scan)
SELECT pm_inner_product_distance(feature, '{0.1,0.2,0.3,0.4}') AS distance
FROM feature_tb
ORDER BY distance DESC
LIMIT 10;

Verify index usage

To confirm the index is being used, run EXPLAIN on your query and check the plan output for Proxima filter: xxxx. If that string is absent, the index is not in use — typically because the table's distance_method doesn't match the query UDF.

1. Create the table

CREATE EXTENSION proxima;

BEGIN;
-- Place the table in a table group with shard_count = 4
CALL HG_CREATE_TABLE_GROUP('test_tg_shard_4', 4);

CREATE TABLE feature_tb (
    id BIGINT,
    feature FLOAT4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
);

CALL set_table_property('feature_tb', 'table_group', 'test_tg_shard_4');
CALL set_table_property('feature_tb', 'proxima_vectors',
    '{"feature":{"algorithm":"Graph","distance_method":"SquaredEuclidean",
      "builder_params":{"min_flush_proxima_row_count": 1000,
                        "min_compaction_proxima_row_count": 1000,
                        "max_total_size_to_merge_mb": 2000}}}');
COMMIT;

2. Import data

-- (Optional) Use Serverless Computing for large-volume batch imports
SET hg_computing_resource = 'serverless';

INSERT INTO feature_tb
SELECT
    i,
    ARRAY[random(), random(), random(), random()]::FLOAT4[]
FROM generate_series(1, 100000) i;

VACUUM feature_tb;
ANALYZE feature_tb;

-- Reset to avoid routing unrelated queries to serverless resources
RESET hg_computing_resource;

3. Run a query

-- (Optional) Use Serverless Computing for large-volume vector queries
SET hg_computing_resource = 'serverless';

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

-- Reset to avoid routing unrelated queries to serverless resources
RESET hg_computing_resource;

To confirm the query used the index, run EXPLAIN on the query and look for Proxima filter: in the output.

When to use a vector index

For small datasets (tens of thousands of rows) or when your instance has enough resources, direct distance calculation without an index may be sufficient. Consider using a Proxima index when direct calculation can't meet your latency or throughput requirements.

Keep the following trade-offs in mind before enabling an index:

• The Proxima index is lossy — results may have a small accuracy bias.

• The index may return fewer results than the LIMIT value. For example, a LIMIT 1000 query might return only 500 rows.

• A Proxima index can be difficult to use.

Set an appropriate shard count

Each shard builds its own set of Proxima index files. More shards mean more index files, which reduces query throughput. Set the shard count to the number of workers as a starting point. For a 64-core instance, a shard count of 4 is typical.

Fewer shards lower single-query latency but also reduce write throughput.

BEGIN;
CALL HG_CREATE_TABLE_GROUP('test_tg_shard_4', 4);
CREATE TABLE proxima_test (
    id BIGINT NOT NULL,
    vectors FLOAT4[] CHECK(array_ndims(vectors) = 1 AND array_length(vectors, 1) = 128),
    PRIMARY KEY (id)
);
CALL set_table_property('proxima_test', 'proxima_vectors',
    '{"vectors":{"algorithm":"Graph","distance_method":"SquaredEuclidean",
      "builder_params":{}, "searcher_init_params":{}}}');
CALL set_table_property('proxima_test', 'table_group', 'test_tg_shard_4');
COMMIT;

Optimize for filter conditions

WHERE filter conditions can prevent the index from being used. The general strategy: minimize the number of shards a filtered query must touch.

• No filter conditions (recommended): The index works most efficiently. Aim for one vector index file per shard so each query runs on a single shard.

• Filter conditions present: Design the table so that filtered queries are routed to as few shards as possible.

BEGIN;
CREATE TABLE feature_tb (
    uuid TEXT,
    feature FLOAT4[] NOT NULL CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = N)
);
CALL set_table_property('feature_tb', 'shard_count', '<shard_count>');
CALL set_table_property('feature_tb', 'proxima_vectors',
    '{"feature":{"algorithm":"Graph","distance_method":"InnerProduct"}}');
END;

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

SELECT pm_xx_distance(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;

BEGIN;
CREATE TABLE feature_tb (
    time_field TIMESTAMPTZ NOT NULL,
    uuid TEXT,
    feature FLOAT4[] NOT NULL CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = N)
);
CALL set_table_property('feature_tb', 'distribution_key', 'uuid');
CALL set_table_property('feature_tb', 'segment_key', 'time_field');
CALL set_table_property('feature_tb', 'clustering_key', 'uuid');
CALL set_table_property('feature_tb', 'proxima_vectors',
    '{"feature":{"algorithm":"Graph","distance_method":"InnerProduct"}}');
COMMIT;

-- If you don't need time-based filtering, remove the segment_key setting.