Improve performance using a cache with Azure Database for PostgreSQL flexible server

When you build an application on Azure Database for PostgreSQL flexible server, adding a caching layer is one of the most effective ways to improve response times, reduce load on your database, and increase resilience. By serving frequently read data from an in-memory cache, your application sends fewer queries to PostgreSQL. That means lower CPU and IOPS consumption, so you can run on a smaller compute tier, scale reads without scaling up the server, and absorb traffic spikes. A cache can also add resilience. If PostgreSQL has a brief interruption, requests that hit data already in the cache can keep succeeding, so read paths stay available while the database recovers.

This article helps you decide when caching helps and which pattern fits your application. It then implements four caching patterns in Python (cache-aside, reference-data prefetching, write-through, and event-driven) by using Azure Managed Redis, the redis and psycopg libraries, and Microsoft Entra ID authentication.

When to add a cache

PostgreSQL already caches frequently accessed data pages in its buffer cache and also benefits from the operating system's file cache. These caches make repeated access faster, but they share the memory allocated to the database server with query execution and other processes. Their contents also need to warm again after some maintenance and failover operations.

You can get more database cache capacity by scaling to a compute option with more memory. You can also tune PostgreSQL memory settings, but allocating more memory to the buffer cache leaves less for query execution and the operating system. Test memory changes carefully to avoid out-of-memory conditions.

Azure Managed Redis complements these native caches. It stores selected application data and query results outside the database server, provides lower-latency access for time-sensitive read paths, and can keep cached reads available while PostgreSQL recovers or warms its cache. Use it when these benefits justify the added application logic and another service to operate. It doesn't replace PostgreSQL as the source of truth.

Adding an external cache like Azure Managed Redis helps most when your workload has these characteristics:

• Read-heavy access patterns. The same rows are read far more often than they change, such as product catalogs, user profiles, configuration data, or reference tables.
• Expensive or repeated queries. Aggregations, joins, or computed results that are costly to produce but stable over short time windows.
• Latency-sensitive endpoints. User-facing operations where an in-memory read (sub-millisecond) is preferable to a database round trip.
• Predictable spikes. Seasonal or event-driven traffic where caching absorbs load that would otherwise force you to scale compute.
• Maintenance and failover sensitivity. Read paths that need steady response times while a PostgreSQL instance recovers or warms its cache after a maintenance or failover operation.

Caching helps less for write-heavy workloads, data that must always be transactionally consistent, or queries that are already fast and rarely repeated.

Caching patterns

This article uses a retail storefront as a running example. Different parts of the app benefit from different caching patterns. The following sections implement the first four patterns in Python. The article describes session and state offload and multi-region caching patterns but doesn't provide code implementations for them.

Prerequisites

• An Azure account with an active subscription. Create an account for free.
• An Azure Database for PostgreSQL flexible server instance with Microsoft Entra authentication enabled. To create one, see Create an Azure Database for PostgreSQL flexible server.
• An Azure Managed Redis instance. To create one, see Create an Azure Managed Redis instance. Create it in the same region as your PostgreSQL server (and, for production, the same virtual network) to minimize latency.
• Data access for your developer or application identity on both services: a Microsoft Entra administrator or role on the PostgreSQL server, and a Redis access policy assignment on the cache. See Microsoft Entra authentication for Azure Database for PostgreSQL and Use Microsoft Entra ID for authentication with Azure Managed Redis.
• Python 3.10 or later.
• The Azure CLI. To install it, see How to install the Azure CLI.

Step 1: Install the client libraries

Install the Redis and PostgreSQL client libraries, along with the Azure Identity library for Microsoft Entra authentication.

pip install "redis>=5.0,<6.0" "psycopg[binary]>=3.1,<4.0" "azure-identity>=1.17,<2.0"

Step 2: Connect with Microsoft Entra ID

