CREATE INDEX CONCURRENTLY and Versioned Migrations Over hibernate.ddl-auto

-- National Bank of India: 200 million transactions
-- Plain CREATE INDEX locks the table for the entire build
CREATE INDEX ix_txn_amount ON transactions(amount);
-- During the build: all INSERTs, UPDATEs, DELETEs are blocked
-- Build time: 5-10 minutes on 200M rows
-- Result: payments queue up, API timeouts, effectively an outage

-- PostgreSQL: build without blocking writes
CREATE INDEX CONCURRENTLY ix_txn_amount
    ON transactions(amount);

-- During the build:
--   Reads: allowed
--   Writes: allowed
--   Build time: ~2x longer than plain CREATE INDEX
--   But: ZERO downtime for the application

-- If CONCURRENTLY fails, it leaves an INVALID index
-- Check for invalid indexes:
SELECT indexrelid::regclass, indisvalid
FROM   pg_index
WHERE  NOT indisvalid;

-- Fix: drop the invalid index and retry
DROP INDEX CONCURRENTLY ix_txn_amount;
CREATE INDEX CONCURRENTLY ix_txn_amount ON transactions(amount);

# application.yml - DANGEROUS in production
spring:
  jpa:
    hibernate:
      ddl-auto: update  # Hibernate guesses and alters the schema

1. Non-deterministic: DDL depends on Hibernate version and dialect
   - What runs in staging may differ from production

2. Unreviewed: nobody sees the SQL before it hits the database
   - A small entity change can trigger a destructive ALTER

3. Only additive: never drops or renames safely
   - Removed fields leave orphaned columns
   - Renames become add + orphan (not a true rename)

4. No rollback, no history
   - No versioned record of what changed when
   - Cannot roll back a bad change

5. Unpredictable locks at startup
   - Can take long locks on large tables, delaying deployment

# application.yml - SAFE for production
spring:
  jpa:
    hibernate:
      ddl-auto: validate  # only checks, never alters
  flyway:
    enabled: true
    locations: classpath:db/migration

-- Flyway migration: V3__add_amount_index.sql
-- Reviewed in pull request, applied exactly once
CREATE INDEX CONCURRENTLY ix_txn_amount
    ON transactions(amount);

db/migration/
  V1__create_accounts.sql
  V2__create_transactions.sql
  V3__add_amount_index.sql
  V4__add_status_column.sql

-- V1__create_accounts.sql
CREATE TABLE accounts (
    account_id   VARCHAR(20) PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    balance      DECIMAL(15,2) NOT NULL DEFAULT 0.00
);

-- V3__add_amount_index.sql
-- Note: CONCURRENTLY requires Flyway to run outside a transaction
-- flyway.postgresql.transactional.lock = false
CREATE INDEX CONCURRENTLY ix_txn_amount
    ON transactions(amount);

// com.bank.config.FlywayConfig
package com.bank.config;

import org.flywaydb.core.Flyway;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;

@Configuration
public class FlywayConfig {

    @Bean
    public Flyway flyway(DataSource dataSource) {
        return Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration")
            .load();
    }
}

-- In the migration file, use Flyway's non-transactional marker:
-- For Flyway 9+, name the file with a specific pattern
-- or set the migration as non-transactional in the config

-- V3__add_amount_index.sql
-- This must run outside a transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_txn_amount
    ON transactions(amount);

-- Add a nullable column: instant, no rewrite
ALTER TABLE accounts ADD COLUMN email VARCHAR(255);

-- Add a NOT NULL column with default: may rewrite table (pre PG11)
-- In PostgreSQL 11+, adding with DEFAULT is instant
ALTER TABLE accounts ADD COLUMN status VARCHAR(10)
    NOT NULL DEFAULT 'ACTIVE';

-- Validate a constraint in two phases:
-- Phase 1: add as NOT VALID (instant, no lock)
ALTER TABLE transactions
    ADD CONSTRAINT fk_account
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
    NOT VALID;

-- Phase 2: validate existing rows (no exclusive lock)
ALTER TABLE transactions
    VALIDATE CONSTRAINT fk_account;

-- Check Flyway migration history
SELECT installed_rank, version, description, success
FROM   flyway_schema_history
ORDER BY installed_rank;