El sistema de planificació de tripulacions de Contoso Airlines assigna cada mes uns 240 tripulants a 3.100 rotacions respectant descansos mínims, llicències, habilitacions per tipus d'aeronau, bases de residència i límits legals d'hores de vol. No fa servir PostgreSQL per casualitat: el va triar fa anys perquè necessitava consultes complexes amb finestres i rangs de dates, tipus de dades rics i, sobretot, extensions: càlcul de distàncies reals entre aeroports per estimar temps de posicionament de la tripulació.

Aquest sistema es migra ara a Azure Database for PostgreSQL - Servidor flexible. Bona part del que necessites ja ho saps de la lliçó anterior, així que aquí no es repeteix: l'apartat 2 marca explícitament què és idèntic a MySQL i la resta de la lliçó es dedica al que només passa a PostgreSQL, que és justament el que cal perquè aquest sistema funcioni.

Avís de cost: mateix ordre de magnitud que MySQL —uns 120-160 € al mes per a Ús general amb 2 vCores, i l'alta disponibilitat duplica el còmput—. També aquí es pot aturar el servidor fins a 30 dies, que és la manera que l'entorn de desenvolupament no costi gairebé res. Elimina el grup de recursos de laboratori en acabar.

1. El cas de les tripulacions i per què PostgreSQL
2. Què és idèntic a MySQL (i no repetirem)
3. Desplegament amb Azure CLI i accés privat
4. Extensions: la llista de permeses
5. El model de tripulacions i una consulta geoespacial
6. Ajust de rendiment: EXPLAIN ANALYZE, índexs i autovacuum
7. Agrupació de connexions amb PgBouncer
8. Alta disponibilitat, rèpliques i còpies: el que és diferencial
9. Migració des de la instància pròpia
10. Quan mirar cap a Azure Cosmos DB for PostgreSQL
11. Errors Comuns i Consells
12. Exercicis
13. Conclusió

Les tres necessitats que van descartar els altres motors:

• Consultes complexes: comprovar que cap tripulant no encadena dues rotacions sense el descans legal exigeix funcions de finestra, rangs de dates i agregacions sobre seqüències temporals. PostgreSQL les resol amb SQL estàndard potent i un planificador madur.
• Tipus de dades rics: tstzrange per a intervals de temps amb zona horària, jsonb per a les habilitacions variables de cada tripulant, arrays per a llistes de bases.
• Extensions: postgis per calcular la distància real entre aeroports i estimar el temps de posicionament quan cal moure un tripulant de Palma a Barcelona per carretera o en un vol intern.

Tots dos serveis comparteixen plataforma, així que el següent funciona exactament igual que a la lliçó 03-04 i n'hi ha prou de recordar-ho:

• Model de desplegament: servidor flexible, amb el mateix repartiment de responsabilitats entre Azure i tu.
• Nivells de còmput: Ampliable, Ús general i Optimitzat per a memòria, amb la mateixa trampa dels crèdits de CPU al nivell Ampliable.
• Emmagatzematge: IOPS lligades a la mida, creixement automàtic recomanat, només ampliable.
• Connectivitat: accés privat amb subxarxa delegada davant d'accés públic amb tallafoc, decidit en crear el servidor i no modificable. Contoso torna a triar accés privat.
• Alta disponibilitat: mateixa zona davant de redundància de zona, totes dues al doble de cost; es tria redundància de zona.
• Còpies de seguretat: automàtiques, retenció d'1 a 35 dies, opció geogràfica, restauració a un servidor nou, que factura i cal eliminar.
• Aturada del servidor: fins a 30 dies, ideal per a desenvolupament, automatitzable amb runbooks.
• TLS obligatori i paràmetres del servidor exposats com a configuració en lloc de fitxer.

A partir d'aquí, tot el que s'explica és propi de PostgreSQL.

L'estructura és gairebé idèntica a la de MySQL, amb dues diferències que convé notar: la retenció és de 21 dies perquè la planificació de tripulacions es revisa amb un mes de marge i convé poder tornar enrere a un quadrant anterior, i no hi ha paràmetre de joc de caràcters perquè PostgreSQL fa servir UTF-8 de manera nativa i sense sorpreses.

Aquí hi ha la diferència pràctica més gran amb MySQL. En un PostgreSQL propi, un superusuari instal·la l'extensió que vulgui; a Azure, només es poden habilitar les d'una llista de permeses mantinguda per Microsoft, i el procés té dos passos: primer s'autoritza l'extensió a nivell de servidor i després es crea a la base de dades.

Què aporta cadascuna a Contoso:

Canviar shared_preload_libraries és un paràmetre estàtic: requereix reiniciar el servidor, així que es planifica. I abans de dissenyar res sobre una extensió, comprova que és a la llista de permeses de la teva regió i versió: descobrir que hi falta a mitja implementació és un contratemps car.

La restricció EXCLUDE USING gist no té equivalent senzill a MySQL i és un exemple perfecte de per què aquest sistema viu a PostgreSQL: el motor garanteix per si mateix que cap tripulant no pot estar assignat a dues rotacions que se solapin en el temps, sense que l'aplicació ho hagi de comprovar. És la mateixa filosofia de la restricció CHECK de db-reservas: les regles crítiques es defensen a la base de dades.

Ara la consulta geoespacial que va justificar postgis. Un vol es queda sense comandant a Palma i cal saber quins tripulants qualificats hi ha a menys de 300 km:

Aquí hi passen tres coses que resumeixen la lliçó: ST_DWithin calcula distàncies sobre la superfície terrestre en metres i pot aprofitar un índex espacial; l'operador @> consulta dins d'un document jsonb sense necessitar cap altra base de dades; i l'operador && comprova solapament d'intervals directament. Reproduir això en un motor sense extensions significaria portar les dades a l'aplicació i calcular-hi allà.

EXPLAIN ANALYZE executa la consulta i mostra el pla real amb temps i files, davant d'EXPLAIN a seques, que només estima. És l'eina bàsica de diagnòstic:

Què cal buscar a la sortida, per ordre: un Seq Scan sobre una taula gran gairebé sempre indica que falta un índex; una diferència enorme entre les files estimades (rows=) i les reals delata estadístiques desactualitzades, que es corregeixen amb ANALYZE; i un temps alt a Sort suggereix que un índex adequat evitaria l'ordenació. Els índexs que necessita aquest model:

Fixa't que cap no és un índex B-tree convencional: GiST per a rangs i geometries, GIN per a jsonb. Triar el tipus correcte és específic de PostgreSQL i marca la diferència entre una consulta de 4 ms i una de 4 segons.

I el problema que sorprèn qui arriba d'altres motors: l'autovacuum. PostgreSQL no esborra ni actualitza files al lloc: marca la versió antiga com a morta i n'escriu una de nova. El procés autovacuum neteja aquestes versions mortes i actualitza les estadístiques. En una taula amb molta rotació —i la taula rotaciones es reescriu sencera cada vegada que es recalcula el quadrant mensual— l'autovacuum predeterminat pot no donar l'abast, i llavors la taula s'infla (bloat), les consultes s'alenteixen de manera progressiva i ningú no entén per què.

El valor predeterminat d'autovacuum_vacuum_scale_factor és 0,2, és a dir, es neteja quan ha mort el 20 % de les files; baixar-lo al 2 % en taules d'alta rotació evita el problema sense castigar la resta de la base.

A PostgreSQL, cada connexió és un procés del sistema operatiu amb la seva pròpia memòria. Això fa que obrir i tancar connexions sigui car i que uns pocs centenars de connexions simultànies bastin per esgotar un servidor mitjà. El patró habitual d'una aplicació web —moltes connexions curtes, una per petició— és precisament el pitjor cas.

PgBouncer ve integrat al servidor flexible i s'activa amb un paràmetre. Manté un conjunt de connexions reals al servidor i hi multiplexa a sobre les connexions de l'aplicació:

Canvia el port de connexió i poca cosa més, però l'efecte és gran: 500 connexions d'aplicació es poden atendre amb 25 connexions reals. L'advertiment important és que el mode d'agrupació per transacció, que és el que dona més benefici, no és compatible amb funcionalitats que depenen de l'estat de la sessió —sentències preparades de sessió, SET persistents, taules temporals entre consultes—, així que cal verificar que l'aplicació no les faci servir.

L'estructural és igual que a MySQL, així que només cal retenir les diferències:

• Les rèpliques de lectura es creen igual, però es llegeixen millor: el sistema de tripulacions genera informes mensuals pesats que ara es dirigeixen a la rèplica i deixen de competir amb la planificació diària.
• PostgreSQL permet a més commutació per error entre regions amb rèpliques geogràfiques, útil si la planificació es considera crítica; Contoso no ho activa, coherent amb la decisió de cost del mòdul 1.
• La restauració a un moment donat fa servir la mateixa mecànica: servidor nou, verificar, eliminar.

Les eines natives són pg_dump i pg_restore. Davant del mysqldump de la lliçó anterior hi ha un avantatge notable: el format personalitzat (-Fc) permet restaurar en paral·lel, cosa que redueix molt el temps de càrrega.

--no-owner i --no-privileges eviten l'error més comú d'aquestes migracions: el bolcat intenta assignar objectes a rols que no existeixen a Azure, o a un superusuari que aquí no tens. Els rols es recreen després, amb permisos mínims.

Abans de migrar cal verificar dues coses pròpies de PostgreSQL: que totes les extensions utilitzades són a la llista de permeses d'Azure —si el sistema depèn d'una que no hi és, el projecte s'atura abans de començar— i que el bolcat inclou els objectes de PostGIS correctament, ja que l'extensió ha d'existir a la destinació abans de restaurar. Per a volums grans o talls mínims, Azure Database Migration Service ofereix migració en línia amb replicació lògica, amb el mateix criteri de tria que a MySQL.

Hi ha una tercera opció que convé conèixer encara que Contoso no la faci servir: Azure Cosmos DB for PostgreSQL, basat en l'extensió Citus, que distribueix taules entre diversos nodes i executa les consultes en paral·lel. És PostgreSQL de debò, amb les seves extensions, però escalat horitzontalment.

El seu criteri d'ús és estret i convé no equivocar-se: té sentit quan una sola instància es queda petita de manera estructural —desenes de terabytes, càrregues analítiques en temps real o aplicacions multiinquilí amb milers de clients— i existeix una columna de distribució natural, com l'identificador d'inquilí. Per al sistema de tripulacions, amb 240 tripulants i uns gigabytes de dades, seria tan desproporcionat com car.

• Dissenyar sobre una extensió sense comprovar la llista de permeses. És el bloqueig més freqüent en migrar PostgreSQL a Azure, i es descobreix tard.
• Oblidar el segon pas de l'extensió. Autoritzar-la a azure.extensions no la crea: falta el CREATE EXTENSION dins de cada base de dades.
• Ignorar l'autovacuum. El símptoma és una degradació lenta durant setmanes en taules de molta rotació. Vigila n_dead_tup abans que algú es queixi.
• Fer servir B-tree per a tot. Els rangs necessiten GiST i el jsonb necessita GIN; amb l'índex equivocat, el planificador simplement no el fa servir.
• Obrir milers de connexions curtes sense PgBouncer. A PostgreSQL cada connexió és un procés: és el camí més ràpid per esgotar la memòria del servidor.
• Restaurar un bolcat amb propietaris i privilegis del servidor antic. Fes servir --no-owner --no-privileges i recrea els rols a la destinació.
• Confiar en EXPLAIN sense ANALYZE. Sense executar, només veus estimacions, i el problema sol ser precisament que les estimacions són dolentes.
• Consell: activa pg_stat_statements des del primer dia. Quan arribi la primera queixa de lentitud tindràs setmanes d'historial en lloc de començar a mesurar llavors.
• Consell: prova EXPLAIN ANALYZE sobre les tres consultes més freqüents després de cada càrrega massiva de dades; els plans canvien quan canvia el volum.

El sistema de tripulacions necessita postgis per a distàncies, pg_stat_statements per a diagnòstic i pgvector per a la cerca semàntica del manual d'operacions.

1. Escriu els passos complets, indicant quins són de servidor i quins de base de dades.
2. Quin d'ells exigeix reiniciar el servidor i per què?
3. Què comprovaries abans de comprometre't amb aquesta arquitectura?

La consulta que llista les rotacions d'un tripulant trigava 20 ms en engegar el sistema i ara, tres mesos després, triga 1,8 segons. El volum de dades ha crescut poc, però el quadrant es recalcula sencer cada setmana.

1. Quina és la causa més probable i com la confirmaries?
2. Escriu la correcció.
3. Què revelaria EXPLAIN ANALYZE si a més faltés un índex adequat sobre periodo?

L'aplicació de planificació obre una connexió per petició HTTP i en hora punta arriba a 800 connexions simultànies. El servidor comença a rebutjar connexions i a consumir tota la memòria.

1. Explica per què això és més greu a PostgreSQL que en altres motors.
2. Proposa la solució i què cal verificar abans d'aplicar-la.
3. Si després d'aplicar-la el problema persistís i el volum creixés fins a desenes de terabytes, quina opció de la lliçó valoraries i amb quina condició?

Solució 1:

1. A nivell de servidor: az postgres flexible-server parameter set --name azure.extensions --value "postgis,pg_stat_statements,vector" i, a més, --name shared_preload_libraries --value "pg_stat_statements". A nivell de base de dades, connectat a tripulaciones: CREATE EXTENSION IF NOT EXISTS postgis;, ... pg_stat_statements; i ... vector;.
2. shared_preload_libraries, perquè és un paràmetre estàtic: la biblioteca es carrega en arrencar el procés, així que no es pot aplicar en calent. Es planifica el reinici a la finestra de manteniment.
3. Que les tres extensions siguin a la llista de permeses d'Azure per a la regió i la versió de PostgreSQL triades. Si alguna no hi fos, caldria replantejar aquesta part del disseny abans de migrar, no després.

Solució 2:

1. La causa més probable és l'acumulació de files mortes (bloat) per un autovacuum que no dona l'abast: recalcular el quadrant sencer cada setmana genera una rotació enorme a rotaciones. Es confirma amb 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 és de l'ordre de n_live_tup o superior, està confirmat.
2. Ajustar l'autovacuum només en aquesta taula i netejar d'una vegada:

3. Mostraria un Seq Scan sobre rotaciones amb un temps real alt i moltes files descartades pel filtre (Rows Removed by Filter), en lloc d'un Index Scan sobre un índex GiST. La correcció seria CREATE INDEX ... USING gist (periodo), perquè un B-tree no serveix per a l'operador de solapament &&.

Solució 3:

1. Perquè a PostgreSQL cada connexió és un procés del sistema operatiu amb la seva pròpia memòria reservada, no un fil lleuger. Amb 800 connexions, el consum de memòria i el cost de crear i destruir processos esgoten el servidor encara que la càrrega de consultes sigui modesta.
2. Activar PgBouncer amb az postgres flexible-server parameter set --name pgbouncer.enabled --value true i connectar l'aplicació al port 6432. Abans cal verificar que l'aplicació no depengui de l'estat de sessió —sentències preparades de sessió, SET persistents o taules temporals reutilitzades entre consultes—, perquè el mode d'agrupació per transacció no ho admet. Pujar la mida del servidor és una solució cara que només ajorna el problema.
3. Azure Cosmos DB for PostgreSQL (Citus), però només amb la condició que existeixi una columna de distribució natural que reparteixi bé les dades i per la qual filtrin la majoria de les consultes. Sense ella, la distribució empitjora el rendiment en lloc de millorar-lo, exactament igual que una clau de partició mal triada a Cosmos DB.

El sistema de planificació de tripulacions ja és a Azure i conserva justament el que el feia valuós. Has vist que Azure Database for PostgreSQL - Servidor flexible comparteix amb MySQL el model de desplegament, els nivells de còmput, l'emmagatzematge amb IOPS lligades a la mida, la connectivitat privada irrevocable, l'alta disponibilitat al doble de cost, les còpies amb restauració a servidor nou i la possibilitat d'aturar el servidor per no pagar en desenvolupament, i a partir d'aquí has treballat només el que és diferencial.

El que és diferencial comença per les extensions i la seva llista de permeses, amb els seus dos passos —autoritzar al servidor i crear a la base— i el reinici que exigeix shared_preload_libraries: postgis per a les distàncies entre bases i aeroports, pg_stat_statements per saber quines consultes consumeixen el servidor, pgcrypto per als documents d'identitat i pgvector apuntant ja a la cerca semàntica del mòdul 6. Has modelat tripulants i rotacions aprofitant tstzrange, jsonb i una restricció EXCLUDE USING gist que impedeix tota sola que un tripulant tingui dues rotacions solapades, i has escrit una consulta geoespacial real amb ST_DWithin. Saps diagnosticar amb EXPLAIN ANALYZE i què buscar a la seva sortida, triar el tipus d'índex correcte —GiST per a rangs i geografia, GIN per a jsonb— i reconèixer i corregir el problema de l'autovacuum en taules de molta rotació, que degrada el rendiment lentament fins que algú es queixa. Has activat PgBouncer entenent per què a PostgreSQL cada connexió pesa, has migrat amb pg_dump/pg_restore en paral·lel evitant l'error de propietaris i privilegis, i coneixes el criteri estret pel qual algun dia es miraria cap a Cosmos DB for PostgreSQL amb Citus.

Amb això, els quatre magatzems operatius del mapa de dades de Contoso Airlines estan desplegats: db-reservas a Azure SQL Database, el catàleg de tarifes a Cosmos DB, el portal a MySQL i les tripulacions a PostgreSQL. Tots comparteixen un tret: estan dissenyats per respondre ràpid a preguntes petites sobre dades recents. Cap no serveix per a la pregunta que la direcció fa des de fa mesos i ningú no sap respondre: quines rutes són realment rendibles per temporada, creuant tres anys de vendes, ocupació, costos de combustible i retards. Llançar aquesta consulta contra db-reservas a les onze del matí degradaria la venda de bitllets per a tots els clients. A l'última lliçó del mòdul, Analítica de dades: Data Lake, Data Factory i Synapse, construiràs la plataforma analítica que respon aquesta pregunta sense tocar ni una sola vegada les bases operatives.