Errores comunes al cambiar el esquema de la DB en Postgres

 (postgres.ai)
5 puntos por GN⁺ 2024-04-29 | 1 comentarios | Compartir por WhatsApp
  • Las migraciones de esquema en Postgres pueden provocar bloqueos, reescritura de tablas y retraso de replicación que terminen en incidentes operativos, por lo que el riesgo es especialmente alto en entornos OLTP grandes
  • El riesgo se concentra en operaciones que provocan escaneos completos y bloqueos largos, como agregar DEFAULT y NOT NULL al mismo tiempo, crear índices sin CONCURRENTLY, eliminar columnas de inmediato, hacer cambios de tipo no seguros y agregar claves foráneas sin validación
  • Desde PostgreSQL 11, el costo de algunas adiciones de columnas se redujo, pero para los índices sigue siendo necesario CREATE INDEX CONCURRENTLY, y para las claves foráneas hacen falta procedimientos como NOT VALID seguido de VALIDATE CONSTRAINT para reducir el impacto operativo
  • Los cambios masivos conviene dividirlos en lotes pequeños y revisar también las réplicas de lectura, el retraso de replicación, los objetos dependientes y si las instancias existentes de la aplicación todavía hacen referencia a esas columnas
  • Hay que probar primero sobre datos de tamaño de producción, y las operaciones destructivas deben ejecutarse solo después de contar con un despliegue en varias etapas y un plan de rollback validado

Supuestos de las migraciones de esquema

  • Aquí, una migración de DB no significa cambiar de DBMS, sino un cambio en el esquema de la DB
  • Los cambios objetivo tienen tres características
    • Cambios versionados con un identificador único para cada cambio y un procedimiento automatizado de aplicación
    • Cambios inmutables que no se modifican después de aplicarse en producción, sino que se agregan como cambios nuevos
    • Cambios incrementales en los que el esquema de la base de datos evoluciona paso a paso
  • El enfoque está en casos de uso OLTP como aplicaciones móviles y web, donde una ejecución de consulta de más de 1 segundo normalmente se considera demasiado lenta
  • En bases de datos pequeñas y con poca actividad, algunos problemas pueden pasar desapercibidos, pero con una escala de unos 10 TiB y una carga de 10⁴ a 10⁵ transacciones por segundo, la mayoría de los problemas pueden aparecer
  • Database Lab Engine se usa para desarrollo y pruebas con clones ligeros, y permite clonar una base de datos de 10 TiB en menos de 10 segundos para verificar el riesgo de cambios de esquema antes del despliegue
  • La GitLab Migration Style Guide es un material de referencia basado en la experiencia de desplegar automáticamente numerosos cambios de esquema en Postgres

Agregar columnas y reescritura de tablas

  • Agregar una columna con DEFAULT y NOT NULL al mismo tiempo es especialmente riesgoso en versiones antiguas de PostgreSQL
    • Antes de PostgreSQL 11, era necesaria una reescritura completa de la tabla
    • En tablas grandes, esto puede tardar horas o días, y durante ese tiempo se producen bloqueos de escritura
  • Un ejemplo riesgoso es el siguiente
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • Un procedimiento más seguro es dividir la adición de la columna, la actualización de datos y la adición de la restricción
    • Primero se agrega la columna sin NOT NULL
    • Si hace falta, se actualizan las filas existentes
    • Después se agrega la restricción NOT NULL
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • En PostgreSQL 11 o superior, agregar una columna con un valor DEFAULT no volátil ya no requiere reescritura de tabla

Creación de índices y adición de claves foráneas

  • Si se crea un índice sin CONCURRENTLY, la creación estándar toma un bloqueo exclusivo sobre la tabla
    • Hasta que termine la creación del índice, pueden bloquearse todas las escrituras y parte de las lecturas
  • Un ejemplo riesgoso es el siguiente
CREATE INDEX idx_users_email ON users(email);
  • En operación activa, es más seguro usar CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY tiene limitaciones
    • Tarda más, pero no bloquea el acceso a la tabla
    • No puede usarse dentro de un bloque de transacción
    • Si falla, puede dejar un índice inválido que habrá que eliminar
  • Si se agrega directamente una restricción de clave foránea a una tabla grande, se escanea toda la tabla para validar los datos existentes y eso provoca bloqueos largos
  • Un procedimiento más seguro es agregar primero la restricción con NOT VALID y luego validarla en un momento de poco tráfico
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

