El sistema de planificación de tripulaciones de Contoso Airlines asigna cada mes unos 240 tripulantes a 3.100 rotaciones respetando descansos mínimos, licencias, habilitaciones por tipo de aeronave, bases de residencia y límites legales de horas de vuelo. No usa PostgreSQL por casualidad: lo eligió hace años porque necesitaba consultas complejas con ventanas y rangos de fechas, tipos de datos ricos y, sobre todo, extensiones: cálculo de distancias reales entre aeropuertos para estimar tiempos de posicionamiento de la tripulación.

Ese sistema se migra ahora a Azure Database for PostgreSQL - Servidor flexible. Buena parte de lo que necesitas ya lo sabes de la lección anterior, así que aquí no se repite: el apartado 2 marca explícitamente qué es idéntico a MySQL y el resto de la lección se dedica a lo que solo pasa en PostgreSQL, que es justamente lo que hace falta para que este sistema funcione.

Aviso de coste: mismo orden de magnitud que MySQL —unos 120-160 € al mes para De uso general con 2 vCores, y la alta disponibilidad duplica el cómputo—. También aquí se puede detener el servidor hasta 30 días, que es la forma de que el entorno de desarrollo no cueste casi nada. Elimina el grupo de recursos de laboratorio al terminar.

1. El caso de las tripulaciones y por qué PostgreSQL
2. Qué es idéntico a MySQL (y no vamos a repetir)
3. Despliegue con Azure CLI y acceso privado
4. Extensiones: la lista de permitidas
5. El modelo de tripulaciones y una consulta geoespacial
6. Ajuste de rendimiento: EXPLAIN ANALYZE, índices y autovacuum
7. Agrupación de conexiones con PgBouncer
8. Alta disponibilidad, réplicas y copias: lo diferencial
9. Migración desde la instancia propia
10. Cuándo mirar a Azure Cosmos DB for PostgreSQL
11. Errores Comunes y Consejos
12. Ejercicios
13. Conclusión

Las tres necesidades que descartaron los otros motores:

• Consultas complejas: comprobar que ningún tripulante encadena dos rotaciones sin el descanso legal exige funciones de ventana, rangos de fechas y agregaciones sobre secuencias temporales. PostgreSQL las resuelve con SQL estándar potente y un planificador maduro.
• Tipos de datos ricos: tstzrange para intervalos de tiempo con zona horaria, jsonb para las habilitaciones variables de cada tripulante, arrays para listas de bases.
• Extensiones: postgis para calcular la distancia real entre aeropuertos y estimar el tiempo de posicionamiento cuando hay que mover a un tripulante de Palma a Barcelona por carretera o en un vuelo interno.

Ambos servicios comparten plataforma, así que lo siguiente funciona exactamente igual que en la lección 03-04 y basta con recordarlo:

• Modelo de despliegue: servidor flexible, con el mismo reparto de responsabilidades entre Azure y tú.
• Niveles de cómputo: Ampliable, De uso general y Optimizado para memoria, con la misma trampa de los créditos de CPU en el nivel Ampliable.
• Almacenamiento: IOPS ligadas al tamaño, crecimiento automático recomendado, solo ampliable.
• Conectividad: acceso privado con subred delegada frente a acceso público con firewall, decidido al crear el servidor y no modificable. Contoso vuelve a elegir acceso privado.
• Alta disponibilidad: misma zona frente a redundancia de zona, ambas al doble de coste; se elige redundancia de zona.
• Copias de seguridad: automáticas, retención de 1 a 35 días, opción geográfica, restauración a un servidor nuevo, que factura y hay que eliminar.
• Detención del servidor: hasta 30 días, ideal para desarrollo, automatizable con runbooks.
• TLS obligatorio y parámetros del servidor expuestos como configuración en lugar de fichero.

A partir de aquí, todo lo que se cuenta es propio de PostgreSQL.

La estructura es casi idéntica a la de MySQL, con dos diferencias que conviene notar: la retención es de 21 días porque la planificación de tripulaciones se revisa con un mes de margen y conviene poder volver atrás a un cuadrante anterior, y no hay parámetro de juego de caracteres porque PostgreSQL usa UTF-8 de forma nativa y sin sorpresas.

Aquí está la mayor diferencia práctica con MySQL. En un PostgreSQL propio, un superusuario instala la extensión que quiera; en Azure, solo se pueden habilitar las de una lista de permitidas mantenida por Microsoft, y el proceso tiene dos pasos: primero se autoriza la extensión a nivel de servidor y después se crea en la base de datos.

Qué aporta cada una en Contoso:

Cambiar shared_preload_libraries es un parámetro estático: requiere reiniciar el servidor, así que se planifica. Y antes de diseñar nada sobre una extensión, comprueba que está en la lista de permitidas de tu región y versión: descubrir que falta a mitad del desarrollo es un contratiempo caro.

La restricción EXCLUDE USING gist no tiene equivalente sencillo en MySQL y es un ejemplo perfecto de por qué este sistema vive en PostgreSQL: el motor garantiza por sí mismo que ningún tripulante puede estar asignado a dos rotaciones que se solapen en el tiempo, sin que la aplicación tenga que comprobarlo. Es la misma filosofía de la restricción CHECK de db-reservas: las reglas críticas se defienden en la base de datos.

Ahora la consulta geoespacial que justificó postgis. Un vuelo se queda sin comandante en Palma y hay que saber qué tripulantes cualificados hay a menos de 300 km:

Tres cosas ocurren aquí que resumen la lección: ST_DWithin calcula distancias sobre la superficie terrestre en metros y puede aprovechar un índice espacial; el operador @> consulta dentro de un documento jsonb sin necesitar otra base de datos; y el operador && comprueba solapamiento de intervalos directamente. Reproducir esto en un motor sin extensiones significaría traer los datos a la aplicación y calcular allí.

EXPLAIN ANALYZE ejecuta la consulta y muestra el plan real con tiempos y filas, frente a EXPLAIN a secas, que solo estima. Es la herramienta básica de diagnóstico:

Qué buscar en la salida, en orden: un Seq Scan sobre una tabla grande casi siempre indica que falta un índice; una diferencia enorme entre las filas estimadas (rows=) y las reales delata estadísticas desactualizadas, que se corrigen con ANALYZE; y un tiempo alto en Sort sugiere que un índice adecuado evitaría la ordenación. Los índices que necesita este modelo:

Fíjate en que ninguno es un índice B-tree convencional: GiST para rangos y geometrías, GIN para jsonb. Elegir el tipo correcto es específico de PostgreSQL y marca la diferencia entre una consulta de 4 ms y una de 4 segundos.

Y el problema que sorprende a quien llega de otros motores: el autovacuum. PostgreSQL no borra ni actualiza filas en el sitio: marca la versión antigua como muerta y escribe una nueva. El proceso autovacuum limpia esas versiones muertas y actualiza las estadísticas. En una tabla con mucha rotación —y la tabla rotaciones se reescribe entera cada vez que se recalcula el cuadrante mensual— el autovacuum predeterminado puede no dar abasto, y entonces la tabla se hincha (bloat), las consultas se ralentizan de forma progresiva y nadie entiende por qué.

El valor predeterminado de autovacuum_vacuum_scale_factor es 0,2, es decir, se limpia cuando ha muerto el 20 % de las filas; bajarlo al 2 % en tablas de alta rotación evita el problema sin castigar al resto de la base.

En PostgreSQL, cada conexión es un proceso del sistema operativo con su propia memoria. Eso hace que abrir y cerrar conexiones sea caro y que unos pocos cientos de conexiones simultáneas basten para agotar un servidor mediano. El patrón habitual de una aplicación web —muchas conexiones cortas, una por petición— es precisamente el peor caso.

PgBouncer viene integrado en el servidor flexible y se activa con un parámetro. Mantiene un conjunto de conexiones reales al servidor y multiplexa sobre ellas las conexiones de la aplicación:

Cambia el puerto de conexión y poco más, pero el efecto es grande: 500 conexiones de aplicación pueden atenderse con 25 conexiones reales. La advertencia importante es que el modo de agrupación por transacción, que es el que da más beneficio, no es compatible con funcionalidades que dependen del estado de la sesión —sentencias preparadas de sesión, SET persistentes, tablas temporales entre consultas—, así que hay que verificar que la aplicación no las use.

Lo estructural es igual que en MySQL, así que solo hay que retener las diferencias:

• Las réplicas de lectura se crean igual, pero se leen mejor: el sistema de tripulaciones genera informes mensuales pesados que ahora se dirigen a la réplica y dejan de competir con la planificación diaria.
• PostgreSQL permite además conmutación por error entre regiones con réplicas geográficas, útil si la planificación se considera crítica; Contoso no lo activa, coherente con la decisión de coste del módulo 1.
• La restauración a un momento dado usa la misma mecánica: servidor nuevo, verificar, eliminar.

Las herramientas nativas son pg_dump y pg_restore. Frente al mysqldump de la lección anterior hay una ventaja notable: el formato personalizado (-Fc) permite restaurar en paralelo, lo que reduce mucho el tiempo de carga.

--no-owner y --no-privileges evitan el error más común de estas migraciones: el volcado intenta asignar objetos a roles que no existen en Azure, o a un superusuario que aquí no tienes. Los roles se recrean después, con permisos mínimos.

Antes de migrar hay que verificar dos cosas propias de PostgreSQL: que todas las extensiones usadas están en la lista de permitidas de Azure —si el sistema depende de una que no lo está, el proyecto se para antes de empezar— y que el volcado incluye los objetos de PostGIS correctamente, ya que la extensión debe existir en el destino antes de restaurar. Para volúmenes grandes o cortes mínimos, Azure Database Migration Service ofrece migración en línea con replicación lógica, con el mismo criterio de elección que en MySQL.

Existe una tercera opción que conviene conocer aunque Contoso no la use: Azure Cosmos DB for PostgreSQL, basado en la extensión Citus, que distribuye tablas entre varios nodos y ejecuta las consultas en paralelo. Es PostgreSQL de verdad, con sus extensiones, pero escalado horizontalmente.

Su criterio de uso es estrecho y conviene no equivocarse: tiene sentido cuando una sola instancia se queda pequeña de forma estructural —decenas de terabytes, cargas analíticas en tiempo real o aplicaciones multiinquilino con miles de clientes— y existe una columna de distribución natural, como el identificador de inquilino. Para el sistema de tripulaciones, con 240 tripulantes y unos gigabytes de datos, sería tan desproporcionado como caro.

• Diseñar sobre una extensión sin comprobar la lista de permitidas. Es el bloqueo más frecuente al migrar PostgreSQL a Azure, y se descubre tarde.
• Olvidar el segundo paso de la extensión. Autorizarla en azure.extensions no la crea: falta el CREATE EXTENSION dentro de cada base de datos.
• Ignorar el autovacuum. El síntoma es una degradación lenta durante semanas en tablas de mucha rotación. Vigila n_dead_tup antes de que alguien se queje.
• Usar B-tree para todo. Los rangos necesitan GiST y el jsonb necesita GIN; con el índice equivocado, el planificador simplemente no lo usa.
• Abrir miles de conexiones cortas sin PgBouncer. En PostgreSQL cada conexión es un proceso: es el camino más rápido a agotar la memoria del servidor.
• Restaurar un volcado con propietarios y privilegios del servidor antiguo. Usa --no-owner --no-privileges y recrea los roles en el destino.
• Confiar en EXPLAIN sin ANALYZE. Sin ejecutar, solo ves estimaciones, y el problema suele estar precisamente en que las estimaciones son malas.
• Consejo: activa pg_stat_statements desde el primer día. Cuando llegue la primera queja de lentitud tendrás semanas de historial en lugar de empezar a medir entonces.
• Consejo: prueba EXPLAIN ANALYZE sobre las tres consultas más frecuentes después de cada carga masiva de datos; los planes cambian cuando cambia el volumen.

El sistema de tripulaciones necesita postgis para distancias, pg_stat_statements para diagnóstico y pgvector para la búsqueda semántica del manual de operaciones.

1. Escribe los pasos completos, indicando cuáles son de servidor y cuáles de base de datos.
2. ¿Cuál de ellos exige reiniciar el servidor y por qué?
3. ¿Qué comprobarías antes de comprometerte con esta arquitectura?

La consulta que lista las rotaciones de un tripulante tardaba 20 ms al lanzarse el sistema y ahora, tres meses después, tarda 1,8 segundos. El volumen de datos ha crecido poco, pero el cuadrante se recalcula entero cada semana.

1. ¿Cuál es la causa más probable y cómo la confirmarías?
2. Escribe la corrección.
3. ¿Qué revelaría EXPLAIN ANALYZE si además faltara un índice adecuado sobre periodo?

La aplicación de planificación abre una conexión por petición HTTP y en hora punta llega a 800 conexiones simultáneas. El servidor empieza a rechazar conexiones y a consumir toda la memoria.

1. Explica por qué esto es más grave en PostgreSQL que en otros motores.
2. Propón la solución y qué hay que verificar antes de aplicarla.
3. Si tras aplicarla el problema persistiera y el volumen creciera hasta decenas de terabytes, ¿qué opción de la lección valorarías y con qué condición?

Solución 1:

1. A nivel de servidor: az postgres flexible-server parameter set --name azure.extensions --value "postgis,pg_stat_statements,vector" y, además, --name shared_preload_libraries --value "pg_stat_statements". A nivel de base de datos, conectado a tripulaciones: CREATE EXTENSION IF NOT EXISTS postgis;, ... pg_stat_statements; y ... vector;.
2. shared_preload_libraries, porque es un parámetro estático: la biblioteca se carga al arrancar el proceso, así que no puede aplicarse en caliente. Se planifica el reinicio en la ventana de mantenimiento.
3. Que las tres extensiones estén en la lista de permitidas de Azure para la región y la versión de PostgreSQL elegidas. Si alguna no lo estuviera, habría que replantear esa parte del diseño antes de migrar, no después.

Solución 2:

1. La causa más probable es la acumulación de filas muertas (bloat) por un autovacuum que no da abasto: recalcular el cuadrante entero cada semana genera una rotación enorme en rotaciones. Se confirma con SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;: si n_dead_tup es del orden de n_live_tup o mayor, está confirmado.
2. Ajustar el autovacuum solo en esa tabla y limpiar de una vez:

3. Mostraría un Seq Scan sobre rotaciones con un tiempo real alto y muchas filas descartadas por el filtro (Rows Removed by Filter), en lugar de un Index Scan sobre un índice GiST. La corrección sería CREATE INDEX ... USING gist (periodo), porque un B-tree no sirve para el operador de solapamiento &&.

Solución 3:

1. Porque en PostgreSQL cada conexión es un proceso del sistema operativo con su propia memoria reservada, no un hilo ligero. Con 800 conexiones, el consumo de memoria y el coste de crear y destruir procesos agotan el servidor aunque la carga de consultas sea modesta.
2. Activar PgBouncer con az postgres flexible-server parameter set --name pgbouncer.enabled --value true y conectar la aplicación al puerto 6432. Antes hay que verificar que la aplicación no dependa del estado de sesión —sentencias preparadas de sesión, SET persistentes o tablas temporales reutilizadas entre consultas—, porque el modo de agrupación por transacción no lo admite. Subir el tamaño del servidor es una solución cara que solo pospone el problema.
3. Azure Cosmos DB for PostgreSQL (Citus), pero solo con la condición de que exista una columna de distribución natural que reparta bien los datos y por la que filtren la mayoría de las consultas. Sin ella, la distribución empeora el rendimiento en lugar de mejorarlo, exactamente igual que una clave de partición mal elegida en Cosmos DB.

El sistema de planificación de tripulaciones ya está en Azure y conserva justo lo que lo hacía valioso. Has visto que Azure Database for PostgreSQL - Servidor flexible comparte con MySQL el modelo de despliegue, los niveles de cómputo, el almacenamiento con IOPS ligadas al tamaño, la conectividad privada irrevocable, la alta disponibilidad al doble de coste, las copias con restauración a servidor nuevo y la posibilidad de detener el servidor para no pagar en desarrollo, y a partir de ahí has trabajado solo lo diferencial.

Lo diferencial empieza por las extensiones y su lista de permitidas, con sus dos pasos —autorizar en el servidor y crear en la base— y el reinicio que exige shared_preload_libraries: postgis para las distancias entre bases y aeropuertos, pg_stat_statements para saber qué consultas consumen el servidor, pgcrypto para los documentos de identidad y pgvector apuntando ya a la búsqueda semántica del módulo 6. Has modelado tripulantes y rotaciones aprovechando tstzrange, jsonb y una restricción EXCLUDE USING gist que impide por sí sola que un tripulante tenga dos rotaciones solapadas, y has escrito una consulta geoespacial real con ST_DWithin. Sabes diagnosticar con EXPLAIN ANALYZE y qué buscar en su salida, elegir el tipo de índice correcto —GiST para rangos y geografía, GIN para jsonb— y reconocer y corregir el problema del autovacuum en tablas de mucha rotación, que degrada el rendimiento lentamente hasta que alguien se queja. Has activado PgBouncer entendiendo por qué en PostgreSQL cada conexión pesa, has migrado con pg_dump/pg_restore en paralelo evitando el error de propietarios y privilegios, y conoces el criterio estrecho por el que algún día se miraría a Cosmos DB for PostgreSQL con Citus.

Con esto, los cuatro almacenes operativos del mapa de datos de Contoso Airlines están desplegados: db-reservas en Azure SQL Database, el catálogo de tarifas en Cosmos DB, el portal en MySQL y las tripulaciones en PostgreSQL. Todos comparten un rasgo: están diseñados para responder rápido a preguntas pequeñas sobre datos recientes. Ninguno sirve para la pregunta que la dirección lleva meses haciendo y nadie sabe responder: qué rutas son realmente rentables por temporada, cruzando tres años de ventas, ocupación, costes de combustible y retrasos. Lanzar esa consulta contra db-reservas a las once de la mañana degradaría la venta de billetes para todos los clientes. En la última lección del módulo, Analítica de datos: Data Lake, Data Factory y Synapse, construirás la plataforma analítica que responde esa pregunta sin tocar ni una vez las bases operativas.