Curso de PostgreSQL para Empresas — Administración y rendimiento

Conexión local con `psql`

Navegación entre bases, tablas y esquemas

Listado y descripción de objetos (`\l`, `\d`, etc.)

Ejecución de scripts `.sql`

Exportación e importación de datos desde CLI

Uso de variables y `\set`

Salida en CSV o JSON desde terminal

Cliente gráfico: pgAdmin y DBeaver

Práctica: exploración guiada de base de ejemplo

BONUS: creación de alias de conexión

Diferencia entre base de datos y esquema

Tipos de datos soportados

Claves primarias, foráneas y restricciones

Creación de tablas con `CREATE TABLE`

Tipos compuestos y arrays

Uso de `SERIAL`, `BIGSERIAL`, `UUID`

Normalización y desnormalización básica

Comentarios sobre columnas y tablas

Práctica: modelado de base para ecommerce

BONUS: inspección con `pg_catalog` y `information_schema`

Consultas simples con `SELECT`

Filtros con `WHERE`, `IN`, `BETWEEN`

Orden con `ORDER BY` y `LIMIT`

Funciones de agregación (`AVG`, `SUM`, `MAX`, etc.)

Agrupaciones con `GROUP BY` y `HAVING`

Subconsultas en `FROM`, `WHERE`, `SELECT`

Alias, funciones y expresiones

Práctica: KPIs básicos de usuarios y ventas

BONUS: cláusulas avanzadas con `DISTINCT ON`

BONUS: expresiones CASE y lógica condicional

JOINs: `INNER`, `LEFT`, `RIGHT`, `FULL OUTER`

Auto joins y alias

Relaciones entre múltiples tablas

Join entre campos derivados

Uso de `COALESCE()` para nulos

Subconsultas correlacionadas

Comparación con subconsultas normales

Práctica: reportes combinados de clientes y pedidos

BONUS: optimización de joins costosos

BONUS: vista para JOIN persistente

¿Qué es un índice y cómo funciona en PostgreSQL?

Índices B-Tree, Hash, GIN, GiST

Índices únicos y compuestos

Uso de `EXPLAIN ANALYZE`

Creación de índices sobre expresiones

Eliminación de índices ineficientes

Particiones e índices parciales

VACUUM y `pg_stat_user_indexes`

Práctica: análisis de consulta con y sin índice

BONUS: índice BRIN para datos masivos

Funciones escalares (`CREATE FUNCTION`)

Funciones con parámetros y retorno

Funciones de tabla

Common Table Expressions (`WITH`)

Vistas simples y materializadas

Refresco de vistas materializadas

Uso de funciones agregadas personalizadas

Práctica: CTE con múltiples niveles

BONUS: función para cálculo de márgenes por mes

BONUS: creación de informes con vista materializada

Creación de roles con `CREATE ROLE`

Roles con `LOGIN`, `SUPERUSER`, `CREATEDB`, etc.

Asignación de privilegios con `GRANT` y `REVOKE`

Roles heredables y agrupados

Permisos sobre funciones, secuencias y vistas

Seguridad a nivel columna y fila (`Row Level Security`)

Práctica: rol analista solo lectura

BONUS: bloqueo de acceso por horario

BONUS: auditoría con `log_connections` y `log_disconnections`

`BEGIN`, `COMMIT`, `ROLLBACK`

Aislamiento de transacciones

MVCC en PostgreSQL

Bloqueos: shared vs exclusive

Deadlocks y cómo evitarlos

Uso de `FOR UPDATE`, `FOR SHARE`

Lock timeout y `NOWAIT`

Práctica: simulación de bloqueo entre sesiones

BONUS: configuración del nivel de aislamiento

BONUS: snapshot transactions

Dump lógico con `pg_dump`

Restauración con `pg_restore`

Dumps parciales (estructura o datos)

Compresión y paralelización

Backup completo con `pg_basebackup`

Programación con `cron`

Restauración selectiva de esquemas

Práctica: backup completo + restauración en staging

BONUS: verificación automática con `pg_isready`

