Kesalahan umum saat mengubah skema DB di Postgres

Here is a summary of the common database schema change mistakes, translated and structured in Korean:

Kesalahan terkait konkurensi

• Gagal memperoleh lock
• Memperbarui terlalu banyak baris sekaligus
• Membiarkan transaksi tetap terbuka terlalu lama setelah memperoleh exclusive lock

Kesalahan terkait ketepatan tahapan - isu logis

• Penyimpangan skema yang tidak terduga
• Ketidaksesuaian antara skema dan kode aplikasi
• Data yang tidak terduga

Kesalahan lainnya

• Mencapai statement_timeout
• Menggunakan primary key integer 4-byte pada tabel yang bisa terus bertambah
• Mengabaikan cara kerja VACUUM dan risiko bloat

Kasus 1. Ketidaksesuaian skema

• Berjalan di lingkungan development/test tetapi gagal di QA/Staging/Production
• Setelah menemukan penyebabnya, masalah ini harus diselesaikan dengan memperbaiki workflow

Kasus 2. Penyalahgunaan IF [NOT] EXISTS

• Jangan mencoba mengabaikan error ketidaksesuaian skema dengan IF NOT EXISTS
• Penyebab mendasarnya harus dicari dan diselesaikan

Kasus 3. Mencapai statement_timeout

• Uji semua perubahan dengan data berukuran besar agar masalah bisa diketahui lebih awal

Kasus 4. Perubahan besar tanpa batas

• Jika terlalu banyak baris diubah dalam satu transaksi, transaksi lain akan terdampak
• Jika checkpointer tidak dituning, data WAL bisa dihasilkan secara berlebihan
• Saturasi penulisan disk dapat menyebabkan penurunan performa secara keseluruhan
• Masalah VACUUM/bloat bisa terjadi
• Pecah pemrosesan menjadi batch dan kelola VACUUM

Kasus 5. Menunggu di dalam transaksi setelah memperoleh exclusive lock

• Jika ada pekerjaan lain dilakukan di antara BEGIN/ALTER TABLE/COMMIT, lock akan dipertahankan lebih lama
• Setelah memperoleh exclusive lock, transaksi harus diselesaikan secepat mungkin

Kasus 6. Transaksi yang mencakup DDL + DML dalam jumlah besar

• Lock yang diperoleh pada tahap DDL akan tetap bertahan lama hingga tahap DML
• Pisahkan DDL dan DML ke transaksi/tahap migrasi yang berbeda

Kasus 7. Sesi lain terblokir karena menunggu memperoleh exclusive lock

• Saat autovacuum berada dalam mode pencegahan wraparound, DDL tidak melakukan yield
• Bahkan SELECT pun bisa terblokir saat menunggu memperoleh lock
• Atur lock_timeout rendah dan buat logika retry

Kasus 8. Hal yang perlu diperhatikan saat membuat FK

• Saat membuat FK pada tabel besar, pemindaian tabel yang direferensikan dapat memakan waktu
• Definisikan FK dengan opsi not valid, lalu lakukan validate di transaksi terpisah

Kasus 9. Hal yang perlu diperhatikan saat menghapus FK

• Karena lock pada dua tabel diperlukan, logika retry dengan lock_timeout diperlukan

Kasus 10. Hal yang perlu diperhatikan saat menambahkan constraint CHECK

• Karena akan terjadi pemindaian seluruh tabel, gunakan pendekatan dua tahap yang mirip dengan FK

Kasus 11. Hal yang perlu diperhatikan saat menambahkan NOT NULL

• Pada Postgres versi sebelum 11, penambahan NOT NULL pada kolom baru menyebabkan pemindaian tabel
• Sejak Postgres 11, ini bisa diatasi dengan menambahkan kolom NOT NULL DEFAULT
• Sejak Postgres 12, NOT NULL dapat diatur dengan menambahkan constraint CHECK

Kasus 12. Hal yang perlu diperhatikan saat mengubah tipe data kolom

• Seluruh tabel mungkin perlu ditulis ulang
• Diperlukan pendekatan menambahkan kolom baru lalu menyalin data dengan trigger

Kasus 13. Hal yang perlu diperhatikan saat CREATE INDEX

• Di OLTP, harus menggunakan CREATE INDEX CONCURRENTLY
• Jika pembuatan unique index gagal, invalid index yang tersisa perlu dibersihkan

Kasus 14. Hal yang perlu diperhatikan saat DROP INDEX

• Karena ada masalah perolehan lock, gunakan DROP INDEX CONCURRENLTY

Kasus 15. Hal yang perlu diperhatikan saat mengganti nama objek

• Urutan deployment perlu disesuaikan agar terhindar dari ketidaksesuaian antara kode aplikasi dan skema DB

Kasus 16. Menambahkan kolom dengan nilai DEFAULT

• Sebelum PG 11, seluruh tabel akan ditulis ulang
• Sejak PG 11, penambahan kolom dengan nilai DEFAULT menjadi lebih cepat

Kasus 17. Pembersihan sisa saat CREATE INDEX CONCURRENTLY gagal

• Jika gagal, invalid index akan tertinggal sehingga perlu dibersihkan sebelum retry

Kasus 18. Menggunakan primary key integer 4-byte pada tabel besar

• Harus menggunakan int8. Sebagian besar framework sudah menggunakan int8.

Rekomendasi

• Lakukan pengujian dengan ukuran data yang realistis
• Periksa lamanya exclusive lock dipertahankan
• Tingkatkan otomatisasi deployment
• Belajar dari orang lain dan bagikan pengetahuan

Opini GN⁺

Tulisan ini merangkum dengan baik berbagai kesalahan dan hal-hal yang perlu diperhatikan saat benar-benar melakukan perubahan skema DB. Khususnya, isu terkait exclusive lock sering disebut, dan ini bisa menjadi masalah yang jauh lebih serius pada database berukuran besar.

Hal-hal yang perlu diperhatikan saat menangani FK, NOT NULL, indeks, dan sebagainya—yang sering kali mudah terlewat oleh developer—juga dijelaskan dengan cukup konkret. Memahami dan memanfaatkan peningkatan di tiap versi Postgres tampaknya juga akan sangat membantu.

Yang terpenting, saya setuju bahwa pengujian menyeluruh dengan ukuran data yang realistis serta peningkatan otomatisasi deployment adalah kunci untuk meminimalkan risiko perubahan skema. Untuk pengujian dan otomatisasi deployment, memanfaatkan alat seperti Database Lab Engine juga bisa menjadi pilihan yang baik.

Akan bagus jika lebih banyak blog teknis yang membagikan tips berguna seperti ini. Semakin luas informasi seperti ini tersebar, semakin besar pula bantuannya dalam meningkatkan kemampuan para developer yang bekerja dengan database.