Diseño del Esquema y Queries Críticas

Concepto clave

En sistemas de reservas de alta demanda, el diseño del esquema y las queries críticas determinan la escalabilidad y consistencia. Imagina un estadio con 100.000 asientos: si todos intentan reservar al mismo tiempo, necesitas un sistema que procese transacciones rápidamente sin conflictos. PostgreSQL maneja esto con transacciones ACID y bloqueos optimistas, pero el diseño debe minimizar puntos de contención.

La clave está en normalización selectiva: normalizar datos estáticos (como información de usuarios) pero desnormalizar aspectos críticos para performance (como contadores de disponibilidad). Esto reduce JOINs en queries de alta frecuencia. Además, el patrón de particionamiento por tiempo (time-based partitioning) ayuda a manejar grandes volúmenes históricos sin degradar queries activas.

Cómo funciona en la práctica

Para un sistema de reservas de conciertos, seguimos estos pasos:

1. Identificar entidades críticas: eventos, asientos, reservas, y pagos.
2. Diseñar tablas con constraints que aseguren integridad (ej: UNIQUE en evento_id + asiento_id para evitar dobles reservas).
3. Implementar transacciones con bloqueos de fila (ROW LEVEL LOCK) para operaciones concurrentes en el mismo asiento.
4. Crear índices compuestos en columnas usadas en WHERE y JOIN de queries frecuentes.
5. Usar vistas materializadas para reportes agregados, actualizadas asincrónicamente.

Ejemplo: cuando 10 usuarios intentan reservar el mismo asiento, PostgreSQL usa FOR UPDATE SKIP LOCKED para que el primero obtenga el bloqueo y los otros pasen al siguiente asiento disponible, evitando esperas largas.

Codigo en accion

Primero, el esquema básico con problemas de concurrencia:

-- Tabla inicial (problema: sin manejo explícito de concurrencia)
CREATE TABLE reservas (
    id SERIAL PRIMARY KEY,
    evento_id INT NOT NULL,
    asiento_id INT NOT NULL,
    usuario_id INT NOT NULL,
    estado VARCHAR(20) DEFAULT 'pendiente',
    creado_en TIMESTAMP DEFAULT NOW(),
    UNIQUE(evento_id, asiento_id)
);

-- Query problemática para reservar
INSERT INTO reservas (evento_id, asiento_id, usuario_id)
VALUES (1, 100, 42); -- Puede fallar con concurrencia alta por violación única

Ahora, la versión optimizada:

-- Esquema mejorado con soporte para alta concurrencia
CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    fecha TIMESTAMP NOT NULL,
    total_asientos INT NOT NULL
) PARTITION BY RANGE (fecha); -- Particionamiento por tiempo

CREATE TABLE eventos_2024 PARTITION OF eventos
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE asientos (
    id SERIAL PRIMARY KEY,
    evento_id INT NOT NULL REFERENCES eventos(id),
    numero VARCHAR(10) NOT NULL,
    seccion VARCHAR(50),
    disponible BOOLEAN DEFAULT TRUE,
    UNIQUE(evento_id, numero)
);

CREATE TABLE reservas (
    id BIGSERIAL PRIMARY KEY,
    evento_id INT NOT NULL,
    asiento_id INT NOT NULL,
    usuario_id INT NOT NULL,
    estado VARCHAR(20) DEFAULT 'reservado',
    creado_en TIMESTAMP DEFAULT NOW(),
    expira_en TIMESTAMP, -- Para reservas temporales
    FOREIGN KEY (evento_id, asiento_id) REFERENCES asientos(evento_id, id),
    UNIQUE(evento_id, asiento_id) WHERE estado != 'cancelado'
);

-- Índice compuesto crítico
CREATE INDEX idx_reservas_evento_asiento 
ON reservas(evento_id, asiento_id) 
WHERE estado = 'reservado';

-- Query optimizada con bloqueo optimista
WITH intento_reserva AS (
    SELECT id FROM asientos 
    WHERE evento_id = 1 AND disponible = TRUE
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE asientos 
SET disponible = FALSE 
WHERE id = (SELECT id FROM intento_reserva)
RETURNING id;
-- Luego insertar en reservas con el asiento obtenido

Errores comunes

• No usar transacciones explícitas: realizar operaciones múltiples (ej: actualizar disponibilidad e insertar reserva) sin BEGIN/COMMIT puede dejar datos inconsistentes en fallos.
• Sobre-indexar tablas de alta escritura: cada índice añade overhead en INSERT/UPDATE; en tablas con miles de escrituras por segundo, limita a índices esenciales.
• Ignorar el plan de ejecución: no analizar EXPLAIN ANALYZE en queries críticas lleva a decisiones de indexación subóptimas.
• Bloqueos pesimistas por defecto: usar FOR UPDATE sin SKIP LOCKED en operaciones concurrentes causa cuellos de botella; mejor usar bloqueos optimistas.
• No particionar datos históricos: mantener todas las reservas en una tabla gigante ralentiza queries activas; particionar por fecha mejora mantenimiento y performance.

Checklist de dominio

1. ¿Diseñé el esquema con particionamiento para datos históricos (ej: por mes/año)?
2. ¿Implementé transacciones ACID para operaciones críticas como crear reservas?
3. ¿Usé índices compuestos en columnas frecuentemente consultadas juntas?
4. ¿Probé las queries con EXPLAIN ANALYZE bajo carga simulada?
5. ¿Apliqué bloqueos optimistas (SKIP LOCKED) en operaciones concurrentes?
6. ¿Normalicé datos estáticos y desnormalicé métricas de performance?
7. ¿Configuré timeouts y reintentos en la aplicación para manejar deadlocks?

Optimizar una Query de Reservas Concurrentes

En este ejercicio, mejorarás una query existente para manejar alta concurrencia en un sistema de reservas.

1. Contexto: Tienes una tabla asientos con columnas id, evento_id, disponible (BOOLEAN), y una tabla reservas con evento_id, asiento_id, usuario_id, estado. Actualmente, la query para reservar un asiento es:

   UPDATE asientos SET disponible = FALSE WHERE evento_id = $1 AND disponible = TRUE LIMIT 1 RETURNING id;

2. Problema: Esta query puede causar contención cuando múltiples usuarios intentan reservar asientos para el mismo evento simultáneamente, llevando a deadlocks o respuestas lentas.
3. Tarea: Refactoriza la query usando FOR UPDATE SKIP LOCKED para manejar concurrencia eficientemente. Escribe una transacción que:
   • Seleccione un asiento disponible para un evento específico.
   • Lo bloquee optimistamente para evitar conflictos.
   • Actualice su disponibilidad a FALSE.
   • Inserte un registro en la tabla reservas.
   • Maneje errores con ROLLBACK si algo falla.
4. Pasos:
   1. Inicia una transacción con BEGIN.
   2. Escribe una CTE (Common Table Expression) para seleccionar y bloquear un asiento disponible usando FOR UPDATE SKIP LOCKED.
   3. Actualiza la tabla asientos basado en el CTE.
   4. Inserta en reservas con los datos obtenidos.
   5. Finaliza con COMMIT.
   6. Prueba con múltiples sesiones simultáneas para verificar que no hay deadlocks.
5. Entrega: Proporciona el código SQL completo de la transacción optimizada.

• Usa WITH (CTE) para encapsular la selección del asiento.
• Considera agregar un índice en (evento_id, disponible) para acelerar la búsqueda.
• Incluye manejo de errores básico con EXCEPTION en PL/pgSQL si es necesario.

Evalua tu comprension

Completa el quiz interactivo de arriba para ganar XP.