Creating SQL Migrations

SQL
-- migrations/001_create_metrics_table.sql
BEGIN;

CREATE TABLE IF NOT EXISTS metrics (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    device_id TEXT NOT NULL,
    metric_name TEXT NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    tags JSONB DEFAULT '{}'
);

-- Convert to hypertable for time-series optimization
SELECT create_hypertable('metrics', 'timestamp', if_not_exists => TRUE);

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_metrics_device_timestamp 
ON metrics (device_id, timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_metrics_name_timestamp 
ON metrics (metric_name, timestamp DESC);

-- Add constraints
ALTER TABLE metrics ADD CONSTRAINT chk_value_not_null CHECK (value IS NOT NULL);

COMMIT;

Progress:

• Create numbered migration file (XXX_description.sql)
• Start with BEGIN; transaction
• Add IF NOT EXISTS clauses for idempotency
• Include TimescaleDB hypertable conversion if time-series data
• Add necessary indexes
• Add constraints and validations
• End with COMMIT;
• Test migration on copy of production data

File naming: 001_create_users_table.sql, 002_add_email_index.sql

Example 1: Adding new table with time-series Input: Need to track sensor readings Output:

SQL
BEGIN;
CREATE TABLE sensor_readings (
    id BIGSERIAL PRIMARY KEY,
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    sensor_id UUID NOT NULL,
    temperature DECIMAL(5,2),
    humidity DECIMAL(5,2)
);

SELECT create_hypertable('sensor_readings', 'recorded_at', if_not_exists => TRUE);
CREATE INDEX IF NOT EXISTS idx_sensor_readings_sensor_time 
ON sensor_readings (sensor_id, recorded_at DESC);
COMMIT;

Example 2: Adding column with default Input: Add status column to orders table Output:

SQL
BEGIN;
ALTER TABLE orders 
ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'pending';

ALTER TABLE orders 
ADD CONSTRAINT chk_orders_status 
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));
COMMIT;

• Always use transactions - Wrap in BEGIN/COMMIT
• Make idempotent - Use IF NOT EXISTS/IF EXISTS clauses
• Index time columns - Create (entity_id, timestamp DESC) indexes for TimescaleDB
• Set chunk intervals - Use SELECT set_chunk_time_interval('table', INTERVAL '1 day'); for high-volume tables
• Add compression - Enable after initial setup: ALTER TABLE table SET (timescaledb.compress);
• Use TIMESTAMPTZ - Always use timezone-aware timestamps
• Validate data - Add CHECK constraints for enum-like values

• Don't forget hypertable conversion - Regular tables miss TimescaleDB benefits
• Avoid large transactions - Split data migrations from schema changes
• Don't use SERIAL for distributed - Use BIGSERIAL or UUID for better scaling
• Missing rollback plan - Always know how to reverse the migration
• Wrong chunk size - Default 7-day chunks may not suit your data volume
• Forgetting retention policies - Set up data retention: SELECT add_retention_policy('table', INTERVAL '30 days');