StarRocks as a Vector Database

Enterprise Architecture, Scaling, and Production Best Practices

StarRocks Cluster Architecture

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   Frontend 1    │    │   Frontend 2    │    │   Frontend 3    │
│   (Leader)      │    │   (Follower)    │    │   (Follower)    │
└─────────────────┘    └─────────────────┘    └─────────────────┘
         │                       │                       │
         └───────────────────────┼───────────────────────┘
                                 │
    ┌────────────────────────────┼────────────────────────────┐
    │                            │                            │
┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   Backend 1     │    │   Backend 2     │    │   Backend 3     │
│   - Vector Index│    │   - Vector Index│    │   - Vector Index│
│   - Tablets     │    │   - Tablets     │    │   - Tablets     │
│   - Query Exec  │    │   - Query Exec  │    │   - Query Exec  │
└─────────────────┘    └─────────────────┘    └─────────────────┘
        

-- Float vectors (most common for embeddings)
VECTOR_FLOAT(dimension)

-- Example: OpenAI text-embedding-ada-002 (1536 dimensions)
embedding VECTOR_FLOAT(1536)

-- HNSW-optimized vectors
VECTOR_HNSW(dimension)

-- Example with index parameters
embedding VECTOR_HNSW(768, 'metric=cosine,ef_construction=200,M=16')

