🐘 PostgreSQL Desde Cero

PostgreSQL es un sistema de gestión de bases de datos relacional y de objetos, conocido por su estabilidad, robustez y características avanzadas.

Historia:

PostgreSQL se originó en el proyecto POSTGRES en la Universidad de California, Berkeley en 1986. Fue creado por Michael Stonebraker como sucesor de Ingres.

Características principales:

• ✅ Cumplimiento ACID completo
• ✅ Transacciones y savepoints
• ✅ Triggers y stored procedures
• ✅ Vistas y vistas materializadas
• ✅ Soporte para JSON y JSONB
• ✅ Full-text search
• ✅ Replicación y clustering

En Windows:

1. Descarga PostgreSQL desde postgresql.org
2. Ejecuta el instalador
3. Selecciona componentes (Server, pgAdmin, Command Line Tools)
4. Configura la contraseña del usuario postgres
5. Selecciona el puerto (default: 5432)
6. Finaliza la instalación

En Linux (Ubuntu/Debian):

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
sudo systemctl status postgresql

En macOS:

brew install postgresql
brew services start postgresql

Verificar instalación:

psql --version
sudo -u postgres psql

Crear Base de Datos:

CREATE DATABASE mi_base;
\c mi_base

Crear Tablas:

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    edad INTEGER CHECK (edad >= 18),
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Tipos de Datos Comunes:

• INTEGER, BIGINT, SMALLINT
• VARCHAR(n), TEXT
• BOOLEAN
• DATE, TIMESTAMP, INTERVAL
• NUMERIC, REAL, DOUBLE PRECISION
• JSON, JSONB
• UUID, ARRAY

Restricciones:

• PRIMARY KEY - Clave primaria
• FOREIGN KEY - Clave foránea
• UNIQUE - Valores únicos
• NOT NULL - No nulo
• CHECK - Validación personalizada

Consultas SELECT:

-- Seleccionar todos
SELECT * FROM usuarios;

-- Seleccionar columnas específicas
SELECT nombre, email FROM usuarios;

-- Con WHERE
SELECT * FROM usuarios WHERE edad > 25;

-- Con ORDER BY
SELECT * FROM usuarios ORDER BY nombre ASC;

JOINs:

-- INNER JOIN
SELECT u.nombre, p.titulo
FROM usuarios u
INNER JOIN posts p ON u.id = p.usuario_id;

-- LEFT JOIN
SELECT u.nombre, p.titulo
FROM usuarios u
LEFT JOIN posts p ON u.id = p.usuario_id;

Funciones de Agregación:

-- COUNT
SELECT COUNT(*) FROM usuarios;

-- AVG
SELECT AVG(edad) FROM usuarios;

-- SUM, MAX, MIN
SELECT SUM(ventas), MAX(ventas), MIN(ventas) FROM ventas;

Subconsultas:

SELECT nombre FROM usuarios
WHERE id IN (SELECT usuario_id FROM posts WHERE fecha > '2024-01-01');

Indexación:

-- Crear índice
CREATE INDEX idx_email ON usuarios(email);

-- Índice compuesto
CREATE INDEX idx_nombre_apellido ON usuarios(nombre, apellido);

-- Índice único
CREATE UNIQUE INDEX idx_email_unique ON usuarios(email);

Optimización de Consultas:

• ✅ Usa EXPLAIN ANALYZE para ver el plan de ejecución
• ✅ Indexa columnas usadas en WHERE y JOIN
• ✅ Evita SELECT * - selecciona solo columnas necesarias
• ✅ Usa LIMIT para paginar resultados grandes
• ✅ Evita funciones en columnas indexadas en WHERE

Diseño de Esquemas:

• ✅ Normaliza hasta 3FN o BCNF
• ✅ Usa claves foráneas para integridad referencial
• ✅ Documenta tu esquema con comentarios
• ✅ Usa nombres descriptivos para tablas y columnas

Patrón 1: E-commerce

-- Tablas relacionadas
CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(200),
    precio DECIMAL(10,2),
    stock INTEGER
);

CREATE TABLE ordenes (
    id SERIAL PRIMARY KEY,
    usuario_id INTEGER REFERENCES usuarios(id),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10,2)
);

CREATE TABLE ordenes_detalle (
    orden_id INTEGER REFERENCES ordenes(id),
    producto_id INTEGER REFERENCES productos(id),
    cantidad INTEGER,
    precio_unitario DECIMAL(10,2),
    PRIMARY KEY (orden_id, producto_id)
);

Patrón 2: Blog con Tags

-- Relación muchos a muchos
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    titulo VARCHAR(200),
    contenido TEXT,
    usuario_id INTEGER REFERENCES usuarios(id)
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(50) UNIQUE
);

CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

Empresas que usan PostgreSQL:

• 🏢 Instagram - Maneja billones de consultas diarias
• 🎵 Spotify - Almacenamiento de metadatos de música
• 🍎 Apple - Servicios de iCloud
• 💼 Reddit - Sistema de votación y comentarios

Desarrolla una base de datos completa para un sistema de gestión de biblioteca:

Requisitos:

1. 📚 Tabla de libros (isbn, titulo, autor, editorial, año)
2. 👥 Tabla de usuarios (id, nombre, email, telefono)
3. 📋 Tabla de préstamos (id, libro_id, usuario_id, fecha_prestamo, fecha_devolucion)
4. 🏷️ Tabla de categorías (id, nombre, descripcion)
5. 📝 Tabla de reservas (id, libro_id, usuario_id, fecha_reserva, estado)

Consultas a implementar:

• ✅ Libros prestados actualmente
• ✅ Usuarios con préstamos vencidos
• ✅ Libros más prestados
• ✅ Historial de préstamos por usuario
• ✅ Disponibilidad de libros

pg_dump:

pg_dump -U postgres -d mi_base -F c -f backup.dump

# Restaurar
pg_restore -U postgres -d mi_base backup.dump

# Backup en texto plano
pg_dump -U postgres -d mi_base > backup.sql

pgAdmin 4:

• Herramienta gráfica oficial para PostgreSQL
• Diseño de bases de datos visual
• Ejecución de queries
• Gestión de backups
• Monitoreo de rendimiento

Herramientas Adicionales:

psql -U postgres -d mi_base -c "\dt"    # Listar tablas
psql -U postgres -d mi_base -c "\d tabla"  # Estructura
psql -U postgres -d mi_base < backup.sql    # Restaurar

Transacciones:

BEGIN;

UPDATE cuentas SET saldo = saldo - 500 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 500 WHERE id = 2;

-- Verificar
SELECT * FROM cuentas WHERE id IN (1, 2);

-- Confirmar o revertir
COMMIT;
-- o ROLLBACK;

Niveles de Aislamiento:

-- Ver nivel actual
SHOW transaction_isolation;

-- Establecer nivel
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- En una transacción
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SAVEPOINT:

BEGIN;
INSERT INTO log VALUES ('evento1');
SAVEPOINT sp1;
INSERT INTO log VALUES ('evento2');
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Control de Concurrencia (MVCC):

PostgreSQL usa MVCC que permite lecturas concurrentes sin bloqueos.

Subconsultas:

-- Escalar
SELECT nombre, salario, 
    (SELECT AVG(salario) FROM empleados) AS promedio
FROM empleados;

-- Tabla
SELECT dept, promedio FROM (
    SELECT departamento_id AS dept, AVG(salario) AS promedio
    FROM empleados GROUP BY departamento_id
) AS stats WHERE promedio > 50000;

CTE (Common Table Expression):

WITH stats AS (
    SELECT departamento_id, AVG(salario) AS promedio
    FROM empleados GROUP BY departamento_id
)
SELECT e.nombre, e.salario, s.promedio
FROM empleados e
JOIN stats s ON e.departamento_id = s.departamento_id
WHERE e.salario > s.promedio;

UNION, INTERSECT, EXCEPT:

-- UNION (sin duplicados)
SELECT nombre FROM empleados_2023
UNION
SELECT nombre FROM empleados_2024;

-- UNION ALL (con duplicados)
SELECT nombre FROM empleados_2023
UNION ALL
SELECT nombre FROM empleados_2024;

-- INTERSECT (comunes)
SELECT email FROM clientes
INTERSECT
SELECT email FROM Newsletter;

Procedimientos (PL/pgSQL):

CREATE OR REPLACE PROCEDURE transferencia(
    origen INT, destino INT, monto DECIMAL(10,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE cuentas SET saldo = saldo - monto WHERE id = origen;
    UPDATE cuentas SET saldo = saldo + monto WHERE id = destino;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END $$;

CALL transferencia(1, 2, 100);

Funciones:

CREATE OR REPLACE FUNCTION calcular_iva(precio DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    RETURN precio * 0.16;
END;
$$ LANGUAGE plpgsql;

SELECT nombre, precio, calcular_iva(precio) AS iva FROM productos;

Triggers:

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (tabla, accion, usuario, fecha)
    VALUES (TG_TABLE_NAME, TG_OP, current_user, now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_empleados_audit
AFTER UPDATE ON empleados
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Replicación Física:

-- En postgresql.conf (maestro)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

-- pg_hba.conf
host replication all 192.168.1.0/24 md5

-- En esclavo
pg_basebackup -h master_ip -D /var/lib/postgresql/data -U replication -P

Logical Replication:

-- Crear publicación
CREATE PUBLICATION mi_pub FOR TABLE productos;

-- Crear suscripción
CREATE SUBSCRIPTION mi_sub
CONNECTION 'host=master port=5432 dbname=mi_db'
PUBLICATION mi_pub;

Patronización:

-- pgpool-II o PgBouncer para pooling
-- Patroni para alta disponibilidad
-- Amazon RDS / Aurora para cloud

Partitioning:

CREATE TABLE ventas (
    id SERIAL, fecha DATE, monto DECIMAL
) PARTITION BY RANGE (fecha);

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