TiDB - Learning Module | OneNoughtOne

Loading content...

0/273

Hybrid OLTP/OLAP: Real-Time Analytics on Transactional Data

The Analytics Gap

In traditional architectures, there's always a gap between operational data and analytical insights.

Your transactional database captures every customer action, every order, every payment—but querying it for analytics would crush your production system. So you build a data warehouse. You create ETL pipelines to copy data nightly. You hire data engineers to maintain the pipeline. You train analysts to understand that yesterday's data is the best they'll get.

This gap has consequences:

Stale insights: Decisions are made on data that's 12-24 hours old
Infrastructure complexity: Separate systems for OLTP and OLAP
Data inconsistency: ETL failures cause divergence between operational and analytical data
Increased latency to insight: Ad-hoc questions require waiting for the next ETL run

TiDB's HTAP (Hybrid Transactional/Analytical Processing) architecture eliminates this gap. Through TiFlash—a columnar storage engine that replicates data in real-time from TiKV—you can run complex analytical queries directly on live transactional data without impacting OLTP performance.

This isn't a compromise. It's a fundamental rethinking of how databases can serve both workloads simultaneously.

What You Will Learn

By the end of this page, you will understand the architectural differences between OLTP and OLAP workloads, how TiFlash provides columnar storage alongside TiKV's row storage, how real-time replication keeps analytical data current, and how TiDB's optimizer chooses between row and columnar storage for each query.

OLTP vs OLAP: Different Workloads, Different Needs

Before diving into TiDB's HTAP architecture, we must understand why traditional systems separate OLTP and OLAP workloads—and why unifying them is technically challenging.

OLTP (Online Transaction Processing):

OLTP workloads are characterized by:

High volume of small operations: Thousands of simple queries per second
Row-oriented access: Fetch/update complete rows (SELECT * WHERE id = X)
Point lookups and narrow ranges: Access a few rows at a time
Low latency requirements: Sub-10ms response time expected
High concurrency: Many users simultaneously
Write-heavy or balanced: Frequent INSERTs, UPDATEs, DELETEs

OLAP (Online Analytical Processing):

OLAP workloads are characterized by:

Complex aggregations: SUM, AVG, COUNT across millions of rows
Column-oriented access: Often needs only a few columns (SELECT SUM(amount) ...)
Full table or large range scans: Analyze entire datasets
Higher latency tolerance: Seconds to minutes acceptable
Lower concurrency: Fewer simultaneous queries
Read-heavy: Rarely modifies data during analysis

OLTP vs OLAP Workload Characteristics
Characteristic	OLTP	OLAP
Query Pattern	Point lookups, narrow ranges	Full scans, complex joins
Rows Accessed	1-100 per query	Millions per query
Columns Accessed	All columns (SELECT *)	Few columns (aggregations)
Latency Requirement	<10ms	Seconds to minutes
Concurrency	Thousands of users	Tens of analysts
Write Pattern	Frequent, small writes	Rare bulk loads
Data Freshness	Must be real-time	Can tolerate delay
Optimal Storage	Row-oriented	Column-oriented

Why Storage Layout Matters:

The fundamental tension is storage layout. Consider a table with 1 million rows and 20 columns:

Row-Oriented Storage (TiKV):

Row 1: [col1, col2, col3, ..., col20]
Row 2: [col1, col2, col3, ..., col20]
...

Great for OLTP: Fetching row 1000 reads one contiguous block
Terrible for OLAP: SUM(col3) must read all 20 columns × 1M rows

Column-Oriented Storage (TiFlash):

Column 1: [row1_val, row2_val, ..., row1M_val]
Column 2: [row1_val, row2_val, ..., row1M_val]
...

Great for OLAP: SUM(col3) reads only col3 data (1/20th the I/O)
Terrible for OLTP: Fetching row 1000 requires reading from 20 separate locations

The Traditional Solution:

Because of this tension, organizations traditionally maintained separate systems:

Production OLTP database (MySQL, PostgreSQL) for transactions
Data warehouse (Snowflake, Redshift, BigQuery) for analytics
ETL pipeline (Airflow, dbt) to move data from OLTP to OLAP

TiDB's HTAP architecture challenges this by maintaining both storage layouts simultaneously, with real-time replication keeping them in sync.

Why Not Just Index Everything?

