Diseño avanzado de bases de datos

📐 Fundamento

Particionamiento declarativo en PostgreSQL (v10+):

-- Tabla padre (no contiene datos directamente)
CREATE TABLE pedidos (
    id          BIGSERIAL,
    fecha       DATE NOT NULL,
    mesa_id     INTEGER,
    total       NUMERIC(10,2),
    estado      TEXT
) PARTITION BY RANGE (fecha);

-- Crear particiones por mes
CREATE TABLE pedidos_2026_01 PARTITION OF pedidos
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE pedidos_2026_02 PARTITION OF pedidos
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- ... etc.

-- Insertar va automáticamente a la partición correcta
INSERT INTO pedidos (fecha, mesa_id, total, estado)
VALUES ('2026-01-15', 3, 8.50, 'CERRADO');
-- → va a pedidos_2026_01

-- Consultar es transparente
SELECT * FROM pedidos WHERE fecha = '2026-01-15';
-- → PostgreSQL consulta solo pedidos_2026_01 (partition pruning)

Tipos de particionamiento:

Índices en tablas particionadas:

-- Crear índice en todas las particiones a la vez
CREATE INDEX ON pedidos (fecha, mesa_id);
-- PostgreSQL crea automáticamente el índice en cada partición

-- Ver particiones existentes
SELECT tablename FROM pg_tables 
WHERE tablename LIKE 'pedidos_%';

Archivado y purga de datos:

-- Eliminar un mes de datos de forma INSTANTÁNEA (vs DELETE que tarda horas)
DROP TABLE pedidos_2024_01;  -- millones de filas eliminadas en segundos

-- O mover a almacenamiento de archivo (tablespace más barato)
ALTER TABLE pedidos_2024_01 SET TABLESPACE archivo_frio;

Partition pruning: El planificador de PostgreSQL automáticamente omite las particiones que no pueden contener filas que satisfagan la condición WHERE. Una query WHERE fecha = '2026-01-15' solo escanea pedidos_2026_01, no las otras 23 particiones del año.

📐 Fundamento

-- Crear vista materializada: reporte de ventas por platillo
CREATE MATERIALIZED VIEW ventas_por_platillo AS
SELECT 
    p.id,
    p.nombre,
    p.categoria,
    COUNT(ip.id)                          AS total_pedidos,
    SUM(ip.cantidad)                      AS unidades_vendidas,
    SUM(ip.precio_unit * ip.cantidad)     AS ingresos_total,
    AVG(ip.precio_unit * ip.cantidad)     AS ticket_promedio
FROM items_pedido ip
JOIN platillos p ON ip.platillo_id = p.id
JOIN pedidos ped ON ip.pedido_id = ped.id
WHERE ped.estado = 'CERRADO'
GROUP BY p.id, p.nombre, p.categoria
ORDER BY ingresos_total DESC;

-- Crear índice sobre la vista materializada
CREATE INDEX ON ventas_por_platillo (categoria);

-- Consultar (instantáneo, sin re-ejecutar el SELECT pesado)
SELECT * FROM ventas_por_platillo WHERE categoria = 'pupusa';

-- Refrescar los datos (esto sí ejecuta el SELECT completo)
REFRESH MATERIALIZED VIEW ventas_por_platillo;