Use Microsoft Entra ID authentication instead of access keys. Microsoft Entra ID removes the need to store secrets in your application and lets you manage access centrally.

The following code creates a Redis client and a PostgreSQL connection, both authenticated with a managed identity or developer credential through DefaultAzureCredential. The example uses the cluster-aware RedisCluster client, which matches the OSS clustering policy this sample uses. If your cache uses the Enterprise clustering policy, use the standard redis.Redis client instead.

import os
import json
import redis
from redis.cluster import RedisCluster
import psycopg
from azure.identity import DefaultAzureCredential

REDIS_HOST = os.environ["REDIS_HOST"]  # for example, mycache.eastus.redis.azure.net
REDIS_PORT = 10000
PG_HOST = os.environ["PG_HOST"]        # for example, myserver.postgres.database.azure.com
PG_DATABASE = os.environ["PG_DATABASE"]

credential = DefaultAzureCredential()

# Acquire a token for Azure Managed Redis and use it as the password.
redis_token = credential.get_token("https://redis.azure.com/.default")

# The username is the object ID of the Microsoft Entra identity.
redis_client = RedisCluster(
    host=REDIS_HOST,
    port=REDIS_PORT,
    ssl=True,
    ssl_check_hostname=False,  # cluster nodes are reached by IP; the certificate chain is still validated
    username=os.environ["REDIS_USER_OBJECT_ID"],
    password=redis_token.token,
    decode_responses=True,
)

# Acquire a token for Azure Database for PostgreSQL and use it as the password.
pg_token = credential.get_token("https://ossrdbms-aad.database.windows.net/.default")

pg_conn = psycopg.connect(
    host=PG_HOST,
    dbname=PG_DATABASE,
    user=os.environ["PG_USER"],
    password=pg_token.token,
    sslmode="require",
)

Step 3: Cache-aside

Cache-aside is the most common pattern and is used for product reads in this sample. The application checks Redis first, and on a miss it queries PostgreSQL and populates the cache with a time-to-live (TTL). A few popular products drive most reads, so the hit rate is high.

Because most reads are served from memory, cache-aside takes sustained read load off PostgreSQL. This reduction means fewer connections, less buffer-cache churn, and lower CPU and IOPS. You can absorb read spikes without scaling up the server or adding read replicas. You query PostgreSQL only on a miss (first access, or after the TTL expires). See Find what to cache to identify the queries worth caching.

CACHE_TTL_SECONDS = 300  # 5 minutes

def get_product(product_id: int) -> dict | None:
    cache_key = f"product:{product_id}"

    # 1. Try the cache first.
    cached = redis_client.get(cache_key)
    if cached is not None:
        return json.loads(cached)

    # 2. On a miss, read from PostgreSQL.
    with pg_conn.cursor() as cursor:
        cursor.execute("SELECT id, name, price FROM products WHERE id = %s", (product_id,))
        row = cursor.fetchone()
    if row is None:
        return None

    product = {"id": row[0], "name": row[1], "price": float(row[2])}

    # 3. Populate the cache with a TTL, then return.
    redis_client.set(cache_key, json.dumps(product), ex=CACHE_TTL_SECONDS)
    return product

Step 4: Reference-data prefetching

Stable data that's read constantly but changes rarely (for example, categories, brands, or shipping configuration) doesn't need to wait for a cache miss. Load it into the cache up front and refresh it when the source changes. In a PostgreSQL schema, these are typically the small lookup and dimension tables that get joined into many queries. Serving them from memory removes a large volume of repeated joins and lookups from the database. Unlike cache-aside, there's no per-request miss and no TTL race. You refresh on change, so reads are always warm.

def prefetch_categories() -> None:
    with pg_conn.cursor() as cursor:
        cursor.execute("SELECT id, name FROM categories ORDER BY name")
        categories = [{"id": r[0], "name": r[1]} for r in cursor.fetchall()]
    redis_client.set("ref:categories", json.dumps(categories))  # no TTL; refreshed on change

