update tutorial postgres multiple example delete columns postgresql transactions sql-update plpgsql dblink

tutorial - ¿Cómo realizo grandes actualizaciones sin bloqueo en PostgreSQL?



update postgresql example (8)

Quiero hacer una gran actualización en una tabla en PostgreSQL, pero no necesito mantener la integridad transaccional en toda la operación, porque sé que la columna que estoy modificando no se va a escribir o leer durante la actualización. Quiero saber si hay una manera fácil en la consola psql de hacer que estos tipos de operaciones sean más rápidos.

Por ejemplo, supongamos que tengo una tabla llamada "pedidos" con 35 millones de filas, y quiero hacer esto:

UPDATE orders SET status = null;

Para evitar ser desviado a una discusión offtópica, supongamos que todos los valores de estado para las 35 millones de columnas están actualmente configurados en el mismo valor (no nulo), lo que hace que el índice sea inútil.

El problema con esta afirmación es que lleva mucho tiempo entrar en vigor (únicamente debido al bloqueo) y todas las filas modificadas se bloquean hasta que se completa la actualización. Esta actualización puede tomar 5 horas, mientras que algo como

UPDATE orders SET status = null WHERE (order_id > 0 and order_id < 1000000);

puede tomar 1 minuto Más de 35 millones de filas, hacer lo anterior y dividirlo en trozos de 35 solo tomaría 35 minutos y me ahorraría 4 horas y 25 minutos.

Podría dividirlo aún más con una secuencia de comandos (usando pseudocódigo aquí):

for (i = 0 to 3500) { db_operation ("UPDATE orders SET status = null WHERE (order_id >" + (i*1000)" + " AND order_id <" + ((i+1)*1000) " + ")"); }

Esta operación puede completarse en solo unos minutos, en lugar de 35.

Entonces eso se reduce a lo que realmente estoy preguntando. No quiero escribir una secuencia de comandos freaking para descomponer las operaciones cada vez que quiero hacer una gran actualización única como esta. ¿Hay alguna manera de lograr lo que quiero por completo dentro de SQL?

Columna / Fila

... No necesito mantener la integridad transaccional en toda la operación, porque sé que la columna que estoy modificando no se va a escribir ni leer durante la actualización.

Cualquier UPDATE en el modelo MVCC de PostgreSQL escribe una nueva versión de toda la fila . Si las transacciones simultáneas cambian cualquier columna de la misma fila, surgen problemas de concurrencia que consumen tiempo. Detalles en el manual. Saber que la misma columna no se verá afectada por transacciones simultáneas evita algunas posibles complicaciones, pero no otras.

Índice

Para evitar ser desviado a una discusión offtópica, supongamos que todos los valores de estado para las 35 millones de columnas están actualmente configurados en el mismo valor (no nulo), lo que hace que el índice sea inútil.

Al actualizar toda la tabla (o partes principales de ella), Postgres nunca usa un índice . Un escaneo secuencial es más rápido cuando se deben leer todas o la mayoría de las filas. Por el contrario: el mantenimiento del índice significa un costo adicional para la UPDATE .

Actuación

Por ejemplo, supongamos que tengo una tabla llamada "pedidos" con 35 millones de filas, y quiero hacer esto:

UPDATE orders SET status = null;

Entiendo que está buscando una solución más general (ver a continuación). Pero para abordar la pregunta real : esto se puede resolver en cuestión de milisegundos , independientemente del tamaño de la tabla:

ALTER TABLE orders DROP column status , ADD column status text;

Por documentación:

Cuando se agrega una columna con ADD COLUMN , todas las filas existentes en la tabla se inicializan con el valor predeterminado de la columna ( NULL si no se especifica ninguna cláusula DEFAULT ). Si no hay una cláusula DEFAULT, esto es simplemente un cambio de metadatos ...

Y:

El formulario DROP COLUMN no elimina físicamente la columna, sino que simplemente la hace invisible para las operaciones de SQL. Las operaciones subsiguientes de inserción y actualización en la tabla almacenarán un valor nulo para la columna. Por lo tanto, eliminar una columna es rápido, pero no reducirá inmediatamente el tamaño en disco de su tabla, ya que el espacio ocupado por la columna eliminada no se recupera. El espacio se recuperará con el tiempo a medida que se actualicen las filas existentes. (Estas afirmaciones no se aplican cuando se suelta la columna del sistema oid, eso se hace con una reescritura inmediata).

Asegúrese de no tener objetos según la columna (restricciones de clave externa, índices, vistas, ...). Tendrás que soltar / recrear esos. Salvo eso, pequeñas operaciones en la tabla de catálogo del sistema pg_attribute hacen el trabajo. Requiere un bloqueo exclusivo en la mesa, lo que puede ser un problema para cargas pesadas concurrentes. Dado que solo lleva unos pocos milisegundos, igual debería estar bien.

Si tiene un valor predeterminado de columna que desea conservar, agréguelo en un comando separado . Hacerlo en el mismo comando lo aplicaría a todas las filas inmediatamente, anulando el efecto. Luego puede actualizar las columnas existentes en batches . Siga el enlace de documentación y lea las Notas en el manual.

Solución general

dblink ha sido mencionado en otra respuesta. Permite el acceso a bases de datos "remotas" de Postgres en conexiones implícitas separadas. La base de datos "remota" puede ser la actual, logrando así "transacciones autónomas" : lo que la función escribe en el db "remoto" se confirma y no se puede revertir.

Esto permite ejecutar una única función que actualiza una gran tabla en partes más pequeñas y cada parte se compromete por separado. Evita aumentar la sobrecarga de transacciones para cantidades muy grandes de filas y, lo que es más importante, libera bloqueos después de cada parte. Esto permite que las operaciones concurrentes procedan sin mucha demora y hace que los bloqueos sean menos probables.

Si no tiene acceso simultáneo, esto no es útil, excepto para evitar ROLLBACK después de una excepción. También considere SAVEPOINT para ese caso.

Renuncia

En primer lugar, muchas transacciones pequeñas son en realidad más caras. Esto solo tiene sentido para las tablas grandes . El punto dulce depende de muchos factores.

Si no está seguro de lo que está haciendo: una sola transacción es el método seguro . Para que esto funcione correctamente, las operaciones simultáneas en la mesa deben seguir el juego. Por ejemplo: las escrituras simultáneas pueden mover una fila a una partición que supuestamente ya está procesada. O las lecturas concurrentes pueden ver estados intermedios inconsistentes. Usted ha sido advertido.

Instrucciones paso a paso

El módulo adicional dblink debe instalarse primero:

La configuración de la conexión con dblink depende en gran medida de la configuración de su clúster de DB y de las políticas de seguridad vigentes. Puede ser complicado Respuesta posterior relacionada con más información sobre cómo conectarse con dblink :

Cree un FOREIGN SERVER y un USER MAPPING como se indica allí para simplificar y agilizar la conexión (a menos que ya tenga uno).
Asumiendo una serial PRIMARY KEY con o sin algunos espacios.

CREATE OR REPLACE FUNCTION f_update_in_steps() RETURNS void AS $func$ DECLARE _step int; -- size of step _cur int; -- current ID (starting with minimum) _max int; -- maximum ID BEGIN SELECT INTO _cur, _max min(order_id), max(order_id) FROM orders; -- 100 slices (steps) hard coded _step := ((_max - _cur) / 100) + 1; -- rounded, possibly a bit too small -- +1 to avoid endless loop for 0 PERFORM dblink_connect(''myserver''); -- your foreign server as instructed above FOR i IN 0..200 LOOP -- 200 >> 100 to make sure we exceed _max PERFORM dblink_exec( $$UPDATE public.orders SET status = ''foo'' WHERE order_id >= $$ || _cur || $$ AND order_id < $$ || _cur + _step || $$ AND status IS DISTINCT FROM ''foo''$$); -- avoid empty update _cur := _cur + _step; EXIT WHEN _cur > _max; -- stop when done (never loop till 200) END LOOP; PERFORM dblink_disconnect(); END $func$ LANGUAGE plpgsql;

Llamada:

SELECT f_update_in_steps();

Puede parametrizar cualquier parte de acuerdo con sus necesidades: el nombre de la tabla, el nombre de la columna, el valor, ... solo asegúrese de desinfectar los identificadores para evitar la inyección de SQL:

Acerca de evitar la ACTUALIZACIÓN vacía:

¿Estás seguro de que esto se debe al bloqueo? No lo creo y hay muchas otras razones posibles. Para descubrirlo, siempre puedes intentar hacer solo el bloqueo. Prueba esto: BEGIN; SELECCIONAR AHORA (); SELECT * FROM order FOR UPDATE; SELECCIONAR AHORA (); RETROCEDER;

Para comprender lo que realmente está sucediendo, primero debe ejecutar un EXPLAIN (EXPLICAR ACTUALIZACIÓN órdenes SET estado ...) y / o EXPLICAR ANALIZAR. Quizás descubras que no tienes suficiente memoria para realizar la ACTUALIZACIÓN de manera eficiente. Si es así, configure work_mem TO ''xxxMB''; podría ser una solución simple.

