Lección 1 de 313 min
Bases de datos relacionales: PostgreSQL y MySQL
Tablas, claves, transacciones ACID e índices, y en qué se diferencian las dos bases de datos libres más usadas.
Una tienda online guarda clientes, productos y pedidos. Cada pedido pertenece a un cliente y contiene varios productos; un producto aparece en muchos pedidos. Si lo guardaras todo en archivos sueltos, ¿cómo evitarías un pedido de un cliente que no existe, o que dos compras simultáneas vendan la última unidad dos veces?
Las bases de datos relacionales resuelven exactamente eso, y llevan haciéndolo desde los años 70. En esta lección ves su modelo, las transacciones que protegen los datos, los índices que hacen rápidas las búsquedas y las dos bases de datos libres más usadas: PostgreSQL y MySQL. El lenguaje con el que se les habla, SQL, lo viste en el módulo de lenguajes.
El modelo relacional
Tablas, filas y claves
Los datos en tablas, unidas entre sí por claves que la base de datos vigila.
- Propuesto por:
- Edgar F. Codd
- Desde:
- 1970
- Lenguaje:
- SQL
En el modelo relacional, que propuso Edgar F. Codd en IBM en 1970, los datos viven en tablas. Cada fila es un registro (un cliente) y cada columna, un atributo con un tipo fijo (su email, de tipo texto). La clave primaria identifica cada fila sin ambigüedad, normalmente con una columna id.
Una clave foránea es una columna que apunta a la clave primaria de otra tabla: pedidos.cliente_id apunta a clientes.id. Así se expresa una relación de uno a muchos (un cliente, muchos pedidos), y la base de datos garantiza la integridad referencial: rechaza un pedido de un cliente inexistente. Las relaciones de muchos a muchos, como pedidos y productos, se resuelven con una tabla intermedia, por ejemplo lineas_pedido.
Úsalo cuando
Cuando los datos tienen una estructura clara y relaciones entre sí, y su coherencia importa: dinero, inventario, reservas, usuarios y permisos.
Ten en cuenta
La estructura se define de antemano, así que cambiarla exige una migración, que verás en el módulo de ORM y acceso a datos. Eso no es un defecto: el esquema es lo que impide que entren datos incoherentes.
CREATE TABLE clientes (
id BIGINT PRIMARY KEY,
nombre TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE pedidos (
id BIGINT PRIMARY KEY,
cliente_id BIGINT NOT NULL REFERENCES clientes (id),
total NUMERIC(10, 2) NOT NULL,
creado TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Los pedidos de Ana, uniendo las dos tablas por la clave foránea
SELECT p.id, p.total
FROM pedidos p JOIN clientes c ON c.id = p.cliente_id
WHERE c.email = 'ana@ejemplo.com';Transacciones ACID
Varias operaciones que se aplican todas juntas o ninguna.
- Comandos:
- BEGIN, COMMIT, ROLLBACK
Una transacción agrupa operaciones que deben ocurrir a la vez. En una transferencia, restar 100 a una cuenta y sumárselos a otra: si el servidor se cae entre las dos, no puede quedar el dinero restado y sin sumar. Empieza con BEGIN y termina con COMMIT, que confirma, o ROLLBACK, que deshace.
Sus garantías se resumen en ACID. Atomicidad: todo o nada. Consistencia: al terminar se cumplen todas las reglas, como las claves foráneas o un CHECK (saldo >= 0). Aislamiento: las transacciones simultáneas no ven el trabajo a medias de las demás. Durabilidad: lo confirmado sobrevive a un apagón, porque antes se ha escrito en un registro en disco.
El aislamiento tiene niveles, de más permisivo a más estricto: READ COMMITTED, REPEATABLE READ y SERIALIZABLE. Más aislamiento evita más anomalías, pero obliga a esperar o a reintentar más a menudo. PostgreSQL usa por defecto READ COMMITTED, y MySQL con InnoDB, REPEATABLE READ.
Úsalo cuando
Siempre que varias escrituras dependan unas de otras: cobrar y crear el pedido, mover stock, reservar un asiento.
Ten en cuenta
Las transacciones largas mantienen bloqueos y frenan a las demás: haz el trabajo lento, como llamar a una API externa, fuera de ellas. Y ACID vale dentro de una base de datos; entre varios servicios hacen falta otros patrones, que verás en el módulo de arquitectura.
BEGIN;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
-- Si algo falla antes de aquí, ROLLBACK deshace los dos cambios
COMMIT;Índices: encontrar sin leerlo todo
Sin ayuda, buscar un email entre diez millones de usuarios obliga a la base de datos a mirar las diez millones de filas. Un índice es lo mismo que el índice de un libro: una lista ordenada que te dice en qué página está cada cosa.
Índices B-tree
Una estructura ordenada aparte que lleva directamente a las filas que buscas.
- Tipo más común:
- B-tree
- Coste:
- Escrituras más lentas y más espacio
Sin índice, una consulta como WHERE email = ? hace una lectura completa (full scan): recorre toda la tabla. Un índice guarda los valores de una columna ordenados, junto a la ubicación de cada fila. El tipo más común es el B-tree, un árbol equilibrado en el que cada nodo contiene cientos de claves: por eso, incluso con millones de filas, bastan tres o cuatro niveles para llegar a la buena.
Un B-tree sirve para igualdades, rangos (BETWEEN, <, >), ORDER BY y búsquedas por prefijo (LIKE 'ana%'). La clave primaria y las columnas UNIQUE tienen índice automáticamente. Un índice compuesto de (cliente_id, creado) sirve para filtrar por cliente y ordenar por fecha, pero no para filtrar solo por fecha: el orden de las columnas importa.
El precio está en las escrituras: cada INSERT, UPDATE o DELETE actualiza también todos los índices de la tabla, y cada índice ocupa espacio en disco y en memoria.
Úsalo cuando
En las columnas por las que filtras, unes o ordenas a menudo y que distinguen bien entre filas, como un email o un cliente_id.
Ten en cuenta
No ayudan en tablas pequeñas ni en columnas con pocos valores distintos, como un booleano activo. Un índice sobre email no sirve para WHERE lower(email) = … ni para LIKE '%ana'. Y en PostgreSQL las claves foráneas no se indexan solas: si filtras por cliente_id, crea tú el índice.
-- Sin índice: la base de datos lee la tabla entera
SELECT * FROM usuarios WHERE email = 'ana@ejemplo.com';
CREATE INDEX idx_usuarios_email ON usuarios (email);
-- Compuesto: filtra por cliente y ordena por fecha
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, creado);
-- No aprovecha idx_usuarios_email: aplica una función a la columna
SELECT * FROM usuarios WHERE lower(email) = 'ana@ejemplo.com';EXPLAIN
Pregunta a la base de datos cómo va a ejecutar tu consulta, en vez de suponerlo.
- PostgreSQL:
- EXPLAIN ANALYZE
- MySQL:
- EXPLAIN, EXPLAIN ANALYZE
Antes de ejecutar una consulta, el planificador decide cómo hacerlo: leer la tabla entera, usar un índice, en qué orden unir las tablas. EXPLAIN te enseña ese plan. En PostgreSQL verás Seq Scan para una lectura completa e Index Scan cuando usa un índice; en MySQL, type: ALL indica que lee toda la tabla.
EXPLAIN ANALYZE va más allá: ejecuta la consulta y muestra los tiempos y filas reales de cada paso. El planificador decide según estadísticas de la tabla, así que en una tabla pequeña puede preferir leerla entera aunque haya índice, porque le cuesta menos.
Úsalo cuando
Cada vez que una consulta es lenta o acabas de crear un índice y quieres comprobar que se usa.
Ten en cuenta
EXPLAIN ANALYZE ejecuta de verdad la consulta: con un UPDATE o un DELETE, modifica los datos. Envuélvelo en una transacción y termina con ROLLBACK.
EXPLAIN ANALYZE
SELECT * FROM usuarios WHERE email = 'ana@ejemplo.com';
-- Sin índice
Seq Scan on usuarios (… rows=1 …)
Filter: (email = 'ana@ejemplo.com'::text)
Rows Removed by Filter: 999999
-- Con idx_usuarios_email
Index Scan using idx_usuarios_email on usuarios (… rows=1 …)
Index Cond: (email = 'ana@ejemplo.com'::text)Pruébalo
En el simulador, mueve la tabla de mil a diez millones de filas con y sin índice. Sin índice, el trabajo crece al mismo ritmo que la tabla; con índice, la altura del árbol apenas sube un nivel. Fíjate también en lo que cuesta cada inserción.
Interactivo
Índice frente a lectura completa
Cambia el tamaño de la tabla y activa o quita el índice para ver cuánto trabajo hace la base de datos al buscar un email, y cuánto le cuesta cada inserción.
SELECT * FROM usuarios WHERE email = 'ana@ejemplo.com';Plan elegido: Seq Scan on usuarios
Lecturas para encontrar a Ana (escala logarítmica: cada marca es ×10)
- Sin índice1 000 000 filas
- Con índice3 nodos + 1 fila
Camino por el B-tree (1 000 000 filas, unas 200 claves por nodo)
- Raíz
- Nivel 2
- Hoja
- Fila
Un árbol binario necesitaría unos 20 niveles (log₂ de 1 000 000). Como cada nodo del B-tree tiene cientos de hijos, bastan 3.
Y al insertar: INSERT INTO usuarios (email, nombre) VALUES (…);
- ✓ Escribe la fila nueva en la tabla.
- ✓ Añade su id al índice de la clave primaria, que existe siempre.
- – Baja 3 niveles de
idx_usuarios_emaily coloca el email en orden; si la página está llena, la divide en dos.
2 estructuras que actualizar en cada inserción.
Sin índice, la base de datos lee las 1 000 000 filas. Aunque Ana esté en la primera, no sabe si otra fila tiene el mismo email, así que sigue hasta el final.
Simplificación: cuenta cada fila o nodo como una lectura. En realidad se leen páginas de varios KB con muchas filas cada una, pero la proporción se mantiene: lineal sin índice, logarítmica con él.
PostgreSQL y MySQL
Las dos son libres, maduras, relacionales y ACID, y cualquiera de las dos aguanta la inmensa mayoría de proyectos. Sus diferencias están en el carácter: PostgreSQL apuesta por la riqueza y la extensibilidad; MySQL, por la sencillez y por una presencia enorme en la web.
PostgreSQL
La base de datos relacional libre más versátil: tipos ricos y extensiones.
- Licencia:
- PostgreSQL (libre)
- Concurrencia:
- MVCC
- Con este nombre desde:
- 1996
PostgreSQL nació del proyecto POSTGRES de la Universidad de California en Berkeley y lleva ese nombre desde 1996. Es conocida por seguir de cerca el estándar SQL y por su extensibilidad: puedes definir tipos, funciones y operadores propios, y elegir entre varios tipos de índice (B-tree, GIN, GiST, BRIN…).
Sus tipos van mucho más allá de texto y números: JSONB guarda JSON en binario y se indexa para buscar dentro de él, y hay arrays, rangos, UUID, fechas con zona horaria o enumerados. Las extensiones añaden capacidades enteras con un CREATE EXTENSION: PostGIS para datos geográficos, pgvector para búsquedas por similitud con los vectores que usan las aplicaciones de IA.
Gestiona la concurrencia con MVCC (control de concurrencia multiversión): cada transacción ve una instantánea coherente de los datos, así que las lecturas no bloquean a las escrituras ni al revés. Las versiones antiguas de las filas las limpia después un proceso llamado VACUUM.
Úsalo cuando
Como opción por defecto para casi cualquier proyecto nuevo, y especialmente con consultas complejas, datos semiestructurados, geodatos o necesidades especiales que cubre una extensión.
Ten en cuenta
Cada conexión es un proceso del servidor: con cientos de conexiones simultáneas, por ejemplo desde funciones serverless, necesitas un pooler como PgBouncer. Y en tablas con muchísimas actualizaciones, vigila que VACUUM dé abasto.
CREATE TABLE productos (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nombre TEXT NOT NULL,
atributos JSONB NOT NULL DEFAULT '{}',
etiquetas TEXT[]
);
-- Índice GIN para buscar dentro del JSON
CREATE INDEX idx_productos_atributos ON productos USING GIN (atributos);
-- Portátiles con 16 GB de RAM
SELECT nombre FROM productos
WHERE atributos @> '{"tipo": "portatil", "ram_gb": 16}';
-- Una línea y tienes tipos y funciones geográficas
CREATE EXTENSION postgis;MySQL
La relacional de la web: sencilla, rápida en lecturas y en todas partes.
- Propietario:
- Oracle
- Motor por defecto:
- InnoDB
- Fork:
- MariaDB
MySQL apareció en 1995 y es la «M» del clásico stack LAMP (Linux, Apache, MySQL, PHP). Hoy pertenece a Oracle, que la adquirió al comprar Sun Microsystems. WordPress y una enorme cantidad de aplicaciones web la usan, y casi cualquier hosting la ofrece.
Su motor de almacenamiento por defecto, InnoDB, aporta transacciones ACID, bloqueos por fila, claves foráneas y MVCC. Guarda cada tabla ordenada por su clave primaria, así que elegir bien esa clave influye en el rendimiento. La replicación de un servidor primario a varias réplicas es muy sencilla de montar y muy usada para repartir las lecturas.
En 2009, tras la compra de Sun por parte de Oracle, uno de los creadores originales de MySQL inició MariaDB, un fork pensado para seguir siendo libre. Es muy compatible, pero los dos proyectos se han ido separando con los años: no des por hecho que todo funciona igual en ambos.
Úsalo cuando
Para aplicaciones web con muchas más lecturas que escrituras, en el ecosistema PHP y WordPress, o cuando tu hosting o tu equipo ya la conocen bien.
Ten en cuenta
Usa siempre utf8mb4: el antiguo utf8 de MySQL solo admite hasta 3 bytes por carácter y no puede guardar emojis. Y revisa el modo SQL estricto: con modos permisivos, MySQL ha llegado a recortar en silencio valores que no cabían en vez de dar un error.
CREATE TABLE usuarios (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
nombre VARCHAR(100) NOT NULL,
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- En una réplica: ¿va al día con el primario?
SHOW REPLICA STATUS;Interactivo
PostgreSQL y MySQL cara a cara
Activa o desactiva opciones para compararlas cara a cara.
| Característica | PostgreSQL | MySQL |
|---|---|---|
| Licencia y gobierno | Licencia PostgreSQL, permisiva; comunidad independiente | GPL y edición comercial; propiedad de Oracle |
| Tipos de datos | Muy ricos: JSONB, arrays, rangos, UUID… | Los habituales y JSON |
| Extensibilidad | Extensiones como PostGIS o pgvector | Motores de almacenamiento; menos extensiones |
| Replicación | Integrada, por streaming o lógica | Primario y réplicas, muy sencilla y extendida |
| Encaja con | Consultas complejas, datos variados, geodatos | Webs con muchas lecturas, PHP y WordPress |
Ponlo en práctica
Saber qué es cada cosa es la mitad del trabajo. La otra mitad es reconocer cuándo usarla.
Interactivo
¿Qué usarías?
Lee cada situación y elige la opción que mejor la resuelve. Fallar aquí no cuenta: es para practicar.
Tu app de reparto tiene que encontrar los restaurantes a menos de dos kilómetros del usuario y ordenarlos por distancia.
Para recordar
- Las bases de datos relacionales guardan los datos en tablas unidas por claves, y es la propia base de datos la que garantiza que las relaciones sean coherentes.
- Una transacción ACID se aplica entera o no se aplica: es lo que protege el dinero y el inventario.
- Un índice B-tree convierte una búsqueda entre millones de filas en un puñado de lecturas, a cambio de escrituras algo más lentas y más espacio.
- Usa
EXPLAINpara saber si tu consulta usa un índice, en lugar de suponerlo. - PostgreSQL destaca por sus tipos y extensiones, y MySQL, por su sencillez y su enorme presencia en la web: las dos son una opción sólida por defecto.
Comprueba lo aprendido
Cuestionario
Responde las 5 preguntas para completar la lección.
1.¿Qué impide la clave foránea
pedidos.cliente_id REFERENCES clientes (id)?2.Añades seis índices a una tabla en la que se insertan miles de filas por segundo. ¿Qué es lo más probable?
3.¿Por qué un índice B-tree encuentra una fila entre millones con muy pocas lecturas?
4.Una transacción hace dos
UPDATEy el servidor se apaga justo después del primero, antes delCOMMIT. Al reiniciar, ¿qué encuentras?5.En PostgreSQL, ¿qué consigue MVCC?