Busca y corrige índices de árbol B incoherentes

Las incoherencias en los índices de la base de datos pueden ocurrir por varias razones, incluidos defectos de software, problemas de hardware o cambios subyacentes en el comportamiento, como cambios en el orden de clasificación.

La comunidad de PostgreSQL creó herramientas para identificar y solucionar estos problemas. Esto incluye herramientas como amcheck, que recomienda la comunidad de PostgreSQL para identificar problemas de coherencia, incluidos los problemas que mostraron las versiones anteriores de PostgreSQL 14.

Escribimos esta guía como referencia para los usuarios de Cloud SQL para PostgreSQL que tienen estos problemas. Esperamos que esta página proporcione información que también pueda ayudar a otros usuarios de PostgreSQL a identificar y solucionar índices incoherentes de árboles B. Nuestro objetivo es mejorar este documento de forma continua como recurso para la comunidad de código abierto más amplia. Si tienes algún comentario, usa el botón Enviar comentarios en la parte superior e inferior de esta página.

Para resolver las incoherencias de un índice, debes seguir los siguientes pasos:

1. Antes de comenzar

   Antes de empezar a indexar, debes crear una copia de seguridad de tu base de datos, configurar los permisos correctos, verificar tu versión de cliente psql y descargar la extensión amcheck.

2. Verifica los índices de arbol B incoherentes

   A fin de identificar los índices para los que necesitas corregir incoherencias, debes identificar todos los índices de árbol B con incoherencias y, además, identificar todos los incumplimientos de clave primaria y única.

3. Corrige las incoherencias del índice.

   La reindexación de un índice corrige todas sus incoherencias. Es posible que debas ajustar la configuración de memoria de la instancia para mejorar el rendimiento.

4. Supervisa las operaciones de reindexación.

   Te recomendamos supervisar el progreso de la operación de reindexación para asegurarte de que la operación avance y no se bloquee.

5. Verifica que los índices sean coherentes.

   Una vez que hayas indexado de forma correcta tu índice, te recomendamos que verifiques que tu índice no contenga incoherencias.

Antes de comenzar

Crea una copia de seguridad de tu base de datos

Para garantizar que no se pierdan datos durante la reindexación, te recomendamos que crees una copia de seguridad de la base de datos. Para obtener más información, consulta Crea una copia de seguridad a pedido.

Precaución: Una copia de seguridad que se toma en una base de datos que ya tiene incoherencias en el índice seguirá teniendo incoherencias cuando se restablezca.

Configura el permiso cloudsqlsuperuser

Para completar los pasos de esta página, debes tener permisos cloudsqlsuperuser. Para obtener más información, consulta session_replication_role.

Asegúrate de tener la versión del cliente psql 9.6 o superior

Para completar los pasos de esta página, debes asegurarte de que tu versión de cliente psql sea 9.6 o superior. Ejecuta el comando psql --version para verificar tu versión actual de cliente psql.

Instala la extensión amcheck

Para verificar las incoherencias en el índice, debes habilitar la extensión amcheck.

PostgreSQL 9.6

Nota: Debes asegurarte de que POSTGRES_9_6_24.R20220710.01_12 esté instalado antes de instalar amcheck_next. Para obtener más información sobre la instalación de una nueva versión de mantenimiento objetivo, consulta Mantenimiento de autoservicio.

Si deseas instalar amcheck para PostgreSQL 9.6, ejecuta la siguiente sentencia:

  CREATE EXTENSION amcheck_next;
  

Si ves un error que dice “No se pudo abrir el archivo de control de la extensión...”, verifica que estés ejecutando la versión de mantenimiento de destino correcta (POSTGRES_9_6_24.R20220710.01_12).

PostgreSQL 10 y versiones posteriores

Si deseas instalar amcheck para PostgreSQL 10 y versiones posteriores, ejecuta la siguiente instrucción:

  CREATE EXTENSION amcheck;
  

Verifica los índices de arbol B incoherentes

En las siguientes secciones, se describe cómo verificar los índices de árbol B incoherentes mediante la verificación de las incoherencias de un índice, así como los incumplimientos de clave primaria y única.