You might wonder: can't we use indexes for analytics? Indexes help with point lookups, but aggregate queries (SUM, COUNT) must still scan all matching rows. A well-designed columnar store with compression can be 10-100x more efficient for analytical queries than row stores with indexes.

TiFlash: Columnar Storage for Analytics

TiFlash is TiDB's columnar storage engine, designed specifically for analytical workloads. It runs alongside TiKV, maintaining a columnar replica of data that's optimized for aggregations, scans, and complex queries.

Key Design Principles:

Columnar Organization: Data is stored by column, not by row, enabling efficient compression and vectorized processing.
Learner Replicas: TiFlash nodes join Raft groups as learner replicas—they receive data updates but don't participate in leader election or write consensus.
Asynchronous Replication: TiFlash replicas lag slightly behind TiKV (typically milliseconds to seconds), but TiDB ensures read consistency when needed.
Delta-Main Architecture: Recent changes are stored in a row-oriented delta layer, periodically merged into the columnar main layer.

Storage Architecture:

tiflash-architecture.txt
TIFLASH STORAGE ARCHITECTURE
════════════════════════════════════════════════════════════════════
 
TIDB CLUSTER WITH TIFLASH
─────────────────────────
 
┌─────────────────────────────────────────────────────────────────┐
│                         TiDB Servers                             │
│     (SQL Layer - decides whether to use TiKV or TiFlash)        │
└─────────────────────────────────────────────────────────────────┘
                    │                           │
                    ▼                           ▼
    ┌───────────────────────────┐   ┌───────────────────────────┐
    │      TiKV CLUSTER         │   │     TiFlash CLUSTER       │
    │    (Row Storage)          │   │     (Columnar Storage)    │
    ├───────────────────────────┤   ├───────────────────────────┤
    │                           │   │                           │
    │  Region 1 [L]  Region 2[F]│   │  Region 1 [Learner]      │
    │  Region 3 [F]  Region 4[L]│   │  Region 2 [Learner]      │
    │  ...                      │   │  Region 3 [Learner]      │
    │                           │   │  ...                      │
    │  • Point lookups          │──►│  • Analytical queries     │
    │  • Small transactions     │   │  • Full scans             │
    │  • Raft leaders/followers │   │  • Aggregations           │
    │                           │   │  • Raft learner only      │
    └───────────────────────────┘   └───────────────────────────┘
                                            │
                                    Raft Replication
                                    (async, learner)
                                            │
                                    ┌───────┴───────┐
                                    │               │
                                    ▼               ▼
 
TIFLASH NODE INTERNAL STRUCTURE
───────────────────────────────
 
┌─────────────────────────────────────────────────────────────────┐
│                      TiFlash Node                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │                    Delta Layer (Row Format)                 │ │
│  │  Recent writes, stored in row format for fast ingestion    │ │
│  │  ┌─────────────────────────────────────────────────────────┐│ │
│  │  │ INSERT row1: (id=1001, name="Alice", amount=150.00)    ││ │
│  │  │ INSERT row2: (id=1002, name="Bob", amount=200.00)      ││ │
│  │  │ UPDATE row 1001: amount = 175.00                       ││ │
│  │  └─────────────────────────────────────────────────────────┘│ │
│  └────────────────────────────────────────────────────────────┘ │
│                              │ Periodic Merge                    │
│                              ▼                                   │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │                 Main Layer (Columnar Format)                │ │
│  │  Historical data, stored in columnar format with encoding  │ │
│  │                                                             │ │
│  │  Column: id        [1, 2, 3, 4, ..., 1000000]  (compressed) │ │
│  │  Column: name      ["Alice", "Bob", "Carol", ...] (dict)   │ │
│  │  Column: amount    [95.0, 150.0, 200.0, ...] (run-length)  │ │
│  │  Column: timestamp [2024-01-01, 2024-01-01, ...] (delta)   │ │
│  │                                                             │ │
│  │  Compression: LZ4, ZSTD, Dictionary, Run-Length, Delta      │ │
│  │  Typical compression ratio: 5-20x                           │ │
│  └────────────────────────────────────────────────────────────┘ │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

Columnar Advantages for Analytics:

