# TiFlash Columnar Storage Setup

**Date:** 2026-04-06  
**Status:** Configured and active example setup

## Overview

TiFlash is TiDB's columnar storage engine, optimized for analytical workloads (aggregations, JOINs, window functions). Row-based TiKV is faster for point lookups; columnar TiFlash is faster for scans and aggregations.

## Configured Tables

| Table | Model | REPLICA_COUNT | AVAILABLE | Purpose |
|-------|-------|--------------|-----------|---------|
| PostAnalytics | PostAnalytics | 2 | ✅ | Analytics metrics (heaviest) |
| Post | Post | 2 | ✅ | Posts, JOIN anchor |
| Campaign | Campaign | 2 | ✅ | Campaign analytics |
| User | User | 2 | ✅ | User lookups in analytics |
| SocialAccount | SocialAccount | 2 | ✅ | Social account data |

> REPLICA_COUNT=2 means one replica per TiFlash node (2-node cluster).

## Query Optimization Hints

A utility library was created at `lib/tiflash.ts` with two functions:

### With Prisma.sql template literals
```ts
import { tiflash } from "@/lib/tiflash";

const result = await prisma.$queryRaw`
  ${tiflash`PostAnalytics, Post, Campaign`}
  SELECT SUM(pa.impressions) AS total
  FROM PostAnalytics pa
  INNER JOIN Post p ON pa.postId = p.id
  INNER JOIN Campaign c ON p.campaignId = c.id
  GROUP BY c.name
`;
```

### With $queryRawUnsafe
```ts
import { tiflashUnsafe } from "@/lib/tiflash";

const result = await prisma.$queryRawUnsafe(
  tiflashUnsafe("PostAnalytics, Post") +
  "SELECT SUM(impressions) FROM PostAnalytics pa JOIN Post p ON ..."
);
```

### Endpoints Updated

| Endpoint | File | Tables Hinted |
|----------|------|---------------|
| Dashboard Overview | `app/api/v1/analytics/overview/route.ts` | PostAnalytics, Post |
| Post Comparison | `app/api/v1/analytics/compare/route.ts` | Post, PostAnalytics |
| Campaign Analytics | `app/api/v1/campaigns/[id]/analytics/route.ts` | PostAnalytics, Post |
| Best Times | `app/api/v1/analytics/best-times/route.ts` | **No TiFlash** — lightweight COUNT aggregation only |

> **Note:** The best-times endpoint does NOT use TiFlash hints. It performs a simple `COUNT(*)` group-by on the Post table, which is handled efficiently by TiKV. TiFlash is unnecessary for this query pattern. See `docs/BEST_TIMES_IMPLEMENTATION.md` for details.

## Checking Replica Status

```sql
-- Check all TiFlash replicas
SELECT 
  TABLE_NAME,
  REPLICA_COUNT,
  AVAILABLE,
  CONCAT(ROUND(PROGRESS * 100, 1), '%') AS PROGRESS
FROM information_schema.tiflash_replica 
WHERE TABLE_SCHEMA = 'pingcap_social'
ORDER BY TABLE_NAME;
```

Expected output:
```
TABLE_NAME       REPLICA_COUNT  AVAILABLE  PROGRESS
Campaign         2              1          100%
Post             2              1          100%
PostAnalytics    2              1          100%
SocialAccount    2              1          100%
User             2              1          100%
```

## Verifying Query Routing

```sql
-- Without hint (optimizer chooses based on cost)
EXPLAIN SELECT c.name, COUNT(p.id), SUM(pa.impressions)
FROM Campaign c
JOIN Post p ON c.id = p.campaignId
JOIN PostAnalytics pa ON p.id = pa.postId
GROUP BY c.name;

-- With hint (forces TiFlash)
EXPLAIN SELECT /*+ READ_FROM_STORAGE(TIFLASH[PostAnalytics, Post, Campaign]) */
  c.name, COUNT(p.id), SUM(pa.impressions)
FROM Campaign c
JOIN Post p ON c.id = p.campaignId
JOIN PostAnalytics pa ON p.id = pa.postId
GROUP BY c.name;
```

Look for `cop[tiflash]` in the `task` column of the EXPLAIN output instead of `cop[tikv]`.

## TiDB Cloud Notes

- Our cluster is on **TiDB Cloud Serverless (Starter tier)** which **does include TiFlash**. The `information_schema.tiflash_replica` table confirms 5 tables with replicas.
- **Critical:** The cluster default has `tidb_allow_tiflash_cop = OFF`. This means TiFlash hints are ignored unless overridden. We set `sessionVariables=tidb_allow_tiflash_cop=ON` in the DATABASE_URL connection string to enable it per-connection.
- System tables like `cluster_info` are restricted on TiDB Cloud managed clusters. Use `information_schema.tiflash_replica` instead.
- The optimizer may still prefer TiKV for small datasets (<1000 rows) — this is correct behavior. TiFlash shines on larger analytical scans.

## Monitoring

### Replica Sync Lag
```sql
SELECT TABLE_NAME, AVAILABLE, PROGRESS 
FROM information_schema.tiflash_replica
WHERE TABLE_SCHEMA = 'pingcap_social';
```
If PROGRESS drops below 100% after data changes, TiFlash is syncing. It should recover quickly.

### Query Performance
Monitor query latency on analytics endpoints:
- `/api/v1/analytics/overview?days=30`
- `/api/v1/analytics/compare`
- `/api/v1/campaigns/[id]/analytics`

Expected improvement with larger datasets:
- Aggregation queries: **3-10x faster** on columnar storage
- Multi-table JOINs: **2-5x faster** with columnar pushdown
- Date/time bucketing: **5-15x faster** (column scan vs row scan)

## Maintenance

### Adding TiFlash to New Tables
```sql
ALTER TABLE new_table SET TIFLASH REPLICA 1;
```

### Removing TiFlash from a Table
```sql
ALTER TABLE old_table SET TIFLASH REPLICA 0;
```

### Updating Hints
When adding new analytics queries, import the hint utility:
```ts
import { tiflash } from "@/lib/tiflash";
```
Add `${tiflash`Table1, Table2`}` right after the opening backtick of the query.