Módulo 2.1 - Índices y transacciones

📈 Por qué usar índices

Un índice y una estructura auxiliar que acelera las consultas al evitar el full table scan. Sin índice, cada búsqueda recorre toda la tabla y el rendimiento cae exponencialmente a medida que crecen los datos.

🎯 Analogía: índice de un libro

Imagina que buscas un tema en un libro de 500 páginas sin índice - leerías página por página. Con el índice, vas directamente al capítulo correcto. Un índice de base de datos funciona exactamente así.

• B-tree: Estructura balanceada, ideal para rangos y ordenamiento (estándar en la mayoría de los SGBD)
• Hash: Búsqueda exacta por igualdad, O(1), pero sin compatibilidad con rangos
• Índice de cobertura: Contiene todas las columnas de la consulta, evita acceder a la tabla
• Selectividad: Cuantos más valores únicos, mejor funciona el índice

📊 Impacto real

Tabla con 1 millón de filas: escaneo completo ~500ms, escaneo por índice ~2ms
Cada índice consume espacio en disco y ralentiza las escrituras (INSERT/UPDATE/DELETE)
Índice en columna con baja selectividad (p. ej., boolean) generalmente no ayuda
PostgreSQL crea índices automáticos para PRIMARY KEY y UNIQUE

🔧 CREATE INDEX en la práctica

Crear un índice es sencillo. Crear el índice correcto exige entender la consulta, los datos y el equilibrio entre lectura y escritura. Un índice incorrecto es peor que ninguno - consume espacio y ralentiza las escrituras sin aportar beneficios.

💻 Ejemplos SQL

-- Indice simples: acelera buscas por email
CREATE INDEX idx_cliente_email ON cliente (email);

-- Indice composto: otimiza queries com WHERE cidade AND estado
CREATE INDEX idx_endereco_cidade_estado ON endereco (cidade, estado);

-- Indice parcial: apenas registros ativos (economia de espaco)
CREATE INDEX idx_pedido_ativo ON pedido (data_criacao)
WHERE status = 'ativo';

-- Indice com INCLUDE: colunas extras sem afetar a arvore
CREATE INDEX idx_produto_categoria ON produto (categoria_id)
INCLUDE (nome, preco);

-- Indice unico: garante unicidade + acelera buscas
CREATE UNIQUE INDEX idx_usuario_cpf ON usuario (cpf);

✅ HAZLO

• Indexa las columnas usadas en WHERE, JOIN y ORDER BY
• Prefiere índices compuestos para consultas de múltiples columnas
• Usa un índice parcial para filtrar subconjuntos
• Monitorea los índices no utilizados y elimínalos

❌ NO HAGAS

• Indexar columnas con baja selectividad (boolean, status)
• Crear un índice para cada columna sin analizar
• Ignorar el costo de escritura en tablas con muchos INSERTs
• Olvidar REINDEX después de grandes cargas de datos

🔬 EXPLAIN y EXPLAIN ANALYZE

EXPLAIN muestra el plan de ejecución que usará la base de datos. EXPLAIN ANALYZE ejecuta la consulta y muestra tiempos reales. Sin estas herramientas, optimizas a ciegas. Y radiografía del rendimiento.

💻 Ejemplos de uso

-- Ver plano estimado (nao executa a query)
EXPLAIN SELECT * FROM cliente WHERE email = 'ana@example.com';

-- Ver plano + tempo real (executa a query)
EXPLAIN ANALYZE SELECT * FROM cliente WHERE email = 'ana@example.com';

-- Exemplo de saida:
-- Index Scan using idx_cliente_email on cliente
--   (cost=0.29..8.31 rows=1 width=64)
--   (actual time=0.027..0.028 rows=1 loops=1)
-- Planning Time: 0.152 ms
-- Execution Time: 0.065 ms

🔍 Operadores del plan de ejecución

Seq Scan

Recorrido secuencial de toda la tabla. Señal de que falta un índice.

Index Scan

Usa el índice para localizar registros. Lo que queremos ver.

Nested Loop

JOIN fila por fila. Eficiente para pocos registros.

Hash Join

Construye una tabla hash en memoria. Es eficiente para muchos registros.

💡 Consejo práctico

Si EXPLAIN muestra Seq Scan en una tabla grande con un filtro WHERE, probablemente falta un índice en esa columna. Crea el índice y ejecuta EXPLAIN de nuevo para confirmar que cambió a Index Scan.

🔒 Transacciones ACID

Una transacción y un conjunto de operaciones que se ejecuta como una unidad atómica: todo o nada. Si falla cualquier operación, todas se revierten. Una transferencia bancaria sin transacción puede hacerte perder dinero. ACID es fundamental.

💻 Transacción en la práctica

-- Transferencia bancaria atomica
BEGIN;
  UPDATE conta SET saldo = saldo - 500.00 WHERE id = 1;
  UPDATE conta SET saldo = saldo + 500.00 WHERE id = 2;

  -- Verifica se saldo ficou negativo
  -- Se sim, desfaz tudo
  DO $$
  BEGIN
    IF (SELECT saldo FROM conta WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Saldo insuficiente';
    END IF;
  END $$;
COMMIT;

-- Se algo der errado, ROLLBACK desfaz tudo
-- ROLLBACK;

🎯 Las 4 propiedades ACID

A - Atomicidad

Todo o nada. Si una operación falla, todas se revierten.

C - Consistencia

La base de datos pasa de un estado válido a otro estado válido. Se respetan las constraints.

I - Aislamiento

Las transacciones concurrentes no interfieren entre sí. Cada una ve una instantánea consistente.

D - Durabilidad

Después de COMMIT, los datos persisten incluso si el servidor falla. WAL lo garantiza.

🔄 Niveles de aislamiento

O nivel de aislamiento define qué ve una transacción de los cambios realizados por otras transacciones concurrentes. Un aislamiento incorrecto causa lecturas sucias, lecturas fantasma y datos inconsistentes.

💻 Configuración del aislamiento

-- Definir nivel para a transacao atual
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT * FROM estoque WHERE produto_id = 42;
  -- ... operacoes ...
COMMIT;

-- Definir nivel padrao da sessao
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Verificar nivel atual
SHOW transaction_isolation;

📊 Comparación de los niveles

Nivel	Lectura sucia	Non-Repeatable	Fantasma	Uso
READ UNCOMMITTED	Sí	Sí	Sí	Casi nunca
READ COMMITTED	No	Sí	Sí	Estándar de PG
REPEATABLE READ	No	No	Sí	Informes
SERIALIZABLE	No	No	No	Crítico

💡 Consejo práctico

Para la mayoría de las aplicaciones, READ COMMITTED (el valor predeterminado de PostgreSQL) es suficiente. Usa REPEATABLE READ para informes que necesitan una instantánea coherente. SERIALIZABLE solo para operaciones financieras críticas donde la consistencia total es obligatoria.

📋 Buenas prácticas SQL

SQL mal escrito y deuda técnica. Patrones y hábitos para consultas seguras, eficientes y fáciles de mantener. Las buenas prácticas de hoy evitan incidente mañana.

✅ HAZLO

• Usa prepared statements para evitar SQL injection
• Selecciona solo las columnas necesarias (nunca SELECT *)
• Usa parámetros en lugar de concatenar valores
• Monitorea queries lentas con pg_stat_statements
• Mantén las transacciones cortas y enfocadas

❌ NO HAGAS

• SELECT * en producción (transfiere datos innecesarios)
• Concatenar entrada directa del usuario en la consulta
• Transacciones largas que mantienen locks durante minutos
• Ignorar EXPLAIN en consultas que se ejecutan en producción
• Crear índice sin probar el impacto en las escrituras

💻 Ejemplos de buenas prácticas

-- RUIM: SELECT * e concatenacao
SELECT * FROM usuario WHERE nome = '" + input + "';

-- BOM: colunas especificas e parametros
SELECT id, nome, email FROM usuario WHERE nome = $1;

-- RUIM: transacao longa com logica de negocio
BEGIN;
  SELECT * FROM pedido WHERE id = 1;
  -- ... 30 segundos de processamento em app ...
  UPDATE pedido SET status = 'aprovado' WHERE id = 1;
COMMIT;

-- BOM: transacao curta e focada
-- Faz o processamento ANTES de abrir a transacao
BEGIN;
  UPDATE pedido SET status = 'aprovado' WHERE id = 1;
  INSERT INTO log_auditoria (pedido_id, acao) VALUES (1, 'aprovado');
COMMIT;

🛡️ Lista de verificación de seguridad de SQL

• Consultas preparadas: Usar siempre para consultas con input externo
• Principio del menor privilegio: Cada app se conecta con un usuario de base de datos con permisos mínimos
• Monitoreo: pg_stat_statements + alertas para queries que superen X ms
• Revisión de consultas: Usa EXPLAIN en cada consulta nueva antes de pasarla a producción

← Volver a la Ruta 2 Módulo 2.2 - Tipos de SGBD →