Menemukan dan memperbaiki indeks B-tree yang tidak konsisten

Inkonsistensi dalam indeks database dapat terjadi karena berbagai alasan, termasuk kerusakan software, masalah hardware, atau perubahan perilaku yang mendasar seperti perubahan tata urutan.

Komunitas PostgreSQL telah membangun alat untuk mengidentifikasi dan mengatasi masalah tersebut. Alat ini mencakup alat seperti amcheck, yang direkomendasikan oleh komunitas PostgreSQL untuk mengidentifikasi masalah konsistensi, termasuk masalah yang ditampilkan dalam versi PostgreSQL 14 sebelumnya.

Kami telah menulis playbook ini sebagai referensi untuk AlloyDB untuk PostgreSQL yang mengalami masalah tersebut. Kami harap halaman ini memberikan informasi yang juga dapat membantu pengguna PostgreSQL lainnya dalam mengidentifikasi dan memperbaiki indeks B-tree yang inkonsisten. Tujuan kami adalah terus meningkatkan kualitas dokumen ini sebagai resource untuk komunitas open source yang lebih luas. Jika Anda memiliki masukan, gunakan tombol Kirim masukan di bagian atas dan bawah halaman ini.

Menyelesaikan inkonsistensi indeks melibatkan langkah-langkah berikut:

1. Sebelum memulai.

   Sebelum memulai pengindeksan ulang, Anda harus mencadangkan database, menetapkan izin yang benar, memverifikasi versi klien psql, dan mengaktifkan ekstensi amcheck.

2. Periksa indeks B-tree yang inkonsisten.

   Untuk mengidentifikasi indeks yang inkonsistensinya perlu diperbaiki, Anda harus mengidentifikasi semua indeks B-tree yang inkonsisten serta mengidentifikasi semua pelanggaran kunci utama dan unik.

3. Perbaiki inkonsistensi indeks.

   Mengindeks ulang sebuah indeks dapat memperbaiki semua inkonsistensi. Anda mungkin perlu menyesuaikan setelan memori instance untuk meningkatkan performa.

4. Pantau operasi pengindeksan ulang.

   Sebaiknya pantau progres operasi pengindeksan ulang untuk memastikan bahwa operasi sedang berlangsung dan tidak diblokir.

5. Verifikasi bahwa indeks tersebut konsisten.

   Setelah berhasil mengindeks ulang, sebaiknya Anda memverifikasi bahwa indeks Anda tidak berisi inkonsistensi.

Sebelum memulai

Mencadangkan data cluster AlloyDB

Untuk memastikan tidak ada data yang hilang selama pengindeksan ulang, sebaiknya cadangkan data cluster Anda. Untuk mengetahui informasi selengkapnya, lihat Membuat cadangan sesuai permintaan.

Perhatian: Jika Anda memulihkan cadangan yang diambil sebelum inkonsistensi indeks diperbaiki, tindakan ini dapat menyebabkan inkonsistensi muncul kembali.

Tetapkan izin alloydbsuperuser

Untuk menyelesaikan langkah-langkah di halaman ini, Anda harus memiliki izin alloydbsuperuser. Untuk informasi selengkapnya, lihat Peran PostgreSQL bawaan AlloyDB.

Pastikan versi klien psql adalah 9.6 atau yang lebih tinggi

Untuk menyelesaikan langkah-langkah di halaman ini, Anda harus memastikan bahwa versi klien psql Anda adalah 9.6 atau yang lebih tinggi. Jalankan perintah psql --version untuk memverifikasi versi klien psql Anda saat ini.

Mengaktifkan ekstensi amcheck

Untuk memeriksa inkonsistensi indeks, Anda harus mengaktifkan ekstensi amcheck.

Contoh Kode

  CREATE EXTENSION amcheck;
  

Memeriksa indeks B-tree yang tidak konsisten

Bagian berikut menjelaskan cara memeriksa indeks B-tree yang tidak konsisten dengan memeriksa inkonsistensi indeks serta pelanggaran kunci utama dan unik.

Memeriksa inkonsistensi

Jalankan pernyataan berikut di setiap database Anda untuk memeriksa inkonsistensi di semua indeks B-tree:

Contoh Kode

DO $$
DECLARE
  r RECORD;
  version varchar(100);
BEGIN
  RAISE NOTICE 'Started amcheck on database: %', current_database();
  SHOW server_version into version;
  SELECT split_part(version, '.', 1) into version;
  FOR r IN
    SELECT c.oid, c.oid::regclass relname, i.indisunique
      FROM pg_index i
      JOIN pg_opclass op ON i.indclass[0] = op.oid
      JOIN pg_am am ON op.opcmethod = am.oid
      JOIN pg_class c ON i.indexrelid = c.oid
      JOIN pg_namespace n ON c.relnamespace = n.oid
     WHERE am.amname = 'btree'
       AND c.relpersistence != 't'
       AND c.relkind = 'i'
       AND i.indisready AND i.indisvalid LOOP
    BEGIN
      RAISE NOTICE 'Checking index %:', r.relname;
      PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
    EXCEPTION
      WHEN undefined_function THEN
        RAISE EXCEPTION 'Failed to find the amcheck extension';
      WHEN OTHERS THEN
        RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
        RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
        END;
  END LOOP;
  RAISE NOTICE 'Finished amcheck on database: %', current_database();
END $$;

Anda akan melihat output yang mirip dengan hal berikut ini:

Contoh Kode

  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"

Peringatan: Jika output Anda tidak berisi pemberitahuan PERINGATAN, pelanggaran unik atau kunci mungkin masih ada. Untuk memeriksa pelanggaran, lakukan prosedur dalam Mengidentifikasi dan memperbaiki pelanggaran kunci unik dan utama.

Untuk mengetahui informasi selengkapnya tentang cara melihat log, lihat Melihat log menggunakan Logs Explorer.

Mengidentifikasi dan memperbaiki pelanggaran kunci utama dan unik

Bagian ini menjelaskan cara memeriksa indeks Anda untuk menemukan pelanggaran kunci utama dan unik, dan jika ada, cara memperbaikinya.

Mengidentifikasi pelanggaran kunci unik

Pelanggaran kunci unik harus diperbaiki sebelum Anda mengindeks ulang indeks. Untuk memeriksa semua pelanggaran kunci unik, jalankan perintah berikut di setiap database:

Contoh Kode

