Esperando Postgres 19: por fin es hora de hablar del 'tiempo'

• Para responder preguntas sobre cómo se veía la información en un momento específico (el martes pasado), Postgres 19 introduce soporte nativo para tablas temporales (temporal table), lo que permite rastrear los datos antes y después de los cambios sin un sistema separado de triggers de auditoría
• Postgres incorpora por fin al núcleo las tablas temporales definidas hace más de una década por el estándar SQL:2011, sumándose más tarde que otros motores de base de datos
• En lugar del enfoque previo con dos columnas valid_from/valid_to y una restricción de exclusión (exclusion constraint) basada en la extensión btree_gist, ahora ofrece una expresión más intuitiva con una sola columna de tipo rango (range type) y la restricción WITHOUT OVERLAPS
• Con la sintaxis FOR PORTION OF, el motor divide automáticamente filas en operaciones UPDATE y DELETE, evitando huecos y superposiciones en la línea temporal
• Esta incorporación cubre la mitad del sistema bi-temporal correspondiente al tiempo válido (application time); el tiempo del sistema (system time) aún no está soportado, pero ya deja sentadas las bases para futuras versiones

---

El enfoque tradicional - The Old-Fashioned Way

• El primer intento de seguir los precios de un producto a lo largo del tiempo consistía en dos columnas de fecha, valid_from y valid_to, junto con una restricción CHECK valid_from < valid_to
  • Pero eso no impedía insertar dos filas con rangos de fecha superpuestos para el mismo producto (por ejemplo, que el producto 42 costara $9.99 y también $14.99 el mismo martes)
• La solución tradicional era usar la extensión btree_gist y una restricción de exclusión (exclusion constraint)
  • Con una forma como EXCLUDE USING gist (product_id WITH =, daterange(valid_from, valid_to) WITH &&), se producía un error al intentar insertar filas superpuestas
• Problemas de este enfoque
  • GiST es un tipo de índice propio de Postgres que requiere experiencia, y al ser una extensión opcional presenta una barrera de entrada
  • La sintaxis de las restricciones de exclusión no es intuitiva y no resulta fácil verla como el enfoque estándar
  • La tabla en sí no tiene conciencia temporal integrada; al cambiar rangos temporales hay que dividir o combinar filas manualmente, trasladando a la aplicación la carga de mantener la consistencia temporal

Una breve historia del tiempo - A Brief History of Time

• El estándar SQL:2011 introdujo periodos de tiempo válido (APPLICATION TIME), la restricción WITHOUT OVERLAPS y la sintaxis FOR PORTION OF para manipular datos temporales
• Henrietta Dombrovskaya (Hetti), junto con Chad Slaughter, desarrolló la extensión pg_bitemporal, un framework en PL/pgSQL para gestionar tablas bi-temporales dentro de Postgres
  • Desde 2015 presentó estos conceptos en varias conferencias, mostrando cómo seguir al mismo tiempo el tiempo válido (valid time) —cuando un hecho es cierto en el mundo real— y el tiempo de transacción (transaction time) —cuando la base de datos registró ese hecho—
• La diferencia entre las dos dimensiones temporales
  • El tiempo válido significa algo como: "este precio fue válido de enero a junio"
  • El tiempo de transacción significa algo como: "esta fila se insertó el 12 de marzo a las 3:47 p. m. y fue reemplazada el 3 de abril a las 9:01 a. m." desde la perspectiva de la base de datos
  • Al combinar ambos, se puede construir una tabla bi-temporal que responda: "según lo que sabíamos en ese momento, ¿qué creíamos que era el precio el martes pasado?"
• pg_bitemporal usa EXCLUDE USING gist por duplicado, una vez para el rango effective (tiempo válido) y otra para el rango asserted (tiempo de transacción)
  • También ofrece funciones para inserciones, actualizaciones, correcciones, desactivaciones y eliminaciones bi-temporales, además de implementar las relaciones de intervalos de Allen (Allen's interval relationships) para inferencia temporal
• Límites de la extensión
  • No puede modificar el planificador de consultas para que entienda condiciones temporales, integrarse con el sistema de restricciones a nivel de motor ni ofrecer sintaxis nativa de manipulación → era necesario llevarlo al núcleo
  • Postgres 19 incorpora la mitad correspondiente al tiempo válido de un sistema bi-temporal; no es el paquete completo, pero sí un gran avance

Los rangos al rescate - Ranges to the Rescue

• El enfoque de Postgres 19 usa una sola columna de tipo rango, valid_at DATERANGE, en lugar de valid_from/valid_to por separado
  • PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) elimina la necesidad de la extensión btree_gist y de una restricción de exclusión explícita
  • WITHOUT OVERLAPS permite varias filas para el mismo producto siempre que product_id sea único en cualquier momento dado y los rangos no se superpongan
• Internamente se sigue usando un índice GiST y las columnas no temporales de la clave aún requieren btree_gist, pero Postgres resuelve automáticamente esa dependencia al inicializar la restricción
• Notación de rangos: [ significa inclusivo y ) exclusivo → [2025-01-01, 2025-07-01) incluye el 1 de enero pero no el 1 de julio
  • La última fila de Gadget, [2026-01-01,), es un rango abierto (open-ended), sin fecha de fin definida para el precio actual
• Cómo protege contra superposiciones
  • Si se intenta insertar un rango inválido como [2025-03-01, 2025-01-01), aparece el error de que "el límite inferior del rango debe ser menor o igual al límite superior"
  • Si se intenta insertar un rango superpuesto como [2025-03-01, 2025-09-01), aparece una violación de restricción de exclusión en products_pkey
  • Con solo usar rangos se obtienen dos validaciones al mismo tiempo

Cortar y dividir - Slicing and Dicing

• Si se quiere cambiar el precio de un producto a $10.99 solo entre marzo y septiembre de 2025, el enfoque anterior obligaba a dividir e insertar filas manualmente, con riesgo de generar huecos o superposiciones por error
• En una tabla temporal, eso puede expresarse directamente como se pretende
  • UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1
• Como resultado, las filas de Widget pasan de 3 a 5
  • La fila original de $9.99 se reduce a [2025-01-01, 2025-03-01)
  • Se agrega una nueva fila de $10.99 para el rango restante
  • La fila original de $12.99 se reduce a [2025-09-01, 2026-01-01)
  • Se agrega otra nueva fila de $10.99 para el rango restante [2025-07-01, 2025-09-01)
• La razón por la que $10.99 queda dividido en dos filas es que FOR PORTION OF procesa cada fila coincidente de manera independiente y no fusiona después (coalesce) rangos adyacentes
  • Aun así, el resultado final no tiene huecos ni superposiciones, una ventaja que no ofrecía la lógica pura de exclusión (exclusion logic)
• Casos límite (edge case)
  • Si el rango de FOR PORTION OF cae completamente dentro de una sola fila existente, pueden generarse hasta 2 filas residuales (antes y después)
  • Si coincide exactamente con un límite existente, no hacen falta filas residuales
• Las nuevas filas temporales residuales no requieren permiso INSERT, pero sí activan los triggers INSERT; esto exige cuidado con el logging de auditoría o con funciones trigger SECURITY DEFINER

Borrar historial - Erasing History

• FOR PORTION OF también funciona con DELETE; por ejemplo, para retirar temporalmente un producto del catálogo entre junio y octubre de 2025
  • DELETE FROM products FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01' WHERE product_id = 2
• Resultado
  • Se elimina el tramo de junio a octubre, y la fila de $22.99 que cubría [2025-04-01, 2026-01-01) se divide en dos filas residuales: una que termina en junio y otra que comienza en octubre
  • Los datos de precio antes y después del hueco se conservan con sus valores originales, lo que hace que DELETE aumente el número de filas
• Todo el mecanismo de gestión de la tabla temporal se maneja automáticamente, eliminando a nivel de aplicación el riesgo de borrado excesivo o de fragmentos huérfanos (orphaned fragment)

La verdad en la publicidad - Truth in Advertising

• Una tabla temporal no está completa sin claves foráneas temporales (temporal foreign key), y Postgres 19 las soporta con la palabra clave PERIOD
  • Con una forma como FOREIGN KEY (product_id, PERIOD valid_at) REFERENCES products (product_id, PERIOD valid_at)
• La palabra clave PERIOD indica que la clave foránea en sí está basada en tiempo
  • El producto referenciado debe existir durante todo el periodo cubierto por el rango valid_at de la variante
  • La combinación de todas las filas coincidentes de la tabla referenciada debe cubrir por completo el periodo de la fila que referencia
• Si se intenta crear una variante que exceda el rango temporal del producto ([2025-01-01, 2027-01-01)), se rechaza
  • Como el precio de Widget solo está definido hasta mediados de 2026, una variante que afirme validez hasta 2027 será rechazada por violar la restricción de clave foránea
• Una limitación importante
  • Las claves foráneas temporales solo admiten NO ACTION como acción referencial; quedan fuera CASCADE, SET NULL y SET DEFAULT
  • Si se elimina una fila de producto de la que depende una variante, siempre se producirá un error, por la complejidad de las operaciones temporales en cascada, y la aplicación deberá manejarlo explícitamente

Pasos pequeños - Baby Steps

• Las funciones disponibles hoy: tablas temporales basadas en tiempo válido con prevención de superposiciones, manipulación temporal de datos y claves foráneas temporales
• Lo más importante que falta es el tiempo del sistema (system time), también conocido como tiempo de transacción
  • El tiempo válido sigue cuándo un hecho es cierto en el mundo real; el tiempo del sistema sigue cuándo la base de datos se entera de ese hecho, y muchos sistemas usan ambos
  • Este es el terreno que la extensión pg_bitemporal ha venido cubriendo desde 2015
  • Es posible emular el tiempo del sistema con triggers, pero no es lo mismo que dejar que el motor lo administre de forma transparente como las demás funciones temporales nuevas
• La documentación de tablas temporales también deja claro que el tiempo del sistema aún no tiene soporte nativo y solo puede emularse; no está definido si llegará en Postgres 20 o después, aunque la base ya existe

Reflexiones finales - Final Thoughts

• El enfoque con EXCLUDE USING gist funciona, pero era una solución alternativa algo tosca; extensiones como pg_bitemporal demostraron el concepto y mantuvieron viva la discusión
• Es un enfoque mucho más intuitivo que las restricciones de exclusión con GiST
  • WITHOUT OVERLAPS en la clave primaria se lee como inglés común, y FOR PORTION OF expresa exactamente lo que hace
  • La división automática de filas durante actualizaciones y eliminaciones temporales elimina toda una clase de bugs potenciales
• El camino desde SQL:2011 hasta Postgres 19 fue largo, y Hetti junto con la comunidad pasaron años demostrando tanto la necesidad como la viabilidad de este patrón, que ahora ya forma parte del núcleo
• Habrá que seguir de cerca el soporte futuro para tiempo del sistema; cuando Postgres complete ambas mitades del modelo bi-temporal, las posibilidades se ampliarán mucho más