🐘 2.8 · PostgreSQL: la base de datos relacional más avanzada del open source

🎯 Objetivo del tema

🗺️ Mapa del tema

En el Tema 2.4 aprendiste MySQL: la base de datos más popular del mundo para aplicaciones web. Hoy conocerás su prima más poderosa: PostgreSQL, la BD relacional open source más avanzada. Es la elección de empresas como Instagram, Spotify, Reddit, Notion y Apple para sus sistemas críticos. La buena noticia: si sabes MySQL, PostgreSQL es muy similar. Las diferencias son avanzadas, no básicas.

2.8.1 PostgreSQL vs MySQL: las 7 diferencias clave

PostgreSQL y MySQL son las dos bases de datos relacionales open source más usadas del mundo. La diferencia es de FILOSOFÍA: MySQL es rápida y simple (buena para web tradicional), PostgreSQL es robusta y extensible (buena para sistemas complejos). Para proyectos nuevos en 2025, PostgreSQL es generalmente la mejor elección.

2.8.2 Conectar Node.js a PostgreSQL con el paquete pg

El paquete oficial de PostgreSQL para Node.js es pg (node-postgres). Te da un cliente y un pool de conexiones. El pool es importante: reutiliza conexiones en vez de abrir y cerrar una por cada query, lo que es MUCHO más rápido bajo carga.

// db.js: pool de conexiones a PostgreSQL
import pg from 'pg';
const { Pool } = pg;

export const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mi_app',
  user: 'postgres',
  password: 'mi_password',  // en producción: variable de entorno
  max: 20,                  // máximo de conexiones simultáneas
  idleTimeoutMillis: 30000,
});

// Hacer una query simple
const result = await pool.query('SELECT NOW()');
console.log(result.rows[0]);

// Query parametrizada (prepared statement, previene SQL injection)
const { rows } = await pool.query(
  'SELECT * FROM usuarios WHERE email = $1',
  ['ana@mail.com']
);

// Liberar el pool al cerrar la app
process.on('SIGTERM', () => pool.end());

Pool de conexiones con pg

2.8.3 SQL avanzado: JOIN, GROUP BY, HAVING

Las consultas reales casi nunca viven en una sola tabla: usuarios con sus pedidos, pedidos con sus productos, categorías con sus posts. Para unir datos de múltiples tablas, necesitas JOIN. Para agregar, GROUP BY y HAVING. Es el SQL que usas en apps reales.

-- Ejemplo: usuarios con su número de pedidos y gasto total
SELECT
  u.id, u.nombre, u.email,
  COUNT(p.id) AS num_pedidos,
  COALESCE(SUM(p.total), 0) AS gasto_total
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
WHERE u.activo = TRUE
GROUP BY u.id, u.nombre, u.email
HAVING COUNT(p.id) > 0   -- solo usuarios CON pedidos
ORDER BY gasto_total DESC
LIMIT 10;

-- Explicación:
-- LEFT JOIN: todos los usuarios, tengan o no pedidos
-- GROUP BY: agrupa por usuario
-- COUNT/SUM: agrega por grupo
-- HAVING: filtra DESPUÉS de agrupar (WHERE filtra antes)

JOIN + GROUP BY + HAVING en acción

2.8.4 Transacciones: atomicidad y consistencia

Una transacción agrupa varias operaciones SQL en una unidad atómica: o se ejecutan TODAS, o NO se ejecuta NINGUNA. Es FUNDAMENTAL para operaciones donde la consistencia es crítica: transferir dinero entre cuentas, registrar un pedido con sus items, etc. PostgreSQL tiene transacciones ACID completas (Atomicidad, Consistencia, Isolation, Durability).

// Ejemplo: transferir $100 entre cuentas (DEBE ser atómico)
import { pool } from './db.js';

async function transferir(origenId, destinoId, monto) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');  // iniciar transacción

    // 1. Descontar del origen
    await client.query(
      'UPDATE cuentas SET saldo = saldo - $1 WHERE id = $2 AND saldo >= $1',
      [monto, origenId]
    );
    // Si no se afectó ninguna fila, el saldo era insuficiente
    const check = await client.query('SELECT saldo FROM cuentas WHERE id = $1', [origenId]);
    if (check.rows[0].saldo < 0) {
      throw new Error('Saldo insuficiente');
    }

    // 2. Acreditar al destino
    await client.query(
      'UPDATE cuentas SET saldo = saldo + $1 WHERE id = $2',
      [monto, destinoId]
    );

    await client.query('COMMIT');  // confirmar: ambas se aplican
  } catch (err) {
    await client.query('ROLLBACK');  // revertir: ninguna se aplica
    throw err;
  } finally {
    client.release();  // devolver la conexión al pool
  }
}

Transacción: BEGIN / COMMIT / ROLLBACK

2.8.5 Tipos de datos avanzados: JSON, ARRAY, UUID

PostgreSQL tiene tipos de datos que MySQL no tiene (o no tan bien): JSON nativo con índices, arrays, UUID, rangos, tipos geométricos, y más. Esto te permite modelar datos complejos sin tener que crear tablas auxiliares. Es una de las grandes ventajas de PostgreSQL para aplicaciones modernas.

-- Crear tabla con tipos avanzados
CREATE TABLE productos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  nombre TEXT NOT NULL,
  precio NUMERIC(10, 2) NOT NULL,
  tags TEXT[] DEFAULT '{}',  -- array de strings
  metadata JSONB DEFAULT '{}',  -- JSON indexable
  creado_en TIMESTAMPTZ DEFAULT NOW()
);

-- Insertar con JSONB y array
INSERT INTO productos (nombre, precio, tags, metadata) VALUES (
  'Laptop Gamer',
  25000.00,
  ARRAY['tecnologia', 'gaming', 'oferta'],  -- array
  '{"marca": "Asus", "ram": 16, "color": "negro"}'::jsonb  -- JSONB
);

-- Consultar con operadores JSONB
SELECT * FROM productos WHERE metadata->>'marca' = 'Asus';  -- ->> es 'obtener como text'
SELECT * FROM productos WHERE tags @> ARRAY['gaming'];  -- @> es 'contiene'

-- Consultar con arrays
SELECT * FROM productos WHERE 'gaming' = ANY(tags);

JSONB, arrays y UUID en PostgreSQL

2.8.6 Índices y optimización de queries

Una query sin índice en una tabla de 1 millón de filas tarda segundos. Con índice, milisegundos. Los índices son la optimización #1 en bases de datos: dominarlos es la diferencia entre un junior y un senior backend.

EXPLAIN ANALYZE: tu microscopio para queries

-- ¿Por qué esta query tarda 3 segundos?
EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE usuario_id = 42;

-- Posibles resultados:
-- 'Seq Scan on pedidos'  -> MAL: escanea TODA la tabla
-- 'Index Scan using idx_pedidos_usuario'  -> BIEN: usa el índice

-- Crear el índice si no existe:
CREATE INDEX idx_pedidos_usuario ON pedidos(usuario_id);

-- Para queries con WHERE en JSONB:
CREATE INDEX idx_productos_metadata_marca ON productos USING GIN ((metadata->>'marca'));

-- Para queries con ORDER BY frecuente:
CREATE INDEX idx_usuarios_email ON usuarios(email DESC);

EXPLAIN ANALYZE y creación de índices

📚 Contenido ampliado

Material adicional, referencias externas verificadas y ejemplos extendidos.