DELETE en Postgres a gran escala: claves foráneas, caché y tuplas muertas

DELETE tiene mala fama en Postgres. Muchos sistemas que necesitan rendimiento evitan borrar filas y prefieren vaciar tablas enteras con TRUNCATE o eliminar particiones completas por fecha. Peter Kraft y Qian Li, de DBOS, han publicado Postgres DELETE Can Actually Scale, donde explican por qué esa fama está justificada y, a la vez, cómo consiguieron que los borrados escalaran en un sistema que procesa decenas de miles de workflows y pasos por segundo.

Me ha gustado porque no es un artículo de trucos, sino de entender lo que hace Postgres por debajo. Y los dos problemas que se encontraron aparecen en cualquier tabla grande con una política de retención.

Borrar en Postgres no es borrar

Para entender por qué DELETE es caro hay que recordar cómo funciona MVCC (control de concurrencia multiversión). Postgres no elimina la fila al borrarla: la marca con el identificador de la transacción que la ha borrado. Así, las transacciones que empezaron antes la siguen viendo, y las posteriores la ignoran. Es lo que permite que un borrado no sea visible hasta el commit sin tener que bloquear a nadie.

La consecuencia es que cada UPDATE y cada DELETE deja una tupla muerta, una versión antigua de la fila que ocupa espacio hasta que VACUUM (normalmente el autovacuum) la limpia, cuando ya ninguna transacción activa puede necesitarla. Con un volumen pequeño no se nota. Cuando borras millones de filas al día, esas tuplas muertas interactúan con la caché y los índices de formas poco evidentes.

El caso: retención de workflows

DBOS es un motor de ejecución duradera que guarda el estado de los workflows en Postgres. Los usuarios definen una política de retención, del tipo “borra los workflows que terminaron hace más de una semana”, y el sistema la aplica periódicamente borrando en lotes los N workflows más antiguos hasta que no queda ninguno fuera de plazo.

Lo curioso es que borrar los workflows en sí no era el problema. El problema eran sus datos asociados en otras tablas, como step_outputs, donde cada paso de un workflow guarda su resultado.

Primer problema: el ON DELETE CASCADE

El diseño original era el de manual: cada fila de step_outputs tenía una clave foránea hacia su workflow en workflow_status, con ON DELETE CASCADE. Borras el workflow y Postgres borra sus pasos. Integridad referencial impecable.

A gran escala, el rendimiento era pésimo, y la razón es la localidad de la caché. Un workflow ejecuta muchos pasos a lo largo de bastante tiempo, así que sus filas en step_outputs acaban repartidas por todo el disco, mezcladas con las de otros workflows. Cada borrado en cascada obliga a traer una página entera a memoria, modificarla y volver a escribirla, para borrar a lo mejor una sola fila. El resultado: borrar workflows era mucho más lento que ejecutarlos.

La solución fue eliminar las claves foráneas y borrar cada tabla por separado, en fases:

1. Se borran en lote los workflows más antiguos de workflow_status, que ya sin cascada es rápido.
2. Se hacen borrados en lote parecidos en las tablas de datos como step_outputs.

Los dos pasos son rápidos por el mismo motivo: borran filas en el orden físico en que se crearon, así que cada página que se carga en memoria contiene muchas filas que borrar.

Hay un detalle delicado. Los workflows se borran según cuándo terminaron, pero las tablas de datos sólo guardan cuándo se crearon. Para no borrar los datos de un workflow largo que sigue activo, el borrado incluye un anti-join: sólo se eliminan los datos cuyo workflow ya no existe. Con un esquema simplificado, sería algo así:

DELETE FROM step_outputs s
WHERE s.created_at <= $1  -- límite del lote
  AND NOT EXISTS (
    SELECT 1
    FROM workflow_status w
    WHERE w.workflow_id = s.workflow_id
  );

Es decir, la integridad que antes garantizaba la base de datos pasa a garantizarla la propia lógica de retención.

Segundo problema: una consulta de índice de 20 minutos

Con el nuevo mecanismo apareció un fallo de rendimiento muy raro. Cada lote empieza con una consulta que busca el created_at de la fila número N más antigua, para saber hasta dónde borrar. Tiene un índice sobre created_at, el plan de ejecución lo usa correctamente y normalmente tarda nada. Pero a veces, con la base de datos cerca de la saturación, tardaba más de 20 minutos.

La causa vuelve a ser MVCC. La marca de “fila borrada” se pone en la fila, no en las entradas de los índices que apuntan a ella. Así que cuando una consulta encuentra una fila a través de un índice, tiene que ir a la tabla a comprobar si sigue viva.

Normalmente las tuplas muertas están repartidas por el índice y esto no importa. Pero aquí se daba el peor caso posible: la consulta recorre el índice de la fila más antigua a la más nueva, y la retención borra precisamente las filas más antiguas. Si acababa de pasar un lote y el autovacuum no había llegado aún, podía haber millones de tuplas muertas justo al principio del índice, y la consulta tenía que visitar cada una en la tabla antes de llegar a la primera fila viva.

La solución fue sencilla una vez entendido el problema: ejecutar un VACUUM manual de la tabla antes y después de cada pasada de retención, para que las tuplas muertas no se acumulen al principio del índice.

Con las dos cosas juntas, el rendimiento de los borrados mejoró en un orden de magnitud.

Por qué no usar particiones

La alternativa clásica es particionar las tablas por tiempo y eliminar las particiones antiguas, como hacen sistemas de colas como pgmq o PgQue. Es mucho más rápido que cualquier DELETE, porque no pasa por MVCC: Postgres borra directamente los ficheros del disco.

El problema es la rigidez. Una partición no se puede eliminar hasta que todos sus datos han caducado. Para una cola de mensajes, donde cada dato vive un tiempo predecible, funciona muy bien. Pero en un sistema de workflows, unos pocos que duren semanas o meses pueden bloquear el borrado de miles de millones de filas de workflows cortos. Y tampoco permite políticas de retención más finas, como guardar los workflows fallidos más tiempo que los que terminaron bien.

Lo que me llevo

Este artículo me ha recordado al de cómo PostgreSQL estima tus consultas: en los dos casos, para entender el rendimiento no basta con mirar la consulta, hay que saber cómo trabaja Postgres por dentro. Aquí, además, el plan de ejecución era correcto: un índice bien usado puede tardar 20 minutos si lo que hay detrás está lleno de filas muertas.

Y me quedo con dos ideas prácticas para cualquier tabla con retención:

• ON DELETE CASCADE es cómodo, pero no es gratis. Si las filas hijas se crean a lo largo del tiempo y quedan repartidas por el disco, borrar en cascada puede ser mucho más caro que borrar cada tabla por separado en orden de creación.
• Si borras siempre por el extremo antiguo de un índice, no esperes al autovacuum. Un VACUUM explícito después de cada pasada evita que la siguiente consulta tenga que atravesar todo lo que acabas de borrar.

Las particiones siguen siendo la mejor opción cuando los datos caducan de forma uniforme. Pero cuando no es así, DELETE puede escalar bien si se tiene en cuenta cómo trabaja MVCC.

Fuente: Postgres DELETE Can Actually Scale, Peter Kraft y Qian Li, DBOS.