Trovare e correggere gli indici incoerenti

Le incoerenze negli indici del database possono verificarsi per una serie di motivi, tra cui difetti del software, problemi hardware o modifiche di comportamento sottostanti, come le modifiche all'ordinamento.

La community di PostgreSQL ha creato strumenti per identificare e risolvere questi problemi. Sono inclusi strumenti come amcheck, consigliato dalla community di PostgreSQL per identificare i problemi di coerenza, inclusi quelli riscontrati nelle versioni precedenti di PostgreSQL 14.

Questo playbook è un riferimento per gli utenti di AlloyDB per PostgreSQL che riscontrano questi problemi di coerenza. Il playbook fornisce informazioni che aiutano gli utenti di PostgreSQL a identificare e correggere gli indici incoerenti.

La risoluzione delle incoerenze di un indice prevede i seguenti passaggi:

  1. Prima di iniziare.

    Prima di iniziare la reindicizzazione, devi eseguire il backup del database, impostare le autorizzazioni corrette, verificare la versione del client psql e attivare l'estensione amcheck.

  2. Verifica la presenza di indici incoerenti.

    Un'istruzione identifica le violazioni delle chiavi univoche e primarie, mentre un'altra istruzione rileva una serie di altre incoerenze.

  3. Correggi le incoerenze dell'indice.

    La reindicizzazione di un indice corregge tutte le sue incoerenze. Potresti dover modificare le impostazioni della memoria dell'istanza per migliorare le prestazioni.

  4. Monitora le operazioni di reindicizzazione.

    Ti consigliamo di monitorare l'avanzamento dell'operazione di reindicizzazione per assicurarti che proceda e non venga bloccata.

  5. Verifica che gli indici siano coerenti.

    Dopo aver reindicizzato l'indice, ti consigliamo di verificare che non contenga incoerenze.

Prima di iniziare

Esegui il backup dei dati del cluster AlloyDB

Per assicurarti che non vengano persi dati durante la reindicizzazione, ti consigliamo di eseguire il backup dei dati del cluster. Per saperne di più, consulta Crea un backup on demand.

Imposta l'autorizzazione alloydbsuperuser

Per completare i passaggi descritti in questa pagina, devi disporre delle autorizzazioni alloydbsuperuser. Per saperne di più, consulta Ruoli PostgreSQL predefiniti di AlloyDB.

Assicurati che la versione del client psql sia 9.6 o successiva

Per completare i passaggi descritti in questa pagina, devi assicurarti che la versione del client psql sia 9.6 o successiva. Esegui il comando psql --version per verificare la versione corrente del client psql.

Abilita l'estensione amcheck

Per verificare la presenza di incoerenze nell'indice, devi abilitare l'estensione amcheck.

Esempio di codice

  CREATE EXTENSION amcheck;

Verifica la presenza di indici incoerenti

Le sezioni seguenti descrivono come verificare la presenza di indici incoerenti controllando le incoerenze di un indice, nonché le violazioni chiave primaria e univoche.

Verifica la presenza di incongruenze

In ogni database, esegui la seguente istruzione per verificare la presenza di incoerenze:

Esempio di codice

DO $$
DECLARE
  r RECORD;
  version varchar(100);
BEGIN
  RAISE NOTICE 'Started relhasindex validation on database: %', current_database();
  FOR r IN
    SELECT indexrelid::regclass relname
      FROM pg_index
    WHERE indrelid NOT IN (SELECT oid FROM pg_class WHERE relhasindex) LOOP
      RAISE LOG 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
      RAISE WARNING 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
  END LOOP;
  RAISE NOTICE 'Finished relhasindex validation on database: %', current_database();

  RAISE NOTICE 'Started b-tree 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 b-tree amcheck on database: %', current_database();
END $$;

Dovresti ricevere un output simile al seguente:

Esempio di codice

  WARNING:  Failed to check index t_i_key: relhasindex is false, want true
  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"

Per saperne di più sulla visualizzazione dei log, consulta Visualizza i log utilizzando Esplora log.

Identifica e correggi le violazioni delle chiavi univoche e primarie

Questa sezione descrive come controllare l'indice per verificare la presenza di violazioni delle chiavi univoche e primarie e, se presenti, come correggerle.

Identifica le violazioni delle chiavi univoche

Le violazioni delle chiavi univoche devono essere corrette prima di reindicizzare un indice. Per verificare la presenza di tutte le violazioni delle chiavi univoche, esegui questo comando in ogni database:

Esempio di codice

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

L'output dello script è simile al seguente:

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

In questo output, l'intestazione della tabella NOTICE mostra l'indice, la colonna e la tabella per i valori visualizzati di seguito. Se l'output contiene righe che mostrano DUPLICATE o BACKWARDS, significa che l'indice è danneggiato e potrebbe essere necessario correggerlo. Le righe con BACKWARDS indicano possibili valori duplicati che potrebbero essere nascosti. Se vedi una di queste voci nella tabella, consulta Correggi le violazioni delle chiavi duplicate.

Correggi le violazioni delle chiavi duplicate

Se hai identificato un indice univoco duplicato o se un'operazione di reindicizzazione non va a buon fine a causa di un errore di violazione della chiave duplicata, completa i seguenti passaggi per trovare e rimuovere le chiavi duplicate.

  1. Estrai key_columns dall'intestazione della tabella NOTICE, come mostrato nell'output di esempio precedente. Nell'esempio seguente, la colonna chiave è email.

    Output

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

    Utilizza questi valori in KEY_COLUMNS nella query del passaggio 3.

  2. Trova lo schema della tabella. Utilizza psql per connetterti al database ed esegui questo comando:

    Esempio di codice

    \dt TABLE_NAME
     Il valore nella colonna "schema" è il valore che utilizzi per SCHEMA_NAME nella query nel passaggio 3. Ad esempio, per la seguente query: 
     \dt games
    L'output è simile al seguente: 
     List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)

  3. Esegui le seguenti istruzioni per forzare una scansione completa della tabella e ottenere chiavi duplicate.

    Esempio di codice

    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;

    Nell'istruzione precedente, KEY_COLUMNS sono una o più colonne coperte dall'indice univoco o dalla chiave primaria nella tabella che stai controllando. Questi sono stati identificati quando hai verificato la presenza di violazioni delle chiavi univoche. L'istruzione restituisce le chiavi duplicate e un conteggio dei duplicati per ciascuna.

    Ad esempio, per la seguente query:

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

    L'output è simile al seguente:

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

    In questo caso, vai al passaggio successivo per rimuovere le chiavi duplicate.

    Se una delle colonne in KEY_COLUMNS è null, puoi ignorarla perché i vincoli univoci non si applicano alle colonne NULL.

    Se non vengono trovate chiavi duplicate, puoi passare a Correggi gli indici incoerenti.

  4. (Facoltativo, ma consigliato) Crea un backup per i record contenenti chiavi duplicate. Esegui questa istruzione per creare i record di backup:

    Esempio di codice

    CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
WHERE (KEY_COLUMNS)
IN (KEY_VALUES);
    In questa istruzione, KEY_VALUES è un elenco di valori copiati dal risultato del passaggio precedente. Ad esempio:

    Esempio di codice

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

    Per un numero elevato di righe, è più facile sostituire il parametro ((KEY_VALUES)) nell'istruzione IN con l'istruzione SELECT del passaggio 2 senza il parametro count. Ad esempio:

    Esempio di codice

      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. Aggiungi un ruolo di replica all'utente per disattivare i trigger:

    Esempio di codice

    ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;

  6. Esegui la seguente istruzione per eliminare le chiavi duplicate:

    Esempio di codice

    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;

    Ad esempio, per KEY_COLUMNS su più colonne:

    Esempio di codice

      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;
     Dove day e rnum sono KEY_COLUMNS.

    L'esecuzione di questa istruzione mantiene una riga ed elimina le altre per ogni insieme di righe duplicate. Se vuoi controllare quale versione della riga viene eliminata, esegui il seguente filtro nell'istruzione DELETE:

    Esempio di codice

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

  7. Completa i seguenti passaggi per verificare che il comando DELETE abbia restituito il numero previsto di righe senza errori:

    1. Esegui la seguente istruzione per identificare le righe in cui le tabelle sono state modificate:

      Esempio di codice

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

    2. Se tutte le righe sono corrette, esegui il commit della transazione DELETE:

      Esempio di codice

      END;

    3. Se sono presenti errori, esegui il rollback delle modifiche per correggerli:

      Esempio di codice

      ROLLBACK;

  8. Dopo aver eliminato le chiavi duplicate, puoi reindicizzare l'indice.

Correggi gli indici incoerenti

Le sezioni seguenti descrivono come correggere le incoerenze dell'indice rilevate nell'istanza.

A seconda di come è configurato il database, potresti dover eseguire le seguenti operazioni per ogni indice identificato nei passaggi precedenti:

  1. Preparati a reindicizzare l'indice.

  2. Reindicizza l'indice.

  3. Se l'operazione di reindicizzazione non va a buon fine a causa di violazioni chiave esterna, devi trovare e correggere queste violazioni.

  4. Esegui di nuovo l'operazione di reindicizzazione.

Preparati a reindicizzare l'indice

Trova le dimensioni dell'indice

L'indicizzazione di database di grandi dimensioni richiede più tempo rispetto a quella di database di piccole dimensioni. Per migliorare la velocità delle operazioni di indicizzazione e reindicizzazione dei database più grandi, puoi allocare più memoria e potenza della CPU a queste operazioni. Questo è un passaggio importante nella pianificazione dell'operazione di reindicizzazione. Una volta note le dimensioni dell'indice, puoi impostare le dimensioni della memoria utilizzata dall'operazione di reindicizzazione e impostare il numero di worker paralleli.