WITH q AS (
    /* this gets info for all UNIQUE indexes */
    SELECT indexrelid::regclass as idxname,
           indrelid::regclass as tblname,
           indcollation,
           pg_get_indexdef(indexrelid),
           format('(%s)',(select string_agg(quote_ident(attname), ', ')
              from pg_attribute a
              join unnest(indkey) ia(nr) on ia.nr = a.attnum
             where attrelid = indrelid)) as idxfields,
           COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
      FROM pg_index
     WHERE indisunique
     /* next line excludes indexes not affected by collation changes */
       AND trim(replace(indcollation::text, '0', '')) != ''
)
SELECT
 /* the format constructs the query to execute for each index */
 format(
$sql$
DO $$ BEGIN RAISE NOTICE 'checking index %3$I%2$I on table %1$I %4$I'; END;$$;
SELECT this,
       prev,
       /* we detect both reversed ordering or just not unique */
       (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
  FROM (SELECT %2$s AS this,
               lag(%2$s) OVER (ORDER BY %2$s) AS prev
          FROM %1$s %4$s
        ) s
 WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL;  /* change to just '<' if looking for reverse order in index */
$sql$, tblname, idxfields, idxname, whereclause
)
  FROM q
-- LIMIT 20 /* may use limit for testing */
-- the next line tells psql to executes this query and then execute each returned line separately
\gexec

Output skrip mirip dengan berikut ini:

Output

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE

Dalam output ini, header tabel NOTICE menunjukkan indeks, kolom, dan tabel untuk nilai yang ditampilkan di bawahnya. Jika output Anda berisi baris yang menampilkan DUPLICATE atau BACKWARDS, hal ini menunjukkan kerusakan dalam indeks dan mungkin perlu diperbaiki. Baris dengan BACKWARDS menunjukkan kemungkinan nilai duplikat yang mungkin disembunyikan. Jika Anda melihat salah satu entri ini dalam tabel, lihat Memperbaiki pelanggaran kunci duplikat.

Memperbaiki pelanggaran kunci duplikat

Jika Anda telah mengidentifikasi indeks unik duplikat atau jika operasi pengindeksan ulang gagal karena error pelanggaran kunci duplikat, selesaikan langkah-langkah berikut untuk menemukan dan menghapus kunci duplikat.

Catatan: Anda harus mengulangi langkah-langkah berikut untuk setiap indeks yang memiliki pelanggaran kunci.

1. Ekstrak key_columns dari header tabel NOTICE, seperti yang ditampilkan dalam contoh output sebelumnya. Dalam contoh berikut, kolom kuncinya adalah email.

   Output

     NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

   Gunakan nilai ini di KEY_COLUMNS dalam kueri di langkah 3.

2. Temukan skema untuk tabel Anda. Gunakan psql untuk terhubung ke database Anda dan jalankan perintah berikut:

   Contoh Kode

   \dt TABLE_NAME

   Nilai di kolom schema adalah nilai yang Anda gunakan untuk SCHEMA_NAME dalam kueri di langkah 3.

   Misalnya, untuk kueri berikut:

    \dt games
    

   Outputnya mirip dengan yang berikut ini:

    List of relations
    Schema  | Name  | Type  | Owner
    --------+-------+-------+----------
    public  | games | table | postgres
    (1 row)
    

3. Jalankan pernyataan berikut untuk memaksa pemindaian tabel lengkap dan mendapatkan kunci duplikat.

   Contoh Kode

   SET enable_indexscan = off;
   SET enable_bitmapscan = off;
   SET enable_indexonlyscan = off;
   
   SELECT KEY_COLUMNS, count(*)
     FROM SCHEMA_NAME.TABLE_NAME
   GROUP BY KEY_COLUMNS
   HAVING count(*) > 1;

   Catatan: Jika database Anda berukuran besar, pernyataan ini mungkin akan memerlukan waktu lama untuk diselesaikan.

   Dalam pernyataan di atas, KEY_COLUMNS adalah satu atau beberapa kolom yang dicakup oleh kunci utama atau indeks unik dalam tabel yang Anda periksa. Kunci unik ini teridentifikasi saat Anda memeriksa pelanggaran kunci unik. Pernyataan ini menampilkan kunci duplikat dan jumlah duplikat untuk setiap kunci.

   Misalnya, untuk kueri berikut:

     SELECT name,count(*)
       FROM public.TEST_NAMES
     GROUP BY name
     HAVING count(*) > 1;
     

   Outputnya mirip dengan yang berikut ini:

     name                | count
     --------------------+-------
     Johnny              |     2
     Peter               |     2
   (2 rows)

   Dalam hal ini, lanjutkan ke langkah berikutnya untuk menghapus kunci duplikat.

   Jika salah satu kolom di KEY_COLUMNS bernilai null, Anda dapat mengabaikannya karena batasan unik tidak berlaku untuk kolom NULL.

   Jika tidak ada kunci duplikat yang ditemukan, Anda dapat beralih ke Memperbaiki indeks yang inkonsisten.

4. Opsional, tetapi direkomendasikan: Buat cadangan untuk data yang berisi kunci duplikat. Jalankan pernyataan berikut untuk membuat data cadangan:

   Contoh Kode

   CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
   AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
   WHERE (KEY_COLUMNS)
   IN (KEY_VALUES);

   Dalam pernyataan ini, KEY_VALUES adalah daftar nilai yang disalin dari hasil langkah sebelumnya. Contoh:

   Contoh Kode

     CREATE TABLE public.TEST_NAMES_bak
     AS SELECT * FROM public.TEST_NAMES
     WHERE (name) IN (('Johnny'),('Peter'))
     

   Untuk jumlah baris yang banyak, akan lebih mudah untuk mengganti ((KEY_VALUES )) di dalamIN dengan pernyataanSELECT dari langkah 2 tanpa parameter count. Misalnya:

   Contoh Kode

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ( SELECT (KEY_COLUMNS)
     FROM SCHEMA_NAME.TABLE_NAME
     GROUP BY (KEY_COLUMNS)
     HAVING count(*) > 1);

5. Tambahkan peran replikasi ke pengguna untuk menonaktifkan pemicu:

   Contoh Kode

   ALTER USER CURRENT_USER with REPLICATION;
   SET session_replication_role = replica;

6. Jalankan pernyataan berikut untuk menghapus kunci duplikat:

   Contoh Kode

   BEGIN;
   
   DELETE FROM  SCHEMA_NAME.TABLE_NAME a
         USING  (
                 SELECT   min(ctid) AS ctid,
                         KEY_COLUMNS
                 FROM     SCHEMA_NAME.TABLE_NAME
                 GROUP BY KEY_COLUMNS
                         HAVING count(*) > 1 ) b
         WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
         AND   a.ctid <> b.ctid;
     

   Misalnya, untuk multi-kolom KEY_COLUMNS:

   Contoh Kode

     DELETE FROM public.test_random a
         USING (
                SELECT min(ctid) AS ctid,
                day, rnum
         FROM public.test_random
         GROUP BY day, rnum
                HAVING count(*) > 1 ) b
         WHERE a.day=b.day and a.rnum = b.rnum
         AND a.ctid <> b.ctid;

   Dengan day dan rnum adalah KEY_COLUMNS.

   Menjalankan pernyataan ini akan mempertahankan satu baris dan menghapus baris lainnya untuk setiap kumpulan baris duplikat. Jika Anda ingin mengontrol versi baris yang akan dihapus, jalankan filter berikut pada pernyataan hapus:

   Contoh Kode

   DELETE FROM  SCHEMA_NAME.TABLE_NAME
   WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);

   Peringatan: Melakukan transaksi ini akan mengubah database Anda dan menghapus kunci duplikat. Pastikan Anda tidak menghapus data yang masih diperlukan. Pemulihan dari cadangan dapat memulihkan indeks yang tidak konsisten.

