Core Concepts
Understanding how CAPYSQUASH optimizes PostgreSQL migrations safely and effectively
Core Concepts
Understanding the foundations of safe, intelligent PostgreSQL migration optimization
CAPYSQUASH transforms complex migration management into a simple, automated process. This section explains the core concepts behind how CAPYSQUASH analyzes, optimizes, and validates your migrations.
Quick Overview
Analyze
Parse SQL with PostgreSQL's actual parser to understand every statement and dependency
Optimize
Intelligently consolidate migrations while preserving all functionality and dependencies
Validate
Prove mathematical equivalence using Docker-based schema comparison
Essential Concepts
How It Works
Learn the fundamentals of CAPYSQUASH's optimization process:
How CAPYSQUASH Works
End-to-end explanation of parsing, dependency analysis, consolidation, and validation
Key topics:
- PostgreSQL parser integration (
pg_query_go) - Dependency graph construction
- Statement consolidation strategies
- Schema equivalence verification
Safety Levels
Choose the right balance between consolidation and safety:
Safety Levels
Understanding conservative, standard, aggressive, and paranoid modes
Safety modes:
- Paranoid: Maximum safety (15-30% reduction)
- Conservative: Production-safe (40-60% reduction)
- Standard: Balanced approach (60-80% reduction)
- Aggressive: Maximum consolidation (80-95% reduction)
Migration Analysis
Deep dive into how CAPYSQUASH analyzes your migrations:
Migration Analysis
Statement categorization, dependency detection, and optimization opportunities
Analysis features:
- SQL statement parsing and classification
- Dependency relationship mapping
- Consolidation opportunity detection
- Platform-specific pattern recognition
Docker Validation
The technology that guarantees zero breakage:
Docker Validation
Schema comparison, equivalence testing, and validation methodology
Validation process:
- Parallel schema deployment
- Automated diff generation
- Foreign key and constraint verification
- Index and trigger validation
Ecosystem Architecture
Understanding the CAPYSQUASH product family:
Ecosystem Architecture
How CAPYSQUASH Platform, capysquash-cli, and pgsquash-engine work together
Components:
- CAPYSQUASH Platform: Visual interface and team features
- capysquash-cli: Command-line tool for developers
- pgsquash-engine: Core Go library powering everything
The CAPYSQUASH Philosophy
Safety First
Zero-Breakage Guarantee
Every optimized migration set is validated to be mathematically equivalent to the original. If schemas don't match perfectly, validation fails and no changes are made.
CAPYSQUASH never guesses or estimates. Using Docker-based validation, we deploy both the original and optimized migrations to fresh databases, then compare the resulting schemas with pg_dump. If there's any difference—even a single constraint or index—the optimization is rejected.
Parser-Grade Accuracy
Unlike tools that use regex or basic SQL parsing, CAPYSQUASH uses PostgreSQL's actual C parser (pg_query) via pg_query_go:
Your SQL → pg_query_go → PostgreSQL's Parser → Parse Tree → CAPYSQUASHWhat this means:
- 100% accurate SQL parsing
- Support for all PostgreSQL syntax
- Future-proof as PostgreSQL evolves
- No false positives or missed patterns
Intelligent Consolidation
CAPYSQUASH doesn't just merge files—it intelligently understands your schema evolution:
Parse & Analyze
Extract all SQL statements and build a complete dependency graph
Categorize & Group
Group related statements (CREATE + ALTER, duplicate indexes, redundant policies)
Consolidate Safely
Merge groups while respecting dependencies and safety settings
Validate & Verify
Prove equivalence with Docker-based schema comparison
Common Questions
Why is validation so important?
Manual migration consolidation is error-prone. Developers often miss:
- Subtle timing dependencies
- Implicit constraint orders
- Cross-table relationships
- Index creation dependencies
Docker validation catches these issues automatically, giving you confidence that nothing breaks.
How does CAPYSQUASH handle my specific platform?
CAPYSQUASH has built-in intelligence for popular platforms:
- Supabase: Preserves auth schemas, RLS policies, storage, realtime
- Clerk: Maintains JWT tables and organization structures
- Prisma/Drizzle: Preserves ORM metadata tables
- Neon: Optimizes for serverless patterns
Platform detection is automatic—no configuration required.
What if I have custom or complex migrations?
CAPYSQUASH handles:
- ✓ Complex triggers and functions
- ✓ Custom types and domains
- ✓ Partitioned tables
- ✓ Foreign data wrappers
- ✓ Extensions and schemas
- ✓ Row Level Security
- ✓ Stored procedures
If validation passes, your custom logic is preserved correctly. If validation fails, try a more conservative safety level.
Can I trust the consolidated migrations?
Yes, because of validation:
- Original migrations deployed to Database A
- Optimized migrations deployed to Database B
- Schemas compared with
pg_dump --schema-only - If identical: ✅ Safe to use
- If different: ❌ Optimization rejected
This mathematical proof ensures zero breakage.
What You'll Learn
Beginner Topics
Intermediate Topics
Advanced Topics
Ready to Start?
Get Started
For Teams
Use CAPYSQUASH Platform for visual workflows and team collaboration
For Developers
Use capysquash-cli for command-line workflows and automation
Next Steps
After understanding core concepts, explore:
- Best Practices: Production-ready strategies
- Platform Integrations: Supabase, Neon, Railway guides
- ORM Integrations: Prisma, Drizzle, Clerk support
- Advanced Topics: Deep technical dives
How is this guide?