Kenapa ALTER TABLE ADD COLUMN NOT NULL berbahaya di tabel besar?

ALTER TABLE ADD COLUMN dengan NOT NULL constraint di PostgreSQL (sebelum versi 11) memerlukan table rewrite penuh — menulis ulang setiap baris untuk menambahkan kolom baru dengan nilai default. Di PostgreSQL 11+, ADD COLUMN dengan DEFAULT static tidak butuh rewrite, tapi NOT NULL tanpa DEFAULT masih memerlukan verifikasi setiap baris. Yang lebih krusial: operasi ini memegang ACCESS EXCLUSIVE lock yang memblokir semua operasi baca DAN tulis di tabel tersebut selama proses berlangsung. Di tabel 50M row, ini bisa 5–15 menit tergantung disk I/O — dan di production, itu berarti outage.

Apa itu pola expand-contract dan kapan digunakan?

Expand-contract adalah strategi untuk melakukan perubahan schema yang breaking (seperti rename kolom) tanpa downtime. Fase Expand: tambah kolom baru, tulis ke kedua kolom (lama dan baru). Fase Migrate: backfill data lama ke kolom baru. Fase Contract: baca dari kolom baru, hentikan penulisan ke kolom lama. Fase Drop: hapus kolom lama. Ini memungkinkan deployment aplikasi bertahap tanpa ada titik di mana schema dan kode tidak kompatibel.

Apa perbedaan NOT VALID constraint vs constraint biasa di PostgreSQL?

ALTER TABLE ADD CONSTRAINT ... NOT VALID menambahkan constraint ke tabel tanpa memvalidasi row yang sudah ada. Ini tidak membutuhkan lock panjang. Kemudian VALIDATE CONSTRAINT memvalidasi row existing, tapi hanya membutuhkan ShareUpdateExclusiveLock (tidak blokir baca/tulis normal). Hasilnya: constraint ditambahkan tanpa table lock, validasi dilakukan secara background-safe. Tersedia sejak PostgreSQL 9.6.

Apakah perlu tools khusus seperti gh-ost atau pt-online-schema-change untuk PostgreSQL?

Untuk MySQL, gh-ost dan pt-online-schema-change (Percona Toolkit) adalah tools standar untuk zero-downtime migration. Untuk PostgreSQL, tools tersebut tidak applicable — PostgreSQL punya mekanisme native yang cukup: CREATE INDEX CONCURRENTLY untuk index, NOT VALID + VALIDATE CONSTRAINT untuk constraint, dan pola backfill bertahap manual atau via tools seperti strong_migrations (Rails gem) atau pglogical untuk kasus yang lebih kompleks. Untuk mayoritas kasus ADD COLUMN + NOT NULL, pola manual yang dibahas di artikel ini sudah cukup.

Database migration zero-downtime untuk tabel besar: pola yang benar-benar jalan

Bulan lalu saya harus menambahkan kolom verified_at TIMESTAMPTZ NOT NULL ke tabel user_events yang waktu itu sudah ada sekitar 60 juta baris di production. Ini bukan tabel kecil, dan ini bukan bisa di-schedule downtime.

Percobaan pertama saya — menjalankan ALTER langsung di staging dulu untuk lihat berapa lama — butuh 8 menit 40 detik. Tabel staging ini ~40 juta row karena baru partial copy. Di production dengan 60 juta row dan disk I/O yang lebih tinggi, estimasinya 12–15 menit.

Dua belas menit dengan ACCESS EXCLUSIVE lock di tabel events = seluruh fitur yang bergantung pada tabel itu tidak bisa baca atau tulis = outage partial yang cukup panjang untuk masuk ke RTO breach.

Artikel ini adalah dokumentasi pola yang akhirnya saya pakai — dan pola-pola lain yang worth tahu sebelum Anda masuk situasi serupa.

Kenapa naive migration berbahaya

ALTER TABLE user_events ADD COLUMN verified_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

Perintah ini terlihat sederhana. Yang terjadi di belakangnya tidak sederhana.

PostgreSQL untuk melakukan ini harus:

Acquire ACCESS EXCLUSIVE lock di tabel — lock yang paling restrictif, memblokir semua operasi lain termasuk SELECT biasa.
Untuk kolom NOT NULL, memverifikasi atau menulis nilai default ke setiap existing row.
Update catalog metadata.
Release lock.

Step 2 adalah yang mahal. Di 50 juta row:

Sequential scan seluruh tabel: 2–4 GB data (tergantung row width)
Disk I/O: bergantung pada apakah data masih di buffer pool atau perlu disk read
Di slow disk atau high-load system: bisa 5–15 menit

Selama waktu itu, setiap query yang menyentuh tabel tersebut — SELECT, INSERT, UPDATE, DELETE — masuk ke queue tunggu. Koneksi pool habis. Application error mulai muncul.

Catatan penting: PostgreSQL 11+ membuat ADD COLUMN dengan DEFAULT static (constant, bukan expression) tidak butuh table rewrite — default disimpan di catalog, bukan ditulis ke setiap row. Ini improvement besar. Tapi ini tidak membantu untuk NOT NULL tanpa default, atau untuk kasus di mana Anda butuh backfill dengan computed value.

Pola 1: Add nullable first, backfill, then constrain

Ini adalah pola paling umum dan paling safe untuk ADD COLUMN NOT NULL dengan nilai default yang perlu di-compute.

Step 1: Tambah kolom sebagai nullable

ALTER TABLE user_events ADD COLUMN verified_at TIMESTAMPTZ;

Operasi ini di PostgreSQL 11+ hampir instantaneous — hanya update catalog, tidak ada table scan, tidak ada row rewrite. Lock yang dipegang sangat singkat (milliseconds). Production traffic: tidak terpengaruh.

Step 2: Backfill dalam batch kecil

Ini yang krusial. Jangan jalankan UPDATE user_events SET verified_at = created_at langsung — itu sama buruknya dengan ALTER awal: satu transaction besar yang lock semua row yang di-update.

Backfill yang aman dilakukan dalam batch, dengan jeda antar batch:

-- Jalankan ini dalam loop, dari luar psql (script Python/bash/dll)
-- Atau bisa juga procedure dengan pg_sleep

DO $$
DECLARE
  batch_start BIGINT := 0;
  batch_size  BIGINT := 1000;
  max_id      BIGINT;
  updated_count INT;
BEGIN
  SELECT MAX(id) INTO max_id FROM user_events;

  WHILE batch_start <= max_id LOOP
    UPDATE user_events
    SET verified_at = created_at  -- atau computed value Anda
    WHERE id BETWEEN batch_start AND batch_start + batch_size - 1
      AND verified_at IS NULL;

    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RAISE NOTICE 'Batch % to %: % rows updated', batch_start, batch_start + batch_size - 1, updated_count;

    batch_start := batch_start + batch_size;

    -- Jeda 100ms antar batch untuk beri napas ke I/O dan vacuum
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

Kenapa batch 1.000 row dan bukan lebih besar? Di 1.000 row per batch:

Setiap UPDATE transaction kecil — tidak menumpuk WAL terlalu banyak
Lock row hanya dipegang sebentar per batch
HOT update storm (Heap Only Tuple — mekanisme update PostgreSQL) tidak terlalu berat
pg_sleep(0.1) antara batch memberi ruang untuk autovacuum bekerja dan mencegah table bloat berlebihan

Untuk tabel 50 juta row: 50.000 batch x 0.1 detik jeda minimum = ~1.4 jam. Ini lama, tapi sepenuhnya background — production traffic tidak terpengaruh.

Kalau ingin lebih cepat, Anda bisa naikkan batch size ke 5.000–10.000 dan monitor pg_stat_activity dan pg_stat_bgwriter untuk pastikan tidak ada I/O contention.

Step 3: Tambah CHECK CONSTRAINT dulu (trick PostgreSQL 12+)

Di PostgreSQL 12+, ada cara untuk set NOT NULL tanpa full table scan menggunakan constraint validation yang sudah ada:

-- Langkah 3a: Tambah CHECK CONSTRAINT sebagai NOT VALID (tidak scan existing rows)
ALTER TABLE user_events
  ADD CONSTRAINT user_events_verified_at_not_null
  CHECK (verified_at IS NOT NULL) NOT VALID;

-- Langkah 3b: Validasi constraint (hanya butuh ShareUpdateExclusiveLock)
-- Ini bisa berjalan concurrent dengan traffic normal
ALTER TABLE user_events
  VALIDATE CONSTRAINT user_events_verified_at_not_null;

NOT VALID artinya constraint ditambahkan ke catalog tapi tidak memverifikasi row existing. VALIDATE CONSTRAINT kemudian memverifikasi row existing — tapi dengan ShareUpdateExclusiveLock yang tidak memblokir SELECT dan DML normal.

Setelah constraint tervalidasi, PostgreSQL sudah “tahu” semua row memenuhi NOT NULL. Sekarang kita bisa set constraint di level column:

-- Langkah 3c: Set NOT NULL di column definition
-- PostgreSQL 12+ cukup smart untuk tidak melakukan full scan kalau CHECK CONSTRAINT sudah divalidasi
ALTER TABLE user_events ALTER COLUMN verified_at SET NOT NULL;

Di PostgreSQL 12+, step ini tidak membutuhkan full table scan karena planner sudah punya proof dari validated CHECK CONSTRAINT.

Step 4: Drop CHECK CONSTRAINT sementara

ALTER TABLE user_events DROP CONSTRAINT user_events_verified_at_not_null;

Sekarang kolom sudah NOT NULL di level column definition, CHECK CONSTRAINT-nya sudah tidak diperlukan.

Ringkasan Step 1-4 untuk ADD COLUMN NOT NULL:

Step	Operasi	Lock Level	Durasi
1	ADD COLUMN nullable	AccessExclusiveLock (sangat singkat)	Milliseconds
2	Backfill batch	RowExclusiveLock per batch	1–2 jam (background)
3a	ADD CONSTRAINT NOT VALID	ShareUpdateExclusiveLock	Milliseconds
3b	VALIDATE CONSTRAINT	ShareUpdateExclusiveLock	Menit (tidak blokir traffic)
3c	SET NOT NULL	AccessExclusiveLock (sangat singkat, karena constraint exist)	Milliseconds
4	DROP CONSTRAINT	AccessExclusiveLock (sangat singkat)	Milliseconds

Total lock time yang meaningful: hampir nol.

Pola 2: expand-contract untuk rename kolom

Rename kolom adalah operasi yang kelihatan sederhana tapi sebenarnya sangat disruptif: ALTER TABLE user_events RENAME COLUMN old_name TO new_name membutuhkan ACCESS EXCLUSIVE lock DAN kode aplikasi yang masih menjalankan query dengan nama lama akan error.

Expand-contract menyelesaikan ini dalam empat fase deployment yang bisa dilakukan tanpa koordinasi downtime:

Fase 1 — Expand

Tambah kolom baru dengan nama baru. Deploy kode yang menulis ke kedua kolom (lama dan baru). Baca dari kolom lama.

ALTER TABLE user_events ADD COLUMN new_column_name TIMESTAMPTZ;

Kode aplikasi:

# Deploy versi ini dulu
def insert_event(conn, event):
    conn.execute("""
        INSERT INTO user_events (old_column_name, new_column_name, ...)
        VALUES (%s, %s, ...)
    """, (event.value, event.value, ...))  # Tulis ke keduanya

Fase 2 — Migrate

Backfill new_column_name dari old_column_name untuk semua row existing. Gunakan pola batch dari Pola 1. Selama fase ini, kode dari Fase 1 masih berjalan — row baru sudah dapat kedua nilai, row lama sedang di-backfill.

-- Backfill batch
UPDATE user_events
SET new_column_name = old_column_name
WHERE id BETWEEN :start AND :end
  AND new_column_name IS NULL;

Verifikasi backfill selesai:

SELECT COUNT(*) FROM user_events WHERE new_column_name IS NULL;
-- Harus 0 sebelum lanjut ke Fase 3

Fase 3 — Contract

Deploy kode yang membaca dari kolom baru, masih menulis ke keduanya untuk safety window.

# Deploy versi ini
def get_event_value(row):
    return row['new_column_name']  # Baca dari kolom baru

def insert_event(conn, event):
    conn.execute("""
        INSERT INTO user_events (old_column_name, new_column_name, ...)
        VALUES (%s, %s, ...)
    """, (event.value, event.value, ...))  # Masih tulis ke keduanya

Monitor selama beberapa jam/hari untuk pastikan tidak ada issue.

Fase 4 — Drop

Deploy kode yang tidak lagi menulis ke kolom lama. Drop kolom lama.

# Deploy versi final — tidak lagi tulis ke old_column_name
def insert_event(conn, event):
    conn.execute("""
        INSERT INTO user_events (new_column_name, ...)
        VALUES (%s, ...)
    """, (event.value, ...))

ALTER TABLE user_events DROP COLUMN old_column_name;

Kenapa empat fase, bukan langsung drop? Karena di setiap titik, versi kode yang sedang berjalan kompatibel dengan schema saat ini. Tidak ada deployment yang bisa menghasilkan “kode menggunakan kolom yang tidak ada”.

Pola 3: ghost tables untuk perubahan schema yang lebih kompleks

Untuk perubahan yang lebih fundamental — mengubah tipe data kolom, mengubah primary key, merge/split tabel — pola backfill manual tidak cukup. Anda butuh pendekatan ghost table.

Untuk MySQL: pt-online-schema-change (Percona Toolkit) dan gh-ost (GitHub) adalah tools yang sudah battle-tested. Mereka membuat shadow table, sync data via trigger, lalu melakukan table swap atomik.

Untuk PostgreSQL: situasinya berbeda karena PostgreSQL tidak punya tools sekelas gh-ost yang mature. Alternatif yang digunakan production:

pglogical: logical replication untuk sync ke shadow table, kemudian swap. Butuh setup lebih kompleks.
pg_repack: tool untuk repack tabel tanpa long lock, berguna untuk reclaim bloat setelah heavy update workload.
Manual logical replication: subscribe ke WAL changes via pgoutput atau wal2json, apply ke shadow table, swap saat lag mendekati nol.

Ini territory yang butuh artikel tersendiri. Poin penting: untuk PostgreSQL, mayoritas ADD COLUMN dan rename bisa diselesaikan dengan Pola 1 dan 2 tanpa tools tambahan. Ghost table biasanya hanya diperlukan untuk perubahan tipe data atau structural refactor yang besar.

Pitfalls yang sering diabaikan

1. Deploy sequence: kode dulu, schema kemudian

Ini yang paling sering jadi penyebab incident bukan migration-nya, tapi sequence-nya.

Urutan yang salah:

Jalankan migration (drop kolom lama)
Deploy kode baru (yang tidak lagi pakai kolom lama)

Antara step 1 dan 2, versi kode lama yang masih berjalan akan error karena mencoba akses kolom yang sudah tidak ada.

Urutan yang benar (untuk expand-contract):

Deploy kode yang kompatibel dengan schema lama DAN baru
Jalankan migration yang additive (tambah kolom baru)
Backfill
Deploy kode yang pakai kolom baru
Deploy kode yang tidak pakai kolom lama
Drop kolom lama

Kalau menggunakan blue-green deployment atau canary: pastikan overlap period antara versi kode tidak ada konflik dengan schema state saat itu.

2. Index harus pakai CREATE INDEX CONCURRENTLY

-- JANGAN lakukan ini di production table besar:
CREATE INDEX idx_user_events_verified_at ON user_events(verified_at);
-- ^ Ini lock tabel selama index build

-- Yang benar:
CREATE INDEX CONCURRENTLY idx_user_events_verified_at ON user_events(verified_at);
-- ^ Tidak blokir read/write, tapi lebih lama dan butuh lebih banyak resource

CREATE INDEX CONCURRENTLY membutuhkan dua pass table scan dan tidak bisa berjalan di dalam transaction block. Tapi tradeoffnya worth it: traffic production tidak terpengaruh selama index build.

Catatan: kalau CREATE INDEX CONCURRENTLY gagal di tengah jalan, ia meninggalkan invalid index. Cek dengan SELECT * FROM pg_indexes WHERE tablename = 'user_events' — kalau ada index dengan INVALID state, drop dan buat ulang.

3. Foreign key constraint juga perlu NOT VALID

-- Ini lock source dan target table selama validasi
ALTER TABLE orders ADD CONSTRAINT fk_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

-- Yang aman:
ALTER TABLE orders ADD CONSTRAINT fk_user_id
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Lalu validasi terpisah (ShareUpdateExclusiveLock, tidak blokir traffic):
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

4. Autovacuum dan table bloat selama backfill

Backfill besar menghasilkan banyak dead tuple — UPDATE menghasilkan dead tuple dari version lama row. Kalau autovacuum tidak bisa keep up, tabel bloat dan query slow.

Monitor selama backfill:

-- Check autovacuum progress
SELECT schemaname, relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'user_events';

Kalau n_dead_tup naik cepat dan autovacuum tidak catch up, pertimbangkan trigger vacuum manual antar batch:

VACUUM (VERBOSE) user_events;

Ini tidak butuh lock eksklusif dan bisa dijalankan concurrent dengan traffic normal.

Checklist migration zero-downtime

Sebelum eksekusi migration di production, verifikasi:

Schema change sudah ditest di staging dengan ukuran data mendekati production (bukan data kosong).
Deploy sequence sudah didokumentasikan: urutan deploy kode dan migration sudah jelas dan sudah di-review.
Kode aplikasi kompatibel dengan schema sebelum DAN sesudah migration untuk setiap step.
Tidak ada ALTER TABLE yang berpotensi butuh table rewrite tanpa rencana mitigasi.
Index dibuat dengan CONCURRENTLY, constraint ditambahkan dengan NOT VALID + VALIDATE terpisah.
Monitoring aktif selama backfill: pg_stat_activity, pg_stat_bgwriter, query latency, connection pool usage.
Rollback plan tersedia: kalau migration harus di-abort di tengah jalan, apa langkahnya? Apakah additive columns bisa di-drop tanpa issue?

Closing note

Zero-downtime migration bukan tentang satu trick ajaib — ini tentang memahami bahwa setiap operasi schema punya biaya locking yang berbeda, dan bahwa perubahan besar perlu dipecah menjadi langkah-langkah kecil yang masing-masing aman.

PostgreSQL memberi Anda tools yang cukup untuk hampir semua skenario tanpa membutuhkan external tooling: NOT VALID constraint, CREATE INDEX CONCURRENTLY, backfill bertahap dengan pg_sleep. Yang sering kurang adalah pemahaman tentang kapan tools ini diperlukan dan bagaimana menyusun sequence yang benar.

Rule of thumb yang paling berguna: kalau ragu apakah sebuah ALTER akan lock lama, test dulu di tabel dengan ukuran data sebanding di lingkungan non-production. Satu EXPLAIN atau timing test di staging jauh lebih murah dari 10 menit outage di production.