Kesalahan umum saat mengubah skema DB di Postgres

 (postgres.ai)
5 poin oleh GN⁺ 2024-04-29 | 1 komentar | Bagikan ke WhatsApp
  • Migrasi skema di Postgres sangat berisiko terutama di lingkungan OLTP besar, karena lock, penulisan ulang tabel, dan keterlambatan replikasi dapat berujung pada gangguan operasional
  • Risiko terkonsentrasi pada operasi yang memicu pemindaian penuh dan lock panjang, seperti menambahkan DEFAULT dan NOT NULL sekaligus, membuat indeks tanpa CONCURRENTLY, langsung menghapus kolom, mengubah tipe secara tidak aman, dan menambahkan foreign key tanpa validasi
  • Sejak PostgreSQL 11, biaya beberapa penambahan kolom telah berkurang, tetapi untuk indeks tetap diperlukan prosedur yang mengurangi dampak operasional seperti CREATE INDEX CONCURRENTLY, dan untuk foreign key seperti NOT VALID lalu VALIDATE CONSTRAINT
  • Perubahan skala besar sebaiknya dipecah menjadi batch kecil, sambil memeriksa replika baca, lag replikasi, objek dependensi, dan apakah instance aplikasi lama masih mereferensikan kolom tersebut
  • Lakukan pengujian pada data berskala produksi, dan untuk operasi destruktif gunakan deploy bertahap serta rencana rollback yang telah tervalidasi sebelum melanjutkan

Prasyarat migrasi skema

  • Di sini migrasi DB berarti perubahan skema DB, bukan perpindahan DBMS
  • Perubahan yang dibahas memiliki tiga karakteristik
    • Perubahan yang dikelola versinya, dengan pengenal unik untuk tiap perubahan dan prosedur penerapan otomatis
    • Perubahan yang immutable, yang tidak diubah setelah diterapkan ke produksi dan hanya ditambah dengan perubahan baru
    • Perubahan inkremental, di mana skema database berevolusi secara bertahap
  • Fokusnya adalah use case OLTP seperti aplikasi mobile dan web, di mana eksekusi query lebih dari 1 detik biasanya dianggap terlalu lambat
  • Pada database kecil dan aktivitas rendah, beberapa masalah mungkin tidak tampak jelas, tetapi pada skala sekitar 10TiB dan beban 10⁴~10⁵ transaksi per detik, sebagian besar masalah dapat muncul
  • Database Lab Engine digunakan untuk pengembangan dan pengujian dengan thin clone, dan dapat meng-clone database 10TiB dalam 10 detik untuk memeriksa risiko perubahan skema sebelum deploy
  • GitLab Migration Style Guide adalah referensi yang merangkum pengalaman deploy otomatis berbagai perubahan skema Postgres

Menambahkan kolom dan penulisan ulang tabel

  • Menambahkan kolom dengan DEFAULT dan NOT NULL sekaligus sangat berisiko, terutama pada PostgreSQL versi lama
    • Sebelum PostgreSQL 11, hal ini memerlukan penulisan ulang seluruh tabel
    • Pada tabel besar, proses ini bisa memakan waktu berjam-jam atau berhari-hari, dan selama itu terjadi write lock
  • Contoh berbahaya seperti berikut
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • Prosedur yang lebih aman adalah memisahkan penambahan kolom, pembaruan data, dan penambahan constraint
    • Pertama tambahkan kolom tanpa NOT NULL
    • Jika perlu, perbarui baris yang sudah ada
    • Setelah itu tambahkan constraint NOT NULL
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • Pada PostgreSQL 11 ke atas, menambahkan kolom dengan nilai DEFAULT non-volatile tidak lagi memerlukan penulisan ulang tabel

Pembuatan indeks dan penambahan foreign key

  • Jika membuat indeks tanpa CONCURRENTLY, pembuatan indeks standar akan mengambil exclusive lock pada tabel
    • Semua operasi tulis dan sebagian operasi baca bisa terblokir sampai pembuatan indeks selesai
  • Contoh berbahaya seperti berikut
CREATE INDEX idx_users_email ON users(email);
  • Dalam sistem yang sedang berjalan, lebih aman menggunakan CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY memiliki beberapa batasan
    • Prosesnya lebih lama, tetapi tidak memblokir akses ke tabel
    • Tidak dapat digunakan di dalam transaction block
    • Jika gagal, bisa meninggalkan indeks tidak valid yang perlu dihapus
  • Menambahkan foreign key constraint langsung pada tabel besar akan memindai seluruh tabel untuk memvalidasi data yang sudah ada dan memicu lock yang panjang
  • Prosedur yang lebih aman adalah menambahkan constraint terlebih dahulu dengan NOT VALID, lalu memvalidasinya saat traffic rendah
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

Menghapus kolom dan mengubah tipe

  • Jika kolom langsung dihapus di produksi, error aplikasi bisa terjadi ketika kode aplikasi masih mereferensikan kolom tersebut
  • Penghapusan kolom harus dilakukan bertahap
    • Deploy dulu kode aplikasi yang tidak lagi menggunakan kolom tersebut
    • Tunggu sampai semua instance aplikasi lama telah diganti
    • Hapus kolom dalam migrasi terpisah
  • Mengubah tipe kolom dapat menyebabkan penulisan ulang tabel atau masalah kompatibilitas
    • Ini bisa berujung pada downtime, kehilangan data, atau error aplikasi
  • Contoh yang bermasalah seperti berikut
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • Saat mengubah dari integer ke bigint, diperlukan prosedur multi-tahap menggunakan kolom baru
  • Saat mengurangi panjang varchar, data harus diperiksa lebih dulu dan dipastikan apakah perubahan itu benar-benar diperlukan

Perubahan massal, replikasi, dan objek dependensi

  • Hindari migrasi yang memodifikasi terlalu banyak data dalam satu transaksi
    • Persaingan lock dan penggunaan memori meningkat
    • Waktu pemulihan akan lebih lama jika terjadi masalah
    • Lag replikasi bisa membesar
  • Migrasi data skala besar lebih aman jika dipecah menjadi batch kecil
  • Dampak migrasi terhadap replika baca dan lag replikasi juga harus diperhatikan
    • Migrasi besar dapat menyebabkan lag replikasi yang signifikan
    • Kinerja replika baca bisa terpengaruh
  • Objek yang bergantung pada kolom atau tabel yang diubah juga harus diperiksa
    • Jika objek dependensi seperti view, function, atau trigger terlewat, bisa terjadi kegagalan berantai atau diperlukan intervensi manual tambahan

Pengujian dan rencana rollback

  • Jika migrasi hanya diuji pada dataset pengembangan kecil, akan sulit memahami karakteristik performa pada dataset besar
  • Pengujian harus dilakukan pada clone data berskala produksi, dan alat seperti Database Lab Engine dapat digunakan
  • Jika tidak ada cara untuk membatalkan migrasi saat masalah muncul, isu produksi dapat berubah menjadi downtime berkepanjangan
  • Terutama untuk operasi destruktif, diperlukan rencana rollback yang telah tervalidasi
  • Dasar perubahan skema yang aman adalah sebagai berikut
    • Uji pada data berskala produksi
    • Gunakan pendekatan multi-tahap untuk operasi berisiko
    • Manfaatkan fitur PostgreSQL seperti CONCURRENTLY dan NOT VALID
    • Pantau dampak terhadap performa dan replikasi
    • Selalu siapkan rencana rollback

Bacaan terkait

