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.

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"

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.

  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;

    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);
  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.

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";

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

    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: