Buscar y corregir índices incoherentes

Las incoherencias en los índices de la base de datos pueden producirse por varios motivos, como defectos de software, problemas de hardware o cambios subyacentes en el comportamiento (por ejemplo, cambios en el orden de clasificación).

La comunidad de PostgreSQL ha creado herramientas para identificar y solucionar estos problemas. Esto incluye herramientas como amcheck, que la comunidad de PostgreSQL recomienda para identificar problemas de coherencia, incluidos los que presentaban versiones anteriores de PostgreSQL 14.

Esta guía es una referencia para los usuarios de AlloyDB para PostgreSQL que experimentan estos problemas de coherencia. La guía proporciona información que ayuda a los usuarios de PostgreSQL a identificar y corregir los índices incoherentes.

Para resolver las incoherencias de un índice, sigue estos pasos:

  1. Antes de empezar

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

  2. Comprueba si hay índices incoherentes.

    Una instrucción identifica las infracciones de claves únicas y primarias, y otra instrucción detecta otras incoherencias.

  3. Corrige las incoherencias del índice.

    Reindexar un índice corrige todas sus inconsistencias. Es posible que tengas que ajustar la configuración de memoria de tu instancia para mejorar el rendimiento.

  4. Monitorizar las operaciones de reindexación.

    Te recomendamos que monitorices el progreso de la operación de reindexación para asegurarte de que avanza y no se bloquea.

  5. Verifica que los índices sean coherentes.

    Una vez que haya vuelto a indexar su índice correctamente, le recomendamos que verifique que no contiene ninguna incoherencia.

Antes de empezar

Crear una copia de seguridad de los datos de un clúster de AlloyDB

Para asegurarte de que no se pierdan datos durante la reindexación, te recomendamos que hagas una copia de seguridad de los datos de tu clúster. Para obtener más información, consulta Crear una copia de seguridad bajo demanda.

Definir el permiso alloydbsuperuser

Para completar los pasos de esta página, debes tener alloydbsuperuser permisos. Para obtener más información, consulta Roles de PostgreSQL predefinidos de AlloyDB.

Asegúrate de que la versión del cliente psql sea la 9.6 o una posterior.

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

Habilitar la extensión amcheck

Para comprobar si hay incoherencias en el índice, debes habilitar la extensión amcheck.

Código de ejemplo

  CREATE EXTENSION amcheck;

Comprobar si hay índices incoherentes

En las siguientes secciones se describe cómo comprobar si hay índices incoherentes. Para ello, se comprueban las incoherencias de un índice, así como las infracciones de claves principales y únicas.

Comprobar si hay incoherencias

En cada base de datos, ejecuta la siguiente instrucción para comprobar si hay incoherencias:

Código de ejemplo

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

Deberías obtener un resultado similar al siguiente:

Código de ejemplo

  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 obtener más información sobre cómo ver registros, consulta el artículo Ver registros con el Explorador de registros.

Identificar y corregir infracciones de claves principales y únicas

En esta sección se describe cómo comprobar si hay infracciones de claves únicas y principales en el índice y, si las hay, cómo solucionarlas.

Identificar infracciones únicas de las claves

Las infracciones de claves únicas deben corregirse antes de volver a indexar un índice. Para comprobar todas las infracciones de claves únicas, ejecuta el siguiente comando en cada base de datos:

Código de ejemplo

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

La salida de la secuencia de comandos es similar a la 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 este resultado, 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 con DUPLICATE o BACKWARDS, significa que el índice está dañado y puede que tengas que corregirlo. Las filas con BACKWARDS indican posibles valores duplicados que podrían estar ocultos. Si ves alguna de estas entradas en la tabla, consulta Corregir infracciones de claves duplicadas.

Corregir infracciones de claves duplicadas

Si has identificado un índice único duplicado o si se produce un error en una operación de reindexación debido a una infracción de clave duplicada, sigue estos pasos para buscar y eliminar las claves duplicadas.

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

    Salida

      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 ejecutar el siguiente comando:

    Código de ejemplo

    \dt TABLE_NAME
     El valor de la columna schema es el que debe usar en SCHEMA_NAME de la consulta del paso 3.

    Por ejemplo, en la siguiente consulta:

     \dt games

    El resultado debería ser similar al siguiente:

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

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

    Código de ejemplo

    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;

    En la instrucción anterior, KEY_COLUMNS son una o varias columnas cubiertas por el índice único o la clave principal de la tabla que estás comprobando. Se identificaron cuando comprobaste si había infracciones de claves únicas. La instrucción devuelve las claves duplicadas y un recuento de los duplicados de cada una.

    Por ejemplo, en la siguiente consulta:

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

    El resultado debería ser similar al siguiente:

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

    En este caso, ve al siguiente paso para eliminar las claves duplicadas.

    Si alguna de las columnas de KEY_COLUMNS es nula, puedes ignorarla porque las restricciones únicas no se aplican a las columnas NULL.

    Si no se encuentran claves duplicadas, puedes ir a Corregir índices incoherentes.

  4. Opcional, pero recomendado: crea una copia de seguridad de los registros que contengan claves duplicadas. Ejecuta la siguiente instrucción para crear registros de copia de seguridad:

    Código de ejemplo

    CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
