pgvector SQL Support Guide¶

BenoStreamDB pgvector-Compatible SQL Interface

BenoStreamDB provides full pgvector-compatible SQL syntax through Apache DataFusion integration, allowing you to use familiar PostgreSQL vector operations for similarity search, aggregations, and type conversions.

Table of Contents¶

Distance Operators
Vector Literals
KNN Queries
Configuration Parameters
Sparse Vectors
Binary Vectors
Vector Aggregations
Type Casting
Query Examples

Distance Operators¶

BenoStreamDB supports all six pgvector distance operators for computing vector similarity:

Operator	Distance Metric	Description	BenoStreamDB Type	Upstream pgvector Type	Use Case
`<->`	L2 (Euclidean)	Straight-line distance	`::vector`	`vector`, `halfvec`, `sparsevec`	General similarity
`<=>`	Cosine	Angle-based similarity	`::vector`	`vector`, `halfvec`, `sparsevec`	Text embeddings
`<#>`	Inner Product	Negative dot product	`::vector`	`vector`, `halfvec`, `sparsevec`	Recommendation systems
`<+>`	L1 (Manhattan)	Sum of absolute differences	`::vector`	`vector`, `halfvec`, `sparsevec`	Sparse data
`<~>`	Hamming	Bit difference count	`::vector` (enhanced)	`bit` only	Binary vectors / bitsets
`<%>`	Jaccard	Set similarity	`::vector` (enhanced)	`bit` only	Categorical / indicator sets

Note on <~> and <%>: In upstream PostgreSQL pgvector, <~> and <%> only accept the native PostgreSQL bit type (e.g. '101'::bit(3)). In BenoStreamDB, these operators are extended to execute directly on standard ::vector (Arrow Float32) columns without requiring casting or binary conversion functions. See Compatibility Notes & Conversions below for exact PostgreSQL translation syntax.

Basic Usage¶

-- L2 distance (Euclidean)
SELECT id, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
WHERE embedding <-> '[0.1, 0.2, 0.3]'::vector < 0.5;

-- Cosine distance
SELECT id, embedding <=> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance LIMIT 10;

-- Inner product
SELECT id, embedding <#> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents;

Operator Equivalence¶

Distance operators are equivalent to their corresponding UDF functions:

-- These are equivalent:
SELECT embedding <-> query_vec FROM table;
SELECT dist_l2(embedding, query_vec) FROM table;

-- These are equivalent:
SELECT embedding <=> query_vec FROM table;
SELECT dist_cosine(embedding, query_vec) FROM table;

Vector Literals¶

Vector literals allow you to specify vectors directly in SQL queries without parameter binding.

Dense Vectors¶

-- Basic syntax
'[1, 2, 3]'::vector

-- With floating-point values
'[0.1, 0.2, 0.3]'::vector

-- With dimension specification
'[1, 2, 3]'::vector(3)

-- In queries
SELECT * FROM documents
WHERE embedding <-> '[0.1, 0.2, 0.3]'::vector < 0.5;

Sparse Vectors¶

-- Sparse vector literal (index:value pairs)
'{1:0.5, 10:0.3, 100:0.8}'::sparsevec(1000)

-- In queries
SELECT id, sparse_embedding <-> '{1:0.5, 10:0.3}'::sparsevec(1000) AS distance
FROM documents
ORDER BY distance LIMIT 10;

Binary Vectors¶

-- Binary literal (bit string)
B'10110101'

-- Hexadecimal format
'\\xB5'::bit(8)

-- In queries
SELECT id, binary_embedding <~> B'10110101' AS distance
FROM documents;

KNN Queries¶

K-Nearest Neighbors (KNN) queries find the k most similar vectors to a query vector. BenoStreamDB automatically optimizes these queries to use vector indexes.

Basic KNN¶

-- Find 10 nearest neighbors
SELECT id, content, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

KNN with Filters¶

Combine vector search with scalar filters for hybrid queries:

-- Find nearest neighbors in a specific category
SELECT id, content, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
WHERE category = 'science'
ORDER BY distance
LIMIT 10;

-- Multiple filters
SELECT id, content, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
WHERE category = 'science' 
  AND published_date > '2024-01-01'
  AND author_id IN (1, 2, 3)
ORDER BY distance
LIMIT 10;

KNN with Pagination¶

-- First page (results 1-10)
SELECT id, content, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Second page (results 11-20)
SELECT id, content, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10 OFFSET 10;

Distance Threshold Queries¶

-- Find all vectors within distance threshold
SELECT id, content, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
WHERE embedding <-> '[0.1, 0.2, 0.3]'::vector < 0.5
ORDER BY distance;

Configuration Parameters¶

Control vector search behavior per query with the INDEX_HINT optimizer hint.

HNSW Parameters¶

-- ef_search controls the search beam width (default: 64)
-- Higher values = more accurate but slower
SELECT /*+ INDEX_HINT(ef_search=128) */
       id, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

IVF Parameters¶

-- probes controls how many IVF clusters are scanned (default: 10)
-- Higher values = more accurate but slower
SELECT /*+ INDEX_HINT(probes=20) */
       id, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

Index Control¶

-- Force index usage (default: true)
SELECT /*+ INDEX_HINT(use_index=true) */
       id, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Disable index for testing (forces sequential scan)
SELECT /*+ INDEX_HINT(use_index=false) */
       id, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

Python API¶

import benostreamdb as bsdb

table = bsdb.Table("s3://bucket/documents")

# Pass index-tuning parameters alongside the vector filter
results = table.to_pandas(
    vector_filter={
        "column": "embedding",
        "query": [0.1, 0.2, 0.3],
        "k": 10,
        "ef_search": 128,
        "probes": 20,
    }
)

Sparse Vectors¶

Sparse vectors efficiently represent high-dimensional vectors with mostly zero values.

Creating Sparse Vector Tables¶

CREATE TABLE documents (
    id INTEGER,
    content TEXT,
    sparse_embedding sparsevec(10000)
);

Sparse Vector Operations¶

-- Insert sparse vectors
INSERT INTO documents (id, content, sparse_embedding)
VALUES (1, 'document text', '{1:0.5, 100:0.3, 500:0.8}'::sparsevec(10000));

-- Query sparse vectors
SELECT id, sparse_embedding <-> '{1:0.5, 10:0.3}'::sparsevec(10000) AS distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Get sparse vector dimensions
SELECT id, sparsevec_dims(sparse_embedding) AS dimensions
FROM documents;

-- Get non-zero count
SELECT id, sparsevec_nnz(sparse_embedding) AS non_zero_count
FROM documents;

Sparse Vector Utility Functions¶

-- Get dimensionality
SELECT sparsevec_dims('{1:0.5, 10:0.3}'::sparsevec(1000));
-- Returns: 1000

-- Get non-zero element count
SELECT sparsevec_nnz('{1:0.5, 10:0.3}'::sparsevec(1000));
-- Returns: 2

Binary Vectors¶

Binary vectors use bit-packed representation for memory-efficient storage and fast Hamming distance computation.

Binary Quantization¶

-- Convert dense vector to binary
SELECT binary_quantize('[0.1, -0.2, 0.3, -0.4]'::vector) AS binary_vec;
-- Returns: B'1010' (1 if >= 0, 0 if < 0)

-- Store binary vectors
CREATE TABLE documents (
    id INTEGER,
    content TEXT,
    binary_embedding bit(1024)
);

Binary Vector Operations¶

-- Hamming distance on binary vectors
SELECT id, binary_embedding <~> B'10110101' AS distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Binary vector literals
SELECT id, binary_embedding <~> '\\xB5'::bit(8) AS distance
FROM documents;

Binary Vector Display¶

-- Format binary vector for display
SELECT id, format_binary_vector(binary_embedding, 8) AS binary_str
FROM documents;
-- Returns: "10110101" or "0xB5"

Vector Aggregations¶

Compute aggregate statistics over vector columns.

Vector Sum¶

-- Sum all vectors in a table
SELECT vector_sum(embedding) AS total
FROM documents;

-- Sum vectors by group
SELECT category, vector_sum(embedding) AS category_sum
FROM documents
GROUP BY category;

Vector Average (Centroid)¶

-- Compute centroid of all vectors
SELECT vector_avg(embedding) AS centroid
FROM documents;

-- Compute centroid per category
SELECT category, vector_avg(embedding) AS centroid
FROM documents
GROUP BY category;

-- Find documents closest to category centroid
WITH centroids AS (
    SELECT category, vector_avg(embedding) AS centroid
    FROM documents
    GROUP BY category
)
SELECT d.id, d.content, d.embedding <-> c.centroid AS distance
FROM documents d
JOIN centroids c ON d.category = c.category
ORDER BY distance
LIMIT 10;

