Какие знаешь способы изменения таблиц?

Способы изменения таблиц (миграции в SQL)

В production системах изменения схемы БД — это критичный процесс. Расскажу о подходах, которые использую для безопасного изменения таблиц.

1. Миграции с Flyway (SQL)

Flyway — это самый популярный инструмент для управления миграциями:

# application.yml
spring:
  flyway:
    locations: classpath:db/migration
    baseline-on-migrate: true

-- db/migration/V1__Create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- db/migration/V2__Add_status_to_users.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'ACTIVE';

-- db/migration/V3__Create_index_on_email.sql
CREATE INDEX idx_users_email ON users(email);

Преимущества:

• Простота и быстрота
• Версионирование миграций
• Автоматическая проверка целостности

Недостатки:

• Нельзя откатить миграцию (только вперёд)
• Требует тестирования на большых таблицах

2. Миграции с Liquibase (XML/YAML)

Liquibase — более гибкий инструмент:

<!-- db/changelog/001-create-users.xml -->
<databaseChangeLog>
    <changeSet id="1" author="john">
        <createTable tableName="users">
            <column name="id" type="BIGINT">
                <constraints primaryKey="true"/>
            </column>
            <column name="username" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="email" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>
    </changeSet>
    
    <changeSet id="2" author="john">
        <addColumn tableName="users">
            <column name="status" type="VARCHAR(50)" defaultValue="ACTIVE"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>

Преимущества:

• Можно откатывать изменения
• Версионирование в любом формате
• Больше контроля

Недостатки:

• Более сложная конфигурация
• Медленнее Flyway

3. Alembic (Python для приложений на Python)

Если приложение на Python/SQLAlchemy:

alembic init migrations
alembic revision --autogenerate -m "Add users table"
alembic upgrade head
alembic downgrade -1  # Откатить последнюю миграцию

4. Online Schema Changes (OSC) - для больших таблиц

Для изменения структуры без блокировки:

-- Проблема: это заблокирует таблицу на время миграции
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(255);

-- Решение 1: Добавить колонку, которую можно заполнять в фоне
ALTER TABLE large_table ADD COLUMN temp_column VARCHAR(255) DEFAULT 'default';

-- Затем в фоне скопируем данные
UPDATE large_table SET temp_column = old_column WHERE temp_column = 'default';

-- Потом переименуем
ALTER TABLE large_table DROP COLUMN old_column;
ALTER TABLE large_table RENAME COLUMN temp_column TO old_column;

Решение 2: Использовать GitHub's gh-ost (online schema migration):

gh-ost \
  --host=192.168.1.100 \
  --user=root \
  --password=secret \
  --database=mydb \
  --table=large_table \
  --alter="ADD COLUMN new_column VARCHAR(255)" \
  --execute

gh-ost создаёт новую таблицу, копирует данные в фоне, переключает триггеры без блокировки.

5. Zero-downtime deployments (Canary)

Стратегия для безопасного развертывания:

// Шаг 1: Добавить новую колонку (не использовать)
ALTER TABLE users ADD COLUMN middle_name VARCHAR(255) NULL;

// Шаг 2: Развернуть новый код, который пишет в обе колонки
@Entity
public class User {
    @Column(name = "full_name")
    private String fullName;
    
    @Column(name = "middle_name")
    private String middleName;
    
    // Сеттер пишет в обе колонки
    public void setFullName(String fullName) {
        this.fullName = fullName;
        // Извлекаем middle name
        this.middleName = extractMiddleName(fullName);
    }
}

// Шаг 3: После того как все инстансы обновлены, удалить старую колонку
ALTER TABLE users DROP COLUMN full_name;

6. Обратная совместимость при миграциях

Всегда пишите код так, чтобы он работал с обеими версиями схемы:

@Entity
@Table(name = "products")
public class Product {
    @Id
    private Long id;
    
    // Старое поле (будет удалено в миграции v3)
    @Column(name = "price_cents", nullable = true)
    private Long priceCents;
    
    // Новое поле
    @Column(name = "price_major", nullable = true)
    private Long priceMajor;
    
    // Геттер работает с обоими полями
    public Long getPrice() {
        if (priceMajor != null) {
            return priceMajor;
        }
        return priceCents / 100;  // Fallback на старое поле
    }
    
    // Сеттер пишет в оба поля
    public void setPrice(Long price) {
        this.priceMajor = price;
        this.priceCents = price * 100;
    }
}

7. Expand-Contract паттерн

Безопасное переименование колонок:

-- Миграция 1: Expand - добавляем новую колонку
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
CREATE UNIQUE INDEX idx_users_email_address ON users(email_address);

-- Миграция 2: Populating (может быть долгой)
UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Миграция 3: Dual-write - пишем в обе колонки
-- Код в приложении пишет в email и email_address

-- Миграция 4: Dual-read - читаем из новой колонки
-- Код читает из email_address, fallback на email

-- Миграция 5: Contract - удаляем старую колонку
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_address TO email;

8. Problematic миграции и их решения

Проблема: Удаление NOT NULL колонки

-- ОПАСНО - может привести к потере данных
ALTER TABLE users DROP COLUMN email;

-- ПРАВИЛЬНО
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;  -- Сначала сделать опциональной
ALTER TABLE users DROP COLUMN email;  -- Потом удалить

Проблема: Добавление колонки с DEFAULT на большой таблице

-- МЕДЛЕННО - переписывает всю таблицу
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'ACTIVE' NOT NULL;

-- БЫСТРЕЕ
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'ACTIVE';  // Nullable сначала
UPDATE users SET status = 'ACTIVE';  // В фоне
ALTER TABLE users ALTER COLUMN status SET NOT NULL;  // Потом ограничение

Проблема: Изменение типа данных

-- МОЖЕТ БЫТЬ ОПАСНО
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- БЕЗОПАСНЕЕ через промежуточную колонку
ALTER TABLE users ADD COLUMN age_new BIGINT;
UPDATE users SET age_new = age::BIGINT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN age_new TO age;

9. Тестирование миграций

@DataJpaTest
public class MigrationTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Test
    public void testMigrationV2_AddStatusToUsers() {
        // Проверяем, что колонка добавлена
        String query = "SELECT column_name FROM information_schema.columns " +
                      "WHERE table_name = 'users' AND column_name = 'status'";
        
        List<Map<String, Object>> result = jdbcTemplate.queryForList(query);
        assertTrue(result.size() > 0, "Status column should exist");
    }
    
    @Test
    public void testMigrationV3_CreateIndexOnEmail() {
        String query = "SELECT indexname FROM pg_indexes WHERE tablename = 'users'";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(query);
        
        boolean hasEmailIndex = result.stream()
            .anyMatch(r -> r.get("indexname").toString().contains("email"));
        assertTrue(hasEmailIndex, "Email index should exist");
    }
}

10. Best Practices для миграций

• Один файл — одна миграция (логическое изменение)
• Тестируй на staging перед production
• Всегда делай бэкап перед большой миграцией
• Мониторь performance во время миграции
• Используй transaction_isolation при needed
• Версионируй миграции (V1, V2...)
• Не используй DROP TABLE без очень веских причин
• Документируй причину миграции в комментариях
• Откатывай стратегию — знай, как откатить изменение
• Параллельные миграции опасны — запускай одну за раз

В целом: миграции БД требуют тщательного планирования. Всегда думай о backwards compatibility, performance impact и плане отката.