AI summary by TwinMind
I Suck At SQL, Now My DB Tells Me How To Fix It
Theo - t3․gg
15m 40s
PlanetScale Schema Recommendations Introduction
- New feature announced by PlanetScale for automatic database optimization
- Creator disclosure: Sometimes paid by PlanetScale, not sponsored for this video
- Feature automatically receives recommendations to improve database performance, reduce memory/storage, improve schema based on production traffic
- Built by Taylor and team over 2 years, upgrading PlanetScale Insights MySQL monitoring tool
PlanetScale Branching Model Overview
- Database branching similar to GitHub workflow
- Create branch = identical clone of existing database schema (no data, just structure)
- Make schema changes, review process, deploy via pull request-like system
- Key feature: After deploy, keeps old database for 30 minutes, writes to both
- Allows revert without data loss, even for data written during transition period
Live Demo - Upload Thing Database
- Accessed PlanetScale Insights tab for production database
- Found 3 redundant indexes identified:
- API key on user ID
- App ID on file
- Key for managing deletions (includes app ID)
- Recommendation: Drop standalone app ID index since it's included in deletion management index
- Similar redundancy found with user ID + tier index vs standalone user ID index
Implementation Workflow
- Challenge: Must make corresponding code changes in application
- Database schema written in Drizzle ORM
- Two approaches:
- Apply recommendation directly via PlanetScale branch
- Use recommendations as guidance, make changes in code first, then deploy normally
- Creator prefers second approach for better code synchronization
Technical Architecture Behind Recommendations
- Internal system called "schema adviser"
- Kafka-based event system triggers on production schema changes
- Background jobs examine schema for potential improvements
- Uses database query performance and statistics data
- Leverages Vitess Query parser and semantic analysis utilities
- Custom MySQL fork with modified ANALYZE TABLE command for cardinality extraction without impacting statistics
PlanetScale Technical Background
- Built on MySQL with Vitess scaling system
- PlanetScale maintains Vitess (originally created for YouTube/Google scale)
- Used by Uber, Slack, GitHub, YouTube for massive scale MySQL deployments
- Fully MySQL compatible while adding modern features
Four Types of Schema Recommendations
- Adding indexes for inefficient queries
- Identifies missing indexes causing table scans
- Referenced famous "$5K database lesson" case study
- Removing redundant indexes
- Exact duplicate indexes (same columns, same order)
- Left prefix duplicate indexes (one index prefixes another)
- 33% of PlanetScale databases have redundant indexes
- Preventing primary key ID exhaustion
- Warns when auto-increment IDs reach 60% of column type maximum
- Recommends upgrading to larger data types
- Dropping unused tables
- Identifies tables unused for 4+ weeks
Case Study: $5K Database Lesson
- Historical example of missing index impact
- Vendor ID field not indexed, causing million-row scans for 100-row results
- Cost escalation: $1000/day due to excessive row reads
- Single index addition reduced costs to $150/month
- PlanetScale waived charges, customer remained satisfied
- Highlighted importance of proper indexing and old pricing model flaws
Performance Monitoring Integration
- Insights tab shows query anomalies and performance breakdowns
- Example: February 23rd anomaly from file upload bursts
- Detailed metrics: 22 queries/second, 7 rows written/second
- Specific query analysis for performance troubleshooting
Pricing and Availability Changes
- Hobby tier no longer globally available (creator expressed disappointment)
- Planning alternative paths for tutorials (local SQLite, other services)
- Scaler tier had same performance as cheapest Pro plan
- CPU limitations more common than read limitations on Scaler
Technical Definitions
- P50 percentile: 50% of queries faster than this value
- P95/P99: 95%/99% of queries faster than these values
- Used for measuring worst-case performance scenarios
