PostgreSQL desde cero: guía completa con ejemplos reales para desarrolladores 2026

PostgreSQL es el sistema de bases de datos relacional de código abierto más avanzado del mundo. No es solo una alternativa a MySQL: es una base de datos con características que MySQL todavía no tiene, como soporte nativo para JSON, arrays, tipos personalizados, full text search avanzado, y extensiones como pgvector para IA. Esta guía te enseña PostgreSQL desde la instalación hasta consultas avanzadas con ejemplos reales.

¿Por qué PostgreSQL?

PostgreSQL tiene más de 35 años de desarrollo activo y sigue siendo la base de datos favorita de desarrolladores según la encuesta de Stack Overflow. Sus ventajas clave son el cumplimiento estricto del estándar SQL, soporte para tipos de datos avanzados (JSONB, arrays, rangos, geometría), transacciones robustas con MVCC, extensibilidad mediante extensiones, y un rendimiento excelente tanto en lectura como en escritura.

Instalación de PostgreSQL

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# macOS con Homebrew
brew install postgresql@16
brew services start postgresql@16

# Verificar instalación
psql --version

# Conectarse como superusuario postgres
sudo -u postgres psql

# Alternativa: usar Docker (la más cómoda para desarrollo)
docker run --name mi-postgres   -e POSTGRES_PASSWORD=mipassword   -e POSTGRES_DB=mi_base_datos   -p 5432:5432   -d postgres:16

# Conectarse al contenedor Docker
docker exec -it mi-postgres psql -U postgres

Comandos básicos de psql

psql es la interfaz de línea de comandos de PostgreSQL. Aunque existen clientes gráficos como pgAdmin o DBeaver, saber moverte por psql es fundamental para trabajar en servidores remotos.

-- Comandos psql (con )
l              -- Listar todas las bases de datos
c mi_base      -- Conectarse a una base de datos
dt             -- Listar tablas de la base de datos actual
d nombre_tabla -- Describir la estructura de una tabla
du             -- Listar usuarios/roles
i archivo.sql  -- Ejecutar un archivo SQL
	iming         -- Mostrar tiempo de ejecución de queries
e              -- Abrir el editor externo
q              -- Salir

-- Conectarse desde línea de comandos
psql -h localhost -p 5432 -U usuario -d mi_base_datos

-- Con URI de conexión
psql "postgresql://usuario:password@localhost:5432/mi_base_datos"

Crear bases de datos, usuarios y permisos

-- Crear una base de datos
CREATE DATABASE tienda
  WITH ENCODING 'UTF8'
  LC_COLLATE 'es_ES.UTF-8'
  LC_CTYPE 'es_ES.UTF-8';

-- Crear un usuario
CREATE USER tienda_user WITH PASSWORD 'password_seguro';

-- Dar permisos
GRANT ALL PRIVILEGES ON DATABASE tienda TO tienda_user;

-- En la base de datos tienda:
c tienda

-- Dar permisos sobre esquema público
GRANT ALL ON SCHEMA public TO tienda_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tienda_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO tienda_user;

-- Permisos para tablas futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO tienda_user;

-- Eliminar base de datos o usuario
DROP DATABASE IF EXISTS tienda_test;
DROP USER IF EXISTS usuario_viejo;

Crear tablas: tipos de datos esenciales

PostgreSQL tiene un sistema de tipos muy rico. Conocer los tipos correctos para cada situación mejora el rendimiento y la integridad de los datos.

