La pseudo-tabla EXCLUDED en PostgreSQL representa la fila propuesta para inserción durante una operación de ON CONFLICT. Históricamente, los desarrolladores que migran de entornos de MySQL u Oracle a menudo asumen que las comparaciones de igualdad directa (=) son suficientes para detectar cambios de valor dentro de patrones de upsert. Sin embargo, la lógica de tres valores estándar de SQL dicta que NULL representa un estado desconocido, lo que significa que NULL = NULL evalúa a NULL (desconocido), no TRUE.
Esto crea un problema crítico cuando la cláusula de resolución de conflictos intenta optimizar actualizaciones añadiendo una cláusula WHERE como WHERE EXCLUDED.phone != users.phone. Si tanto la fila existente como la fila propuesta contienen NULL para la columna teléfono, la comparación devuelve NULL, lo que falla el predicado WHERE. En consecuencia, la base de datos omite la actualización, aunque los valores puedan ser genuinamente diferentes en el contexto de la lógica empresarial, o no puede distinguir entre un NULL en los nuevos datos y un NULL en los datos antiguos.
La solución implica utilizar el operador IS DISTINCT FROM, que trata NULL como un valor comparable. Al estructurar la cláusula de actualización con WHERE EXCLUDED.column IS DISTINCT FROM table.column, la comparación devuelve FALSE cuando ambos valores son NULL (indicando que no hay cambio) y TRUE cuando uno es NULL y el otro no lo es. Esto asegura un comportamiento determinista mientras evita escrituras innecesarias.
INSERT INTO patient_records (clinic_id, external_id, phone, updated_at) VALUES (101, 'P-2024-001', NULL, NOW()) ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = COALESCE(EXCLUDED.phone, patient_records.phone), updated_at = EXCLUDED.updated_at WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;
Una red hospitalaria necesitaba sincronizar los datos de admisión diaria de pacientes de 50 clínicas externas en un almacén de datos central de PostgreSQL. Cada clínica exportaba archivos CSV donde los números de teléfono de pacientes ausentes aparecían como cadenas vacías, las cuales el comando COPY convertía a NULL durante la ingestión. El script Python ETL existente utilizaba SQLAlchemy para ejecutar upserts masivos con ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone.
El problema surgió cuando el personal clínico informó que los números de teléfono válidos ingresados directamente en el sistema central estaban desapareciendo misteriosamente después de la sincronización nocturna. La investigación reveló que cuando la alimentación externa enviaba NULL (indicando un teléfono desconocido), sobrescribía los números válidos existentes porque la cláusula SET se ejecutaba incondicionalmente. Agregar un filtro ingenuo WHERE EXCLUDED.phone != patient_records.phone falló porque cuando ambos eran NULL, la comparación devolvía NULL (desconocido), causando que la actualización se omitiera incorrectamente, y cuando el nuevo valor era NULL y el viejo no lo era, la lógica se comportaba de manera inconsistente a través de diferentes versiones menores de PostgreSQL.
Se evaluaron tres soluciones.
El primer enfoque utilizó COALESCE exclusivamente en la cláusula SET: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Esto evitó sobrescribir con NULL, pero forzó una actualización en cada conflicto, desencadenando costosas reconstrucciones de índices B-Tree en la columna phone y disparando activadores de auditoría que registraron cambios de "no-op" como modificaciones legítimas. Esto aumentó el tráfico de WAL (Write-Ahead Log) en un 300%, amenazando el retraso de replicación y saturando la I/O del disco.
La segunda solución intentó lógica booleana explícita para manejar NULL: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Aunque lógicamente correcta, este patrón prolijo requería un mantenimiento cuidadoso a través de 15 columnas anulables y confundía al optimizador de consultas. El planificador abandonó los escaneos de índices a favor de escaneos secuenciales en la tabla de 20 millones de filas, haciendo que el trabajo ETL excediera su ventana de mantenimiento de seis horas.
La tercera solución implementó IS DISTINCT FROM para todas las columnas anulables en la cláusula WHERE. Esto proporcionó un predicado conciso y apto para el optimizador que identificó correctamente los cambios de datos genuinos, incluidas las transiciones de NULL. Permitió actualizaciones solo cuando fue necesario, eliminando ejecuciones de activadores superfluas y generación de WAL mientras mantenía planes de consulta consistentes.
El equipo eligió la tercera solución para campos de contacto críticos y la primera solución para metadatos no críticos donde la protección contra sobrescritura importaba más que el rendimiento. El resultado fue dramático: la duración del trabajo de sincronización se redujo de 45 minutos a 12 minutos, el retraso de replicación se estabilizó por debajo de cinco segundos, y los incidentes de "número de teléfono desaparecido" cesaron por completo dentro de la primera semana de implementación.
¿Por qué WHERE EXCLUDED.column != table.column omite filas cuando ambos valores son NULL, y cómo interactúa esto con el mecanismo de actualización de PostgreSQL?
Muchos candidatos asumen que si dos NULL no son iguales, la comparación debería devolver TRUE y permitir la actualización. Sin embargo, SQL utiliza lógica de tres valores: NULL representa un valor desconocido. Cualquier comparación con NULL (incluyendo NULL = NULL o NULL != NULL) resulta en NULL (desconocido), no en un booleano TRUE o FALSE. En la cláusula WHERE de PostgreSQL, solo las filas que evalúan como TRUE proceden; NULL se trata como FALSE. Así, al comparar dos números de teléfono NULL, el resultado es NULL, se omite la actualización y el sistema asume incorrectamente que no se necesita ningún cambio. IS DISTINCT FROM devuelve FALSE para NULL vs NULL, indicando correctamente que son idénticos y omitiendo la actualización solo cuando es apropiado, mientras que devuelve TRUE cuando un valor es NULL y el otro no lo es.
¿Cómo afecta el orden de las columnas en una restricción única de múltiples columnas al rendimiento de la resolución de ON CONFLICT, y qué sucede si el objetivo de conflicto no coincide exactamente con la definición del índice?
Los candidatos suelen pasar por alto que PostgreSQL requiere que el objetivo de conflicto (las columnas enumeradas en ON CONFLICT (...)) coincida exactamente con la definición de índice único, incluyendo el orden de las columnas y cualquier expresión funcional. Si existe un índice único en (clinic_id, external_id) pero la consulta especifica ON CONFLICT (external_id, clinic_id), el planificador puede no inferir el índice, lanzando un error que dice "no hay una restricción única o de exclusión que coincida con la especificación ON CONFLICT". Incluso si tiene éxito, un orden de columnas desajustado puede impedir que el optimizador use un escaneo solo de índices para localizar la tupla en conflicto, forzando una recuperación del montón e incrementando significativamente el costo de I/O.
¿Cuál es la diferencia entre usar COALESCE(EXCLUDED.column, table.column) en la cláusula SET frente a usar WHERE EXCLUDED.column IS DISTINCT FROM table.column, particularmente en lo que respecta a la ejecución de activadores y la versionado de filas?
Usar COALESCE en la cláusula SET escribe incondicionalmente un valor en la fila (ya sea los nuevos datos o los datos antiguos preservados). Esta operación genera una nueva versión de fila (CTID), escribe en el WAL, y dispara todos los activadores BEFORE y AFTER asociados con la tabla, incluso si el valor final sigue siendo idéntico al estado anterior. Esto crea "ruido" en las tablas de auditoría y aumenta la carga de replicación. Por el contrario, la cláusula WHERE con IS DISTINCT FROM impide la modificación de la fila por completo si no ha ocurrido ningún cambio real. No se crea una nueva versión de tupla, no se disparan los activadores y se evita la generación de WAL. Esta distinción es crítica para sistemas de alto rendimiento con registro de auditoría o cascadas de claves foráneas, donde las actualizaciones de "no-op" crean una sobrecarga significativa.