Saat Menambahkan Index Membekukan Aplikasi Anda

Tim Anda telah melacak query lambat selama berminggu-minggu. Tabel users telah berkembang hingga jutaan baris, dan pencarian berdasarkan email kini membutuhkan waktu detik, bukan milidetik. Seseorang menyarankan untuk menambahkan index. Perbaikannya tampak sederhana: satu perintah CREATE INDEX, masalah selesai.

Anda menjalankan migrasi di jam kerja karena query tersebut sudah merugikan pengguna. Index mulai dibangun. Kemudian peringatan datang: aplikasi timeout, transaksi gagal, tiket dukungan menumpuk. Index yang Anda tambahkan untuk memperbaiki kinerja justru menyebabkan insiden produksi.

Skenario ini lebih sering terjadi daripada yang diperkirakan sebagian besar tim. Index dan constraint terlihat seperti perubahan skema yang sederhana, tetapi mereka membawa biaya tersembunyi yang baru muncul saat berada di bawah beban.

Apa yang Terjadi Saat Anda Membuat Index

Ketika Anda menjalankan CREATE INDEX idx_users_email ON users(email), database perlu membaca setiap baris dalam tabel, membangun struktur balanced tree, dan menulisnya ke disk. Selama proses ini, database biasanya mengunci tabel untuk mencegah perubahan data yang dapat merusak struktur index.

Jenis kunci tergantung pada database Anda. Di PostgreSQL, CREATE INDEX biasa memperoleh SHARE LOCK. Operasi baca masih bisa berjalan, tetapi operasi tulis apa pun — insert, update, delete — harus menunggu. Di MySQL dengan InnoDB, perilaku penguncian bervariasi tergantung pada jenis operasi dan versi storage engine.

Untuk tabel yang melayani ribuan pengguna, bahkan beberapa detik kunci tulis dapat membuat antrean. Koneksi aplikasi mengantre, timeout berantai, dan apa yang seharusnya menjadi peningkatan kinerja berubah menjadi peristiwa downtime.

Opsi Konkuren yang Sering Dilupakan Tim

Sebagian besar database utama menawarkan cara untuk membangun index tanpa memblokir operasi tulis. PostgreSQL memiliki CREATE INDEX CONCURRENTLY. MySQL mendukung ALGORITHM=INPLACE dan LOCK=NONE pada versi yang kompatibel.

-- PostgreSQL: membangun index tanpa memblokir operasi tulis
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL: membangun index tanpa memblokir operasi tulis (InnoDB)
ALTER TABLE users ADD INDEX idx_users_email (email), ALGORITHM=INPLACE, LOCK=NONE;

Pendekatan konkuren membutuhkan waktu lebih lama. CREATE INDEX CONCURRENTLY milik PostgreSQL harus menunggu transaksi yang sedang berlangsung selesai, kemudian melakukan pass tambahan untuk menangkap perubahan yang terjadi selama pembangunan. Namun, trade-off-nya jelas: aplikasi Anda tetap berjalan saat index dibangun.

Kesalahannya adalah menganggap pembuatan index konkuren sebagai opsional atau lanjutan. Ini harus menjadi default untuk setiap migrasi produksi pada tabel yang aktif. Tulis secara eksplisit di skrip migrasi Anda. Jangan mengandalkan perilaku default.

Constraint Juga Mengunci

Foreign key dan unique constraint membawa kejutan penguncian mereka sendiri. Saat Anda menambahkan foreign key, database memvalidasi bahwa semua baris yang ada memenuhi relasi tersebut. Validasi ini membaca seluruh tabel dan menguncinya selama proses berlangsung.

PostgreSQL menawarkan jalan keluar pragmatis: NOT VALID. Ini menambahkan constraint hanya untuk data baru, melewatkan validasi pada baris yang sudah ada. Anda kemudian dapat menjalankan VALIDATE CONSTRAINT secara terpisah, di luar jam sibuk.

-- Menambahkan foreign key tanpa memvalidasi data yang ada
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Validasi nanti, saat lalu lintas rendah
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

Unique constraint memiliki cerita serupa. Menambahkan UNIQUE pada sebuah kolom memerlukan pemeriksaan duplikat di seluruh tabel. Pada tabel besar, pemindaian ini dapat mengunci operasi tulis untuk durasi yang tidak nyaman.

Rencanakan Migrasi Index Secara Terpisah

Banyak tim menggabungkan semua perubahan skema ke dalam satu file migrasi: menambahkan kolom, membuat index, menambahkan foreign key. Ini berfungsi baik pada tabel kecil. Pada tabel produksi dengan jutaan baris, ini menciptakan satu titik kegagalan.

Perubahan index dan constraint layak mendapatkan file migrasi mereka sendiri, dijadwalkan terpisah dari perubahan struktur tabel. Beberapa tim melangkah lebih jauh dengan menjalankan migrasi index secara manual, di luar pipeline otomatis, untuk mempertahankan kontrol ketat atas waktu.

Pendekatan praktis:

  • Jalankan perubahan struktur tabel (tambah kolom, ubah tipe) dalam satu migrasi
  • Jalankan perubahan index dan constraint dalam migrasi terpisah
  • Jadwalkan migrasi index selama jendela lalu lintas rendah
  • Gunakan opsi konkuren secara eksplisit di setiap skrip migrasi
  • Validasi constraint secara terpisah dari penambahannya

Pertanyaan Sebenarnya

Setelah index dan constraint terpasang, Anda perlu memverifikasi bahwa mereka tidak merusak aplikasi yang sedang berjalan. Index yang mengubah rencana query dapat menyebabkan pergeseran kinerja yang tidak terduga. Foreign key yang memblokir cascade delete dapat menghentikan pekerjaan latar belakang.

Ini mengarah pada pertanyaan yang lebih luas: bagaimana Anda menjaga keamanan migrasi ketika versi lama dan baru dari aplikasi Anda mengakses database yang sama? Itu adalah topik untuk diskusi selanjutnya, tetapi prinsipnya dimulai di sini: perlakukan setiap perubahan skema sebagai potensi peristiwa produksi, bukan sekadar skrip untuk dijalankan.

Daftar Periksa untuk Migrasi Index dan Constraint

  • Gunakan pembuatan index konkuren (CREATE INDEX CONCURRENTLY atau yang setara) pada tabel dengan operasi tulis aktif
  • Tambahkan foreign key dengan NOT VALID jika memungkinkan, validasi secara terpisah
  • Jalankan migrasi index dalam file terpisah dari perubahan struktur tabel
  • Jadwalkan pembangunan index besar selama jendela lalu lintas rendah
  • Pantau durasi kunci dan kinerja query selama dan setelah migrasi
  • Uji migrasi pada salinan data produksi sebelum menjalankannya secara langsung

Kesimpulan

Index bukan sekadar alat kinerja. Ini adalah perubahan skema yang dapat mengunci tabel, mengantrekan operasi tulis, dan membuat pengguna Anda timeout. Perlakukan dengan hati-hati yang sama seperti deployment produksi lainnya. Gunakan opsi konkuren, pisahkan migrasi Anda, dan jadwalkan untuk keamanan. Kecepatan query yang Anda peroleh tidak ada artinya jika migrasi itu sendiri merusak aplikasi.