DSQL + ElastiCache Performance Demo

This demo showcases the performance benefits of using Amazon ElastiCache (Valkey) as a caching layer in front of Amazon DSQL (PostgreSQL-compatible serverless database).

Architecture

• DSQL: PostgreSQL-compatible serverless database
• ElastiCache Valkey: Valkey-compatible in-memory cache with TLS
• Environment: AWS CloudShell within VPC for secure connectivity

Architecture Diagram

Prerequisites

Before running this demo, you'll need:

• AWS Region: Any region that supports DSQL and ElastiCache (e.g., us-east-1, us-west-2)
• VPC Setup: A VPC with appropriate security groups for DSQL and ElastiCache connectivity
• DSQL Cluster: A running DSQL cluster endpoint (format: cluster-id.dsql.region.on.aws)
• ElastiCache Valkey: A Valkey serverless cache (format: cache-name.serverless.region.cache.amazonaws.com)
• AWS CloudShell: Running within the VPC for secure connectivity

The best option is to use the provided CloudFormation template to deploy a Multi-region architecture for DSQL and Valkey.

Refer to CloudFormation/README for instructions on how to deploy the solution.

Quick Start

Option 1: Automated Demo (Recommended)

./quick_start.sh

What happens:

1. Interactive Setup: You'll be prompted once for your AWS configuration:
   • AWS Region (e.g., us-east-1)
   • DSQL cluster endpoint
   • ElastiCache Valkey endpoint
2. Automated Demo: Complete performance demonstration runs automatically
3. Persistent Config: Settings are saved as environment variables for subsequent runs

Option 2: Manual Steps

# 1. Set environment variables (one-time setup)
export AWS_REGION="your-region"
export DSQL_ENDPOINT="your-dsql-endpoint"
export VALKEY_ENDPOINT="your-valkey-endpoint"

# 2. Setup database
python3 setup_database.py

# 3. Run performance test (cache miss)
python3 cloudshell_dsql_elasticache.py

# 4. Run again immediately (cache hit)
python3 cloudshell_dsql_elasticache.py

Option 3: Command Line Arguments

# Direct execution with arguments (legacy support)
python3 cloudshell_dsql_elasticache.py your-dsql-endpoint your-valkey-endpoint
python3 cloudshell_dsql_elasticache.py your-region your-dsql-endpoint your-valkey-endpoint

Expected Results

First Run (Cache Miss)

• DSQL query execution: ~70ms
• Data stored in ElastiCache with 20-second TTL

Second Run (Cache Hit)

• ElastiCache retrieval: ~2ms
• Performance improvement: ~35-40x faster (97%+ improvement)

Files

• cloudshell_dsql_elasticache.py - Main performance test script
• setup_database.py - Database schema creation and test data
• setup_cloudshell.sh - Environment setup (optional)
• quick_start.sh - Complete automated demo

Configuration

Environment variables (optional):

export DSQL_ENDPOINT="your-dsql-endpoint"
export VALKEY_ENDPOINT="your-valkey-endpoint"
export VALKEY_TTL="20"  # seconds
export QUERY="select * from users1"

Requirements

• AWS CloudShell environment within the VPC
• IAM permissions for DSQL and ElastiCache
• Python packages: valkey, psycopg2-binary, boto3 (auto-installed)

Authentication

• DSQL: Uses IAM authentication tokens (no hardcoded credentials)
• ElastiCache: TLS connection with VPC security groups

Demo Flow

1. Cache Miss: Query DSQL directly → Measure timing → Store in cache
2. Cache Hit: Retrieve from ElastiCache → Compare performance
3. Results: Display speedup metrics and percentage improvement

The demo uses a simple users1 table with 10 employee records to demonstrate real-world database caching scenarios.

Verification & Troubleshooting

Check Valkey Cache Entries

To inspect what's stored in the ElastiCache Valkey cache:

# Connect to Valkey using TLS
valkey-cli -h YOUR_VALKEY_ENDPOINT -p 6379 --tls

# Check if your query is cached
EXISTS "select * from users1"
# Returns: 1 if key exists, 0 if not

# Get a specific cached query result
GET "select * from users1"
# Shows the JSON data: {"result":"...", "dsql_time_seconds":0.082}

# Check TTL (time-to-live) for a key  
TTL "select * from users1"
# Shows remaining seconds, or -1 if no expiry, -2 if key doesn't exist

# List all keys using SCAN (production-safe)
SCAN 0
# Returns cursor and keys. Use cursor in next SCAN if needed

# Search for keys matching a pattern
SCAN 0 MATCH "*users*" COUNT 100

# Alternative: KEYS command (use only in development)
KEYS "*"
# Note: KEYS command may block server and is not recommended for production

# Get cache info and stats
INFO memory
INFO stats

Drop Database for Fresh Demo

To reset the demo and start from scratch:

# Connect to DSQL using psql
psql -h YOUR_DSQL_ENDPOINT -U admin -d postgres --set=sslmode=require

# Drop the users table
DROP TABLE IF EXISTS users1;

# Verify table is gone
\dt

# Exit psql
\q

Or use the Python cleanup script:

#!/usr/bin/env python3
import subprocess
import psycopg2

# Generate auth token
result = subprocess.run([
    'aws', 'dsql', 'generate-db-connect-admin-auth-token',
    '--hostname', 'YOUR_DSQL_ENDPOINT',
    '--expires-in', '3600'
], capture_output=True, text=True, check=True)
token = result.stdout.strip()

# Connect and drop table
conn = psycopg2.connect(
    host='YOUR_DSQL_ENDPOINT',
    user='admin',
    dbname='postgres',
    password=token,
    sslmode='require'
)
conn.set_session(autocommit=True)
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS users1;')
print("[OK] Database reset - users1 table dropped")
conn.close()

Clear Cache Manually

To clear the ElastiCache for a fresh performance comparison:

# Connect to Valkey
valkey-cli -h YOUR_VALKEY_ENDPOINT -p 6379 --tls

# Delete specific query cache
DEL "select * from users1"

# Or flush all cache data (use with caution)
FLUSHDB

# Verify cache is empty
SCAN 0

Common Troubleshooting

1. Connection Issues:

   # Test DSQL connectivity
   aws dsql generate-db-connect-admin-auth-token --hostname YOUR_DSQL_ENDPOINT
   
   # Test ElastiCache connectivity
   valkey-cli -h YOUR_VALKEY_ENDPOINT -p 6379 --tls ping

2. Permission Issues:

   # Check IAM permissions
   aws sts get-caller-identity
   aws iam list-attached-user-policies --user-name $(aws sts get-caller-identity --query 'Arn' --output text | cut -d'/' -f2)

3. VPC/Security Group Issues:

   # Check VPC and security groups
   aws ec2 describe-vpcs --vpc-ids YOUR_VPC_ID
   aws ec2 describe-security-groups --group-ids YOUR_SECURITY_GROUP_ID

4. Reset Everything:

   # Complete reset script
   ./setup_database.py  # Recreates fresh data
   valkey-cli -h YOUR_VALKEY_ENDPOINT -p 6379 --tls FLUSHDB  # Clears cache