Hybrid Database Architecture: SQL + NoSQL E-Learning Guide

🔗 Hybrid Database Architecture: SQL + NoSQL Integration Tutorial

🎯 What You'll Learn

By the end of this tutorial, you will:

Understand why single-database solutions fail at scale
Design a polyglot persistence architecture using PostgreSQL, MongoDB, Redis, and Elasticsearch
Write integration commands that sync data across databases
Know exactly which database to use for every data type
Build API routing logic that queries the right store

🤔 Part 1: Why Hybrid Architecture?

The Single Database Problem

If You Use Only SQL	If You Use Only NoSQL
Rich lesson content forces schema migrations	Enrollment/payment data lacks ACID safety
Activity logs slow down with millions of rows	Complex reports require MapReduce
Quiz questions need 10+ tables	User relationships are hard to query

The Hybrid Solution

┌─────────────────────────────────────────────────────────────────┐
│                     FRONTEND (React/Vue)                        │
└─────────────────────────────────────────────────────────────────┘
                              │
        ┌─────────────────────┼─────────────────────┐
        ▼                     ▼                     ▼
┌───────────────┐    ┌───────────────┐    ┌───────────────┐
│   Auth API    │    │  Course API   │    │ Content API   │
│  (SQL Users)  │    │  (SQL Meta)   │    │ (NoSQL Docs)  │
└───────────────┘    └───────────────┘    └───────────────┘
        │                     │                     │
        ▼                     ▼                     ▼
┌─────────────────────────────────────────────────────────────────┐
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────────────┐ │
│  │  PostgreSQL │◄──►│   Redis     │◄──►│    MongoDB          │ │
│  │  (Users,    │    │  (Cache,    │    │  (Course Content,   │ │
│  │  Enrollments│    │  Sessions,  │    │  Logs, Discussions) │ │
│  │  Progress)  │    │  Leaderboard│    │                     │ │
│  └─────────────┘    └─────────────┘    └─────────────────────┘ │
│                                                                 │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  Elasticsearch (Search: courses, lessons, discussions)  │   │
│  └─────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

🗄️ Part 2: PostgreSQL — The Structured Core

What Goes Here

User accounts, roles, authentication
Course metadata (title, price, status)
Enrollments, payments, subscriptions
Lesson progress tracking
Reviews and ratings

Why PostgreSQL?

ACID compliance for financial transactions
Foreign key constraints prevent orphan data
Complex JOINs for analytics and reporting
JSONB support for semi-structured data when needed

Core Schema

sql
1-- Users table
2CREATE TABLE users (
3    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4    email VARCHAR(255) UNIQUE NOT NULL,
5    password_hash VARCHAR(255) NOT NULL,
6    full_name VARCHAR(100) NOT NULL,
7    role VARCHAR(20) DEFAULT 'student' CHECK (role IN ('student', 'instructor', 'admin')),
8    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
9);
10
11-- Courses table
12CREATE TABLE courses (
13    course_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
14    instructor_id UUID NOT NULL REFERENCES users(user_id),
15    title VARCHAR(200) NOT NULL,
16    slug VARCHAR(200) UNIQUE NOT NULL,
17    price DECIMAL(10,2) DEFAULT 0.00,
18    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
19    enrolled_count INT DEFAULT 0,
20    rating_avg DECIMAL(2,1) DEFAULT 0.0,
21    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
22);
23
24-- Enrollments table
25CREATE TABLE enrollments (
26    enrollment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
27    user_id UUID NOT NULL REFERENCES users(user_id),
28    course_id UUID NOT NULL REFERENCES courses(course_id),
29    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
30    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'dropped')),
31    progress_pct DECIMAL(5,2) DEFAULT 0.00,
32    UNIQUE(user_id, course_id)
33);

Practical Commands

sql
1-- Enroll a student (ACID transaction)
2BEGIN;
3    INSERT INTO enrollments (user_id, course_id, status)
4    VALUES ('user-uuid-123', 'course-uuid-456', 'active');
5    
6    UPDATE courses 
7    SET enrolled_count = enrolled_count + 1 
8    WHERE course_id = 'course-uuid-456';
9COMMIT;
10
11-- Get student dashboard with enrolled courses
12SELECT 
13    c.course_id,
14    c.title,
15    c.slug,
16    c.thumbnail_url,
17    e.progress_pct,
18    e.enrolled_at,
19    u.full_name AS instructor_name
20FROM enrollments e
21JOIN courses c ON e.course_id = c.course_id
22JOIN users u ON c.instructor_id = u.user_id
23WHERE e.user_id = 'user-uuid-123'
24AND e.status = 'active'
25ORDER BY e.enrolled_at DESC;
26
27-- Revenue report for instructor
28SELECT 
29    c.title,
30    c.price,
31    c.enrolled_count,
32    (c.price * c.enrolled_count) AS total_revenue
33FROM courses c
34WHERE c.instructor_id = 'instructor-uuid-789'
35AND c.status = 'published';

🍃 Part 3: MongoDB — The Content Store

What Goes Here

Full course curriculum (modules, lessons, nested content)
Quiz questions with flexible structures
User-generated notes and highlights
Activity logs (high write volume)
Discussion threads with nested replies

Why MongoDB?

Flexible schema for varying lesson types
Document nesting eliminates JOINs
High write throughput for activity tracking
Horizontal scaling via sharding

Core Collections

javascript
1// courses_content collection
2{
3  _id: "course_001",
4  title: "Full-Stack Web Development",
5  slug: "fullstack-web-dev",
6  metadata: {
7    level: "intermediate",
8    category: "Programming",
9    tags: ["javascript", "react", "node.js"],
10    last_updated: ISODate("2026-08-10T10:00:00Z")
11  },
12  modules: [
13    {
14      module_id: "mod_001",
15      order: 1,
16      title: "JavaScript Fundamentals",
17      lessons: [
18        {
19          lesson_id: "les_001",
20          order: 1,
21          title: "Variables & Data Types",
22          type: "video",
23          duration_mins: 15,
24          video_url: "https://cdn.example.com/videos/les_001.mp4",
25          transcript: "In this lesson...",
26          resources: [
27            { name: "Cheat Sheet", url: "https://cdn.example.com/sheets/js.pdf" }
28          ]
29        },
30        {
31          lesson_id: "les_002",
32          order: 2,
33          title: "Async/Await Deep Dive",
34          type: "text",
35          content: {
36            body: "<h2>Understanding Promises</h2><p>...</p>",
37            code_snippets: [
38              {
39                language: "javascript",
40                code: "async function fetchData() { ... }",
41                output: "Data loaded"
42              }
43            ]
44          }
45        }
46      ]
47    }
48  ]
49}

Practical Commands

javascript
1// Insert complete course curriculum
2db.courses_content.insertOne({
3  _id: "course_001",
4  title: "Full-Stack Web Development",
5  slug: "fullstack-web-dev",
6  metadata: {
7    level: "intermediate",
8    tags: ["javascript", "react", "node.js"]
9  },
10  modules: [
11    {
12      module_id: "mod_001",
13      order: 1,
14      title: "JavaScript Fundamentals",
15      lessons: [
16        {
17          lesson_id: "les_001",
18          order: 1,
19          title: "Variables & Data Types",
20          type: "video",
21          duration_mins: 15,
22          video_url: "https://cdn.example.com/videos/les_001.mp4"
23        }
24      ]
25    }
26  ],
27  created_at: new Date(),
28  updated_at: new Date()
29});
30
31// Get full course curriculum in one query
32db.courses_content.findOne({ slug: "fullstack-web-dev" });
33
34// Add a new module to existing course
35db.courses_content.updateOne(
36  { _id: "course_001" },
37  {
38    $push: {
39      modules: {
40        module_id: "mod_002",
41        order: 2,
42        title: "React Framework",
43        lessons: []
44      }
45    },
46    $set: { updated_at: new Date() }
47  }
48);
49
50// Activity logs collection
51db.user_activity_logs.insertOne({
52  user_id: "user_123",
53  course_id: "course_001",
54  events: [
55    {
56      type: "video_play",
57      lesson_id: "les_001",
58      timestamp: new Date(),
59      metadata: { play_position: 0, device: "desktop" }
60    },
61    {
62      type: "video_pause",
63      lesson_id: "les_001",
64      timestamp: new Date(),
65      metadata: { pause_position: 300, device: "desktop" }
66    }
67  ]
68});
69
70// Append new event atomically
71db.user_activity_logs.updateOne(
72  { user_id: "user_123", course_id: "course_001" },
73  {
74    $push: {
75      events: {
76        type: "quiz_submit",
77        lesson_id: "les_003",
78        timestamp: new Date(),
79        metadata: { score: 85, time_taken_secs: 480 }
80      }
81    }
82  }
83);
84
85// Discussion threads with nested replies
86db.course_discussions.insertOne({
87  course_id: "course_001",
88  lesson_id: "les_002",
89  user_id: "user_123",
90  title: "Confused about Promise.all()",
91  body: "Can someone explain...",
92  replies: [
93    {
94      reply_id: "rep_001",
95      user_id: "user_456",
96      body: "Use Promise.all when all promises must succeed...",
97      timestamp: new Date(),
98      is_instructor: true,
99      likes: 5
100    }
101  ],
102  tags: ["javascript", "promises"],
103  is_resolved: false,
104  created_at: new Date()
105});

