# TiFlash Integration Guide

## Overview

TiFlash is TiDB's columnar storage engine, optimized for analytical queries (aggregations, JOINs, window functions).

## TiDB Cloud Architecture

This project runs on **TiDB Cloud Serverless** (`gateway01.us-west-2.prod.aws.tidbcloud.com`).

### ⚠️ IMPORTANT: TiFlash is NOT available on TiDB Cloud Serverless

- TiDB Cloud Serverless does NOT include TiFlash nodes
- TiFlash hints (`READ_FROM_STORAGE(TIFLASH[...])`) are no-ops on Serverless
- All analytics queries run on TiKV (row-based storage) — still fast for current dataset sizes
- TiDB's optimizer will auto-route to TiFlash if the cluster is ever upgraded to Dedicated

### Enabling TiFlash on Dedicated clusters:
- Set `TIFLASH_AVAILABLE=true` in environment variables
- Provision TiFlash nodes on your Dedicated cluster
- Set TiFlash replicas on tables: `ALTER TABLE PostAnalytics SET TIFLASH REPLICA 1;`

### TiFlash vs TiKV
- **TiKV**: Row-based storage — faster for point lookups, OLTP
- **TiFlash**: Columnar storage — faster for aggregations, OLAP
- TiDB automatically chooses between them; hints force TiFlash usage

## Implementation

### Core Utility: `lib/tiflash.ts`

```typescript
import { getTiFlashHint, tiflash, tiflashUnsafe, isTiFlashAvailable } from "@/lib/tiflash";

// Pattern 1: Template literal with Prisma.$queryRaw (preferred)
import { tiflash } from "@/lib/tiflash";
const result = await prisma.$queryRaw`
  ${tiflash`PostAnalytics, Post`}
  SELECT SUM(pa.impressions) FROM PostAnalytics pa
  INNER JOIN Post p ON pa.postId = p.id
`;

// Pattern 2: Dynamic hint with availability check (safest)
const hint = await getTiFlashHint(prisma, "PostAnalytics", "Post");
const result = await prisma.$queryRawUnsafe(
  `${hint.raw}
    SELECT SUM(pa.impressions) FROM PostAnalytics pa
    INNER JOIN Post p ON pa.postId = p.id`
);
```

### Availability Detection

The `isTiFlashAvailable()` function:
1. First tries `information_schema.tiflash_tables` (works on Dedicated, denied on Serverless)
2. Falls back to a probe query with `READ_FROM_STORAGE(TIFLASH[\`Post\`])`
3. Caches the result per process lifetime

### Fallback Behavior

If TiFlash is unavailable:
- `getTiFlashHint()` returns an empty hint string
- Queries execute without hints — TiDB optimizer handles routing
- No errors, graceful degradation

## SQL Hint Syntax

```sql
/*+ READ_FROM_STORAGE(TIFLASH[Table1, Table2, Table3]) */
```

**Important:**
- Table names are case-sensitive (match the actual table name)
- Only list tables that have TiFlash replicas configured
- The hint applies to the entire query, not individual tables

## Best Practices

### When to use TiFlash hints
- Heavy aggregations: `SUM()`, `COUNT()`, `AVG()` with `GROUP BY`
- Multi-table JOINs on analytics tables
- Window functions: `ROW_NUMBER()`, `RANK()`
- Date/time bucketing queries

### When NOT to use TiFlash hints
- Point lookups by primary key (TiKV is faster)
- Small result sets (<100 rows)
- Write-heavy operations (TiFlash is read-only)

## Troubleshooting

### "Unknown column in where clause" on tiflash_tables
Expected on TiDB Cloud Serverless. System tables are restricted. Use `isTiFlashAvailable()` for detection.

### "Do not know how to serialize a BigInt"
This is a JSON serialization issue, not TiFlash-specific. Use `Number(bigintValue)` to convert before serializing.

### TiFlash hints not improving performance
- Verify TiFlash replicas exist for the tables in question
- Check that queries are truly analytical (not point lookups)
- Use `EXPLAIN` to verify query execution plan

## Testing

```bash
cd /path/to/pingcap-social-hub
npx tsx scripts/test-tiflash.ts
```

## References
- [TiFlash Overview](https://docs.pingcap.com/tidb/stable/tiflash-overview)
- [Use TiFlash](https://docs.pingcap.com/tidb/stable/use-tiflash)
- [TiDB Cloud Limited SQL Features](https://docs.pingcap.com/tidbcloud/limited-sql-features#system-tables)