🎯 Meta: llevar tu manejo de datos al siguiente nivel. Paginación que escala, búsqueda de verdad, datos flexibles con JSONB, y qué hacer cuando una tabla tiene cientos de millones de filas. Estos patrones distinguen a quien "sabe SQL" de quien "sabe diseñar datos".
Seguimos con PostgreSQL 18 (caps. 01-02).
H.1 · Paginación: offset vs cursor
Ya viste paginación con LIMIT/OFFSET. Funciona, pero no escala:
-- Paginación por offset (la de los frameworks por defecto)
SELECT * FROM productos ORDER BY id LIMIT 20 OFFSET 10000;
-- ⚠️ PostgreSQL tiene que LEER Y DESCARTAR 10000 filas para darte 20. Lento en páginas altas.Paginación por cursor (keyset): en vez de "salta 10000", dices "dame los siguientes después del último que vi". Usa un índice y es igual de rápida en la página 1 que en la 10000:
-- Primera página
SELECT * FROM productos ORDER BY id LIMIT 20;
-- Siguiente: pasa el id del último visto (ej. 20)
SELECT * FROM productos WHERE id > 20 ORDER BY id LIMIT 20;| Offset | Cursor (keyset) | |
|---|---|---|
| Rendimiento en páginas altas | ❌ Empeora | ✅ Constante |
| "Ir a la página 500" | ✅ Sí | ❌ No (solo siguiente/anterior) |
| Datos que cambian mientras paginas | ⚠️ Duplicados/saltos | ✅ Estable |
| Uso típico | Admin, tablas pequeñas | Feeds, scroll infinito, APIs |
🧠 Regla: para scroll infinito y APIs con muchos datos, usa cursor. Para un panel de admin donde saltas a páginas concretas, offset está bien. Twitter/X, Instagram y toda red social usan cursores — por eso su feed no se ralentiza aunque bajes horas.
💡 El cursor puede codificarse en Base64 para no exponer el
idcrudo:?cursor=eyJpZCI6MjB9. El cliente lo trata como opaco y lo devuelve tal cual.
H.2 · Búsqueda full-text (dentro de PostgreSQL)
LIKE '%palabra%' para buscar texto es lento (no usa índices) y tonto (no entiende plurales ni relevancia). PostgreSQL trae búsqueda full-text nativa:
-- Añade una columna generada con el vector de búsqueda
ALTER TABLE articulos ADD COLUMN busqueda tsvector
GENERATED ALWAYS AS (
to_tsvector('spanish', titulo || ' ' || contenido)
) STORED;
-- Índice GIN para que vuele
CREATE INDEX idx_articulos_busqueda ON articulos USING GIN(busqueda);
-- Buscar (entiende raíces: "programación" encuentra "programar", "programa")
SELECT titulo,
ts_rank(busqueda, query) AS relevancia
FROM articulos, to_tsquery('spanish', 'programación & backend') query
WHERE busqueda @@ query
ORDER BY relevancia DESC;to_tsvector('spanish', ...)normaliza el texto (quita acentos, plurales, palabras vacías).@@es el operador "coincide con".ts_rankordena por relevancia, no solo por coincidencia.
💡 Para el 90% de las apps, la búsqueda full-text de PostgreSQL es suficiente y te ahorra montar Elasticsearch/OpenSearch (otra pieza que operar). Solo cuando necesites búsqueda a escala masiva, faceting complejo o typo-tolerance avanzada, considera un motor dedicado (Elasticsearch, Meilisearch, Typesense). Empieza por lo que ya tienes.
⚠️
LIKE '%x%'(comodín al inicio) nunca usa índice → escaneo completo. Para "empieza por" ('x%') sí puede usarlo. Para "contiene en cualquier parte", usa full-text o un índice trigram (pg_trgm).
H.3 · JSONB: datos flexibles y consultables
Cuando parte de tus datos no tiene esquema fijo (metadatos, configuraciones, atributos variables de productos), JSONB te da flexibilidad sin renunciar a consultar e indexar:
CREATE TABLE productos (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nombre VARCHAR(255),
atributos JSONB -- { "color": "rojo", "tallas": ["S","M"], "peso_g": 300 }
);
-- Insertar
INSERT INTO productos (nombre, atributos)
VALUES ('Camiseta', '{"color": "rojo", "tallas": ["S","M","L"], "peso_g": 200}');
-- Consultar dentro del JSON
SELECT nombre FROM productos WHERE atributos->>'color' = 'rojo'; -- ->> devuelve texto
SELECT nombre FROM productos WHERE atributos @> '{"color": "rojo"}'; -- @> "contiene"
SELECT nombre FROM productos WHERE atributos->'tallas' ? 'M'; -- ? "tiene la clave/elemento"
-- Índice GIN para consultas JSONB rápidas
CREATE INDEX idx_productos_attr ON productos USING GIN(atributos);🧠 JSONB vs columnas normales — cuándo usar cada uno:
- Columnas normales: datos que consultas/filtras siempre, con tipo fijo (precio, stock). Más rápidos, con constraints e integridad.
- JSONB: datos variables, opcionales o que cambian de forma según el tipo de producto. Flexible pero sin las garantías de una columna.
⚠️ No metas TODO en un JSONB "por comodidad". Perderías integridad referencial, tipos y validación. Es un complemento para lo flexible, no un reemplazo del modelo relacional. Un error común es usar la BD como un almacén de blobs JSON y acabar con datos inconsistentes.
H.4 · Soft delete y auditoría
En sistemas serios raramente se borra de verdad. Soft delete = marcar como borrado sin eliminar la fila (recuperable, auditable):
ALTER TABLE pedidos ADD COLUMN borrado_en TIMESTAMPTZ;
-- "Borrar" = marcar
UPDATE pedidos SET borrado_en = now() WHERE id = 5;
-- Las consultas normales excluyen los borrados
SELECT * FROM pedidos WHERE borrado_en IS NULL;
-- Vista para no repetir el filtro
CREATE VIEW pedidos_activos AS SELECT * FROM pedidos WHERE borrado_en IS NULL;Los ORMs lo automatizan: SoftDeletes en Laravel, paranoid en algunos, un scope global en otros.
💡 Combínalo con los triggers de auditoría (cap. 02): soft delete conserva el "qué hay ahora", la tabla de auditoría conserva "qué pasó y cuándo". Juntos te dan trazabilidad total, imprescindible en banca, salud o ERP (como tu CLAINEV ERP).
H.5 · Concurrencia: evitar pisarse los datos
Dos usuarios editan el mismo recurso a la vez. ¿Quién gana? Dos estrategias:
Bloqueo optimista (recomendado para web): una columna version que se comprueba al guardar:
-- El UPDATE solo aplica si la versión no cambió desde que la leíste
UPDATE productos
SET stock = 5, version = version + 1
WHERE id = 1 AND version = 3; -- si version ya no es 3, afecta 0 filas → conflictoSi afecta 0 filas, otro usuario ya lo modificó: respondes 409 Conflict y el cliente reintenta con los datos frescos.
Bloqueo pesimista (para operaciones críticas como stock/dinero): SELECT ... FOR UPDATE bloquea la fila hasta el COMMIT:
BEGIN;
SELECT stock FROM productos WHERE id = 1 FOR UPDATE; -- nadie más puede tocarla
-- ...comprobar y decrementar stock...
UPDATE productos SET stock = stock - 1 WHERE id = 1;
COMMIT;⚠️ El bug de concurrencia clásico: dos compras del último ítem en stock a la vez. Ambas leen "stock = 1", ambas restan, acabas con stock = -1 y dos clientes con el mismo producto.
FOR UPDATEo un bloqueo optimista lo evitan. Es un fallo que solo aparece bajo carga real — imposible de ver en pruebas manuales, por eso hay que diseñarlo desde el principio.
H.6 · Escala: índices avanzados y particionado
Índices parciales (solo indexan lo que consultas → más pequeños y rápidos):
-- Solo indexa los pedidos pendientes (que son los que consultas mucho)
CREATE INDEX idx_pedidos_pendientes ON pedidos(creado_en)
WHERE estado = 'pendiente';Índices compuestos — el orden importa (regla "izquierda a derecha"):
CREATE INDEX idx ON pedidos(usuario_id, estado, creado_en);
-- Sirve para filtrar por usuario_id, o usuario_id+estado, o los tres.
-- NO sirve para filtrar solo por estado (no es la primera columna).Particionado — cuando una tabla es gigante (cientos de millones de filas), la divides en trozos por fecha o rango. PostgreSQL las trata como una sola pero opera sobre el trozo relevante:
CREATE TABLE eventos (
id BIGINT, creado_en TIMESTAMPTZ, datos JSONB
) PARTITION BY RANGE (creado_en);
CREATE TABLE eventos_2026 PARTITION OF eventos
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');Consultas por fecha solo tocan la partición correspondiente (mucho más rápidas), y puedes archivar o borrar particiones viejas al instante (DROP TABLE eventos_2024).
🧠 La secuencia de optimización, en orden: 1) escribe la consulta, 2) mírala con
EXPLAIN ANALYZE(cap. 01), 3) si vesSeq Scansobre tabla grande → añade índice, 4) si el índice no basta y la tabla es enorme → considera particionar. No optimices a ciegas ni antes de medir (YAGNI, cap. 10): el 99% de los problemas de rendimiento se arreglan con el índice correcto.
H.7 · Read replicas y separación lectura/escritura
Cuando las lecturas superan a tu BD, replicas: una BD primaria para escrituras y varias réplicas de solo lectura. Repartes las consultas:
Escrituras (INSERT/UPDATE) ──► BD Primaria ──replica──► Réplica 1 (lecturas)
└─replica──► Réplica 2 (lecturas)⚠️ Cuidado con el lag de replicación: las réplicas van unos milisegundos por detrás. Si un usuario crea algo y justo después lo lee de una réplica, podría no verlo aún ("lo guardé pero no aparece"). Para acciones donde el usuario espera ver su cambio inmediato, lee de la primaria. Esto se llama read-your-own-writes y es una trampa sutil.
H.8 · Cuando una tabla no basta: CQRS, outbox y saga (introducción)
Todo H.1-H.7 asume una verdad: una base de datos relacional modela tu dominio. Eso te lleva lejos (H.6 lo dice: la mayoría de problemas de rendimiento se arreglan con el índice correcto), pero hay un límite estructural que ni el mejor índice resuelve: cuando lecturas y escrituras tienen necesidades de forma completamente distintas, o cuando una operación de negocio toca más de un sistema (tu BD + una cola + un buscador, ap. T).
CQRS (Command Query Responsibility Segregation): separar el modelo de escritura (normalizado, con constraints, optimizado para integridad) del modelo de lectura (desnormalizado, optimizado para la pantalla exacta que lo consume):
-- Escritura: tablas normalizadas de siempre (pedidos, lineas_pedido, productos)
-- Lectura: una tabla/vista materializada, ya con la forma exacta que pide el dashboard
CREATE MATERIALIZED VIEW resumen_pedidos AS
SELECT p.id, p.usuario_id, count(lp.id) AS items, sum(lp.precio * lp.cantidad) AS total
FROM pedidos p JOIN lineas_pedido lp ON lp.pedido_id = p.id
GROUP BY p.id, p.usuario_id;
REFRESH MATERIALIZED VIEW CONCURRENTLY resumen_pedidos; -- se actualiza periódicamente, no en cada escritura🧠 No es "para todo". CQRS añade una fuente de verdad de escritura Y una(s) proyección(es) de lectura que hay que mantener sincronizadas — el mismo problema de consistencia eventual del apéndice T (buscador dedicado). Empieza con vistas materializadas de PostgreSQL (arriba) antes de pensar en bases de datos de lectura separadas; resuelve el 90% del caso sin la complejidad del 100%.
Outbox pattern: cuando una operación debe cambiar tu BD y notificar a otro sistema (una cola, un evento) de forma atómica, no puedes hacer un UPDATE y un publish() por separado — si el proceso muere entre los dos, quedan desincronizados para siempre (el mismo problema que T.4 describe para el buscador):
BEGIN;
UPDATE pedidos SET estado = 'pagado' WHERE id = 42;
INSERT INTO outbox (evento, payload) VALUES ('pedido.pagado', '{"pedido_id": 42}');
COMMIT;
-- Un worker aparte LEE la tabla outbox y publica a Kafka/RabbitMQ (ap. S) de forma reintentable.
-- Si el worker falla a mitad, el evento sigue en outbox: no se pierde, solo se reintenta.🔗 Tratamiento completo en el apéndice S (Kafka/RabbitMQ) — ahí ves el worker que consume la tabla
outboxy la publica de verdad. Aquí lo importante es el porqué: la única forma segura de que "cambio en BD" y "evento publicado" ocurran juntos es que ambos vivan en la misma transacción de la misma base de datos — nunca unUPDATEseguido de unpublish()suelto.
Event Sourcing: en vez de guardar solo el estado actual de un pedido (una fila que se sobrescribe con cada UPDATE), guardas la secuencia completa de eventos que lo llevó ahí. El estado actual se deriva reproduciendo esos eventos, no se guarda como verdad principal:
-- En vez de UPDATE pedidos SET estado = 'pagado' (pierdes el "cómo llegó aquí"):
CREATE TABLE eventos_pedido (
id BIGINT GENERATED ALWAYS AS IDENTITY,
pedido_id BIGINT,
tipo TEXT, -- 'creado', 'pagado', 'enviado', 'cancelado'
payload JSONB,
ocurrido_en TIMESTAMPTZ DEFAULT now()
);
INSERT INTO eventos_pedido (pedido_id, tipo, payload) VALUES
(42, 'creado', '{"items": [...]}'),
(42, 'pagado', '{"metodo": "tarjeta", "monto": 89.90}');
-- El estado actual se RECONSTRUYE aplicando los eventos en orden (o se cachea en una
-- proyección de lectura, el mismo concepto de CQRS de arriba, para no recalcular siempre)🧠 Qué gana y qué cuesta: ganas una auditoría perfecta ("exactamente qué pasó y cuándo", reconstruir el estado en cualquier punto del pasado, depurar bugs viendo la secuencia real) y la capacidad de añadir nuevas proyecciones de lectura sin perder historia. Cuesta complejidad real: consultar "el estado actual" ya no es un
SELECTdirecto, y necesitas proyecciones (CQRS) para que sea rápido. No lo adoptes para un CRUD normal — brilla cuando el histórico de cambios ES parte del valor de negocio (contabilidad, trazabilidad regulatoria, sistemas tipo ERP como CLAINEV). Para todo lo demás, una tabla de auditoría (H.4) ya te da suficiente trazabilidad con una fracción de la complejidad.
Saga pattern: cuando una operación de negocio abarca varios servicios con su propia BD (reservar inventario → cobrar → confirmar envío) y no puedes usar una transacción SQL normal porque cruza fronteras de servicio:
1. Reservar inventario ✅
2. Cobrar el pago ❌ (tarjeta rechazada)
3. Compensar: liberar inventario reservado en el paso 1 ← "deshacer" manual, no un ROLLBACK💡 Una saga es una secuencia de pasos locales, cada uno con su acción compensatoria para deshacer el efecto si un paso posterior falla — porque no existe un
ROLLBACKque abarque varias bases de datos a la vez. Es un patrón de arquitectura de microservicios (cap. 11 avisa: empieza con un monolito); si tu app sigue siendo un monolito con una sola BD, una transacción SQL normal ya te da esta garantía gratis y no necesitas saga en absoluto.
✅ Ejercicio del apéndice
Sobre tu API del blog (con datos de sobra: genera miles de artículos):
1. Cambia el listado de offset a paginación por cursor. Compara la velocidad
de la "página 1" vs la "página 500" en ambos enfoques con EXPLAIN ANALYZE.
2. Añade búsqueda full-text en español sobre título+contenido, ordenada por
relevancia, con índice GIN.
3. Añade una columna JSONB "metadatos" a los artículos y consúltala con @> e índice GIN.
4. Implementa soft delete + una vista de artículos activos.
4.1. (Opcional, avanzado) Modela el ciclo de vida de un pedido como eventos
(creado/pagado/cancelado) en vez de una sola fila con estado, y reconstruye
el estado actual a partir de ellos.
5. Simula el bug de concurrencia (dos updates a la vez) y resuélvelo con
bloqueo optimista (columna version → 409 Conflict).
6. Crea un índice parcial para los artículos "borrador" y verifica con EXPLAIN
que se usa.💡 Pistas de la solución
- Para comparar offset vs cursor, genera al menos 100.000 artículos (
generate_seriesen SQL lo hace en segundos) — con pocas filas la diferencia de rendimiento no se nota. - El índice GIN de full-text y el de JSONB son índices distintos aunque ambos usen GIN: cada uno indexa la columna que le corresponde. No compartas uno para las dos consultas.
- Para simular el bug de concurrencia, abre dos conexiones (dos pestañas de
psqlo dos scripts) y ejecuta elUPDATEconversiondesde ambas casi a la vez — la segunda debe afectar 0 filas. EXPLAIN ANALYZEsobre el índice parcial debe mostrarIndex Scanusando tu índiceidx_articulos_borrador— si vesSeq Scan, revisa que elWHEREde tu consulta coincide exactamente con elWHEREdel índice.
🧠 Autoevaluación
Porque el cursor no cuenta posiciones: solo sabe "dame lo siguiente después de este id/valor". Sin recorrer las páginas anteriores, no hay forma de saber qué cursor corresponde a la posición 500. Es el precio de tener rendimiento constante en vez de saltos arbitrarios.
Columna normal. JSONB es para datos variables u opcionales que no consultas siempre con el mismo patrón. Un atributo que filtras constantemente merece su propia columna tipada, con índice B-tree normal — más rápido y con garantías de tipo que un ->>'color' sobre JSONB.
Porque sobrescribir silenciosamente perdería el cambio del otro usuario sin que nadie se entere ("lost update"). El 409 fuerza al cliente a enterarse del conflicto, recargar los datos frescos y decidir cómo proceder — la alternativa segura a "el último que guarda gana".
No. La regla "izquierda a derecha" de los índices compuestos significa que solo son útiles si la consulta usa las columnas empezando por la primera (usuario_id). Filtrar solo por estado no puede aprovechar este índice — necesitarías uno donde estado sea la primera columna, o uno dedicado solo a estado.
Cuando la tabla tiene cientos de millones de filas y ni siquiera con los índices correctos EXPLAIN ANALYZE mejora lo suficiente — el particionado añade complejidad operativa (gestión de particiones, límites en algunas constraints) que no vale la pena pagar hasta que el problema es real y medido, siguiendo la misma secuencia de optimización de H.6.
Si el proceso muere entre las dos operaciones (tras el UPDATE, antes del publish, o viceversa), la base de datos y el sistema de mensajería quedan desincronizados para siempre — nadie se entera de que el evento nunca se publicó. El outbox pattern lo arregla escribiendo el evento en una tabla outbox dentro de la MISMA transacción que el UPDATE: ambos cambios se confirman juntos o ninguno, y un worker aparte publica desde esa tabla de forma reintentable.
No. Una transacción SQL normal (BEGIN/COMMIT/ROLLBACK) ya garantiza que ambos pasos ocurran juntos o ninguno, porque ambos viven en la misma base de datos. El patrón saga solo resuelve el problema cuando esos pasos cruzan varios servicios con su propia BD y no existe un ROLLBACK que los abarque a todos — el mismo consejo del cap. 11: no adoptes complejidad de microservicios que tu monolito no necesita.
Con una tabla de auditoría, la fila principal (con UPDATE) sigue siendo la fuente de verdad y la auditoría es un registro secundario "por si acaso". Con Event Sourcing, los eventos SON la fuente de verdad — el estado actual no existe como tal hasta que reproduces la secuencia (o la cacheas en una proyección). Es un cambio de modelo, no solo añadir una tabla más.
Volver al: README.md · Relacionado: 01-bases-de-datos-sql.md, 02-sql-avanzado.md, B-redis-cache-colas.md