DB Development: SQL, modelado, transacciones, índices

Diseñar schemas que escalan, elegir el índice que acelera la query correcta sin destruir las escrituras, leer un EXPLAIN ANALYZE, garantizar corrección bajo concurrencia y decidir cuándo NO usar SQL. Un mid escribe queries que funcionan; un senior decide.

Teoría

Los ejemplos usan PostgreSQL como dialecto de referencia. La sintaxis SQL es en gran parte estándar (ANSI), pero se marca explícitamente cuando algo es específico de Postgres.

RDBMS: tablas, columnas, filas, claves y relaciones

Un RDBMS organiza los datos en tablas (relaciones): cada fila (tupla) es un registro, cada columna (atributo) tiene un tipo. El modelo relacional se apoya en dos garantías estructurales que otros modelos no dan gratis: integridad de entidad (cada fila es identificable de forma única) e integridad referencial (las referencias entre tablas siempre apuntan a filas que existen).

• Primary key (PK): identifica de forma única cada fila. NOT NULL + UNIQUE implícito. Puede ser natural (un dato del dominio, p.ej. email) o surrogate (un id sin significado de negocio: SERIAL/BIGSERIAL/UUID). En la práctica senior casi siempre surrogate, porque las claves naturales cambian (la gente cambia de email) y arrastrar ese cambio por todas las FK es doloroso.
• Foreign key (FK): una columna que referencia la PK de otra tabla. El motor rechaza insertar una FK que apunte a una fila inexistente, y controla qué pasa al borrar el padre vía ON DELETE.
• Unique / Check / Not null: constraints que codifican invariantes de negocio en el schema, no en el código de la app. Esta es la diferencia clave: un CHECK (balance >= 0) lo respetan todos los clientes que escriban a la tabla, no solo tu servicio.

