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.
1WITH2 table_match AS (3 SELECT4 m.title,5 m.movieId,6 m.vector7 FROM8 user_choice t9 INNER JOIN movie_with_tags_with_vectors m ON m.title = t.title10 WHERE11 userid = 'user1'12 ),13 movie_pairs AS (14 SELECT15 m1.movieId AS movieId1,16 m1.title AS title1,17 m2.movieId AS movieId2,18 m2.title AS title2,19 DOT_PRODUCT(m1.vector, m2.vector) AS similarity20 FROM21 table_match m122 CROSS JOIN movie_with_tags_with_vectors m223 WHERE24 m1.movieId != m2.movieId25 AND NOT EXISTS (26 SELECT27 128 FROM29 user_choice uc30 WHERE31 uc.userid = 'user1'32 AND uc.title = m2.title33 )34 ),35 movie_match AS (36 SELECT37 movieId1,38 title1,39 movieId2,40 title2,41 similarity42 FROM43 movie_pairs44 ORDER BY45 similarity DESC46 ),47 distinct_count AS (48 SELECT DISTINCT49 movieId2,50 title2 AS Title,51 ROUND(AVG(similarity), 4) AS Rating_Match52 FROM53 movie_match54 GROUP BY55 movieId2,56 title257 ORDER BY58 Rating_Match DESC59 ),60 average_ratings AS (61 SELECT62 movieId,63 AVG(rating) AS Avg_Rating64 FROM65 ratings66 GROUP BY67 movieId68 )69SELECT70 dc.Title,71 dc.Rating_Match as 'Match Score',72 ROUND(ar.Avg_Rating, 1) AS 'Average User Rating'73FROM74 distinct_count dc75 JOIN average_ratings ar ON dc.movieId2 = ar.movieId76ORDER BY77 dc.Rating_Match DESC78LIMIT79 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 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.
1-- Combine real-time inventory, user behavior, and vector similarity2SELECT3 p.product_id,4 p.name,5 p.price,6 i.stock_level,7 DOT_PRODUCT(vec, JSON_ARRAY_PACK('[0.23,0.54,0.43,0.87]') as8similarity_score9FROM products p10JOIN inventory i ON p.product_id = i.product_id11WHERE i.stock_level > 012AND p.category = :user_category13ORDER BY similarity_score DESC14LIMIT 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 query2SELECT3 category,4 COUNT(*) as document_count,5 AVG(6 DOT_PRODUCT(embedding, JSON_ARRAY_PACK('[0.23,0.54,0.43,0.87]'))7 ) as avg_relevance,8 JSON_AGG(title) as matching_titles9FROM documents10WHERE MATCH(TABLE content) AGAINST('body:SingleStore')11GROUP BY category12HAVING 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
Multi-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 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
1CREATE 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 OpenAI6 category VARCHAR(50),7 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,8 metadata JSON,9 FULLTEXT INDEX ft_idx (content), -- For keyword search10 SHARD KEY (id)11);12 13-- Create vector index for similarity search14ALTER TABLE documents15ADD VECTOR INDEX idx_embedding (embedding)16INDEX_OPTIONS '{"metric_type": "DOT_PRODUCT"}';
1INSERT INTO documents (title, content, embedding, category, metadata)2VALUES 3 ('SingleStore Overview', 4 'SingleStore is a distributed SQL database...', 5 '[0.1, 0.2, ..., 0.5]', -- Replace with actual embedding6 '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 embedding11 '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 search2DELIMITER //3 4CREATE 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 INT10)11BEGIN12 DECLARE EXIT HANDLER FOR SQLEXCEPTION13 BEGIN14 ROLLBACK;15 RESIGNAL;16 END;17 18 START TRANSACTION;19 20 -- Validate inputs21 IF JSON_VALID(search_vector) = 0 THEN22 SIGNAL SQLSTATE '45000'23 SET MESSAGE_TEXT = 'Invalid vector format';24 END IF;25 26 -- Execute search with timeouts and limits27 SELECT28 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 (34 DOT_PRODUCT(embedding, '[0.1, 0.2, ..., 0.5]') * 0.7 +35 MATCH(TABLE content) AGAINST('body:database') * 0.336 ) as combined_score -- Calculate combined score (70% vector, 30% text)37FROM documents38WHERE39 category = 'Technical' -- Filter by category40 AND MATCH(TABLE documents) AGAINST(body:'database') > 0 -- Ensure some text relevance41GROUP BY42 category43HAVING44 combined_score > 0.545ORDER BY46 combined_score DESC47LIMIT 10;
The query would return a table with the following columns:
title— Stringcategory— 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:
.png?width=1024&disable=upscale&auto=webp)
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.
