Database Index

Jangan Pernah Jalankan CREATE INDEX di Production Saat Jam Sibuk! (Kecuali Tahu Rahasia Ini)

0 Comments

Lebih dari dua tahun saya berkecimpung mengelola database skala enterprise dan PostgreSQL, jika ditanya apa salah satu penyebab downtime konyol yang paling sering saya temui, jawabannya selalu sama: Mengeksekusi manipulasi Index di tabel raksasa (VLDB) pada jam operasional

Niatnya baik: Aplikasi mulai melambat, lalu Backend Engineer atau DBA memutuskan untuk membuat indeks baru agar query kembali ngebut. Mereka mengetik CREATE INDEX idx_baru ON tabel_transaksi(kolom_a);, menekan Enter, dan tiba-tiba… BAM! Seluruh aplikasi berhenti merespons. Timeout di mana-mana. Layanan pelanggan berteriak.

Apa yang sebenarnya terjadi di balik layar PostgreSQL (Internal Engine)? Mari kita bedah apa yang dilakukan Lock Manager, dan bagaimana cara profesional menangani ini dengan status Zero Downtime.

Jebakan Batman: Standard Indexing Operations

“Di dalam memori PostgreSQL, terdapat komponen krusial bernama Lock Manager. Komponen ini bertugas mengevaluasi Conflict Matrix untuk memastikan konsistensi data.”

Agus Syarif

Senior Database Engineer
Postgres Index Operation
Gambar 2: Postgres Index Operation
  1. Tragedi CREATE INDEX & REINDEX (SHARE Lock) Saat kita menjalankan perintah standar CREATE INDEX atau REINDEX, PostgreSQL akan mengambil SHARE Lock pada tabel tersebut.
    • Apa yang lolos? Aplikasi Reporting yang melakukan SELECT masih bisa membaca data.
    • Apa yang tersumbat? Aplikasi Transactional yang melakukan UPDATE, INSERT, atau DELETE akan diblokir total! Jika tabel tersebut berukuran ratusan Gigabyte dan proses indeks memakan waktu 30 menit, artinya aplikasi kita tidak bisa menerima transaksi baru selama setengah jam.
  2. Bencana DROP INDEX (ACCESS EXCLUSIVE Lock) Banyak yang berpikir, “Ah, menghapus indeks kan cepat, hapus saja.” Ini adalah jebakan paling mematikan. Perintah standar DROP INDEX akan meminta ACCESS EXCLUSIVE Lock.
    • Dampaknya? Total Traffic Jam! Lock ini memblokir SEMUANYA. Baik aplikasi transaksional (UPDATE/INSERT) maupun aplikasi pelaporan (SELECT) akan dipaksa mengantre sampai indeks selesai dihapus.
Solusi : Kekuatan Sakti CONCURRENTLY

Lalu, bagaimana cara Tech Giants dan perusahaan Enterprise melakukan pemeliharaan indeks tanpa mematikan layanan? Jawabannya ada pada satu kata ajaib: CONCURRENTLY.

Dengan menambahkan keyword ini (CREATE INDEX CONCURRENTLY atau DROP INDEX CONCURRENTLY), kita mengubah cara PostgreSQL meminta izin pada Lock Manager. Alih-alih meminta lock yang agresif, PostgreSQL hanya akan meminta SHARE UPDATE EXCLUSIVE Lock.

Mengapa ini sangat aman? Karena lock jenis ini sangat ramah. Ia mengizinkan ACCESS SHARE (untuk SELECT) dan ROW EXCLUSIVE (untuk UPDATE/INSERT) berjalan berdampingan tanpa saling memblokir. Aplikasi kita tetap hidup, transaksi tetap masuk, laporan tetap berjalan, sementara PostgreSQL diam-diam membangun indeks di latar belakang.

 

Berikut adalah Best Practices yang wajib diterapkan pada Very Large Databases (VLDB)
  • Haramkan Standard Indexing di Production: Jangan pernah menjalankan CREATE INDEX atau REINDEX biasa di tabel yang aktif menerima operasi tulis (Writes).
  • Gunakan CONCURRENTLY: Selalu biasakan menulis CREATE INDEX CONCURRENTLY atau REINDEX INDEX CONCURRENTLY. Proses ini memang memakan waktu lebih lama (karena PostgreSQL harus melakukan scanning tabel beberapa kali), tetapi ini adalah harga yang sangat murah untuk menjamin Zero Downtime. Biar lambat asal selamat!
  • Hati-hati dengan DROP INDEX: Menghapus indeks yang tidak terpakai itu bagus, tetapi ingat bahwa DROP INDEX biasa mengunci semuanya. Selalu gunakan DROP INDEX CONCURRENTLY di lingkungan live.
  • Waspada Index INVALID: Pembangunan indeks secara concurrent bisa saja gagal (misalnya karena deadlock ringan atau unique constraint violation). Jika gagal, PostgreSQL akan meninggalkan indeks berstatus 'INVALID'. Indeks ini tidak akan digunakan oleh query planner, tetapi tetap membebani proses INSERT/UPDATE. Kita harus segera melakukan DROP INDEX CONCURRENTLY pada indeks yang invalid tersebut sebelum mencoba membuatnya lagi.
Kesimpulan

Mengelola database bukan hanya soal membuat query berjalan cepat, tapi juga tentang memahami bagaimana internal engine bekerja (khususnya mekanisme antrean Locking). Memahami matriks konflik pada PostgreSQL adalah garis batas yang membedakan antara Engineer amatir dan Profesional Sejati.

Punya pengalaman horor terkait locking atau indexing di PostgreSQL? Mari berdiskusi di kolom komentar! 👇

Tags

Leave a Reply

Your email address will not be published. Required fields are marked *