Entrevista de backend: ¿Cómo elegir entre columnas generadas virtuales y almacenadas en PostgreSQL 18?

Planteamiento y alcance

Un servicio de pedidos tiene unit_price, quantity y discount, y necesita un net_amount derivado únicamente de la misma fila. El entrevistador pregunta si conviene calcularlo en la escritura o en cada lectura, mientras que la base de datos debe admitir índices, replicación lógica, rollback y un suscriptor más antiguo. La habilidad fundamental es decidir el límite de persistencia, no recordar la sintaxis.

Qué evalúa el entrevistador

• Si distingue el cómputo en tiempo de lectura de VIRTUAL frente al cómputo en tiempo de escritura y el costo de almacenamiento de STORED.
• Si verifica que la expresión utilice únicamente la fila actual, funciones inmutables y tipos admitidos.
• Si sabe que las columnas virtuales no pueden utilizar tipos o funciones definidos por el usuario, mientras que las columnas almacenadas tienen menos restricciones.
• Si la frecuencia de consultas, el volumen de escritura, la indexación y la topología de replicación guían la elección.
• Si el publicador de PostgreSQL 18 y los suscriptores más antiguos tienen una ruta explícita de compatibilidad y rollback.

Preguntas para aclarar primero

• ¿Se utiliza net_amount para filtros frecuentes, ordenamiento o unicidad? Un índice suele favorecer materializarlo en la escritura.
• ¿La carga de trabajo es intensiva en lecturas o en escrituras? Una columna virtual ahorra almacenamiento, pero se calcula en cada lectura.
• ¿Los suscriptores de replicación lógica son de PostgreSQL 18? Una versión más antigua no copia las columnas generadas durante la sincronización inicial.
• ¿Podría la expresión depender de una función de usuario, una tabla externa o la hora actual? Eso cambia el determinismo y la compatibilidad.

Marco de respuesta en 30 segundos

Primero definiría la fórmula y el límite de consistencia. Para un valor simple, leído con poca frecuencia y que no necesita una réplica física, usaría VIRTUAL y dejaría que PostgreSQL lo calcule en la lectura. Si el valor necesita un índice estable, menor uso de CPU en lectura o debe llegar ya calculado a un suscriptor, usaría STORED. Verificaría la inmutabilidad de la expresión y la compatibilidad de versiones, probaría el publicador, el suscriptor, los índices y el rollback, y luego compararía la latencia de lectura, la amplificación de escritura y el comportamiento de replicación con datos similares a los de producción.

Razonamiento paso a paso

PostgreSQL 18 hace que VIRTUAL sea el tipo de columna generada predeterminado: no ocupa almacenamiento de fila y se calcula al leerse. STORED se calcula al insertar o actualizar y ocupa almacenamiento. Ninguno de los dos tipos se puede asignar directamente en INSERT o UPDATE; la expresión solo puede hacer referencia a la fila actual y a funciones inmutables.

La regla de decisión es situar el costo donde la carga de trabajo sea menos sensible. Lecturas frecuentes, índices o réplicas que deban consumir el resultado favorecen a STORED, sacrificando espacio y CPU de escritura a cambio de lecturas estables. Lecturas poco frecuentes con una ruta de escritura activa y una expresión corta favorecen a VIRTUAL, sacrificando almacenamiento y trabajo de escritura a cambio de CPU de lectura. Una columna virtual no es un caché de resultados compartidos simplemente porque su nombre parezca sugerirlo.

Ejemplo de SQL y replicación

Este ejemplo almacena explícitamente el monto; cambiar STORED a VIRTUAL hace que las lecturas evalúen la expresión nuevamente:

CREATE TABLE order_line (
  id bigint PRIMARY KEY,
  unit_price numeric(12, 2) NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  discount numeric(5, 4) NOT NULL CHECK (discount BETWEEN 0 AND 1),
  net_amount numeric(12, 2)
    GENERATED ALWAYS AS (unit_price * quantity * (1 - discount)) STORED
);

CREATE INDEX order_line_net_amount_idx ON order_line (net_amount);

CREATE PUBLICATION order_pub
  FOR TABLE order_line
  WITH (publish_generated_columns = 'stored');

El publicador puede optar por publicar columnas generadas almacenadas; una columna virtual no tiene ningún valor físico que copiar a través de la misma ruta. Si un suscriptor es anterior a PostgreSQL 18, su sincronización inicial no copia las columnas generadas incluso cuando el publicador habilita la opción, por lo que el suscriptor necesita un plan de recálculo o de contingencia.

Migración, índices y rutas de fallo

Pruebe ambas opciones en una tabla sombra utilizando la distribución de datos real. Compare la latencia de escritura, el uso de CPU en lectura, el tamaño del índice y el tiempo de puesta al día de la réplica. Introduzca el nuevo valor como una columna ordinaria con escritura doble, concílielo y solo entonces cambie a una columna generada; no altere una tabla grande durante su período de mayor actividad.

Para una topología de replicación de versiones mixtas, registre la configuración del publicador, la versión del suscriptor y el estado de la sincronización inicial. Si los valores discrepan, pause los consumidores downstream que dependan de la columna y recalcule a partir de las columnas base en lugar de tratar una brecha de replicación como cero. Conserve las columnas originales y una versión de la fórmula para el rollback hasta que pasen las comprobaciones de igualdad por muestreo y completas.

Errores comunes

• Tratar a VIRTUAL como un caché y olvidar que cada lectura lo calcula.
• Asumir que cualquier expresión puede invocar la hora actual, una subconsulta o una función definida por el usuario.
• Elegir una columna virtual para una ruta indexada sin verificar la versión y la compatibilidad con índices.
• Actualizar solo el publicador e ignorar el comportamiento de sincronización inicial en suscriptores más antiguos.
• Eliminar las columnas de origen de modo que la replicación y el rollback ya no puedan recalcular el valor.

Preguntas de seguimiento

¿Cuándo preferiría VIRTUAL?

Prefiéralo para una expresión corta, frecuencia de lectura limitada, escrituras frecuentes y sin necesidad de un índice físico. Antes del despliegue, utilice pruebas de CPU de lectura, latencia de cola y lecturas concurrentes para demostrar que el ahorro de almacenamiento no se convierte en un costo de cómputo inaceptable.

¿Cuándo se requiere STORED?

Úselo cuando el valor necesite un índice, comprobaciones de unicidad, lecturas estables en réplicas o un suscriptor que no pueda recalcular de forma segura. Incluya el valor en la auditoría de escritura y trate los cambios de fórmula como migraciones de datos.

¿Cómo actualizar una topología de replicación de versiones mixtas?

Haga un inventario de las versiones de publicador y suscriptor. Mantenga a los suscriptores más antiguos en recálculo sobre columnas base o en una columna de transición ordinaria replicada; tras actualizar y completar la sincronización inicial, habilite publish_generated_columns. Concilie recuentos de filas, hashes y montos muestreados, con una ruta de rollback si alguna comprobación falla.