Materialized views vs Django cache for slow queries

The instinctive response to a slow reporting endpoint is often cache. A @cache_page, a cache.set(), and the problem seems to vanish until the next expiration. This approach has a structural limitation that PostgreSQL materialized views solve at the root.

The problem with cache on analytics endpoints

Django cache stores the result of a Python view. The expensive SQL query still runs on every cache expiration. For a report built on multiple JOINs and aggregations, this means the first user after each cache miss waits several seconds.

from django.views.decorators.cache import cache_page
from django.db.models import Count, Q, Sum

@cache_page(60 * 15)
def billing_dashboard(request):
    # This query runs on every cache expiration
    data = (
        Plan.objects
        .annotate(
            active_subscriptions=Count(
                "subscriptions", filter=Q(subscriptions__status="active")
            ),
            monthly_revenue=Sum(
                "subscriptions__monthly_price",
                filter=Q(subscriptions__status="active"),
            ),
        )
        .order_by("-monthly_revenue")
    )
    return JsonResponse(list(data.values()), safe=False)

Two more limitations compound this. First, cache stores a fixed result: no dynamic filtering is possible (?period=30d, ?region=eu). Each parameter combination generates a distinct cache miss with a full recalculation. Second, a Redis flush causes an immediate recalculation across all keys at once, which can saturate the database at the worst possible moment.

What a materialized view does instead

A PostgreSQL materialized view moves the expensive computation outside the request/response cycle. The result is physically stored on disk, indexable, and available in a few milliseconds.

Consider a SaaS application with three tables: plans, subscriptions, and usage_records. The calculation of per-plan stats (active subscriptions, API calls, revenue) happens once at refresh time, not on every request.

-- Materialized view: pre-computed result stored on disk
CREATE MATERIALIZED VIEW plan_usage_mv AS
SELECT
    p.id AS plan_id,
    p.name AS plan_name,
    COUNT(s.id) AS active_subscriptions,
    SUM(ur.api_calls) AS total_api_calls,
    SUM(s.monthly_price) AS monthly_revenue
FROM plans p
JOIN subscriptions s ON s.plan_id = p.id AND s.status = 'active'
JOIN usage_records ur ON ur.subscription_id = s.id
GROUP BY p.id, p.name
WITH DATA;

-- Unique index required for REFRESH CONCURRENTLY
CREATE UNIQUE INDEX ON plan_usage_mv (plan_id);

The difference from a regular SQL view is fundamental: a plain view re-executes the query on every SELECT. The materialized view responds with data that is already computed.

Integrating into Django via a migration

The view is managed inside a migration with RunSQL. It lives in the migration history the same way a table does.

from django.db import migrations

CREATE_VIEW = """
    CREATE MATERIALIZED VIEW plan_usage_mv AS
    SELECT
        p.id AS plan_id,
        p.name AS plan_name,
        COUNT(s.id) AS active_subscriptions,
        SUM(ur.api_calls) AS total_api_calls,
        SUM(s.monthly_price) AS monthly_revenue
    FROM plans p
    JOIN subscriptions s ON s.plan_id = p.id AND s.status = 'active'
    JOIN usage_records ur ON ur.subscription_id = s.id
    GROUP BY p.id, p.name
    WITH DATA;

    CREATE UNIQUE INDEX ON plan_usage_mv (plan_id);
"""

DROP_VIEW = "DROP MATERIALIZED VIEW IF EXISTS plan_usage_mv;"


class Migration(migrations.Migration):
    dependencies = [("billing", "0005_usage_record")]

    operations = [
        migrations.RunSQL(sql=CREATE_VIEW, reverse_sql=DROP_VIEW)
    ]

The Django model points to the view with managed = False. Django does not touch the structure, it only reads from it.

class PlanUsageSummary(models.Model):
    plan = models.OneToOneField(
        "Plan",
        on_delete=models.DO_NOTHING,
        primary_key=True,
        db_column="plan_id",
    )
    plan_name = models.CharField(max_length=100)
    active_subscriptions = models.IntegerField()
    total_api_calls = models.BigIntegerField()
    monthly_revenue = models.DecimalField(max_digits=14, decimal_places=2)

    class Meta:
        managed = False
        db_table = "plan_usage_mv"

The view is queried like any Django table:

stats = (
    PlanUsageSummary.objects
    .order_by("-monthly_revenue")
)

Scheduling the refresh with Celery

The view does not update itself. REFRESH MATERIALIZED VIEW CONCURRENTLY recomputes without blocking ongoing reads, provided a unique index exists.

from celery import shared_task
from django.db import connection


@shared_task
def refresh_plan_usage():
    with connection.cursor() as cursor:
        cursor.execute(
            "REFRESH MATERIALIZED VIEW CONCURRENTLY plan_usage_mv"
        )

The frequency depends on context: every hour for an operational report, once a night for a management dashboard. The key point is that the refresh is decoupled from user requests.

Both together in production

The two tools are complementary, not competing. The materialized view ensures data is already computed. Django cache avoids even the SELECT on the view when parameters are identical.

Plans / Subscriptions / UsageRecords
    │
    ▼  REFRESH CONCURRENTLY (Celery Beat, decoupled)
Materialized view  ← pre-computed aggregates, indexed
    │
    ▼  Simple SELECT (< 50ms)
Django cache       ← short-circuits the SELECT for repeated queries
    │
    ▼
HTTP response

A cache miss on this architecture takes 50ms instead of several seconds. That is the difference between a problem solved and a problem hidden.

When to use which

For concrete benchmarks on this pattern with before/after measurements, this article covers a real-world case: Django materialized views PostgreSQL real benchmarks.