1 komentar

 
GN⁺ 2024-04-29
Opini Hacker News

  • Saya sangat menyukai Postgres, tetapi sebagian besar isi tulisan ini bisa dihindari dan memang patut diperhatikan. Namun menurut saya hal terburuk dari Postgres adalah manajemen role
    Fiturnya kuat, jadi kalau digunakan dengan baik pasti luar biasa, tetapi proses membuatnya benar-benar berfungsi terasa seperti ilmu hitam. Di berbagai bagian antarmukanya, rasanya seperti mantra-mantra rumit yang tidak jelas apakah akan bekerja sesuai harapan, dan ini cara yang mengerikan untuk mengelola sesuatu sepenting ini
    Manual untuk bagian ini juga tipis, hanya sebatas memberi tahu secara garis besar bagaimana seharusnya bekerja untuk kasus penggunaan yang sempit. Kalau tidak berjalan seperti dugaan, kita harus mencari lewat trial and error apa yang salah, dan cara yang benar tetap tidak terasa jelas. Kalau harus memigrasikan DB dengan hak akses pengguna yang kompleks, benar-benar menyiksa
    Saya merasa perlu meluangkan sekitar sebulan untuk menulis sebuah cookbook. Kalau itu bisa membuat setidaknya satu orang tidak tertidur sambil menangis setelah membacanya, nilainya sudah sepadan

    • Saya setuju bahwa IAM PostgreSQL itu kompleks. Alasannya kompleks adalah karena hierarki objeknya terdiri dari 3 level: Database, Schema, dan Tables, serta ada juga hak akses yang diberikan secara implisit kepada pemilik objek DB
      Untuk melakukan SELECT dari tabel, diperlukan CONNECT pada Database dan USAGE pada Schema, yang diberikan secara implisit kepada pemilik Schema. SELECT pada Table juga diperlukan dan diberikan secara implisit kepada pemilik tabel
      Untuk melihat hak akses, Anda harus memahami entri ACL dalam format grantee=privilege-abbreviation[]/grantor:. Hak akses Database bisa dilihat dengan \l+, hak akses Schema dengan \dn+, dan hak akses Table dengan \dp+
      Daftar hak akses ada di here. Misalnya, user=arwdDxt/postgres berarti role postgres telah memberikan semua hak akses kepada pengguna
      Jika kolom grantee pada suatu objek kosong, itu bisa berarti hak akses pemilik default, yaitu semua hak akses, atau bisa juga berarti hak akses untuk role PUBLIC, yaitu semua role yang ada. Contohnya =r/postgres
      Kalau menggunakan Schema public, ini makin membingungkan. Karena Schema memiliki hak akses CREATE, jika Anda membuat tabel dengan pengguna yang sama yang membaca data, hak akses pemilik otomatis melekat dan tabel itu bisa langsung dibaca
    • Dokumentasi postgREST yang bergantung pada role untuk autentikasi juga tampaknya tidak begitu rinci: https://postgrest.org/en/v12/explanations/db_authz.html
      Kalau Anda serius menulis cookbook tentang role Postgres dan membuka sesuatu seperti Kickstarter, saya mungkin akan menjadi salah satu pendukung pertama
    • Saya setuju dengan ungkapan “membuatnya bekerja terasa seperti ilmu hitam”. Tahun lalu saya mengimplementasikan server postgREST sederhana dengan row-level security, dan jalan untuk sampai ke sana cukup sulit
      Namun setelah akhirnya berjalan, rasanya benar-benar seperti sihir, dan mekanisme terkaitnya sendiri ternyata cukup sederhana
    • Kalau ada tulisan seperti itu, saya rasa saya akan membacanya. Manajemen role banyak melibatkan tebak-tebakan, dan akibatnya role terlalu sering diberi hak akses berlebihan
    • Tolong benar-benar tulis itu. Untuk konten setingkat itu, saya rela membayar sekitar 20 dolar

  • Jika menjalankan migrasi Schema di lingkungan produksi, Anda harus memakai lock_timeout
    Perubahan yang tampak tidak berbahaya seperti menghapus tabel yang memiliki foreign key atau menghapus foreign key, yang dalam pengujian hampir selesai seketika, tetap bisa mengalami konflik lock di DB produksi dengan trafik tinggi karena transaksi yang sudah ada atau autovacuum
    ALTER itu akan menahan lock ACCESS EXCLUSIVE sambil menunggu lock dari transaksi pertama, dan akibatnya semua query ke tabel yang terkunci akan terblokir
    Jika mengoperasikan Postgres dalam skala besar, konflik seperti ini hanya soal waktu. Dengan mengatur lock_timeout, alih-alih menunggu sambil memblokir semua query lain, migrasi akan gagal setelah batas waktu terlewati

    • statement_timeout juga mencakup waktu menunggu lock, sehingga lebih baik untuk memperkirakan dampaknya pada tabel yang sibuk
      Jika batas waktunya ditetapkan 5 detik, Anda tahu total downtime maksimal 5 detik, dan setelah itu transaksi akan berlanjut. Kalau hanya memakai lock_timeout, Anda tidak bisa mengendalikan berapa lama pekerjaan berlangsung setelah lock didapat, dan karena trafik bersamaan, bisa cepat atau lambat
    • Bergantung pada versi Postgres, apakah query DML tertentu mengambil lock eksklusif atau tidak bisa cukup berbeda
      Saya penasaran apakah ada cara bagus untuk menganalisis query dan memberi tahu jenis lock apa yang akan diambil. Saat tidak yakin, saya selama ini selalu membaca ulang dokumentasi
    • Saran yang bagus. Namun secara teknis, setahu saya bukan karena ACCESS EXCLUSIVE lock sudah diperoleh lalu menunggu, melainkan karena menunggu akibat antrean lock
      ALTER berada dalam kondisi menunggu lock yang lebih rendah dari ACCESS EXCLUSIVE dilepas
    • Kalau begitu, ALTER bisa saja tidak pernah berjalan. Itu bisa terjadi jika tabel tersebut memiliki trafik yang cukup
      Dalam kasus seperti ini, jika aplikasinya bisa pulih, menurut saya yang terbaik adalah membunuh query lain yang sedang berjalan dan memblokir ALTER

  • Saya merujuk panduan Safe Migrations in Ecto dari Fly.io beberapa kali dalam seminggu. Ecto adalah adapter DB untuk Elixir
    Ini referensi yang sangat berguna untuk cepat memeriksa apakah migrasi default sudah cukup, atau perlu prosedur yang lebih kompleks
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • Saat masih pemula, hal yang paling mengejutkan bagi saya tentang indeks Postgres adalah bahwa indeks UNIQUE dapat memengaruhi hasil kueri bersamaan karena penguncian tambahan
    Kueri seperti INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo); jika dijalankan bersamaan dalam mode default dapat memasukkan nilai bar yang duplikat. Itu karena satu transaksi mungkin tidak melihat nilai maksimum baru yang dibuat oleh transaksi lain
    Jika menambahkan indeks UNIQUE, rasanya transaksi yang “kalah” akan mendapat error constraint, tetapi kenyataannya kedua transaksi sama-sama berhasil dan kondisi balapan juga hilang

    • Itu sebenarnya tidak benar. Subtransaksi yang kalah dalam persaingan indeks akan dibatalkan
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • Jika maksudnya meskipun ada indeks UNIQUE kedua penyisipan sama-sama berhasil dan akhirnya masuk nilai duplikat, kalau benar itu adalah bug
    • Kalau bukan salah paham, ini bisa dilakukan tanpa downtime dengan membuat indeks biasa secara CONCURRENTLY lalu membuat constraint UNIQUE yang belum diperiksa
      Constraint itu hanya berlaku untuk INSERT/UPDATE baru. Setelah itu, jika menjalankan VALIDATE pada constraint tersebut, constraint itu menjadi constraint UNIQUE penuh
    • Kalau ini terasa mengejutkan, menurut saya itu karena terlalu banyak terpapar bahasa imperatif
      Saya setuju ini hal yang umum, tetapi masalahnya lebih pada pengembangan perangkat lunak secara umum daripada Postgres
    • Pada tingkat isolasi yang mana hal itu terjadi?

  • Karena jebakan-jebakan seperti ini, saya membuat Reshape [0] dengan tujuan mengotomatiskan migrasi schema tanpa downtime
    Saya tidak bisa mengatakan bahwa kami menghindari semua masalah, tetapi kami sedang membuat produk baru yang menjadikan itu sebagai tujuan. Jika Anda tertarik pada area ini, terutama Postgres, saya ingin dihubungi: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • Ada kemungkinan ini juga bekerja di crdb?

  • Kesalahan lain yang sering saya lihat adalah menyalin tabel tetapi lupa menyertakan indeks
    CREATE TABLE SELECT * FROM WHERE <> tidak bekerja seperti itu. Orang sering melakukannya saat ingin membuat tabel backup atau melakukan penghapusan massal

    • Kalau kasusnya membuat tabel backup, yaitu ketika hendak melakukan operasi rumit dan ambigu yang bisa langsung rusak dengan cara yang tidak terduga, saya sama sekali tidak peduli pada indeks atau constraint
      Agar tidak perlu memulihkan dari backup DB dan WAL, saya hanya ingin salinan data yang langsung ada, meskipun mungkin tidak akan dipakai. Membuat indeks adalah pemborosan waktu server dan ruang disk
      Kalau situasi memburuk atau benar-benar dibutuhkan, indeks itu bisa dibuat nanti
    • Kalau begitu, bisa sekalian jelaskan cara yang tepat?

  • Bagian “Case 2. Penyalahgunaan IF [NOT] EXISTS” tidak memberikan contoh penyalahgunaan yang bagus
    Dan sebenarnya memang benar menggunakannya seperti itu. Rapi, sederhana, dan tidak ada jebakan tersembunyi. Kalau tabelnya hanya beberapa, alat migrasi schema terasa terlalu berlebihan

    • Jebakannya sederhana: “menutupi masalah dengan logika sehingga menambah risiko keadaan yang aneh”
      Menempelkan plester di atas data buruk tidak menyelesaikan masalah, hanya menyembunyikannya. Bergantung pada jenis masalahnya, hal itu bisa meledak nanti dengan cara yang tidak terduga, pada saat yang paling buruk
      Dalam kasus ini, “data buruk” adalah tabel, kolom, atau view yang seharusnya ada atau tidak ada, tetapi kenyataannya kebalikannya. Mengapa tabel yang belum boleh ada sudah ada? Apakah penghapusannya gagal? Apakah schema tabel yang ada sudah benar? Apakah migrasi yang sama tidak sengaja dijalankan dua kali?
      Setelah setiap migrasi, schema harus berada dalam keadaan yang tepat. Jika ada IF [NOT] EXISTS dalam migrasi, itu berarti schema setelah migrasi sebelumnya tidak ditinggalkan dalam keadaan yang tepat. Tidak yakin terhadap keadaan schema itu bukan hal yang baik
    • Menurut saya artikel itu menjelaskan penyalahgunaannya dengan cukup baik. Intinya, perubahan schema melalui jalur terpisah adalah masalah proses dan workflow, jadi harus diselesaikan secara langsung
      Bagaimana jika kolom pada tabel yang sudah ada berbeda dari yang ingin dibuat oleh migrasi? IF EXISTS akan membuat migrasi dianggap berhasil, tetapi meninggalkan schema dalam keadaan buruk. Dalam kasus seperti ini, lebih baik migrasi gagal dengan cepat

  • Catatan kecil tentang penggunaan int4 sebagai surrogate primary key
    Bukankah yang penting bukan ukuran tabel, melainkan ukuran indeks? Pada ukuran tabel sudah ada header 23 byte dan padding penyelarasan, jadi selisih 4 byte tidak terlalu berpengaruh. Namun, jika lebih banyak indeks bisa dimuat ke memori, itu bisa menguntungkan. Entri indeks memiliki header 8 byte
    Selain itu, 1 miliar baris dalam contoh terlalu dekat dengan nilai maksimum int4, jadi agak mengkhawatirkan
    Meski begitu, artikelnya bagus

    • Benar. Ada ukuran indeks, dan ada juga ukuran disk. Postgres memadatkan baris tabel di disk, tetapi tidak demikian di RAM
      Apakah itu berarti halaman 8KB di disk bisa menjadi lebih besar dari 8KB di RAM?
      Sepertinya ini hanya memengaruhi memori kerja untuk data baris tabel. Tetap penting. Terutama karena Postgres memiliki urutan baris yang acak, sehingga lokalitas untuk kueri rentang sangat buruk. Namun menurut saya itu belum sampai menjadi insight yang menentukan

  • Saya adalah developer yang sejauh ini cukup terlindungi dari masalah terkait DB. Di dalam Django, saya tahu cara membuat migrasi, membuat tabel model, dan melakukan kueri dengan ORM, tetapi banyak hal yang terjadi di dalamnya terasa seperti ilmu hitam
    Sekarang saya memulai perusahaan, saya khawatir akan menghadapi masalah seperti ini dan harus menyelesaikannya sendiri. Bagaimana sebaiknya saya mulai belajar apa yang perlu dilakukan di lingkungan pengembangan?

    • Gagal saja dan belajar dari kesalahan. Atau rekrut developer, lalu gagal bersama dan belajar bersama

  • Saya suka Postgres, tetapi saya benar-benar tidak suka karena tidak ada cara bawaan untuk melakukan update/delete secara batch
    Itu bagian yang paling menyebalkan, dan setiap kali terbentur tembok, saya hampir harus menulis ulang batcher setiap bulan