Contoso Airlines' crew planning system assigns around 240 crew members to 3,100 rotations every month while respecting minimum rest periods, licenses, aircraft type ratings, home bases and legal flight-hour limits. It does not use PostgreSQL by accident: it was chosen years ago because it needed complex queries with window functions and date ranges, rich data types and, above all, extensions: calculating real distances between airports to estimate crew positioning times.

That system is now being migrated to Azure Database for PostgreSQL - Flexible Server. A good part of what you need you already know from the previous lesson, so it is not repeated here: section 2 explicitly marks what is identical to MySQL, and the rest of the lesson is devoted to what only happens in PostgreSQL, which is exactly what this system needs in order to work.

Cost warning: the same order of magnitude as MySQL — around €120–160 a month for General Purpose with 2 vCores, and high availability doubles the compute. Here too you can stop the server for up to 30 days, which is how the development environment ends up costing almost nothing. Delete the lab resource group when you finish.

1. The crew case and why PostgreSQL
2. What is identical to MySQL (and we are not going to repeat)
3. Deployment with Azure CLI and private access
4. Extensions: the allowlist
5. The crew model and a geospatial query
6. Performance tuning: EXPLAIN ANALYZE, indexes and autovacuum
7. Connection pooling with PgBouncer
8. High availability, replicas and backups: what is different
9. Migration from the in-house instance
10. When to look at Azure Cosmos DB for PostgreSQL
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

The three needs that ruled out the other engines:

• Complex queries: checking that no crew member chains two rotations without the legally required rest demands window functions, date ranges and aggregations over time sequences. PostgreSQL resolves them with powerful standard SQL and a mature planner.
• Rich data types: tstzrange for time intervals with a time zone, jsonb for each crew member's varying qualifications, arrays for lists of bases.
• Extensions: postgis to calculate the real distance between airports and estimate positioning time when a crew member has to be moved from Palma to Barcelona by road or on an internal flight.

Both services share a platform, so the following works exactly the same as in lesson 03-04 and it is enough to recall it:

• Deployment model: flexible server, with the same split of responsibilities between Azure and you.
• Compute tiers: Burstable, General Purpose and Memory Optimized, with the same trap of the CPU credits on the Burstable tier.
• Storage: IOPS tied to the size, auto-grow recommended, increase only.
• Connectivity: private access with a delegated subnet versus public access with a firewall, decided when the server is created and not changeable. Contoso again chooses private access.
• High availability: same zone versus zone redundant, both at double the cost; zone redundant is chosen.
• Backups: automated, retention from 1 to 35 days, a geo option, restore to a new server, which bills and has to be deleted.
• Stopping the server: up to 30 days, ideal for development, automatable with runbooks.
• Mandatory TLS and server parameters exposed as configuration instead of a file.

From here on, everything covered is specific to PostgreSQL.

The structure is almost identical to MySQL's, with two differences worth noting: retention is 21 days because crew planning is reviewed a month in advance and it is useful to be able to go back to a previous roster, and there is no character set parameter because PostgreSQL uses UTF-8 natively and without surprises.

Here is the biggest practical difference from MySQL. On your own PostgreSQL, a superuser installs whatever extension they like; on Azure, you can only enable the ones on an allowlist maintained by Microsoft, and the process has two steps: first you authorize the extension at server level and then you create it in the database.

What each one brings at Contoso:

Changing shared_preload_libraries is a static parameter: it requires restarting the server, so it has to be planned. And before designing anything around an extension, check that it is on the allowlist for your region and version: discovering that it is missing halfway through development is an expensive setback.

The EXCLUDE USING gist constraint has no simple equivalent in MySQL and it is a perfect example of why this system lives in PostgreSQL: the engine guarantees on its own that no crew member can be assigned to two rotations that overlap in time, without the application having to check it. It is the same philosophy as the CHECK constraint in db-reservas: critical rules are defended in the database.

Now the geospatial query that justified postgis. A flight is left without a captain in Palma and you need to know which qualified crew members are within 300 km:

Three things happen here that sum up the lesson: ST_DWithin calculates distances over the Earth's surface in meters and can take advantage of a spatial index; the @> operator queries inside a jsonb document without needing another database; and the && operator checks interval overlap directly. Reproducing this on an engine without extensions would mean pulling the data into the application and calculating there.

EXPLAIN ANALYZE runs the query and shows the real plan with times and row counts, unlike plain EXPLAIN, which only estimates. It is the basic diagnostic tool:

What to look for in the output, in order: a Seq Scan over a large table almost always means an index is missing; a huge gap between the estimated rows (rows=) and the real ones betrays stale statistics, which are fixed with ANALYZE; and a high time in Sort suggests that a suitable index would avoid the sorting. The indexes this model needs:

Notice that not one of them is a conventional B-tree index: GiST for ranges and geometries, GIN for jsonb. Choosing the right type is specific to PostgreSQL and it makes the difference between a 4 ms query and a 4-second one.

And now the problem that surprises people arriving from other engines: autovacuum. PostgreSQL does not delete or update rows in place: it marks the old version as dead and writes a new one. The autovacuum process cleans up those dead versions and refreshes the statistics. On a table with a lot of churn — and the rotaciones table is rewritten in full every time the monthly roster is recalculated — the default autovacuum may not keep up, and then the table bloats, queries slow down progressively and nobody understands why.

The default value of autovacuum_vacuum_scale_factor is 0.2, that is, cleanup happens when 20% of the rows have died; lowering it to 2% on high-churn tables avoids the problem without punishing the rest of the database.

In PostgreSQL, every connection is an operating system process with its own memory. That makes opening and closing connections expensive, and it means a few hundred simultaneous connections are enough to exhaust a mid-sized server. A web application's usual pattern — many short connections, one per request — is precisely the worst case.

PgBouncer comes built into the flexible server and is enabled with a parameter. It keeps a pool of real connections to the server and multiplexes the application's connections over them:

It changes the connection port and little else, but the effect is large: 500 application connections can be served with 25 real ones. The important warning is that transaction pooling mode, which is the one that gives the most benefit, is not compatible with features that depend on session state — session-level prepared statements, persistent SETs, temporary tables across queries — so you have to verify that the application does not use them.

The structural side is the same as in MySQL, so you only need to retain the differences:

• Read replicas are created the same way, but they read better: the crew system generates heavy monthly reports that are now sent to the replica and stop competing with day-to-day planning.
• PostgreSQL also allows cross-region failover with geo replicas, useful if planning is considered critical; Contoso does not enable it, consistently with the cost decision from module 1.
• Point-in-time restore uses the same mechanics: a new server, verify, delete.

The native tools are pg_dump and pg_restore. Compared with the previous lesson's mysqldump there is one notable advantage: the custom format (-Fc) allows restoring in parallel, which cuts the load time considerably.

--no-owner and --no-privileges avoid the most common error in these migrations: the dump tries to assign objects to roles that do not exist in Azure, or to a superuser you do not have here. The roles are recreated afterwards, with minimal permissions.

Before migrating there are two PostgreSQL-specific things to verify: that every extension in use is on Azure's allowlist — if the system depends on one that is not, the project stops before it starts — and that the dump includes the PostGIS objects correctly, since the extension has to exist in the target before you restore. For large volumes or minimal outages, Azure Database Migration Service offers online migration with logical replication, with the same selection criterion as in MySQL.

There is a third option worth knowing about even though Contoso does not use it: Azure Cosmos DB for PostgreSQL, based on the Citus extension, which distributes tables across several nodes and runs queries in parallel. It is genuine PostgreSQL, with its extensions, but scaled horizontally.

Its usage criterion is narrow and it is important not to get it wrong: it makes sense when a single instance becomes structurally too small — tens of terabytes, real-time analytical workloads, or multi-tenant applications with thousands of customers — and there is a natural distribution column, such as the tenant identifier. For the crew system, with 240 crew members and a few gigabytes of data, it would be as disproportionate as it is expensive.

• Designing around an extension without checking the allowlist. It is the most frequent blocker when migrating PostgreSQL to Azure, and it is discovered late.
• Forgetting the extension's second step. Authorizing it in azure.extensions does not create it: the CREATE EXTENSION inside each database is still missing.
• Ignoring autovacuum. The symptom is a slow degradation over weeks on high-churn tables. Watch n_dead_tup before anybody complains.
• Using B-tree for everything. Ranges need GiST and jsonb needs GIN; with the wrong index, the planner simply does not use it.
• Opening thousands of short connections without PgBouncer. In PostgreSQL every connection is a process: it is the fastest route to exhausting the server's memory.
• Restoring a dump with the old server's owners and privileges. Use --no-owner --no-privileges and recreate the roles in the target.
• Trusting EXPLAIN without ANALYZE. Without running the query you only see estimates, and the problem is usually precisely that the estimates are bad.
• Tip: enable pg_stat_statements from day one. When the first complaint about slowness arrives you will have weeks of history instead of starting to measure then.
• Tip: run EXPLAIN ANALYZE on the three most frequent queries after every bulk data load; plans change when the volume changes.

The crew system needs postgis for distances, pg_stat_statements for diagnosis and pgvector for semantic search over the operations manual.

1. Write out the complete steps, saying which are at server level and which at database level.
2. Which of them requires restarting the server, and why?
3. What would you check before committing to this architecture?

The query that lists a crew member's rotations took 20 ms when the system launched and now, three months later, it takes 1.8 seconds. The data volume has grown very little, but the roster is recalculated in full every week.

1. What is the most likely cause and how would you confirm it?
2. Write the fix.
3. What would EXPLAIN ANALYZE reveal if a suitable index on periodo were also missing?

The planning application opens one connection per HTTP request and at peak it reaches 800 simultaneous connections. The server starts refusing connections and consuming all the memory.

1. Explain why this is more serious in PostgreSQL than in other engines.
2. Propose the solution and what has to be verified before applying it.
3. If after applying it the problem persisted and the volume grew to tens of terabytes, which option from the lesson would you consider, and on what condition?

Solution 1:

1. At server level: az postgres flexible-server parameter set --name azure.extensions --value "postgis,pg_stat_statements,vector" and, in addition, --name shared_preload_libraries --value "pg_stat_statements". At database level, connected to tripulaciones: CREATE EXTENSION IF NOT EXISTS postgis;, ... pg_stat_statements; and ... vector;.
2. shared_preload_libraries, because it is a static parameter: the library is loaded when the process starts, so it cannot be applied live. The restart is planned into the maintenance window.
3. That the three extensions are on Azure's allowlist for the chosen region and PostgreSQL version. If any of them were not, that part of the design would have to be rethought before migrating, not afterwards.

Solution 2:

1. The most likely cause is the accumulation of dead rows (bloat) from an autovacuum that cannot keep up: recalculating the whole roster every week generates enormous churn in rotaciones. It is confirmed with SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;: if n_dead_tup is of the same order as n_live_tup or higher, it is confirmed.
2. Tune autovacuum on that table alone and clean up once:

3. It would show a Seq Scan over rotaciones with a high real time and many rows discarded by the filter (Rows Removed by Filter), instead of an Index Scan over a GiST index. The fix would be CREATE INDEX ... USING gist (periodo), because a B-tree is no good for the overlap operator &&.

Solution 3:

1. Because in PostgreSQL every connection is an operating system process with its own reserved memory, not a lightweight thread. With 800 connections, the memory consumption and the cost of creating and destroying processes exhaust the server even if the query load is modest.
2. Enable PgBouncer with az postgres flexible-server parameter set --name pgbouncer.enabled --value true and connect the application to port 6432. First you have to verify that the application does not depend on session state — session-level prepared statements, persistent SETs or temporary tables reused across queries — because transaction pooling mode does not support it. Increasing the server size is an expensive solution that only postpones the problem.
3. Azure Cosmos DB for PostgreSQL (Citus), but only on the condition that there is a natural distribution column that spreads the data well and that most queries filter on. Without it, distribution makes performance worse rather than better, exactly like a badly chosen partition key in Cosmos DB.

The crew planning system is now in Azure and it keeps precisely what made it valuable. You have seen that Azure Database for PostgreSQL - Flexible Server shares with MySQL the deployment model, the compute tiers, the storage with IOPS tied to the size, the irrevocable private connectivity, the high availability at double the cost, the backups with restore to a new server and the ability to stop the server so you do not pay in development, and from there you have worked only on what is different.

What is different starts with the extensions and their allowlist, with its two steps — authorize on the server and create in the database — and the restart that shared_preload_libraries demands: postgis for the distances between bases and airports, pg_stat_statements to know which queries are consuming the server, pgcrypto for the identity documents and pgvector already pointing towards module 6's semantic search. You have modeled crew members and rotations taking advantage of tstzrange, jsonb and an EXCLUDE USING gist constraint that on its own prevents a crew member from having two overlapping rotations, and you have written a real geospatial query with ST_DWithin. You know how to diagnose with EXPLAIN ANALYZE and what to look for in its output, how to choose the right index type — GiST for ranges and geography, GIN for jsonb — and how to recognize and fix the autovacuum problem on high-churn tables, which degrades performance slowly until somebody complains. You have enabled PgBouncer understanding why every connection is heavy in PostgreSQL, you have migrated with pg_dump/pg_restore in parallel avoiding the owners-and-privileges mistake, and you know the narrow criterion by which one day you would look at Cosmos DB for PostgreSQL with Citus.

With that, the four operational stores on Contoso Airlines' data map are deployed: db-reservas in Azure SQL Database, the fare catalog in Cosmos DB, the portal in MySQL and the crews in PostgreSQL. They all share one trait: they are designed to answer small questions about recent data, fast. None of them is any use for the question management has been asking for months and nobody can answer: which routes are genuinely profitable by season, crossing three years of sales, occupancy, fuel costs and delays. Firing that query at db-reservas at eleven in the morning would degrade ticket sales for every customer. In the module's last lesson, Data Analytics: Data Lake, Data Factory and Synapse, you will build the analytics platform that answers that question without touching the operational databases even once.