PostgreSQL introdujo la partición declarativa en la versión 10 para reemplazar el engorroso modelo de partición basado en herencia. El planificador de consultas realiza la eliminación estática de particiones durante la fase de planificación comparando los predicados de consulta con los límites de partición, pero requiere que las expresiones sean evaluables a constantes en el momento de la planificación para determinar qué particiones pueden ser eliminadas.
La restricción arquitectónica principal es que las funciones STABLE, como now() o current_timestamp, no se evalúan durante la planificación porque sus resultados pueden diferir entre la planificación y la ejecución o incluso durante la ejecución de la consulta. En consecuencia, el planificador trata los predicados que involucran estas funciones como cajas negras, incapaz de probar que ciertas particiones no pueden contener filas coincidentes, lo que obliga a escanear todas las particiones.
La solución implica reescribir el predicado para usar funciones IMMUTABLE o constantes literales, o depender de la eliminación de particiones en tiempo de ejecución disponible en PostgreSQL 11 y versiones posteriores. Al establecer enable_partition_pruning en on, el ejecutor evalúa los resultados de funciones STABLE contra los límites de partición en el momento de la ejecución, omitiendo dinámicamente particiones irrelevantes después de la fase de planificación inicial.
Una firma de análisis financiero particionó una tabla de transacciones por TIMESTAMPTZ en la columna execution_time utilizando rangos diarios para gestionar terabytes de datos de ticks. Los analistas consultaban frecuentemente la actividad reciente con WHERE execution_time >= now() - interval '1 hour', pero observaron que estas consultas sufrían una degradación de rendimiento catastrófica, escaneando todas las 365 particiones diarias en lugar de solo la más reciente.
El primer enfoque considerado fue hacer que la capa de aplicación calculase el límite de tiempo e insertase como una constante literal. Esto permitió la eliminación estática inmediata y redujo el tiempo de consulta de 45 segundos a 80 milisegundos. Sin embargo, rompió las consultas SQL existentes incrustadas en herramientas de BI de terceros que no se podían modificar fácilmente.
El segundo enfoque consistió en crear una función inmutable personalizada que devolviera una marca de tiempo fija basada en la fecha actual. Esto fue rechazado porque produciría resultados incorrectos si la transacción de la base de datos permanecía abierta a través del límite de medianoche, violando el contrato STABLE que proporciona now() dentro de una transacción. Tal violación podría llevar a resultados de consulta incorrectos sin que el planificador lo detectara si almacenaba en caché un rango de partición desactualizado.
La solución elegida aprovechó las capacidades de eliminación de particiones en tiempo de ejecución de PostgreSQL 12. Los administradores de la base de datos se aseguraron de que enable_partition_pruning estuviera habilitado y refactorizaron la aplicación para usar sentencias preparadas con el límite de tiempo pasado como un parámetro en lugar de concatenado en la cadena SQL. Esto permitió que el ejecutor eliminara dinámicamente particiones utilizando el valor real del parámetro en el momento de la ejecución, logrando un rendimiento casi óptimo sin requerir cambios en la generación de texto SQL en herramientas de informes heredadas.
¿Cómo difiere el mecanismo de exclusión de restricciones de PostgreSQL para tablas heredadas de la eliminación nativa de particiones, y por qué el primero requiere una configuración GUC explícita mientras que el segundo no?
La exclusión de restricciones era el método de partición utilizado antes de la partición declarativa, basándose en restricciones CHECK en tablas hijo para probar que las tablas no podían contener filas relevantes. Debido a que evaluar estas restricciones contra cada tabla heredada durante la planificación es costoso cuando existen cientos de tablas, se controla mediante el parámetro constraint_exclusion, que por defecto es partition (solo verifica al consultar a través de la herencia). La eliminación nativa de particiones utiliza estructuras de datos especializadas en el planificador que entienden la jerarquía de partición directamente, lo que la hace más rápida y siempre habilitada, sin requerir ajustes GUC para un funcionamiento correcto.
¿Por qué PostgreSQL ejecuta internamente un DELETE y un INSERT en lugar de una actualización en el lugar al actualizar una fila para moverla entre particiones modificando la clave de partición, y qué implicaciones tiene esto en los disparadores?
Dado que cada partición es una relación de montículo distinta con almacenamiento físico separado, cambiar la clave de partición requiere mover la tupla de un archivo a otro. PostgreSQL implementa esta transición eliminando la fila de la partición fuente e insertándola en la partición de destino. Esto significa que los disparadores de nivel de fila BEFORE DELETE, AFTER DELETE, BEFORE INSERT, y AFTER INSERT se activan durante lo que parece ser una única operación de UPDATE. Además, la replicación lógica transmite esto como dos entradas WAL separadas (eliminar e insertar), lo que puede causar conflictos en los suscriptores si la identidad de réplica no está configurada correctamente.
¿Qué costos específicos de bloqueo y validación ocurren al adjuntar una nueva partición a una tabla que tiene una partición DEFAULT, y cómo se puede evitar el escaneo completo de la partición por defecto?
Al adjuntar una nueva partición a una tabla particionada por rango o lista que contiene una partición DEFAULT, PostgreSQL debe escanear toda la partición DEFAULT para verificar que no existan filas que deban pertenecer a la nueva partición más específica. Este escaneo de validación adquiere un bloqueo ACCESS EXCLUSIVE en la tabla particionada y puede tardar horas para particiones por defecto grandes. Para evitar esto, se debe desanexar la partición DEFAULT antes de adjuntar la nueva partición, luego volver a adjuntar la partición DEFAULT solo después de asegurarse de que no existan filas en conflicto, o alternativamente, usar CREATE TABLE ... PARTITION OF para crear una nueva partición vacía y migrar datos utilizando INSERT ... SELECT con una cláusula WHERE que filtra por el rango específico de partición, eludiendo el escaneo de validación del contenido de la partición por defecto.