WHERE (KEY_COLUMNS)
IN (KEY_VALUES);
    En esta instrucción, KEY_VALUES es una lista de valores copiados del resultado del paso anterior. Por ejemplo:

    Código de ejemplo

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

    Si hay un gran número de filas, es más fácil sustituir el parámetro ((KEY_VALUES)) de la instrucción IN por la instrucción SELECT del paso 2 sin el parámetro count. Por ejemplo:

    Código de ejemplo

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

    Código de ejemplo

    ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;

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

    Código de ejemplo

    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, en el caso de KEY_COLUMNS de varias columnas:

    Código de ejemplo

      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.

    Al ejecutar esta instrucción, se conserva una fila y se eliminan las demás de cada conjunto de filas duplicadas. Si quieres controlar qué versión de la fila se elimina, ejecuta el siguiente filtro en la instrucción de eliminación:

    Código de ejemplo

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

  7. Sigue estos pasos para comprobar que el comando DELETE ha devuelto el número de filas esperado sin errores:

    1. Ejecuta la siguiente instrucción para identificar las filas en las que se han cambiado las tablas:

      Código de ejemplo

      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:

      Código de ejemplo

      END;

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

      Código de ejemplo

      ROLLBACK;

  8. Una vez que se hayan eliminado las claves duplicadas, puedes volver a indexar el índice.

Corregir índices incoherentes

En las siguientes secciones se describe cómo corregir las incoherencias de índice que se hayan encontrado en tu instancia.

En función de cómo esté configurada tu base de datos, es posible que tengas que hacer lo siguiente con cada índice identificado en los pasos anteriores:

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

  2. Reindexa tu índice.

  3. Si la operación de reindexación falla debido a infracciones de claves externas, debes buscar y corregir estas infracciones.

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

Prepararse para reindexar un índice

Buscar el tamaño del índice

Indexar bases de datos más grandes requiere más tiempo que indexar bases de datos más pequeñas. Para mejorar la velocidad de las operaciones de indexación 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 sepas el tamaño del índice, puedes definir el tamaño de la memoria que usa la operación de reindexación y definir el número de trabajadores paralelos.

Ejecuta la siguiente instrucción para buscar el tamaño del índice, en kilobytes, del índice que quieras corregir:

Código de ejemplo

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 instrucción es similar al siguiente:

Salida

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

Definir el tamaño de la memoria que se va a usar para la reindexación

En función del tamaño de tu índice, tal como se ha determinado en la sección anterior, es importante que definas el valor adecuado para el parámetro de configuración maintenance_work_mem. Este parámetro especifica la cantidad de memoria que se debe 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.

En el ejemplo siguiente se muestra cómo definir maintenance_work_mem:

Código de ejemplo

SET maintenance_work_mem TO "1GB";

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

Definir el número de trabajadores paralelos

Para aumentar el número de trabajadores paralelos para la reindexación, defina el parámetro de configuración max_parallel_maintenance_workers en la base de datos. El valor predeterminado de este parámetro es 2, pero se puede asignar un valor superior para aumentar el número de trabajadores de la reindexación. En las instancias con 8 o más núcleos de vCPU, te recomendamos que asignes el valor 4 a la marca max_parallel_maintenance_workers.

A continuación se muestra cómo identificar los valores definidos para estos parámetros:

Código de ejemplo

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

El parámetro max_parallel_maintenance_workers es un subconjunto de max_worker_processes y está limitado por max_parallel_workers. Si necesitas más trabajadores paralelos, aumenta el valor de max_worker_processes y max_parallel_workers.

En el ejemplo siguiente se muestra cómo definir max_parallel_maintenance_workers:

Código de ejemplo

SET max_parallel_maintenance_workers TO 4;

El parámetro max_parallel_maintenance_workers no garantiza la asignación de trabajadores. Para confirmar que la reindexación ha iniciado varios procesos paralelos, ejecuta la siguiente consulta desde otra sesión después de haber iniciado la reindexación:

Código de ejemplo

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 el índice

Puede volver a indexar un índice sin bloquear su carga de trabajo de producción mediante la utilidad pg_repack. Esta utilidad automatiza y simplifica el proceso de reindexación simultánea, lo que te permite reindexar sin tiempo de inactividad. Para llevar a cabo este procedimiento, utiliza la pg_repack versión 1.4.7.

Sigue estos pasos para volver a indexar tu índice con pg_repack:

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

  2. Crea la extensión pg_repack:

    Código de ejemplo

    CREATE EXTENSION pg_repack;

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

    Código de ejemplo

      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 tiene un resultado similar al siguiente:

    Salida

    INFO: repacking index "public.t_i_key"

    Si se ha producido algún error al ejecutar pg_repack, puede corregirlo e intentarlo de nuevo. Una vez que haya corregido todos los índices de clave única y los índices de clave principal, debe comprobar si hay infracciones de clave externa y corregir las que encuentre.

Buscar y corregir infracciones de claves externas

Para obtener información sobre cómo buscar y corregir infracciones de claves externas, consulta el artículo Buscar y corregir infracciones de claves externas.

Monitorizar operaciones de reindexación

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

Sigue estos pasos para identificar las sesiones bloqueadas y en espera y, a continuación, cancelarlas en la operación INDEX:

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

    Código de ejemplo

    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:

    Código de ejemplo

    SELECT pg_cancel_backend(PID);

Verificar que los índices sean coherentes

Debe seguir comprobando si hay incoherencias en los índices de cada índice incoherente. Una vez que hayas corregido todos los índices incoherentes y las infracciones de claves de tu instancia, puedes comprobar que no haya ningún problema siguiendo los pasos de las secciones anteriores: