Open-source databases on Azure: PostgreSQL and MySQL

The website vendor's product catalogue is the one Harrow & Finch database that Walt has never looked after. It runs on PostgreSQL, on a server the vendor rents, and holds about 14,000 products with descriptions, prices, photos' file names and a search index that lets customers find "blue exterior paint" even when they type "outdoor paint blue". Marcus Feldt, the e-commerce manager, wants it moved into Harrow & Finch's own Azure environment in 2027 so the company controls its own data. At the eighth data hour Elena explains that Azure offers managed services for the two most popular open-source relational databases, PostgreSQL and MySQL, and that the DP-900 exam expects you to know they exist and what they offer.

What open source means for a database

An open-source database is one whose source code is published under a licence that lets anyone use, study, change and share it, usually free of licence fees. PostgreSQL and MySQL are the two best-known open-source relational databases, alongside MariaDB, which began as a fork of MySQL. They are used by millions of websites and applications, from small blogs to some of the largest companies in the world, and they run on almost any operating system.

Organizations choose them for several reasons: no licence cost for the database itself, large communities and ecosystems of tools, portability between clouds and on-premises servers, and particular features that suit certain applications. Many software products, including the catalogue vendor's, are built on them, which means a company that buys the software inherits the database choice.

Running an open-source database yourself still costs something: servers, storage, patching, backups, replication and the expertise to tune it. Managed services take on that work in the same way Azure SQL does for SQL Server. Both open-source services are platform as a service: Microsoft runs the engine, the operating system and the hardware, while the customer designs the database and controls access.

PostgreSQL

PostgreSQL, often called Postgres, began at the University of California, Berkeley in the 1980s and has been developed by a worldwide community since the 1990s. It is known for following the SQL standard closely, for strong reliability and data integrity, and above all for being extensible. Its extension system lets developers add new data types, functions and index types without changing the core engine.

Several extensions are widely used. PostGIS adds geographic data, such as store locations and delivery zones, with functions for distances and areas. pgvector adds vector data types for storing embeddings, the numeric representations of text or images used by AI applications for similarity search. Full-text search, built into PostgreSQL, finds documents that match words in any order, which is how the catalogue copes with "outdoor paint blue". PostgreSQL also has excellent support for JSON through its jsonb type, so a single table can mix structured columns with semi-structured details, such as product attributes that differ between paint and power tools.

PostgreSQL's SQL dialect differs from T-SQL in many small ways: functions have different names, text is concatenated with two vertical bars, and procedures are written in a language called PL/pgSQL. The core concepts of the earlier lessons are identical: tables, keys, constraints, joins, views, indexes and transactions with ACID guarantees.

CREATE TABLE products (
    sku         text PRIMARY KEY,
    name        text NOT NULL,
    price       numeric(10, 2) NOT NULL CHECK (price >= 0),
    attributes  jsonb
);

-- Find exterior paints with a satin finish, using a field inside the JSON column
SELECT sku, name, price
FROM products
WHERE attributes ->> 'finish' = 'satin'
  AND attributes ->> 'use' = 'exterior'
ORDER BY price;

MySQL

MySQL was created in Sweden in the mid-1990s and became the database behind much of the early web, as the M in the LAMP stack of Linux, Apache, MySQL and PHP. Many content management systems, online shops and web applications are built on it, including WordPress, which runs a large share of the world's websites. MySQL is known for being easy to learn, fast for typical web workloads and widely supported by hosting providers. It is now owned by Oracle and available in an open-source community edition and commercial editions.

MariaDB is a community-developed fork of MySQL, created by some of MySQL's original developers, and is largely compatible with it. Azure used to offer a managed MariaDB service, but Microsoft retired Azure Database for MariaDB on September 19, 2025 and directed customers to Azure Database for MySQL instead. You may still see MariaDB in older study materials; for Azure today, the managed open-source options are PostgreSQL and MySQL.

Azure Database for PostgreSQL and Azure Database for MySQL

Both services are fully managed, and the current deployment option for each is called flexible server. Earlier deployment options, called single server, have been retired, so new work uses flexible server. Each flexible server is a managed database server that can hold one or more databases, with the following features.

• Compute tiers. Burstable compute suits light or variable workloads that need full power only occasionally, such as test servers. General Purpose suits most production workloads. A higher tier, called Memory Optimized for PostgreSQL and Business Critical for MySQL, suits workloads that need more memory and the best performance.
• Automatic backups with point-in-time restore, with a retention period you choose, up to 35 days, and the option of geo-redundant backup storage.
• High availability, optionally zone-redundant, which keeps a standby server in another availability zone and fails over to it automatically if the primary fails.
• Read replicas, which are read-only copies that can take reporting queries away from the main server, in the same region or another one.
• Scheduled maintenance windows that you choose, so updates happen at a quiet time.
• Network choices: public access protected by firewall rules, or private access inside an Azure virtual network.
• The ability to stop the server when it is not needed, for example a development server at night, paying only for storage while stopped.

Managed services also handle minor version updates of the engine, while major version upgrades, such as moving from PostgreSQL 16 to 17, are done when the customer chooses, because they can change behaviour. Microsoft supports a set of PostgreSQL and MySQL versions at any time and publishes when each will reach the end of support, so a DBA plans upgrades rather than waiting for a deadline.

Scaling PostgreSQL out

A single database server, however large, eventually reaches a limit. For PostgreSQL workloads that outgrow one server, such as a software service with thousands of tenants or a very large analytics application, Azure Database for PostgreSQL offers elastic clusters. An elastic cluster spreads, or shards, tables across several nodes using Citus, an open-source PostgreSQL extension. Queries are sent to a coordinator, which runs them in parallel on the nodes and combines the results. An older service, Azure Cosmos DB for PostgreSQL, offered similar distributed PostgreSQL; Microsoft's documentation now describes it as on a retirement path and recommends elastic clusters for new PostgreSQL scale-out work, so if you meet the older name in study materials, think of it as distributed PostgreSQL.

import math
peak_reads = 900        # read queries per second at the holiday peak
primary_keeps = 100     # reads the primary still serves itself
per_replica = 400       # reads one replica can handle
headroom = 1.25         # allow a quarter more than the estimate

needed = (peak_reads - primary_keeps) / per_replica
with_margin = math.ceil(needed * headroom)
print("Replicas needed (exact):", needed)
print("Replicas with headroom:", with_margin)

Replicas needed (exact): 2.0
Replicas with headroom: 3

Connecting and working with the services

Applications connect to a flexible server exactly as they would to any PostgreSQL or MySQL server, using the same drivers, connection strings and client tools, such as psql for PostgreSQL or the mysql command-line client and MySQL Workbench. The server has a host name ending in an Azure domain, and connections are encrypted with TLS by default. Microsoft Entra authentication is supported alongside the engines' own user names and passwords, so the same central sign-in rules described in lesson 12 can apply. Server parameters, the engine's configuration settings, can be adjusted in the Azure portal or with the Azure CLI, and metrics such as processor use, connections and storage appear in Azure Monitor.

For administrators used to SQL Server, the main adjustment is learning the engine's own habits: PostgreSQL's VACUUM process that reclaims space from updated rows, MySQL's storage engine settings, and each engine's approach to query plans. The managed service automates the routine parts, but understanding the engine still matters when something is slow.

Choosing between SQL Server, PostgreSQL and MySQL

For a new application, which relational engine to use is often decided by the development team's skills, the frameworks they use and any software they depend on. For an existing application, the decision is usually already made, and the question is simply how to run that engine in Azure. The catalogue is built on PostgreSQL by its vendor, so moving it to Azure Database for PostgreSQL flexible server is the natural path: same engine, same queries, same extensions, with Microsoft handling backups, patching and high availability.

Ines asks whether it would be simpler to convert the catalogue to Azure SQL so that everything is on one engine. Elena's answer is practical: converting means rewriting queries, procedures and possibly parts of the vendor's application, testing everything again and losing the vendor's support. Unless there is a strong reason, a managed service for the engine the application already uses is the cheaper, safer choice. Many organizations run several engines for exactly this reason.

The plan is written down: in 2027 the catalogue moves to Azure Database for PostgreSQL flexible server in Canada Central, General Purpose tier, with zone-redundant high availability for the holiday season and a read replica for the website's search. The vendor will use Azure Database Migration Service or PostgreSQL's own backup and restore tools to move it. Walt, who has never touched PostgreSQL, signs up for a short course. "Same ideas," he says after the first evening. "Different spelling."