⚡ Part 4: Redis — The Speed Layer

What Goes Here

User sessions (JWT blacklists, refresh tokens)
Course catalog cache
Leaderboards
Rate limiting counters
Real-time progress bars

Why Redis?

Sub-millisecond latency for hot data
TTL support for automatic expiration
Pub/Sub for real-time notifications
Sorted sets for leaderboards

Practical Commands

bash
1# Cache course catalog for 5 minutes
2SET "catalog:published" '{"courses":[...]}' EX 300
3
4# Store user session with 24h expiry
5SET "session:user_123" '{"role":"student","enrolled":[1,2,3]}' EX 86400
6
7# Increment daily active users counter
8INCR "dau:2026-08-13"
9
10# Leaderboard for course completion
11ZADD "leaderboard:course_001" 100 "user_123" 95 "user_456" 88 "user_789"
12
13# Get top 10 students
14ZREVRANGE "leaderboard:course_001" 0 9 WITHSCORES
15
16# Rate limit API calls (10 requests per minute)
17INCR "rate_limit:user_123:11:45"
18EXPIRE "rate_limit:user_123:11:45" 60
19
20# Check if course is cached before hitting PostgreSQL
21GET "course:slug:fullstack-web-dev"
22# If null: query PostgreSQL, then SET cache

🔍 Part 5: Elasticsearch — The Search Engine

What Goes Here

Course title and description indexing
Lesson content full-text search
Discussion thread search
Fuzzy matching for typos

Why Elasticsearch?

Full-text search across millions of documents
Fuzzy matching ("javscript" → "javascript")
Aggregations for search analytics
Highlighting for search result snippets

Practical Commands

json
1// Index a course document
2PUT /courses/_doc/course_001
3{
4  "title": "Full-Stack Web Development",
5  "description": "Master HTML, CSS, JavaScript, React, Node.js and PostgreSQL...",
6  "slug": "fullstack-web-dev",
7  "level": "intermediate",
8  "tags": ["javascript", "react", "node.js"],
9  "instructor": "Sarah Johnson",
10  "price": 49.99,
11  "rating_avg": 4.8,
12  "enrolled_count": 15420
13}
14
15// Search courses with fuzzy matching
16GET /courses/_search
17{
18  "query": {
19    "multi_match": {
20      "query": "javscript react",
21      "fields": ["title^3", "description", "tags"],
22      "fuzziness": "AUTO"
23    }
24  },
25  "highlight": {
26    "fields": {
27      "title": {},
28      "description": {}
29    }
30  }
31}
32
33// Filtered search: intermediate level, under $100
34GET /courses/_search
35{
36  "query": {
37    "bool": {
38      "must": [
39        { "match": { "description": "web development" } }
40      ],
41      "filter": [
42        { "term": { "level": "intermediate" } },
43        { "range": { "price": { "lte": 100 } } }
44      ]
45    }
46  },
47  "sort": [
48    { "rating_avg": "desc" },
49    { "enrolled_count": "desc" }
50  ]
51}
52
53// Aggregations: popular tags
54GET /courses/_search
55{
56  "size": 0,
57  "aggs": {
58    "popular_tags": {
59      "terms": {
60        "field": "tags.keyword",
61        "size": 10
62      }
63    }
64  }
65}

