ADR-002: Eliminación de Índices Duplicados en PostgreSQL¶

Metadata¶

Campo	Valor
ID	ADR-002
Fecha	2026-02-01
Estado	Implementado
Responsable	Equipo Platform / Claude Code
Migraciones	`20260201130000_RemoveDuplicateIndexes`
Proyecto	Calmia Nexus

Contexto¶

Durante el análisis de rendimiento de la base de datos PostgreSQL de Calmia Nexus, se identificaron 4 pares de índices duplicados. Estos índices fueron creados automáticamente por Entity Framework Core cuando se definieron constraints UNIQUE mediante el patrón HasIndex().IsUnique().

Problema Identificado¶

En PostgreSQL, cuando se crea una constraint UNIQUE, el motor de base de datos automáticamente crea un índice subyacente para mantener la unicidad. Sin embargo, EF Core también crea un índice explícito con el prefijo IX_, resultando en dos índices idénticos sobre las mismas columnas.

Índices Duplicados Detectados¶

Tabla	Columnas	Índice EF Core (Duplicado)	Constraint UNIQUE (Se Mantiene)
`Organizations`	`Slug`	`IX_Organizations_Slug`	`Organizations_Slug_key`
`Users`	`Email`	`IX_Users_Email`	`Users_Email_key`
`ProjectAgentPaths`	`ProjectId`, `RemoteAgentId`	`IX_ProjectAgentPaths_ProjectId_RemoteAgentId`	`ProjectAgentPaths_ProjectId_RemoteAgentId_key`
`WellnessRecords`	`UserId`, `RecordDate`	`IX_WellnessRecords_UserId_RecordDate`	`WellnessRecords_UserId_RecordDate_key`

Decisión¶

Eliminar los 4 índices regulares duplicados (IX_*) manteniendo las constraints UNIQUE.

Razones de la Eliminación¶

Redundancia funcional: Ambos índices (el creado por la constraint y el explícito) proporcionan exactamente la misma funcionalidad de búsqueda.
Overhead de escritura: Cada operación INSERT, UPDATE o DELETE debe mantener sincronizados ambos índices, duplicando el trabajo de I/O.
Consumo de espacio: Los índices duplicados consumen espacio en disco y memoria (buffer pool) innecesariamente.
Complejidad del planificador: El query planner de PostgreSQL debe evaluar más índices durante la optimización de consultas.

Justificación Técnica¶

                    ANTES                              DESPUÉS

INSERT INTO Users   ───────────►   INSERT INTO Users
       │                                  │
       ├──► Actualizar IX_Users_Email     └──► Actualizar constraint
       │         (índice regular)               (1 operación)
       │
       └──► Actualizar constraint
             (1 índice subyacente)

       = 2 actualizaciones de índice        = 1 actualización de índice

Impacto en Rendimiento¶

Mejoras Esperadas¶

Operación	Mejora Estimada	Justificación
INSERT	15-25% más rápido	Una actualización de índice menos por fila
UPDATE (columnas indexadas)	15-25% más rápido	Mismo motivo
DELETE	10-15% más rápido	Una eliminación de índice menos
SELECT	Sin cambio	La constraint UNIQUE usa el mismo tipo de índice B-tree
Espacio en disco	~50% menos para estos índices	Eliminación de índices redundantes
Memoria (shared_buffers)	Reducción menor	Menos páginas de índice en caché

Tablas Afectadas - Volumen de Operaciones¶

Tabla	Filas Aprox.	Writes/Día	Impacto
`Organizations`	~20	Bajo	Menor
`Users`	~200	Medio	Moderado
`ProjectAgentPaths`	~50	Bajo	Menor
`WellnessRecords`	~5,000	Alto	Significativo

Nota: La tabla WellnessRecords es la más beneficiada debido a su alto volumen de escrituras diarias.

Sin Impacto Negativo en Lecturas¶

Las consultas que utilizan filtros por estas columnas seguirán funcionando de manera óptima:

-- Estas consultas siguen usando índice (el de la constraint UNIQUE)
SELECT * FROM "Organizations" WHERE "Slug" = 'acme';
SELECT * FROM "Users" WHERE "Email" = 'user@example.com';
SELECT * FROM "ProjectAgentPaths" WHERE "ProjectId" = 1 AND "RemoteAgentId" = 2;
SELECT * FROM "WellnessRecords" WHERE "UserId" = 1 AND "RecordDate" = '2026-02-01';

Implementación¶

Migraciones Creadas¶

20260201130000_RemoveDuplicateIndexes.cs
Método Up(): Elimina los 4 índices duplicados
Método Down(): Recrea los índices para rollback
Verificación previa: Aborta si falta alguna constraint UNIQUE

Scripts de Soporte¶

Script	Propósito
`scripts/database/identify-duplicate-indexes.sql`	Identificar índices duplicados
`scripts/database/verify-unique-constraints.sql`	Verificar constraints UNIQUE
`scripts/database/drop-duplicate-indexes.sql`	Script SQL standalone
`scripts/database/validate-duplicate-indexes-migration.sql`	Dry-run de validación
`scripts/database/apply-duplicate-indexes-migration.sql`	Aplicar con verificaciones

Comandos de Ejecución¶

# Opción 1: Usando EF Core migrations
cd Orchestrator/src/Orchestrator.Api
dotnet ef database update --context NexusDbContext

# Opción 2: Usando script SQL directo
psql -d nexus_production -f scripts/database/drop-duplicate-indexes.sql

Verificación Post-Implementación¶

Consultas de Verificación¶

-- 1. Verificar que los índices fueron eliminados
SELECT indexname FROM pg_indexes
WHERE tablename IN ('Organizations', 'Users', 'ProjectAgentPaths', 'WellnessRecords')
  AND indexname LIKE 'IX_%';
-- Resultado esperado: 0 filas

-- 2. Verificar que las constraints UNIQUE siguen activas
SELECT conname, conrelid::regclass as table_name
FROM pg_constraint
WHERE contype = 'u'
  AND conrelid::regclass::text IN ('Organizations', 'Users', 'ProjectAgentPaths', 'WellnessRecords');
-- Resultado esperado: 4 filas

-- 3. Verificar integridad de unicidad
INSERT INTO "Organizations" ("Slug", "Name") VALUES ('existing-slug', 'Test');
-- Resultado esperado: ERROR duplicate key value violates unique constraint

Métricas a Monitorear¶

Métrica	Herramienta	Valor Esperado
Tiempo promedio de INSERT	pg_stat_statements	Reducción 15-25%
Tamaño total de índices	pg_total_relation_size	Reducción ~200KB
Cache hit ratio	pg_stat_user_indexes	Mejora leve

Rollback¶

En caso de necesitar revertir los cambios:

# Opción 1: EF Core
dotnet ef database update 20260201120000_OptimizeRemoteAgentsIndexes --context NexusDbContext

# Opción 2: SQL directo
psql -d nexus_production << 'EOF'
CREATE UNIQUE INDEX IF NOT EXISTS "IX_Organizations_Slug" ON "Organizations" ("Slug");
CREATE UNIQUE INDEX IF NOT EXISTS "IX_Users_Email" ON "Users" ("Email");
CREATE UNIQUE INDEX IF NOT EXISTS "IX_ProjectAgentPaths_ProjectId_RemoteAgentId"
    ON "ProjectAgentPaths" ("ProjectId", "RemoteAgentId");
CREATE UNIQUE INDEX IF NOT EXISTS "IX_WellnessRecords_UserId_RecordDate"
    ON "WellnessRecords" ("UserId", "RecordDate");
EOF

Consecuencias¶

Positivas¶

Reducción de overhead en operaciones de escritura
Menor consumo de espacio en disco e memoria
Simplificación de la estructura de índices
Query planner más eficiente

Negativas¶

Ninguna identificada (la funcionalidad se preserva completamente)

Neutrales¶

Las consultas SELECT mantienen el mismo rendimiento
La constraint UNIQUE sigue garantizando integridad de datos

Referencias¶

PostgreSQL: Indexes and Constraints
EF Core: Index and Unique Constraints
Análisis previo: docs/database/RemoteAgents-Query-Analysis.md
Informe de validación: docs/database/RemoveDuplicateIndexes-Validation-Report.md

Historial de Cambios¶

Fecha	Versión	Descripción	Autor
2026-02-01	1.0	Documento inicial	Claude Code