-- Refrescar sin bloquear lecturas (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY ventas_por_platillo;
-- (requiere un UNIQUE INDEX en la vista)

Estrategias de refresco:

-- Opción 1: Refresco manual desde la aplicación
-- Llamar REFRESH al final de cada jornada (ej: a las 23:00)

-- Opción 2: pg_cron (extensión para jobs programados)
SELECT cron.schedule('refresco-ventas', '0 23 * * *', 
  'REFRESH MATERIALIZED VIEW CONCURRENTLY ventas_por_platillo');

-- Opción 3: Trigger que invalida y marca como "dirty" la vista
-- (requiere lógica adicional en la aplicación)

Vista vs Vista Materializada vs Tabla desnormalizada:

📐 Fundamento

Función / Procedimiento almacenado en PL/pgSQL:

-- Función que registra un pedido con verificación de inventario
CREATE OR REPLACE FUNCTION registrar_pedido(
    p_mesa_id   INTEGER,
    p_mozo_id   INTEGER,
    p_items     JSONB  -- [{platillo_id: 1, cantidad: 2}, ...]
) RETURNS INTEGER AS $$
DECLARE
    v_pedido_id INTEGER;
    v_item      JSONB;
    v_stock     INTEGER;
BEGIN
    -- Crear el pedido
    INSERT INTO pedidos (mesa_id, mozo_id, estado, fecha)
    VALUES (p_mesa_id, p_mozo_id, 'ABIERTO', NOW())
    RETURNING id INTO v_pedido_id;
    
    -- Procesar cada ítem
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
        -- Verificar stock
        SELECT cantidad INTO v_stock
        FROM inventario
        WHERE platillo_id = (v_item->>'platillo_id')::INTEGER
        FOR UPDATE;  -- lock para evitar race condition
        
        IF v_stock < (v_item->>'cantidad')::INTEGER THEN
            RAISE EXCEPTION 'Stock insuficiente para platillo %', 
                            v_item->>'platillo_id';
        END IF;
        
        -- Insertar ítem
        INSERT INTO items_pedido (pedido_id, platillo_id, cantidad, precio_unit)
        SELECT v_pedido_id, 
               (v_item->>'platillo_id')::INTEGER, 
               (v_item->>'cantidad')::INTEGER,
               precio
        FROM platillos WHERE id = (v_item->>'platillo_id')::INTEGER;
        
        -- Descontar inventario
        UPDATE inventario 
        SET cantidad = cantidad - (v_item->>'cantidad')::INTEGER
        WHERE platillo_id = (v_item->>'platillo_id')::INTEGER;
    END LOOP;
    
    RETURN v_pedido_id;
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- Llamar la función
SELECT registrar_pedido(3, 7, '[{"platillo_id": 1, "cantidad": 2}]'::jsonb);

Trigger — auditoría automática:

-- Tabla de auditoría
CREATE TABLE auditoria_pedidos (
    id          BIGSERIAL PRIMARY KEY,
    pedido_id   INTEGER,
    campo       TEXT,
    valor_antes TEXT,
    valor_despues TEXT,
    usuario     TEXT,
    fecha       TIMESTAMP DEFAULT NOW()
);

-- Función del trigger
CREATE OR REPLACE FUNCTION auditar_cambio_pedido()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.estado IS DISTINCT FROM NEW.estado THEN
        INSERT INTO auditoria_pedidos 
            (pedido_id, campo, valor_antes, valor_despues, usuario)
        VALUES 
            (NEW.id, 'estado', OLD.estado, NEW.estado, current_user);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Asociar el trigger a la tabla
CREATE TRIGGER tg_auditar_pedido
AFTER UPDATE ON pedidos
FOR EACH ROW
EXECUTE FUNCTION auditar_cambio_pedido();

-- Ahora cualquier UPDATE en pedidos queda registrado
UPDATE pedidos SET estado = 'CERRADO' WHERE id = 123;
-- → auditoria_pedidos recibe automáticamente una fila

📐 Fundamento

-- Preparar la tabla para búsqueda full-text
ALTER TABLE platillos ADD COLUMN ts_busqueda TSVECTOR;

-- Generar el vector de búsqueda (en español)
UPDATE platillos 
SET ts_busqueda = to_tsvector('spanish', nombre || ' ' || COALESCE(descripcion, ''));

-- Crear índice GIN para búsqueda rápida
CREATE INDEX idx_platillos_fts ON platillos USING GIN (ts_busqueda);

-- Mantener actualizado con trigger
CREATE OR REPLACE FUNCTION actualizar_ts_platillo()
RETURNS TRIGGER AS $$
BEGIN
    NEW.ts_busqueda := to_tsvector('spanish', 
        NEW.nombre || ' ' || COALESCE(NEW.descripcion, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_ts_platillo
BEFORE INSERT OR UPDATE ON platillos
FOR EACH ROW EXECUTE FUNCTION actualizar_ts_platillo();

-- Buscar
SELECT nombre, precio,
       ts_rank(ts_busqueda, query) AS relevancia
FROM platillos, 
     to_tsquery('spanish', 'pupusa & queso') AS query
WHERE ts_busqueda @@ query
ORDER BY relevancia DESC;

-- Busca: documentos que contienen "pupusa" Y "queso"
-- Maneja automáticamente: "pupusas", "quesos", acentos

Operadores en to_tsquery:

to_tsquery('pupusa & queso')    -- Y
to_tsquery('pupusa | loroco')   -- O
to_tsquery('!picante')          -- NOT
to_tsquery('pupusa:*')          -- prefijo (pupusas, pupusería...)

📐 Fundamento

El principio de mínimo privilegio: cada usuario y aplicación debe tener solo los permisos necesarios para su función.

-- Crear roles
CREATE ROLE app_lectura;
CREATE ROLE app_escritura;
CREATE ROLE app_admin;

-- Asignar permisos
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_lectura;

GRANT SELECT, INSERT, UPDATE ON pedidos, items_pedido, facturas 
TO app_escritura;

GRANT app_lectura TO app_escritura;  -- escritura hereda lectura

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;

-- Crear usuarios y asignar roles
CREATE USER mozo_app WITH PASSWORD 'password_seguro';
GRANT app_escritura TO mozo_app;

CREATE USER reporte_app WITH PASSWORD 'otro_password';
GRANT app_lectura TO reporte_app;

-- Row Level Security (RLS) — cada local solo ve sus propios datos
ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

CREATE POLICY local_policy ON pedidos
    USING (local_id = current_setting('app.local_id')::INTEGER);

-- Cuando la app conecta:
SET app.local_id = '2';
-- Ahora SELECT * FROM pedidos solo devuelve pedidos del local 2

Mejores prácticas de seguridad en BD:

1. Nunca conectar como superusuario desde la aplicación.
2. Usar SSL/TLS para todas las conexiones a la BD en producción.
3. Rotar contraseñas periódicamente o usar autenticación certificada.
4. Auditar accesos — registrar quién accede a qué datos sensibles.
5. Nunca concatenar SQL — siempre usar prepared statements/parámetros.
6. Cifrar datos sensibles (números de tarjeta, DUIs) con pgcrypto.

-- Cifrar datos sensibles
CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO clientes (nombre, dui_cifrado)
VALUES ('Ana', pgp_sym_encrypt('01234567-8', 'clave_secreta'));

-- Descifrar (solo si se tiene la clave)
SELECT nombre, pgp_sym_decrypt(dui_cifrado::bytea, 'clave_secreta')
FROM clientes WHERE id = 1;