7. Selesaikan langkah-langkah berikut untuk memeriksa apakah perintah DELETE menampilkan jumlah baris yang diharapkan tanpa error:

   1. Jalankan pernyataan berikut untuk mengidentifikasi baris tempat tabel diubah:

      Contoh Kode

      SELECT schemaname, relname, n_tup_del, n_tup_upd
        FROM pg_stat_xact_all_tables
      WHERE n_tup_del+n_tup_upd > 0;

   2. Jika semua baris sudah benar, commit transaksi DELETE:

      Contoh Kode

      END;

   3. Jika terjadi error, roll back perubahan untuk memperbaiki error:

      Contoh Kode

      ROLLBACK;

8. Setelah kunci duplikat dihapus, Anda dapat mengindeks ulang indeks.

Memperbaiki indeks yang inkonsisten

Bagian berikut menjelaskan cara memperbaiki inkonsistensi indeks dalam instance Anda.

Catatan: Jika indeks Anda memiliki pelanggaran kunci utama dan unik, Anda harus memperbaikinya sebelum pengindeksan ulang.

Bergantung pada cara database dikonfigurasi, Anda mungkin perlu melakukan hal berikut untuk setiap indeks yang diidentifikasi pada langkah sebelumnya:

1. Bersiaplah untuk mengindeks ulang indeks.

2. Indeks ulang indeks.

3. Jika operasi indeks ulang gagal karena pelanggaran kunci asing, Anda harus menemukan dan memperbaiki pelanggaran ini.

4. Jalankan lagi operasi indeks ulang.

Bersiap mengindeks ulang indeks

Menemukan ukuran indeks

Mengindeks database yang lebih besar memerlukan waktu yang lebih lama daripada database yang lebih kecil. Untuk meningkatkan kecepatan operasi indeks dan mengindeks ulang database yang lebih besar, Anda dapat mengalokasikan lebih banyak memori dan daya CPU ke operasi ini. Langkah ini penting dalam merencanakan operasi indeks ulang. Setelah mengetahui ukuran indeks, Anda dapat menetapkan ukuran memori yang digunakan oleh operasi indeks ulang dan menetapkan jumlah worker paralel.

Jalankan pernyataan berikut untuk menemukan ukuran indeks, dalam kilobyte, dari indeks yang ingin Anda perbaiki:

Contoh Kode

SELECT i.relname                                      AS index_name,
       pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
FROM   pg_index x
       JOIN pg_class i
         ON i.oid = x.indexrelid
WHERE  i.relname = 'INDEX_NAME';

Output pernyataan ini mirip dengan hasil berikut:

Output

 index_name | index_size
------------+------------
 my_index   | 16 kB
(1 row)

Menetapkan ukuran memori yang akan digunakan untuk pengindeksan ulang

Berdasarkan ukuran indeks Anda seperti yang ditentukan di bagian sebelumnya, penting untuk menetapkan nilai yang sesuai untuk parameter konfigurasi maintenance_work_mem. Parameter ini menentukan jumlah memori yang akan digunakan untuk operasi pengindeksan ulang. Misalnya, jika ukuran indeks Anda lebih besar dari 15 GB, sebaiknya sesuaikan memori pemeliharaan.

Contoh berikut menunjukkan cara menetapkan maintenance_work_mem:

Contoh Kode

SET maintenance_work_mem TO "1GB";

Catatan: Nilai maintenance_work_mem ditetapkan untuk setiap sesi. Anda tidak perlu memulai ulang instance setelah mengubah nilainya. Namun, jika Anda terhubung lagi ke database dalam sesi baru, nilai dari setelan sebelumnya tidak akan disimpan.

Mengindeks database yang lebih besar memerlukan waktu yang lebih lama daripada database yang lebih kecil. Untuk meningkatkan kecepatan operasi indeks dan pengindeksan ulang, sebaiknya tetapkan maintenance_work_mem ke minimal 2% dari memori instance untuk instance dengan memori 4 GB atau lebih selama operasi pengindeksan ulang ini.

Tetapkan jumlah worker paralel

