Perubahan indeks tanpa downtime di PostgreSQL: panduan aman

Mengapa perubahan indeks menyebabkan downtime (dan bagaimana menghindarinya)

Pekerjaan indeks terdengar tidak berbahaya. Anda "hanya" menambahkan struktur bantu. Di PostgreSQL, membangun, menghapus, atau menukar indeks bisa mengambil kunci yang memblok sesi lain. Jika tabel sibuk, penantian itu menumpuk dan aplikasi mulai terasa rusak.

Downtime jarang terlihat seperti banner pemadaman yang rapi. Seringkali muncul sebagai halaman yang menggantung, job background yang tertinggal, dan antrean permintaan yang menunggu database. Seseorang menekan "Search" dan mendapat timeout, sementara alat dukungan dan layar admin tiba-tiba terasa lambat karena kueri sederhana tidak bisa mendapatkan kunci yang mereka butuhkan.

"Jalankan saja malam hari" sering gagal karena dua alasan umum. Banyak sistem tidak pernah benar-benar tenang (pengguna global, job batch, ETL, backup). Dan operasi indeks bisa memakan waktu lebih lama dari yang Anda kira karena mereka membaca banyak data dan bersaing untuk CPU dan disk. Jika jendela waktu tertutup saat build berlangsung, Anda harus memilih menunggu lebih lama atau menghentikan pekerjaan itu.

Perubahan indeks tanpa downtime bukan sulap. Intinya memilih operasi yang paling sedikit memblokir, memasang pengaman (timeout dan pengecekan disk), dan mengawasi database saat berjalan.

Playbook ini fokus pada kebiasaan produksi yang praktis:

• Pilih build indeks concurrent saat baca dan tulis harus terus berjalan.
• Pantau kunci dan progres build sehingga Anda bisa bereaksi lebih awal.
• Miliki jalur rollback jika perubahan menyebabkan regresi atau memakan waktu terlalu lama.

Yang tidak dibahas: teori desain indeks mendalam, tuning kueri umum, atau refaktor skema yang menulis ulang banyak data.

Model kunci sederhana di balik pekerjaan indeks

PostgreSQL menggunakan kunci untuk menjaga data tetap benar ketika banyak sesi menyentuh tabel yang sama. Kunci hanyalah aturan yang menentukan siapa yang boleh membaca atau menulis sebuah objek saat ini, dan siapa yang harus menunggu.

Sebagian besar waktu Anda tidak merasakan kunci karena PostgreSQL bisa menggunakan mode ringan yang memungkinkan kueri normal berjalan. DDL berbeda. Saat Anda membuat atau menghapus indeks, PostgreSQL membutuhkan kontrol yang cukup atas tabel untuk menjaga katalog dan data konsisten. Semakin besar kontrol yang dibutuhkan, semakin banyak sesi lain mungkin terpaksa menunggu.

Membangun indeks vs menggunakan indeks

Menggunakan indeks biasanya murah dari sisi penguncian. SELECT, UPDATE, dan DELETE dapat membaca atau memelihara indeks sementara sesi lain melakukan hal yang sama.

Membangun indeks berbeda. PostgreSQL harus memindai tabel, mengurutkan atau meng-hash kunci, dan menulis struktur baru ke disk. Pekerjaan itu memakan waktu, dan waktu itulah yang mengubah "kunci kecil" menjadi "masalah besar" di produksi.

Apa yang diubah CONCURRENTLY (dan apa yang tidak)

CREATE INDEX biasa mengambil kunci kuat yang memblokir operasi tulis selama proses. CREATE INDEX CONCURRENTLY dirancang agar baca dan tulis normal tetap berjalan saat indeks dibangun.

Tapi "concurrent" bukan berarti "bebas kunci." Anda masih mendapat jendela kunci singkat di awal dan akhir, dan build bisa gagal atau menunggu jika ada yang memegang kunci yang tidak kompatibel.

Hasil yang paling penting:

• Build non-concurrent dapat memblokir insert, update, dan delete pada tabel.
• Build concurrent biasanya mengizinkan baca dan tulis, tetapi bisa diperlambat atau terhenti oleh transaksi yang berjalan lama.
• Langkah penyelesaian masih membutuhkan kunci singkat, jadi sistem yang sangat sibuk bisa melihat penantian singkat.

Pilih pendekatan yang tepat: concurrent atau tidak

Anda punya dua pilihan utama saat mengubah indeks: membangun indeks normal (cepat, tapi memblokir), atau membangunnya dengan CONCURRENTLY (biasanya tidak memblokir traffic aplikasi, tapi lebih lambat dan lebih sensitif terhadap transaksi lama).

Kapan CONCURRENTLY adalah pilihan tepat

Gunakan CREATE INDEX CONCURRENTLY ketika tabel melayani traffic nyata dan Anda tidak bisa menghentikan penulisan. Ini biasanya pilihan yang lebih aman ketika:

• Tabel cukup besar sehingga build normal bisa memakan menit atau jam.
• Tabel memiliki penulisan yang stabil, bukan hanya baca.
• Anda tidak bisa menjadwalkan jendela maintenance yang sebenarnya.
• Anda perlu membangun dulu, memverifikasi, lalu menghapus indeks lama nanti.

Kapan build indeks normal dapat diterima

CREATE INDEX normal bisa baik ketika tabel kecil, traffic rendah, atau Anda punya jendela terkontrol. Biasanya selesai lebih cepat dan lebih sederhana dijalankan.

Pertimbangkan pendekatan normal jika build konsisten cepat di staging dan Anda bisa menghentikan penulisan sementara (bahkan sebentar).

Jika Anda membutuhkan keunikan, putuskan sejak awal. CREATE UNIQUE INDEX CONCURRENTLY bekerja, tapi akan gagal jika nilai duplikat ada. Di banyak sistem produksi, menemukan dan memperbaiki duplikat itu adalah proyek tersendiri.

Pemeriksaan awal sebelum menyentuh produksi

Sebagian besar masalah terjadi sebelum perintah bahkan dimulai. Beberapa pengecekan membantu Anda menghindari dua kejutan besar: blocking tak terduga dan build indeks yang berjalan jauh lebih lama (atau menggunakan lebih banyak ruang) dari yang Anda rencanakan.

1) Pastikan Anda tidak berada di dalam transaksi. CREATE INDEX CONCURRENTLY akan gagal jika Anda menjalankannya setelah BEGIN, dan beberapa alat GUI diam-diam membungkus pernyataan dalam transaksi. Jika ragu, buka sesi baru dan jalankan hanya perintah indeks di sana.

2) Tetapkan ekspektasi waktu dan disk. Build concurrent biasanya lebih lama daripada build normal dan membutuhkan ruang kerja tambahan saat berjalan. Rencanakan untuk indeks baru plus overhead sementara, dan pastikan Anda punya ruang disk bebas yang nyaman.

3) Atur timeout yang sesuai dengan tujuan Anda. Anda ingin build gagal cepat jika tidak bisa mendapatkan kunci, tetapi Anda tidak ingin sesi mati di tengah build karena statement timeout yang agresif.

4) Tangkap baseline. Anda butuh bukti perubahan membantu dan cara cepat mendeteksi regresi. Rekam snapshot sebelum: waktu kueri lambat, EXPLAIN (ANALYZE, BUFFERS) yang representatif, dan gambaran singkat CPU, IO, koneksi, dan ruang disk bebas.

Setelan sesi aman yang banyak tim gunakan sebagai titik awal (sesuaikan dengan aturan Anda):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Langkah demi langkah: buat indeks dengan CONCURRENTLY

Gunakan CREATE INDEX CONCURRENTLY ketika Anda perlu agar traffic aplikasi tetap bergerak dan Anda bisa menerima waktu build yang lebih lama.

Pertama, putuskan dengan jelas apa yang akan Anda bangun:

• Jelaskan urutan kolom (itu penting).
• Pertimbangkan apakah indeks parsial sudah cukup. Jika sebagian besar kueri memfilter baris "active", indeks parsial bisa lebih kecil, lebih cepat, dan lebih murah untuk dipelihara.

Jalankan yang aman seperti ini: tuliskan tujuan dan nama indeks, jalankan build di luar blok transaksi, awasi sampai selesai, lalu verifikasi planner bisa menggunakannya sebelum Anda menghapus apa pun.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Untuk catatan progres (berguna untuk audit), catat waktu mulai, waktu selesai, dan penantian yang Anda lihat. Saat berjalan, Anda bisa query pg_stat_progress_create_index dari sesi lain.

Validasi bukan hanya "indeks ada." Konfirmasi planner bisa memilihnya, lalu pantau waktu kueri nyata setelah deployment. Jika indeks baru tidak digunakan, jangan terburu-buru menghapus yang lama. Perbaiki kueri atau definisi indeks dulu.

Langkah demi langkah: mengganti atau menghapus indeks tanpa memblokir

Polanya paling aman adalah menambah dulu, biarkan traffic memanfaatkan indeks baru, dan baru kemudian hapus indeks lama. Dengan begitu Anda punya fallback yang bekerja.

Menukar indeks lama dengan yang baru (urutan aman)

1. Buat indeks baru dengan CREATE INDEX CONCURRENTLY.

2. Verifikasi indeks digunakan. Periksa EXPLAIN pada kueri lambat yang Anda pedulikan, dan pantau penggunaan indeks dari waktu ke waktu.

3. Hanya setelah itu, drop indeks lama secara concurrent. Jika risikonya tinggi, simpan kedua indeks selama satu siklus bisnis penuh sebelum menghapus apa pun.

Menghapus indeks: kapan CONCURRENTLY bekerja (dan kapan tidak)

Untuk indeks biasa yang Anda buat sendiri, DROP INDEX CONCURRENTLY biasanya pilihan yang tepat. Dua catatan: itu tidak bisa dijalankan di dalam transaction block, dan masih membutuhkan kunci singkat di awal dan akhir, sehingga bisa tertunda oleh transaksi yang berjalan lama.

Jika indeks ada karena PRIMARY KEY atau UNIQUE constraint, Anda biasanya tidak bisa langsung menghapusnya. Anda harus mengubah constraint dengan ALTER TABLE, yang bisa mengambil kunci lebih kuat. Perlakukan itu sebagai operasi maintenance terjadwal yang terpisah.

Mengganti nama indeks untuk kejelasan

Rename (ALTER INDEX ... RENAME TO ...) biasanya cepat, tapi hindari jika tooling atau migrasi merujuk nama indeks. Kebiasaan yang lebih aman adalah memilih nama yang jelas sejak awal.

Jika indeks lama masih diperlukan

Kadang dua pola kueri membutuhkan dua indeks berbeda. Jika kueri penting masih mengandalkan yang lama, pertahankan. Pertimbangkan menyesuaikan indeks baru (urutan kolom, kondisi parsial) daripada memaksa penghapusan.

Pantau kunci dan progres saat indeks dibangun

Bahkan dengan CREATE INDEX CONCURRENTLY, Anda harus mengawasi apa yang terjadi secara real time. Kebanyakan kejutan datang dari dua hal: sesi yang memblokir yang tidak Anda perhatikan, atau transaksi panjang yang membuat build terhenti menunggu.

Menemukan sesi yang memblok (siapa memblok siapa)

Mulailah dengan menemukan sesi yang menunggu kunci:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Jika Anda butuh blocker yang tepat, ikuti blocked_pid ke blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Pantau progres build dan sinyal "stuck"

PostgreSQL mengekspos progres pembuatan indeks. Jika Anda tidak melihat pergerakan untuk waktu lama, cari transaksi panjang (seringkali sesi idle yang memegang snapshot lama).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Juga perhatikan tekanan sistem: IO disk, lag replikasi, dan meningkatnya waktu kueri. Build concurrent lebih ramah untuk uptime, tapi tetap membaca banyak data.

Aturan sederhana yang bekerja baik di produksi:

• Tunggu jika progres bergerak dan dampak ke pengguna rendah.
• Batalkan dan jadwalkan ulang jika build terjebak di belakang transaksi panjang yang tidak bisa Anda akhiri dengan aman.
• Berhenti sementara saat traffic puncak jika IO mengganggu kueri yang berhadapan dengan pelanggan.
• Terminasi hanya sebagai upaya terakhir, dan hanya setelah mengonfirmasi apa yang sedang dilakukan sesi tersebut.

Untuk komunikasi tim, buat pembaruan singkat: waktu mulai, fase saat ini, apa yang diblok (jika ada), dan kapan Anda akan cek lagi.

Rencana rollback: cara mundur dengan aman

Perubahan indeks tetap berisiko rendah jika Anda merencanakan keluarannya sebelum mulai. Rollback paling aman sering bukan undo dramatis. Itu cukup menghentikan pekerjaan baru dan membiarkan indeks lama tetap ada.

Cara umum pekerjaan indeks gagal

Sebagian besar kegagalan produksi bisa diprediksi: build kena timeout, seseorang membatalkannya saat insiden, server kehabisan disk, atau build bersaing dengan traffic normal sehingga latensi yang terlihat pengguna melonjak.

Dengan CREATE INDEX CONCURRENTLY, membatalkan biasanya aman untuk aplikasi karena kueri tetap berjalan. Tradeoff-nya adalah pembersihan: build concurrent yang dibatalkan atau gagal bisa meninggalkan indeks tidak valid.

Aturan aman untuk batal dan pembersihan

Membatalkan build concurrent tidak rollback seperti transaksi biasa. PostgreSQL bisa meninggalkan indeks yang ada tetapi tidak valid untuk planner.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Sebelum menghapus, konfirmasi apa yang Anda lihat:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Jika indisvalid = false, indeks itu tidak digunakan dan aman untuk dihapus.

Checklist rollback praktis saat mengganti indeks yang ada:

• Simpan indeks lama sampai yang baru sepenuhnya dibangun dan valid.
• Jika build baru gagal atau dibatalkan, drop indeks baru yang tidak valid secara concurrent.
• Jika Anda sudah menghapus indeks lama, buat ulang dengan CREATE INDEX CONCURRENTLY untuk mengembalikan keadaan sebelumnya.
• Jika kegagalan disebabkan tekanan disk, kosongkan ruang dulu, lalu coba lagi.
• Jika timeout menyebabkan kegagalan, jadwalkan jendela yang lebih tenang daripada memaksa.

Contoh: Anda memulai indeks baru untuk pencarian admin, berjalan 20 menit, lalu alarm disk berbunyi. Batalkan build, drop indeks tidak valid secara concurrent, dan biarkan indeks lama melayani traffic. Anda bisa coba lagi setelah membebaskan ruang, tanpa outage yang terlihat pengguna.

Kesalahan umum yang menyebabkan outage mengejutkan

Sebagian besar outage seputar indeks bukan karena PostgreSQL "lambat." Mereka terjadi karena satu detail kecil mengubah perubahan yang aman menjadi sesuatu yang memblok.

1) Menempatkan build concurrent di dalam transaksi

CREATE INDEX CONCURRENTLY tidak bisa dijalankan di dalam transaction block. Banyak tool migrasi membungkus setiap perubahan dalam satu transaksi secara default. Hasilnya bisa berupa error keras (kasus terbaik) atau deploy berantakan dengan retry.

Sebelum menjalankan migrasi, pastikan alat Anda bisa menjalankan pernyataan tanpa transaksi luar, atau pisahkan migrasi ke langkah non-transaksional khusus.

2) Menjalankannya saat traffic puncak

Build concurrent mengurangi blocking, tapi tetap menambah beban: baca ekstra, tulis ekstra, dan lebih banyak tekanan pada autovacuum. Memulai build saat jendela deploy ketika traffic melonjak adalah cara umum menciptakan perlambatan yang terasa seperti outage.

Pilih periode tenang dan perlakukan itu seperti maintenance produksi lain.

3) Mengabaikan transaksi yang berjalan lama