Además, alinee el registro de PostgreSQL para ver si ocurren algunos problemas relacionados con el rendimiento.

Algunas opciones que no se han mencionado:

Usa el nuevo truco de mesa . Probablemente lo que tendría que hacer en su caso es escribir algunos desencadenantes para manejarlo de modo que los cambios a la tabla original también se propaguen a su copia de tabla, algo así ... ( percona es un ejemplo de algo que lo hace forma de disparo). Otra opción podría ser el trick "crear una nueva columna y luego reemplazar la anterior con ella", para evitar bloqueos (no está claro si ayuda con la velocidad).

Posiblemente calcule el ID máximo, luego genere "todas las consultas que necesita" y páselos como una sola consulta, como update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ... update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ... update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ... entonces podría no hacer tanto bloqueo, y seguir siendo SQL, aunque tienes lógica extra para hacerlo :(

Antes que nada, ¿está seguro de que necesita actualizar todas las filas?

Quizás algunas de las filas ya tienen el status NULL?

Si es así, entonces:

UPDATE orders SET status = null WHERE status is not null;

En cuanto a particionar el cambio, eso no es posible en sql puro. Todas las actualizaciones están en una sola transacción.

Una forma posible de hacerlo en "sql puro" sería instalar dblink, conectarse a la misma base de datos usando dblink, y luego emitir muchas actualizaciones sobre dblink, pero parece exagerado para una tarea tan simple.

Por lo general, simplemente agregando correctamente where resuelve el problema. Si no es así, solo participen de forma manual. Escribir una secuencia de comandos es demasiado; por lo general, puede hacerlo en un simple documento de una sola línea:

perl -e '' for (my $i = 0; $i <= 3500000; $i += 1000) { printf "UPDATE orders SET status = null WHERE status is not null and order_id between %u and %u;/n", $i, $i+999 } ''

Envolví líneas aquí para leer, generalmente es una sola línea. La salida del comando anterior se puede alimentar a psql directamente:

perl -e ''...'' | psql -U ... -d ...

O primero para archivar y luego para psql (en caso de que necesite el archivo más adelante):

perl -e ''...'' > updates.partitioned.sql psql -U ... -d ... -f updates.partitioned.sql

Debe delegar esta columna en otra tabla como esta:

create table order_status ( order_id int not null references orders(order_id) primary key, status int not null );

Entonces su operación de configuración de estado = NULL será instantánea:

truncate order_status;

No soy de ninguna manera un DBA, pero un diseño de base de datos donde con frecuencia tiene que actualizar 35 millones de filas podría tener ... problemas.

Un WHERE status IS NOT NULL simple WHERE status IS NOT NULL puede acelerar bastante las cosas (siempre que tenga un índice de estado). Sin conocer el caso de uso real, supongo que si se ejecuta con frecuencia, una gran parte de las 35 millones de filas podría ya tiene un estado nulo.

Sin embargo, puede hacer bucles dentro de la consulta a través de la instrucción LOOP . Voy a cocinar un pequeño ejemplo:

CREATE OR REPLACE FUNCTION nullstatus(count INTEGER) RETURNS integer AS $$ DECLARE i INTEGER := 0; BEGIN FOR i IN 0..(count/1000 + 1) LOOP UPDATE orders SET status = null WHERE (order_id > (i*1000) and order_id <((i+1)*1000)); RAISE NOTICE ''Count: % and i: %'', count,i; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql;

Luego se puede ejecutar haciendo algo similar a:

SELECT nullstatus(35000000);

Es posible que desee seleccionar el recuento de filas, pero tenga en cuenta que el recuento exacto de filas puede llevar mucho tiempo. El wiki de PostgreSQL tiene un artículo sobre el conteo lento y cómo evitarlo .

Además, la parte de AVISO DE RAISE está allí para realizar un seguimiento de qué tan avanzado está el guión. Si no está monitoreando los avisos, o no le importa, sería mejor dejarlo fuera.

Postgres usa MVCC (control de concurrencia de múltiples versiones), evitando así cualquier bloqueo si usted es el único escritor; cualquier cantidad de lectores concurrentes puede funcionar en la mesa, y no habrá ningún bloqueo.

Entonces, si realmente toma 5 h, debe ser por una razón diferente (por ejemplo, que tiene escrituras concurrentes, contrariamente a su afirmación de que no lo hace).

Yo usaría CTAS:

begin; create table T as select col1, col2, ..., <new value>, colN from orders; drop table orders; alter table T rename to orders; commit;