SingleStore as a Vector Database for AI Applications: A Developer's Guide

If you are building an AI application, chances are you are using unstructured and structured data, which means you need vector similarity search, exact keyword text search and a way to also retrieve data from SQL or JSON documents.

Typically this requires multiple databases, yet they are not geared for working with real-time data and analytics — all of which are now table stakes for all AI applications.

In other words, AI applications require complex queries across multiple data types and enterprise-grade performance at scale. This guide shows how to use SingleStore's unified database platform for building sophisticated AI applications that go beyond basic vector operations with “single-shot retrievals” both in SQL or using JSON.

If you are looking for a <tl;dr> on what is different about SingleStore, here is an example of single-shot retrieval relevant for all Retrieval Augmented Generation (RAG) use cases. In the following query, we are retrieving all recommended movies for a specific user using vector search and rich analytics in one round-trip.

WITH

table_match AS (

SELECT

m.title,

m.movieId,

m.vector

FROM

user_choice t

INNER JOIN movie_with_tags_with_vectors m ON m.title =

t.title

WHERE

userid = 'user1'

movie_pairs AS (

SELECT

m1.movieId AS movieId1,

m1.title AS title1,

m2.movieId AS movieId2,

m2.title AS title2,

DOT_PRODUCT(m1.vector, m2.vector) AS similarity

FROM

table_match m1

CROSS JOIN movie_with_tags_with_vectors m2

WHERE

m1.movieId != m2.movieId

AND NOT EXISTS (

SELECT

FROM

user_choice uc

WHERE

uc.userid = 'user1'

AND uc.title = m2.title

)

movie_match AS (

SELECT

movieId1,

title1,

movieId2,

title2,

similarity

FROM

movie_pairs

ORDER BY

similarity DESC

distinct_count AS (

SELECT DISTINCT

movieId2,

title2 AS Title,

ROUND(AVG(similarity), 4) AS Rating_Match

FROM

movie_match

GROUP BY

movieId2,

title2

ORDER BY

Rating_Match DESC

average_ratings AS (

SELECT

movieId,

AVG(rating) AS Avg_Rating

FROM

ratings

GROUP BY

movieId

)

SELECT

dc.Title,

dc.Rating_Match as 'Match Score',

ROUND(ar.Avg_Rating, 1) AS 'Average User Rating'

FROM

distinct_count dc

JOIN average_ratings ar ON dc.movieId2 = ar.movieId

ORDER BY

dc.Rating_Match DESC

LIMIT

But before we dig deeper into the details, let’s first look at some core concepts. If you are familiar with these concepts you can skip to the real-world applications section.

Understanding vector database concepts

What is a vector database?

A vector database is a specialized database system designed to store and query high-dimensional vectors, typically used in AI and machine learning applications. These vectors are numerical representations (embeddings) of data like text, images or audio, created by machine learning models.

Embeddings

Embeddings are dense numerical vectors that capture semantic meaning from raw data. Sometimes, embeddings are used interchangeably with vectors. For example, a text embedding might be a 1536-dimensional vector (common with OpenAI's models) where similar texts have similar vector representations. Some key aspects of embeddings:

They preserve semantic relationships in their vector space
Similar items have smaller distances between their vectors (often measured using some well known methods like dot_product)
They enable semantic search and similarity comparisons
Dimensions typically range from 128 to 1536 or more
Embeddings are generated by passing the data through an embedding model

Vector similarity metrics

Vector databases use several similarity metrics to compare vectors:

Dot product

Simple multiplication and sum of corresponding vector elements
Higher values indicate greater similarity
Best for comparing vectors of similar magnitudes

Cosine similarity

Measures the cosine of the angle between vectors
Range: -1 to 1 (1 being most similar)
Good for comparing vectors of different magnitudes

Euclidean Distance

Measures straight-line distance between vector endpoints
Lower values indicate greater similarity
Good for comparing vectors in absolute terms

Vector indexing methods

Indexing is a data structure technique that improves the speed of data retrieval operations by creating additional data structures (indices) that provide optimized paths to locate data. SingleStore supports specialized vector indexes for efficient similarity search:

Hierarchical Navigable Small World (HNSW)

Creates a hierarchical graph structure
Excellent search performance with high recall
Higher memory usage
Best for high-precision requirements

Inverted File with Product Quantization and Fast Scan (IVF_PQFS)

Combines clustering and vector compression
Excellent balance of speed, memory and accuracy
Significantly faster index builds
Good for production deployments with large datasets

The choice between HNSW and IVF_PQFS depends on your requirements:

Use HNSW when accuracy is critical and memory is available
Use IVF_PQFS when dealing with large datasets or memory constraints

Unlike other databases, you can create both of these indices on the same column for different query use cases in SingleStore. In addition, once you create an index on a vector, the data becomes immediately available for query. This is typically not possible in some vector-only databases that run indexing async and incrementally.

Real-world applications

Before diving into implementation details, let's look at some practical scenarios where SingleStore's unified approach shines.

Real-time product recommendations

Traditionally, product recommendations happen after an event has occurred and the analytics is run in a batch mode in a data warehouse. This is called next-session personalization and recommendation. With SingleStore, users can bring in streaming data from Kafka stream, run live analytics and combine them with vectors to build real-time “in-session” recommendations and personalization systems.

-- Combine real-time inventory, user behavior, and vector similarity

SELECT

p.product_id,

p.name,

p.price,

i.stock_level,

DOT_PRODUCT(vec, JSON_ARRAY_PACK('[0.23,0.54,0.43,0.87]') as

similarity_score

FROM products p

JOIN inventory i ON p.product_id = i.product_id

WHERE i.stock_level > 0

AND p.category = :user_category

ORDER BY similarity_score DESC

LIMIT 10;

This query shows how we combine vector similarity with real-time inventory data — something that otherwise requires multiple systems or complex ETL with traditional architectures.

Semantic search with analytics

Newer AI applications don’t just require data augmentation from enterprise data, but often fresh analytical data to make decisions in real time. This is critical for building agentic or agent-based systems. For example, if a support agent is helping customers with a known issue, the agent may need to access real-time logs, other support tickets and run some analytics to provide accurate data. SingleStore is ideal for agentic applications and use cases like this, given that you can run both semantic and lexical searches across multiple data types — along with analytics in a single shot.

1
-- Search documents and aggregate insights in one query
2
SELECT
3
    category,
4
    COUNT(*) as document_count,
5
    AVG(DOT_PRODUCT(embedding, JSON_ARRAY_PACK('[0.23,0.54,0.43,0.87]') as
6
avg_relevance,
7
    JSON_AGG(title) as matching_titles
8
FROM documents
9
WHERE MATCH(TABLE content) AGAINST('body:SingleStore')
10
GROUP BY category
11
HAVING avg_relevance > 0.7;

This demonstrates combining vector search, full-text search and analytical aggregations in a single query — eliminating the need for multiple round trips or complex application logic (single-shot retrieval).

SingleStore's unique approach to vector operations

Most AI applications require multi-modal data and SingleStore stands out by offering:

Native vector data type alongside traditional SQL types
Ability to store vectors, JSON, text and structured data in the same table
Immediate availability of newly inserted vectors (no indexing delay)
Support for multiple vector indices on the same column
Memory-first three-tier architecture that provides milliseconds response times across petabytes of data

Unified query capabilities

SingleStore enables:

Single-query combination of vector similarity search and SQL analytics
Hybrid search combining vector similarity and keyword matching
Complex joins between vector and non-vector data
Real-time analytics on vector search results

Vector features

Native VECTOR data type with dimension specification
Multiple indexing options (HNSW, IVF, PQ) on the same column
Comprehensive vector operations (dot_product, normalization, etc.)
BM25-based keyword search integration

Practical example: Building a document search system

To demonstrate the simplicity of using one database to build an entire AI or agentic application, let's create a simple document search system that combines vector search with traditional SQL capabilities. To try it out, sign up for a free SingleStore trial.

1. Create the table

1
CREATE TABLE documents (
2
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
3
    title VARCHAR(255),
4
    content TEXT,
5
    embedding VECTOR(1536),  -- Embeddings from a model for ex OpenAI
6
    category VARCHAR(50),
7
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
8
    metadata JSON,
9
    FULLTEXT INDEX ft_idx (content),  -- For keyword search
10
    SHARD KEY (id)
11
);
12

13
-- Create vector index for similarity search
14
ALTER TABLE documents
15
ADD VECTOR INDEX idx_embedding (embedding)
16
INDEX_OPTIONS '{"metric_type": "DOT_PRODUCT"}';

2. Insert sample data

1
INSERT INTO documents (title, content, embedding, category, metadata)
2
VALUES 
3
    ('SingleStore Overview', 
4
     'SingleStore is a distributed SQL database...', 
5
     '[0.1, 0.2, ..., 0.5]',  -- Replace with actual embedding
6
     'Technical',
7
     '{"author": "John Doe", "tags": ["database", "technical"]}'),
8
    ('Vector Search Guide', 
9
     'Understanding vector similarity search...', 
10
     '[0.15, 0.25, ..., 0.45]',  -- Replace with actual embedding
11
     'Tutorial',
12
     '{"author": "Jane Smith", "tags": ["AI", "search"]}');

3. Production-ready search implementation

Let’s create a single query and wrap it in a stored procedure to give it more enterprise-grade heft, including error handling and connection management by combining vector similarity search, keyword matching and SQL analytics:

1
-- Create a procedure for robust vector search
2
DELIMITER //
3

4
CREATE PROCEDURE search_documents(
5
    IN search_vector VARCHAR(8000),
6
    IN search_text VARCHAR(255),
7
    IN min_score FLOAT,
8
    IN category_filter VARCHAR(50),
9
    IN batch_size INT
10
)
11
BEGIN
12
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
13
    BEGIN
14
        ROLLBACK;
15
        RESIGNAL;
16
    END;
17

18
    START TRANSACTION;
19

20
    -- Validate inputs
21
    IF JSON_VALID(search_vector) = 0 THEN
22
        SIGNAL SQLSTATE '45000'
23
        SET MESSAGE_TEXT = 'Invalid vector format';
24
    END IF;
25

26
    -- Execute search with timeouts and limits
27
    SELECT
28
        title,
29
    category,
30
    DOT_PRODUCT(embedding, '[0.1, 0.2, ..., 0.5]') as vector_score,
31
    MATCH(TABLE content) AGAINST('body:database')as text_score,
32
    JSON_EXTRACT_STRING(metadata, '$.author') as author,
33
    -- Calculate combined score (70% vector, 30% text)
34
    (DOT_PRODUCT(embedding, '[0.1, 0.2, ..., 0.5]') * 0.7 +
35
     MATCH(TABLE content) AGAINST('body:database') * 0.3) as combined_score
36
FROM documents
37
WHERE
38
    -- Filter by category
39
    category = 'Technical'
40
    -- Ensure some text relevance
41
    AND MATCH(TABLE documents) AGAINST(body:'database') > 0
42
GROUP BY
43
    category
44
HAVING
45
    combined_score > 0.5
46
ORDER BY
47
    combined_score DESC
48
LIMIT 10;
49

The query would return a table with the following columns:

title — String
category — String (will only show 'Technical' due to WHERE clause)
vector_score — Float (dot_product result, typically between -1 and 1)
text_score — Float (MATCH AGAINST score, typically between 0 and 1)
author — String (extracted from JSON metadata)
combined_score — Float (weighted average of vector_score and text_score)

Here's an example of what the results might look like:

A few important notes about this output:

Only 'Technical' category results appear due to the WHERE clause
All results have text_score > 0 due to the WHERE clause condition
All results have combined_score > 0.5 due to the HAVING clause
Results are sorted by combined_score in descending order
The GROUP BY category means we'll get one result per category (in this case, just one group since we filtered for 'Technical')

This query demonstrates several key SingleStore capabilities:

Vector similarity search using DOT_PRODUCT
Full-text search using MATCH AGAINST
JSON field extraction
Complex scoring combining multiple similarity metrics
Traditional SQL operations (GROUP BY, HAVING, ORDER BY)
All in a single, efficient query

Architectural considerations

Having looked at a simple example, let’s also consider the bigger picture from the architectural perspective. Here's how SingleStore fits into a scalable AI stack:

Application layers

API layer. REST/GraphQL endpoints for vector operations. Within the SingleStore cloud service you can create a Jupyter Notebook and expose SQL queries as API endpoints similar to a Lambda function. You can also run periodic async jobs as scheduled notebooks, with the advantage of running processing local to your data.
Service layer. Business logic and AI model integration. Here you may call an embedding model to convert raw data into vectors. Within SingleStore, you can also run open source embedding model directly in Jupyter notebooks (currently in private preview).
Data layer. SingleStore as a unified store for:
- Vector embeddings
- Operational data (row based, JSON)
- Analytics (columnar data)
- Full-text search
- Real-time updates (Kafka, etc.)
Cache layer. Optional for frequently accessed vectors or even as a semantic LRU cache

Integration patterns

Direct SQL for simple applications
ORM (e.g., SQLAlchemy or Drizzle) for complex applications
CDC (Change Data Capture) in and out for real-time synchronization with existing systems
Streaming ingestion for real-time vector updates

Monitoring and observability

Track vector operation latency
Monitor index performance
Set up alerts for memory usage
Log vector search quality metrics

Start building with SingleStore

Given the inherent need to retrieve different kinds of data types using both semantic and keyword search along with deep analytics, SingleStore is a good fit for modern AI applications that are used by several large and small AI applications companies. Check out this cheat sheet or head over to the SingleStore Spaces gallery and give this a try.

Build production-ready vector databases quickly. Start free with SingleStore today.

SingleStore as a Vector Database for AI Applications: A Developer's Guide

Understanding vector database concepts

What is a vector database?

Embeddings

Vector similarity metrics

Vector indexing methods

Real-world applications

Real-time product recommendations

Semantic search with analytics

SingleStore's unique approach to vector operations

Unified query capabilities

Vector features

Practical example: Building a document search system

1. Create the table

2. Insert sample data

3. Production-ready search implementation

Architectural considerations

Start building with SingleStore

On this page

Start building with SingleStore

Explore more resources

SingleStore as a Vector Database for AI Applications: A Developer's Guide

understanding-vector-database-conceptsUnderstanding vector database concepts

what-is-a-vector-databaseWhat is a vector database?

embeddingsEmbeddings

vector-similarity-metricsVector similarity metrics

vector-indexing-methodsVector indexing methods

real-world-applicationsReal-world applications

real-time-product-recommendationsReal-time product recommendations

semantic-search-with-analyticsSemantic search with analytics

single-stores-unique-approach-to-vector-operationsSingleStore's unique approach to vector operations

multi-modal-data-storageMulti-modal data storage

unified-query-capabilitiesUnified query capabilities

vector-featuresVector features

practical-example-building-a-document-search-systemPractical example: Building a document search system

11. Create the table

2-insert-sample-data2. Insert sample data

3-production-ready-search-implementation3. Production-ready search implementation

architectural-considerationsArchitectural considerations

start-building-with-single-storeStart building with SingleStore

On this page

related-readingRelated reading

Start building with SingleStore

Explore more resources

Understanding vector database concepts

What is a vector database?

Embeddings

Vector similarity metrics

Vector indexing methods

Real-world applications

Real-time product recommendations

Semantic search with analytics

SingleStore's unique approach to vector operations

Multi-modal data storage

Unified query capabilities

Vector features

Practical example: Building a document search system

1. Create the table

2. Insert sample data

3. Production-ready search implementation

Architectural considerations

Start building with SingleStore

Related reading