def get_categories() -> list[dict]:
    cached = redis_client.get("ref:categories")
    return json.loads(cached) if cached else []

Step 5: Write-through

When a change must be visible immediately, write PostgreSQL and the cache in the same operation instead of waiting for a TTL to expire or invalidating the key. For example, this pattern is used for updating pricing information in the sample. PostgreSQL stays the source of truth. The change commits there first, then the cache is refreshed, so a read after the write returns the new value.

When writing to two systems that don't share a transaction, the database commit can succeed while the cache refresh fails, and there's no simple fix. The following snippet shows the happy path and leaves failure handling out. In production you decide how to reconcile a failed refresh, such as retrying it when the failure looks transient, or invalidating the key so the next read reloads from PostgreSQL. Either way, PostgreSQL holds the correct value, so a stale or missing cache entry is always recoverable. When the cache update must reliably land, drive it from the database's change stream instead (see event-driven invalidation).

def update_price(product_id: int, new_price: float) -> None:
    # 1. Write to PostgreSQL, the source of truth.
    with pg_conn.cursor() as cursor:
        cursor.execute("UPDATE products SET price = %s WHERE id = %s", (new_price, product_id))
    pg_conn.commit()

    # 2. Refresh the cached entry so reads see the new price right away.
    with pg_conn.cursor() as cursor:
        cursor.execute("SELECT id, name, price FROM products WHERE id = %s", (product_id,))
        row = cursor.fetchone()
    if row is not None:
        product = {"id": row[0], "name": row[1], "price": float(row[2])}
        redis_client.set(f"product:{product_id}", json.dumps(product), ex=CACHE_TTL_SECONDS)

Step 6: Event-driven invalidation

Event-driven invalidation keeps the cache consistent with the database by reacting to data-change events. It updates or invalidates entries as they change instead of expiring them on a timer. Writers append events to a durable Redis stream, an append-only log, and one or more consumers read those events and update the cache. Because the stream persists, events survive a consumer restart. A consumer group delivers each event to a single worker, tracks acknowledgments so nothing is lost or processed twice, and lets you scale processing across workers.

Use this pattern when a cached value is derived from data that changes elsewhere (a status, a projection, or an aggregate), where a TTL would either serve stale data or force constant recomputation. In the storefront, this pattern drives order status through fulfillment. Placing an order writes the order to PostgreSQL, caches its initial status, and appends a placed event to the stream:

ORDER_STREAM = "orders:events"

def place_order(product_id: int, quantity: int) -> int:
    with pg_conn.cursor() as cursor:
        cursor.execute(
            "INSERT INTO orders (product_id, quantity, status) VALUES (%s, %s, 'placed') RETURNING id",
            (product_id, quantity),
        )
        order_id = cursor.fetchone()[0]
    pg_conn.commit()

    redis_client.set(f"order:{order_id}:status", "placed", ex=86400)
    redis_client.xadd(ORDER_STREAM, {"order_id": order_id, "status": "placed"}, maxlen=10000, approximate=True)
    return order_id

A fulfillment worker runs a consumer group: it reads new events, advances each order in PostgreSQL, refreshes the cached order:{id}:status projection, and acknowledges the event. The order page reads that projection, so status checks stay fast and never touch the database. The value stays correct because events keep it current.

GROUP = "fulfillment"

def process_orders() -> None:
    try:
        redis_client.xgroup_create(ORDER_STREAM, GROUP, id="0", mkstream=True)
    except redis.exceptions.ResponseError:
        pass  # group already exists

    while True:
        events = redis_client.xreadgroup(GROUP, "worker-1", {ORDER_STREAM: ">"}, count=10, block=5000)
        for _stream, entries in events or []:
            for event_id, fields in entries:
                order_id = int(fields["order_id"])
                with pg_conn.cursor() as cursor:
                    cursor.execute("UPDATE orders SET status = 'shipped' WHERE id = %s", (order_id,))
                pg_conn.commit()
                redis_client.set(f"order:{order_id}:status", "shipped", ex=86400)
                redis_client.xack(ORDER_STREAM, GROUP, event_id)