CREATE TABLE usuarios (
    -- Identificadores
    id          BIGSERIAL PRIMARY KEY,           -- Auto-incremento grande
    uuid        UUID DEFAULT gen_random_uuid(),   -- UUID como alternativa

    -- Texto
    nombre      VARCHAR(100) NOT NULL,            -- Longitud máxima
    email       TEXT NOT NULL UNIQUE,             -- Sin límite de longitud
    bio         TEXT,                             -- Nullable por defecto

    -- Números
    edad        SMALLINT,                         -- 2 bytes (-32768 a 32767)
    puntuacion  INTEGER DEFAULT 0,                -- 4 bytes
    saldo       NUMERIC(12, 2) DEFAULT 0.00,      -- Precisión exacta (moneda)
    altura      REAL,                             -- Punto flotante 4 bytes

    -- Booleanos y fechas
    activo      BOOLEAN NOT NULL DEFAULT TRUE,
    fecha_nac   DATE,
    creado_en   TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Con zona horaria
    ultima_vez  TIMESTAMPTZ,

    -- Tipos especiales de PostgreSQL
    etiquetas   TEXT[],                            -- Array de texto
    metadatos   JSONB,                             -- JSON binario (indexable)
    ip_address  INET,                              -- Dirección IP

    -- Constraints
    CHECK (edad IS NULL OR (edad >= 0 AND edad <= 150)),
    CHECK (puntuacion >= 0)
);

-- Índices
CREATE INDEX idx_usuarios_email ON usuarios(email);
CREATE INDEX idx_usuarios_creado ON usuarios(creado_en DESC);
CREATE INDEX idx_usuarios_activo ON usuarios(activo) WHERE activo = TRUE;

-- Índice GIN para búsquedas en JSONB
CREATE INDEX idx_usuarios_meta ON usuarios USING GIN(metadatos);

El schema completo de una tienda online

-- Categorías con árbol recursivo
CREATE TABLE categorias (
    id          SERIAL PRIMARY KEY,
    nombre      VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) NOT NULL UNIQUE,
    padre_id    INTEGER REFERENCES categorias(id) ON DELETE SET NULL,
    orden       INTEGER DEFAULT 0
);

-- Productos
CREATE TABLE productos (
    id              SERIAL PRIMARY KEY,
    sku             VARCHAR(50) UNIQUE NOT NULL,
    nombre          VARCHAR(200) NOT NULL,
    descripcion     TEXT,
    precio          NUMERIC(10, 2) NOT NULL CHECK (precio >= 0),
    precio_oferta   NUMERIC(10, 2) CHECK (precio_oferta IS NULL OR precio_oferta < precio),
    stock           INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    categoria_id    INTEGER REFERENCES categorias(id) ON DELETE SET NULL,
    atributos       JSONB DEFAULT '{}',         -- Atributos variables del producto
    imagenes        TEXT[],                     -- Array de URLs
    activo          BOOLEAN DEFAULT TRUE,
    creado_en       TIMESTAMPTZ DEFAULT NOW(),
    actualizado_en  TIMESTAMPTZ DEFAULT NOW()
);

-- Trigger para actualizar fecha de modificación
CREATE OR REPLACE FUNCTION actualizar_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.actualizado_en = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_productos_actualizar
    BEFORE UPDATE ON productos
    FOR EACH ROW EXECUTE FUNCTION actualizar_timestamp();

-- Pedidos
CREATE TABLE pedidos (
    id              BIGSERIAL PRIMARY KEY,
    usuario_id      BIGINT NOT NULL REFERENCES usuarios(id),
    estado          VARCHAR(20) NOT NULL DEFAULT 'pendiente'
                    CHECK (estado IN ('pendiente', 'pagado', 'enviado', 'entregado', 'cancelado')),
    total           NUMERIC(12, 2) NOT NULL,
    direccion       JSONB NOT NULL,
    creado_en       TIMESTAMPTZ DEFAULT NOW()
);

-- Líneas de pedido
CREATE TABLE lineas_pedido (
    id              SERIAL PRIMARY KEY,
    pedido_id       BIGINT NOT NULL REFERENCES pedidos(id) ON DELETE CASCADE,
    producto_id     INTEGER NOT NULL REFERENCES productos(id),
    cantidad        INTEGER NOT NULL CHECK (cantidad > 0),
    precio_unitario NUMERIC(10, 2) NOT NULL,
    subtotal        NUMERIC(12, 2) GENERATED ALWAYS AS (cantidad * precio_unitario) STORED
);

