PostgreSQL

Capacidades que conviene aprovechar

Capacidad	Uso típico
Restricciones	CHECK, UNIQUE, FOREIGN KEY, EXCLUDE: reglas que no dependen del código
JSONB + índice GIN	Atributos variables por país o producto sin un esquema rígido
Índices parciales	WHERE estado = 'PENDIENTE': pequeños y muy rápidos
SKIP LOCKED	Colas de trabajo y relays de outbox con varios workers
Replicación lógica	CDC, migraciones entre versiones, réplicas parciales
Extensiones	pg_stat_statements, pgvector, PostGIS, pg_partman

Diagnóstico de consultas

-- Las consultas que más tiempo total consumen
SELECT query, calls, round(total_exec_time) AS ms_total, round(mean_exec_time, 1) AS ms_media
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Plan real con tiempos y buffers
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM creditos WHERE cliente_id = $1 AND estado = 'VIGENTE';

Un Seq Scan sobre una tabla grande con un filtro selectivo indica un índice ausente. Para este caso, CREATE INDEX CONCURRENTLY ON creditos (cliente_id) WHERE estado = 'VIGENTE';.

Migraciones sin bloqueo

Operación arriesgada	Alternativa segura
CREATE INDEX	CREATE INDEX CONCURRENTLY (fuera de una transacción)
Añadir FOREIGN KEY	Añadir con NOT VALID y luego VALIDATE CONSTRAINT
Añadir NOT NULL	CHECK (col IS NOT NULL) NOT VALID, validar y después SET NOT NULL
Renombrar o borrar una columna en uso	Expand and contract: nueva columna, doble escritura, migrar lectores, borrar
Cualquier ALTER TABLE	SET lock_timeout = '3s' para fallar rápido en vez de encolar bloqueos

Desde PostgreSQL 11 añadir una columna con valor por defecto constante no reescribe la tabla.

Operación

• Connection pooling: cada conexión es un proceso; usa PgBouncer o el pooler gestionado, especialmente con servicios serverless que escalan instancias.
• Autovacuum: no lo desactives; ajústalo en tablas con muchas actualizaciones para evitar bloat.
• Backups y PITR: copia base + WAL archivado; prueba la restauración periódicamente. Ver Barman.
• Alta disponibilidad: réplica síncrona o gestionada (Cloud SQL HA, AlloyDB) con failover probado.

Dimensionar las conexiones

conexiones totales = instancias × tamaño del pool por instancia
  Ejemplo: Cloud Run con max-instances = 20 y pool de 10 → hasta 200 conexiones
  Cloud SQL con 4 vCPU suele quedar cómodo con bastantes menos conexiones activas

Más conexiones no significa más rendimiento: por encima de un múltiplo moderado de los núcleos, la contención empeora la latencia. Con muchos clientes efímeros, PgBouncer en modo transaction pooling (o el pooler gestionado) multiplexa cientos de conexiones de cliente sobre pocas de servidor.

Particionado declarativo

CREATE TABLE pagos (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  credito_id  text NOT NULL,
  fecha_pago  date NOT NULL,
  importe     numeric(14,2) NOT NULL,
  PRIMARY KEY (id, fecha_pago)
) PARTITION BY RANGE (fecha_pago);

CREATE TABLE pagos_2026_10 PARTITION OF pagos
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- Retirar datos antiguos sin DELETE masivo
ALTER TABLE pagos DETACH PARTITION pagos_2024_01 CONCURRENTLY;

Particiona tablas muy grandes con un criterio de consulta y retención claro (normalmente fecha). Las consultas que filtran por la clave de partición leen solo las particiones necesarias. pg_partman automatiza la creación de particiones futuras.

Roles y seguridad por filas

-- Roles por función, sin privilegios de superusuario para las aplicaciones
CREATE ROLE app_originacion LOGIN;
GRANT SELECT, INSERT, UPDATE ON solicitudes TO app_originacion;
CREATE ROLE analitica_ro LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA reportes TO analitica_ro;

-- Aislamiento por país dentro de una misma tabla
ALTER TABLE solicitudes ENABLE ROW LEVEL SECURITY;
CREATE POLICY por_pais ON solicitudes
  USING (pais = current_setting('app.pais'));

La aplicación fija SET app.pais = 'CO' al inicio de cada transacción y PostgreSQL filtra automáticamente. Es una segunda barrera, útil en plataformas multipaís o multitenant, que complementa la autorización en la aplicación.

Consultas de diagnóstico

-- Quién bloquea a quién
SELECT bloqueado.pid, bloqueado.query AS esperando,
       bloqueador.pid AS pid_bloqueador, bloqueador.query AS bloqueando
FROM pg_stat_activity bloqueado
JOIN pg_stat_activity bloqueador ON bloqueador.pid = ANY(pg_blocking_pids(bloqueado.pid));

-- Transacciones abiertas hace mucho (impiden el vacuum)
SELECT pid, now() - xact_start AS duracion, state, query
FROM pg_stat_activity WHERE xact_start < now() - interval '5 minutes';

-- Tablas con más filas muertas
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Índices que nunca se usan (candidatos a eliminar)
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

Convierte estas consultas en métricas y alertas (exporter de Prometheus o el monitor de la nube) en lugar de ejecutarlas solo durante un incidente.

Actualizaciones de versión

Las versiones mayores salen una vez al año y se mantienen cinco años. Para actualizar con poco tiempo de corte: pg_upgrade con --link, o replicación lógica hacia un servidor de la nueva versión y cambio de tráfico. Prueba antes la aplicación y las extensiones contra la versión nueva.

Criterios de implementación

Usa un servicio gestionado salvo que tengas un equipo de bases de datos. Activa pg_stat_statements desde el primer día. Revisa las migraciones en el pull request con un linter (p. ej. squawk) y programa simulacros de restauración. Evalúa ClickHouse o un lakehouse para cargas analíticas en lugar de sobrecargar la base transaccional.