AnalyticDB:Create a vector table

Create a single-precision dense vector table

real[] and float4[] are equivalent. The following example creates a table that uses real[] to store text vectors:

CREATE TABLE chunks_float4 (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding REAL[] NOT NULL
) DISTRIBUTED BY (id);

Array types do not specify vector dimensions in the CREATE TABLE statement. When you create a vector index, you must specify the dimension through the dim parameter.

Create a pgvector-compatible dense vector table

The following example creates a vector column with a dimension of 1,536:

CREATE TABLE chunks_vector (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536) NOT NULL
) DISTRIBUTED BY (id);

This type is suitable for workloads that require compatibility with pgvector data read and write formats.

Create a half-precision vector table

Create a float2[] vector column by using native array syntax:

CREATE TABLE chunks_float2 (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding FLOAT2[] NOT NULL
) DISTRIBUTED BY (id);

Create a halfvec vector column with a dimension of 1,536 by using pgvector-compatible syntax:

CREATE TABLE chunks_halfvec (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding HALFVEC(1536) NOT NULL
) DISTRIBUTED BY (id);

Both float2[] and halfvec use half-precision floating-point numbers to store vectors, which reduces storage and memory usage for high-dimensional vectors.

Create an SQ8-compressed vector table

The following example creates a sq8vector column with a dimension of 1,536:

CREATE TABLE chunks_sq8 (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding SQ8VECTOR(1536) NOT NULL
) DISTRIBUTED BY (id);

sq8vector uses INT8 format to store vectors and can be used with Novam or Novad indexes. This type is suitable for scenarios that require reduced memory and storage costs.

Create a native sparse vector table

The following example creates an svector sparse vector column with a dimension of 100,000:

CREATE TABLE sparse_documents (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding SVECTOR(100000) NOT NULL
) DISTRIBUTED BY (id);

svector uses indices and values to represent non-zero elements. For example, the following data represents an 8-dimensional sparse vector where the values at dimensions 2, 3, and 6 are 1.2, 1.1, and 0.7 respectively:

SELECT
    '{"indices":[2,3,6],"values":[1.2,1.1,0.7]}'::SVECTOR(8);

When you use svector, take note of the following items:

• The subscripts in indices start from 0.

• The valid subscript range of SVECTOR(N) is 0 to N-1.

• indices and values must contain the same number of elements and correspond to each other by position.

• values cannot contain NaN, Infinity, or -Infinity.

• Each vector can contain up to 4,000 non-zero elements.

Create a pgvector-compatible sparse vector table

The following example creates a sparsevec column with a dimension of 100,000:

CREATE TABLE sparse_documents_compat (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding SPARSEVEC(100000) NOT NULL
) DISTRIBUTED BY (id);

This type is suitable for workloads that require compatibility with pgvector sparse vector data formats.

Create a binary vector table

The following example creates a binary vector column with a dimension of 256:

CREATE TABLE binary_vectors (
    id BIGINT PRIMARY KEY,
    content TEXT,
    embedding BIT(256) NOT NULL
) DISTRIBUTED BY (id);

The bit type is suitable for hash fingerprints and binary encoding scenarios. It supports similarity search by using Hamming distance and Jaccard distance.