Satu transaksi panjang bisa menahan fase pembersihan build concurrent. Indeks mungkin terlihat bergerak, lalu berhenti di dekat akhir sambil menunggu snapshot lama hilang.

Biasakan: cek transaksi yang berjalan lama sebelum mulai, dan lagi jika progres tersendat.

4) Menghapus hal yang salah (atau merusak constraint)

Tim kadang menghapus indeks berdasarkan nama dari ingatan, atau menghapus indeks yang mendukung aturan keunikan. Jika Anda menghapus objek yang salah, Anda bisa kehilangan enforcement (constraint unik) atau langsung menurunkan performa kueri.

Checklist cepat: verifikasi nama indeks di katalog, konfirmasi apakah itu mendukung constraint, cek skema dan tabel dua kali, dan pisahkan "buat baru" dari "hapus lama." Siapkan perintah rollback sebelum mulai.

Contoh realistis: mempercepat pencarian admin

Poin sakit umum adalah pencarian admin yang terasa instan di staging tapi melambat di produksi. Misalnya Anda punya tabel tickets besar (puluhan juta baris) di balik panel admin internal, dan agen sering mencari "open tickets untuk satu pelanggan, terbaru dulu."

Kuerinya seperti ini:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Indeks penuh pada (customer_id, status, created_at) membantu, tapi menambah overhead tulis untuk setiap update tiket, termasuk yang ditutup. Jika kebanyakan baris bukan open, indeks parsial seringkali menjadi kemenangan sederhana:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Garis waktu aman di produksi:

• Preflight: pastikan bentuk kueri stabil dan tabel punya cukup disk bebas untuk build indeks baru.
• Build: jalankan CREATE INDEX CONCURRENTLY di sesi terpisah dengan pengaturan timeout yang jelas.
• Validate: jalankan ANALYZE tickets; dan pastikan planner menggunakan indeks baru.
• Cleanup: setelah yakin, drop indeks lama yang kini redundan dengan DROP INDEX CONCURRENTLY.

Tanda sukses:

• Pencarian admin turun dari detik ke puluhan milidetik untuk pelanggan tipikal.
• Baca dan tulis reguler tetap bekerja selama build.
• CPU dan IO disk naik saat build tapi tetap dalam batas aman Anda.
• Anda bisa menunjukkan angka before/after yang jelas: waktu kueri, baris yang dipindai, dan riwayat kunci.

Checklist cepat dan langkah selanjutnya

Pekerjaan indeks paling aman jika Anda memperlakukannya seperti rilis kecil produksi: siapkan, awasi saat berjalan, lalu verifikasi hasil sebelum membersihkan.

Sebelum mulai:

• Atur timeout supaya kunci tak terduga tidak menggantung selamanya.
• Pastikan cukup ruang disk bebas untuk build indeks baru.
• Cari transaksi yang berjalan lama yang bisa memperlambat build.
• Pilih jendela traffic rendah dan definisikan apa arti "selesai".
• Tuliskan rencana rollback sekarang.

Saat berjalan:

• Awasi blocking dan rantai tunggu kunci.
• Lacak progres build dengan pg_stat_progress_create_index.
• Perhatikan gejala aplikasi: error rate, timeout, dan endpoint lambat yang terkait tabel.
• Siap untuk membatalkan jika wait lock naik atau timeout yang terlihat pengguna melonjak.
• Catat apa yang terjadi: waktu mulai, waktu selesai, dan alarm apa pun.

Setelah selesai, konfirmasi indeks valid, jalankan satu atau dua kueri kunci untuk melihat rencana dan waktu meningkat, dan baru kemudian hapus indeks lama dengan cara non-blocking.

Jika Anda melakukan ini lebih dari sekali, ubah menjadi langkah delivery yang bisa diulang: runbook kecil, latihan di staging dengan data mirip produksi, dan pemilik jelas yang mengawasi build.

Jika Anda membangun alat internal atau panel admin dengan AppMaster (appmaster.io), perlakukan perubahan basis data seperti build indeks sebagai bagian dari checklist rilis yang sama dengan pembaruan backend Anda: terukur, termonitor, dan dengan rollback yang bisa dieksekusi cepat.