AnalyticDB:Use AnalyticDB for PostgreSQL sparse vectors

Step 1: Create a sparse vector table

CREATE TABLE <table_name> (
    id int PRIMARY KEY,
    description text,
    sparse_vector svector(<MAX_DIM>)
) DISTRIBUTED BY (id);

-- Set the storage mode of the sparse vector column to PLAIN.
ALTER TABLE <table_name> ALTER COLUMN sparse_vector SET STORAGE PLAIN;

Parameters:

Why PLAIN storage? PostgreSQL uses The Oversized-Attribute Storage Technique (TOAST) to store large field values. PLAIN mode disables TOAST compression and splitting, keeping each vector in a single row without being moved to an external table. This avoids the overhead that TOAST introduces for sparse vector access patterns.

Example:

-- Create a table with a sparse vector column.
CREATE TABLE svector_test (
    id bigint,
    type int,
    tag varchar(10),
    document text,
    sparse_features svector(250000)
) DISTRIBUTED BY (id);

-- Set PLAIN storage to prevent TOAST overhead.
ALTER TABLE svector_test ALTER COLUMN sparse_features SET STORAGE PLAIN;

Step 2: Create indexes

Sparse vector indexes have two constraints:

• Distance metric: inner product (IP) only — do not use other metrics to avoid errors

• No support for product quantization (PQ) or memory mapping

-- Create B-tree indexes for structured columns used in filtered search.
CREATE INDEX ON svector_test (type);
CREATE INDEX ON svector_test (tag);

-- Create an HNSW (Hierarchical Navigable Small World) index on the sparse vector column.
-- DISTANCEMEASURE must be IP (inner product). Using other metrics causes errors.
-- pq_enable=0 and external_storage=0 are required for sparse vectors.
CREATE INDEX ON svector_test USING ANN (sparse_features)
WITH (DISTANCEMEASURE=IP, HNSW_M=64, pq_enable=0, external_storage=0);

For HNSW_M and HNSW_EF_CONSTRUCTION tuning, see Create a vector index.

Step 3: Import sparse vector data

Use INSERT or COPY to load data. Cast the JSON string to svector using ::svector.

-- Insert rows with sparse vector values.
INSERT INTO svector_test VALUES (1, 1, 'a', 'document one',
    '{"indices":[2, 6, 9], "values":[1.1, 2.2, 3.3]}'::svector);
INSERT INTO svector_test VALUES (2, 2, 'b', 'document two',
    '{"indices":[50, 100, 200], "values":[2.1, 3.2, 4.3]}'::svector);
INSERT INTO svector_test VALUES (3, 3, 'b', 'document three',
    '{"indices":[150, 60, 90], "values":[5, 1e3, 1e-3]}'::svector);

To verify the cast:

SELECT '{"indices":[2, 6, 9], "values":[1.1, 2.2, 3.3]}'::svector;
-- Output: {"indices":[2,6,9],"values":[1.1,2.2,3.3]}

Step 4: Search sparse vectors

Choose between exact search and approximate search based on your precision and latency requirements:

SELECT id,
       inner_product(sparse_features,
           '{"indices":[2,60,50], "values":[2, 0.01, 0.02]}'::svector) AS score
FROM svector_test
ORDER BY negative_inner_product(sparse_features,
    '{"indices":[2,60,50], "values":[2, 0.01, 0.02]}'::svector)
LIMIT 3;

SELECT id,
       sparse_features,
       '{"indices":[2,60,50], "values":[2, 0.01, 0.02]}' <#> sparse_features AS score
FROM svector_test
ORDER BY score
LIMIT 3;

 id |                 sparse_features                 |        score
----+-------------------------------------------------+---------------------
  3 | {"indices":[60,90,150],"values":[1000,0.001,5]} |                 -10
  1 | {"indices":[2,6,9],"values":[1.1,2.2,3.3]}      |   -2.20000004768372
  2 | {"indices":[50,100,200],"values":[2.1,3.2,4.3]} | -0.0419999957084656
(3 rows)

Create the hybrid search table and indexes

-- Create a table with both dense and sparse vector columns.
CREATE TABLE IF NOT EXISTS hybrid_search_test (
    id integer PRIMARY KEY,
    corpus text,
    filter integer,
    dense_vector float4[],
    sparse_vector svector(250003)
) DISTRIBUTED BY (id);

-- Set PLAIN storage for both vector columns.
ALTER TABLE hybrid_search_test ALTER COLUMN dense_vector SET STORAGE PLAIN;
ALTER TABLE hybrid_search_test ALTER COLUMN sparse_vector SET STORAGE PLAIN;

-- Structured index for filter pushdown.
CREATE INDEX ON hybrid_search_test (filter);

