Bagaimanakah anda membina atau membina semula indeks PostgreSQL secara dalam talian dengan selamat?

1. Soalan

Jadual pesanan terus menerima penulisan sementara pasukan pertanyaan meminta indeks komposit. Satu lagi jadual mempunyai indeks yang perlu dibina semula disebabkan oleh kembung (bloat). Jadual-jadual tersebut bersaiz besar, tetingkap masa adalah di luar waktu puncak, dan operasi baca serta tulis biasa tidak boleh disekat. Berikan pelan dalam talian yang boleh diperhati dan boleh diterbalikkan.

2. Kekangan dan penjelasan

• Sahkan versi PostgreSQL, jenis jadual dan indeks, topologi replikasi, ruang simpanan cakera tambahan, dan puncak penulisan.
• Bezakan CREATE INDEX, CREATE INDEX CONCURRENTLY, dan REINDEX CONCURRENTLY.
• Pembinaan serentak mengurangkan impak kunci tulis (write-lock) tetapi melakukan imbasan lebih daripada sekali, menggunakan CPU/IO, dan tidak boleh dijalankan di dalam blok transaksi.
• Tentukan masa pembinaan yang boleh diterima, masa menunggu kunci, kependaman, dan tetingkap pembersihan sebelum pelaksanaan.

3. Idea teras

Pembinaan indeks biasa boleh menyekat penulisan. Pembinaan serentak membenarkan operasi sisip (insert), kemas kini (update), dan padam (delete) diteruskan tetapi mengambil masa yang lebih lama dan menggunakan lebih banyak sumber. Mula-mula anggarkan kos pada persekitaran bayangan (shadow environment) dengan skala yang realistik; dalam pengeluaran, tetapkan lock_timeout, batasi statement_timeout, dan pantau sumber. Selepas penciptaan, sahkan pilihan perancang (planner), laluan undur balik (rollback), dan ketekalan replika; arahan DDL yang berjaya bukanlah pelancaran perniagaan yang berjaya.

4. Aliran rujukan

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()

Utamakan REINDEX CONCURRENTLY untuk pembinaan semula. Jika kegagalan meninggalkan indeks sementara yang tidak sah, kenal pasti dan bersihkannya mengikut dokumentasi sebelum mencuba semula. Skrip penggunaan hendaklah memastikan penamaan, keidempotenan, dan amaran boleh dijejaki; jangan sembunyikan DDL serentak di dalam migrasi transaksi biasa.

5. Kes kegagalan dan pertukaran kompromi (trade-offs)

Pembinaan serentak boleh gagal disebabkan oleh transaksi yang panjang, snapshot yang bercanggah, atau ruang cakera yang tidak mencukupi. Objek yang gagal mungkin kekal tidak sah dan terus menggunakan ruang. Pertumbuhan CPU, IO, dan WAL semasa pembinaan boleh memperlahankan perkhidmatan dan meningkatkan kelengahan replika (replica lag), jadi hadkan kelajuan (throttle) atau jedakannya. Jika kosnya tidak boleh diterima, optimumkan pertanyaan, lakukan pemetakan (partition) jadual, atau gunakan alat migrasi dalam talian, tetapi tetap sahkan picu (triggers), pengisian semula (backfill), penukaran (cutover), dan tingkah laku undur baliknya.

6. Pengesahan dan kebolehperhatian

• Rekodkan keadaan indeks, saiz, tempoh pembinaan, masa menunggu kunci, WAL, CPU/IO, dan kelengahan replika.
• Bandingkan pelan, baris yang diimbas, kependaman p95/p99, dan daya pemprosesan penulisan untuk pertanyaan yang representatif.
• Periksa transaksi yang panjang, indeks tidak sah, indeks pendua, dan kebergantungan kekangan.
• Tunggu sehingga beberapa waktu puncak trafik lengkap berlalu sebelum memadamkan indeks lama, dan simpan skrip pemulihan.

7. Kesilapan biasa

• Menganggap CONCURRENTLY tidak mengenakan sebarang kunci dan mengabaikan masa menunggu kunci yang singkat serta perebutan sumber.
• Meletakkan CREATE INDEX CONCURRENTLY di dalam blok transaksi, yang menyebabkannya gagal serta-merta.
• Hanya menyemak kod pulangan DDL dan bukannya indeks tidak sah, kelengahan replika, dan pelan pertanyaan sebenar.
• Membina semula jadual yang sangat besar tanpa belanjawan cakera, WAL, dan transaksi panjang.

8. Perkara pemarkahan temu duga

Membezakan semantik DDL serentak

Calon menerangkan kunci, pas imbasan, sekatan transaksi, dan kos sumber untuk operasi cipta atau bina semula biasa dan serentak.

Merancang pemeriksaan pra-pelancaran pengeluaran

Calon memeriksa versi, cakera, transaksi yang panjang, topologi replikasi, dan bentuk pertanyaan, kemudian membuat anggaran dengan volum data yang realistik.

Mereka bentuk pemulihan kegagalan

Calon mengendalikan indeks tidak sah, tamat masa, kehabisan cakera, dan kelengahan replika, dengan langkah-langkah pembersihan, percubaan semula, dan undur balik.

Mengesahkan dengan metrik perniagaan

Calon membandingkan pelan, p95/p99, kependaman penulisan, WAL, masa menunggu kunci, dan kelengahan replika dan bukannya hanya mempercayai kod pulangan DDL.