migrations/
  20240301100000_create_users_table.sql
  20240305120000_add_email_index_to_users.sql
  20240310090000_create_orders_table.sql
  20240315143022_add_status_to_orders.sql

-- Migration: 20240315143022_add_status_to_orders.sql

-- UP
ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT 'pending';
CREATE INDEX idx_orders_status ON orders (status);

-- DOWN
DROP INDEX IF EXISTS idx_orders_status;
ALTER TABLE orders DROP COLUMN IF EXISTS status;

// Batch backfill to avoid locking and memory issues
async function backfillStatus(db, batchSize = 1000) {
  let totalUpdated = 0;
  let updated;

  do {
    const result = await db.query(`
      UPDATE orders
      SET status = 'pending'
      WHERE id IN (
        SELECT id FROM orders
        WHERE status IS NULL
        LIMIT $1
        FOR UPDATE SKIP LOCKED
      )
      RETURNING id
    `, [batchSize]);

    updated = result.rowCount;
    totalUpdated += updated;
    console.log(`Backfilled ${totalUpdated} rows...`);

    // Pause between batches to reduce load
    await new Promise(resolve => setTimeout(resolve, 100));
  } while (updated === batchSize);

  console.log(`Backfill complete. Total rows updated: ${totalUpdated}`);
}

-- 1. Create the new table with desired schema
CREATE TABLE users_v2 (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Copy data in batches
INSERT INTO users_v2 (full_name, email, created_at)
SELECT
  COALESCE(first_name || ' ' || last_name, first_name, 'Unknown'),
  email,
  created_at
FROM users
WHERE id BETWEEN 1 AND 10000;
-- Repeat for remaining batches

-- 3. Set up triggers or dual-write for new data during migration

-- 4. Swap tables atomically
ALTER TABLE users RENAME TO users_deprecated;
ALTER TABLE users_v2 RENAME TO users;

-- 5. Drop old table after observation period
-- DROP TABLE users_deprecated;

undefined

undefined

// seeds/001_users.js
exports.seed = async function(knex) {
  await knex('users').del();
  await knex('users').insert([
    { id: 1, name: 'Alice Developer', email: 'alice@example.com', role: 'admin' },
    { id: 2, name: 'Bob Tester', email: 'bob@example.com', role: 'user' },
    { id: 3, name: 'Carol Manager', email: 'carol@example.com', role: 'manager' },
  ]);
};

// seeds/002_orders.js
exports.seed = async function(knex) {
  await knex('orders').del();
  await knex('orders').insert([
    { id: 1, user_id: 1, status: 'completed', total: 99.99 },
    { id: 2, user_id: 2, status: 'pending', total: 49.50 },
    { id: 3, user_id: 1, status: 'shipped', total: 150.00 },
  ]);
};

-- Set a lock timeout to fail fast instead of waiting indefinitely
SET lock_timeout = '5s';

-- Retry the operation if it times out
-- Application code should handle this and retry with backoff

-- Kill long-running queries that block migrations
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
  AND query NOT LIKE '%pg_stat_activity%';