CREATE TABLE customers (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      VARCHAR(255) UNIQUE NOT NULL,
    name       VARCHAR(255) NOT NULL,
    tier       VARCHAR(20) NOT NULL DEFAULT 'regular'
               CHECK (tier IN ('regular', 'premium', 'enterprise')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    total       DECIMAL(12, 2) NOT NULL DEFAULT 0 CHECK (total >= 0),
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ON DELETE — la decisión que se olvida:

Tipos numéricos — la trampa del dinero: nunca uses FLOAT/REAL/DOUBLE para dinero. Son binarios y no representan 0.10 exacto → errores de redondeo que se acumulan. Usa DECIMAL(p, s) (exacto) o guarda centavos como enteros (BIGINT). Igual con TIMESTAMPTZ (con zona) sobre TIMESTAMP (sin zona) para eventos: guardar sin zona es un bug esperando a un servidor en otro huso.

Views

Una view es una query guardada con nombre, que se comporta como una tabla virtual. No almacena datos (salvo la materializada): se re-ejecuta la query subyacente en cada SELECT.

-- View: encapsula una query recurrente; simplifica el consumo
CREATE VIEW active_premium_customers AS
SELECT id, email, name, created_at
FROM customers
WHERE tier = 'premium';

SELECT * FROM active_premium_customers WHERE created_at > NOW() - INTERVAL '30 days';

Usos senior de views: (1) capa de abstracción/contrato estable sobre un schema que puede refactorizarse por debajo; (2) seguridad — dar SELECT sobre una view que excluye columnas sensibles en lugar de sobre la tabla base; (3) simplificar joins recurrentes.

Materialized view: almacena físicamente el resultado; hay que refrescarla (REFRESH MATERIALIZED VIEW). Es denormalización controlada por el motor: úsala para agregaciones caras de reporting que toleran datos algo desactualizados.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day, SUM(total) AS revenue
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- no bloquea lecturas

DDL vs DML

• DDL (Data Definition Language) define la estructura: CREATE, ALTER, DROP, TRUNCATE. En Postgres el DDL es transaccional (puedes BEGIN; ALTER ...; ROLLBACK;), a diferencia de MySQL/Oracle donde un DDL hace commit implícito. Esto importa para migraciones seguras.
• DML (Data Manipulation Language) manipula los datos: INSERT, UPDATE, DELETE, SELECT.

-- DDL: evolución de schema
ALTER TABLE orders ADD COLUMN currency VARCHAR(3) NOT NULL DEFAULT 'COP';
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE orders ADD CONSTRAINT chk_status
    CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled'));
DROP TABLE IF EXISTS legacy_orders;

DELETE vs TRUNCATE vs DROP: DELETE borra filas (fila por fila, dispara triggers, se puede filtrar con WHERE, es transaccional y lento en masa). TRUNCATE vacía la tabla entera (rapidísimo, no dispara triggers de fila, resetea secuencias). DROP elimina la tabla y su estructura.

-- DML básico
INSERT INTO customers (email, name) VALUES ('a@x.com', 'Ana')
RETURNING id;                        -- Postgres: devuelve la fila insertada

UPDATE orders SET status = 'shipped', updated_at = NOW()
WHERE id = '...' AND status = 'confirmed';   -- guard en el WHERE: solo transiciona si estaba confirmed

DELETE FROM orders WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL '1 year';

Regla dura: un UPDATE/DELETE sin WHERE toca toda la tabla. En producción, ejecuta primero el SELECT con el mismo WHERE para ver qué filas afectará.

Merge / Upsert

Upsert = insertar, y si ya existe (violaría una constraint única), actualizar. Resuelve el clásico read-modify-write con race condition (“¿existe? no → inserto”; entre el check y el insert, otro proceso insertó).

-- Postgres: INSERT ... ON CONFLICT (upsert atómico)
INSERT INTO product_stock (product_id, qty)
VALUES ('P1', 100)
ON CONFLICT (product_id)
DO UPDATE SET qty = product_stock.qty + EXCLUDED.qty;  -- EXCLUDED = la fila que se intentó insertar

-- ON CONFLICT DO NOTHING: insertar solo si no existe, sin error
INSERT INTO customers (email, name) VALUES ('a@x.com', 'Ana')
ON CONFLICT (email) DO NOTHING;

El SQL estándar tiene MERGE (Postgres 15+, SQL Server, Oracle) que es más general (permite WHEN MATCHED / WHEN NOT MATCHED con DELETE), pero para el caso insert-or-update ON CONFLICT es más idiomático y seguro en Postgres.

Selección: WHERE, JOINs, UNION, subqueries

Joins — cuándo cada uno

-- INNER JOIN: solo filas con match en AMBAS tablas
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN: TODAS las de la izquierda + match (o NULL). Preserva filas sin relación.
SELECT c.name, COUNT(o.id) AS order_count   -- COUNT(o.id) cuenta 0 para clientes sin órdenes
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Anti-join: filas SIN match (clientes que nunca ordenaron)
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Trampa del LEFT JOIN + WHERE: si filtras la tabla derecha en el WHERE (WHERE o.status = 'x'), conviertes el LEFT JOIN en INNER JOIN de facto, porque NULL = 'x' es falso y descarta las filas sin match. Si necesitas el filtro y preservar las filas, ponlo en el ON: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'x'.

UNION vs UNION ALL

-- UNION: combina y ELIMINA duplicados (hace un sort/hash costoso)
SELECT email FROM customers
UNION
SELECT email FROM prospects;

-- UNION ALL: combina y CONSERVA duplicados. Más rápido: sin dedup.
SELECT 'order' AS kind, id FROM orders
UNION ALL
SELECT 'refund', id FROM refunds;

Regla: usa UNION ALL por defecto; solo UNION cuando realmente necesitas deduplicar, porque el dedup implica un pase adicional. Ambas ramas deben tener el mismo número de columnas y tipos compatibles.

Subqueries y CTEs

-- Subquery escalar (correlacionada): se evalúa por cada fila externa
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS n
FROM customers c;

-- CTE (Common Table Expression): nombra un subresultado; legibilidad + reuso
WITH high_value AS (
    SELECT customer_id, SUM(total) AS spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 1000000
)
SELECT c.name, h.spent
FROM high_value h
JOIN customers c ON c.id = h.customer_id
ORDER BY h.spent DESC;

EXISTS vs IN: para chequear existencia, WHERE EXISTS (SELECT 1 FROM ...) suele optimizar mejor que IN (subquery) y maneja NULLs sin sorpresas (NOT IN con un NULL en el conjunto devuelve cero filas — bug clásico). Nota Postgres: las CTEs son un optimization fence solo si son MATERIALIZED; desde PG12 las CTEs simples se inlinean por defecto.

Agregaciones y window functions

-- GROUP BY: colapsa filas en una por grupo. HAVING filtra grupos (WHERE filtra filas).
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE created_at > '2020-01-01'      -- filtra filas ANTES de agrupar
GROUP BY customer_id
HAVING SUM(total) > 500000;          -- filtra grupos DESPUÉS de agregar

-- Window function: agrega SIN colapsar; ves la fila individual Y su contexto agregado
SELECT
    id, customer_id, total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth_order,
    SUM(total)   OVER (PARTITION BY customer_id ORDER BY created_at
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    LAG(total)   OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_total
FROM orders;

GROUP BY y window functions responden preguntas distintas. GROUP BY cuando solo quieres el agregado (“revenue total por cliente”). Window function cuando necesitas el agregado junto a cada fila (“cada orden con el ranking dentro de su cliente”, “top 3 por grupo”, “running total”) — cosas imposibles con GROUP BY sin self-joins. Funciones comunes: ROW_NUMBER, RANK (con gaps: 1,1,3), DENSE_RANK (sin gaps: 1,1,2), LAG/LEAD, SUM/AVG OVER, FIRST_VALUE/LAST_VALUE.

Data modeling y normalización

Modelar es decidir dónde vive cada dato de forma que la verdad esté en un solo lugar (normalización) o duplicada a propósito para leer rápido (denormalización). El proceso: identificar entidades → atributos → relaciones (1:1, 1:N, N:M) → aplicar formas normales → decidir dónde romperlas conscientemente.

Relación N:M se modela con una tabla de unión (junction/join table):

-- Un pedido tiene muchos productos; un producto está en muchos pedidos → N:M
CREATE TABLE order_items (
    order_id   UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity   INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,   -- precio AL MOMENTO de compra (denormalización intencional)
    PRIMARY KEY (order_id, product_id)   -- PK compuesta: evita el mismo producto dos veces por orden
);

Formas normales

Normalizar = eliminar redundancia y anomalías de actualización/inserción/borrado. Las tres primeras cubren el 99% de los casos:

• 1NF (Primera): valores atómicos, sin grupos repetidos ni columnas multivaluadas. Nada de phones = "300-111, 300-222" en una celda ni columnas phone1, phone2, phone3. Una fila por valor.
• 2NF (Segunda): ya en 1NF y todo atributo no-clave depende de la PK completa, no de una parte. Aplica cuando la PK es compuesta. Si en order_items(order_id, product_id) guardaras product_name, este depende solo de product_id (media clave) → viola 2NF → sácalo a products.
• 3NF (Tercera): ya en 2NF y sin dependencias transitivas: ningún atributo no-clave depende de otro atributo no-clave. Si orders tuviera customer_id y customer_email, el email depende de customer_id (no de la PK de la orden) → transitivo → vive en customers.

Regla mnemotécnica clásica: cada atributo no-clave depende de “the key, the whole key, and nothing but the key” (1NF, 2NF, 3NF respectivamente). BCNF es un refinamiento de 3NF para casos con múltiples claves candidatas solapadas; raro en la práctica.

Normalizado (3NF) — integridad primero:

-- El nombre del cliente vive en UN solo lugar. Cambiarlo = 1 UPDATE.
-- Costo: mostrar "orden + nombre cliente + items" requiere 3 JOINs.
CREATE TABLE customers (id UUID PRIMARY KEY, name TEXT, email TEXT UNIQUE);
CREATE TABLE orders    (id UUID PRIMARY KEY, customer_id UUID REFERENCES customers(id), total DECIMAL);

Denormalizado — lectura primero:

-- Duplica customer_name en cada orden para evitar el JOIN. Lecturas ultrarrápidas.
-- Costo: si el cliente cambia de nombre, hay que actualizarlo en TODAS sus órdenes,
-- o los datos quedan inconsistentes.
CREATE TABLE order_summaries (
    order_id      UUID PRIMARY KEY,
    customer_id   UUID,
    customer_name TEXT,      -- duplicado
    total         DECIMAL,
    item_count    INT        -- pre-calculado
);

La pregunta senior: ¿con qué frecuencia se lee esto vs se escribe, y qué pasa cuando el dato fuente cambia — cuántas copias tengo que actualizar y qué pasa si olvido una? La denormalización es una optimización que se gana con evidencia (una query lenta medida), no un default.

Dos patrones de denormalización controlada que no rompen integridad: generated columns (el motor mantiene el valor derivado) y snapshots intencionales (copiar el precio al momento de compra: no es redundancia, es un dato histórico distinto del precio actual).

-- Generated column: el motor la calcula; imposible desincronizar
ALTER TABLE order_items
  ADD COLUMN subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED;

Índices y EXPLAIN

Un índice es una estructura de datos separada (por defecto un B-tree en Postgres) que permite al planner encontrar filas sin escanear toda la tabla. Convierte un WHERE col = x de O(n) (Sequential Scan, lee toda la tabla) a O(log n) (Index Scan, navega el árbol).

El trade-off fundamental: cada índice acelera lecturas pero ralentiza escrituras (cada INSERT/UPDATE/DELETE debe actualizar todos los índices de la tabla) y ocupa espacio. La pregunta no es “¿indexo?” sino “¿qué queries son críticas y con qué frecuencia escribo?”.

Tipos de índice (Postgres)

CREATE INDEX idx_orders_customer ON orders(customer_id);        -- FK: siempre indexar
CREATE INDEX idx_orders_created  ON orders(created_at);         -- rangos y ORDER BY
CREATE INDEX idx_orders_pending  ON orders(created_at)          -- partial: solo lo que más se consulta
       WHERE status = 'pending';

Postgres NO indexa las FK automáticamente (MySQL/InnoDB sí). Sin índice en customer_id, un JOIN o un ON DELETE CASCADE hace Seq Scan. Es la causa #1 de joins lentos.

Cuándo NO indexar: tablas pequeñas (el Seq Scan gana por cache locality), columnas de baja cardinalidad (un boolean que devuelve el 50% de filas — el planner ignora el índice y hace Seq Scan de todos modos), columnas de alta frecuencia de escritura sin lecturas selectivas.

Índices compuestos y la regla del prefijo izquierdo

CREATE INDEX idx_orders_cust_status ON orders(customer_id, status);
-- Sirve para:   WHERE customer_id = ?                    (prefijo)
--               WHERE customer_id = ? AND status = ?     (completo)
-- NO sirve para: WHERE status = ?                        (no es prefijo izquierdo)

Un índice (A, B, C) sirve para filtros por A, A+B, o A+B+C, pero no para B solo ni C solo. El orden de las columnas es una decisión: pon primero la columna de igualdad más selectiva, y las de rango al final (un rango en la primera columna “corta” el uso de las siguientes para filtrado exacto).

Covering index (INCLUDE): si el índice contiene todas las columnas que la query necesita, Postgres hace un Index Only Scan sin tocar la tabla (heap):

CREATE INDEX idx_orders_cust_covering ON orders(customer_id) INCLUDE (total, status);
-- SELECT total, status FROM orders WHERE customer_id = ? → Index Only Scan

EXPLAIN ANALYZE — leer un plan

EXPLAIN estima; EXPLAIN ANALYZE ejecuta y reporta tiempos y filas reales. EXPLAIN (ANALYZE, BUFFERS) añade cache hits vs lecturas de disco.

EXPLAIN ANALYZE
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

Limit  (cost=1234.56..1234.78 rows=20) (actual time=8.1..8.2 rows=20 loops=1)
  -> Sort  (actual time=8.1..8.1 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 45kB
        -> Hash Join  (actual time=0.5..7.2 rows=4500 loops=1)
              Hash Cond: (o.customer_id = c.id)
              -> Seq Scan on orders o  (actual time=0.1..6.0 rows=4500 loops=1)
                    Filter: (status = 'pending')
                    Rows Removed by Filter: 45500          <-- leyó 50000 para devolver 4500
              -> Hash  (actual rows=1000 loops=1)
                    -> Seq Scan on customers c

Qué leer (de adentro hacia afuera; el nodo más interno corre primero):

Para el plan de arriba, el fix es un índice que cubra el filtro (y ya de paso el orden):

CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- Ahora: Index Scan using idx_orders_status_created → lee ~las 20 filas del LIMIT, no 50000.

Workflow de diagnóstico de query lenta (respuesta de entrevista): (1) ¿creció el dataset? lo que era rápido en 10k necesita índice en 1M. (2) EXPLAIN ANALYZE → nodo con mayor actual time. (3) ¿Seq Scan en tabla grande? → candidato a índice. (4) ¿estimado vs real muy distinto? → VACUUM ANALYZE. (5) ¿sort a disco? → índice sobre la columna del ORDER BY. (6) validar comparando EXPLAIN antes/después.

Cursors, stored procedures, functions, triggers

Herramientas del lado del servidor. La regla senior: la lógica de negocio vive en la app (testeable, versionada, portable); en la DB van los invariantes de datos (constraints) y, con criterio, operaciones data-local de alto volumen.

• Cursor: permite iterar un result set fila por fila en el servidor, sin materializar todo en memoria. Útil para procesar millones de filas en un batch sin cargarlas de golpe. En Python, los drivers exponen server-side cursors (psycopg con name=...) para streamear resultados grandes.
• Stored procedure / function: código SQL/PL-pgSQL guardado en la DB. Una function devuelve un valor y se llama dentro de queries; una procedure (PG11+) puede gestionar transacciones (COMMIT interno). Justifícalas para lógica data-local que de otro modo serían N round-trips desde la app.
• Trigger: ejecuta una función automáticamente ante un evento (BEFORE/AFTER INSERT/UPDATE/DELETE). Útil para cross-cutting concerns de datos: audit trails, mantener updated_at, sincronizar una columna denormalizada. Riesgo: esconde efectos secundarios → complica testing y debugging. Úsalos con moderación.

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Transacciones: ACID

ACID no es una sigla para memorizar: son cuatro garantías que el motor te da (o no).

• A — Atomicity (todo o nada): si algo falla a mitad, TODO se deshace; no hay estado intermedio. Sin atomicity, un débito ejecuta pero el crédito falla → $100 desaparecen del sistema.
• C — Consistency (reglas siempre válidas): cada transacción lleva la DB de un estado válido a otro válido, respetando constraints/FK/checks. Sin consistency: saldos negativos, órdenes sin cliente, precios en -1.
• I — Isolation (transacciones no se ven a medias): el resultado de transacciones concurrentes debe equivaler a alguna ejecución en serie. Es el nivel más matizado (ver abajo).
• D — Durability (lo confirmado permanece): tras COMMIT, los datos sobreviven un crash. En Postgres esto es el WAL (Write-Ahead Log): cada cambio va primero al log en disco, luego a las páginas de datos.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;   -- si el segundo UPDATE falla, ROLLBACK automático deshace el primero

Trade-off central: ACID te da consistencia fuerte y simplicidad mental (“si el COMMIT pasó, todo está bien”) a cambio de latencia y throughput (locks o MVCC con overhead de versiones, escrituras sincronizadas a disco). La regla de oro: la pregunta correcta no es “¿uso ACID?” sino “¿qué pasa si esta operación se ejecuta a medias?”. Si la respuesta es “un incidente de negocio” (dinero, stock, facturas) → necesitas ACID. Si es “da igual” (logs de telemetría, eventos analíticos) → optimiza por velocidad de escritura.

Durability configurable: synchronous_commit = off en Postgres acelera escrituras pero acepta perder las últimas transacciones ante un crash. Es un dial, no un absoluto.

Isolation levels

Más aislamiento = más corrección pero menos concurrencia (más locks/reintentos). El estándar SQL define cuatro niveles por las anomalías que permiten:

Las anomalías, con ejemplo:

• Dirty read: leer un cambio de otra transacción que aún no commiteó (y podría hacer rollback). Lees datos que “nunca existieron”.
• Non-repeatable read: lees una fila, otra tx la actualiza y commitea, la relees dentro de tu misma tx y el valor cambió.
• Phantom read: ejecutas SELECT ... WHERE x > 5 dos veces; entre ambas, otra tx insertó filas que ahora aparecen (fantasmas).
• Lost update: dos tx leen balance=100, cada una calcula 100-10 en la app y escriben 90; un débito se perdió.

Detalles que un senior conoce (Postgres):

• El default es READ COMMITTED (cada statement ve un snapshot fresco del commit más reciente).
• Postgres implementa aislamiento con MVCC: las lecturas no bloquean escrituras ni viceversa. REPEATABLE READ = un snapshot para toda la transacción.
• El REPEATABLE READ de Postgres es más fuerte que el del estándar: también previene phantoms (por eso los * en la tabla). El de MySQL/InnoDB se comporta distinto.
• SERIALIZABLE en Postgres usa SSI (Serializable Snapshot Isolation): no bloquea, pero puede abortar una tx con un serialization failure → tu app debe reintentar. Poner SERIALIZABLE “por si acaso” sin código de reintento es un foot-gun.

Lost update — el bug más común y su fix. Subir el isolation level NO arregla un read-modify-write no atómico; el problema es hacer la aritmética en la app:

-- MAL (lost update en READ COMMITTED): app lee, calcula, escribe
-- SELECT balance FROM accounts WHERE id='A';  -> 100 en Python
-- UPDATE accounts SET balance = 90 WHERE id='A';  -- pisa la escritura concurrente

-- BIEN opción 1: UPDATE atómico basado en set (sin aritmética en la app)
UPDATE accounts SET balance = balance - 10 WHERE id = 'A' AND balance >= 10;

-- BIEN opción 2: pessimistic lock (bloquea la fila hasta el COMMIT)
BEGIN;
SELECT balance FROM accounts WHERE id = 'A' FOR UPDATE;   -- otros esperan aquí
UPDATE accounts SET balance = balance - 10 WHERE id = 'A';
COMMIT;

-- BIEN opción 3: optimistic concurrency con columna version + reintento
UPDATE accounts SET balance = 90, version = version + 1
WHERE id = 'A' AND version = 7;   -- si afectó 0 filas, alguien ganó la carrera → reintentar

Cuál elegir: contención baja → optimistic (sin locks, reintenta raro). Contención alta sobre las mismas filas → pessimistic (FOR UPDATE). Y siempre que se pueda, prefiere el UPDATE set-based atómico sobre traer el valor a la app.

CAP theorem (transacciones distribuidas)

ACID describe una base single-node. Cuando la DB es distribuida (réplicas, sharding), aparece CAP: ante una partición de red (P), solo puedes preservar dos de tres —Consistency, Availability, Partition tolerance—. Como las particiones en un sistema distribuido no son opcionales, la elección real durante una partición es CP (rechazar escrituras antes que divergir) vs AP (seguir sirviendo, posiblemente datos stale).

• La C de CAP ≠ la C de ACID. CAP-Consistency = linealizabilidad (todos los nodos ven el mismo valor al mismo tiempo). ACID-Consistency = respetar constraints. Confundirlas es un clásico de entrevista.
• Es una decisión por operación, no por sistema: un ledger de pagos quiere CP (Postgres primario único); una lista de “vistos recientemente” tolera AP (eventual consistency).
• PACELC extiende CAP: incluso sin partición (Else) hay un trade Latency-vs-Consistency (réplicas sync vs async).

Paginación: cursor vs offset

Paginar un dataset grande tiene dos estrategias, y la diferencia no es sintáctica: es de complejidad asintótica y de corrección bajo escrituras concurrentes.

-- OFFSET-based: "dame la página 3". Escanea y DESCARTA los primeros N. O(offset).
SELECT * FROM orders ORDER BY created_at DESC
LIMIT 20 OFFSET 999980;   -- escanea ~1M filas para devolver 20

-- CURSOR-based (keyset): "los 20 siguientes después de este punto". O(log n) con índice.
SELECT * FROM orders
WHERE (created_at, id) < ('2026-04-18T10:00:00', 'uuid-abc')
ORDER BY created_at DESC, id DESC
LIMIT 20;   -- índice compuesto (created_at, id) → salta directo al punto

Por qué OFFSET 999980 es lento aunque haya índice sobre created_at: el índice ordena, pero la DB igual tiene que contar y descartar las 999980 filas anteriores para llegar a la ventana; no puede saltar directo a la posición N sin recorrerlas.

Por qué offset produce duplicados/huecos: si entre pedir la página 1 y la 2 se inserta una fila al inicio del orden, todo se desplaza: un registro que estaba en la página 1 reaparece en la 2 (duplicado), o uno se salta (hueco). El cursor no sufre esto porque ancla en un valor (created_at, id), no en una posición.

El cursor compuesto (created_at, id) es obligatorio: si dos órdenes comparten created_at, necesitas id para romper el empate de forma determinista y no saltarte filas. El sort key debe ser un orden total.

Regla: dataset < 10k filas y sin escrituras concurrentes → offset (simple, permite saltar a página N). Dataset grande, en crecimiento, o con consumidores externos iterando (export, polling de partners) → cursor. Si el dataset pasa de 100k filas o tiene inserts constantes, offset es un bug esperando.

SQL vs NoSQL — tipos de base de datos

No es “SQL vs NoSQL” como bando; es elegir el modelo de datos según el acceso y las garantías que necesitas.

Cuándo salir de SQL — con criterio senior:

• Escala de escritura horizontal que un primario relacional no aguanta (millones de writes/s de telemetría) → wide-column.
• Schema genuinamente sin forma o que muta cada semana → document (aunque Postgres jsonb cubre mucho de esto sin renunciar a ACID).
• Latencia sub-milisegundo de lecturas simples por clave → key-value en memoria.
• Consultas que son fundamentalmente recorridos de grafo (“amigos de amigos que compraron X”) → graph.

El default sigue siendo relacional. Postgres da ACID por defecto, joins, constraints, jsonb para las partes flexibles, full-text search decente, y escala vertical + réplicas de lectura muy lejos. La mayoría de “necesitamos NoSQL” es prematuro; la atomicidad multi-fila que Postgres te da gratis es un feature, no un obstáculo. Poliglot persistence (varios motores) es válido, pero cada motor extra es costo operacional: justifícalo con un requisito que el relacional no cubra, no con moda.

Ejercicios

Ejercicio 1 — Diseñar un schema normalizado (3NF)

Modela un sistema de cursos online: un student se inscribe (enrollment) en muchos course, y cada curso lo dicta un instructor. Guarda la nota final de cada inscripción. Aplica 3NF, define PKs/FKs, constraints e índices sobre las FK. Justifica una decisión.

CREATE TABLE instructors (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE courses (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title         VARCHAR(255) NOT NULL,
    instructor_id UUID NOT NULL REFERENCES instructors(id) ON DELETE RESTRICT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE students (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

-- Junction table para la relación N:M student <-> course
CREATE TABLE enrollments (
    student_id  UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id   UUID NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
    grade       DECIMAL(4,2) CHECK (grade >= 0 AND grade <= 100),  -- NULL = aún sin nota
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)   -- evita inscripción duplicada
);

-- Postgres no indexa FK automáticamente:
CREATE INDEX idx_courses_instructor ON courses(instructor_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);  -- el prefijo de la PK ya cubre student_id

Ejercicio 2 — Join con agregación

Sobre el schema del Ejercicio 1: lista cada curso con su título, el nombre del instructor y cuántos estudiantes inscritos tiene, incluyendo cursos con cero inscritos, ordenado por cantidad de inscritos descendente.

SELECT
    c.title,
    i.name AS instructor,
    COUNT(e.student_id) AS enrolled_count   -- COUNT sobre la FK: 0 para cursos sin inscritos
FROM courses c
JOIN instructors i ON i.id = c.instructor_id       -- INNER: todo curso tiene instructor (NOT NULL)
LEFT JOIN enrollments e ON e.course_id = c.id      -- LEFT: preserva cursos sin inscripciones
GROUP BY c.id, c.title, i.name
ORDER BY enrolled_count DESC;

Ejercicio 3 — Elegir el índice y leer el EXPLAIN

Esta query es lenta sobre una tabla orders de 5M filas:

SELECT id, total FROM orders
WHERE customer_id = 'C123' AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

El EXPLAIN ANALYZE muestra Seq Scan on orders con Rows Removed by Filter: 4.9M y un Sort costoso. ¿Qué índice creas y por qué? ¿Qué nodo esperas ver después?

CREATE INDEX idx_orders_cust_status_created
    ON orders(customer_id, status, created_at DESC);

Limit (actual rows=50 loops=1)
  -> Index Scan using idx_orders_cust_status_created on orders
        Index Cond: (customer_id = 'C123' AND status = 'pending')
        (sin nodo Sort: el índice ya provee el orden)

Ejercicio 4 — Cazar el race condition y arreglarlo

Este código Python decrementa stock. Con dos requests concurrentes para el último ítem, ambos “venden”. ¿Qué anomalía es y cómo lo arreglas en SQL sin subir el isolation level?

row = db.execute("SELECT qty FROM stock WHERE product_id = %s", [pid]).one()
if row.qty > 0:
    db.execute("UPDATE stock SET qty = %s WHERE product_id = %s", [row.qty - 1, pid])

# UPDATE atómico y condicional: la resta y el check ocurren en la DB, en una sola operación
result = db.execute(
    "UPDATE stock SET qty = qty - 1 "
    "WHERE product_id = %s AND qty > 0 "   # guard: solo decrementa si hay stock
    "RETURNING qty",
    [pid],
)
if result.rowcount == 0:
    raise OutOfStock(pid)   # 0 filas afectadas = otro ganó la carrera / sin stock

Ejercicio 5 — Migrar de offset a cursor

Un endpoint público expone GET /orders?page=N con LIMIT/OFFSET. Funciona hasta que un partner empieza a paginar 500k órdenes y (a) se vuelve lentísimo en páginas profundas y (b) reporta órdenes duplicadas. Diseña la versión cursor y explica cómo migrar sin romper los clientes actuales.

-- Índice que soporta el keyset (orden total: created_at + id como desempate)
CREATE INDEX idx_orders_keyset ON orders(created_at DESC, id DESC);

-- Query cursor: recibe el (created_at, id) de la última fila de la página anterior
SELECT id, total, created_at FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size;

Preguntas tipo entrevista (EN)

Q1 — “A query that took 100ms now takes 10 seconds. Walk me through how you’d diagnose it.”

• ❌ Wrong / trap: “I read the code and add an index.” Why it fails: guessing without measuring — you might add an index the planner won’t even use, slowing writes for nothing. It also ignores that the cause might be stale statistics or data growth, not a missing index.
• ✅ Correct: Run EXPLAIN ANALYZE to get the actual execution plan and look for sequential scans on large tables and expensive sorts.
• ⭐ Optimal (senior): First check whether the data grew — a plan that was fine at 10k rows can need an index at 1M. Then EXPLAIN (ANALYZE, BUFFERS): I read inside-out for the node with the highest actual time. Signals: Seq Scan + high Rows Removed by Filter (missing usable index), estimated rows vs actual rows wildly off (stale stats → VACUUM ANALYZE), Sort Method: external merge Disk (sort spilling → index on the ORDER BY column or more work_mem), nested loops with high loops (N+1 baked into the query). Buffers: read vs hit tells me if I’m hitting disk or cache. The fix is usually one index on a FK or WHERE column, and I validate by diffing EXPLAIN before and after — I don’t ship a guess.

Q2 — “Explain isolation levels and give a real bug caused by choosing the wrong one.”

• ❌ Wrong / trap: “Just use SERIALIZABLE everywhere to be safe.” Why it fails: SERIALIZABLE maximizes correctness but under MVCC (Postgres SSI) it aborts transactions with serialization failures your app must catch and retry — blanket use without retry logic is a correctness and throughput foot-gun.
• ✅ Correct: The order is READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE, each removing an anomaly: dirty read, non-repeatable read, phantom read. Pick per transaction based on the invariant at stake.
• ⭐ Optimal (senior): Concrete bug — a lost update on READ COMMITTED (Postgres’ default): two requests read balance=100, each computes 100-10 in app code, both write 90, one debit vanishes. The trap is thinking a higher isolation level “reads fresher data” and fixes it — it doesn’t, because the read-modify-write is non-atomic. Fixes: an atomic set-based UPDATE ... SET balance = balance - 10 WHERE id=? AND balance >= 10, or SELECT ... FOR UPDATE (pessimistic), or a version column with retry (optimistic). Note Postgres’ REPEATABLE READ is snapshot isolation and blocks phantoms the SQL standard allows, and its level names don’t behave identically to MySQL’s — I never assume portability of isolation semantics.

Q3 — “ACID Consistency and CAP Consistency — same thing?”

• ❌ Wrong / trap: “Yes, consistency means the data is correct.” Why it fails: conflates two unrelated properties; interviewers use this to catch buzzword knowledge.
• ✅ Correct: No. ACID’s C means each transaction moves the DB from one valid state to another respecting constraints/FKs/checks. CAP’s C means linearizability — every node sees the same value at the same time in a distributed system.
• ⭐ Optimal (senior): They live at different layers. ACID-C is about integrity rules on a single logical database; it’s really enforced by A, I, D plus your constraints. CAP-C is a distributed-systems statement about replica agreement during a network partition. CAP forces a per-partition choice between consistency and availability (you’re really picking CP or AP, since P isn’t optional), and PACELC adds that even without a partition there’s a latency-vs-consistency trade. So “is the system consistent?” is meaningless until you say which consistency and at what layer — and it’s a per-operation decision: the payment path leans CP, the “recently viewed” list is fine AP.

Q4 — “When would you denormalize, and how do you keep it from corrupting data?”

• ❌ Wrong / trap: “Denormalize for performance — copy the fields you read together so you avoid joins.” Why it fails: denormalizing by default trades a measured problem you don’t have yet for guaranteed update anomalies; every duplicated field is now a consistency bug waiting to happen.
• ✅ Correct: Denormalize when reads dominate and a measured join is the bottleneck — reporting/dashboards, read-heavy aggregates — and accept you must keep the copies in sync on writes.
• ⭐ Optimal (senior): I start normalized (3NF) and denormalize only against evidence — a slow query I’ve profiled — because the cost is real: when the source changes I must update every copy or data diverges. I prefer forms the engine keeps honest: generated/computed columns, materialized views (refreshed on a schedule) for reporting, or intentional snapshots that aren’t actually redundant — copying unit_price onto an order line is historical truth, not duplication, since the product’s current price is a different fact. If I must hand-maintain a denormalized field, I centralize the write (a trigger or a single service method) so it can’t drift, and I ask up front: how many copies exist and what happens when I forget one?

Q5 — “Offset vs cursor pagination — which and why?”

• ❌ Wrong / trap: “Offset is simpler and fine; just add LIMIT/OFFSET.” Why it fails: offset is O(offset) — the DB scans and discards every prior row even with an index on the sort column, so deep pages get linearly slower — and it produces duplicates/gaps under concurrent inserts because it anchors on a position, not a value.
• ✅ Correct: Offset for small, stable datasets where users must jump to page N; cursor (keyset) for large or continuously-growing datasets, or external consumers iterating, where you need stable results and constant per-page cost.
• ⭐ Optimal (senior): Cursor uses WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT n backed by a composite index — O(log n) whether you’re on page 1 or 10,000, and stable under inserts because it anchors on a value. The compound key is mandatory: if created_at ties, you need id as a total-order tiebreak or you’ll skip or repeat rows. The trade-off is you lose “jump to page N” and the cursor is opaque. Migrating a public API: run both in parallel behind a version-prefixed cursor, deprecate page, measure adoption, then retire — never repurpose an existing param.

Q6 — “When do you reach for NoSQL over a relational database?”

• ❌ Wrong / trap: “NoSQL scales and is schemaless, so use it when you need to scale or move fast.” Why it fails: treats NoSQL as one thing and ‘scale’ as a reason without a workload; most ‘we need NoSQL’ is premature and throws away ACID and joins you’ll miss.
• ✅ Correct: Match the store to the access pattern: document for self-contained variable-schema aggregates, key-value for sub-ms lookups (cache/sessions), wide-column for write-heavy horizontal scale (telemetry), graph for multi-hop relationship traversals, search for full-text/relevance.
• ⭐ Optimal (senior): My default is relational — Postgres gives ACID, joins, constraints, jsonb for the genuinely unstructured parts, decent full-text, and scales vertically plus read replicas a long way. I leave it only for a requirement it can’t meet: horizontal write scale a single primary can’t sustain (→ wide-column), sub-millisecond key lookups at huge throughput (→ in-memory KV), or queries that are fundamentally graph traversals. Polyglot persistence is legitimate, but each extra engine is operational cost and a consistency boundary, so I justify it with a concrete workload, not a trend — and I remember multi-row atomicity that Postgres gives for free is a feature I’m giving up.

Referencias

• PostgreSQL — Transaction Isolation
• PostgreSQL — Indexes
• PostgreSQL — Using EXPLAIN
• PostgreSQL — INSERT … ON CONFLICT (upsert)
• PostgreSQL — Explicit Locking (FOR UPDATE)
• PostgreSQL — Window Functions Tutorial
• PostgreSQL — CREATE VIEW / Materialized Views
• Martin Kleppmann, Designing Data-Intensive Applications, cap. 7 (Transactions) y cap. 9 (Consistency & Consensus).
• Use The Index, Luke — SQL indexing and pagination