Encontre e corrija índices inconsistentes

As inconsistências nos índices da base de dados podem ocorrer por vários motivos, incluindo defeitos de software, problemas de hardware ou alterações subjacentes no comportamento, como alterações na ordem de ordenação.

A comunidade PostgreSQL criou ferramentas para identificar e corrigir esses problemas. Isto inclui ferramentas como o amcheck, que é recomendado pela comunidade do PostgreSQL para identificar problemas de consistência, incluindo problemas que as versões anteriores do PostgreSQL 14 apresentavam.

Este manual é uma referência para os utilizadores do AlloyDB for PostgreSQL que têm estes problemas de consistência. O manual fornece informações que ajudam os utilizadores do PostgreSQL a identificar e corrigir índices inconsistentes.

A resolução das inconsistências de um índice envolve os seguintes passos:

  1. Antes de começar.

    Antes de começar a reindexar, deve fazer uma cópia de segurança da base de dados, definir as autorizações corretas, verificar a versão do cliente psql e ativar a extensão amcheck.

  2. Verifique se existem índices inconsistentes.

    Uma declaração identifica violações de chaves únicas e principais, e outra declaração deteta uma variedade de outras inconsistências.

  3. Corrija as inconsistências do índice.

    A reindexação de um índice corrige todas as respetivas inconsistências. Pode ter de ajustar as definições de memória da instância para melhorar o desempenho.

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

    Recomendamos que monitorize o progresso da operação de reindexação para garantir que a operação está em curso e não está bloqueada.

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

    Depois de reindexar com êxito o índice, recomendamos que verifique se o índice não contém inconsistências.

Antes de começar

Faça uma cópia de segurança dos dados do cluster do AlloyDB

Para garantir que não são perdidos dados durante a reindexação, recomendamos que faça uma cópia de segurança dos dados do cluster. Para mais informações, consulte o artigo Crie uma cópia de segurança a pedido.

Defina a autorização alloydbsuperuser

Para concluir os passos nesta página, tem de ter alloydbsuperuserautorizações. Para mais informações, consulte os papéis predefinidos do PostgreSQL do AlloyDB.

Certifique-se de que a versão do cliente psql é 9.6 ou superior

Para concluir os passos nesta página, tem de garantir que a versão do cliente psqlé 9.6 ou superior. Execute o comando psql --version para verificar a versão atual do cliente psql.

Ative a extensão amcheck

Para verificar inconsistências do índice, tem de ativar a extensão amcheck.

Exemplo de código

  CREATE EXTENSION amcheck;

Verifique se existem índices inconsistentes

As secções seguintes descrevem como verificar a existência de índices inconsistentes, verificando as inconsistências de um índice, bem como as violações de chaves únicas e primárias.

Verifique se existem inconsistências

Em cada base de dados, execute a seguinte declaração para verificar se existem inconsistências:

Exemplo de código

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

Deve receber um resultado semelhante ao seguinte:

Exemplo de código

  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"

Para mais informações sobre como ver registos, consulte o artigo Veja registos através do Explorador de registos.

Identifique e corrija violações de chaves únicas e primárias

Esta secção descreve como verificar o índice quanto a violações de chaves únicas e primárias e, se existirem, como as corrigir.

Identifique violações de chaves únicas

As violações de chaves únicas têm de ser corrigidas antes de reindexar um índice. Para verificar todas as violações de chaves únicas, execute o seguinte comando em cada base 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 do script é semelhante à seguinte:

Resultado

  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

Neste resultado, o cabeçalho da tabela NOTICE mostra o índice, a coluna e a tabela para os valores apresentados abaixo. Se o resultado contiver linhas que apresentem DUPLICATE ou BACKWARDS, significa que o índice está danificado e pode ter de ser corrigido. As linhas com BACKWARDS indicam possíveis valores duplicados que podem estar ocultos. Se vir qualquer uma destas entradas na tabela, consulte o artigo Corrija violações de chaves duplicadas.

