{
"@context": "https://schema.org",
"@type": "FAQPage",
"mainEntity": [
{
"@type": "Question",
"name": "What is a real-time recommendation system?",
"acceptedAnswer": {
"@type": "Answer",
"text": "A real-time recommendation system continuously updates its signals and serves personalized suggestions within milliseconds of each user action. Unlike batch systems that retrain overnight, real-time systems use streaming pipelines to keep interaction scores, item co-occurrence matrices, and trending signals fresh at all times."
}
},
{
"@type": "Question",
"name": "How do you build a recommendation system in SQL?",
"acceptedAnswer": {
"@type": "Answer",
"text": "You build a recommendation system in SQL by creating materialized views that continuously compute user-item interaction scores, item co-occurrence matrices, category affinity profiles, and trending signals from event streams. A streaming database like RisingWave maintains these views incrementally so each query returns fresh results without waiting for a batch job."
}
},
{
"@type": "Question",
"name": "Why do batch recommendation pipelines fail at personalization?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Batch pipelines compute signals on a fixed schedule, usually nightly. Any user action that happens after the last run is invisible to the model until the next run. A purchase made at noon will not affect recommendations until the following morning. This 12 to 24-hour freshness gap means users see items they just bought, categories they just left, and trends that have already peaked."
}
},
{
"@type": "Question",
"name": "What is the difference between item-based and user-based collaborative filtering?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Item-based collaborative filtering finds items that are frequently co-viewed or co-purchased, then recommends items similar to what the current user has engaged with. User-based collaborative filtering finds users with similar taste profiles and recommends what those similar users liked. Item-based scales better because the item catalog is typically smaller and more stable than the user base."
}
},
{
"@type": "Question",
"name": "Can RisingWave power a production recommendation system?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Yes. RisingWave is a PostgreSQL-compatible streaming database that maintains materialized views incrementally as events arrive. You can ingest user events from Kafka, compute recommendation signals as materialized views, and serve results over a standard PostgreSQL connection using psycopg2 or any PostgreSQL client. The views update within milliseconds, so recommendations reflect the latest user behavior at every page load."
}
}
]
}
When Batch Recommendations Become a Customer Service Problem
Picture this: a customer spends twenty minutes on your site selecting the perfect pair of running shoes. They compare three options, add one to their cart, hesitate, then buy. Conversion complete.
They come back two days later looking for running socks. What do they see in the recommendation carousel? Running shoes. The exact model they just bought, plus four variations of it. The homepage banner highlights trail runners. The sidebar says "based on your recent activity" and shows more shoes.
This is not a bad algorithm. The algorithm is doing exactly what it was trained to do. It saw a strong purchase signal for running shoes, and it is acting on it. The problem is that the signal it saw came from last night's batch run. The algorithm does not know the purchase already happened.
The customer clicks away to a competitor.
This failure happens millions of times per day across e-commerce platforms, streaming services, and content sites. And it is almost entirely a latency problem, not a model quality problem.
Why Recommendations Are Worth Getting Right
The business stakes are not abstract. Amazon attributes roughly 35% of its total revenue to its recommendation engine. Netflix has estimated that keeping subscribers engaged through personalized recommendations saves approximately $1 billion per year in churn prevention. Spotify credits playlist and track recommendations for a substantial share of its listening hours.
These numbers reflect a fundamental truth about consumer behavior: the moment of highest purchase intent is the moment of engagement. A user browsing running shoes right now has purchase intent for running shoes right now, not for the item they bought three days ago.
Every hour of staleness in your recommendation signals is a direct tax on conversion. A recommendation system that updates within seconds of each user action captures intent at its peak. A system that updates overnight is, in effect, a different product.
The gap between those two products is exactly what this article closes. We will build a complete, production-ready recommendation pipeline in SQL using RisingWave, a streaming database that maintains materialized views in real time.
The Architecture: Two Layers, One Principle
A real-time recommendation system splits naturally into two layers.
The write layer is your transactional system: PostgreSQL, MySQL, or whatever database powers your application. This is where purchases land, where user profiles live, and where your product catalog sits. This layer is optimized for writes and ACID consistency. It is not designed for analytical queries across millions of events.
The read layer is where recommendation signals are computed and served. This is where you need continuous aggregation over event streams, joins across multiple tables, and low-latency query responses. This is RisingWave's job.
The key principle is that RisingWave does not replace your recommendation model. It is the observation layer that keeps every signal feeding your model fresh. Instead of your model reading 24-hour-old aggregates from a batch pipeline, it reads sub-second aggregates from streaming materialized views.
User actions flow from your application into Kafka. RisingWave consumes those events and maintains materialized views that update within milliseconds. Your recommendation service queries those views over a standard PostgreSQL connection. The result: recommendations that reflect what users are doing right now.
Setting Up the Event Pipeline
All SQL in this article runs on RisingWave v2.x and follows the PostgreSQL-compatible dialect. You can run it on RisingWave Cloud or locally.
Step 1: Ingest User Events from Kafka
User events are the raw material for every recommendation signal. Views, cart additions, purchases, and likes all carry different weights and should all flow through the same source.
CREATE SOURCE user_events_source
WITH (
connector = 'kafka',
topic = 'user-events',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
)
FORMAT PLAIN ENCODE JSON;
CREATE TABLE user_events (
event_id TEXT,
user_id TEXT,
item_id TEXT,
event_type TEXT, -- 'view', 'add_to_cart', 'purchase', 'like'
category TEXT,
session_id TEXT,
event_time TIMESTAMPTZ
) FROM user_events_source;
Two design decisions worth noting here. First, event_type is a text field rather than an enum. This keeps the schema flexible as new event types emerge (wishlist additions, shares, reviews) without requiring a migration. Second, session_id is included at the source. Session-level co-occurrence is a powerful recommendation signal, and you need the session identifier to compute it.
For local development or testing without Kafka, you can create the table directly and insert mock events:
-- Local testing: insert events manually
INSERT INTO user_events VALUES
('evt001', 'user_A', 'item_001', 'view', 'footwear', 'sess_1', NOW()),
('evt002', 'user_A', 'item_002', 'view', 'footwear', 'sess_1', NOW()),
('evt003', 'user_A', 'item_001', 'add_to_cart', 'footwear', 'sess_1', NOW()),
('evt004', 'user_A', 'item_001', 'purchase', 'footwear', 'sess_1', NOW()),
('evt005', 'user_B', 'item_001', 'view', 'footwear', 'sess_2', NOW()),
('evt006', 'user_B', 'item_003', 'view', 'apparel', 'sess_2', NOW()),
('evt007', 'user_B', 'item_002', 'purchase', 'footwear', 'sess_2', NOW());
You also need an items table for metadata enrichment at serving time. This can be populated via CDC from your product database:
CREATE TABLE items (
item_id TEXT PRIMARY KEY,
title TEXT,
category TEXT,
subcategory TEXT,
price DECIMAL,
image_url TEXT,
created_at TIMESTAMPTZ
);
Step 2: Score User-Item Interactions
Not all interactions carry equal weight. A purchase is a strong positive signal. A view is weak. An add-to-cart falls somewhere in between. This materialized view assigns weights and aggregates them into a single interaction score per user-item pair.
CREATE MATERIALIZED VIEW user_item_scores AS
SELECT
user_id,
item_id,
SUM(CASE
WHEN event_type = 'purchase' THEN 10
WHEN event_type = 'add_to_cart' THEN 5
WHEN event_type = 'like' THEN 3
WHEN event_type = 'view' THEN 1
ELSE 0
END) AS interaction_score,
COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
COUNT(*) FILTER (WHERE event_type = 'add_to_cart') AS cart_count,
MAX(event_time) AS last_interaction
FROM user_events
GROUP BY user_id, item_id;
This view updates within milliseconds whenever a new event arrives. The moment a user adds an item to their cart, their interaction_score for that item jumps from 1 (view) to 6 (view + add_to_cart). When they purchase, it jumps to 16. Any downstream view that depends on user_item_scores also updates immediately.
The purchase_count column serves double duty: it powers recommendation scores and, as we will see at serving time, it filters out items the user has already bought.
Step 3: Build Category Affinity Profiles
Category affinity tells you which product categories a user gravitates toward. This is a crucial filter at serving time: even if the item-based model generates a strong recommendation for a power drill, that recommendation is irrelevant for a user whose entire history is in fashion.
CREATE MATERIALIZED VIEW user_category_affinity AS
SELECT
user_id,
category,
SUM(CASE
WHEN event_type = 'purchase' THEN 10
WHEN event_type = 'add_to_cart' THEN 5
WHEN event_type = 'view' THEN 1
ELSE 0
END) AS affinity_score,
COUNT(DISTINCT item_id) AS distinct_items,
MAX(event_time) AS last_activity
FROM user_events
GROUP BY user_id, category;
A user with a high affinity_score in "footwear" and a low one in "electronics" will get footwear-biased recommendations even when the pure collaborative filtering signal might suggest electronics. This view is the personalization filter that wraps the recommendation scores.
Step 4: Compute Item Co-occurrence Within Sessions
The core intuition of collaborative filtering is: "users who interacted with item A also interacted with item B." Within-session co-occurrence is the cleanest version of this signal. If two items appear in the same browsing session, there is a real behavioral connection between them, not just a coincidence from users who happened to buy both over the course of months.
CREATE MATERIALIZED VIEW item_coviewed AS
SELECT
a.item_id AS item_a,
b.item_id AS item_b,
COUNT(*) AS coview_count,
COUNT(DISTINCT a.user_id) AS unique_users
FROM user_events a
JOIN user_events b
ON a.session_id = b.session_id
AND a.item_id < b.item_id -- prevents duplicates (A,B) and (B,A)
AND a.event_type = 'view'
AND b.event_type = 'view'
GROUP BY a.item_id, b.item_id;
The a.item_id < b.item_id condition is important. Without it, each co-occurrence pair would appear twice: once as (A, B) and once as (B, A). By enforcing a lexicographic order, we store each pair once and halve the table size.
unique_users adds noise resistance. An item pair with coview_count = 100 but unique_users = 2 might just be two users who both had long sessions. An item pair with coview_count = 50 and unique_users = 40 is a much stronger signal.
Step 5: Track Trending Items by Hour
Trending signals are time-sensitive by definition. An item that went viral on social media an hour ago should rank higher than one that sold well last quarter. Tumble windows let you compute discrete hourly counts without state accumulation.
CREATE MATERIALIZED VIEW trending_items AS
SELECT
item_id,
category,
COUNT(*) AS interaction_count,
COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
COUNT(*) FILTER (WHERE event_type = 'add_to_cart') AS cart_count,
COUNT(DISTINCT user_id) AS unique_users,
window_start,
window_end
FROM TUMBLE(user_events, event_time, INTERVAL '1' HOUR)
GROUP BY item_id, category, window_start, window_end;
Note the syntax: FROM TUMBLE(table, time_column, interval) with window_start and window_end in the GROUP BY. This is the correct RisingWave syntax. Do not use GROUP BY TUMBLE(...) -- that is invalid.
You can query this view filtered to the most recent window to get currently trending items:
-- Items trending in the current hour
SELECT item_id, category, interaction_count, purchase_count
FROM trending_items
WHERE window_end = (SELECT MAX(window_end) FROM trending_items)
ORDER BY interaction_count DESC
LIMIT 20;
Step 6: Generate Recommendation Scores
This is the view that ties everything together. It combines the user's historical interaction scores with item co-occurrence weights to produce a recommendation score for every (user, candidate_item) pair.
CREATE MATERIALIZED VIEW recommendation_scores AS
SELECT
uis.user_id,
cv.item_b AS recommended_item_id,
SUM(uis.interaction_score * cv.coview_count) AS recommendation_score,
COUNT(DISTINCT uis.item_id) AS supporting_items
FROM user_item_scores uis
JOIN item_coviewed cv ON uis.item_id = cv.item_a
GROUP BY uis.user_id, cv.item_b;
Walk through the logic: for user A, who has viewed item 1 (score 1) and purchased item 2 (score 16), we look up all items that co-occur with item 1 and item 2 in session data. If item 3 co-occurred 20 times with item 1 and 5 times with item 2, the recommendation score for (user A, item 3) is (1 20) + (16 5) = 100. If item 4 co-occurred 3 times with item 2 only, its score is 48. Item 3 ranks higher.
The supporting_items count is a quality signal. A recommendation backed by three interacted items is more reliable than one backed by a single view.
The Serving Layer: Querying at Recommendation Time
The materialized views above are always current. Serving recommendations is a standard SQL query against live data.
Top Recommendations Filtered by Category Affinity
-- Get top 10 recommendations for a user,
-- filtered to their preferred categories and excluding purchased items
SELECT
r.recommended_item_id,
i.title,
i.category,
r.recommendation_score,
r.supporting_items
FROM recommendation_scores r
JOIN user_category_affinity a
ON r.user_id = a.user_id
AND a.affinity_score > 5 -- only categories the user actually cares about
JOIN items i
ON r.recommended_item_id = i.item_id
AND i.category = a.category
WHERE r.user_id = $1
AND r.recommended_item_id NOT IN (
SELECT item_id
FROM user_item_scores
WHERE user_id = $1
AND purchase_count > 0 -- exclude already-purchased items
)
ORDER BY r.recommendation_score DESC
LIMIT 10;
This query runs in milliseconds because every view it touches is pre-computed and indexed. There is no aggregation happening at query time. RisingWave has already done the work.
Trending Items for Cold-Start Users
New users have no interaction history, so collaborative filtering produces nothing. Fall back to trending items filtered by the categories they have glanced at:
-- Cold-start recommendations: trending in categories the new user browsed
SELECT
t.item_id,
i.title,
t.category,
t.interaction_count,
t.purchase_count
FROM trending_items t
JOIN items i ON t.item_id = i.item_id
WHERE t.category IN (
SELECT category
FROM user_events
WHERE user_id = $1
AND event_time > NOW() - INTERVAL '1 hour'
)
AND t.window_end = (SELECT MAX(window_end) FROM trending_items)
ORDER BY t.purchase_count DESC, t.interaction_count DESC
LIMIT 10;
Recently Trending Complementary Items
For product detail pages, surface items that are both co-viewed with the current item AND trending right now:
-- "Others also viewed" on a product page, boosted by trending signal
SELECT
cv.item_b AS related_item_id,
i.title,
cv.coview_count,
cv.unique_users,
COALESCE(t.interaction_count, 0) AS trending_boost
FROM item_coviewed cv
JOIN items i ON cv.item_b = i.item_id
LEFT JOIN trending_items t
ON t.item_id = cv.item_b
AND t.window_end = (SELECT MAX(window_end) FROM trending_items)
WHERE cv.item_a = $1
ORDER BY (cv.coview_count * cv.unique_users) + COALESCE(t.interaction_count, 0) DESC
LIMIT 10;
Why This Beats a Nightly Batch Pipeline
The difference shows up the moment a user takes an action.
In the batch world: user buys running shoes at 2pm. Batch job runs at 2am. For the next 12 hours, every recommendation algorithm treats this user as a "running shoe buyer" even though they just completed that purchase and are now looking for socks. The recommendation engine is optimizing for a conversion that already happened.
In the streaming world: user buys running shoes at 2pm. The user_item_scores view updates within milliseconds. The recommendation_scores view that depends on it also updates. By 2:00:00.500pm, the user's interaction score for running shoes already reflects the purchase. The next page load serves fresh recommendations.
The latency gap is not 12 hours. It is half a second.
This matters even more for cart abandonment recovery. If a user adds an item to their cart and then navigates away, a real-time system can immediately serve recommendations for complementary items ("you have shoes in your cart -- here are socks and insoles"). A batch system cannot do this because the cart addition event has not been processed yet.
What RisingWave Is Doing Under the Hood
RisingWave processes the user_events table as an unbounded stream. Each materialized view is compiled into a streaming operator graph. When a new row arrives in user_events, it propagates through the graph, updating only the affected rows in each view. A new purchase event for (user_A, item_1) updates exactly one row in user_item_scores and a bounded number of rows in recommendation_scores -- not the entire table.
This incremental computation is what makes sub-second freshness feasible. A full recomputation of recommendation_scores across all users every time any event arrives would be impossibly expensive. Incremental updates to affected rows are cheap, even at scale.
State is checkpointed to S3-compatible storage automatically, so the views survive restarts without reprocessing from scratch. You can add more compute nodes to increase throughput. Distribution happens automatically at the GROUP BY key.
Connecting to AI Agents and MCP
Once your recommendation views are live, any system that speaks PostgreSQL can query them. Python services connect with psycopg2:
import psycopg2
conn = psycopg2.connect(
host="your-risingwave-host",
port=4566,
dbname="dev",
user="root",
password=""
)
cur = conn.cursor()
cur.execute("""
SELECT recommended_item_id, recommendation_score
FROM recommendation_scores
WHERE user_id = %s
ORDER BY recommendation_score DESC
LIMIT 10
""", (user_id,))
recommendations = cur.fetchall()
AI agents and LLM-powered systems can access the same views through the RisingWave MCP server. An agent handling a customer support conversation can query user_item_scores to understand what a user has been looking at, or query recommendation_scores to proactively suggest relevant products. The data is always current, so the agent is never working from a stale snapshot.
This is where streaming recommendations and agentic systems intersect: agents that need to reason about user behavior in real time need access to real-time state, not hourly snapshots.
Scaling Beyond Heuristic Recommendations
The SQL pipeline in this article produces strong heuristic recommendations: items that co-occur with things you have liked, filtered to categories you prefer. This is the same approach used by Amazon's original item-based collaborative filtering system, described in their 2003 paper, which powered recommendations at scale before neural models became tractable.
For teams that want to go deeper, the next step is typically matrix factorization or neural collaborative filtering (NCF). These approaches learn latent embeddings for users and items that capture patterns the co-occurrence matrix cannot represent directly.
RisingWave supports vector operations natively, which means you can store and query learned embeddings alongside streaming signals:
-- Add embedding column to items table for vector-based similarity
ALTER TABLE items ADD COLUMN embedding vector(128);
-- Create HNSW index for fast approximate nearest-neighbor search
CREATE INDEX item_embedding_idx ON items
USING hnsw (embedding vector_cosine_ops);
-- Find items similar to a target item by embedding cosine distance
SELECT item_id, title, category,
embedding <=> $1::vector(128) AS cosine_distance
FROM items
WHERE item_id != $2
ORDER BY cosine_distance ASC
LIMIT 20;
You can also use OpenAI embeddings for semantic similarity over product descriptions:
-- Generate embedding for a product description
SELECT openai_embedding(
'sk-...', -- API key
'text-embedding-3-small', -- model
title || ' ' || description -- input text
)::vector(1536) AS embedding
FROM items
WHERE item_id = 'item_001';
The architecture adapts: instead of (or in addition to) the co-occurrence-based recommendation_scores view, your serving layer can query by vector similarity. The streaming signals from earlier views (interaction scores, category affinity, trending) still apply as re-ranking signals on top of the embedding-based candidates.
End-to-End Data Flow Summary
Here is the complete picture of what we built:
- User actions hit your application and publish to a Kafka topic.
- RisingWave ingests events from Kafka through a source connector.
- Five materialized views update in real time: interaction scores, category affinity, item co-occurrence, trending items, and recommendation scores.
- Your recommendation service queries those views over a standard PostgreSQL connection.
- New events immediately ripple through the view graph, so every query returns current results.
The entire pipeline from Kafka event to queryable recommendation takes under one second. There is no batch job to schedule, no Airflow DAG to maintain, and no separate feature store to synchronize. The streaming computation, state management, and low-latency serving all happen inside RisingWave.
Frequently Asked Questions
How is this different from a feature store?
A feature store like Feast or Tecton separates feature computation from feature serving. You run a batch or streaming job to compute features, write them to the store, and then your model reads from the store at inference time. RisingWave collapses these into a single system: materialized views are both the computation definition and the serving layer. You query them directly over PostgreSQL. This reduces operational complexity significantly, particularly for smaller teams.
Can this handle millions of users and items?
Yes. RisingWave distributes materialized view computation across multiple compute nodes. Queries that group by user_id are automatically partitioned by that key, so adding compute nodes increases throughput linearly. Item co-occurrence computation is more expensive because it involves a self-join on session_id, but the a.item_id < b.item_id constraint halves the work, and the join is bounded by session length in practice. Production deployments at this scale typically add a caching layer (Redis) in front of the recommendation query for the most active users.
Does this replace machine learning?
No. The SQL pipeline here produces heuristic recommendations based on co-occurrence and interaction weights. These are strong baselines that many production systems run successfully. But they cannot capture latent patterns the way matrix factorization or neural collaborative filtering can. The right architecture uses both: the streaming SQL layer provides fresh signals, and a trained model uses those signals as features. RisingWave supports vector columns and HNSW indexes if you want to store model embeddings alongside streaming features.
How do I handle the cold-start problem?
Cold-start -- no interaction history for a new user -- is addressed by the trending_items view. New users see trending items in categories they have browsed, even within their first session. As they accumulate interactions, the user_item_scores and recommendation_scores views begin to populate, and collaborative filtering takes over. The transition is smooth because both views are always being queried; trending items simply have a higher weight when collaborative scores are sparse.
What events should I track?
The minimum viable event set is: page view (item_id, user_id, timestamp), add to cart, and purchase. With just these three, you can populate all views in this article. From there, add: search queries (to infer intent without click), dismissals (to suppress items the user actively rejected), wishlist additions (a strong positive signal between a view and a purchase), and social actions like shares and likes if your platform supports them. Each additional event type sharpens the signals without changing the schema.
