tablas - modificar el tipo de dato de una columna sql

¿Agregar una nueva columna sin bloqueo de tabla?

Question

En mi proyecto, tener 23 millones de registros y alrededor de 6 campos ha sido indexado de esa tabla.

Anteriormente probé para agregar una columna delta para la búsqueda de Thinking Sphinx, pero se produce la retención del bloqueo de la base de datos completa durante una hora. Después, cuando se agrega el archivo y trato de reconstruir los índices, esta es la consulta que mantiene el bloqueo de la base de datos durante aproximadamente 4 horas:

"update user_messages set delta = false where delta = true"

Bueno, para hacer que el servidor funcione, creé una nueva base de datos de db dump y la promocioné como base de datos para que el servidor pueda activarse.

Ahora lo que estoy buscando es que agregar columna delta en mi tabla con bloqueo de tabla ¿es posible? Y una vez que se agrega la columna delta , ¿por qué se ejecuta la consulta anterior cuando ejecuto el comando de reconstrucción del índice y por qué bloquea el servidor durante tanto tiempo?

PD .: Estoy en Heroku y uso Postgres con el modelo de dika ika.

Answer 1

La actualización en PostgreSQL significa escribir una nueva versión de la fila. Su pregunta no proporciona toda la información, pero eso probablemente signifique escribir millones de filas nuevas.

Si su modelo de datos y el espacio disponible en el disco lo permiten, CREATE una nueva tabla en el fondo y luego, en una transacción: DROP la tabla anterior, RENAME la nueva.
Detalles y advertencias en esta respuesta posterior relacionada en dba.SE.

Además, si una proporción importante de la tabla se ve afectada, elimine todos los índices antes de realizar la UPDATE masiva, ya que todos los índices deben actualizarse también. Es más rápido eliminarlos y volverlos a crear después de que se complete la ACTUALIZACIÓN masiva.

Y finalmente, al crear la nueva tabla en segundo plano: aplique todos los cambios a la vez o cree varias versiones actualizadas de las filas afectadas.

Descargo de responsabilidad: Agregar una nueva columna sin DEFAULT (= DEFAULT NULL ) normalmente no creará una nueva fila y es muy barato per se. Agregarle valores crea nuevas filas.
Detalles en el maunal .

Si no puede eliminar la tabla original debido a restricciones, otra manera rápida es crear una tabla temporal, TRUNCATE la original y la masa INSERT las filas nuevas, ordenadas, si eso ayuda al rendimiento. Todo en una transacción. Algo como esto:

BEGIN

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

CREATE TEMP TABLE tmp AS
SELECT * FROM tbl LIMIT 0; -- copy layout of table

ALTER TABLE tmp ADD column delta boolean; -- NOT DEFAULT

INSERT INTO tmp (col1, col2, ... , delta)
SELECT col1, col2, ... , FALSE
FROM   tbl;                -- copy existing rows plus new value
-- ORDER BY ???

-- DROP all indexes here

TRUNCATE tbl;              -- empty table - truncate is super fast

ALTER TABLE tbl ADD column delta boolean DEFAULT FALSE; -- NOT NULL?

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

-- recreate all indexes here

COMMIT;

Answer 2

Puede agregar otra tabla con la columna, no habrá tales bloqueos largos. Por supuesto, debería haber otra columna, una clave externa a la primera columna.

Para los índices, puede usar "CREATE INDEX CONCURRENTLY", no usa bloqueos demasiado pesados ​​en esta tabla http://www.postgresql.org/docs/9.1/static/sql-createindex.html .