Eliminación de columnas y cambios de tipo

  • Si una columna se elimina de inmediato en producción, pueden producirse errores en la aplicación si el código todavía hace referencia a ella
  • La eliminación de columnas debe hacerse en varias etapas
    • Primero se despliega el código de la aplicación que ya no usa esa columna
    • Luego se espera a que todas las instancias antiguas de la aplicación sean reemplazadas
    • En una migración separada, se elimina la columna
  • Cambiar el tipo de una columna puede causar reescritura de tabla o problemas de compatibilidad
    • Puede derivar en downtime, pérdida de datos o errores de aplicación
  • Ejemplos problemáticos
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • Para pasar de integer a bigint, hace falta un procedimiento en varias etapas usando una columna nueva
  • Si se quiere reducir la longitud de un varchar, primero hay que revisar los datos y evaluar si el cambio realmente es necesario

Cambios masivos, replicación y objetos dependientes

  • Hay que evitar las migraciones que modifican demasiados datos en una sola transacción
    • Aumentan la contención por bloqueos y el uso de memoria
    • Si surge un problema, el tiempo de recuperación se alarga
    • El retraso de replicación puede crecer
  • En migraciones de datos grandes, es más seguro dividirlas en lotes pequeños
  • También hay que revisar el impacto de la migración sobre las réplicas de lectura y el retraso de replicación
    • Una migración grande puede generar un retraso de replicación considerable
    • Puede afectar el rendimiento de las réplicas de lectura
  • También deben verificarse los objetos que dependen de la columna o tabla que se va a modificar
    • Si se pasan por alto vistas, funciones, triggers u otros objetos dependientes, puede haber fallas en cadena o necesidad de intervención manual adicional

Pruebas y plan de rollback

  • Si una migración solo se prueba con un dataset pequeño de desarrollo, es difícil verificar las características de rendimiento con datasets grandes
  • Debe probarse sobre un clon con datos de tamaño de producción, y pueden usarse herramientas como Database Lab Engine
  • Si no hay forma de revertir la migración cuando ocurre un problema, un incidente en producción puede convertirse en un downtime prolongado
  • En especial para operaciones destructivas, hace falta un plan de rollback validado
  • Los principios básicos de un cambio de esquema seguro son los siguientes
    • Probar sobre datos de tamaño de producción
    • Usar un enfoque en varias etapas para operaciones riesgosas
    • Aprovechar funciones de PostgreSQL como CONCURRENTLY y NOT VALID
    • Monitorear el rendimiento y el impacto en la replicación
    • Tener siempre preparado un plan de rollback

Lecturas relacionadas

1 comentarios

 
GN⁺ 2024-04-29
Opiniones en Hacker News

  • Me gusta mucho Postgres, pero la mayor parte de este artículo trata sobre cosas evitables y dignas de tener en cuenta. Dicho eso, creo que lo peor de Postgres es la gestión de roles.
    La funcionalidad es potente y, si se usa bien, puede ser excelente, pero el proceso para hacer que realmente funcione se siente como magia negra. En muchas partes, la interfaz parece una serie de conjuros crípticos que uno no sabe si se comportarán como espera, y es una forma terrible de administrar algo tan importante.
    El manual sobre esta parte también es escueto: apenas explica, para casos de uso acotados, más o menos cómo debería funcionar. Si no sale como esperabas, tienes que encontrar por prueba y error qué hiciste mal, y aun así no queda claro cuál es la forma correcta. Migrar una DB con permisos de usuario complejos es un verdadero suplicio.
    Siento que debería reservarme más o menos un mes para escribir un cookbook. Si con eso al menos una persona evita dormirse llorando, habrá valido la pena.

    • Coincido en que el IAM de PostgreSQL es complejo. Lo es porque la jerarquía de objetos tiene 3 niveles: Database, Schema y Tables, y porque también hay permisos otorgados implícitamente al dueño de los objetos de la DB.
      Para hacer SELECT en una tabla necesitas CONNECT en la Database y USAGE en el Schema, que se otorgan implícitamente al dueño del Schema. También necesitas SELECT en la Table, que se otorga implícitamente al dueño de la tabla.
      Para ver los permisos hay que entender las entradas ACL con el formato grantee=privilege-abbreviation[]/grantor:. Los permisos de Database se ven con \l+, los de Schema con \dn+ y los de Table con \dp+.
      La lista de permisos está aquí. Por ejemplo, user=arwdDxt/postgres significa que el rol postgres le dio todos los permisos al usuario.
      Si la columna grantee de un objeto está vacía, puede significar los permisos predeterminados del dueño, es decir, todos los permisos, o permisos para el rol PUBLIC, que abarca todos los roles existentes. Un ejemplo es =r/postgres.
      Usar el Schema public lo vuelve todavía más confuso. Como el Schema tiene permiso CREATE, si creas una tabla con el mismo usuario que consulta los datos, obtiene permisos de dueño por defecto y puede consultarla de inmediato.
    • La documentación de postgREST, que depende de roles para la autenticación, tampoco parece muy detallada: https://postgrest.org/en/v12/explanations/db_authz.html
      Si te pones en serio a escribir un cookbook sobre roles en Postgres y abres algo tipo Kickstarter, creo que sería de los primeros en apoyarlo.
    • Coincido con eso de que “hacer que funcione parece magia negra”. El año pasado implementé un servidor postgREST sencillo con seguridad a nivel de fila, y llegar hasta ahí fue bastante difícil.
      Aun así, una vez que funcionó se sintió realmente mágico, y los mecanismos relacionados en sí mismos resultaron ser sorprendentemente simples.
    • Creo que leería algo así. La gestión de roles implica muchas conjeturas y, como resultado, con demasiada frecuencia los roles terminan con permisos excesivos.
    • Por favor, escríbelo. Por algo así estaría dispuesto a pagar unos 20 dólares.

  • Si vas a ejecutar migraciones de Schema en producción, deberías usar lock_timeout.
    Incluso cambios que parecen inofensivos y que en pruebas terminan casi al instante, como eliminar una tabla con claves foráneas o eliminar una clave foránea, pueden encontrarse con conflictos de bloqueo en una DB de producción con mucho tráfico debido a transacciones existentes o a autovacuum.
    Ese ALTER tomará un bloqueo ACCESS EXCLUSIVE mientras espera el bloqueo de la primera transacción, y entonces todas las consultas sobre la tabla bloqueada quedarán detenidas.
    Si operas Postgres a cierta escala, este tipo de conflicto es cuestión de tiempo. Al configurar lock_timeout, en vez de esperar bloqueando todas las demás consultas, la migración falla cuando se supera el tiempo límite.

    • statement_timeout incluye también el tiempo de espera por bloqueos, así que permite estimar mejor el impacto sobre una tabla ocupada.
      Si fijas el límite en 5 segundos, sabes que la interrupción total será como máximo de 5 segundos, y después las transacciones continúan. Si usas solo lock_timeout, no puedes controlar cuánto tardará el trabajo después de obtener el bloqueo, y por el tráfico concurrente puede ser rápido o lento.
    • Según la versión de Postgres, cambia bastante si ciertas consultas DML toman o no bloqueos exclusivos.
      Me pregunto si existe una buena forma de analizar una consulta y saber qué tipo de bloqueo tomará. Cuando no estoy seguro, siempre termino releyendo la documentación.
    • Buen consejo. Aunque técnicamente tenía entendido que no es que ya haya adquirido el bloqueo ACCESS EXCLUSIVE y esté esperando, sino que espera por la cola de bloqueos.
      El ALTER está en estado de espera hasta que se liberen bloqueos de menor nivel que ACCESS EXCLUSIVE.
    • Si haces eso, puede que el ALTER nunca llegue a ejecutarse. Si hay suficiente tráfico sobre esa tabla, puede pasar.
      En ese caso, si la app puede recuperarse, creo que lo mejor es matar las otras consultas en curso que están bloqueando el ALTER.

  • Consulto varias veces por semana la guía Safe Migrations in Ecto de Fly.io. Ecto es el adaptador de DB de Elixir.
    Es una referencia muy útil para comprobar rápidamente si una migración básica alcanza o si hace falta un procedimiento más complejo.
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • Lo que más me sorprendió de los índices de Postgres cuando era principiante fue que un índice UNIQUE podía afectar los resultados de consultas concurrentes debido a bloqueos adicionales.
    Una consulta como INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);, si se ejecuta al mismo tiempo en el modo predeterminado, puede insertar valores bar duplicados. Eso se debe a que una transacción puede no ver el nuevo valor máximo creado por otra transacción.
    Uno pensaría que, al agregar un índice UNIQUE, la transacción que “pierde” recibiría un error de restricción, pero en realidad ambas transacciones tienen éxito y la condición de carrera también desaparece.

    • Eso no es cierto. La subtransacción que pierde en la competencia por el índice se aborta.
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • Si lo que quieres decir es que, incluso con un índice UNIQUE, ambas inserciones tienen éxito y al final quedan valores duplicados, entonces, si eso es cierto, es un bug.
    • Si no me equivoco, se puede hacer sin interrupciones creando un índice normal con CONCURRENTLY y luego creando una restricción UNIQUE no validada.
      Esa restricción solo se aplica a nuevos INSERT/UPDATE. Después, si ejecutas VALIDATE sobre la restricción, se convierte en una restricción UNIQUE completa.
    • Si eso te parece sorprendente, creo que es porque has estado demasiado expuesto a lenguajes imperativos.
      Estoy de acuerdo en que es algo común, pero el problema no es tanto Postgres como el desarrollo de software en general.
    • ¿Con qué nivel de aislamiento pasa eso?

  • Por este tipo de trampas creé Reshape [0], con el objetivo de automatizar migraciones de Schema sin downtime.
    No puedo decir que evite todos los problemas, pero estoy construyendo un producto nuevo con ese objetivo. Si te interesa esta área, especialmente Postgres, me gustaría saber de ti: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • ¿Hay posibilidad de que también funcione en crdb?

  • Otro error que veo seguido es copiar una tabla y olvidarse de los índices.
    CREATE TABLE SELECT * FROM WHERE <> no funciona así. La gente suele hacer esto cuando quiere crear una tabla de respaldo o hacer eliminaciones masivas.

    • Si estás creando una tabla de respaldo, es decir, si vas a hacer una operación compleja y ambigua que podría romperse de formas impredecibles de inmediato, no me importan en absoluto los índices ni las restricciones.
      Lo que quiero es una copia de los datos que exista de inmediato, probablemente no la vaya a usar, para no tener que restaurar desde el backup de la DB y el WAL. Crear índices sería desperdiciar tiempo de servidor y espacio en disco.
      Si algo sale mal o realmente los necesito, puedo crear esos índices después.
    • Entonces, ¿también podrías decir cuál sería la forma adecuada?

  • La sección “Caso 2. Mal uso de IF [NOT] EXISTS” no presentó un buen ejemplo de mal uso.
    Y, en la práctica, usarlo así está bien. Es limpio, simple y no tiene trampas ocultas. Si solo tienes unas pocas tablas, una herramienta de migración de Schema es una carga excesiva.

    • La trampa es simple: “ocultar el problema con lógica y agregar riesgo de un estado anómalo”.
      Ponerle una curita a datos malos no resuelve el problema, solo lo oculta. Según el tipo de problema, puede explotar más adelante de una forma inesperada y en el peor momento.
      En este caso, los “datos malos” son tablas, columnas o vistas que deberían existir o no existir, pero están en el estado contrario. ¿Por qué existe una tabla que todavía no debería existir? ¿Falló el borrado? ¿El Schema de la tabla existente es correcto? ¿Se ejecutó por error dos veces la misma migración?
      Después de cada migración, el Schema debe estar en el estado exacto. Si una migración contiene IF [NOT] EXISTS, significa que el Schema no quedó en el estado correcto después de la migración anterior. No es bueno no tener certeza sobre el estado del Schema.
    • Creo que el artículo explicó bastante bien el mal uso. La idea central es que los cambios de Schema por rutas separadas son un problema de proceso y flujo de trabajo, así que hay que resolverlo directamente.
      ¿Qué pasa si una columna de una tabla ya existente es distinta de la que la migración intenta crear? IF EXISTS hace que la migración sea exitosa, pero deja el Schema en mal estado. En estos casos, es mejor que la migración falle rápido.

  • Una observación menor sobre usar int4 como clave primaria sustituta.
    Lo importante no es el tamaño de la tabla, sino el tamaño del índice, ¿no? El tamaño de la tabla ya incluye un encabezado de 23 bytes y padding de alineación, así que una diferencia de 4 bytes no cambia mucho. Pero si puedes cargar más del índice en memoria, puede haber una ventaja. Cada entrada del índice tiene un encabezado de 8 bytes.
    Además, los 1.000 millones de filas del ejemplo están demasiado cerca del máximo de int4, lo cual inquieta.
    Aun así, el artículo es excelente.

    • Exacto. Está el tamaño del índice y también el tamaño en disco. Postgres empaqueta las filas de la tabla de forma compacta en disco, pero no en RAM.
      ¿Eso significa que una página de 8 KB en disco podría ocupar más de 8 KB en RAM?
      Parece que solo afecta a la memoria de trabajo de los datos de filas de la tabla. Sigue siendo importante. Sobre todo porque en Postgres las filas están en orden aleatorio, lo que es terrible para la localidad en consultas por rango. Aunque no creo que sea una revelación decisiva.

  • Soy un desarrollador que, en general, ha estado bastante protegido de los problemas relacionados con DB. Dentro de Django sé crear migraciones, crear tablas de modelos y hacer consultas con el ORM, pero muchas de las cosas que pasan por dentro me parecen magia negra.
    Ahora que estoy iniciando una empresa, me preocupa tener que enfrentar estos problemas y resolverlos solo. ¿Cómo debería abordarlo para aprender qué hacer en un entorno de desarrollo?

    • Falla y aprende de tus errores. O contrata a un desarrollador, fallen juntos y aprendan juntos.

  • Me gusta Postgres, pero de verdad odio que no tenga una forma integrada de hacer actualizaciones/eliminaciones por lotes.
    Es la parte más frustrante, y cada vez que me topo con esa pared termino reescribiendo casi todos los meses un batcher.