Bagaimana cara membangun atau membangun kembali indeks PostgreSQL secara online dengan aman?

1. Pertanyaan

Tabel pesanan terus menerima operasi penulisan sementara tim kueri meminta indeks komposit. Tabel lain memiliki indeks yang perlu dibangun kembali karena bloat. Tabel-tabel tersebut berukuran besar, jendela waktunya di luar jam sibuk (off-peak), dan operasi baca serta tulis biasa tidak boleh terblokir. Berikan rencana online yang dapat diobservasi dan dapat dibatalkan (reversible).

2. Batasan dan klarifikasi

• Konfirmasikan versi PostgreSQL, tipe tabel dan indeks, topologi replikasi, sisa ruang disk, dan puncak penulisan.
• Bedakan antara CREATE INDEX, CREATE INDEX CONCURRENTLY, dan REINDEX CONCURRENTLY.
• Pembangunan konkuren (concurrent builds) mengurangi dampak penguncian tulis tetapi memindai lebih dari sekali, mengonsumsi CPU/IO, dan tidak dapat dijalankan di dalam blok transaksi.
• Tentukan waktu pembangunan, waktu tunggu penguncian (lock wait), latensi, dan jendela pembersihan yang dapat diterima sebelum implementasi.

3. Gagasan utama

Pembangunan indeks biasa dapat memblokir penulisan. Pembangunan konkuren memungkinkan insert, update, dan delete terus berjalan tetapi membutuhkan waktu lebih lama dan mengonsumsi lebih banyak sumber daya. Pertama, perkirakan biaya pada lingkungan bayangan (shadow environment) dengan skala yang realistis; di lingkungan produksi, atur lock_timeout, batasi statement_timeout, dan pantau sumber daya. Setelah pembuatan, verifikasi pilihan planner, jalur rollback, dan konsistensi replika; perintah DDL yang berhasil bukanlah peluncuran bisnis yang berhasil.

4. Alur referensi

preflight:
  verify_version_replicas_disk_and_query_shape()
  estimate_scan_cost_on_shadow_copy()
  reserve_maintenance_window_and_abort_thresholds()

build:
  set lock_timeout = short
  set statement_timeout = bounded
  CREATE INDEX CONCURRENTLY idx_orders_customer_time
    ON orders (customer_id, created_at DESC)

verify:
  inspect_index_state_and_size()
  EXPLAIN (ANALYZE, BUFFERS) representative_queries()
  compare_write_latency_replica_lag_and_error_rate()

Pilih REINDEX CONCURRENTLY untuk rebuild. Jika kegagalan menyisakan indeks sementara yang tidak valid, identifikasi dan bersihkan sesuai dengan dokumentasi sebelum mencoba lagi. Skrip deployment harus membuat penamaan, idempotensi, dan peringatan dapat dilacak; jangan sembunyikan DDL konkuren di dalam migrasi transaksional biasa.

5. Kasus kegagalan dan kompromi (trade-offs)

Pembangunan konkuren dapat gagal karena transaksi yang panjang, snapshot yang berkonflik, atau ruang disk yang tidak mencukupi. Objek yang gagal mungkin tetap tidak valid dan terus mengonsumsi ruang. Pertumbuhan CPU, IO, dan WAL selama pembangunan dapat memperlambat layanan dan meningkatkan latensi replika (replica lag), jadi lakukan pembatasan (throttle) atau jeda. Jika biayanya tidak dapat diterima, optimalkan kueri, partisi tabel, atau gunakan alat migrasi online, tetapi tetap verifikasi perilaku pemicu (trigger), backfill, cutover, dan rollback-nya.

6. Verifikasi dan observabilitas

• Catat status indeks, ukuran, durasi pembangunan, waktu tunggu penguncian, WAL, CPU/IO, dan replika lag.
• Bandingkan rencana eksekusi (plan), baris yang dipindai, latensi p95/p99, dan throughput penulisan untuk kueri-kueri yang representatif.
• Periksa transaksi yang berjalan lama, indeks yang tidak valid, indeks duplikat, dan dependensi batasan (constraint).
• Tunggu melewati beberapa puncak traffic penuh sebelum menghapus indeks lama, dan simpan skrip pemulihan.

7. Kesalahan umum

• Menganggap CONCURRENTLY tidak menggunakan kunci sama sekali dan mengabaikan waktu tunggu penguncian singkat serta perebutan sumber daya (resource contention).
• Menempatkan CREATE INDEX CONCURRENTLY di dalam blok transaksi, yang membuatnya langsung gagal.
• Hanya memeriksa kode kembalian DDL alih-alih indeks yang tidak valid, replika lag, dan rencana kueri yang sebenarnya.
• Membangun kembali tabel yang sangat besar tanpa memperhitungkan anggaran disk, WAL, dan transaksi panjang.

8. Poin penilaian wawancara

Membedakan semantik DDL konkuren

Kandidat menjelaskan penguncian, tahapan pemindaian, pembatasan transaksi, dan biaya sumber daya untuk operasi pembuatan atau pembangunan kembali reguler dan konkuren.

Merencanakan pemeriksaan pra-peluncuran produksi

Kandidat memeriksa versi, disk, transaksi panjang, topologi replikasi, dan pola kueri, lalu membuat estimasi dengan volume data yang realistis.

Merancang pemulihan kegagalan

Kandidat menangani indeks yang tidak valid, timeout, kehabisan ruang disk, dan replika lag, disertai dengan langkah-langkah pembersihan, percobaan ulang, dan rollback.

Memverifikasi dengan metrik bisnis

Kandidat membandingkan rencana eksekusi, p95/p99, latensi penulisan, WAL, waktu tunggu penguncian, dan replika lag alih-alih hanya mengandalkan kode kembalian DDL.