go-caatsm/docs/migrations.md

Database Migrations Guide

This document describes the database schema management and migration strategy for the CAATSM application.

Current Approach

The application currently uses DDL (Data Definition Language) files for schema management:

• Schema file: internal/infra/postgres/telegrams.ddl
• Manual execution: Schema changes are applied manually using psql or similar tools
• Version control: DDL files are version-controlled in the repository

Current Schema Structure

The application uses TimescaleDB (PostgreSQL extension) with the following key components:

• Schema: aviation
• Main table: aviation.telegrams (hypertable for time-series data)
• Raw table: aviation.telegrams_raw (for unparsed/failed messages)
• Indexes: Multiple indexes on key fields for query performance

Recommended Migration Tools

For production deployments, we recommend using a dedicated migration tool for better schema management:

Option 1: golang-migrate (Recommended)

golang-migrate is a popular Go-based migration tool with excellent PostgreSQL support.

Installation:

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

Setup:

1. Create migrations directory:

   mkdir -p migrations
   

2. Create initial migration from existing schema:

   migrate create -ext sql -dir migrations -seq initial_schema
   

3. Copy DDL content to migration files:

   • migrations/000001_initial_schema.up.sql - Create schema
   • migrations/000001_initial_schema.down.sql - Drop schema

Usage:

# Apply all migrations
migrate -path migrations -database "postgres://user:pass@localhost:5432/aviation?sslmode=disable" up

# Rollback last migration
migrate -path migrations -database "postgres://user:pass@localhost:5432/aviation?sslmode=disable" down 1

# Check migration version
migrate -path migrations -database "postgres://user:pass@localhost:5432/aviation?sslmode=disable" version

Option 2: migrate (by golang-migrate, different package)

Similar to golang-migrate but distributed as a separate package.

Option 3: Custom Migration Scripts

For simple deployments, you can create custom migration scripts that:

• Check current schema version
• Apply migrations sequentially
• Track migration state in a schema_migrations table

Migration Workflow

Development

1. Create migration file:

   migrate create -ext sql -dir migrations -seq add_new_column
   

2. Write up migration (migrations/XXXXXX_add_new_column.up.sql):

   ALTER TABLE aviation.telegrams
   ADD COLUMN new_field TEXT;
   
   CREATE INDEX idx_telegrams_new_field ON aviation.telegrams (new_field);
   

3. Write down migration (migrations/XXXXXX_add_new_column.down.sql):

   DROP INDEX IF EXISTS idx_telegrams_new_field;
   ALTER TABLE aviation.telegrams
   DROP COLUMN IF EXISTS new_field;
   

4. Test migration:

   # Apply
   migrate -path migrations -database "$DATABASE_URL" up
   
   # Rollback
   migrate -path migrations -database "$DATABASE_URL" down 1
   

Production

1. Backup database before applying migrations:

   pg_dump -U postgres -d aviation > backup_$(date +%Y%m%d_%H%M%S).sql
   

2. Test migration on staging environment first

3. Apply migration during maintenance window:

   migrate -path migrations -database "$DATABASE_URL" up
   

4. Verify migration success:

   migrate -path migrations -database "$DATABASE_URL" version
   

5. Monitor application for any issues

Schema Evolution Best Practices

1. Backward Compatibility

• Additive changes (new columns, indexes) are generally safe
• Removing columns requires application code changes first
• Changing column types requires careful planning and data migration

2. TimescaleDB Considerations

• Hypertables: Be careful when modifying hypertable structure
• Retention policies: Consider impact on existing data
• Compression: Test compression policies with schema changes

3. Index Management

• Create indexes concurrently in production to avoid locking:

  CREATE INDEX CONCURRENTLY idx_telegrams_new_field ON aviation.telegrams (new_field);
  

• Drop unused indexes to improve write performance

4. Data Migrations

For data transformations, use separate migration steps:

1. Add new column (nullable)
2. Backfill data in application or migration script
3. Add constraints (NOT NULL, etc.) after backfill
4. Remove old column in separate migration

5. Rollback Procedures

Always provide rollback migrations:

• Test rollback on staging before production
• Document rollback steps in migration comments
• Consider data loss implications of rollbacks

Example Migration

Adding a New Index

Up migration:

-- Add index for querying by category and date
CREATE INDEX CONCURRENTLY idx_telegrams_category_date 
ON aviation.telegrams (category, received_at DESC);

Down migration:

-- Remove index
DROP INDEX IF EXISTS idx_telegrams_category_date;

Adding a New Column

Up migration:

-- Add processing_metadata column for additional metadata
ALTER TABLE aviation.telegrams
ADD COLUMN processing_metadata JSONB;

-- Add index for JSONB queries
CREATE INDEX CONCURRENTLY idx_telegrams_processing_metadata_gin
ON aviation.telegrams USING GIN (processing_metadata);

Down migration:

-- Remove index and column
DROP INDEX IF EXISTS idx_telegrams_processing_metadata_gin;
ALTER TABLE aviation.telegrams
DROP COLUMN IF EXISTS processing_metadata;

Migration State Management

Schema Version Tracking

Migration tools typically use a schema_migrations table to track applied migrations:

CREATE TABLE IF NOT EXISTS schema_migrations (
    version BIGINT NOT NULL PRIMARY KEY,
    dirty BOOLEAN NOT NULL
);

Checking Migration Status

# Check current version
migrate -path migrations -database "$DATABASE_URL" version

# Check for pending migrations
migrate -path migrations -database "$DATABASE_URL" up

CI/CD Integration

Automated Migration Testing

Add migration tests to CI pipeline:

# Example GitHub Actions workflow
- name: Test migrations
  run: |
    # Start test database
    docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=test postgres:15
    
    # Wait for database
    sleep 5
    
    # Apply migrations
    migrate -path migrations -database "postgres://postgres:test@localhost:5432/test?sslmode=disable" up
    
    # Verify schema
    psql "postgres://postgres:test@localhost:5432/test?sslmode=disable" -c "\d aviation.telegrams"

Deployment Automation

For production deployments, integrate migrations into deployment process:

1. Pre-deployment: Backup database
2. Deployment: Apply migrations
3. Post-deployment: Verify migration success
4. Rollback: If migration fails, rollback application and database

Troubleshooting

Migration Failures

Common issues:

• Lock conflicts: Use CONCURRENTLY for index creation
• Timeout errors: Increase migration timeout for large tables
• Dirty state: Manually fix schema_migrations table if migration fails mid-way

Recovery:

-- Check migration state
SELECT * FROM schema_migrations;

-- Fix dirty state (if needed)
UPDATE schema_migrations SET dirty = false WHERE version = X;

Performance Considerations

• Large tables: Test migrations on production-sized data
• Downtime: Plan for maintenance windows for major schema changes
• Replication lag: Consider impact on read replicas

Future Improvements

Consider implementing:

1. Automated migration testing in CI/CD
2. Migration rollback automation in deployment pipeline
3. Schema validation before applying migrations
4. Migration dry-run mode for testing
5. Migration status monitoring and alerting

References

• golang-migrate Documentation
• TimescaleDB Best Practices
• PostgreSQL Migration Guide