ALTER TABLE and the Locking Minefield

Hibernate generates ALTER TABLE statements with the assumption that the database will handle them gracefully. Databases do handle them. They also lock the table while doing so.

The Lie

Schema changes are fast. Add a column, restart the app, move on.

The Reality

ALTER TABLE locking behavior depends on the database, the operation, and the table size. Hibernate’s DDL generation does not consider any of these factors.

PostgreSQL lock levels for common ALTER TABLE operations:

Operation	Lock Level	Blocks Reads?	Blocks Writes?
ADD COLUMN (nullable, no default)	ACCESS EXCLUSIVE	Yes	Yes
ADD COLUMN (with volatile default)	ACCESS EXCLUSIVE	Yes	Yes
ADD COLUMN (with constant default, PG 11+)	ACCESS EXCLUSIVE	Brief	Brief
DROP COLUMN	ACCESS EXCLUSIVE	Yes	Yes
ALTER COLUMN TYPE	ACCESS EXCLUSIVE	Yes	Yes
CREATE INDEX	SHARE	No	Yes
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	No	No

PostgreSQL 11 improved constant defaults. Adding a column with DEFAULT 'ACTIVE' no longer rewrites the table. But the lock is still acquired, even if briefly. On a table with millions of rows and active transactions, that brief lock can queue behind existing transactions and create a pileup.

MySQL (InnoDB) online DDL:

MySQL 8 handles many ALTER TABLE operations online using the INPLACE algorithm. Adding a column, adding an index, and changing defaults can proceed without blocking reads or writes. But changing a column type, dropping a primary key, or converting character sets requires a table copy, which blocks writes for the duration.

Hibernate does not know about any of this. When ddl-auto=update generates ALTER TABLE customers ADD COLUMN status VARCHAR(255), it does not set a lock timeout, does not use CONCURRENTLY, does not check table size.

The Evidence

// Entity with a new column added
@Entity
@Table(name = "audit_log")
public class AuditLog {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(name = "action")
    private String action;

    @Column(name = "timestamp")
    private Instant timestamp;

    // New column added in a refactor
    @Column(name = "source_ip")
    private String sourceIp;
}

// Hibernate generates on startup with ddl-auto=update:
// alter table audit_log add column source_ip varchar(255)
//
// On PostgreSQL, this acquires ACCESS EXCLUSIVE lock.
// If audit_log has 50 million rows and active transactions:
// - All SELECT queries queue behind the lock
// - All INSERT queries queue behind the lock
// - The lock waits for all existing transactions to complete
// - Your application appears frozen until the ALTER completes

A 50-million-row audit_log table with active long-running transactions can stall for 30 seconds to several minutes. Every connection in your pool queues. Your health check fails. Your load balancer marks the instance as dead.

The Fix

Use migrations with explicit lock management.

-- Flyway migration: V5__add_source_ip_to_audit_log.sql

-- Set a lock timeout so we fail fast instead of blocking
SET lock_timeout = '5s';

-- Add the column. On PG 11+ with a constant default, this is fast.
ALTER TABLE audit_log ADD COLUMN source_ip VARCHAR(45);

-- If we need an index, create it without blocking writes
CREATE INDEX CONCURRENTLY idx_audit_log_source_ip ON audit_log (source_ip);

For MySQL, use pt-online-schema-change from Percona Toolkit for large tables, or rely on InnoDB’s online DDL for operations it supports natively.

The key principle: you control the DDL, you control the locking. Hibernate does not give you that control.

Zero-Downtime Column Addition Pattern

For critical tables, use the expand-and-contract pattern:

-- Step 1: Add nullable column (fast, brief lock)
ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(50);

-- Step 2: Backfill in batches (no lock, runs in background)
UPDATE orders SET shipping_method = 'STANDARD'
WHERE shipping_method IS NULL AND id BETWEEN 1 AND 100000;

UPDATE orders SET shipping_method = 'STANDARD'
WHERE shipping_method IS NULL AND id BETWEEN 100001 AND 200000;
-- ... repeat until done

-- Step 3: Add NOT NULL constraint (PG 12+ validates without full scan if all rows are non-null)
ALTER TABLE orders ALTER COLUMN shipping_method SET NOT NULL;

-- Step 4: Drop old column in a later migration after code no longer references it

This is a multi-deployment process. You cannot do this with ddl-auto=update.

The Cost Model

The cost depends on table size and concurrent load.

Table Rows	ADD COLUMN (PG, nullable)	ADD COLUMN (PG, with default, < PG 11)	CREATE INDEX
10,000	< 100ms lock	< 1s	< 1s
1,000,000	< 100ms lock	5-30s lock	10-60s (CONCURRENTLY: no lock)
50,000,000	< 100ms lock	2-10 min lock	5-30 min (CONCURRENTLY: no lock)

For tables under 100,000 rows with low concurrent load, Hibernate’s DDL generation is unlikely to cause visible impact. Above that threshold, every uncontrolled ALTER TABLE is a potential outage. Migration tools give you the control to avoid it.