Aggregation with Filters¶

-- Aggregate filtered results
SELECT category, vector_avg(embedding) AS centroid
FROM documents
WHERE published_date > '2024-01-01'
GROUP BY category
HAVING COUNT(*) > 10;

Type Casting¶

Convert between different vector representations.

Dense to Sparse¶

-- Cast dense vector to sparse (filters out zeros)
SELECT '[0.1, 0, 0.3, 0, 0.5]'::vector::sparsevec AS sparse_vec;
-- Returns: {0:0.1, 2:0.3, 4:0.5}

Sparse to Dense¶

-- Cast sparse vector to dense (expands with zeros)
SELECT '{1:0.5, 10:0.3}'::sparsevec(100)::vector AS dense_vec;
-- Returns: [0, 0.5, 0, 0, 0, 0, 0, 0, 0, 0, 0.3, 0, ...]

Vector to Binary¶

-- Cast vector to binary (binary quantization)
SELECT '[0.1, -0.2, 0.3, -0.4]'::vector::bit AS binary_vec;
-- Returns: B'1010'

Float Precision Conversion¶

-- Cast Float32 to Float16 (reduces precision)
SELECT '[0.123456789, 0.987654321]'::vector::halfvec AS half_vec;

-- Cast Float16 to Float32 (expands precision)
SELECT '[0.123, 0.987]'::halfvec::vector AS full_vec;

Round-Trip Conversions¶

-- Dense -> Sparse -> Dense (preserves values)
SELECT '[0.1, 0, 0.3]'::vector::sparsevec::vector AS round_trip;

-- Vector -> Binary -> Vector (loses precision, binary quantization)
SELECT '[0.1, -0.2, 0.3]'::vector::bit::vector AS quantized;

Query Examples¶

Example 1: Semantic Search¶

-- Find documents similar to a query
SELECT 
    id,
    title,
    content,
    embedding <=> '[0.1, 0.2, 0.3, ...]'::vector AS similarity
FROM documents
WHERE language = 'en'
ORDER BY similarity
LIMIT 20;

Example 2: Hybrid Search with Multiple Filters¶

-- Find similar documents with complex filters
SELECT 
    d.id,
    d.title,
    d.embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance,
    d.published_date
FROM documents d
WHERE d.category IN ('science', 'technology')
  AND d.published_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND d.author_id IN (SELECT id FROM authors WHERE verified = true)
ORDER BY distance
LIMIT 10;

Example 3: Clustering Analysis¶

-- Find cluster centroids and assign documents
WITH cluster_centroids AS (
    SELECT 
        cluster_id,
        vector_avg(embedding) AS centroid
    FROM documents
    GROUP BY cluster_id
),
document_distances AS (
    SELECT 
        d.id,
        d.cluster_id AS assigned_cluster,
        c.cluster_id AS nearest_cluster,
        d.embedding <-> c.centroid AS distance,
        ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY d.embedding <-> c.centroid) AS rank
    FROM documents d
    CROSS JOIN cluster_centroids c
)
SELECT 
    id,
    assigned_cluster,
    nearest_cluster,
    distance
FROM document_distances
WHERE rank = 1 AND assigned_cluster != nearest_cluster;

Example 4: Recommendation System¶

-- Find similar items based on user preferences
WITH user_profile AS (
    SELECT vector_avg(i.embedding) AS preference_vector
    FROM user_interactions ui
    JOIN items i ON ui.item_id = i.id
    WHERE ui.user_id = 123
      AND ui.rating >= 4
)
SELECT 
    i.id,
    i.name,
    i.embedding <#> up.preference_vector AS score
FROM items i
CROSS JOIN user_profile up
WHERE i.id NOT IN (
    SELECT item_id FROM user_interactions WHERE user_id = 123
)
ORDER BY score DESC
LIMIT 20;

Example 5: Deduplication¶

-- Find near-duplicate documents
SELECT 
    d1.id AS doc1_id,
    d2.id AS doc2_id,
    d1.embedding <-> d2.embedding AS distance
FROM documents d1
JOIN documents d2 ON d1.id < d2.id
WHERE d1.embedding <-> d2.embedding < 0.1
ORDER BY distance;

Example 6: Multi-Vector Search¶

-- Search across multiple vector columns
SELECT 
    id,
    title,
    LEAST(
        text_embedding <=> '[0.1, 0.2, ...]'::vector,
        image_embedding <=> '[0.3, 0.4, ...]'::vector
    ) AS min_distance
FROM multimedia_documents
ORDER BY min_distance
LIMIT 10;

Example 7: Temporal Vector Search¶

-- Find similar documents with time decay
SELECT 
    id,
    title,
    embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance,
    EXTRACT(EPOCH FROM (NOW() - published_date)) / 86400 AS days_old,
    (embedding <-> '[0.1, 0.2, 0.3]'::vector) * 
        (1 + 0.01 * EXTRACT(EPOCH FROM (NOW() - published_date)) / 86400) AS time_weighted_distance
FROM documents
ORDER BY time_weighted_distance
LIMIT 10;

Performance Tips¶

Index Usage¶

Ensure indexes exist: Vector searches automatically use HNSW or IVF indexes when available
Monitor fallback: Check logs for “using sequential scan” messages indicating missing indexes
Tune parameters: Adjust ef_search and probes based on accuracy/speed requirements

Query Optimization¶

Use filters early: Scalar filters are applied before vector search for better performance
Limit results: Always use LIMIT for KNN queries to enable index optimization
Project only needed columns: Avoid SELECT * when working with large embeddings

Data Types¶

Use sparse vectors: For high-dimensional vectors with >90% zeros
Use binary vectors: For memory-constrained environments (32x compression)
Use Float16: For 2x memory savings with acceptable precision loss

Error Handling¶

Common Errors¶

Dimension Mismatch:

-- Error: Vector dimension mismatch: expected 768, got 512
SELECT embedding <-> '[0.1, 0.2]'::vector FROM documents;

Malformed Literal:

-- Error: Vector literal must be enclosed in brackets
SELECT '[0.1, 0.2'::vector;

-- Error: Invalid number at position 5
SELECT '[0.1, abc]'::vector;

Invalid Cast:

-- Error: Cannot cast string to vector without proper format
SELECT 'not a vector'::vector;

Aggregation Dimension Mismatch:

-- Error: Cannot aggregate vectors of different dimensions
SELECT vector_avg(embedding) FROM mixed_dimension_table;

Troubleshooting¶

Check vector dimensions: Use vector_dims(column) to verify dimensions
Validate data: Ensure no NaN or infinite values in vectors
Test with small data: Verify queries on small datasets before scaling
Check configuration: Verify ef_search and probes are positive integers

Compatibility Notes & Conversions¶

pgvector Compatibility Matrix¶

BenoStreamDB implements the pgvector SQL interface with the following behavior:

Feature / Operator	BenoStreamDB	PostgreSQL + pgvector	Notes
`<->` (L2 / Euclidean)	✅ Direct on `::vector`	✅ Direct on `vector`, `halfvec`, `sparsevec`	100% 1-to-1 drop-in syntax
`<=>` (Cosine)	✅ Direct on `::vector`	✅ Direct on `vector`, `halfvec`, `sparsevec`	100% 1-to-1 drop-in syntax
`<#>` (Negative Inner Product)	✅ Direct on `::vector`	✅ Direct on `vector`, `halfvec`, `sparsevec`	100% 1-to-1 drop-in syntax
`<+>` (L1 / Manhattan)	✅ Direct on `::vector`	✅ Direct on `vector`, `halfvec`, `sparsevec`	Added in pgvector 0.7.0+
`<~>` (Hamming Distance)	✅ Enhanced: Direct on `::vector`	⚠️ `bit` type only	BenoStreamDB evaluates on float indicators directly; PostgreSQL requires `bit` type
`<%>` (Jaccard Distance)	✅ Enhanced: Direct on `::vector`	⚠️ `bit` type only	BenoStreamDB evaluates on float indicator sets directly; PostgreSQL requires `bit` type
Vector Literals	`[0.1, 0.2]'::vector`	`[0.1, 0.2]'` or `::vector`	Compatible
KNN Pushdown	`ORDER BY dist LIMIT k`	`ORDER BY dist LIMIT k`	Compatible
Vector Aggregations	`vector_sum`, `vector_avg`	`avg(vector)`, `sum(vector)`	Compatible

Exact PostgreSQL Conversion Guide¶