Verifica las incoherencias

Ejecuta la siguiente declaración para verificar las incoherencias en todos los índices de árbol B en cada una de las bases de datos:

Muestra 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;
        IF version = '10' THEN
          PERFORM bt_index_check(index => r.oid);
        ELSE
          PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
        END IF;
      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 $$;
  

Deberías recibir un resultado similar al siguiente.

Salida

  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"
  

Advertencia: Aunque tu salida no contenga notificaciones de ADVERTENCIA, todavía pueden existir violaciones de clave o únicas. Para verificar los incumplimientos, realiza los procedimientos que se indican en Identifica y corrige incumplimientos únicos y de clave primaria.

Para obtener más información sobre la visualización de registros de PostgreSQL, consulta Visualiza registros de instancias.

Identifica y corrige incumplimientos de claves primarias y únicas

En esta sección, se describe cómo verificar tu índice para detectar incumplimientos de claves primarias y únicas y, si existe alguna, cómo solucionarlos.

Identifica incumplimientos de claves únicas

Los incumplimientos de claves únicas se deben corregir antes de volver a indexar un índice. Para verificar todos los incumplimientos de claves únicas, ejecuta el siguiente comando en cada base de datos:

Muestra 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    on   table=%1$I      key_columns=%2$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
  

El resultado de la secuencia de comandos es similar al siguiente:

Salida

  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
  

En esta salida, el encabezado de la tabla NOTICE muestra el índice, la columna y la tabla de los valores que se muestran debajo. Si el resultado contiene filas que muestran DUPLICATE o BACKWARDS, esto muestra el daño en el índice y es posible que se deba corregir. Las filas con BACKWARDS indican posibles valores duplicados que pueden estar ocultos. Si ves alguna de estas entradas en la tabla, consulta Corrige los incumplimientos de claves duplicadas.

Cómo corregir incumplimientos de claves duplicadas

Si identificaste un índice único duplicado o si una operación de reíndice falla debido a un error de incumplimiento de clave duplicada, completa los siguientes pasos para encontrar y quitar las claves duplicadas.

Nota: Debes repetir los siguientes pasos para cada índice que tenga infracciones de claves.

1. Extrae el key_columns del encabezado de la tabla NOTICE, como se muestra en el resultado de muestra anterior. En el siguiente ejemplo, la columna de clave es email.

   Muestra de código

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

   Usa estos valores en KEY_COLUMNS en la consulta del paso 3.

2. Busca el esquema de tu tabla. Usa psql para conectarte a tu base de datos y ejecuta el siguiente comando:

   Muestra de código

   \dt TABLE_NAME

   El valor en la columna schema es el valor que usas para SCHEMA_NAME en la consulta del paso 3.

   Por ejemplo, en la siguiente consulta:

    \dt games
    

   El resultado es similar a este:

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

3. Ejecuta las siguientes declaraciones para forzar un análisis completo de la tabla y obtener claves duplicadas.

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

   Nota: Si tu base de datos es grande, esta declaración puede tardar mucho tiempo en completarse.

   En la declaración anterior, KEY_COLUMNS son una o más columnas cubiertas por el índice único o la clave primaria de la tabla que verificas. Se identifican cuando verificas incumplimientos de claves únicas. La instrucción muestra las claves duplicadas y un recuento de los duplicados para cada una.

   Por ejemplo, en la siguiente consulta:

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

   El resultado es similar a este:

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

   En este caso, continúa con el siguiente paso para quitar las claves duplicadas.

   Si alguna de las columnas de KEY_COLUMNS es nula, puedes ignorarlas, ya que no se aplican restricciones únicas para las columnas NULAS.

   Si no se encuentran claves duplicadas, puedes pasar a la página Corregir índices incoherentes.

4. Opcional, pero recomendado: Crea una copia de seguridad de los registros que contienen claves duplicadas. Ejecuta la siguiente declaración para crear registros de copia de seguridad:

   Muestra de código

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

   En esta declaración, KEY_VALUES es una lista de valores copiados del resultado del paso anterior. Por ejemplo:

   Muestra de código

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

   Para una gran cantidad de filas, es más fácil reemplazar el parámetro ((KEY_VALUES ) en la sentencia IN con la sentencia SELECT del paso 2 sin el parámetro count. Por ejemplo:

   Muestra 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. Agrega un rol de replicación al usuario para inhabilitar los activadores:

   Muestra de código

     ALTER USER CURRENT_USER with REPLICATION;
     SET session_replication_role = replica;
     

6. Ejecuta la siguiente instrucción para borrar las claves duplicadas:

   Muestra 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 ejemplo, para KEY_COLUMNS de varias columnas:

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

   Donde day y rnum son KEY_COLUMNS.

   Cuando se ejecuta esta sentencia, se mantiene una fila y se borran las otras para cada conjunto de filas duplicadas. Si deseas controlar qué versión de la fila se borra, ejecuta el siguiente filtro en la declaración de eliminación:

   Muestra de código

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

   Advertencia: La confirmación de esta transacción cambia tu base de datos y elimina las claves duplicadas. Asegúrate de no borrar ningún dato que aún sea necesario. El restablecimiento desde una copia de seguridad puede restablecer los índices incoherentes.

7. Completa los siguientes pasos para verificar que el comando DELETE mostró la cantidad esperada de filas sin ningún error:

   1. Ejecuta la siguiente declaración para identificar las filas en las que se cambiaron las tablas:

      Muestra 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. Si todas las filas son correctas, confirma la transacción DELETE:

      Muestra de código

        END;
        

   3. Si hay errores, revierte los cambios para solucionarlos:

      Muestra de código

        ROLLBACK;
        

8. Una vez que se borren las claves duplicadas, puedes volver a indexar tu índice.

Corrige los índices incoherentes

En las siguientes secciones, se describe cómo puedes corregir las incoherencias del índice que se encuentran en tu instancia.

Nota: Si tu índice tiene incumplimientos de claves únicas y principales, debes corregirlos antes de volver a indexar.

Según cómo esté configurada la base de datos, es posible que debas hacer lo siguiente para cada índice identificado en los pasos anteriores:

1. Prepárate para volver a indexar tu índice.

2. Vuelve a indexar tu índice.

3. Si la operación de reindexación falla debido a incumplimientos de clave externa, debes encontrar y corregir estos incumplimientos.

4. Vuelve a ejecutar la operación de reindexación.

Prepárate para volver a indexar tu índice

Busca el tamaño del índice

Indexar bases de datos más grandes requiere más tiempo que las bases de datos más pequeñas. Para mejorar la velocidad de las operaciones de índice y reindexación de bases de datos más grandes, puedes asignar más memoria y potencia de CPU a estas operaciones. Este es un paso importante para planificar la operación de reindexación. Una vez que conoces el tamaño del índice, puedes establecer el tamaño de la memoria que usa la operación de reíndice y establecer la cantidad de trabajadores paralelos.

Ejecuta la siguiente declaración para encontrar el tamaño del índice, en kilobytes, del índice que deseas corregir:

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

El resultado de esta sentencia es similar al siguiente:

Salida

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

Establece el tamaño de la memoria para usar en la reindexación

Según el tamaño de tu índice, como se determina en la sección anterior, es importante configurar el valor adecuado para maintenance_work_mem. Este parámetro especifica la cantidad de memoria que se usará para la operación de reindexación. Por ejemplo, si el tamaño de tu índice es superior a 15 GB, te recomendamos que ajustes la memoria de mantenimiento. Para obtener más información, consulta Configura una marca de base de datos.

Nota: El valor de maintenance_work_mem se establece para cada sesión. No necesitas reiniciar tu instancia después de cambiar el valor. Sin embargo, si vuelves a conectarte a la base de datos en una sesión nueva, el valor de la configuración anterior no se guarda.

Indexar bases de datos más grandes requiere más tiempo que las bases de datos más pequeñas. A fin de mejorar la velocidad de las operaciones de índice y reindexación, recomendamos configurar maintenance_work_mem en al menos el 2% de la memoria de la instancia para las instancias con 4 GB o más de memoria durante esta operación de reindexación.

Configura la cantidad de trabajadores paralelos

Puedes aumentar la cantidad de trabajadores paralelos para que se vuelvan a indexar si configuras el parámetro max_parallel_maintenance_workers en las bases de datos con PostgreSQL 11 o versiones posteriores. El valor predeterminado de este parámetro es 2, pero se puede establecer en un valor más alto para aumentar la cantidad de trabajadores a fin de volver a indexar. Para las instancias con 8 núcleos de CPU virtuales o más, recomendamos establecer el valor de la marca max_parallel_maintenance_workers en 4.

Para obtener más información, consulta Configura una marca de base de datos.

Vuelve a indexar tu índice

Puedes volver a indexar un índice sin bloquear tu carga de trabajo de producción con la utilidad pg_repack. Esta utilidad automatiza y simplifica el proceso de reindexación simultáneo, lo que te permite volver a indexar sin tiempo de inactividad, en especial para las versiones de PostgreSQL 11 y anteriores, que no tienen la operación REINDEX CONCURRENTLY. Para este procedimiento, usa la versión 1.4.7 pg_repack.

Sigue los pasos que se indican a continuación para volver a indexar tu índice con pg_repack:

1. Descarga, compila y, luego, instala la utilidad pg_repack desde la página de pg_repack.

   Debian GNU/Linux 11

   Para tu comodidad, recomendamos que los usuarios de Debian Linux descarguen e instalen este objeto binario ejecutable ya compilado para la plataforma x86_64 de Linux.

   El hash de la suma de comprobación sha256 del objeto binario es el siguiente:

   ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

   Para verificar que tu versión de Linux sea Debian GNU/Linux 11, ejecuta el comando hostnamectl.

   Autocompilación

   Nota: Para este procedimiento, debes ejecutar el comando en la terminal en Linux o en Unix con una cadena de herramientas de GNU que funcione.

   Descarga, compila e instala la utilidad pg_repack desde la página pg_repack.

2. Crea una extensión pg_repack:

   Muestra de código

     CREATE EXTENSION pg_repack;
     

3. Ejecuta el siguiente comando para volver a indexar tu índice de forma simultánea:

   Muestra 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
     

   Nota: pg_repack No te permite reindexar un índice en una vista materializada.

   Este comando tiene un resultado similar al siguiente:

   Salida

     INFO: repacking index "public.t_i_key"
     

   Si se produjo algún error cuando se ejecutó pg_repack, puedes corregir el error y volver a intentarlo. Una vez que hayas corregido todos los índices de claves únicas y los índices de claves primarias, debes verificar los incumplimientos de claves externas y corregir los que se encuentren.

Encuentra y corrige incumplimientos de claves externas

Para obtener información sobre cómo encontrar y corregir incumplimientos de claves externas, consulta Encuentra y corrige incumplimientos de claves externas.

Supervisa las operaciones de reindexación

En ocasiones, otras sesiones pueden bloquear la reindexación. Te recomendamos que lo verifiques cada 4 horas. Si la operación de reindexación está bloqueada, puedes cancelar la sesión de bloqueo para que se pueda completar la operación.

Completa los siguientes pasos para identificar las sesiones de bloqueo y espera y, luego, cancélalos en la operación INDEX:

1. Para identificar las sesiones de bloqueo, ejecuta la siguiente consulta:

   Muestra 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 una sesión, ejecuta la siguiente consulta con el PID de la sesión de bloqueo de la consulta anterior:

   Muestra de código

     SELECT pg_cancel_backend(PID);
     

Verifica que tus índices sean coherentes

Debes continuar verificando las incoherencias en cada índice. Después de corregir todos los índices incoherentes y los incumplimientos de clave de tu instancia, puedes verificar que no existan problemas mediante los pasos en las secciones anteriores:

• Verifica las incoherencias de un índice

• Comprueba si hay incumplimientos de claves únicas