I/O Efficiency: Only read columns needed for query (often 10-20% of data)
Compression: Same-type values compress much better (10-20x is common)
- Dictionary encoding: "United States" stored once, referenced by integer
- Run-length encoding: [100, 100, 100, 100] → [(100, 4)]
- Delta encoding: Timestamps stored as differences from base
Vectorized Execution: Process batches of column values using SIMD instructions
- CPU processes 8-16 values per instruction instead of 1
- 5-10x faster aggregation on modern CPUs

Enabling TiFlash Replicas:

tiflash-enable.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Enable TiFlash replica for a table
ALTER TABLE orders SET TIFLASH REPLICA 1;
 
-- Enable TiFlash replica for partition tables
ALTER TABLE events SET TIFLASH REPLICA 2;  -- 2 replicas for HA
 
-- Check TiFlash replica status
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    REPLICA_COUNT,
    LOCATION_LABELS,
    AVAILABLE,
    PROGRESS
FROM information_schema.tiflash_replica;
 
/*
+--------------+------------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+---------------+-----------------+-----------+----------+
| mydb         | orders     | 1             | NULL            | 1         | 1.0      |
| mydb         | events     | 2             | NULL            | 1         | 1.0      |
+--------------+------------+---------------+-----------------+-----------+----------+
*/
 
-- PROGRESS = 1.0 means replication is complete
-- AVAILABLE = 1 means TiFlash can serve queries
 
-- Remove TiFlash replica (rare, usually for cost reduction)
ALTER TABLE logs SET TIFLASH REPLICA 0;

TiFlash Doesn't Slow Writes

TiFlash uses Raft learner replicas, which receive data but don't participate in write consensus. This means adding TiFlash doesn't increase write latency—TiKV writes complete without waiting for TiFlash acknowledgment. TiFlash catches up asynchronously.

Real-Time Replication: Raft Learner Protocol

The magic of TiDB's HTAP lies in how TiFlash maintains a consistent view of data without impacting OLTP performance. This is achieved through Raft Learner replicas.

What is a Raft Learner?

In the Raft consensus protocol, learners are special replicas:

Receive all log entries: Just like followers, learners receive every write
Don't vote: Learners don't participate in leader election
Don't block writes: Leaders don't wait for learner acknowledgment to commit
Can be inconsistent temporarily: Learners may lag behind, but will eventually catch up

This makes learners perfect for TiFlash:

Data is replicated in real-time (typically <1 second lag)
OLTP performance is unaffected (no additional consensus overhead)
TiFlash can be unavailable without affecting transactions

raft-learner-flow.txt
RAFT LEARNER REPLICATION FLOW
════════════════════════════════════════════════════════════════════
 
WRITE: INSERT INTO orders VALUES (1001, 'Alice', 150.00, NOW());
 
STEP 1: Normal Raft Commit (TiKV only)
───────────────────────────────────────
 
Client
   │
   └──► TiKV Leader (Region 42)
           │
           ├──► TiKV Follower 1:  AppendLog ─► ACK
           ├──► TiKV Follower 2:  AppendLog ─► ACK
           │         └── Majority achieved (2 of 2 followers)
           │
           └──► Commit & Apply ─► Response to Client (SUCCESS)
                     │
                     │ (Client returns here, write complete)
 
 
STEP 2: Async Learner Replication (TiFlash)
───────────────────────────────────────────
 
           ... milliseconds later ...
 
TiKV Leader (Region 42)
   │
   └──► TiFlash Learner:  AppendLog (async)
                              │
                              ▼
                       ┌──────────────┐
                       │ Raft Log     │
                       │ Received     │
                       └──────┬───────┘
                              │
                              ▼
                       ┌──────────────┐
                       │ Apply to     │
                       │ Delta Layer  │ (row format for fast apply)
                       └──────┬───────┘
                              │
                              ▼ (background, periodic)
                       ┌──────────────┐
                       │ Merge to     │
                       │ Main Layer   │ (columnar format)
                       └──────────────┘
 
 
REPLICATION LAG CHARACTERISTICS
───────────────────────────────
 
Typical Lag:
  - Normal operations: 10-100ms
  - Under heavy write load: 100ms - 1s
  - Catching up after TiFlash restart: seconds to minutes
 
Query Implications:
  - Strong reads (read at specific timestamp): May wait for TiFlash to catch up
  - Weak reads (read latest in TiFlash): Slightly stale, no waiting
  - Default: TiDB uses strong reads for consistency

