Why and When to Use Materialized Views in Django + PostgreSQL

Question

Part 1: Understanding Materialized Views

🧐 What Is a Materialized View?

A Materialized View in PostgreSQL is like a snapshot of a SQL query result that’s physically stored in the database. Unlike a regular view (which re-runs the query every time you access it), a materialized view stores the data and can be refreshed manually or on a schedule.

✅ Benefits of Using Materialized Views

Performance Boost: Materialized views avoid running complex or expensive queries repeatedly.
Precomputed Aggregates: Great for analytics (e.g., leaderboards, dashboards, reports).
Simplifies Code: Complex joins or calculated fields can be offloaded to the database.
Can Be Indexed: Unlike regular views, you can add indexes to speed up access.

🔁 Refresh Strategies

Manual Refresh:

REFRESH MATERIALIZED VIEW my_view;

Concurrent Refresh (allows reads during refresh but requires a unique index):

REFRESH MATERIALIZED VIEW CONCURRENTLY my_view;

How to Use Materialized Views in Django

Django doesn’t have native support for materialized views, but you can integrate them easily with:

Step 1: Create the View in SQL (via migration or manually)

CREATE MATERIALIZED VIEW user_leaderboard AS
SELECT user_id, COUNT(*) AS quizzes_played
FROM quiz_quizresult
GROUP BY user_id;

Step 2: Create a Django Model for the View

class UserLeaderboard(models.Model):
    user = models.ForeignKey(User, on_delete=models.DO_NOTHING)
    quizzes_played = models.IntegerField()

    class Meta:
        managed = False  # So Django doesn't try to create the table
        db_table = 'user_leaderboard'

Step 3: Refresh It from Django

from django.db import connection

def refresh_leaderboard():
    with connection.cursor() as cursor:
        cursor.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY user_leaderboard;")

KFSys · Accepted Answer

Part 2: Real Use Case – A Quiz App Leaderboard

Let’s say you have a quiz platform and want to show a daily leaderboard showing who took the most quizzes yesterday.

Step-by-Step Guide

1. Create the Materialized View

CREATE MATERIALIZED VIEW daily_leaderboard AS
SELECT
    user_id,
    COUNT(*) AS quizzes_played
FROM
    quiz_quizresult
WHERE
    DATE(created_at) = CURRENT_DATE - INTERVAL '1 day'
GROUP BY
    user_id;

You could also create this view via Django migration using RunSQL.

2. Add the Model in Django

class DailyLeaderboard(models.Model):
    user = models.ForeignKey(User, on_delete=models.DO_NOTHING)
    quizzes_played = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'daily_leaderboard'

3. Add an Admin View or API Endpoint

def leaderboard_view(request):
    leaderboard = DailyLeaderboard.objects.select_related('user').order_by('-quizzes_played')[:10]
    return render(request, 'leaderboard.html', {'leaderboard': leaderboard})

4. Automate the Refresh

Use a management command or Celery periodic task to refresh it nightly:


from django.core.management.base import BaseCommand
from django.db import connection

class Command(BaseCommand):
    def handle(self, *args, **kwargs):
        with connection.cursor() as cursor:
            cursor.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY daily_leaderboard;")

Then schedule it with cron, Celery Beat, or any task scheduler.

🧠 Final Thoughts

Materialized views are a powerful but underused tool in Django apps that rely on PostgreSQL. They’re especially helpful when:

You’re building dashboards or reports
You need quick read access to expensive queries
You can tolerate slightly stale data

By combining Django models with manually refreshed materialized views, you get the best of both worlds: fast reads and clean ORM integration.

alexdo · Answer

Heya, @kfsys

That is a great tutorial!

Also, for apps that rely heavily on PostgreSQL, tools like pg_cron or extensions like TimescaleDB’s continuous aggregates offer even more flexibility than materialized views alone — especially for time-series or event-driven data.

Regards