Fly.io Database Management: AI-Optimized Reference

Critical Decision Matrix

Database Option	Monthly Cost	Setup Time	3AM Pages	Best For
MPG (Managed PostgreSQL)	$38-962+	5 minutes	Rare (auto-failover)	Production apps with budget
Self-hosted PostgreSQL	$15-40	6+ hours	You're the DBA	Cost-conscious with expertise
SQLite + Litestream	$5-15	5 minutes	60s restore from S3	Single-writer apps
External Services	$25-75	Account signup	Provider handles	Most recommended

Configuration That Works in Production

MPG (Managed PostgreSQL)

Cost Reality: $38 minimum (Starter), $72 (Basic), $282-962 (Production)

• Only 7 regions available - permanent choice
• Built-in PgBouncer connection pooling
• Auto-failover in 30 seconds
• Automatic backups (actually work)

Implementation:

fly postgres create --name my-app-db --region iad --plan starter
fly secrets set DATABASE_URL=postgresql://username:password@my-app-db.internal:5432/my_app_production

Critical Limitation: Region choice is permanent. Wrong region = 200ms+ queries forever.

Self-hosted PostgreSQL

Hidden Costs:

• 6+ hours initial setup
• Weekend debugging sessions
• Manual backup management
• You handle all failures

Production Config:

-- postgresql.conf settings that don't crash
shared_buffers = '256MB'          -- 25% of RAM max
effective_cache_size = '1GB'      -- Tell PG available OS RAM
work_mem = '4MB'                  -- Per-query memory
max_connections = 100             -- More = more problems
log_statement = 'all'             -- Essential for debugging

Backup Script (Test Before You Need It):

pg_dump $DATABASE_URL | gzip > backup-$(date +%Y%m%d-%H%M%S).sql.gz
aws s3 cp backup-*.sql.gz s3://my-backups/postgres/

SQLite + Litestream

Performance Reality: 0.1ms queries (no network hop)

• Only one writer, unlimited readers
• Litestream streams to S3 every second
• 60-second restore time from S3
• Expensify runs 4M queries/second on SQLite

Mandatory PRAGMA Settings:

PRAGMA journal_mode = WAL;        -- Required for concurrency
PRAGMA synchronous = NORMAL;      -- FULL too slow, OFF loses data
PRAGMA cache_size = 10000;        -- More cache = faster
PRAGMA foreign_keys = ON;         -- Data integrity
PRAGMA temp_store = MEMORY;       -- RAM faster than disk

Litestream Config:

dbs:
- path: /data/app.db
  replicas:
  - url: s3://my-backup-bucket/db
    retention: 72h
    sync-interval: 1s  # Every second backup

Critical Failure Modes

Connection Pool Hell

Symptom: "too many connections" errors during traffic spikes
Solution: Always use connection pooling

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // Don't exceed database limits
  idleTimeoutMillis: 30000,   // Kill idle connections
  connectionTimeoutMillis: 2000  // Fail fast
});

Volume Disasters

Reality: Volumes glued to one region/machine

• When volumes disappear: ENOENT: no such file or directory
• No cross-region volume migration
• Max 500GB per volume

Check Volume Health:

fly volumes list --app my-app
fly ssh console --app my-app
df -h  # Verify volume mounted

Backup Failures

Critical Test: Restore backup BEFORE production disaster

# Test PostgreSQL backup restore
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U username -d test_db backup.sql

# Test Litestream restore (30 seconds if S3 responsive)
litestream restore -config litestream.yml /tmp/restored.db
sqlite3 /tmp/restored.db ".tables"

Performance Thresholds

Latency Expectations

• Same region: 1-5ms (acceptable)
• Cross-region: 50-200ms (users notice)
• SQLite: 0.1ms (stupid fast)
• External DB: 25-100ms (network tax)

Breaking Points

• UI fails at 1000 spans (distributed tracing becomes unusable)
• Connection limits hit during traffic spikes (causes cascading failures)
• Volume corruption (complete data loss without backups)

Resource Requirements