Consistency Guarantees:

TiDB provides different consistency options for TiFlash reads:

1. Strong Consistency (Default):

Reads see all committed data up to the read timestamp
TiDB waits for TiFlash to catch up if necessary
Safe for transactional analytics

2. Weak Consistency:

Reads return data available in TiFlash (may lag)
Lower latency for non-critical analytics
Useful for dashboards where slight staleness is acceptable

Read Consistency in Practice:

consistency-examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Check TiFlash replication lag
SELECT 
    ADDRESS,
    STORE_ID,
    REGION_COUNT,
    --- Learner safe TS indicates how far TiFlash has caught up
    TIFLASH_VERSION
FROM information_schema.tiflash_cluster_info;
 
-- Default: Strong consistency (waits for TiFlash to catch up)
SELECT COUNT(*), SUM(amount) 
FROM orders 
WHERE order_date = CURDATE();
-- This will wait if TiFlash hasn't received today's latest writes
 
-- Force TiFlash execution with hint
SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */ 
    customer_id, 
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id
HAVING total_spent > 1000;
 
-- For dashboards where slight staleness is acceptable
-- Set tiflash_allow_read_stale_data for the session
SET SESSION tiflash_allow_stale_read = ON;
 
-- Or use AS OF TIMESTAMP for point-in-time reads
SELECT COUNT(*) FROM orders 
AS OF TIMESTAMP TIMESTAMPADD(SECOND, -60, NOW());
-- Reads data as of 60 seconds ago (definitely available in TiFlash)

Understand Your Freshness Requirements

For financial reporting or compliance, use strong consistency to ensure accuracy. For real-time dashboards, stale reads (10-30 seconds) are often acceptable and reduce query latency. Match consistency mode to business requirements.

Intelligent Query Routing

One of TiDB's most powerful features is its ability to automatically choose between TiKV (row storage) and TiFlash (columnar storage) based on query characteristics. The optimizer analyzes each query and selects the optimal engine—or even uses both engines in a single query.

How the Optimizer Decides:

TiDB's cost-based optimizer estimates the cost of executing each query plan on TiKV vs TiFlash:

Factors Favoring TiKV:

Point lookups (WHERE id = X)
Small result sets
Index-covered queries
High selectivity filters

Factors Favoring TiFlash:

Full table scans
Aggregations (SUM, COUNT, AVG, GROUP BY)
Queries touching many columns
Low selectivity filters (WHERE status = 'active' on 80% of rows)

The optimizer considers:

Estimated row count
Index availability and selectivity
Required columns
Aggregation operations
Network cost of transferring data

routing-examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- EXPLAIN shows which engine TiDB will use
 
-- Example 1: Point lookup → TiKV
EXPLAIN SELECT * FROM orders WHERE id = 12345;
/*
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| Point_Get               | 1.00     | root      | table:orders  | handle:12345                   |
+-------------------------+----------+-----------+---------------+--------------------------------+
   ↑ task=root means TiKV (point get is always TiKV)
*/
 
-- Example 2: Aggregation → TiFlash (automatic)
EXPLAIN SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;
/*
+----------------------------------+------------+-------------------+---------------+--------------------------------+
| id                               | estRows    | task              | access object | operator info                  |
+----------------------------------+------------+-------------------+---------------+--------------------------------+
| HashAgg                          | 50000.00   | root              |               | group by:orders.customer_id    |
| └─TableReader                    | 1000000.00 | root              |               | data:ExchangeSender            |
|   └─ExchangeSender               | 1000000.00 | mpp[tiflash]      |               | ExchangeType: PassThrough      |
|     └─HashAgg                    | 50000.00   | mpp[tiflash]      |               | group by:orders.customer_id    |
|       └─TableFullScan            | 1000000.00 | mpp[tiflash]      | table:orders  | keep order:false               |
+----------------------------------+------------+-------------------+---------------+--------------------------------+
   ↑ task=mpp[tiflash] indicates TiFlash execution with MPP (Massively Parallel Processing)
*/
 
-- Example 3: Hybrid - Join TiFlash aggregation with TiKV lookup
EXPLAIN SELECT 
    c.name,
    c.email,
    stats.order_count,
    stats.total_spent
FROM customers c
INNER JOIN (
    SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_spent
    FROM orders
    WHERE created_at >= '2024-01-01'
    GROUP BY customer_id
    HAVING COUNT(*) > 10
) stats ON c.id = stats.customer_id;
/*
+------------------------------------------+----------+-------------------+---------------+---------------------------+
| id                                       | estRows  | task              | access object | operator info             |
+------------------------------------------+----------+-------------------+---------------+---------------------------+
| Projection                               | 1000.00  | root              |               | ...                       |
| └─HashJoin                               | 1000.00  | root              |               | inner join                |
|   ├─TableReader(Build)                   | 1000.00  | root              |               | table:customers           |
|   │ └─TableFullScan                      | 1000.00  | cop[tikv]         | table:c       |                           |
|   └─TableReader(Probe)                   | 1000.00  | root              |               | data:ExchangeSender       |
|     └─...                                |          | mpp[tiflash]      |               | aggregation on TiFlash    |
+------------------------------------------+----------+-------------------+---------------+---------------------------+
   ↑ cop[tikv] for customer lookup, mpp[tiflash] for order aggregation
*/
 
-- Force specific engine with hints
-- Force TiFlash
SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */ 
    SUM(amount) FROM orders;
 
-- Force TiKV  
SELECT /*+ READ_FROM_STORAGE(TIKV[orders]) */
    SUM(amount) FROM orders;
 
-- For most queries, let the optimizer decide (it's usually right)

MPP (Massively Parallel Processing):

For complex analytical queries, TiFlash doesn't just store data in columnar format—it also processes queries using MPP (Massively Parallel Processing).

MPP distributes query execution across all TiFlash nodes:

Data Shuffle: Data is redistributed based on GROUP BY or JOIN keys
Parallel Aggregation: Each node computes partial aggregates
Result Merge: Partial results are combined

This enables complex queries to scale horizontally—adding TiFlash nodes increases analytical query capacity.

MPP Execution Benefits

•Parallel Scans: Each TiFlash node scans its local data simultaneously
•Distributed Aggregations: GROUP BY and SUM computed locally, then merged
•Shuffle Joins: Large joins partitioned across nodes by join key
•Vectorized Execution: SIMD operations on columnar data for 5-10x speedup
•Compression Benefits: Compressed data decompressed during processing (less I/O)

Real-World Performance

Analytical queries that take minutes on TiKV often complete in seconds on TiFlash/MPP. Organizations report 10-100x speedups for dashboard queries after enabling TiFlash, with no changes to their SQL.

HTAP Use Cases

HTAP architecture enables use cases that were previously impractical or required complex infrastructure. Let's explore scenarios where TiDB's unified OLTP/OLAP capability provides significant value.

1. Real-Time Dashboards on Transactional Data:

Traditional approach:

ETL runs at midnight → Data warehouse → Dashboard queries
Users see yesterday's data at best

With TiDB HTAP:

Dashboard queries hit TiFlash directly
Data is seconds old, not hours
No separate data warehouse to maintain

htap-dashboard.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Real-time revenue dashboard (runs every minute)
SELECT 
    DATE_FORMAT(ordered_at, '%Y-%m-%d %H:00') as hour,
    COUNT(*) as orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY hour
ORDER BY hour DESC;
 
-- Query hits TiFlash (automatic), returns in ~100ms
-- Same query on TiKV would take 5-10 seconds (full scan)
 
-- Customer segmentation (would traditionally require data warehouse)
WITH customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as lifetime_value,
        DATEDIFF(NOW(), MAX(ordered_at)) as days_since_last_order,
        DATEDIFF(NOW(), MIN(ordered_at)) as customer_age_days
    FROM orders
    GROUP BY customer_id
)
SELECT
    CASE 
        WHEN lifetime_value > 10000 AND days_since_last_order < 30 THEN 'VIP Active'
        WHEN lifetime_value > 10000 THEN 'VIP At-Risk'
        WHEN lifetime_value > 1000 AND days_since_last_order < 60 THEN 'Regular Active'
        WHEN lifetime_value > 1000 THEN 'Regular At-Risk'
        ELSE 'Occasional'
    END as segment,
    COUNT(*) as customer_count,
    SUM(lifetime_value) as segment_value,
    AVG(order_count) as avg_orders
FROM customer_metrics
GROUP BY segment;

