repetidos - no repetir registros en consulta sql

La mejor manera de eliminar millones de filas por ID

Question

Necesito eliminar alrededor de 2 millones de filas de mi base de datos PG. Tengo una lista de ID que necesito eliminar. Sin embargo, cualquier forma en que intente hacerlo es tomar días.

Intenté ponerlos en una tabla y hacerlo en lotes de 100. 4 días después, esto todavía se está ejecutando con solo 297268 filas eliminadas. (Tuve que seleccionar 100 identificadores de una tabla de ID, eliminar donde EN esa lista, eliminar de la tabla de identificadores los 100 que seleccioné).

Lo intenté:

DELETE FROM tbl WHERE id IN (select * from ids)

Eso lleva una eternidad, también. Es difícil calcular cuánto tiempo, ya que no puedo ver el progreso hasta que termine, pero la consulta aún se estaba ejecutando después de 2 días.

Solo estoy buscando la forma más efectiva de eliminar de una tabla cuando sé que los identificadores específicos deben eliminarse, y hay millones de ID.

Answer 1

Dos posibles respuestas:

1. Su tabla puede tener muchas restricciones o factores desencadenantes asociados cuando intenta eliminar un registro. Se incurrirá en muchos ciclos de procesador y comprobación de otras tablas.

2. Es posible que deba colocar esta declaración dentro de una transacción.

Answer 2

La forma más fácil de hacer esto sería eliminar todas sus restricciones y luego hacer la eliminación.

Answer 3

Primero asegúrese de tener un índice en los campos ID, tanto en la tabla de la que desea eliminar como en la tabla que está utilizando para eliminar ID.

100 a la vez parece demasiado pequeño. Pruebe con 1000 o 10000.

No es necesario eliminar nada de la tabla de ID de eliminación. Agregue una nueva columna para un número de lote y llénela con 1000 para el lote 1, 1000 para el lote 2, etc. y asegúrese de que la consulta de eliminación incluya el número de lote.

Answer 4

Puede intentar copiar todos los datos en la tabla excepto los ID que desea eliminar en una nueva tabla, luego cambiar el nombre y luego intercambiar las tablas (siempre que tenga suficientes recursos para hacerlo).

Este no es un consejo de expertos.

Answer 5

Sabemos que el rendimiento de actualización / eliminación de PostgreSQL no es tan poderoso como Oracle. Cuando necesitamos eliminar millones o decenas de millones de filas, es realmente difícil y lleva mucho tiempo.

Sin embargo, aún podemos hacer esto en dbs de producción. La siguiente es mi idea:

En primer lugar, debemos crear una tabla de registro con 2 columnas: id y flag ( id refiere a la identificación que desea eliminar, la flag puede ser Y o null , con Y significa que el registro se borró correctamente).

Más tarde, creamos una función. Hacemos la tarea de borrar cada 10.000 filas. Puedes ver más detalles en mi blog . Aunque está en chino, todavía puede obtener la información que desea del código SQL allí.

Asegúrese de que la columna de id de ambas tablas sean índices, ya que se ejecutará más rápido.

Answer 6

Si la tabla a la que está eliminando está referenciada por some_other_table (y no desea soltar las claves externas ni siquiera temporalmente), asegúrese de tener un índice en la columna de referencia en some_other_table .

Tuve un problema similar y usé auto_explain con auto_explain.log_nested_statements = true , que reveló que la delete realmente estaba haciendo seq_scans en some_other_table :

    Query Text: SELECT 1 FROM ONLY "public"."some_other_table" x WHERE $1 OPERATOR(pg_catalog.=) "id" FOR KEY SHARE OF x    
    LockRows  (cost=[...])  
      ->  Seq Scan on some_other_table x  (cost=[...])  
            Filter: ($1 = id)

Aparentemente está intentando bloquear las filas de referencia en la otra tabla (que no debería existir, o la eliminación fallará). Después de crear índices en las tablas de referencia, la eliminación fue de órdenes de magnitud más rápida.

Answer 7

Todo depende ...

• Eliminar todos los índices (excepto el que está en la ID que necesita para eliminar)
  Recupérelos luego (= mucho más rápido que las actualizaciones incrementales de los índices)

• Compruebe si tiene activadores que se pueden borrar / deshabilitar de manera segura

• ¿Llaves foráneas hacen referencia a su mesa? Pueden ser eliminados? ¿Eliminado temporalmente?

• Dependiendo de su configuración de autovacío, puede ayudar ejecutar VACUUM ANALYZE antes de la operación.

• Suponiendo que no hay acceso de escritura concurrente a las tablas involucradas o puede tener que bloquear tablas exclusivamente o esta ruta puede no ser para usted en absoluto.

• Algunos de los puntos enumerados en el capítulo relacionado del manual Poblando una base de datos también pueden ser útiles, dependiendo de su configuración.

• Si elimina grandes porciones de la tabla y el resto se adapta a la memoria RAM, la forma más rápida y sencilla sería esta:

SET temp_buffers = ''1000MB''; -- or whatever you can spare temporarily

CREATE TEMP TABLE tmp AS
SELECT t.*
FROM   tbl t
LEFT   JOIN del_list d USING (id)
WHERE  d.id IS NULL;      -- copy surviving rows into temporary table

TRUNCATE tbl;             -- empty table - truncate is very fast for big tables

INSERT INTO tbl
SELECT * FROM tmp;        -- insert back surviving rows.

De esta forma, no tiene que volver a crear vistas, claves externas u otros objetos dependientes. Lea sobre la configuración temp_buffers en el manual . Este método es rápido siempre que la tabla se ajuste a la memoria, o al menos a la mayor parte. Tenga en cuenta que puede perder datos si su servidor falla en el medio de esta operación. Puede envolver todo en una transacción para que sea más seguro.

Ejecute ANALYZE después. O VACUUM ANALYZE si no siguió la ruta truncada, o VACUUM FULL ANALYZE si desea llevarlo al tamaño mínimo. Para grandes tablas, considere las alternativas CLUSTER / pg_repack :

• Optimizar el rango de consultas de marca de tiempo de Postgres

Para las tablas pequeñas, un DELETE simple en lugar de TRUNCATE suele ser más rápido:

DELETE FROM tbl t
USING  del_list d
WHERE  t.id = d.id;

Lea la sección de Notas para TRUNCATE en el manual . En particular (como Pedro también señaló en su comentario ):

TRUNCATE no se puede utilizar en una tabla que tenga referencias de clave externa de otras tablas, a menos que todas esas tablas también se trunquen en el mismo comando. [...]

Y:

TRUNCATE no disparará ningún desencadenador ON DELETE que pueda existir para las tablas.