CREATE TABLE document_embeddings (
    id BIGINT NOT NULL,
    document_id STRING NOT NULL,
    chunk_text STRING,
    embedding VECTOR_FLOAT(1536),
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
    tenant_id INT NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(id)
PARTITION BY RANGE(created_at) (
    PARTITION p20240101 VALUES [('2024-01-01'), ('2024-02-01')),
    PARTITION p20240201 VALUES [('2024-02-01'), ('2024-03-01'))
)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 32;

-- Basic k-nearest neighbors search
SELECT id, document_id, chunk_text,
       COSINE_DISTANCE(embedding, [0.12, 0.45, 0.67, ...]) as similarity
FROM document_embeddings 
ORDER BY similarity ASC
LIMIT 10;

-- Hybrid search: vector similarity + metadata filtering
SELECT d.id, d.document_id, d.chunk_text, 
       COSINE_DISTANCE(d.embedding, [0.12, 0.45, 0.67, ...]) as similarity
FROM document_embeddings d
WHERE d.tenant_id = 12345
  AND JSON_EXTRACT(d.metadata, '$.category') = 'technical'
  AND d.created_at >= '2024-01-01'
ORDER BY similarity ASC
LIMIT 20;

-- Complex join with user permissions
SELECT d.chunk_text, d.similarity, u.username
FROM (
    SELECT id, chunk_text,
           COSINE_DISTANCE(embedding, [0.12, 0.45, 0.67, ...]) as similarity
    FROM document_embeddings 
    WHERE tenant_id = 12345
    ORDER BY similarity ASC
    LIMIT 50
) d
JOIN user_document_permissions p ON d.id = p.document_id
JOIN users u ON p.user_id = u.id
WHERE u.id = 67890
ORDER BY d.similarity ASC
LIMIT 10;

-- Basic HNSW index creation
CREATE INDEX embedding_hnsw_idx ON document_embeddings (embedding) USING HNSW;

-- Index with custom parameters
CREATE INDEX embedding_hnsw_idx ON document_embeddings (embedding) 
USING HNSW (
    'metric' = 'cosine',
    'ef_construction' = 200,
    'M' = 16,
    'ef_search' = 100
);

-- Check index status
SHOW INDEX FROM document_embeddings;

-- Check index refresh status
SELECT table_name, index_name, last_refresh_time, refresh_status
FROM information_schema.vector_indexes
WHERE table_name = 'document_embeddings';

-- Manual index refresh (rarely needed)
ALTER TABLE document_embeddings REFRESH INDEX embedding_hnsw_idx;

-- Monitor index memory usage
SHOW PROC '/backends';

-- Create routine load job for streaming embeddings
CREATE ROUTINE LOAD document_embeddings_stream ON document_embeddings
COLUMNS(id, document_id, chunk_text, embedding_json, metadata_json, tenant_id, created_at),
COLUMNS FROM PATH AS (embedding = vector_from_string(embedding_json))
PROPERTIES (
    "desired_concurrent_number" = "3",
    "max_batch_interval" = "10",
    "max_batch_rows" = "250000",
    "max_batch_size" = "100MB",
    "strict_mode" = "false"
)
FROM KAFKA (
    "kafka_broker_list" = "kafka-1:9092,kafka-2:9092,kafka-3:9092",
    "kafka_topic" = "document_embeddings",
    "property.group.id" = "starrocks_embeddings_consumer",
    "property.kafka_default_offsets" = "OFFSET_BEGINNING"
);

-- Broker load for large embedding datasets
LOAD LABEL document_embeddings_batch_20240115
(
    DATA INFILE("s3://data-lake/embeddings/2024/01/15/*.parquet")
    INTO TABLE document_embeddings
    COLUMNS(id, document_id, chunk_text, embedding, metadata, tenant_id)
    FORMAT AS "parquet"
)
WITH BROKER
PROPERTIES (
    "timeout" = "3600",
    "max_filter_ratio" = "0.1"
);

End-to-End Vector Pipeline

┌─────────────┐    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  Documents  │───▶│  Embedding  │───▶│    Kafka    │───▶│  StarRocks  │
│   (Source)  │    │   Service   │    │   Topics    │    │   Cluster   │
└─────────────┘    └─────────────┘    └─────────────┘    └─────────────┘
                                                                │
                                                                ▼
                                                      ┌─────────────┐
                                                      │   Vector    │
                                                      │   Indexes   │
                                                      │ (Auto-Sync) │
                                                      └─────────────┘
        

-- Optimize for high-throughput vector ingestion
SET GLOBAL stream_load_default_timeout_second = 600;
SET GLOBAL max_stream_load_concurrent_num = 20;

-- Backend configuration for vector workloads
vector_chunk_size = 4096
pipeline_dop = 8
disable_storage_page_cache = false

# Backend configuration (be.conf)
mem_limit = 80%
vector_index_memory_limit = 30%
chunk_reserved_bytes_limit = 2147483648
storage_page_cache_limit = 20%

# Query execution tuning
pipeline_dop = 16
parallel_fragment_exec_instance_num = 8
enable_pipeline_engine = true

-- Add new Backend nodes
ALTER SYSTEM ADD BACKEND "new-backend-1:9050";
ALTER SYSTEM ADD BACKEND "new-backend-2:9050";
ALTER SYSTEM ADD BACKEND "new-backend-3:9050";

-- Verify cluster balance
SHOW PROC '/cluster_balance/cluster_info';

-- Trigger rebalance if needed
ADMIN SET FRONTEND CONFIG ("tablet_rebalancer_type" = "partition");

-- Enable query cache for repeated vector searches
SET enable_query_cache = true;
SET query_cache_size = 268435456; -- 256MB

-- Optimize for vector query patterns
SET enable_vectorized_engine = true;
SET batch_size = 4096;
SET parallel_fragment_exec_instance_num = 8;

-- Monitor query performance
SELECT query_id, query_time_ms, scan_rows, return_rows
FROM information_schema.query_log 
WHERE query_type = 'SELECT' 
  AND query LIKE '%COSINE_DISTANCE%'
ORDER BY query_time_ms DESC
LIMIT 20;

3-AZ High Availability Deployment

Zone A                    Zone B                    Zone C
┌─────────────┐          ┌─────────────┐          ┌─────────────┐
│ FE Leader   │          │ FE Follower │          │ FE Observer │
│ BE Node 1   │          │ BE Node 2   │          │ BE Node 3   │
│ BE Node 4   │          │ BE Node 5   │          │ BE Node 6   │
└─────────────┘          └─────────────┘          └─────────────┘
      │                        │                        │
      └────────────────────────┼────────────────────────┘
                               │
                    ┌─────────────┐
                    │   HAProxy   │
                    │ (Multi-AZ)  │
                    └─────────────┘
        

-- Configure table replication for vector data
ALTER TABLE document_embeddings SET ("replication_num" = "3");

-- Create backup for vector embeddings
BACKUP SNAPSHOT document_embeddings_backup_20240115
TO `s3://backup-bucket/starrocks/`
ON (document_embeddings)
PROPERTIES ("type" = "full");

-- Verify backup status
SHOW BACKUP FROM REPOSITORY `s3://backup-bucket/starrocks/`;

# HAProxy configuration for StarRocks FE
global
    daemon
    maxconn 4096

defaults
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

frontend starrocks_fe
    bind *:9030
    default_backend starrocks_fe_servers

backend starrocks_fe_servers
    balance roundrobin
    option tcp-check
    server fe1 fe1.internal:9030 check
    server fe2 fe2.internal:9030 check
    server fe3 fe3.internal:9030 check backup

-- Key metrics to monitor for HA
SELECT 
    backend_id,
    host,
    alive,
    system_decommissioned,
    disk_total_capacity,
    disk_available_capacity,
    cpu_cores
FROM information_schema.be_tablets
WHERE alive = false;

-- Monitor Frontend status
SHOW PROC '/frontends';

-- Check tablet replica health
SHOW PROC '/statistic';

• Frontend node unavailable for >30 seconds
• Backend node disk usage >85%
• Replica count below replication_num
• Query latency P95 >1000ms for vector searches

-- Separate metadata from embeddings for better compression
CREATE TABLE document_metadata (
    id BIGINT NOT NULL,
    document_id STRING NOT NULL,
    title STRING,
    author STRING,
    category STRING,
    tags ARRAY<STRING>,
    created_at DATETIME,
    tenant_id INT NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 16;

CREATE TABLE document_vectors (
    id BIGINT NOT NULL,
    document_id STRING NOT NULL,
    chunk_index INT,
    embedding VECTOR_FLOAT(1536),
    tenant_id INT NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 32;

    -- Time-based partitioning for vector data lifecycle management
CREATE TABLE document_embeddings (
    id BIGINT NOT NULL,
    document_id STRING NOT NULL,
    embedding VECTOR_FLOAT(1536),
    tenant_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP()
) ENGINE=OLAP
DUPLICATE KEY(id)
PARTITION BY RANGE(created_at) (
    PARTITION p202401 VALUES [('2024-01-01'), ('2024-02-01')),
    PARTITION p202402 VALUES [('2024-02-01'), ('2024-03-01')),
    PARTITION p202403 VALUES [('2024-03-01'), ('2024-04-01'))
)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "storage_cooldown_time" = "2024-04-01 00:00:00"
);

-- Dynamic partition management
ALTER TABLE document_embeddings SET (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.end" = "2",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "32"
);

-- Configure compaction for vector workloads
ALTER TABLE document_embeddings SET (
    "compaction_policy" = "time_series",
    "time_series_compaction_goal_size_mbytes" = "1024",
    "time_series_compaction_file_count_threshold" = "10",
    "time_series_compaction_time_threshold_seconds" = "3600"
);

-- Monitor compaction status
SHOW PROC '/compactions';

-- Storage optimization settings
SET GLOBAL enable_storage_vectorization = true;
SET GLOBAL vector_chunk_size = 4096;

-- Create resource groups for workload isolation
CREATE RESOURCE GROUP vector_workload
PROPERTIES (
    "cpu_core_limit" = "20",
    "mem_limit" = "40%",
    "concurrency_limit" = "10",
    "type" = "normal"
);

CREATE RESOURCE GROUP analytics_workload  
PROPERTIES (
    "cpu_core_limit" = "30",
    "mem_limit" = "50%",
    "concurrency_limit" = "20",
    "type" = "normal"
);

-- Assign users to resource groups
SET PROPERTY FOR 'vector_user' 'resource_group' = 'vector_workload';
SET PROPERTY FOR 'analytics_user' 'resource_group' = 'analytics_workload';

-- Complex hybrid query benchmark
SELECT d.id, d.chunk_text, d.similarity, m.title, m.category
FROM (
    SELECT id, chunk_text,
           COSINE_DISTANCE(embedding, [0.12, 0.45, ...]) as similarity
    FROM document_embeddings 
    WHERE tenant_id = 12345
      AND created_at >= '2024-01-01'
    ORDER BY similarity ASC
    LIMIT 100
) d
JOIN document_metadata m ON d.document_id = m.document_id
WHERE m.category IN ('technical', 'research')
  AND JSON_EXTRACT(m.tags, '$[*]') @> '["machine-learning"]'
ORDER BY d.similarity ASC
LIMIT 20;

-- Execution time: 45ms (StarRocks)
-- Equivalent multi-system approach: 120ms+ (vector DB + join in application)

-- RAG query with user permissions and content filtering
WITH relevant_chunks AS (
    SELECT 
        d.id,
        d.document_id,
        d.chunk_text,
        d.chunk_index,
        COSINE_DISTANCE(d.embedding, ${user_query_embedding}) as similarity
    FROM document_embeddings d
    JOIN document_permissions p ON d.document_id = p.document_id
    WHERE p.user_id = ${user_id}
      AND p.permission_type = 'read'
      AND d.tenant_id = ${tenant_id}
    ORDER BY similarity ASC
    LIMIT 50
),
filtered_content AS (
    SELECT 
        c.*,
        m.title,
        m.classification,
        m.last_updated
    FROM relevant_chunks c
    JOIN document_metadata m ON c.document_id = m.document_id
    WHERE m.classification != 'confidential'
      AND m.last_updated >= '2023-01-01'
)
SELECT 
    chunk_text,
    title,
    similarity,
    RANK() OVER (ORDER BY similarity ASC) as relevance_rank
FROM filtered_content
ORDER BY similarity ASC
LIMIT 10;

-- User-item recommendation using vector similarity
CREATE TABLE user_embeddings (
    user_id BIGINT NOT NULL,
    embedding VECTOR_FLOAT(256),
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP()
) ENGINE=OLAP
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16;

CREATE TABLE item_embeddings (
    item_id BIGINT NOT NULL,
    category STRING,
    embedding VECTOR_FLOAT(256),
    popularity_score DOUBLE,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP()
) ENGINE=OLAP
DUPLICATE KEY(item_id)
DISTRIBUTED BY HASH(item_id) BUCKETS 32;

-- Generate recommendations
SELECT 
    i.item_id,
    i.category,
    i.popularity_score,
    COSINE_DISTANCE(u.embedding, i.embedding) as similarity,
    i.popularity_score * (1 - COSINE_DISTANCE(u.embedding, i.embedding)) as recommendation_score
FROM user_embeddings u
CROSS JOIN item_embeddings i
WHERE u.user_id = ${target_user_id}
  AND i.category = ${preferred_category}
ORDER BY recommendation_score DESC
LIMIT 20;

-- Fraud detection based on transaction embedding similarity
WITH normal_patterns AS (
    SELECT embedding
    FROM transaction_embeddings
    WHERE label = 'normal'
      AND created_at >= CURRENT_DATE() - INTERVAL 30 DAY
),
transaction_scores AS (
    SELECT 
        t.transaction_id,
        t.amount,
        t.merchant_category,
        MIN(COSINE_DISTANCE(t.embedding, n.embedding)) as min_distance_to_normal
    FROM transaction_embeddings t
    CROSS JOIN normal_patterns n
    WHERE t.created_at >= CURRENT_DATE() - INTERVAL 1 DAY
    GROUP BY t.transaction_id, t.amount, t.merchant_category
)
SELECT 
    transaction_id,
    amount,
    merchant_category,
    min_distance_to_normal,
    CASE 
        WHEN min_distance_to_normal > 0.8 THEN 'HIGH_RISK'
        WHEN min_distance_to_normal > 0.6 THEN 'MEDIUM_RISK'
        ELSE 'LOW_RISK'
    END as risk_level
FROM transaction_scores
WHERE min_distance_to_normal > 0.5
ORDER BY min_distance_to_normal DESC;

-- Query performance monitoring
SELECT 
    DATE_TRUNC('hour', query_start_time) as hour,
    COUNT(*) as query_count,
    AVG(query_time_ms) as avg_latency_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY query_time_ms) as p95_latency_ms,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY query_time_ms) as p99_latency_ms
FROM information_schema.query_log
WHERE query LIKE '%COSINE_DISTANCE%'
  AND query_start_time >= CURRENT_DATE() - INTERVAL 1 DAY
GROUP BY DATE_TRUNC('hour', query_start_time)
ORDER BY hour DESC;

-- Backend node health check
SELECT 
    backend_id,
    host,
    alive,
    last_update_time,
    cpu_cores,
    mem_used_pct,
    disk_used_pct,
    num_running_queries
FROM information_schema.be_tablets
ORDER BY mem_used_pct DESC;

-- Compaction monitoring
SELECT 
    table_name,
    partition_name,
    compaction_score,
    total_file_size_mb,
    num_rowsets
FROM information_schema.table_compaction_status
WHERE compaction_score > 10
ORDER BY compaction_score DESC;

# StarRocks metrics for Prometheus
# /etc/starrocks/be.conf
enable_metric_calculator = true
metric_calculator_interval_seconds = 15
webserver_port = 8040

# Sample Prometheus configuration
- job_name: 'starrocks-be'
  static_configs:
    - targets: ['be1:8040', 'be2:8040', 'be3:8040']
  metrics_path: /metrics
  scrape_interval: 15s

- job_name: 'starrocks-fe'  
  static_configs:
    - targets: ['fe1:8030', 'fe2:8030', 'fe3:8030']
  metrics_path: /metrics
  scrape_interval: 15s

# Vector query latency
starrocks_be_query_latency{quantile="0.95", job="starrocks-be"}

# Memory usage for vector indexes
starrocks_be_memory_pool{type="vector_index"}

# Compaction queue length
starrocks_be_compaction{type="pending_tasks"}

# Query throughput
rate(starrocks_fe_query_total[5m])

-- Configure hot/cold storage for embeddings
CREATE TABLE document_embeddings_hot (
    id BIGINT NOT NULL,
    document_id STRING NOT NULL,
    embedding VECTOR_FLOAT(1536),
    tenant_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP()
) ENGINE=OLAP
DUPLICATE KEY(id)
PARTITION BY RANGE(created_at) (
    PARTITION p_current VALUES [('2024-01-01'), ('2024-02-01'))
)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "storage_medium" = "SSD",
    "storage_cooldown_time" = "2024-03-01 00:00:00"
);

-- Archive old embeddings to S3
CREATE TABLE document_embeddings_cold (
    id BIGINT NOT NULL,
    document_id STRING NOT NULL,  
    embedding VECTOR_FLOAT(1536),
    tenant_id INT NOT NULL,
    created_at DATETIME
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 16
PROPERTIES (
    "replication_num" = "2",
    "storage_medium" = "HDD"
);

-- Auto-scaling configuration for cloud deployments
# Kubernetes HPA for StarRocks Backend
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
  name: starrocks-be-hpa
spec:
  scaleTargetRef:
    apiVersion: apps/v1
    kind: StatefulSet
    name: starrocks-be
  minReplicas: 3
  maxReplicas: 12
  metrics:
  - type: Resource
    resource:
      name: cpu
      target:
        type: Utilization
        averageUtilization: 70
  - type: Resource
    resource:
      name: memory
      target:
        type: Utilization
        averageUtilization: 80

-- Use result caching for repeated vector searches
SET enable_sql_cache = true;
SET sql_cache_manage_num = 100;

-- Optimize vector queries with proper indexing
-- Bad: Full table scan
SELECT * FROM document_embeddings 
WHERE COSINE_DISTANCE(embedding, [...]) < 0.8;

-- Good: Use LIMIT with ORDER BY for top-k
SELECT id, document_id, COSINE_DISTANCE(embedding, [...]) as distance
FROM document_embeddings 
ORDER BY distance ASC
LIMIT 20;

-- Optimize with tenant isolation
SELECT id, document_id, COSINE_DISTANCE(embedding, [...]) as distance
FROM document_embeddings 
WHERE tenant_id = 12345  -- Partition pruning
ORDER BY distance ASC
LIMIT 20;

-- Future LangChain integration example
from langchain.vectorstores import StarRocks
from langchain.embeddings import OpenAIEmbeddings

# Native StarRocks vector store
vectorstore = StarRocks(
    connection_string="mysql://user:pass@starrocks-fe:9030/vectordb",
    table_name="document_embeddings",
    embedding_function=OpenAIEmbeddings()
)

# Automatic index creation and optimization
vectorstore.add_documents(documents)
results = vectorstore.similarity_search("query", k=10)

-- Query vectors stored in Iceberg tables
CREATE EXTERNAL TABLE lakehouse_embeddings (
    id BIGINT,
    document_id STRING,
    embedding VECTOR_FLOAT(1536),
    partition_date DATE
) ENGINE=ICEBERG
PROPERTIES (
    "iceberg.catalog.type" = "hive",
    "iceberg.catalog.hive.metastore.uris" = "thrift://hive-metastore:9083",
    "iceberg.table" = "vector_db.document_embeddings"
);

-- Federated vector search across data lake and StarRocks
SELECT 
    'local' as source,
    id, 
    document_id,
    COSINE_DISTANCE(embedding, [...]) as similarity
FROM document_embeddings
WHERE tenant_id = 12345

UNION ALL

SELECT 
    'lakehouse' as source,
    id,
    document_id, 
    COSINE_DISTANCE(embedding, [...]) as similarity
FROM lakehouse_embeddings
WHERE partition_date >= '2023-01-01'

ORDER BY similarity ASC
LIMIT 20;

JusDB Expert Insights & Recommendations

As database reliability experts, JusDB has extensive experience deploying and optimizing StarRocks for vector workloads across enterprise environments. Our recommendations:

When to Choose StarRocks Over Dedicated Vector Databases:

• Hybrid Workloads: You need both vector search and complex analytical queries
• Real-time Requirements: Sub-second latency from data ingestion to query availability
• Cost Optimization: Reducing infrastructure complexity and operational overhead
• Enterprise Features: RBAC, audit logging, backup/restore for vector data

When to Consider Alternatives:

• Pure Vector Use Cases: Only vector similarity search without analytical requirements
• Specialized Algorithms: Need for proprietary vector search algorithms
• Managed Service Priority: Preference for fully-managed cloud solutions

Ready to leverage StarRocks for your AI/LLM workloads? JusDB provides end-to-end consulting services for StarRocks deployment, optimization, and production support.

Contact JusDB today to design, size, and productionize StarRocks for your vector database requirements. Our experts will help you achieve optimal performance while minimizing operational complexity.

Frequently Asked Questions