Consultas SQL esenciales

-- SELECT básico con filtros
SELECT id, nombre, email, creado_en
FROM usuarios
WHERE activo = TRUE
  AND creado_en > NOW() - INTERVAL '30 days'
ORDER BY creado_en DESC
LIMIT 20 OFFSET 0;

-- JOINs
SELECT
    p.id,
    p.nombre AS producto,
    p.precio,
    c.nombre AS categoria,
    COUNT(lp.id) AS veces_vendido
FROM productos p
LEFT JOIN categorias c ON c.id = p.categoria_id
LEFT JOIN lineas_pedido lp ON lp.producto_id = p.id
WHERE p.activo = TRUE
GROUP BY p.id, p.nombre, p.precio, c.nombre
HAVING COUNT(lp.id) > 0
ORDER BY veces_vendido DESC
LIMIT 10;

-- Subconsultas y CTEs (Common Table Expressions)
WITH ventas_por_mes AS (
    SELECT
        DATE_TRUNC('month', pe.creado_en) AS mes,
        SUM(pe.total) AS total_ventas,
        COUNT(*) AS num_pedidos
    FROM pedidos pe
    WHERE pe.estado != 'cancelado'
      AND pe.creado_en >= NOW() - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', pe.creado_en)
),
mes_anterior AS (
    SELECT * FROM ventas_por_mes
    ORDER BY mes DESC
    LIMIT 1 OFFSET 1
)
SELECT
    vm.mes,
    vm.total_ventas,
    vm.num_pedidos,
    ROUND(
        ((vm.total_ventas - ma.total_ventas) / ma.total_ventas * 100)::NUMERIC,
        2
    ) AS variacion_porcentual
FROM ventas_por_mes vm
CROSS JOIN mes_anterior ma
ORDER BY vm.mes DESC;

JSONB: el poder de los datos semi-estructurados

JSONB es una de las características más potentes de PostgreSQL. Te permite guardar datos semi-estructurados que se pueden indexar y consultar eficientemente, sin necesidad de definir el esquema de antemano.

-- Insertar con JSONB
UPDATE productos
SET atributos = '{"color": "rojo", "talla": ["S", "M", "L"], "peso_kg": 0.5}'
WHERE id = 1;

-- Consultar propiedades JSONB
SELECT nombre, atributos->>'color' AS color
FROM productos
WHERE atributos->>'color' = 'rojo';

-- Consultar arrays en JSONB
SELECT nombre, atributos->'talla' AS tallas
FROM productos
WHERE atributos->'talla' ? 'XL';

-- Buscar en propiedades anidadas
SELECT nombre, atributos#>>'{especificaciones, procesador}' AS cpu
FROM productos
WHERE atributos @> '{"categoria": "laptop"}';

-- Índice GIN para búsquedas eficientes
CREATE INDEX idx_productos_atributos ON productos USING GIN(atributos);

-- Funciones de JSONB
SELECT
    jsonb_each_text(atributos) AS (clave, valor)
FROM productos
WHERE id = 1;

Full Text Search en PostgreSQL

PostgreSQL tiene búsqueda de texto completo integrada sin necesidad de Elasticsearch para casos de uso simples a medianos:

-- Añadir columna para búsqueda vectorizada
ALTER TABLE productos
ADD COLUMN busqueda_ts TSVECTOR
    GENERATED ALWAYS AS (
        setweight(to_tsvector('spanish', COALESCE(nombre, '')), 'A') ||
        setweight(to_tsvector('spanish', COALESCE(descripcion, '')), 'B')
    ) STORED;

-- Índice GIN para búsqueda eficiente
CREATE INDEX idx_productos_busqueda ON productos USING GIN(busqueda_ts);

-- Buscar productos
SELECT
    id,
    nombre,
    ts_rank(busqueda_ts, query) AS relevancia
FROM productos,
     plainto_tsquery('spanish', 'zapatillas running') AS query
WHERE busqueda_ts @@ query
ORDER BY relevancia DESC
LIMIT 20;

-- Con resaltado de resultados
SELECT
    nombre,
    ts_headline('spanish', descripcion, query, 'MaxFragments=2') AS extracto
FROM productos,
     plainto_tsquery('spanish', 'zapatillas running') AS query
WHERE busqueda_ts @@ query;

Transacciones y concurrencia

-- Transacción básica
BEGIN;
  UPDATE productos SET stock = stock - 1 WHERE id = 42 AND stock > 0;
  INSERT INTO lineas_pedido (pedido_id, producto_id, cantidad, precio_unitario)
    VALUES (1001, 42, 1, 29.99);
  UPDATE pedidos SET total = total + 29.99 WHERE id = 1001;
COMMIT;  -- O ROLLBACK si algo falla

-- Nivel de aislamiento
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- ...
COMMIT;

-- SELECT FOR UPDATE: bloquear filas para actualización
BEGIN;
  SELECT * FROM productos WHERE id = 42 FOR UPDATE;
  -- Nadie más puede modificar este registro hasta que se haga COMMIT
  UPDATE productos SET stock = stock - 1 WHERE id = 42;
COMMIT;

-- UPSERT (INSERT o UPDATE si ya existe)
INSERT INTO usuarios (email, nombre)
VALUES ('ana@ejemplo.com', 'Ana García')
ON CONFLICT (email) DO UPDATE
SET nombre = EXCLUDED.nombre,
    ultima_vez = NOW();

Optimización de consultas: EXPLAIN ANALYZE

Cuando una consulta es lenta, el primer paso es entender qué está haciendo el planificador de PostgreSQL. EXPLAIN ANALYZE te muestra el plan de ejecución real con tiempos.

-- Ver el plan de ejecución
EXPLAIN ANALYZE
SELECT u.nombre, COUNT(p.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
WHERE u.activo = TRUE
GROUP BY u.id, u.nombre
HAVING COUNT(p.id) > 5
ORDER BY total_pedidos DESC;

-- Buscar Sequential Scans en tablas grandes (señal de índice faltante)
-- Buscar tiempos altos en nodos específicos
-- Hash Join vs Nested Loop vs Merge Join

-- Índice parcial para queries frecuentes
CREATE INDEX idx_pedidos_pendientes
    ON pedidos(usuario_id, creado_en DESC)
    WHERE estado = 'pendiente';

-- Ver estadísticas de tablas
SELECT schemaname, tablename, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Forzar actualización de estadísticas
ANALYZE productos;

Backup y restauración

# Backup de una base de datos
pg_dump -h localhost -U usuario -d mi_base -Fc -f backup.dump

# Backup de todas las bases de datos
pg_dumpall -h localhost -U postgres > backup_total.sql

# Restaurar
pg_restore -h localhost -U usuario -d mi_base_nueva -Fc backup.dump

# Backup continuo / WAL archiving (producción)
# Usar herramientas como pgBackRest o Barman para backups en producción

# Ver tamaño de bases de datos
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS tamaño
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

# Ver tamaño de tablas e índices
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS tamaño_total,
    pg_size_pretty(pg_relation_size(tablename::regclass)) AS tamaño_tabla,
    pg_size_pretty(pg_indexes_size(tablename::regclass)) AS tamaño_indices
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

PostgreSQL es una base de datos que cuanto más la conoces, más aprecias. Su robustez, su cumplimiento de estándares y sus características avanzadas como JSONB, full text search y las extensiones la convierten en la elección más sólida para la mayoría de proyectos. Si estás pensando en qué base de datos usar para tu próximo proyecto, PostgreSQL rara vez es la respuesta incorrecta.