Guía Práctica de PostgreSQL: Índices, JSONB, Diagnóstico y Backup

PostgreSQL es el motor relacional por excelencia en arquitecturas backend modernas gracias a su estabilidad, soporte avanzado de datos semiestructurados y herramientas precisas de observabilidad. Esta guía condensa comandos de consola (psql), optimización de consultas, uso de JSONB y tareas habituales de mantenimiento.

1. Atajos Clave en la Consola psql

Moverse dentro del cliente de terminal sin recurrir a interfaces pesadas:

  • Conectarse a una base de datos específica:
    bash
    psql -U mi_usuario -d mi_base_datos -h localhost
  • Listar bases de datos: \l
  • Conectarse a otra base de datos: \c nombre_db
  • Listar tablas y vistas: \dt
  • Describir estructura, columnas e índices de una tabla: \d+ nombre_tabla
  • Activar visualización expandida (clave para filas con muchas columnas): \x auto
  • Salir: \q

2. Diagnóstico y Optimización con EXPLAIN ANALYZE

Antes de crear índices a ciegas, analiza el plan de ejecución real del motor:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * 
FROM pedidos 
WHERE usuario_id = 42 
  AND estado = 'completado';

Qué revisar en la salida:

  • Seq Scan vs Index Scan: Un Seq Scan en tablas con millones de registros significa que PostgreSQL recorre todo el disco sin usar índices.
  • Cost vs Actual Time: El tiempo real medido en milisegundos (actual time=...) refleja el cuello de botella.
  • Shared Hit / Read: Indica cuántas páginas leyó desde la memoria caché (hit) vs disco (read).

3. Estrategias de Indexación Avanzada

No todos los índices deben ser un B-Tree simple:

Índice Compuesto (Orden Importante)

Alinea el índice con los filtros más frecuentes:

sql
CREATE INDEX idx_pedidos_usuario_estado 
ON pedidos (usuario_id, estado);

Índice Parcial (Ahorro de Espacio y Memoria)

Ideal si consultas recurrentemente un subconjunto específico de registros:

sql
-- Solo indexa pedidos pendientes, ignorando el 95% ya completados o cancelados
CREATE INDEX idx_pedidos_pendientes 
ON pedidos (creado_en) 
WHERE estado = 'pendiente';

Creación sin Bloquear Escrituras en Producción

sql
CREATE INDEX CONCURRENTLY idx_usuarios_email 
ON usuarios (email);

4. Manipulación de Datos Semi-Estructurados con JSONB

JSONB almacena JSON en formato binario descompuesto, permitiendo indexación y lecturas ultrarrápidas:

sql
-- Crear tabla con payload JSONB
CREATE TABLE logs_eventos (
    id SERIAL PRIMARY KEY,
    metadata JSONB NOT NULL
);

-- Extraer un valor como texto (operador ->>)
SELECT metadata->>'navegador' AS navegador 
FROM logs_eventos;

-- Filtrar registros donde el JSON contenga una estructura (operador @>)
SELECT * 
FROM logs_eventos 
WHERE metadata @> '{"pais": "CL", "rol": "admin"}';

-- Crear índice GIN para acelerar búsquedas sobre cualquier propiedad JSON
CREATE INDEX idx_logs_metadata_gin 
ON logs_eventos USING gin (metadata);

5. Respaldos y Restauración Rápida (pg_dump y pg_restore)

  • Exportar base de datos completa en formato binario comprimido (recomendado):

    bash
    pg_dump -U mi_usuario -h localhost -Fc mi_base_datos > backup_prod.dump

  • Exportar solo el esquema (sin datos):

    bash
    pg_dump -U mi_usuario -s mi_base_datos > schema.sql

  • Restaurar respaldo binario en paralelo con múltiples cores:

    bash
    pg_restore -U mi_usuario -d mi_base_datos_dev -j 4 backup_prod.dump