¿La mejor forma de completar una nueva columna en una tabla grande?

• ¿Qué causa que INSERT grande se ralentice y el uso del disco explote?

El siguiente código usa una función del uuid-oss módulo adicional.

• Bloquea la tabla contra cambios simultáneos en el SHARE modo (que aún permite lecturas simultáneas). Los intentos de escribir en la tabla esperarán y eventualmente fallarán. Vea a continuación.

• Copie toda la tabla mientras completa la nueva columna sobre la marcha, posiblemente ordenando las filas de manera favorable mientras está en ello.
  Si va a reordenar las filas, asegúrese de establecer work_mem lo suficientemente alto como para hacer la clasificación en RAM o tan alto como pueda pagar (solo para su sesión, no globalmente).

• Luego agregue restricciones, claves externas, índices, disparadores, etc. a la nueva mesa. Cuando se actualizan grandes porciones de una tabla, es mucho más rápido crear índices desde cero que agregar filas de forma iterativa. Consejos relacionados en el manual.

• Cuando la nueva tabla esté lista, elimine la antigua y cambie el nombre de la nueva. para convertirlo en un reemplazo directo. Solo este último paso adquiere un bloqueo exclusivo en la tabla anterior para el resto de la transacción, que ahora debería ser muy corta.
  También requiere que elimine cualquier objeto según el tipo de tabla (vistas, funciones que usan el tipo de tabla en la firma, …) y luego recrearlos.

• Hágalo todo en una transacción para evitar estados incompletos.

BEGIN; LOCK TABLE tbl IN SHARE MODE; SET LOCAL work_mem = "???? MB"; -- just for this transaction CREATE TABLE tbl_new AS SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order> FROM tbl ORDER BY ??; -- optionally order rows favorably while being at it. ALTER TABLE tbl_new ALTER COLUMN tbl_uuid SET NOT NULL , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid); -- more constraints, indices, triggers? DROP TABLE tbl; ALTER TABLE tbl_new RENAME tbl; -- recreate views etc. if any COMMIT; 

Esto debería ser más rápido. Cualquier otro método de actualización en el lugar tiene que reescribir toda la tabla también, solo que de una manera más cara. Solo tomaría ese camino si no tiene suficiente espacio libre en el disco o no puede permitirse bloquear toda la tabla o generar errores para intentos de escritura simultáneos.

¿Qué sucede con las escrituras simultáneas?

Otra transacción (en otras sesiones) intentando INSERT / UPDATE / DELETE en la misma tabla después de que su transacción haya tomado el SHARE bloqueo, esperará hasta que se libere el bloqueo o se active un tiempo de espera, lo que ocurra primero. Ellos fail de cualquier manera, ya que la tabla en la que estaban tratando de escribir se eliminó debajo de ellos.

La nueva tabla tiene una nueva tabla OID, pero la transacción concurrente ya ha resuelto el nombre de la tabla al OID de la tabla anterior . Cuando finalmente se libera el bloqueo, intentan bloquear la tabla ellos mismos antes de escribir en ella y encontrarla » se ha ido.Postgres responderá:

ERROR: could not open relation with OID 123456

Donde 123456 es el OID de la tabla anterior. Debe detectar esa excepción y volver a intentar las consultas en el código de su aplicación para evitarla.

Si no puede permitirse que eso suceda, debe mantener su tabla original.

Mantener la tabla existente, alternativa 1

Actualización en su lugar (posiblemente ejecutando la actualización en segmentos pequeños a la vez) antes de agregar la restricción NOT NULL . Agregar una nueva columna con valores NULL y sin la restricción NOT NULL es barato.
Desde Postgres 9.2 también puede crear una restricción CHECK con NOT VALID :

La restricción seguirá se aplicará contra inserciones o actualizaciones posteriores

Eso le permite actualizar filas peu à peu – en varias transacciones independientes . Esto evita mantener bloqueos de hileras durante demasiado tiempo y también permite reutilizar hileras muertas. (Tendrá que ejecutar VACUUM manualmente si no hay suficiente tiempo para que se active la aspiración automática). Por último, agregue NOT NULL restricción y elimine la NOT VALID CHECK restricción:

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID; -- update rows in multiple batches in separate transactions -- possibly run VACUUM between transactions ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL; ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null; 

Respuesta relacionada sobre NOT VALID con más detalle:

• Deshabilite todas las restricciones y verificaciones de tablas mientras restaura un volcado

Mantener la tabla existente, alternativa 2

Preparar el nuevo estado en una tabla temporal , TRUNCATE el original y rellene de la tabla temporal. Todo en una transacción . Usted todavía es necesario realizar un SHARE bloqueo antes pr preparando la nueva tabla para evitar perder escrituras simultáneas.

Detalles en estas respuestas relacionadas sobre SO:

• La mejor manera de eliminar millones de filas por ID
• ¿Agregar una nueva columna sin bloqueo de tabla?

Comentarios

• ¡Respuesta fantástica! Exactamente la información que estaba buscando. Dos preguntas 1. ¿Tiene alguna idea de una manera fácil de probar cuánto tiempo tomaría una acción como esta? 2. Si toma 5 minutos, ¿qué sucede con las acciones que intentan actualizar una fila en esa tabla durante esos 5 minutos?
• @CollinPeters: 1. El león 'LOCK hasta y excluyendo el DROP. Solo pude pronunciar conjeturas locas e inútiles. En cuanto a 2., considere el apéndice a mi respuesta.
• @ErwinBrandstetter Continúe con la recreación de vistas, por lo que si tengo una docena de vistas que todavía usan la tabla antigua (oid) después del cambio de nombre de la tabla. ¿Hay alguna forma de realizar un reemplazo profundo en lugar de volver a ejecutar la actualización / creación de la vista completa?
• @CodeFarmer: Si simplemente cambia el nombre de una tabla, las vistas siguen funcionando con la tabla renombrada. Para que las vistas usen la tabla nueva en su lugar, debe volver a crearlas basándose en la tabla nueva. (También para permitir que se elimine la tabla anterior.) No hay forma (práctica) de evitarlo.
• ya que 9.2 postgres no ' t lanza could not open relation with OID 123456