Entrevista de SQL: Eliminar filas duplicadas de forma segura y conservar el registro más reciente

Enunciado y contexto aplicable

Una tabla customer_contacts de PostgreSQL tiene 200 millones de filas. Los reintentos en un importador antiguo crearon múltiples filas para el mismo (tenant_id, external_id). La fila más reciente según updated_at debe sobrevivir; si las marcas de tiempo empatan, gana el mayor id. Una clave foránea de contact_events.contact_id puede apuntar a cualquier copia, por lo que eliminar los perdedores antes de redirigir las referencias fallaría o perdería la relación.

customer_contacts(
    id bigint primary key,
    tenant_id bigint not null,
    external_id text not null,
    updated_at timestamptz not null,
    payload jsonb not null
)

contact_events(
    id bigint primary key,
    contact_id bigint not null references customer_contacts(id),
    event_type text not null,
    created_at timestamptz not null
)

Diseña una reparación en PostgreSQL que conserve un contacto canónico determinista por clave de negocio, preserve cada evento, procese el trabajo destructivo en transacciones acotadas, pueda auditarse y reintentarse, y finalice con unicidad impuesta por la base de datos. Las lecturas deben permanecer disponibles. Se permite una pausa final de escritura controlada, pero su duración debe medirse en lugar de asumirse.

Los 200 millones de filas y la pausa final de escritura son restricciones de entrevista. Esta es una pregunta difícil de ingeniería de datos y SQL porque la función de ventana es solo el mecanismo de selección; la respuesta real también debe proteger la integridad referencial, la concurrencia, el rollback, el estado del almacenamiento y las escrituras futuras. El material público actual de entrevistas de SQL continúa utilizando la deduplicación con ROW_NUMBER y el desempate determinista como patrones explícitos de entrevista. No se dispone de una atribución verificable a una empresa, por lo que companyName es nulo.

Qué evalúa el entrevistador

La primera señal es si el candidato define "duplicado" y "más reciente" antes de escribir DELETE. La clave de negocio es (tenant_id, external_id), mientras que id identifica una fila física. El orden total updated_at DESC, id DESC hace que exactamente una fila sea la ganadora, incluso cuando las marcas de tiempo empatan. RANK puede retener varias filas empatadas; ROW_NUMBER asigna exactamente una posición 1.

La segunda señal es el control del límite destructivo. Una respuesta de producción previsualiza recuentos y muestras, congela un mapa inmutable de perdedor a ganador, preserva las filas perdedoras o una instantánea restaurable, y elimina por clave primaria. Recalcular el ranking de forma independiente durante cada lote puede cambiar el conjunto objetivo mientras continúan las escrituras y hace que la ejecución sea difícil de explicar.

La tercera señal es la corrección referencial y transaccional. Cada referencia hija debe moverse del perdedor al ganador antes de que se elimine su padre. Las reglas de unicidad de la tabla hija pueden hacer que esa actualización colisione, por lo que "actualizar todas las claves foráneas" está incompleto sin un inventario y una política de conflictos. Cada lote debe ser idempotente, corto, observable y seguro de detener.

La señal final es si la limpieza soluciona la causa raíz. Una consulta que elimina los duplicados de hoy deja al importador de mañana libre para recrearlos. El invariante duradero pertenece a una restricción unique o un índice unique, con un contrato explícito de nulos y normalización. El candidato también debe distinguir la evidencia de corrección de la evidencia de rendimiento: cero claves duplicadas y cero referencias huérfanas prueban la semántica; EXPLAIN, esperas de bloqueos, tasa de WAL, desfase de replicación, tuplas muertas y el comportamiento del autovacuum determinan una tasa operativa aceptable.

Preguntas para aclarar antes de responder

• ¿Qué columnas definen la entidad de negocio? Aquí es el par exacto (tenant_id, external_id). La conversión de mayúsculas/minúsculas, el recorte de espacios en blanco o la normalización Unicode definirían una clave diferente y deben acordarse antes de la reparación.
• ¿Qué fila gana? Mayor updated_at, luego mayor id. Una marca de tiempo por sí sola no es un orden total cuando dos filas empatan.
• ¿Pueden las filas perdedoras contener datos únicos? Si las cargas útiles deben fusionarse, "conservar la más reciente" es insuficiente. Este enunciado trata la carga útil más reciente como fidedigna y archiva los perdedores para su revisión.
• ¿Qué tablas hacen referencia a customer_contacts.id? Haz un inventario de las claves foráneas declaradas y las referencias a nivel de aplicación. Se muestra contact_events, pero una ejecución real debe buscar en el catálogo y en la documentación de propiedad.
• ¿Puede la redirección crear duplicados en tablas hijas? Si una tabla hija tiene UNIQUE(contact_id, event_type, created_at), dos eventos equivalentes pueden colisionar tras la convergencia. Decide si fusionarlos, retenerlos o rechazarlos antes de la actualización.
• ¿Pueden continuar las escrituras durante la reparación? Los lotes históricos pueden ejecutarse mientras continúan los escritores protegidos, pero el escaneo final de duplicados y la transferencia a la unicidad necesitan una protección de escritura verificada a nivel de base de datos o una pausa de escritura medida. Una convención de aplicación que algunos escritores ignoran no es una garantía.
• ¿Cuál es el requisito de rollback? Mantén una copia de seguridad o las filas perdedoras archivadas más los mapeos originales de las tablas hijas hasta que finalicen la validación y la ventana de rollback. Un mapa de perdedor a ganador por sí solo no puede reconstruir cargas útiles descartadas.
• ¿Cuánta carga es aceptable? El tamaño del lote es un control, no una constante. Comienza con poco y regula según las esperas de bloqueos, el p99 de escritura, el WAL, el desfase de replicación, las tuplas muertas y el progreso de autovacuum.
• ¿Cómo deben comportarse los nulos? Ambas columnas de clave no son nulas aquí. Si se permiten nulos y deben colisionar, la unicidad de PostgreSQL necesita NULLS NOT DISTINCT; la semántica unique predeterminada permite múltiples nulos.

Marco de respuesta de 30 segundos

"Primero congelaría la regla de negocio: los duplicados comparten (tenant_id, external_id), y el ganador es el mayor updated_at, luego el mayor id. Previsualizaría el resultado clasificado, archivaría las filas perdedoras y materializaría un mapa inmutable run_id de cada perdedor a su ganador. En lotes cortos e idempotentes registraría y redirigiría todas las referencias hijas, verificaría que ninguna apunte todavía a un perdedor y luego eliminaría los perdedores por clave primaria. Reconciliaría recuentos, muestras de cargas útiles, claves duplicadas y referencias huérfanas después de cada fase. Finalmente, bajo una protección de escritores verificada o una pausa de escritura medida, ejecutaría una última limpieza delta y construiría un índice unique, para luego hacer que todas las escrituras utilicen el mismo contrato de claves. Regularía el ritmo a partir de las métricas de producción y mantendría el archivo hasta que expire la ventana de rollback".

Este marco establece la selección, el orden de dependencias, los controles destructivos, la convergencia y la prevención. El SQL sigue esas decisiones; no las reemplaza.

Respuesta profunda paso a paso

Paso 1: Establecer invariantes, propiedad y un límite de ejecución

Escribe las poscondiciones antes de tocar los datos:

• existe exactamente un contacto para cada (tenant_id, external_id);
• su ID es el máximo bajo el ordenamiento de (updated_at, id);
• cada fila preexisting de contact_events todavía existe y hace referencia a ese ganador;
• ninguna referencia declarada o a nivel de aplicación apunta a un ID eliminado;
• un reintento de un lote completado modifica cero filas;
• las nuevas escrituras no pueden crear otro duplicado de clave de negocio.

Asigna un run_id, propietario, instantánea de origen o copia de seguridad, hora de inicio, versión de código, cursor de lote, paneles de control, umbrales de pausa y fecha límite de rollback. Registra el recuento de filas previo a la reparación, el recuento de claves de negocio distintas, el recuento de claves duplicadas, el recuento de perdedores y el recuento de eventos. Mantén totales de control por tenant para que un total global no oculte una pérdida a nivel de tenant.

Paso 2: Previsualizar el ranking determinista

Comienza con una consulta de solo lectura e inspecciona recuentos y diferencias de carga útil:

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY tenant_id, external_id
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM customer_contacts
)
SELECT tenant_id, external_id, COUNT(*) AS loser_count
FROM ranked
WHERE rn > 1
GROUP BY tenant_id, external_id
ORDER BY loser_count DESC, tenant_id, external_id
LIMIT 100;

ROW_NUMBER es apropiado porque el contrato requiere un ganador. RANK o DENSE_RANK asignarían el mismo rango a valores de ordenamiento iguales a menos que se incluya id, y un desempate determinista ausente haría que ejecuciones repetidas pudieran elegir diferentes ganadores. DISTINCT solo elimina filas de salida seleccionadas idénticas; no puede preservar una fila completa preferida por regla de negocio.

Paso 3: Materializar un mapa inmutable de perdedor a ganador

No recalcules los ganadores por separado para las actualizaciones de tablas hijas y la eliminación. Materializa la decisión una vez bajo el límite de ejecución acordado. Los fragmentos usan :name para denotar parámetros vinculados proporcionados por el ejecutor de la reparación:

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        ROW_NUMBER() OVER w AS rn,
        FIRST_VALUE(id) OVER w AS winner_id
    FROM customer_contacts
    WINDOW w AS (
        PARTITION BY tenant_id, external_id
        ORDER BY updated_at DESC, id DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
)
INSERT INTO contact_dedup_map (
    run_id, loser_id, winner_id, tenant_id, external_id
)
SELECT :run_id, id, winner_id, tenant_id, external_id
FROM ranked
WHERE rn > 1;

contact_dedup_map debe tener PRIMARY KEY(run_id, loser_id), una verificación de que perdedor y ganador difieren, y un índice en (run_id, winner_id). Archiva las filas completas de contactos perdedores bajo el mismo run_id, o mantén una ruta de restauración a un punto en el tiempo probada. Valida que cada ganador del mapa exista, ningún ganador aparezca también como perdedor en la misma ejecución, cada perdedor se mapee una sola vez y el tamaño del mapa sea igual al recuento de perdedores medido.

Para 200 millones de filas, la clasificación puede requerir un escaneo y ordenamiento grandes. Ejecuta EXPLAIN en datos con forma de producción, confirma el espacio temporal y el impacto en la carga de trabajo, y programa o regula en consecuencia. Un índice de cobertura (covering index) puede ayudar a un plan medido, pero construirlo es en sí mismo costoso y no debe prescribirse sin evidencia.

Paso 4: Redirigir referencias antes de eliminar padres

Haz un inventario de cada referencia primero. Para contact_events, preserva el mapeo original en una tabla de auditoría y luego actualiza rangos de ID acotados:

UPDATE contact_events AS e
SET contact_id = m.winner_id
FROM contact_dedup_map AS m
WHERE m.run_id = :run_id
  AND e.contact_id = m.loser_id
  AND e.id > :after_event_id
  AND e.id <= :batch_end_event_id;

El reintento es idempotente: después de que un evento apunta al ganador, ya no coincide con m.loser_id. Haz commit de cada lote acotado, persiste el cursor solo después del commit y reconcilia las filas actualizadas con las filas de auditoría de referencias. Si una restricción de unicidad hija colisiona, aplica la regla preacordada de fusión o rechazo; no desactives las restricciones esperando que el estado final sea válido.

Antes de la eliminación del padre, esta consulta debe devolver cero para cada tabla hija:

SELECT COUNT(*) AS remaining_loser_references
FROM contact_events AS e
JOIN contact_dedup_map AS m
  ON m.run_id = :run_id
 AND m.loser_id = e.contact_id;

Paso 5: Eliminar perdedores en lotes cortos y reiniciables

Elimina únicamente IDs del mapa congelado:

WITH batch AS (
    SELECT loser_id
    FROM contact_dedup_map
    WHERE run_id = :run_id
      AND loser_id > :after_loser_id
    ORDER BY loser_id
    LIMIT 10000
)
DELETE FROM customer_contacts AS c
USING batch AS b
WHERE c.id = b.loser_id
RETURNING c.id, c.tenant_id, c.external_id;

10000 es un valor inicial de entrevista, no un óptimo universal. Ajusta el tamaño del lote y la demora a partir de la duración medida de bloqueos, p99, WAL, retraso de réplica, tuplas muertas y autovacuum. La salida de RETURNING se convierte en evidencia de eliminación. Si el proceso falla después del commit pero antes de la persistencia del cursor, volver a ejecutar el lote simplemente encuentra IDs ya eliminados y sigue siendo seguro.

Las eliminaciones grandes en PostgreSQL crean tuplas muertas y WAL. Planifica un VACUUM normal y monitorea el hinchamiento (bloat) de tablas e índices; no uses por defecto VACUUM FULL, que reescribe la tabla y toma un bloqueo fuerte. Las lecturas permanecen disponibles, pero la saturación de recursos aún puede violar el SLO del servicio.

Paso 6: Converger escrituras e instalar la barrera duradera

La reparación masiva por sí sola no puede cerrar un objetivo en movimiento. Antes de la convergencia final, despliega un único contrato de escritura que serialice la creación para la misma clave de negocio normalizada y actualice la fila canónica en lugar de insertar otra copia. Verifica que cada API, importador, job y escritor directo de base de datos lo cumpla. Si esa prueba no está disponible, pausa las escrituras para la limpieza delta final y la transferencia del índice.

Después de que el último escaneo de duplicados devuelva cero, crea el índice unique fuera de un bloque de transacción:

CREATE UNIQUE INDEX CONCURRENTLY customer_contacts_business_key_uidx
ON customer_contacts (tenant_id, external_id);

CONCURRENTLY evita que las escrituras sean bloqueadas por el bloqueo habitual de construcción de índices, pero hace más trabajo, no puede ejecutarse dentro de un bloque de transacción y puede fallar por una violación de unicidad dejando un índice inválido. Inspecciona la validez, diagnostica condiciones de carrera de datos o escritores, elimina el índice inválido de acuerdo con el runbook y reintenta; no asumas que IF NOT EXISTS demuestra el éxito. Una vez válido, asócialo como una restricción unique con nombre en un paso DDL corto y controlado si la gobernanza del esquema requiere semántica de restricción.

Si todas las escrituras están pausadas y una construcción regular medida se ajusta a la ventana permitida, un índice unique no concurrente es más simple y rápido. La forma concurrente es la compensación adecuada cuando deben continuar escrituras protegidas verificadas; el SLO y el ensayo determinan cuál elegir.

El escritor final debe utilizar el invariante de unicidad de la base de datos y una política explícita de conflictos. No consideres capturar una violación de unicidad tras la limpieza como el único diseño de idempotencia: decide si la misma clave actualiza la fila canónica, rechaza cargas útiles en conflicto o entra en revisión.

Paso 7: Validar, observar y cerrar la ventana de rollback

Reconcilia al menos estas comprobaciones:

• los contactos totales después de la reparación son iguales a los contactos antes de la reparación menos los perdedores archivados;
• GROUP BY tenant_id, external_id HAVING COUNT(*) > 1 devuelve cero;
• cada ganador del mapa existe y cada perdedor está ausente;
• el recuento de eventos hijos no cambia, las referencias a perdedores restantes son cero y las referencias huérfanas son cero;
• los ganadores muestreados coinciden con la regla (updated_at DESC, id DESC) y las cargas útiles archivadas;
• una inserción duplicada es rechazada o sigue la política de actualización declarada;
• volver a ejecutar los lotes completados de mapeo, redirección y eliminación modifica cero filas.

Monitorea la tasa de lotes, errores, esperas de bloqueos de fila, p50/p95/p99 de escritura, bytes de WAL, desfase de replicación, tuplas muertas, progreso de autovacuum, crecimiento de mapa/archivo, referencias a perdedores restantes, claves duplicadas restantes y progreso de construcción de índices únicos. Mantén los datos del archivo y de auditoría de referencias durante la ventana probada de rollback. Solo entonces elimina las protecciones de escritura temporales y las tablas de reparación de acuerdo con la política de retención.

Respuesta de muestra de alta calidad

"Definiría los duplicados como aquellos que tienen igual (tenant_id, external_id) y elegiría la fila canónica por updated_at DESC, id DESC; el ID único hace que los empates sean deterministas. Antes de la eliminación, registraría recuentos de línea base, inspeccionaría diferencias de carga útil, inventariaría cada referencia, tomaría una copia de seguridad restaurable y crearía un mapa inmutable run_id desde cada ID perdedor hacia su ganador usando ROW_NUMBER y FIRST_VALUE.

Archivaría las filas perdedoras y los mapeos originales de tablas hijas. Luego redirigiría contact_events en rangos cortos de clave primaria. Cada lote hace commit antes de que su cursor avance, y un reintento es idempotente porque los eventos ya movidos ya no coinciden con un ID perdedor. Las restricciones de tablas hijas permanecen habilitadas; cualquier colisión sigue una regla explícita de fusión o rechazo. Solo después de que cada tabla hija reporte cero referencias a perdedores, eliminaría los contactos por IDs desde el mapa congelado, usando transacciones acotadas y RETURNING como evidencia.

La tasa operativa sigue el tiempo de bloqueo, el p99 de solicitudes, WAL, desfase de replicación, tuplas muertas y autovacuum en lugar de un número de lote fijo. Validaría la conservación del recuento de filas, una fila por clave de negocio, ausencia de perdedores, ausencia de huérfanos, recuento de eventos sin cambios, muestras deterministas de sobrevivientes y reintentos con cero cambios.

Para evitar la recurrencia, todos los escritores deben converger en la misma clave de negocio normalizada. Bajo una protección de escritores verificada o una pausa medida, realizaría una limpieza delta final y construiría un índice unique concurrente en (tenant_id, external_id) fuera de una transacción. Verificaría que el índice sea válido porque una construcción concurrente fallida puede dejar un índice inválido. La ruta de escritura permanente luego actualiza, rechaza o envía a revisión los conflictos según el contrato declarado. El archivo permanece hasta que la evidencia de rollback y la ventana de observación se completen".

Esta respuesta hace que las operaciones irreversibles dependan de compuertas explícitas y mantiene el SQL, las claves foráneas, el comportamiento de los escritores y la restricción de la base de datos bajo un único contrato de clave de negocio.

Errores comunes

• Ejecutar DELETE inmediatamente tras encontrar duplicados → se pueden perder referencias, cargas útiles y evidencia de rollback → previsualizar, archivar, mapear, redirigir, validar, y luego eliminar.
• Particionar únicamente por external_id → IDs externos iguales en diferentes tenants colapsan juntos → usar la clave de negocio completa (tenant_id, external_id).
• Ordenar solo por updated_at → marcas de tiempo empatadas permiten un ganador diferente en otra ejecución → agregar el id único como desempate final.
• Usar RANK con rn > 1 → las filas más recientes empatadas pueden recibir ambas el rango 1 → usar ROW_NUMBER con un orden total cuando se requiere exactamente un sobreviviente.
• Recalcular ganadores en cada lote → los cambios concurrentes pueden desplazar el objetivo y destruir la auditabilidad → congelar un único mapa versionado de perdedores a ganadores.
• Eliminar padres antes de redirigir hijos → las claves foráneas rechazan la eliminación o los cascades eliminan historial válido → inventariar y migrar todas las referencias primero.
• Desactivar restricciones para ganar velocidad → la reparación puede crear huérfanos silenciosos o colisiones en tablas hijas → mantener las restricciones activas y definir el manejo de conflictos.
• Llamar a 10,000 el tamaño de lote correcto → el hardware y la carga de trabajo deciden el rendimiento seguro → comenzar con poco y adaptar a partir de métricas de SLO, WAL, desfase y vacuum.
• Detenerse tras la limpieza → el escritor defectuoso recrea los duplicados → hacer converger a los escritores e imponer unicidad en PostgreSQL.
• Asumir que CREATE UNIQUE INDEX CONCURRENTLY siempre tiene éxito → una carrera o duplicado puede dejar un índice inválido → inspeccionar la validez y seguir un procedimiento de recuperación explícito.
• Usar VACUUM FULL como limpieza rutinaria → reescribe y bloquea fuertemente la tabla → monitorear el vacuum ordinario y el hinchamiento, y luego programar reescrituras excepcionales por separado.

Preguntas de seguimiento y respuestas

Pregunta de seguimiento 1: ¿Por qué no eliminar con un CTE y terminar en una sola transacción?

Para una tabla pequeña y aislada sin referencias y con escritores pausados, un CTE con DELETE puede ser suficiente. En 200 millones de filas, una transacción puede retener bloqueos y versiones antiguas de filas, generar una ráfaga masiva de WAL, retrasar réplicas, complicar el vacuum y crear un evento de recuperación de todo o nada. El mapa congelado junto con lotes acotados proporciona progreso, auditabilidad, regulación de ritmo y capacidad de reinicio. Utiliza la consulta más simple solo después de probar sus suposiciones de escala y dependencias.

Pregunta de seguimiento 2: ¿Qué sucede si dos filas comparten la misma marca de tiempo pero tienen cargas útiles diferentes?

La regla del enunciado elige el mayor id, por lo que el resultado es determinista, pero el determinismo no prueba que la carga útil sea semánticamente correcta. Mide las cargas útiles en conflicto antes de la reparación y dirige los campos de alto riesgo a revisión o a una regla de fusión específica del dominio. Archiva ambas filas. No inventes una fusión campo por campo una vez que la eliminación ha comenzado.

Pregunta de seguimiento 3: ¿Qué sucede si external_id puede ser nulo?

Primero define si null significa "desconocido e independiente" o un valor duplicado. Las restricciones unique y los índices de PostgreSQL tratan los nulos como distintos por defecto, por lo que se permiten varias claves nulas. Si los nulos deben colisionar, utiliza NULLS NOT DISTINCT y clasifica con la misma semántica; si los contactos desconocidos son independientes, mantén el comportamiento predeterminado o utiliza una regla de unicidad parcial para IDs no nulos. La limpieza y la restricción deben coincidir.

Pregunta de seguimiento 4: ¿Puede la reparación ser totalmente en línea sin pausa de escritura?

Sí, únicamente si se demuestra que cada escritor utiliza una regla de serialización o reserva respaldada por la base de datos para la clave de negocio antes del escaneo final. Entonces la limpieza histórica puede converger y se puede construir un índice unique concurrente. Si un importador heredado o un escritor directo omite la protección, puede aparecer un nuevo duplicado durante la construcción y hacer que falle. Una tabla sombra (shadow table) y una migración controlada (cutover) es otra opción, pero agrega complejidad de doble escritura, backfill, claves foráneas y rollback, y debe justificarse por el SLO de tiempo de inactividad.

Pregunta de seguimiento 5: ¿Cómo encuentras cada clave foránea y referencia oculta?

Consulta los catálogos de PostgreSQL para obtener claves foráneas cuya relación referenciada sea customer_contacts, luego inspecciona vistas, triggers, consumidores de CDC, índices de búsqueda, exportaciones de datos y esquemas de aplicación en busca de IDs de contacto almacenados. Las claves foráneas declaradas proporcionan evidencia exigible; la documentación de propiedad y la búsqueda de código cubren las referencias a nivel de aplicación. La compuerta de eliminación lista cada consumidor descubierto y su verificación de reconciliación.

Pregunta de seguimiento 6: ¿Qué sucede si redirigir eventos viola una restricción unique en la tabla hija?

Pausa antes de actualizar. La colisión significa que dos filas hijas se vuelven iguales después de que sus IDs padre convergen. Define si son eventos duplicados, observaciones distintas que necesitan una nueva clave o un conflicto de datos. Materializa los grupos de colisión, archívalos y aplica una regla determinista de fusión o rechazo antes de redirigir. Eliminar la restricción de la tabla hija cambia la semántica de negocio y no es un plan de reparación.

Pregunta de seguimiento 7: ¿Cómo demuestras que el ganador seleccionado fue el más reciente después de que la tabla cambió durante una ejecución prolongada?

El mapa prueba el ganador bajo su instantánea registrada o límite de ejecución, no bajo escrituras futuras ilimitadas. Protege a los escritores para que las operaciones posteriores actualicen la fila canónica mapeada, registren una versión de origen y realicen un escaneo delta final antes de la transferencia a la unicidad. Si las reglas de negocio requieren que una actualización posterior reemplace la carga útil, actualiza el ganador; no crees otro contacto físico. Declara la garantía temporal en el registro de auditoría.