Esegui la seguente istruzione per trovare le dimensioni dell'indice, in kilobyte, che vuoi correggere:

Esempio di codice

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

L'output di questa istruzione è simile al seguente:

Output

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

Imposta la dimensione della memoria da utilizzare per la reindicizzazione

In base alle dimensioni dell'indice determinate nella sezione precedente, è importante impostare il valore appropriato per il parametro di configurazione maintenance_work_mem. Questo parametro specifica la quantità di memoria da utilizzare per l'operazione di reindicizzazione. Ad esempio, se la dimensione dell'indice è superiore a 15 GB, ti consigliamo di modificare la memoria di manutenzione.

L'esempio seguente mostra come impostare maintenance_work_mem:

Esempio di codice

SET maintenance_work_mem TO '1GB';

L'indicizzazione di database di grandi dimensioni richiede più tempo rispetto a quella di database di piccole dimensioni. Per migliorare la velocità delle operazioni di indicizzazione e reindicizzazione, è consigliabile impostare maintenance_work_mem su almeno il 2% della memoria dell'istanza per le istanze con almeno 4 GB di memoria durante questa operazione di reindicizzazione.

Imposta il numero di worker paralleli

Puoi aumentare il numero di worker paralleli per la reindicizzazione impostando il parametro di configurazione max_parallel_maintenance_workers nel database. Il valore predefinito di questo parametro è 2, ma può essere impostato su un valore più alto per aumentare il numero di worker per la reindicizzazione. Per le istanze con 8 o più core vCPU, consigliamo di impostare il valore del flag max_parallel_maintenance_workers su 4.

Di seguito viene mostrato come identificare i valori impostati per questi parametri:

Esempio di codice

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

Il parametro max_parallel_maintenance_workers è un sottoinsieme di max_worker_processes ed è limitato da max_parallel_workers. Se hai bisogno di più worker paralleli, aumenta il valore di max_worker_processes e max_parallel_workers.

L'esempio seguente mostra come impostare max_parallel_maintenance_workers:

Esempio di codice

SET max_parallel_maintenance_workers TO 4;

Il parametro max_parallel_maintenance_workers non garantisce l'allocazione dei worker. Per verificare che siano stati avviati più worker paralleli dalla reindicizzazione, esegui la seguente query da un'altra sessione dopo aver avviato la reindicizzazione:

Esempio di codice

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;

Reindicizza l'indice

Puoi reindicizzare un indice senza bloccare il workload di produzione utilizzando l'utilità pg_repack. Questa utilità automatizza e semplifica il processo di reindicizzazione simultanea, consentendoti di reindicizzare senza tempi di inattività. Per questa procedura, utilizza pg_repack versione 1.5.3.

Completa i seguenti passaggi per reindicizzare l'indice utilizzando pg_repack:

  1. Scarica, compila e installa l'utilità pg_repack dalla pagina pg_repack.

  2. Crea l'estensione pg_repack:

    Esempio di codice

    CREATE EXTENSION pg_repack;

  3. Esegui questo comando per reindicizzare l'indice contemporaneamente:

    Esempio di codice

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

    Questo comando ha un output simile al seguente:

    Output

    INFO: repacking index "public.t_i_key"

    Se si sono verificati errori durante l'esecuzione di pg_repack, puoi correggerli e riprovare. Dopo aver corretto tutti gli indici di chiavi univoche e gli indici di chiave primaria, devi verificare la presenza di chiave esterna esterne e correggere quelle trovate.

Trova e correggi le violazioni delle chiave esterna

Per informazioni su come trovare e correggere le violazioni delle chiave esterna, consulta Trova e correggi le chiave esterna esterne.

Monitora le operazioni di reindicizzazione

Occasionalmente, altre sessioni potrebbero bloccare l'operazione di reindicizzazione. Controlla l'operazione ogni 4 ore. Se l'operazione di reindicizzazione è bloccata, puoi annullare la sessione di blocco in modo che l'operazione di reindicizzazione possa essere completata.

Completa i seguenti passaggi per identificare le sessioni di blocco e di attesa, quindi annullale nell'operazione INDEX:

  1. Per identificare le sessioni di blocco, esegui questa query:

    Esempio di codice

    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. Per annullare una sessione, esegui la seguente query utilizzando il PID della sessione di blocco della query precedente:

    Esempio di codice

    SELECT pg_cancel_backend(PID);

Verifica che gli indici siano coerenti

Devi continuare a verificare la presenza di incoerenze nell'indice per ogni indice incoerente. Dopo aver corretto tutti gli indici incoerenti e le violazioni delle chiavi dell'istanza, puoi verificare che non esistano problemi seguendo i passaggi descritti nelle sezioni precedenti: