Imagine you’re the CTO of a rapidly growing e-commerce company. Your platform has gained traction, and you’re now processing thousands of orders daily. As the volume of data grows, so does the complexity of your analytics and reporting. Your team is constantly running complex, time-consuming queries to produce insights and reports for various stakeholders. Page-loading times increase due to the growing amount of data in the database.

Now imagine a world where your team could get the same insights more efficiently. That’s where materialized views in PostgreSQL come in. Materialized views are a valuable feature that can help speed up complex queries by caching the results of these queries and refreshing them on a schedule or when triggered by an event.

While materialized views are powerful, they were not as straightforward to use in Django applications due to the lack of a dedicated library for defining and managing them, including handling migrations. That’s where our story begins.

A Django Package to Manage Materialized Views

As the CTO of a company that relies heavily on open-source software, I’ve always been keen on giving back to the community. So when our developer Farruh Sheripov faced the challenge of not having a Django library for materialized views, he decided to create one.

Farruh’s library, Django-Materialized-View, aims to make it easier for Django developers working with PostgreSQL to create and manage materialized views. It simplifies the process and includes support for migrations.

Here’s a quick rundown of the key features of Django-Materialized-Views:

1. Easily define materialized views: Just like you define models in Django, you can now define materialized views. The library provides a clean, intuitive syntax for creating materialized views and defining their SQL queries.

2. Manage migrations: One of the challenges with materialized views is handling migrations. Django-Materialized-Views integrates with Django’s migration system, allowing you to manage materialized view migrations just like you would with regular models.

3. Simple refreshing: The library offers options for easy refreshing materialized views, ensuring that your views are up-to-date. Refreshes are logged in a dedicated table.

4. Support for indexing: To further improve query performance, Django-Materialized-Views supports indexing on materialized views. This enables you to create indices on your materialized views to speed up specific queries.

A Practical Example

To illustrate the power of materialized views and Django-Materialized-Views, let’s dive into a practical example. I have prepared a sample project that demonstrates how to use materialized views in a Django application. The project downloads 6 million movie titles from IMDB, imports them into a PostgreSQL database, and displays the average duration of movies over time:

Press enter or click to view image in full size

Average duration of movies in minutes by year

In this example, we use a materialized view to aggregate the average duration of movies per year.

Let’s first examine how we define the materialized view:

class YearlyRuntimeModel(MaterializedViewModel):    create_pkey_index = True  # if you need add unique field as a primary key and create indexes    class Meta:        managed = False    # if create_pkey_index=True you must add argument primary_key=True    year = models.IntegerField(primary_key=True)    average_runtime = models.IntegerField()    @staticmethod    def get_query_from_queryset():        # define this method only in case use queryset as a query for materialized view.        # Method must return Queryset        return Movie.objects.values('year').annotate(average_runtime=Avg('runtime_minutes'))

There are three key differences from a standard Django model:

• It extends MaterializedViewModel.

• It provides a static method get_query_from_queryset() which describes the query used to populate the materialized view.

• It includes managed = False in its configuration.

To set up the application for managing materialized views, you’ll need to run the following command once: ./manage.py migrate

Next, you’ll use the new migrate_with_views command instead of the default migrate command:

python manage.py migrate_with_views

This command automatically handles materialized view models, creating, updating, or deleting the corresponding view in your database as needed.

Refreshing a materialized view is as simple as calling the following statement:

YearlyRuntimeModel.refresh()

Try it now

In conclusion, Django-Materialized-View seeks to make it more convenient for Django developers to harness the benefits of PostgreSQL materialized views. By contributing to the open-source community, we hope to help others take advantage of this useful feature and improve the efficiency and performance of their applications.

Find Django-Materialized-Views on PyPi and get started today: https://pypi.org/project/django-materialized-view/