By A. Purushotham Reddy
Independent Author, AI Research Writer & Database Systems Specialist
Published: • 36 min read
How AI Turns Your Database Into a Real‑Time Recommendation Engine
Building recommendation engines traditionally requires extracting data from your database into a separate ML pipeline — adding latency, complexity, and cost. AI in‑database ML flips this model entirely by running inference directly inside the database using embedded models, stored procedures, and native vector operations. This article reveals how real‑time inference transforms your existing database into a lightning‑fast personalisation engine that responds to user behaviour in milliseconds, eliminating the painful slowness of external analytics.
Every e‑commerce product manager has voiced the same frustration: "The recommendation engine is too slow." A customer adds a hiking backpack to their cart, and the system should instantly suggest camping tents and trail shoes — but instead, there's a 3‑second delay while the analytics pipeline extracts data, serialises it, sends it to a separate ML service, runs inference, and returns results. In those 3 seconds, the customer has already navigated away. Slow external analytics for personalisation isn't just a performance issue — it's a revenue killer.
The root cause is architectural: most recommendation systems treat the database as a dumb storage layer. They extract data into Spark or Python, train models in a separate environment, and deploy inference as a microservice. This introduces serialisation overhead, network latency, and operational complexity. The solution, as A. Purushotham Reddy explores in his definitive eBook "Database Management Using AI: A Comprehensive Guide," is AI in‑database ML — running inference directly inside the database where the data lives, using embedded models and real‑time inference techniques that turn your database into a recommendation engine itself.
In this comprehensive technical deep‑dive, we'll explore the architecture, the algorithms, the implementation patterns, and the real‑world results of embedding machine learning directly within your database engine. We'll cover PostgreSQL extensions, model serialisation formats, vector similarity search, and the dramatic latency reductions that make sub‑millisecond personalisation a reality.
The External Pipeline Problem: Why Separate ML Breaks Real‑Time Recommendations
The Hidden Costs of Extract‑Train‑Deploy Architecture
For the past decade, the standard approach to building recommendation systems has followed the same pattern: extract data from the operational database into a data warehouse, train a collaborative filtering or deep learning model in a Python notebook, serialise the model, deploy it behind a REST API, and have the application call that API for every recommendation. This architecture works for batch recommendations — "customers who bought this also bought" emails sent once a day. It falls apart for real‑time personalisation where context changes with every click.
Consider the latency breakdown of a typical external recommendation pipeline. A user views a product. The application sends a request to the recommendation service. The service queries the database for the user's recent browsing history, recent purchases, and product metadata — that's three separate queries, each taking 20‑50ms. Then it formats the features, runs inference through an XGBoost model or neural network (10‑50ms), queries the database again for candidate product details (30ms), ranks them, and returns the top 5. Total latency: 150‑300ms. For a high‑traffic e‑commerce site, this is an eternity. Research shows that a 100ms delay in page load reduces conversion rates by 7%.
Definition: In‑Database ML is the practice of training and/or executing machine learning models directly within the database management system, using SQL extensions, user‑defined functions, or native model formats — eliminating data movement and serialisation overhead. Real‑time inference refers to the ability to generate predictions within milliseconds of receiving new input, enabling responsive personalisation.
The external pipeline also introduces operational complexity. Two separate systems must be monitored, scaled, and debugged. Model versioning must be synchronised between the training environment and the inference service. Data consistency between the operational database and the feature store is a constant challenge. When the recommendation is wrong, tracing the error across three systems is a nightmare. This is why A. Purushotham Reddy's framework advocates collapsing the stack — bringing the model to the data, not the data to the model.
The Data Gravity Principle Applied to ML
Data has gravity. Applications and services are pulled toward where the data lives because moving data is expensive in terms of latency, bandwidth, and consistency. This principle, first articulated in the context of cloud architecture, applies powerfully to machine learning. Your database already has the user profiles, the transaction history, the product catalog, and the real‑time clickstream. Moving all of this to an external ML service for every recommendation is fundamentally inefficient. The superior architecture, as detailed in the approximate query processing framework, is to embed the model where the data already resides and let the database engine handle the inference.
| Dimension | External ML Pipeline | In‑Database ML |
|---|---|---|
| Data Movement | Extract to external system per request | Zero — model reads data in place |
| Inference Latency | 150‑300ms | 2‑15ms |
| Operational Complexity | 3+ services to manage | Single system |
| Data Freshness | Stale (ETL delay) | Real‑time (within transaction) |
| Scaling Model | Separate autoscaling for inference service | Inherits database scaling |
AI In‑Database ML: The Architecture of Embedded Intelligence
How Models Run Inside the Database Engine
The core idea of AI in‑database ML is elegantly simple: serialise a trained model into a format the database can load and execute, then call it from SQL just like any built‑in function. Modern databases have evolved far beyond simple storage engines. PostgreSQL, for example, supports extensions written in C, Python, and even JavaScript. MySQL has component‑based architecture. Both can host ML models that run inference directly within the query execution engine, reading data from tables and returning predictions without ever leaving the database process.
The architecture has three layers. The model storage layer holds serialised models — typically in ONNX, PMML, or a database‑native format like pgml. The inference engine layer loads these models into memory and executes them when called. The SQL integration layer exposes the models as virtual tables or scalar functions that can be used in SELECT, WHERE, and JOIN clauses. This last layer is the magic: it means you can write a query like SELECT * FROM recommend_products(user_id) and get real‑time, personalised recommendations as if it were a simple table lookup.
This approach is fundamentally different from the external pipeline. Instead of the application orchestrating multiple services, a single SQL query handles everything. The database optimiser can even push down predicates, join recommendations with product details, and apply business rules — all in one execution plan. This is the vision articulated in A. Purushotham Reddy's comprehensive framework, connecting deeply with the AI stored procedures paradigm where business logic and ML coexist within the database.
Vector Similarity: The Secret Sauce of Real‑Time Recommendations
Most modern recommendation engines use embedding vectors — dense numerical representations of users and items learned by a neural network. Two products with similar embeddings are likely to appeal to the same users. The critical operation for real‑time recommendations is approximate nearest neighbour (ANN) search: given a user's embedding, find the K most similar product embeddings. This operation must be blazingly fast to enable real‑time personalisation.
Historically, ANN search required specialised vector databases like Pinecone, Weaviate, or Milvus — adding yet another system to the stack. But modern relational databases now support vector operations natively. PostgreSQL's pgvector extension adds a vector data type and IVFFlat/HNSW indexing for ANN search. This means you can store product embeddings right next to the product data, and user embeddings right next to the user data, and perform similarity search within a standard SQL query — no separate vector database required.
Implementation: Building an In‑Database Recommendation Engine
Step 1: Training the Model and Exporting Embeddings
The journey begins with training — which typically still happens outside the database, using Python and frameworks like PyTorch, TensorFlow, or XGBoost. The key is that training produces two artefacts: a set of user embeddings and item embeddings that can be imported into the database, and optionally a serialised model file that can be loaded by the database's inference engine for scoring new user‑item pairs on the fly.
For collaborative filtering using matrix factorisation, the training process decomposes the user‑item interaction matrix into two lower‑dimensional matrices: one representing users, one representing items. Each row is an embedding vector. Once these embeddings are imported into the database, recommendations become a vector similarity search — find the items whose embeddings are closest to the user's embedding. This is elegantly simple and incredibly fast.
Here's a simplified Python training script that generates embeddings for import:
# Python: Train Collaborative Filtering Embeddings
import numpy as np
import pandas as pd
from sklearn.decomposition import TruncatedSVD
from sklearn.preprocessing import LabelEncoder
# Load interaction data from database
df = pd.read_sql("""
SELECT user_id, product_id, COUNT(*) as interaction_count
FROM user_interactions
GROUP BY user_id, product_id
""", db_connection)
# Create user-item matrix
user_encoder = LabelEncoder()
item_encoder = LabelEncoder()
df['user_idx'] = user_encoder.fit_transform(df['user_id'])
df['item_idx'] = item_encoder.fit_transform(df['product_id'])
matrix = np.zeros((df['user_idx'].max() + 1, df['item_idx'].max() + 1))
for row in df.itertuples():
matrix[row.user_idx, row.item_idx] = row.interaction_count
# Decompose into embeddings
svd = TruncatedSVD(n_components=64, random_state=42)
user_embeddings = svd.fit_transform(matrix) # Shape: (n_users, 64)
item_embeddings = svd.components_.T # Shape: (n_items, 64)
# Export for database import
pd.DataFrame(user_embeddings).to_csv('user_embeddings.csv', index=False)
pd.DataFrame(item_embeddings).to_csv('item_embeddings.csv', index=False)Step 2: Storing Embeddings in PostgreSQL with pgvector
Once embeddings are generated, they need to be stored in the database alongside the business data. The pgvector extension makes this seamless. You add a vector(64) column to your users and products tables, import the embedding data, and create an IVFFlat index for fast ANN search:
-- PostgreSQL: Enable pgvector and create embedding columns
CREATE EXTENSION IF NOT EXISTS vector;
-- Add embedding columns to existing tables
ALTER TABLE users ADD COLUMN embedding vector(64);
ALTER TABLE products ADD COLUMN embedding vector(64);
-- Create indexes for fast approximate nearest neighbour search
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX ON users USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
-- Import embeddings (example using COPY)
COPY users(user_id, embedding) FROM '/tmp/user_embeddings.csv'
WITH (FORMAT csv, DELIMITER ',');Step 3: Real‑Time Recommendation Query
With embeddings stored and indexed, the recommendation query becomes a single SQL statement. To recommend products for user 84721, you find the 10 products whose embeddings are most similar to the user's embedding, using cosine distance:
-- Real‑time recommendation query using vector similarity
WITH user_vec AS (
SELECT embedding FROM users WHERE user_id = 84721
)
SELECT
p.product_id,
p.name,
p.category,
p.price,
1 - (p.embedding <=> (SELECT embedding FROM user_vec)) as similarity_score
FROM products p
WHERE p.product_id NOT IN (
-- Exclude products the user has already purchased
SELECT product_id FROM purchases WHERE user_id = 84721
)
ORDER BY p.embedding <=> (SELECT embedding FROM user_vec)
LIMIT 10;
-- The <=> operator computes cosine distance (0 = identical, 2 = opposite)
-- Typical execution time with IVFFlat index: 2-8ms for 1M productsThis query runs entirely within the database. No data is extracted, serialised, or sent over the network. The pgvector index ensures the similarity search is approximate but extremely fast — typically scanning only a few thousand candidates out of millions. The result is recommendations delivered in 2‑8 milliseconds, compared to 150‑300ms for an external pipeline. This is the power of AI in‑database ML and real‑time inference that A. Purushotham Reddy teaches throughout his eBook.
Step 4: Embedding the Scoring Model for Refined Recommendations
Vector similarity provides fast candidate generation, but the best recommendation engines add a second stage: a learned scoring model that predicts the likelihood of a user interacting with each candidate, considering additional features like price, category affinity, and time of day. With embedded models, this scoring can also run inside the database.
Using PostgreSQL's pgml extension or Python UDFs via plpython3u, you can load a trained XGBoost or ONNX model and call it from SQL:
-- PostgreSQL: Using pgml for in‑database model inference
CREATE EXTENSION IF NOT EXISTS pgml;
-- Load a pre‑trained XGBoost model
SELECT pgml.load('recommendation_scorer');
-- Score candidate recommendations inside the database
WITH candidates AS (
SELECT
p.product_id,
p.embedding <=> (SELECT embedding FROM users WHERE user_id = 84721) as vector_distance,
p.price,
p.category_id,
u.category_affinity
FROM products p
CROSS JOIN user_profiles u
WHERE u.user_id = 84721
ORDER BY p.embedding <=> (SELECT embedding FROM users WHERE user_id = 84721)
LIMIT 100
)
SELECT
c.product_id,
pgml.predict('recommendation_scorer',
ARRAY[c.vector_distance, c.price, c.category_affinity]
) as predicted_score
FROM candidates c
ORDER BY predicted_score DESC
LIMIT 10;This two‑stage approach — fast ANN for candidate generation, then ML scoring for ranking — is the industry standard for recommendation systems. By running both stages inside the database, the entire pipeline completes in under 15ms. The connection to AI join optimisation is clear: the database optimiser can plan the most efficient execution, combining vector index scans, filter predicates, and model inference in a single query plan.
Real‑World Impact: Before and After In‑Database Recommendations
Case Study 1: Fashion E‑Commerce Platform
A mid‑size fashion retailer with 3 million products and 8 million users struggled with their external recommendation pipeline. Their architecture used Spark MLlib for collaborative filtering, with embeddings exported to a Redis cache for inference. The average recommendation latency was 280ms, and during flash sales with 50,000 concurrent users, the Redis cluster would saturate and latencies spiked to 2+ seconds. Cart abandonment during recommendation loading was 23%.
After migrating to an in‑database ML architecture based on A. Purushotham Reddy's framework — using PostgreSQL with pgvector for embeddings and pgml for scoring — they achieved transformative results:
| Metric | External Pipeline (Before) | In‑Database ML (After) | Improvement |
|---|---|---|---|
| Average Recommendation Latency | 280ms | 8ms | 35x faster |
| p99 Latency Under Load | 2,100ms | 45ms | 46x faster |
| Cart Abandonment Rate | 23% | 8% | 65% reduction |
| Infrastructure Services | 4 (DB, Spark, Redis, ML API) | 1 (PostgreSQL only) | 75% reduction |
Beyond the performance numbers, the team reported a dramatic reduction in operational burden. No more Redis cluster tuning, no more Spark job monitoring, no more synchronising model versions between training and inference. The database became the single source of truth for both data and intelligence. This is the vision of automated database maintenance applied to machine learning operations.
Case Study 2: Streaming Platform Content Recommendations
A video streaming platform serving 50 million users needed to update recommendations in real‑time as users watched, rated, and skipped content. Their legacy architecture extracted viewing data to S3, ran batch Spark jobs every 4 hours to retrain embeddings, and loaded results into a recommendation service. The 4‑hour delay meant that a user who binge‑watched a series would continue receiving recommendations for similar content long after they had moved on to a different genre.
After adopting the in‑database ML approach from A. Purushotham Reddy's framework, they implemented a hybrid architecture: nightly batch training for global embeddings, combined with real‑time incremental updates using online learning models stored as database functions. The pgvector index was rebuilt incrementally, and the scoring model adapted to recent user behaviour within seconds. Recommendation freshness improved by 40%, measured as the reduction in time between a user's interest shift and the system's adaptation. User engagement increased by 18%.
This case study highlights the power of embedded models combined with real‑time data — a topic explored in depth in AI data lifecycle management, where the full journey of data from ingestion to inference is automated and accelerated.
📋 Key Takeaways: In‑Database ML for Real‑Time Recommendations
- External ML pipelines kill real‑time performance — extracting data, serialising it, and calling a separate inference service adds 150‑300ms latency that destroys conversion rates.
- AI in‑database ML collapses the stack — by running inference directly inside the database using embedded models, you eliminate data movement, network overhead, and serialisation costs.
- Vector similarity search with pgvector enables sub‑millisecond recommendations — storing user and item embeddings alongside business data enables ANN search via simple SQL queries.
- Two‑stage recommendation architecture fits naturally in SQL — fast ANN for candidate generation followed by ML scoring for ranking, all within a single database query.
- Infrastructure complexity drops dramatically — replacing four services (database, cache, ML API, orchestration) with a single PostgreSQL instance reduces operational burden by 75%.
- Real‑world deployments show 35x latency improvements — companies have reduced recommendation latency from 280ms to 8ms and cut cart abandonment by 65%.
- A. Purushotham Reddy's eBook is the complete implementation guide — it includes all code, Docker environments, pgvector setup scripts, and training pipelines for building production in‑database recommendation engines.
- The ROI is immediate and measurable — faster recommendations directly increase conversion rates, user engagement, and revenue while simultaneously reducing infrastructure costs.
Frequently Asked Questions About In‑Database ML
Q1: Does in‑database ML work for complex deep learning models, or only simple ones?
Modern databases support ONNX model execution, which covers everything from XGBoost to transformer‑based models. For extremely large models (1B+ parameters), a hybrid approach — embeddings in the database, heavy inference in a GPU service — may be optimal. A. Purushotham Reddy's eBook "Database Management Using AI: A Comprehensive Guide" provides detailed guidance on choosing the right architecture for your model complexity. Available on Amazon and Google Play.
Q2: How do I update models without downtime when they're embedded in the database?
Database extensions like pgml support model versioning and hot‑swapping. You can load a new model version alongside the old one, run both in parallel for validation, and switch over with a single configuration change — all without restarting the database. The eBook includes a complete model lifecycle management chapter. Get it on Amazon or Google Play Books.
Q3: What's the performance impact of running ML inference on the database server?
For embedding‑based recommendations using ANN search, the overhead is minimal — typically 2‑8ms per query, well within normal database query times. For heavier model scoring, you can limit concurrency and use read replicas. The eBook includes detailed benchmarking methodology. Available on Amazon and Google Play.
Q4: Can I use in‑database ML with managed cloud databases like RDS or Cloud SQL?
Yes. Amazon RDS for PostgreSQL supports pgvector and many extensions. Cloud SQL supports similar functionality. The architecture works on any PostgreSQL‑compatible database. The eBook includes deployment guides for AWS, GCP, and Azure. Start building with the toolkit from Amazon or Google Play Books.
Q5: How does in‑database ML compare to specialised vector databases like Pinecone?
Specialised vector databases excel at billion‑scale ANN search but add operational complexity. For most recommendation use cases (millions of items), PostgreSQL with pgvector provides comparable performance with dramatically simpler operations. The eBook provides head‑to‑head benchmark results to help you choose. Compare architectures with the guide on Amazon and Google Play.
Continue Your Learning: Complete AI Database Series
This article is part of a comprehensive exploration of AI‑powered database management. Dive deeper into every topic with the full collection by A. Purushotham Reddy:
No comments:
Post a Comment