Encontrar e corrigir índices de árvore B inconsistentes

Inconsistências nos índices de banco de dados podem ocorrer por vários motivos, incluindo defeitos de software, problemas de hardware ou mudanças no comportamento, como mudanças na ordem de classificação.

A comunidade do PostgreSQL criou ferramentas para identificar e corrigir esses problemas. Isso inclui ferramentas como o amcheck, recomendado pela comunidade do PostgreSQL para identificar problemas de consistência, incluindo problemas apresentados pelas versões anteriores do PostgreSQL 14.

Escrevemos este manual como uma referência para usuários do AlloyDB para PostgreSQL que enfrentam esses problemas. Esperamos que esta página tenha informações que também possam ajudar outros usuários do PostgreSQL a identificar e corrigir índices inconsistentes de árvore B. Nosso objetivo é melhorar continuamente este documento como um recurso para a comunidade de código aberto mais ampla. Para fazer comentários, use o botão Enviar feedback na parte de cima e de baixo da página.

Para resolver as inconsistências de um índice, siga estas etapas:

  1. Antes de começar.

    Antes de começar a reindexação, faça backup do seu banco de dados, defina as permissões corretas, verifique a versão do cliente psql e ative a extensão amcheck.

  2. Verifique se há índices inconsistentes de árvore B.

    Para identificar os índices em que você precisa corrigir inconsistências, é necessário identificar todos os índices de árvore B com inconsistências e todas as violações de chaves únicas e primárias.

  3. Corrija as inconsistências do índice.

    A reindexação de um índice corrige todas as inconsistências dele. Talvez seja necessário ajustar as configurações de memória da instância para melhorar o desempenho.

  4. Monitore as operações de reindexação.

    Recomendamos monitorar o progresso da operação de reindexação para garantir que ela esteja em andamento e não seja bloqueada.

  5. Verifique se os índices são consistentes.

    Após reindexar com êxito o índice, recomendamos que você verifique se não há inconsistências nele.

Antes de começar

Fazer backup dos dados do cluster do AlloyDB

Para garantir que nenhum dado seja perdido durante a reindexação, recomendamos fazer backup dos dados do cluster. Para ver mais informações, consulte Criar um backup sob demanda.

Definir a permissão alloydbsuperuser

Para concluir as etapas nesta página, você precisa ter permissões alloydbsuperuser. Para mais informações, consulte Funções predefinidas do PostgreSQL do AlloyDB.

Verifique se a versão do psql do cliente é 9.6 ou mais recente

Para concluir as etapas nesta página, verifique se a versão do psql do cliente é 9.6 ou mais recente. Execute o comando psql --version para verificar a versão atual do psql do cliente.

Ativar a extensão amcheck

Para verificar se há inconsistências de índice, ative a extensão amcheck.

Exemplo de código

  CREATE EXTENSION amcheck;

Verificar se há índices inconsistentes de árvore B

As seções a seguir descrevem como verificar índices de árvore B inconsistentes verificando as inconsistências de um índice e as violações únicas e primárias de chave.

Conferir se há inconsistências

Execute a seguinte instrução em cada um dos bancos de dados para verificar se há inconsistências em todos os índices de árvore B:

Exemplo de código

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

Será exibida uma saída semelhante à exibida a seguir.

Exemplo de código

  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"

Para mais informações sobre como visualizar registros, consulte Ver registros usando o Explorador de registros.

Identificar e corrigir violações de chaves exclusivas e principais

Nesta seção, descrevemos como verificar se há violações de chave primária e única no índice e como corrigir as ocorrências encontradas.

Identificar violações de chaves exclusivas

As violações de chave únicas precisam ser corrigidas antes de reindexar um índice. Para verificar se há violações de chave única, execute o seguinte comando em cada banco de dados:

Exemplo de código

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

A saída deste script é semelhante a:

Saída

  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

Nesta saída, o cabeçalho da tabela NOTICE mostra abaixo de si o índice, a coluna e a tabela dos valores. Se a saída contiver linhas que exibem DUPLICATE ou BACKWARDS, haverá corrupção no índice e ela poderá precisar ser corrigida. As linhas com BACKWARDS indicam possíveis valores duplicados que podem estar ocultos. Se você encontrar uma dessas entradas na tabela, consulte Corrigir violações de chaves duplicadas.

Corrigir violações de chaves duplicadas

Se você tiver identificado um índice único duplicado ou se uma operação de reindexação falhar devido a um erro de violação de chave duplicada, siga as etapas abaixo para encontrar e remover as chaves duplicadas.

  1. Extraia key_columns do cabeçalho da tabela NOTICE, conforme mostrado no exemplo de saída anterior. No exemplo a seguir, a coluna de chaves é email.

    Saída

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

    Use esses valores no KEY_COLUMNS na consulta da etapa 3.

  2. Encontre o esquema da sua tabela. Use psql para se conectar ao banco de dados e execute o seguinte comando:

    Exemplo de código

    \dt TABLE_NAME
     O valor na coluna schema é o valor usado para SCHEMA_NAME na consulta da etapa 3.

    Por exemplo, para a seguinte consulta:

     \dt games

    O resultado será assim:

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

  3. Execute as instruções a seguir para forçar uma verificação completa da tabela e receber chaves duplicadas.

    Exemplo de código

    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;

    Na instrução acima, KEY_COLUMNS são uma ou mais colunas cobertas pelo índice único ou pela chave primária na tabela que você está verificando. Elas foram identificadas quando você verificou violações de chaves únicas. A instrução retorna as chaves duplicadas e uma contagem das cópias para cada uma.

    Por exemplo, para a seguinte consulta:

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

    O resultado será assim:

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

    Nesse caso, siga para a próxima etapa e remova as chaves duplicadas.

    Se alguma das colunas em KEY_COLUMNS for nula, ignore-a porque as restrições únicas não se aplicam a colunas NULL.

    Se nenhuma chave duplicada for encontrada, acesse Corrigir índices inconsistentes.

  4. Opcional, mas recomendado: crie um backup para os registros que contêm chaves duplicadas. Execute a seguinte instrução para criar registros de backup:

    Exemplo de código

    CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
WHERE (KEY_COLUMNS)
IN (KEY_VALUES);
    Nessa instrução, KEY_VALUES é uma lista de valores copiados do resultado da etapa anterior. Por exemplo:

    Exemplo de código

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

    Para um grande número de linhas, é mais fácil substituir o parâmetro ((KEY_VALUES)) na instrução IN com a instrução SELECT da etapa 2 sem o parâmetro count. Exemplo:

    Exemplo de código

      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. Adicione um papel de replicação ao usuário para desativar os gatilhos:

    Exemplo de código

    ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;

  6. Execute a seguinte instrução para excluir as chaves duplicadas:

    Exemplo de código

    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;

    Por exemplo, para KEY_COLUMNS de várias colunas:

    Exemplo de código

      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;
     Em que dia e rnum são KEY_COLUMNS.

    A execução dessa instrução mantém uma linha e exclui outras para cada conjunto de linhas duplicadas. Para controlar qual versão da linha é excluída, execute o seguinte filtro na instrução de exclusão:

    Exemplo de código

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

  7. Conclua as etapas a seguir para verificar se o comando DELETE retornou o número esperado de linhas sem erros:

    1. Execute a seguinte instrução para identificar as linhas em que as tabelas foram alteradas:

      Exemplo de código

      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 todas as linhas estiverem corretas, confirme a transação DELETE:

      Exemplo de código

      END;

    3. Se houver erros, reverta as alterações para corrigi-los:

      Exemplo de código

      ROLLBACK;

  8. Após a exclusão das chaves duplicadas, será possível reindexar o índice.

Corrigir índices inconsistentes

Veja nas seções a seguir como corrigir as inconsistências de índice encontradas na instância.

Dependendo de como o banco de dados está configurado, pode ser necessário fazer o seguinte para cada índice identificado nas etapas anteriores:

  1. Preparar-se para reindexar o índice.

  2. Reindexar o índice.

  3. Se a operação de reindexação falhar devido a violações de chave externa, será necessário encontrar e corrigir essas violações.

  4. Executar a operação de reindexação novamente.

Preparar-se para indexar novamente o índice

Encontrar o tamanho do índice

Os bancos de dados maiores exigem mais tempo do que os menores para a indexação. Para melhorar a velocidade da indexação e da reindexação das operações de bancos de dados maiores, aloque mais memória e CPU para essas operações. Essa é uma etapa importante no planejamento da operação de reindexação. Depois de saber o tamanho do índice, é possível definir o tamanho da memória usado pela operação de reindexação e definir o número de workers paralelos.

Execute a seguinte instrução para encontrar o tamanho, em kilobytes, do índice que você quer corrigir:

Exemplo de código

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

A saída desta instrução é semelhante a:

Saída

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

Definir o tamanho da memória a ser usado para reindexação

Com base no tamanho do índice, conforme determinado na seção anterior, é importante definir o valor apropriado para o parâmetro de configuração maintenance_work_mem. Esse parâmetro especifica a quantidade de memória a ser usada para a operação de reindexação. Por exemplo, se o tamanho do índice for maior que 15 GB, ajuste a memória de manutenção.

O exemplo a seguir mostra como definir maintenance_work_mem:

Exemplo de código

SET maintenance_work_mem TO "1GB";

Os bancos de dados maiores exigem mais tempo do que os menores para a indexação. Para aumentar a velocidade das operações de indexação e reindexação, defina maintenance_work_mem como pelo menos 2% da memória para instâncias com 4 GB ou mais de memória durante essa operação de reindexação.

Definir o número de workers paralelos

É possível aumentar o número de workers paralelos para reindexação definindo o parâmetro de configuração max_parallel_maintenance_workers no banco de dados. O valor padrão desse parâmetro é 2, mas ele pode ser definido mais alto para aumentar o número de workers relacionados à reindexação. Para instâncias com oito ou mais núcleos de vCPU, recomendamos definir o valor da sinalização max_parallel_maintenance_workers como 4.

Confira a seguir como identificar os valores definidos para esses parâmetros:

Exemplo de código

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

O parâmetro max_parallel_maintenance_workers é um subconjunto de max_worker_processes e limitado por max_parallel_workers. Se você precisar de mais workers em paralelo, aumente o valor de max_worker_processes e max_parallel_workers.

O exemplo a seguir mostra como definir max_parallel_maintenance_workers:

Exemplo de código

SET max_parallel_maintenance_workers TO 4;

O parâmetro max_parallel_maintenance_workers não garante a alocação de workers. Para confirmar que você tem vários workers paralelos iniciada pela reindexação, execute a consulta a seguir em outra sessão depois de iniciar a reindexação:

Exemplo de código

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;

Reindexar o índice

É possível reindexar um índice sem bloquear a carga de trabalho de produção usando o utilitário pg_repack. Esse utilitário automatiza e simplifica o processo de reindexação simultânea, permitindo a reindexação sem inatividade. Para este procedimento, use a versão 1.4.7 de pg_repack.

Conclua as etapas a seguir para indexar o índice novamente usando pg_repack:

  1. Faça o download, compile e instale o utilitário pg_repack na página pg_repack.

  2. Crie a extensão pg_repack:

    Exemplo de código

    CREATE EXTENSION pg_repack;

  3. Execute o comando a seguir para indexar o índice simultaneamente:

    Exemplo de código

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

    A saída do comando é parecida com o seguinte:

    Saída

    INFO: repacking index "public.t_i_key"

    Se ocorreram erros ao executar pg_repack, corrija-os e tente novamente. Depois de corrigir todos os índices de chaves únicas e primárias, verifique se há violações de chaves externas e corrija-as, se encontradas.

Encontrar e corrigir violações de chaves externas

Para informações sobre como encontrar e corrigir violações de chaves externas, consulte Encontrar e corrigir violações de chaves externas.

Monitorar operações de reindexação

De vez em quando, a operação de reindexação pode ser bloqueada por outras sessões. Recomendamos verificar isso a cada quatro horas. Se a operação de reindexação for bloqueada, cancele a sessão de bloqueio para que ela seja concluída.

Conclua as etapas a seguir para identificar sessões de bloqueio e espera e cancelá-las na operação INDEX:

  1. Para identificar sessões de bloqueio, execute a seguinte consulta:

    Exemplo de código

    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. Para cancelar uma sessão, execute a seguinte consulta usando o PID da sessão de bloqueio da consulta anterior:

    Exemplo de código

    SELECT pg_cancel_backend(PID);

Verificar se os índices são consistentes

Você precisa continuar a verificar as inconsistências de cada índice inconsistente. Depois de corrigir todos os índices inconsistentes e as violações de chave da instância, verifique se não há problemas seguindo as etapas nas seções anteriores: