Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 57 ยท Databases and Data

PostgreSQL, MySQL and Redis on Azure

Run PostgreSQL, MySQL and Redis on Azure: Flexible Server options, high availability, backups, and cache-aside with TTL and eviction explained.

Many teams already know PostgreSQL, MySQL or Redis, and do not want to rewrite their applications to use a proprietary database. Azure runs these open-source engines as managed services: Azure installs and patches the software, takes backups, monitors the servers and offers high availability, while you keep the same drivers, SQL dialect and tools you use anywhere else.

This lesson covers the two relational services (Azure Database for PostgreSQL and for MySQL, both in the Flexible Server deployment model) and the Redis cache service, with a focus on the decisions you make when you create them. The real services need an Azure account, so commands and code are read-only. The runnable stand-ins are Node.js programs that model the cache-aside pattern, time-to-live (TTL) and eviction, which are the ideas that matter most for a cache.

Flexible Server for PostgreSQL and MySQL

Both services use the same shape. You create a server inside a resource group (see Resource Groups and Azure Resource Manager), choose a compute tier, a storage size and a few options, and then connect with a normal connection string. A server holds one or more databases.

The main decisions:

Decision Options
Compute tier Burstable for development and light workloads, General Purpose for most production, Memory Optimized for large working sets
Version The engine major version, for example a recent PostgreSQL or MySQL release. Pick a supported current one; old versions are retired on a schedule
Storage Sized in GiB; it can grow, and an autogrow option avoids running out of space
High availability Off, same-zone or zone-redundant
Networking Public access with firewall rules, or private access by placing the server in a virtual network
Authentication Database password, Microsoft Entra authentication, or both

A read-only example for PostgreSQL (the names are invented, and the exact parameters and defaults vary between CLI versions, so check az postgres flexible-server create --help):

Terminal
az postgres flexible-server create \
  --resource-group data-rg \
  --name shop-pg-demo \
  --location westeurope \
  --tier Burstable \
  --sku-name Standard_B1ms \
  --storage-size 32 \
  --version 16 \
  --admin-user shopadmin \
  --admin-password "$DB_PASSWORD"

The password comes from an environment variable, never from a script committed to a repository. Better still, enable Entra authentication and sign in with a managed identity (see Managed Identities and Service Principals) so that no database password exists in your application settings.

Networking and security

A new server should not be open to the internet. The two patterns are:

  1. Public access with firewall rules. The server has a public name but only allows listed IP addresses (and, if you choose, other Azure services). Quick for development, easy to get wrong.
  2. Private access. The server is injected into a subnet of your virtual network (see Virtual Networks and Subnets), or reached through a private endpoint. Nothing on the public internet can talk to it.

TLS is enforced by default for connections, and data is encrypted at rest. Always keep the require-TLS setting on and verify the server certificate in your client.

High availability and backups

High availability (HA) keeps a standby copy of the server that takes over automatically if the primary fails. With zone-redundant HA the standby is in a different availability zone, so even the loss of a whole datacenter in the region is tolerated; same-zone HA protects against server and host failures only. Failover takes tens of seconds to a couple of minutes, during which connections drop, so applications need retry logic. HA costs roughly double the compute, so use it for production only.

Backups are automatic. Azure takes snapshots of the data and keeps the transaction logs, which lets you perform point-in-time restore to any moment inside the retention period. Retention is configurable in days (the default is a short period and the maximum is about a month; check the current limits). A restore always creates a new server; it does not overwrite the old one. HA is not a backup: if someone drops a table, the standby faithfully drops it too, and only a restore brings it back.

Two more features are worth knowing:

  • Read replicas copy data asynchronously to other servers, so reporting queries do not load the primary. They can be in another region, and they lag slightly behind.
  • Stop and start lets you pause a development server to save compute cost, within limits (the platform restarts a long-stopped server automatically).
flowchart LR
    App["Application"] --> P["Primary server<br/>Zone 1"]
    P -- "synchronous copy" --> S["Standby<br/>Zone 2"]
    P -- "asynchronous copy" --> R["Read replica"]
    P -. "logs + snapshots" .-> B["Backup storage"]

HA replicates synchronously for fast failover, a read replica lags a little, and backups let you rewind time.

Connecting from code

A Python snippet with the psycopg driver, read-only (needs a server). Notice sslmode:

Python
import os
import psycopg

conn = psycopg.connect(
    host="shop-pg-demo.postgres.database.azure.com",
    dbname="shop",
    user="shopadmin",
    password=os.environ["DB_PASSWORD"],
    sslmode="require",
)
with conn.cursor() as cur:
    cur.execute("SELECT count(*) FROM orders WHERE status = %s", ("paid",))
    print(cur.fetchone())

The placeholder %s makes the query parameterized. MySQL works the same way with its own driver. For general SQL practice use the in-browser runners in the SQL tutorial; the managed servers add operations, not new SQL.

Azure Cache for Redis

Redis is an in-memory data store. A cache sits in front of a slower database and answers hot reads in well under a millisecond. Microsoft's Redis offering has been changing: Azure Cache for Redis is being succeeded by Azure Managed Redis, so check the current documentation before you pick a tier for a new project. The ideas below apply to either.

When you create a cache you choose a size (memory), a tier, optional clustering for scale and optional replication for availability, and network access (public with firewall, or private endpoint). Clients connect over TLS with a hostname and either an access key or Entra authentication.

The cache-aside pattern

The most common pattern is cache-aside (or lazy loading). The application, not the cache, owns the logic:

  1. Look in the cache for the key.
  2. On a hit, return the value.
  3. On a miss, read from the database, store the result in the cache with a TTL, and return it.
  4. When the data changes, update the database and delete (or update) the cache entry.
JavaScript
// Read-only: needs a Redis cache and a client library.
async function getProduct(id) {
  const key = `product:${id}`;
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const product = await db.query("SELECT * FROM products WHERE id = $1", [id]);
  await redis.set(key, JSON.stringify(product), { EX: 300 }); // expire in 300 s
  return product;
}

async function updateProduct(id, fields) {
  await db.query("UPDATE products SET name = $2 WHERE id = $1", [id, fields.name]);
  await redis.del(`product:${id}`); // next read reloads fresh data
}

TTL: the safety net

A time to live makes every entry expire on its own. TTL protects you when an invalidation is missed: stale data lives at most TTL seconds. Short TTLs mean fresher data but more database load; long TTLs reduce load but allow staleness. Pick TTL from the question "how wrong can this value be, and for how long?" Add a little random jitter so that many keys do not expire in the same instant.

A cache-aside stand-in

The stand-in below uses a fake clock so the output is deterministic. A Map plays the cache, a function plays the database and counts how often it is called. It is a model of the pattern, not Redis.

Node.js Cache-aside with a TTL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Look at the two p2 reads: the database changed to 7 but the cache still says 6 until the TTL ends. That is the staleness window you accept in exchange for fewer database reads.

Eviction: what happens when memory is full

A cache has fixed memory, and it will fill up. The eviction policy decides what happens next. Common Redis policies include noeviction (refuse new writes), allkeys-lru (remove the least recently used key of any kind), volatile-lru (only among keys that have a TTL) and allkeys-lfu (least frequently used). The default in managed Redis offerings is often a volatile policy, but verify it on your instance: with volatile-lru, keys without a TTL are never evicted, so a cache full of TTL-less keys can reject writes. Rules of thumb: set a TTL on cache keys, monitor evictions and memory use, and do not use a cache as the only copy of data.

Node.js Least-recently-used eviction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Choosing between them

  • Use PostgreSQL or MySQL for relational data with transactions. Prefer the engine your team and tools already know. PostgreSQL has a richer feature set (extensions, JSON, advanced indexing); MySQL is common with PHP and many CMS products (see the MySQL tutorial).
  • Use Redis for caching, sessions, rate counters and leaderboards: data you can rebuild.
  • Compare with Azure SQL Database when you need SQL Server features and Azure Cosmos DB for globally distributed documents with predictable latency.

Common mistakes

  • Leaving public access open to all addresses "just for testing". Use firewall rules or private access from day one.
  • Believing HA is a backup. Practice a point-in-time restore before you need one.
  • Caching without a TTL, or caching per-user data under a shared key.
  • No retry logic for failover or connection resets.
  • Treating the cache as the source of truth. If an empty cache would break the app, it is a database, not a cache.

Azure Database for MySQL: key terms and migration

Azure Database for MySQL is the managed MySQL service (the Flexible Server deployment is the current one). Terms you will meet:

  • Server: the managed MySQL instance; it holds your databases.
  • Compute tier: Burstable, General Purpose or Business Critical, which set vCores and memory.
  • Storage and IOPS: storage grows on demand, and IOPS can scale with it.
  • Backups and point-in-time restore: automatic backups let you restore to any moment in the retention window as a new server.
  • Read replicas: asynchronous copies for scaling read-heavy workloads.
  • High availability: a standby in the same or another zone with automatic failover.
  • Intelligent insights: Query Performance Insight and slow-query logs show which statements cost the most.
  • Networking: public access with firewall rules, or private access inside a virtual network.

Creating a server in the portal ends with a Review + create step that shows the estimated cost before you commit. To move an existing MySQL database to Azure, dump the data with mysqldump, or use the Azure Database Migration Service for an online migration (see Moving existing systems: Azure Migrate and database migration): check compatibility, copy the schema and data, switch the application's connection string, then compare row counts before shutting down the old server.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does zone-redundant high availability protect you from?

    A standby in a second zone takes over automatically. It replicates your mistakes too, so it is not a backup.
  2. 2. A point-in-time restore on a Flexible Server...

    Restores always produce a new server. You then repoint the application or copy the needed data back.
  3. 3. In the cache-aside pattern, what happens on a cache miss?

    In cache-aside the application owns the loading logic; the cache is a passive store.
  4. 4. Why set a TTL on cached entries even when you also delete them on update?

    TTL is a safety net: even a forgotten invalidation can only cause staleness up to the TTL.
  5. 5. With the volatile-lru policy and a full cache, which keys can be evicted?

    Volatile policies consider only keys with an expiry. Keys with no TTL are never evicted, so writes can fail if they fill memory.

Summary

  • Flexible Server runs PostgreSQL and MySQL as managed services: you choose tier, storage, networking, HA and authentication.
  • Prefer private access or tight firewall rules, enforced TLS and Entra authentication over shared passwords.
  • HA keeps a standby for fast failover; backups with point-in-time restore protect against mistakes and always restore to a new server; read replicas offload reads.
  • A cache holds rebuildable data: use cache-aside, set a TTL, invalidate on write and know your eviction policy.
  • Check the current status of Azure Cache for Redis versus Azure Managed Redis before choosing a tier.

Next: Data and Analytics Services.