The event source in this sample is the application, which writes PostgreSQL and appends the event in the same path. PostgreSQL can also emit changes itself: LISTEN/NOTIFY for lightweight notifications, or logical decoding (change data capture) for a durable, row-level change stream. Driving the cache from PostgreSQL's own change stream means it reacts to every committed change, even writes that bypass the application.

Best practices

Follow these practices to keep your cache correct, efficient, and cost-effective.

• Set a TTL wherever staleness is a risk. A TTL limits staleness if invalidation fails. Match the TTL to the maximum staleness the application can accept. Use a long TTL or no TTL for reference data only when reliable invalidation and refresh processes exist.
• Use a consistent key naming scheme. Namespace keys by service, entity, and identifier, such as product:42 or user:1001:profile. Add a version when the key format or value schema can change.
• Cache the right granularity. Cache individual entities or small result sets when they're reused often and are easy to invalidate. Don't cache data that has little reuse. Excessive caching wastes memory and can reduce the hit rate.
• Handle cache misses and outages gracefully. Treat the cache as an optimization, not a source of truth. If Redis is unavailable, use a bounded fallback to PostgreSQL. Add timeouts, circuit breakers, backoff, and request limits to protect PostgreSQL. If PostgreSQL has a brief interruption, you can keep serving reads for data already in the cache while writes wait for the database to recover.
• Prevent cache stampedes. When a popular key expires, many requests can hit the database at once. Use TTL jitter, request coalescing, stale-while-revalidate, or a short distributed lock to let one request repopulate the entry.
• Right-size the cache. Monitor the hit rate, memory usage, eviction rate, expiration rate, latency, hot keys, and key cardinality. A low hit rate can indicate a cache that is too small, excessive evictions, poor key selection, or a poor access pattern. For sizing guidance, see Azure Managed Redis tier selection guidance.
• Choose an eviction policy that fits your keys. For a cache-only database, start with allkeys-lru or allkeys-lfu. Use a volatile-* policy only when the same database contains expiring cache keys and protected non-expiring keys. A volatile policy can stop evicting when no keys have a TTL. Separate cache data from protected data when possible.
• Serialize efficiently. JSON is readable and portable. For high-throughput paths, test a compact binary format to reduce memory and network overhead. Benchmark memory, CPU, latency, schema evolution, and debugging impact before you change the format.

Find what to cache

The most effective cache targets are the queries your application runs most often against the data that changes least. Instead of guessing which queries fit this description, compare the historical and current views in the query telemetry that Azure Database for PostgreSQL can collect when you enable the relevant features:

• Query Store persists query execution statistics for historical analysis. Use call counts and total and mean execution time to find queries that consistently dominate database load over longer periods. See Monitor performance with Query Store.
• Query Performance Insight visualizes Query Store data in the Azure portal, so you can spot frequent and resource-intensive queries and compare their behavior over time. See Query Performance Insight.
• pg_stat_statements exposes cumulative per-statement statistics inside the database for a direct view of the current observation window. Its statistics can be reset, so use Query Store when you need retained history across observation windows.

Use both views before you choose a cache candidate. A short-term spike might not represent the workload's normal behavior, while a historical average can hide a current regression. Prioritize queries that are frequent, expensive, and stable, meaning high call count, high total execution time, and results that don't change on every request. Those queries deliver the highest cache hit rate and the largest drop in database load. A query that runs constantly but returns the same rows for minutes, such as a product listing, a category tree, or a pricing table, is an ideal candidate.

Related content

• What is Azure Managed Redis?
• Caching guidance
• Plan Azure Database for PostgreSQL flexible server deployments for operational performance