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 Cloud SQL 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 descargar 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

Crea una copia de seguridad de tu base de datos

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

Precaución: Si se crea una copia de seguridad de una base de datos que ya tiene incoherencias en los índices, estas se mantendrán cuando se restaure.

Definir el permiso cloudsqlsuperuser

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

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.

Instalar la extensión amcheck

Para comprobar si hay incoherencias en los índices, 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 cómo instalar una nueva versión de mantenimiento de destino, consulta Mantenimiento de autoservicio.

Para instalar amcheck para PostgreSQL 9.6, ejecuta la siguiente instrucción:

  CREATE EXTENSION amcheck_next;
  

Si aparece un error que indica que no se ha podido abrir el archivo de control de la extensión, comprueba que estás ejecutando la versión de mantenimiento de destino correcta (POSTGRES_9_6_24.R20220710.01_12).

PostgreSQL 10 y versiones posteriores

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

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

Deberías obtener un resultado similar al siguiente:

Salida

  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"
  

Advertencia: Aunque en el resultado no aparezcan avisos de WARNING, puede que siga habiendo infracciones únicas o clave. Para comprobar si hay infracciones, sigue los procedimientos que se indican en el artículo sobre cómo identificar y corregir infracciones de claves únicas y principales.

Para obtener más información sobre cómo ver los registros de PostgreSQL, consulta Ver registros de instancias.

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

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 una operación de reindexación falla debido a un error de infracción de clave duplicada, sigue estos pasos para encontrar y eliminar las claves duplicadas.

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

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

   Código de ejemplo

     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;

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

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

   Advertencia: Si confirmas esta transacción, se modificará tu base de datos y se eliminarán las claves duplicadas. Asegúrate de no eliminar ningún dato que sigas necesitando. Si restauras una copia de seguridad, es posible que se restauren los índices incoherentes.

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.

Nota: Si tu índice tiene infracciones de clave principal y única, debes corregirlas antes de volver a indexar.

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 del índice, tal como se ha determinado en la sección anterior, es importante definir el valor adecuado para 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. Para obtener más información, consulta Definir una marca de base de datos.

Nota: El valor de maintenance_work_mem se define para cada sesión. No es necesario que reinicies la instancia después de cambiar el valor. Sin embargo, si vuelves a conectarte a la base de datos en una nueva sesión, el valor del ajuste anterior no se guardará.

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

Puedes aumentar el número de trabajadores paralelos para la reindexación configurando el parámetro max_parallel_maintenance_workers en las bases de datos que usen PostgreSQL 11 o versiones posteriores. El valor predeterminado de este parámetro es 2, pero se puede asignar un valor más alto 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.

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

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, sobre todo en las versiones 11 y anteriores de PostgreSQL, que no tienen la operación REINDEX CONCURRENTLY. 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.

   Debian GNU/Linux 11

   Para mayor comodidad, recomendamos a los usuarios de Debian Linux que descarguen e instalen este ejecutable binario precompilado para la plataforma Linux x86_64.

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

   ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

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

   Autocompilación

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

   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
     

   Nota: pg_repack no 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 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:

• Comprobar las incoherencias de un índice

• Comprobar si hay infracciones de claves únicas