BONUS: restauración PITR (Point in Time Recovery)

Archivos `postgresql.conf` y `pg_hba.conf`

Parámetros de conexión y rendimiento

Reload vs restart

Configuración del listen_address

Logging y niveles de detalle

Comprobación de actividad con `pg_stat_activity`

Versión y extensiones instaladas

Práctica: ajuste de `work_mem` y `shared_buffers`

BONUS: configuración por usuario o sesión

BONUS: logs rotativos

Autenticación por contraseña, peer, md5 y scram-sha-256

Conexión cifrada por SSL

Certificados y claves

Protección contra SQL Injection

Prácticas de roles mínimos

Seguridad por aplicación o IP

Monitorización de conexiones sospechosas

Práctica: habilitar SSL y bloquear conexiones no seguras

BONUS: auditoría con `pgaudit`

BONUS: políticas de seguridad row-level (`RLS`)

Replicación física y lógica

Configuración de `wal_level`, `max_wal_senders`

Replica caliente (hot standby)

Monitoreo del estado de replicación

Switchover manual

Uso de `pg_stat_replication`

Limitaciones y pruebas

Práctica: réplica en segunda VM

BONUS: delay replica (streaming con lag)

BONUS: cluster asíncrono seguro

¿Qué son las extensiones?

`pg_stat_statements` para análisis de consultas

`uuid-ossp` para UUIDs

`tablefunc` para tablas cruzadas

`pg_trgm` para búsqueda aproximada

`hstore` para key-values

`postgis` para datos geográficos

`citext` para insensibilidad a mayúsculas

Práctica: búsqueda fuzzy con `pg_trgm`

BONUS: instalación vía `CREATE EXTENSION`

`pg_stat_activity`, `pg_stat_database`, `pg_locks`

Consultas costosas: tiempo, buffer, locking

Herramientas externas: pgAdmin, pgtop, pgBadger

Uso de `auto_explain`

Configuración del log de consultas lentas

Visualización de hit ratio

Práctica: habilitar estadísticas y analizar workload

BONUS: integración con Grafana y Prometheus

BONUS: informes PDF con pgBadger

¿Qué es el particionado?

Declaración y herencia vs particionado nativo

Rango (`RANGE`), lista (`LIST`) y hash (`HASH`)

`DEFAULT` partition y reglas de redirección

Verificación de particiones activas

Eliminación segura de particiones antiguas

Mejora en consultas masivas

Práctica: particionado mensual de tabla de logs

BONUS: reindexado paralelo de particiones

BONUS: mantenimiento automatizado con funciones

Columnas `JSON` y `JSONB`

Acceso a propiedades con `->` y `->>`

Indexación en `JSONB`

Funciones `jsonb_each`, `jsonb_array_elements`

Validación de estructuras

Transformación de JSON a relaciones

Consultas avanzadas sobre campos JSON

Práctica: modelado mixto SQL + JSON

BONUS: uso de JSON en APIs

BONUS: agregaciones dinámicas con `json_agg`

Simulación de cron con `pg_cron`

Automatización con `pgAgent`

Programación de análisis, vacíos, backups

Envío de reportes por email

Tareas condicionales

Validación de tareas programadas

Logs de ejecución y errores

Práctica: tarea de limpieza semanal

BONUS: integración con sistema externo (shell + SQL)

BONUS: alertas automáticas

Migración desde MySQL, SQL Server u Oracle

Herramientas: `pgloader`, `ora2pg`

Validación de esquemas

Conversión de tipos incompatibles

Migración de datos históricos

Estrategia de pruebas A/B

Comparación de rendimiento

Práctica: migración simulada desde MySQL

BONUS: checklist de migración

BONUS: rollback seguro en staging

Diseño y modelado relacional complejo

Creación de funciones, índices y vistas

Carga de datos simulados

Reportes analíticos con funciones de ventana

Tareas automáticas y backup

Monitorización activa

Seguridad por usuario y acceso

Exportación del proyecto a CSV

Documentación y checklist de producción

Presentación final del entorno PostgreSQL operativo