Anda dapat meningkatkan jumlah pekerja paralel untuk pengindeksan ulang dengan menetapkan parameter konfigurasi max_parallel_maintenance_workers di database. Nilai default parameter ini adalah 2, tetapi dapat ditetapkan ke nilai yang lebih tinggi untuk meningkatkan jumlah pekerja pengindeksan ulang. Untuk instance dengan 8 inti vCPU atau lebih, sebaiknya tetapkan nilai flag max_parallel_maintenance_workers ke 4.

Berikut ini cara mengidentifikasi nilai yang ditetapkan untuk parameter ini:

Contoh Kode

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

Parameter max_parallel_maintenance_workers adalah subset dari max_worker_processes dan dibatasi oleh max_parallel_workers. Jika Anda memerlukan lebih banyak pekerja paralel, tingkatkan nilai max_worker_processes dan max_parallel_workers.

Contoh berikut menunjukkan cara menetapkan max_parallel_maintenance_workers:

Contoh Kode

SET max_parallel_maintenance_workers TO 4;

Parameter max_parallel_maintenance_workers tidak menjamin alokasi pekerja. Untuk mengonfirmasi bahwa Anda memiliki beberapa pekerja paralel yang dimulai oleh pengindeksan ulang, jalankan kueri berikut dari sesi lain setelah Anda memulai pengindeksan ulang:

Contoh Kode

SELECT
leader.leader_pid,leader.pid "worker_pid",leader.query
FROM
pg_stat_activity leader, pg_stat_activity worker
WHERE leader.leader_pid = worker.pid;

Mengindeks ulang indeks

Anda dapat mengindeks ulang indeks tanpa memblokir workload produksi menggunakan utilitas pg_repack. Utilitas ini mengotomatiskan dan menyederhanakan proses pengindeksan ulang secara konkurensi, sehingga Anda dapat mengindeks ulang tanpa periode nonaktif. Untuk prosedur ini, gunakan pg_repack versi 1.4.7.

Selesaikan langkah-langkah berikut untuk mengindeks ulang indeks menggunakan pg_repack:

1. Download, kompilasi, dan instal utilitas pg_repack dari halaman pg_repack.

2. Buat ekstensi pg_repack:

   Contoh Kode

   CREATE EXTENSION pg_repack;

3. Jalankan perintah berikut untuk mengindeks ulang indeks secara konkurensi:

   Contoh Kode

     pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600

   Catatan: pg_repack tidak memungkinkan Anda mengindeks ulang indeks pada tampilan terwujud.

   Perintah ini memiliki output yang mirip dengan hasil berikut:

   Output

   INFO: repacking index "public.t_i_key"

   Jika terjadi error saat menjalankan pg_repack, Anda dapat memperbaiki error tersebut dan mencoba lagi. Setelah memperbaiki semua indeks kunci unik dan indeks kunci utama, Anda harus memeriksa pelanggaran kunci asing dan memperbaiki setiap pelanggaran yang ditemukan.

Menemukan dan memperbaiki pelanggaran kunci asing

Untuk informasi tentang cara menemukan dan memperbaiki pelanggaran kunci asing, lihat Menemukan dan memperbaiki pelanggaran kunci asing.

Memantau operasi pengindeksan ulang

Terkadang, operasi indeks ulang mungkin diblokir oleh sesi lain. Sebaiknya periksa hal ini setiap 4 jam. Jika operasi indeks ulang diblokir, Anda dapat membatalkan sesi pemblokiran sehingga operasi pengindeksan ulang dapat selesai.

Selesaikan langkah-langkah berikut untuk mengidentifikasi sesi tunggu dan pemblokiran, lalu batalkan dalam operasi INDEX:

1. Untuk mengidentifikasi sesi pemblokiran, jalankan kueri berikut:

   Contoh Kode

   SELECT pid,
         usename,
         pg_blocking_pids(pid) AS blocked_by,
         query                 AS blocked_query
   FROM   pg_stat_activity
   WHERE  cardinality(pg_blocking_pids(pid)) > 0;

2. Untuk membatalkan sesi, jalankan kueri berikut menggunakan PID sesi pemblokiran dari kueri sebelumnya:

   Contoh Kode

   SELECT pg_cancel_backend(PID);

Memverifikasi bahwa indeks Anda konsisten

Anda harus terus memeriksa inkonsistensi indeks untuk setiap indeks yang inkonsisten. Setelah memperbaiki semua indeks yang inkonsisten dan pelanggaran kunci instance, Anda dapat memeriksa bahwa tidak ada masalah saat mengikuti langkah-langkah di bagian sebelumnya:

• Memeriksa inkonsistensi indeks

• Memeriksa pelanggaran kunci unik