---

Title: Guide for Backend Specialists. Description: How backend engineers use capysquash for database optimization.

GUIDE FOR BACKEND SPECIALISTS

How backend engineers and database specialists leverage capysquash for database optimization and performance.

WHY BACKEND SPECIALISTS USE capysquash

⚡ PERFORMANCE Faster deployments, optimized migrations

🔧 CONTROL CLI-first, scriptable, CI/CD ready

🧠 INTELLIGENCE
Deep analysis, dependency resolution

ADVANCED CAPYSQUASH WORKFLOWS

Deep Analysis


# Comprehensive analysis with AI

export ANTHROPIC_API_KEY="sk-ant-..."
capysquash analyze migrations/*.sql \
  --deep \
  --ai-analysis \
  --report-format json \
  --export analysis.json

Custom Safety Rules

{
  "safety_level": "custom",
  "rules": {
    "consolidate_creates": true,
    "consolidate_alters": true,
    "deduplicate_indexes": true,
    "deduplicate_functions": true,
    "optimize_rls": true,
    "remove_dead_code": false,
    "merge_grants": true
  },
  "conflict_resolution": {
    "duplicate_names": "error",
    "type_conflicts": "error"
  }
}

Streaming for Large Projects


# For 1000+ migration files

capysquash squash migrations/*.sql \
  --streaming \
  --memory-limit 1024 \
  --parallel 8 \
  --progress

DATABASE OPTIMIZATION PATTERNS

Index Deduplication

Problem: Multiple developers create similar indexes

-- Developer A
CREATE INDEX idx_users_email ON users(email);

-- Developer B (later)
CREATE INDEX idx_user_email_lookup ON users(email);

capysquash detects: Duplicate indexes

Solution: Consolidates to single index

Function Deduplication

Problem: Same logic, different names

CREATE FUNCTION update_timestamp_v1() ...
CREATE FUNCTION set_updated_at() ...
-- Both do the same thing

capysquash detects: Semantic duplicates with AI

RLS Policy Optimization

Problem: RLS policies added incrementally

-- Migration 1
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY posts_select ON posts FOR SELECT ...;

-- Migration 5
CREATE POLICY posts_insert ON posts FOR INSERT ...;

-- Migration 12
CREATE POLICY posts_update ON posts FOR UPDATE ...;

capysquash consolidates: All policies together after table creation

PERFORMANCE OPTIMIZATION

Deployment Speed Analysis


# Analyze deployment time impact

capysquash analyze migrations/*.sql --metrics deployment-time

# Output shows:

# Current deployment time: ~8 minutes

# After consolidation: ~45 seconds

# Savings: 7.25 minutes per deployment

Migration Size Optimization


# Check migration folder size

du -sh migrations/

# 45 MB

capysquash squash migrations/*.sql --output clean/
du -sh clean/

# 2.1 MB (95% reduction)

CI/CD INTEGRATION

GitHub Actions

name: Migration Quality Check

on:
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  analyze:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Install capysquash
        run: go install github.com/CAPYSQUASH/pgsquash-engine/cmd/pgsquash@latest

      - name: Analyze migrations
        run: |
          capysquash analyze migrations/*.sql --report-format json > analysis.json

      - name: Check consolidation opportunity
        run: |
          CONSOLIDATION=$(jq '.consolidation_percentage' analysis.json)
          if (( $(echo "$CONSOLIDATION > 50" | bc -l) )); then
            echo "::warning::Migrations have $CONSOLIDATION% consolidation opportunity"
          fi

      - name: Validate migrations
        run: capysquash validate migrations/

GitLab CI

migration-analysis:
  stage: test
  image: CAPYSQUASH/pgsquash:latest
  script:
    - capysquash analyze migrations/*.sql --report-format html --export analysis.html
  artifacts:
    paths:
      - analysis.html
    expire_in: 30 days

Pre-Commit Hook

#!/bin/bash

# .git/hooks/pre-commit

# Check if migrations changed

if git diff --cached --name-only | grep -q "migrations/"; then
  echo "Migrations changed, running analysis..."
  capysquash analyze migrations/*.sql

  # Optionally fail if too many opportunities

  POTENTIAL=$(capysquash analyze migrations/*.sql --report-format json | jq '.consolidation_percentage')
  if (( $(echo "$POTENTIAL > 70" | bc -l) )); then
    echo "ERROR: Consolidation opportunity >70%. Consider running capysquash."
    exit 1
  fi
fi

ADVANCED VALIDATION

Custom PostgreSQL Version


# Validate against specific Postgres version

capysquash validate migrations/ clean/ --postgres-version 17

Schema Diff Analysis


# Show detailed differences if validation fails

capysquash validate migrations/ clean/ --show-diff --verbose

Production Database Validation


# Compare against actual production schema

export PROD_DB_DSN="postgres://..."
capysquash validate-against-db migrations/ --db-dsn $PROD_DB_DSN

DEPENDENCY GRAPH ANALYSIS

Visualize Dependencies


# Export dependency graph

capysquash analyze migrations/*.sql --export-graph deps.dot

# Convert to SVG

dot -Tsvg deps.dot > dependencies.svg

Detect Cycles


# Find circular dependencies

capysquash squash migrations/*.sql --detect-cycles --cycle-details

MONITORING & METRICS

Track Migration Health


# Weekly analysis script
#!/bin/bash
DATE=$(date +%Y-%m-%d)
capysquash analyze migrations/*.sql --report-format json > "metrics/$DATE.json"

# Track over time

echo "$DATE,$(jq '.file_count' metrics/$DATE.json),$(jq '.consolidation_percentage' metrics/$DATE.json)" >> metrics.csv

Alert on Quality Degradation


# Alert if consolidation opportunity increases significantly

CURRENT=$(capysquash analyze migrations/*.sql --report-format json | jq '.consolidation_percentage')
THRESHOLD=60

if (( $(echo "$CURRENT > $THRESHOLD" | bc -l) )); then
  # Send alert (Slack, email, etc.)

  curl -X POST $SLACK_WEBHOOK -d "{\"text\":\"Migration consolidation opportunity: $CURRENT%\"}"
fi

DATABASE MIGRATION STRATEGIES

Blue-Green Deployments


# Squash for both environments

capysquash squash migrations/*.sql --output blue/
capysquash squash migrations/*.sql --output green/

# Deploy to blue first

# Switch traffic

# Then update green

Zero-Downtime Migrations

Capysquash ensures migrations are safely ordered for zero-downtime deploys:

Non-blocking operations first
Index creation with CONCURRENTLY
No DROP COLUMN without notice
RLS policies after table structure

DEBUGGING & TROUBLESHOOTING

Verbose Output

capysquash squash migrations/*.sql --verbose --debug

Parse Error Investigation


# Find which file has issues

capysquash analyze migrations/*.sql --verbose 2>&1 | grep ERROR

# Test specific file

capysquash analyze migrations/problematic.sql --debug

Memory Profiling


# Track memory usage

/usr/bin/time -v capysquash squash migrations/*.sql --memory-limit 512

BEST PRACTICES

Regular consolidation - Monthly or quarterly
Validate always - Never skip validation
Conservative in prod - Use conservative or paranoid safety
Automate analysis - Add to CI/CD pipeline
Monitor metrics - Track migration health over time
Document changes - Clear commit messages

ADVANCED CONFIGURATION

Multi-Environment Setup


# Development

capysquash squash migrations/*.sql \
  --config dev.config.json \
  --safety aggressive

# Staging

capysquash squash migrations/*.sql \
  --config staging.config.json \
  --safety standard

# Production

capysquash squash migrations/*.sql \
  --config prod.config.json \
  --safety conservative \
  --validate

NEXT STEPS

🔄 CI/CD Complete CI/CD setup

✨ BEST PRACTICES Expert tips and patterns

📖 capysquash-cli REFERENCE Complete command reference

---

On this page