2. Anti-Fraud and Anomaly Detection:

Fraud detection requires analyzing patterns across large datasets in real-time. With HTAP:

Detection: TiFlash aggregates transaction patterns across millions of records
Action: TiKV serves the transactional system that blocks suspicious transactions
No delay: Detection happens on live data, not stale warehouse data

htap-fraud.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- Fraud detection: Find accounts with unusual activity in last hour
-- This needs OLAP (aggregate analysis) + OLTP (immediate action)
 
WITH hourly_patterns AS (
    -- TiFlash: Analyze all transactions in last hour
    SELECT 
        account_id,
        COUNT(*) as txn_count,
        SUM(amount) as total_amount,
        COUNT(DISTINCT merchant_id) as unique_merchants,
        COUNT(DISTINCT country_code) as countries_used
    FROM transactions
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY account_id
),
historical_patterns AS (
    -- TiFlash: Get 30-day averages for comparison
    SELECT 
        account_id,
        AVG(daily_txns) as avg_daily_txns,
        STDDEV(daily_txns) as stddev_txns,
        AVG(daily_amount) as avg_daily_amount
    FROM (
        SELECT 
            account_id,
            DATE(created_at) as day,
            COUNT(*) as daily_txns,
            SUM(amount) as daily_amount
        FROM transactions
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY account_id, day
    ) daily
    GROUP BY account_id
)
SELECT 
    h.account_id,
    h.txn_count as hourly_txns,
    h.total_amount as hourly_amount,
    h.countries_used,
    p.avg_daily_txns,
    -- Flag if hourly activity exceeds 50% of average daily
    CASE WHEN h.txn_count > p.avg_daily_txns * 0.5 THEN 'HIGH_FREQUENCY' ELSE 'NORMAL' END as frequency_flag,
    -- Flag if multiple countries in one hour
    CASE WHEN h.countries_used > 2 THEN 'MULTI_GEO' ELSE 'NORMAL' END as geo_flag
FROM hourly_patterns h
JOIN historical_patterns p ON h.account_id = p.account_id
WHERE h.txn_count > p.avg_daily_txns * 0.5  -- Anomaly threshold
   OR h.countries_used > 2;  -- Geographic anomaly
 
-- Results feed into TiKV-based blocking/alerting system

3. Personalization and Recommendation:

Recommendation engines need to:

Aggregate user behavior (OLAP: "users who bought X also bought Y")
Serve recommendations in real-time (OLTP: product page load)

With separate systems, recommendations are based on stale data. With HTAP, recommendations can consider the user's last-minute activity.

4. Operational Intelligence:

Real-time inventory optimization
Dynamic pricing based on current demand
Supply chain visibility
Customer service insights ("show me this customer's activity while they're on the phone")

The Common Thread:

All these use cases share a pattern: analytics that inform real-time operations. When analytics and transactions are in separate systems with ETL between them, there's always a gap. HTAP closes that gap.

HTAP Use Case Summary
Use Case	OLAP Component	OLTP Component	Traditional Challenge	HTAP Benefit
Real-time Dashboards	Aggregate metrics	Record transactions	Stale data (T+1)	Live data (seconds)
Fraud Detection	Pattern analysis	Transaction blocking	Detection delay	Real-time detection
Personalization	User behavior aggregation	Serve recommendations	Stale recommendations	Current-session aware
Inventory Optimization	Demand forecasting	Stock updates	Delayed reordering	Dynamic optimization
Customer 360	Aggregate history	Serve to agents	Incomplete view	Complete, current view

Start with TiFlash on High-Value Tables

You don't need TiFlash on every table. Start with the 3-5 largest tables that drive analytical queries (orders, transactions, events). The cost-benefit is highest for large, frequently-aggregated tables.

TiFlash Deployment Considerations

Deploying TiFlash effectively requires understanding its resource requirements and architectural implications.

Resource Requirements:

TiFlash nodes have different resource profiles than TiKV nodes:

CPU:

More cores benefit MPP (parallel processing)
16-48 cores recommended per node
Modern CPUs with AVX2/AVX-512 for vectorization

Memory:

Larger for data caching and query execution
64-256 GB recommended
Memory = faster aggregations

Storage:

High-throughput SSDs (NVMe preferred)
Storage ~= TiKV storage (columnar compression often reduces size)
IOPS less critical than sequential throughput

Network:

High bandwidth for MPP shuffles between TiFlash nodes
10 Gbps minimum, 25 Gbps preferred for large clusters

TiFlash Node Sizing Guidelines
Scale	Nodes	CPU	Memory	Storage
Small (< 1TB)	2-3	16 cores	64 GB	500 GB NVMe
Medium (1-10TB)	3-5	32 cores	128 GB	2 TB NVMe
Large (10-100TB)	5-10+	48 cores	256 GB	4 TB NVMe

Deployment Patterns:

1. Dedicated TiFlash Nodes (Recommended):

Separate hardware for TiFlash
No resource contention with TiKV
Clear capacity planning
Higher cost but predictable performance

2. Shared Development/Test:

TiFlash on same nodes as TiKV (for dev/test only)
Reduced hardware cost
Risk of resource contention
NOT recommended for production

TiFlash High Availability:

TiFlash replicas work like TiKV:

Multiple replicas for fault tolerance
If one TiFlash node fails, queries route to another replica
PD handles replica scheduling

Recommendation: 2 TiFlash replicas for HA, 1 for non-critical analytics

tiflash-monitoring.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Monitor TiFlash status and performance
 
-- TiFlash cluster overview
SELECT * FROM information_schema.tiflash_cluster_info;
 
-- Tables with TiFlash replicas
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    REPLICA_COUNT,
    AVAILABLE,
    PROGRESS
FROM information_schema.tiflash_replica
WHERE REPLICA_COUNT > 0;
 
-- Check if TiFlash is being used (query history)
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY customer_id;
 
-- Look for "tiflash" in the task column:
/*
+----------------------------------+----------+---------+-----------+-------------------+
| id                               | estRows  | actRows | task      | access object     |
+----------------------------------+----------+---------+-----------+-------------------+
| HashAgg                          | 50000.00 | 48721   | root      |                   |
| └─TableReader                    | 50000.00 | 48721   | root      |                   |
|   └─ExchangeSender               | 50000.00 | 48721   | mpp[tiflash]                    | ← TiFlash/MPP
+----------------------------------+----------+---------+-----------+-------------------+
*/
 
-- TiFlash-specific metrics (in Prometheus/Grafana)
-- tiflash_proxy_apply_log_duration_seconds: Replication lag
-- tiflash_system_current_metric_Region_Count: Regions in TiFlash
-- tiflash_coprocessor_executor_count: Query processing stats

Initial Sync Time

When you first enable TiFlash replicas on a large table, initial sync can take hours depending on data size and network bandwidth. Plan for this during off-peak hours. Monitor PROGRESS in tiflash_replica until it reaches 1.0.

Summary: HTAP Principles

We've explored TiDB's HTAP capabilities—how TiFlash enables real-time analytics on transactional data. Let's consolidate the key principles:

Key Takeaways

•OLTP and OLAP Have Different Needs: Row storage for transactions, columnar storage for analytics—TiDB provides both.
•TiFlash is Columnar Storage: Optimized for compression, vectorized execution, and aggregations. 10-100x faster for analytical queries.
•Raft Learners Enable Real-Time Sync: TiFlash replicas receive data without blocking OLTP writes. Typical lag is milliseconds to seconds.
•Intelligent Query Routing: TiDB's optimizer automatically chooses between TiKV and TiFlash based on query characteristics.
•MPP Scales Analytics Horizontally: Complex queries are distributed across TiFlash nodes for parallel execution.
•HTAP Eliminates the Analytics Gap: Real-time dashboards, fraud detection, personalization—all on live data without ETL.
•Deploy TiFlash on High-Value Tables: Start with large, frequently-aggregated tables for maximum benefit.

What's Next:

We've covered MySQL compatibility, horizontal scalability, and HTAP capabilities. The final piece is understanding when to choose TiDB—the decision framework for evaluating TiDB against alternatives like MySQL, PostgreSQL, CockroachDB, and other distributed databases. We'll examine the tradeoffs, sweet spots, and anti-patterns to help you make the right choice for your specific requirements.

Page Complete

You now understand TiDB's HTAP architecture—how TiFlash provides columnar storage for analytics, how real-time replication keeps data current, and how the optimizer routes queries to the appropriate engine. Next, we'll explore when TiDB is the right choice for your system.