Corrija violações de chaves duplicadas

Se identificou um índice único duplicado ou se uma operação de reindexação falhar devido a um erro de violação de chave duplicada, conclua os passos seguintes para encontrar e remover as chaves duplicadas.

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

    Resultado

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

    Use estes valores em KEY_COLUMNS na consulta no passo 3.

  2. Encontre o esquema da sua tabela. Use o psql para se ligar à sua base de dados e execute o seguinte comando:

    Exemplo de código

    \dt TABLE_NAME
     O valor na coluna schema é o valor que usa para SCHEMA_NAME na consulta no passo 3.

    Por exemplo, para a seguinte consulta:

     \dt games

    O resultado é semelhante ao seguinte:

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

  3. Execute as seguintes declarações para forçar uma análise completa da tabela e obter 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 declaração acima, KEY_COLUMNS são uma ou mais colunas abrangidas pelo índice único ou pela chave principal na tabela que está a verificar. Estas foram identificadas quando verificou se existiam violações de chaves únicas. A declaração devolve as chaves duplicadas e uma contagem dos duplicados para cada uma.

    Por exemplo, para a seguinte consulta:

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

    O resultado é semelhante ao seguinte:

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

    Neste caso, avance para o passo seguinte para remover as chaves duplicadas.

    Se alguma das colunas em KEY_COLUMNS for nula, pode ignorá-la, uma vez que as restrições exclusivas não se aplicam a colunas NULL.

    Se não forem encontradas chaves duplicadas, pode avançar para a secção Corrija índices inconsistentes.

  4. Opcional, mas recomendado: crie uma cópia de segurança dos registos que contêm chaves duplicadas. Execute a seguinte declaração para criar registos de cópia de segurança:

    Exemplo de código

    CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
WHERE (KEY_COLUMNS)
IN (KEY_VALUES);
    Nesta declaração, KEY_VALUES é uma lista de valores copiados do resultado do passo 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 declaração IN pela declaração SELECT do passo 2 sem o parâmetro count. Por 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 uma função de replicação ao utilizador para desativar os acionadores:

    Exemplo de código

    ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;

  6. Execute a seguinte declaração para eliminar 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;
     Onde day e rnum são KEY_COLUMNS.

    A execução desta declaração mantém uma linha e elimina as outras para cada conjunto de linhas duplicadas. Se quiser controlar que versão da linha é eliminada, execute o seguinte filtro na declaração de eliminação:

    Exemplo de código

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

  7. Conclua os passos seguintes para verificar se o comando DELETE devolveu o número esperado de linhas sem erros:

    1. Execute a seguinte declaração para identificar as linhas nas quais 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 existirem erros, reverta as alterações para os corrigir:

      Exemplo de código

      ROLLBACK;

  8. Depois de eliminar as chaves duplicadas, pode reindexar o índice.

Corrija índices inconsistentes

As secções seguintes descrevem como pode corrigir as inconsistências de índice encontradas na sua instância.

Consoante a configuração da sua base de dados, pode ter de fazer o seguinte para cada índice identificado nos passos anteriores:

  1. Prepare-se para reindexar o seu índice.

  2. Reindexe o seu índice.

  3. Se a operação de reindexação falhar devido a violações de chaves externas, tem de encontrar e corrigir estas violações.

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

Prepare-se para reindexar o seu índice

Encontre o tamanho do índice

A indexação de bases de dados maiores requer mais tempo do que a de bases de dados mais pequenas. Para melhorar a velocidade das operações de indexação e reindexação de bases de dados maiores, pode atribuir mais memória e potência do CPU a estas operações. Este é um passo importante no planeamento da operação de reindexação. Depois de saber o tamanho do índice, pode definir o tamanho da memória usado pela operação de reindexação e definir o número de trabalhadores paralelos.

Execute a seguinte declaração para encontrar o tamanho do índice, em kilobytes, do índice que 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';

O resultado desta declaração é semelhante ao seguinte:

Resultado

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

Defina o tamanho da memória a usar para a reindexação

Com base na dimensão do seu índice, conforme determinado na secção anterior, é importante definir o valor adequado para o parâmetro de configuração maintenance_work_mem. Este parâmetro especifica a quantidade de memória a usar para a operação de reindexação. Por exemplo, se o tamanho do índice for superior a 15 GB, recomendamos que ajuste a memória de manutenção.

O exemplo seguinte mostra como definir maintenance_work_mem:

Exemplo de código

SET maintenance_work_mem TO "1GB";

A indexação de bases de dados maiores requer mais tempo do que a de bases de dados mais pequenas. Para melhorar a velocidade das operações de indexação e reindexação, recomendamos que defina maintenance_work_mem para, pelo menos, 2% da memória da instância para instâncias com 4 GB ou mais de memória durante esta operação de reindexação.

Defina o número de trabalhadores paralelos

Pode aumentar o número de trabalhadores paralelos para a reindexação definindo o parâmetro de configuração max_parallel_maintenance_workers na base de dados. O valor predefinido deste parâmetro é 2, mas pode ser definido para um valor superior para aumentar o número de trabalhadores para a reindexação. Para instâncias com 8 ou mais núcleos de vCPU, recomendamos que defina o valor da flag max_parallel_maintenance_workers como 4.

A imagem seguinte mostra como identificar os valores definidos para estes 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 está limitado por max_parallel_workers. Se precisar de mais trabalhadores paralelos, aumente o valor de max_worker_processes e max_parallel_workers.

O exemplo seguinte 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 atribuição de trabalhadores. Para confirmar que tem vários trabalhadores paralelos iniciados pela reindexação, execute a seguinte consulta a partir de 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;

Reindexe o seu índice

Pode reindexar um índice sem bloquear a carga de trabalho de produção através do utilitário pg_repack. Esta utilidade automatiza e simplifica o processo de reindexação concorrente, o que lhe permite reindexar sem tempo de inatividade. Para este procedimento, use a pg_repackversão 1.4.7.

Conclua os passos seguintes para reindexar o seu índice através do pg_repack:

  1. Transfira, compile e instale a utilidade pg_repack a partir da página pg_repack.

  2. Crie a extensão pg_repack:

    Exemplo de código

    CREATE EXTENSION pg_repack;

  3. Execute o seguinte comando para reindexar o índice em simultâneo:

    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

    Este comando tem um resultado semelhante ao seguinte:

    Resultado

    INFO: repacking index "public.t_i_key"

    Se ocorrerem erros ao executar pg_repack, pode corrigi-los e tentar novamente. Depois de corrigir todos os índices de chaves únicas e índices de chaves primárias, deve verificar se existem violações de chaves externas e corrigir as que forem encontradas.

Encontre e corrija violações de chaves externas

Para obter informações sobre como encontrar e corrigir violações de chaves externas, consulte o artigo Encontre e corrija violações de chaves externas.

Monitorize as operações de reindexação

Ocasionalmente, a operação de reindexação pode ser bloqueada por outras sessões. Recomendamos que verifique esta opção a cada 4 horas. Se a operação de reindexação estiver bloqueada, pode cancelar a sessão de bloqueio para que a operação de reindexação possa ser concluída.

Conclua os passos seguintes para identificar sessões de bloqueio e espera e, em seguida, 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 com o PID da sessão de bloqueio da consulta anterior:

    Exemplo de código

    SELECT pg_cancel_backend(PID);

Verifique se os seus índices são consistentes

Tem de continuar a verificar inconsistências de índice para cada índice inconsistente. Depois de corrigir todas as violações de chaves e índices inconsistentes da sua instância, pode verificar se existem problemas seguindo os passos nas secções anteriores: