SingleStore logo

  • Pricing

User Icon
LoginL
Start free
SingleStore logo
Arrow Left Icon

Blog

/

Engineering

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

Clock Icon

7 min read

Pencil Icon

Jan 30, 2025

Madhukar Kumar, Chief Dev Evangelist

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.

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

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
                   1
               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
   5;

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-conceptsUnderstanding vector database concepts

what-is-a-vector-databaseWhat 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.

embeddingsEmbeddings

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-metricsVector 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-methodsVector 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-applicationsReal-world applications

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

real-time-product-recommendationsReal-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-analyticsSemantic 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.

-- Search documents and aggregate insights in one query
SELECT
    category,
    COUNT(*) as document_count,
    AVG(DOT_PRODUCT(embedding, JSON_ARRAY_PACK('[0.23,0.54,0.43,0.87]') as
avg_relevance,
    JSON_AGG(title) as matching_titles
FROM documents
WHERE MATCH(TABLE content) AGAINST('body:SingleStore')
GROUP BY category
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).

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

multi-modal-data-storageMulti-modal data storage

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-capabilitiesUnified 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-featuresVector 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-systemPractical 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.

11. Create the table

CREATE TABLE documents (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    embedding VECTOR(1536),  -- Embeddings from a model for ex OpenAI
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metadata JSON,
    FULLTEXT INDEX ft_idx (content),  -- For keyword search
    SHARD KEY (id)
);


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

2-insert-sample-data2. Insert sample data

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

3-production-ready-search-implementation3. 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:

-- Create a procedure for robust vector search
DELIMITER //


CREATE PROCEDURE search_documents(
    IN search_vector VARCHAR(8000),
    IN search_text VARCHAR(255),
    IN min_score FLOAT,
    IN category_filter VARCHAR(50),
    IN batch_size INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;


    START TRANSACTION;


    -- Validate inputs
    IF JSON_VALID(search_vector) = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid vector format';
    END IF;


    -- Execute search with timeouts and limits
    SELECT
        title,
    category,
    DOT_PRODUCT(embedding, '[0.1, 0.2, ..., 0.5]') as vector_score,
    MATCH(TABLE content) AGAINST('body:database')as text_score,
    JSON_EXTRACT_STRING(metadata, '$.author') as author,
    -- Calculate combined score (70% vector, 30% text)
    (DOT_PRODUCT(embedding, '[0.1, 0.2, ..., 0.5]') * 0.7 +
     MATCH(TABLE content) AGAINST('body:database') * 0.3) as combined_score
FROM documents
WHERE
    -- Filter by category
    category = 'Technical'
    -- Ensure some text relevance
    AND MATCH(TABLE documents) AGAINST(body:'database') > 0
GROUP BY
    category
HAVING
    combined_score > 0.5
ORDER BY
    combined_score DESC
LIMIT 10;

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-considerationsArchitectural 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-single-storeStart 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.

On this page

EngineeringProduct

Share

related-readingRelated reading

How We Made Jupyter Notebooks Load 10 Times Faster

Blog

How We Made Jupyter Notebooks Load 10 Times Faster

Engineering
Faster Portal Project: A Detailed Overview

Blog

Faster Portal Project: A Detailed Overview

Engineering
Introducing the Vercel Integration for SingleStore Helios

Blog

Introducing the Vercel Integration for SingleStore Helios

Engineering
New Performance Benchmark for Live Dashboards and Fast Updates

Blog

New Performance Benchmark for Live Dashboards and Fast Updates

Engineering
Third Normal Form, Star Schema, and a Performance Centric Data Strategy

Blog

Third Normal Form, Star Schema, and a Performance Centric Data Strategy

Engineering
Running SingleStore&#8217;s 107 Node Test Infrastructure on CoreOS

Blog

Running SingleStore&#8217;s 107 Node Test Infrastructure on CoreOS

Engineering

Start building with SingleStore

Start freeAngle Right IconTalk to a specialist

Explore more resources

Book IconDocumentationCircle Dollar IconPricingCircle Play IconGet started with SingleStore