Qué ocurre realmente cuando eliminas una columna en Postgres

• En PostgreSQL, aunque hagas DROP de una columna, los datos en realidad no se eliminan; solo quedan "ocultos" en los metadatos
• Como la columna sigue existiendo internamente incluso después de DROP COLUMN, puedes llegar al límite de 1600 columnas
• Para eliminar los datos por completo, se necesita VACUUM FULL o una reescritura manual de la tabla
• Es un diseño pensado para optimizar el rendimiento, pero requiere cuidado desde la perspectiva de cumplimiento normativo, como GDPR
• Entender "qué ocurre realmente" ayuda en la resolución de problemas, optimización de rendimiento y gestión de datos

---

Cómo funciona realmente DROP COLUMN en PostgreSQL

El problema: ¿qué pasa si agregas y eliminas columnas repetidamente?

• Con un código como este, se agrega y luego se elimina una columna 2000 veces:

  ALTER TABLE t ADD COLUMN c1 int;  
  ALTER TABLE t DROP COLUMN c1;  
  ...  
  

• Al final, en la tabla solo quedan 2 columnas, pero PostgreSQL aun así lanza un error por el límite de 1600 columnas
• ¿La razón? Las columnas eliminadas siguen existiendo internamente

¿Qué ocurre dentro de PostgreSQL?

Eliminar una columna no es una "eliminación real"

• PostgreSQL almacena los datos en páginas de 8 KB
• Eliminar físicamente una columna implicaría reescribir toda la tabla, por lo que sería ineficiente
• En su lugar, la columna se marca como 'dropped' en los metadatos y se ignora

Se puede comprobar en la tabla del sistema pg_attribute

SELECT attnum, attname, attisdropped FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attnum > 0;  

• Ejemplo de salida:

  attnum | attname                  | attisdropped  
  --------+--------------------------+--------------  
        1 | a                        | f  
        2 | ........pg.dropped.2.... | t  
        3 | c                        | f  
  

• Las columnas con attisdropped = t se ignoran en las consultas, pero siguen presentes internamente

Comprobarlo en el archivo de datos (usando pg_filedump)

• Si analizas el archivo de datos de PostgreSQL, puedes confirmar que los valores de la columna eliminada siguen ahí
• En los datos antiguos (Item 1) todavía existen los valores de 3 columnas
• En los datos insertados después de la eliminación (Item 3), ese valor de columna ya no está y se trata como NULL

Cómo eliminar de verdad una columna eliminada

1. VACUUM FULL

• Reescribe toda la tabla y también elimina los datos de las columnas borradas
• Desventaja: la columna en sí sigue existiendo en pg_attribute con estado 'dropped'

2. Reescritura manual de la tabla

• Se crea una tabla nueva y se copian con SELECT solo las columnas necesarias

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• Restricciones, índices, triggers, etc., deben recrearse manualmente
• También es posible hacer respaldo con pg_dump → modificar el archivo dump → restaurar

DROP COLUMN y el problema del "derecho al olvido" en GDPR

• Algunas personas plantean la preocupación de si "¿no sería una violación de GDPR si la columna no se elimina realmente?"
• Pero la eliminación de datos personales normalmente se hace a nivel de fila (row)

  DELETE FROM users WHERE id = <user_id>; -- o eliminar incluyendo las tablas relacionadas  
  

• DROP COLUMN no está directamente relacionado con GDPR; lo importante es modelar y eliminar correctamente los datos personales

Precauciones

• Como PostgreSQL usa MVCC, incluso después de eliminar filas los datos permanecen hasta que VACUUM termine
• Incluso a nivel de sistema operativo, puede tratarse de una marca de eliminación y no de un borrado físico
• Desde el punto de vista legal, lo importante es hacer un “esfuerzo razonable de eliminación”; en la mayoría de los casos no se exige borrar por completo el disco físico

Conclusión: DROP COLUMN solo "oculta", no "elimina"

• Es un diseño orientado al rendimiento, pero si se acumulan columnas puedes chocar con el límite de 1600
• Si hace falta, se deben limpiar los datos con VACUUM FULL o una reescritura de la tabla
• Desde la perspectiva de diseño de sistemas o cumplimiento, entender el funcionamiento interno de PostgreSQL resulta muy útil

Material de referencia

• Postgres File Layout
• Postgres Page Layout
• Internal Layout of Heap Tables
• Documentación de VACUUM
• pg_filedump

[ohyecloudy]

La perspectiva de que una elección de implementación para optimizar el rendimiento también puede pensarse en relación con el problema del derecho al olvido del GDPR es muy perspicaz. Al final, la conclusión es que no está relacionado, y que la clave es modelar y eliminar correctamente los datos personales. Muy limpio.

[click]

Aunque postgresql está bastante popular últimamente, en cuanto a la implementación de MVCC prefiero más el enfoque donde existen áreas separadas de redo/undo.
Como en las áreas de redo/undo está bien sacrificar en cierta medida la inmediatez, también hay margen para optimizar costos usando almacenamiento de menor nivel.
Y el hecho de que en algún momento haya que bloquear toda la DB y ejecutar VACUUM FULL también es algo que no me gusta.

[salsa]

¿Entonces es cierto que en algún momento necesariamente hay que ejecutar VACUUM FULL? La mayoría de los documentos que vi más bien dicen que no se debe hacer.

Uno de los materiales que vi:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/