Prompt y contexto
Esta pregunta no compara todas las opciones de columnas generadas. Se enfoca en migrar una expresión de fila repetida a una columna generada virtual de PostgreSQL 18. El objetivo es un único contrato de cálculo sin un backfill de columnas almacenadas, al tiempo que se controla la CPU de lectura, las restricciones de expresiones y los cambios de privilegios.
Qué evalúa el entrevistador
- Saber que PostgreSQL 18 introdujo las columnas generadas virtuales y las convirtió en la opción predeterminada.
- Demostrar que la expresión utiliza únicamente la fila actual y funciones y tipos integrados inmutables.
- Validar mediante lecturas duales y planes de consulta de producción, no solo con un DDL exitoso.
- Comprender que los valores virtuales se calculan en la lectura, no ocupan almacenamiento de fila y no pueden ser claves de partición.
Aclaraciones antes de responder
Confirme que la expresión use solo elementos integrados, identifique las lecturas y filtros que hacen referencia a ella, verifique la existencia de un índice de expresión y pregunte si la aplicación puede comparar la expresión anterior con la nueva columna temporalmente. Revise también los roles, ya que las columnas generadas y base tienen privilegios independientes. Si la columna generada está destinada a aislar la columna base, verifique que cada función, operador y conversión en la expresión cumpla con el requisito LEAKPROOF.
Estructura de respuesta de 30 segundos
Primero demostraría que la expresión cumple con las restricciones de columnas virtuales de PostgreSQL 18 y luego agregaría una columna explícitamente VIRTUAL. Durante la migración, la aplicación realiza lecturas sombra de la expresión anterior y de la nueva columna en valores nulos, Unicode, entradas inusuales y particiones históricas. Compararía la CPU, la latencia de cola y los planes para consultas representativas. Después de pasar los filtros semánticos, de rendimiento y de privilegios, las lecturas cambian a la nueva columna manteniendo una reversión rápida a la expresión anterior.
Análisis detallado paso a paso
ALTER TABLE report_events
ADD COLUMN normalized_country text
GENERATED ALWAYS AS (lower(trim(country_code))) VIRTUAL;Un valor virtual se calcula al leerse y no ocupa almacenamiento de fila. Su expresión solo puede hacer referencia a la fila actual, no puede contener subconsultas ni otra columna generada, y debe usar funciones inmutables. Una columna virtual tampoco puede depender de funciones o tipos definidos por el usuario. Escribir VIRTUAL explícitamente evita que la intención de la migración dependa del valor predeterminado de PostgreSQL 18.
Escanee datos históricos y compare normalized_country IS NOT DISTINCT FROM lower(trim(country_code)), incluyendo NULL, espacios en blanco, mayúsculas y minúsculas, y entradas no ASCII. Luego ejecute verificaciones representativas de EXPLAIN (ANALYZE, BUFFERS). El cómputo en tiempo de lectura puede elevar el uso de CPU; si los filtros necesitan un índice, verifique la ruta de índice admitida exacta y el costo de escritura en lugar de asumir que la ausencia de almacenamiento significa ausencia de costo.
Haga el despliegue por etapas: agregue la columna; realice lecturas sombra de ambas formas mientras la expresión anterior sigue siendo la fuente de verdad; cambie solo después de obtener cero diferencias y un rendimiento aceptable. La reversión restaura la expresión anterior. Finalmente, pruebe los privilegios de la columna base y de la generada con los roles reales de la aplicación. Si no se puede demostrar que alguna función, operador o conversión sea LEAKPROOF, los privilegios de la columna generada no formarán un límite de seguridad completo alrededor de la columna base.
Ejemplo de respuesta sólida
Trataría esto como una migración de contrato de consulta. Después de demostrar que la expresión solo utiliza elementos integrados e inmutables de la fila actual, agregaría una columna virtual explícita. Esto evita un backfill físico pero traslada el trabajo a las lecturas, por lo que las pruebas de CPU y latencia de cola con perfil de producción son obligatorias.
La aplicación primero compara los resultados anteriores y nuevos y agrupa las discrepancias por clase de entrada. Solo realiza el cambio después de pasar los filtros semánticos, de planes y de privilegios. Si el comportamiento experimenta una regresión, los datos base permanecen intactos y las consultas regresan a la expresión anterior de inmediato.
Errores comunes
- Omitir
VIRTUALy depender del valor predeterminado específico de la versión para explicar la intención. - Descubrir funciones volátiles, subconsultas o tipos definidos por el usuario solo durante el DDL.
- Probar valores ASCII comunes pasando por alto
NULLy el comportamiento de Unicode. - Asumir que la ausencia de almacenamiento de fila equivale a cero uso de CPU en las consultas.
- Eliminar la expresión anterior en el momento del cambio perdiendo la capacidad de una reversión rápida.
Preguntas de seguimiento
¿Por qué no usar STORED en este caso?
La tarea tiene como objetivo una normalización económica en tiempo de lectura y busca evitar un backfill físico. Si los escaneos, ordenamientos o filtros de producción hacen que la CPU de lectura sea inaceptable, una columna almacenada se convierte en una decisión independiente de almacenamiento y replicación.
¿Puede la columna virtual ser una clave de partición?
No. PostgreSQL 18 no permite que una columna generada sea una clave de partición. Utilice una columna normal mantenida por la ruta de escritura cuando el enrutamiento de particiones necesite el valor.
¿Cómo prueba que los privilegios no se ampliaron?
Consulte las columnas base y generadas con los roles reales de la aplicación, verificando las concesiones en las columnas y los privilegios de ejecución para las funciones en la expresión. Cuando la columna generada tiene como fin ocultar la columna base, inspeccione también si cada función, incluidas las funciones detrás de operadores y conversiones, está marcada como LEAKPROOF; PostgreSQL no exige esta condición para la aplicación. Si no se puede demostrar que alguna ruta de la expresión sea a prueba de fugas, no trate la concesión sobre la columna generada como un aislamiento completo.
¿Cuándo pueden finalizar las lecturas duales?
Después de que haya transcurrido un ciclo comercial completo, particiones históricas y carga máxima con cero diferencias semánticas y resultados aceptables de rendimiento y privilegios.