🔗 Part 6: Integration Patterns

Pattern 1: SQL + MongoDB Sync

When a course is published in PostgreSQL, sync its content to MongoDB.

sql
1-- PostgreSQL: Publish course
2UPDATE courses 
3SET status = 'published', published_at = CURRENT_TIMESTAMP
4WHERE course_id = 'course-uuid-456'
5RETURNING course_id, title, slug, instructor_id, level, price;

javascript
1// MongoDB: Update content metadata to match
2db.courses_content.updateOne(
3  { _id: "course_456" },
4  {
5    $set: {
6      "metadata.level": "intermediate",
7      "metadata.price": 49.99,
8      "metadata.is_published": true,
9      "metadata.published_at": new Date()
10    }
11  }
12);

Pattern 2: Cache-Aside with Redis

javascript
1// Node.js pseudocode for course detail API
2async function getCourseDetail(slug) {
3  // 1. Check Redis cache
4  const cached = await redis.get(`course:${slug}`);
5  if (cached) return JSON.parse(cached);
6  
7  // 2. Query PostgreSQL for metadata
8  const courseMeta = await pg.query(
9    'SELECT * FROM courses WHERE slug = $1', [slug]
10  );
11  
12  // 3. Query MongoDB for content
13  const courseContent = await mongo.collection('courses_content')
14    .findOne({ slug });
15  
16  // 4. Combine and cache
17  const result = { ...courseMeta, content: courseContent };
18  await redis.setex(`course:${slug}`, 300, JSON.stringify(result));
19  
20  return result;
21}

Pattern 3: Search Index Sync

When a course is updated, reindex in Elasticsearch.

javascript
1// After course update in PostgreSQL
2const course = await pg.query('SELECT * FROM courses WHERE course_id = $1', [id]);
3
4// Reindex in Elasticsearch
5await es.index({
6  index: 'courses',
7  id: course.course_id,
8  document: {
9    title: course.title,
10    description: course.description,
11    slug: course.slug,
12    level: course.level,
13    price: course.price,
14    rating_avg: course.rating_avg,
15    enrolled_count: course.enrolled_count
16  }
17});

Pattern 4: Write-Through Logging

Activity logs write to MongoDB immediately, aggregate to PostgreSQL nightly.

javascript
1// Real-time: Write to MongoDB
2db.user_activity_logs.updateOne(
3  { user_id, course_id },
4  { $push: { events: { type: 'video_complete', lesson_id, timestamp: new Date() } } }
5);
6
7// Nightly batch job: Aggregate to PostgreSQL
8const pipeline = [
9  { $match: { 'events.timestamp': { $gte: yesterday, $lt: today } } },
10  { $unwind: '$events' },
11  { $group: {
12      _id: { user_id: '$user_id', course_id: '$course_id', lesson_id: '$events.lesson_id' },
13      watch_time: { $sum: '$events.metadata.duration' }
14  }}
15];
16// Update lesson_progress in PostgreSQL with aggregated data

📊 Part 7: When to Use Which — Decision Matrix

Data Type	Storage	Reason	Example Query
User accounts	PostgreSQL	ACID compliance, strict schema	`SELECT * FROM users WHERE email = ?`
Payments	PostgreSQL	Transaction safety, reporting	`BEGIN; INSERT INTO payments...; COMMIT;`
Course metadata	PostgreSQL	Relational integrity, catalog	`SELECT * FROM courses WHERE status = 'published'`
Enrollments	PostgreSQL	Unique constraints, progress tracking	`SELECT * FROM enrollments WHERE user_id = ?`
Lesson content	MongoDB	Flexible schema, rich documents	`db.courses_content.findOne({ slug })`
Quiz questions	MongoDB	Varying structures per type	`db.courses_content.findOne({ "modules.lessons.quiz_data": ... })`
Activity logs	MongoDB	High write throughput, time-series	`db.user_activity_logs.updateOne({ $push: { events } })`
Discussions	MongoDB	Nested replies, flexible

