The Upgrade

I recently upgraded my personal website from:
- Remix v1 → React Router 7
- Node.js 18 → Node.js 22
- Old Prisma schema → New schema with additional tables

What started as a routine upgrade turned into a deep dive into performance troubleshooting that revealed some surprising bottlenecks.

The Problems

Initial Symptoms

- Random slow requests in production on Fly.io
- Static images taking 10+ seconds to load
- 502 Bad Gateway errors appearing randomly
- Database timeouts in Fly.io logs

The Investigation

#### 1. Database Performance Issues
Initially, I suspected database problems due to the new schema changes. We discovered:
- StreamerHistory table with 779 rows and no indexes
- Expensive subqueries in user search functionality
- Rate limiting hitting the database on every request

Fixes Applied:
- Added proper indexes to `StreamerHistory` table
- Optimized the `searchUsers.sql` query to use `LEFT JOIN` instead of subqueries
- Improved rate limiting to reduce database hits
- Added SQLite `PRAGMA` optimizations for better performance

#### 2. Concurrency Bottlenecks
The database optimizations helped, but static assets were still slow. This led to discovering:
- Concurrency limits in `fly.toml` set to only 80-100 requests
- Request queuing when limits were exceeded
- Health check timeouts of 2 seconds

Fixes Applied:
- Increased concurrency limits from 80-100 to 400-500 requests
- Increased health check timeouts from 2s to 5s
- Made health checks more robust with proper error handling

#### 3. The Real Culprit: Memory Exhaustion
Despite all the optimizations, 502 errors persisted. The final investigation revealed:
- Server running on only 256MB of RAM
- Node.js 22 + React Router 7 requiring significantly more memory
- Out-of-memory crashes causing 502 errors

The Root Cause

The upgrade from Remix v1 to React Router 7, combined with Node.js 22, changed the memory requirements dramatically:

Memory Usage Breakdown:
- Node.js 22 runtime: ~100-150MB
- React Router 7 application: ~50-100MB
- Prisma client: ~50-100MB
- LiteFS replication: ~50MB
- OS overhead: ~50MB
- Buffer for requests: ~100MB+

Total needed: ~400-500MB minimum

With only 256MB available, the server was constantly running out of memory and crashing.

The Solution

The final fix was simple but crucial:

```bash

Scale the Fly.io machine to adequate resources

fly machine update --memory 2048 --cpus 2
```

Key Lessons Learned

1. Modern Frameworks Need More Resources

React Router 7 and Node.js 22 are more powerful but also more resource-intensive than their predecessors. Always account for increased memory requirements when upgrading.

2. Concurrency Limits Matter

The default concurrency settings in `fly.toml` (80-100 requests) were fine for Remix v1 but became a bottleneck with React Router 7's improved request handling.

3. Health Checks Are Critical

Short health check timeouts (2s) combined with slow database queries caused Fly.io to think the server was dead, leading to unnecessary restarts and 502 errors.

4. Database Indexes Are Essential

The new `StreamerHistory` table with 779 rows and no indexes was causing full table scans, significantly impacting performance.

5. Systematic Troubleshooting Works

By methodically checking:
- Database performance
- Concurrency settings
- Health check configuration
- Server resources

We identified and resolved each bottleneck in sequence.

The Final Configuration

Fly.io Machine:
- Memory: 2GB (up from 256MB)
- CPUs: 2
- Concurrency: 400-500 requests (up from 80-100)

Database Optimizations:
- Added indexes to `StreamerHistory` table
- Optimized expensive queries
- Improved rate limiting efficiency
- Added SQLite performance pragmas

Health Checks:
- Timeout: 10s (up from 2s)
- More robust error handling
- Faster database connectivity checks

Results

After the memory upgrade:
- ✅ No more 502 errors
- ✅ Static images load in milliseconds
- ✅ Database queries complete quickly
- ✅ Health checks pass consistently
- ✅ Overall site performance dramatically improved

Takeaway

When upgrading modern web frameworks, don't just focus on code changes—consider the infrastructure requirements too. What worked fine with older, simpler frameworks might not be sufficient for their more powerful successors.

The upgrade was successful, but it required both code optimizations and infrastructure scaling to achieve the desired performance.