-- Dense vector HNSW index (DIM=1024, inner product, external storage enabled).
CREATE INDEX ON hybrid_search_test USING ANN (dense_vector)
WITH (DISTANCEMEASURE=IP, DIM=1024, HNSW_M=64, HNSW_EF_CONSTRUCTION=600, EXTERNAL_STORAGE=1);

-- Sparse vector HNSW index (inner product only, no external storage).
CREATE INDEX ON hybrid_search_test USING ANN (sparse_vector)
WITH (DISTANCEMEASURE=IP, HNSW_M=64, HNSW_EF_CONSTRUCTION=600);

Run search queries

All three examples below use FILTER IN (...) for pre-filtering results by a structured column before vector ranking.

-- Dense vector search with filter.
SELECT id, corpus
FROM hybrid_search_test
WHERE filter IN (0, 100, 200, 300, 400, 500, 600, 700, 800, 900)
ORDER BY dense_vector <#> ARRAY[1,2,3,...,1024]::float4[]
LIMIT 100;

-- Sparse vector search with filter.
SELECT id, corpus
FROM hybrid_search_test
WHERE filter IN (0, 100, 200, 300, 400, 500, 600, 700, 800, 900)
ORDER BY sparse_vector <#> '{"indices":[1,2,..], "values":[1.1,2.2...]}'::svector
LIMIT 100;

-- Hybrid search with filter.
-- Strategy: retrieve the top 100 candidates from each index independently,
-- then re-rank by combining both distance scores (dist1 - dist2).
-- This avoids score-scale incompatibility between sparse and dense distances.
-- Using MAX(dist1 - dist2) per id deduplicates candidates from the UNION ALL.
WITH combined AS (
    -- Branch 1: top 100 by sparse score.
    -- dist1 = dense distance (secondary signal for re-ranking).
    -- dist2 = sparse distance (primary retrieval signal for this branch).
    (SELECT id, corpus,
            inner_product_distance(dense_vector, ARRAY[1,2,3,...,1024]::float4[]) AS dist1,
            sparse_vector <#> '{"indices":[1,2,..], "values":[1.1,2.2...]}'::svector AS dist2
     FROM hybrid_search_test
     WHERE filter IN (0, 100, 200, 300, 400, 500, 600, 700, 800, 900)
     ORDER BY dist2 LIMIT 100)
    UNION ALL
    -- Branch 2: top 100 by dense score.
    -- dist1 = sparse distance (secondary signal for re-ranking).
    -- dist2 = dense distance (primary retrieval signal for this branch).
    (SELECT id, corpus,
            inner_product(sparse_vector, '{"indices":[1,2,..], "values":[1.1,2.2...]}'::svector) AS dist1,
            dense_vector <#> ARRAY[1,2,3,...,1024]::float4[] AS dist2
     FROM hybrid_search_test
     WHERE filter IN (0, 100, 200, 300, 400, 500, 600, 700, 800, 900)
     ORDER BY dist2 LIMIT 100)
)
SELECT DISTINCT
    first_value(id) OVER (PARTITION BY id) AS id,
    first_value(corpus) OVER (PARTITION BY id) AS corpus,
    MAX(dist1 - dist2) OVER (PARTITION BY id) AS dist
FROM combined
ORDER BY dist DESC
LIMIT 100;

Engine parameters

Both parameters apply per session. Use them to tune the trade-off between recall rate and query speed for approximate sparse vector search.

Supported functions

All functions below operate on the SVECTOR data type.

Examples:

-- Euclidean distance
SELECT l2_distance(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

-- Inner product
SELECT inner_product(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

-- Cosine similarity
SELECT cosine_similarity(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

-- Vector arithmetic
SELECT svector_add(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

SELECT svector_sub(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

SELECT svector_mul(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

-- Sorting functions
SELECT l2_squared_distance(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

SELECT negative_inner_product(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

SELECT cosine_distance(
    '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector,
    '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector);

Supported operators

All operators below work with the SVECTOR type.

Examples:

-- Squared Euclidean distance
SELECT '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector
    <-> '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector AS score;

-- Negative inner product (use in ORDER BY for approximate search)
SELECT '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector
    <#> '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector AS score;

-- Cosine distance
SELECT '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector
    <=> '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector AS score;

-- Vector arithmetic
SELECT '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector
    + '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector AS value;

SELECT '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector
    - '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector AS value;

SELECT '{"indices":[1,2,3,4,5,6], "values":[1,2,3,4,5,6]}'::svector
    * '{"indices":[1,2,3,4,5,6], "values":[2,3,4,5,6,7]}'::svector AS value;