🧪 Part 8: Hands-On Integration Exercise

Exercise: Build a Course Detail API

Write the complete data flow for fetching a course detail page.

Requirements:

Check Redis cache first
If miss: query PostgreSQL for course metadata
Query MongoDB for course content (modules, lessons)
Query Elasticsearch for "related courses"
Cache result in Redis for 5 minutes
Return combined response

Solution (Pseudocode):

javascript
1async function getCourseDetail(slug, userId) {
2  const cacheKey = `course:detail:${slug}`;
3  
4  // 1. Redis cache check
5  let result = await redis.get(cacheKey);
6  if (result) return JSON.parse(result);
7  
8  // 2. PostgreSQL: course metadata + enrollment status
9  const courseMeta = await pg.query(`
10    SELECT c.*, u.full_name as instructor_name,
11           e.progress_pct, e.status as enrollment_status
12    FROM courses c
13    JOIN users u ON c.instructor_id = u.user_id
14    LEFT JOIN enrollments e ON c.course_id = e.course_id AND e.user_id = $2
15    WHERE c.slug = $1 AND c.status = 'published'
16  `, [slug, userId]);
17  
18  // 3. MongoDB: full curriculum content
19  const courseContent = await mongo.collection('courses_content')
20    .findOne({ slug }, { projection: { modules: 1, metadata: 1 } });
21  
22  // 4. Elasticsearch: related courses
23  const related = await es.search({
24    index: 'courses',
25    query: {
26      more_like_this: {
27        fields: ['title', 'tags'],
28        like: [{ _index: 'courses', _id: courseMeta.course_id }],
29        min_term_freq: 1
30      }
31    },
32    size: 4
33  });
34  
35  // 5. Combine and cache
36  result = {
37    meta: courseMeta,
38    content: courseContent,
39    related: related.hits.hits
40  };
41  
42  await redis.setex(cacheKey, 300, JSON.stringify(result));
43  return result;
44}

⚡ Part 9: Performance Optimization Checklist

Layer	Optimization	Command
PostgreSQL	Index foreign keys	`CREATE INDEX idx_enrollments_user ON enrollments(user_id)`
PostgreSQL	Partial indexes	`CREATE INDEX idx_courses_published ON courses(status) WHERE status = 'published'`
MongoDB	Compound indexes	`db.courses_content.createIndex({ slug: 1, "metadata.level": 1 })`
MongoDB	Text indexes	`db.courses_content.createIndex({ title: "text", description: "text" })`
Redis	Pipeline commands	`redis.pipeline().get('a').set('b', 1).exec()`
Redis	Appropriate TTL	`SET key value EX 3600`
Elasticsearch	Index templates	`PUT _index_template/courses`
Elasticsearch	Query caching	`"cache": true` in aggregations

🎯 Quick Reference: Cross-Database Commands

sql
1-- PostgreSQL: Get course catalog
2SELECT course_id, title, slug, price, rating_avg 
3FROM courses 
4WHERE status = 'published' 
5ORDER BY enrolled_count DESC 
6LIMIT 20;

javascript
1// MongoDB: Get student activity
2db.user_activity_logs.find(
3  { user_id: "user_123" },
4  { events: { $slice: -20 } }
5).sort({ "events.timestamp": -1 });

bash
1# Redis: Session management
2SET session:user_123 '{"role":"student"}' EX 86400
3GET session:user_123
4DEL session:user_123  # Logout

json
1// Elasticsearch: Course search
2GET /courses/_search
3{
4  "query": {
5    "multi_match": {
6      "query": "javascript beginner",
7      "fields": ["title^3", "description", "tags"]
8    }
9  }
10}

This hybrid architecture gives you the best of every database: PostgreSQL for reliability, MongoDB for flexibility, Redis for speed, and Elasticsearch for discovery. Start with PostgreSQL, add MongoDB when content gets complex, then layer in Redis and Elasticsearch as you scale.