Skip to content

REDIS_CACHE_WORKING.md

Latest commit

 

History

History
87 lines (62 loc) · 2.2 KB

REDIS_CACHE_WORKING.md

File metadata and controls

87 lines (62 loc) · 2.2 KB

Redis Cache Implementation - SUCCESS! 🎉

What We Built

A transparent Redis cache for Web CTEs that dramatically speeds up repeated queries.

Performance Results

Test: 5000-row trades CSV (25 columns, 1MB)

Execution Time Speed Source
First (cold) 640ms 1x HTTP Server + Cache write
Second (cached) 101ms 6.3x faster Redis Cache
Third (cached) 50ms 12.8x faster Redis Cache

How It Works

  1. Cache Key Generation: SHA256 hash of (URL + Method + Headers + Body)

    • Same query = same cache key
    • Different parameters = different cache

  2. Automatic TTL:

    • Explicit: CACHE 300 = 5 minutes
    • Smart defaults: prod URLs = 1hr, staging = 5min

  3. Graceful Degradation:

    • Redis down? sql-cli still works (just no cache)
    • Cache failure? Query still executes

Your Production Workflow

-- Morning: First query fetches 20k Barclays trades (30 seconds)
WITH WEB trades AS (
    URL 'https://prod.api/trades'
    METHOD POST
    BODY '{"source": "Barclays", "date": "2025-09-01"}'
    CACHE 86400  -- 24 hours for yesterday's data
)
SELECT * FROM trades WHERE amount > 1000000;

-- Rest of day: Same query returns instantly from cache (50ms)

Redis Commands

# Start Redis (WSL)
sudo service redis-server start

# Check cache status
redis-cli KEYS "sql-cli:*"

# Check TTL
redis-cli TTL "sql-cli:web:..."

# Clear cache
redis-cli DEL "sql-cli:web:..."

# Monitor cache activity
redis-cli MONITOR | grep sql-cli

Configuration

# Disable cache
SQL_CLI_NO_CACHE=1 sql-cli query.sql

# Custom Redis URL
SQL_CLI_REDIS_URL=redis://other-host:6379 sql-cli query.sql

Next Steps

  1. At Work: Install Redis, pull latest changes, enjoy instant queries
  2. Future: Upgrade from MessagePack to Parquet for better compression
  3. Smart Caching: Add date detection for intelligent TTLs

Key Benefits

  • No code changes: Existing queries just get faster
  • Shared cache: All sql-cli instances share same Redis
  • Persistent: Cache survives sql-cli restarts
  • Observable: See cache hits/misses in stderr

This will save you HOURS when working with production trade data!