If you are porting queries between BenoStreamDB and PostgreSQL with pgvector, use the exact conversions below:

1. Standard Distance Operators (`<->`, `<=>`, `<#>`, `<+>`)¶

These operators share identical semantics and syntax between engines:

-- BenoStreamDB:
SELECT id, content FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3]'::vector
LIMIT 10;

-- PostgreSQL (pgvector):
SELECT id, content FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3]'::vector
LIMIT 10;

2. Hamming Distance (`<~>`)¶

BenoStreamDB: Directly calculates Hamming distance over standard float embeddings or binary indicator vectors.
PostgreSQL (pgvector): <~> is strictly typed for PostgreSQL’s bit type. You must convert float vectors using binary_quantize() (or store the column as bit(N)):

-- BenoStreamDB (direct on ::vector):
SELECT id, content FROM documents
ORDER BY embedding <~> '[1, 0, 1]'::vector
LIMIT 10;

-- Equivalent PostgreSQL (pgvector 0.7.0+):
SELECT id, content FROM documents
ORDER BY binary_quantize(embedding) <~> binary_quantize('[1, 0, 1]'::vector)
LIMIT 10;

-- If your table in PostgreSQL already stores native bit columns:
SELECT id, content FROM documents
ORDER BY bit_embedding <~> '101'::bit(3)
LIMIT 10;

3. Jaccard Distance (`<%>`)¶

BenoStreamDB: Evaluates Jaccard set distance ($1.0 - \frac{\text{intersection}}{\text{union}}$) directly on float binary indicator vectors (where values $> 0.0$ represent set membership).
PostgreSQL (pgvector): <%> is strictly typed for the bit type. Convert via binary_quantize():

-- BenoStreamDB (direct on ::vector):
SELECT id, content FROM documents
ORDER BY embedding <%> '[1, 0, 1]'::vector
LIMIT 10;

-- Equivalent PostgreSQL (pgvector 0.7.0+):
SELECT id, content FROM documents
ORDER BY binary_quantize(embedding) <%> binary_quantize('[1, 0, 1]'::vector)
LIMIT 10;

-- If your table in PostgreSQL already stores native bit columns:
SELECT id, content FROM documents
ORDER BY bit_embedding <%> '101'::bit(3)
LIMIT 10;

Additional Resources¶

BenoStreamDB Architecture
DataFusion SQL Reference
pgvector Documentation
Vector Search Best Practices

pgvector SQL Support Guide¶

Table of Contents¶

Distance Operators¶

Basic Usage¶

Operator Equivalence¶

Vector Literals¶

Dense Vectors¶

Sparse Vectors¶

Binary Vectors¶

KNN Queries¶

Basic KNN¶

KNN with Filters¶

KNN with Pagination¶

Distance Threshold Queries¶

Configuration Parameters¶

HNSW Parameters¶

IVF Parameters¶

Index Control¶

Python API¶

Sparse Vectors¶

Creating Sparse Vector Tables¶

Sparse Vector Operations¶

Sparse Vector Utility Functions¶

Binary Vectors¶

Binary Quantization¶

Binary Vector Operations¶

Binary Vector Display¶

Vector Aggregations¶

Vector Sum¶

Vector Average (Centroid)¶

Aggregation with Filters¶

Type Casting¶

Dense to Sparse¶

Sparse to Dense¶

Vector to Binary¶

Float Precision Conversion¶

Round-Trip Conversions¶

Query Examples¶

Example 1: Semantic Search¶

Example 2: Hybrid Search with Multiple Filters¶

Example 3: Clustering Analysis¶

Example 4: Recommendation System¶

Example 5: Deduplication¶

Example 6: Multi-Vector Search¶

Example 7: Temporal Vector Search¶

Performance Tips¶

Index Usage¶

Query Optimization¶

Data Types¶

Error Handling¶

Common Errors¶

Troubleshooting¶

Compatibility Notes & Conversions¶

pgvector Compatibility Matrix¶

Exact PostgreSQL Conversion Guide¶

1. Standard Distance Operators (<->, <=>, <#>, <+>)¶

2. Hamming Distance (<~>)¶

3. Jaccard Distance (<%>)¶

Additional Resources¶

1. Standard Distance Operators (`<->`, `<=>`, `<#>`, `<+>`)¶

2. Hamming Distance (`<~>`)¶

3. Jaccard Distance (`<%>`)¶