Time Investment

• MPG setup: 5 minutes
• Self-hosted setup: 6+ hours initial, ongoing maintenance
• Migration complexity: 2x estimated time, always have rollback plan
• Disaster recovery testing: Monthly or learn during outage

Expertise Requirements

• SQLite: Basic SQL knowledge sufficient
• Self-hosted PostgreSQL: DBA-level skills mandatory
• MPG: Application developer level
• External services: Minimal database knowledge needed

Security Implementation

Secrets Management (Never Hardcode)

fly secrets set DATABASE_URL="postgresql://user:pass@host:5432/dbname"
fly secrets set REDIS_URL="redis://user:pass@host:6379"

# Application should crash if missing
const dbUrl = process.env.DATABASE_URL;
if (!dbUrl) {
  throw new Error('DATABASE_URL missing, check secrets');
}

Network Security

• Use private networking (never expose DB to internet)
• SSL/TLS required for all connections
• Encrypt data at rest for compliance

Global Distribution Strategy

Read Replica Setup

fly postgres create --name my-app-db-replica --region fra --fork-from my-app-db

Routing Logic

const getDatabaseUrl = (operation) => {
  const region = process.env.FLY_REGION;

  if (operation === 'read') {
    switch (region) {
      case 'fra': return process.env.DATABASE_URL_EU;
      case 'nrt': return process.env.DATABASE_URL_ASIA;
      default: return process.env.DATABASE_URL_US;
    }
  }

  // All writes to primary (avoid split-brain)
  return process.env.DATABASE_URL_US;
};

Monitoring Critical Metrics

Essential Alerts

1. Connection count (before hitting limits)
2. Slow queries (>100ms indicates problems)
3. Replication lag (data consistency issues)
4. Disk space (running out kills databases)
5. Backup success (untested backups are lies)

Query Performance

-- Find slow queries
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Check connection usage
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Verify replication health
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Migration Strategies

PostgreSQL Migration

pg_dump $OLD_DATABASE_URL > migration.sql
psql $NEW_DATABASE_URL < migration.sql

Common Migration Failures

• Character encoding mismatches
• Foreign key constraint violations during restore
• Sequence resets break auto-increment IDs
• Plan for 2x estimated time + rollback strategy

Cost Optimization Decisions

When to Choose Each Option

Pay for MPG if:

• Monthly revenue > $2000
• Previous self-hosted disasters
• Sleep valued over cost optimization
• Users in MPG's 7 regions

Self-host if:

• Budget-conscious + database competent
• Custom PostgreSQL extensions needed
• Enjoy 3am debugging sessions

Use SQLite if:

• Single-writer application pattern
• Sub-millisecond query requirements
• Simple deployment preferences

External services if:

• Focus on application over infrastructure
• Global distribution required
• Team lacks database expertise

Vendor Lock-in Assessment

• MPG: Moderate (standard PostgreSQL underneath)
• Self-hosted: Zero (Docker containers portable)
• SQLite: Zero (single file format)
• External services: High (proprietary APIs)

Disaster Recovery Procedures

Automatic Failover Times

• MPG: 30 seconds auto-failover
• Self-hosted: Manual recovery (hours without clustering)
• SQLite + Litestream: 60 seconds S3 restore
• External: Provider-dependent (usually automatic)

Recovery Testing Requirements

• Test restore procedures monthly
• Verify backup integrity before disasters
• Document exact recovery steps
• Practice under time pressure

Support and Documentation Quality

Official Resources

• Fly.io docs: Good for setup, lacking troubleshooting
• PostgreSQL docs: Comprehensive but obtuse
• SQLite docs: Excellent technical reference
• Community forums: Essential for real-world problems

Community Wisdom Sources

• Fly.io Discord for real-time issues
• Database Administrators Stack Exchange
• PostgreSQL IRC for deep technical issues
• SQLite forum for performance optimization

This reference prioritizes operational intelligence over marketing claims, focusing on real-world failure modes, actual costs, and production-tested configurations that prevent 3AM emergencies.