Faster Reads With Materialized Views
Published: 2021-08-13 11:14:30

<!-- # Materialized Views for Faster Reads -->

This Blog aims to give you a gist of Views in PostgreSQL and how to integrate in Django.

PostgreSQL Views are quite handy, think of Views as wrapping(abstracting) your complex queries and assigning a name to them.

They are several benifits of using Views, here are few

- Views hide the complexity

  if you have a query that requires aggregating or joining multiple tables and has complex logic, you can code all that logic into a view
  and then retreive data from it as if it were a normal table.

- Views could be used for security

  imagine you have a table with sensitive data in certain columns/rows and you want to give access to a user without exposing that data, views allow us to wrap only the required data and grant permission to that view instead of underlying table.

Like all the SQL databases postgres has two kinds of views

- Views
- Materialized Views

Here are few differences between Views and Materialized Views

| Views                                                                     | Materialized Views                                                                                                                                |
| ------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| Views are not stored in the disk.                                         | Materiliazed Views are stored on the disk.                                                                                                        |
| Whenever we read data from views, data is read from the underlying table. | Since materialized views has its own table, reading from materialized views will read from the table directly, but not from the underlying table. |

Let us try to implement Materialized Views. Consider a following scenario of a Blogging site. A typical Blogging site models would contain

- blogs table
- categories table
- tags table

where the relationship between them is quite straightforward, blogs has **foreign key** to categories and has a **many to many** relationship with tags. The equivalent Django Models would look like this.

```python

class Tags(models.Model):
    name = models.CharField(max_length=200, unique=True)

    class Meta:
        verbose_name = "Tag"
        verbose_name_plural = "Tags"
        db_table = "tags"

    def __str__(self):
        return self.name


class Category(models.Model):
    name = models.CharField(max_length=200, unique=True)

    class Meta:
        verbose_name = "Category"
        verbose_name_plural = "Categories"
        db_table = "categories"

    def __str__(self):
        return self.name


class Blog(models.Model):
    title = models.CharField(max_length=200, unique=True)
    content = models.TextField()
    created_at = models.DateTimeField(auto_now=True)
    is_published = models.BooleanField(default=True)
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)
    tags = models.ManyToManyField(Tags)

    class Meta:
        verbose_name = "Blog"
        verbose_name_plural = "Blogs"
        db_table = "blogs"

    def __str__(self):
        return self.title


```

Let's assume a situation where we have to query all the three tables and get data from all the three tables, for us to achieve this we'll have to join all the tables, the resulting django query would look like this

```python


queryset = Blog.objects.select_related("category").prefetch_related("tags").all()

# the resulting SQL query generated by Django ORM is

SELECT "blogs"."id",
    "blogs"."title",
    "blogs"."content",
    "blogs"."created_at",
    "blogs"."is_published",
    "blogs"."category_id",
    "categories"."id",
    "categories"."name"
FROM "blogs"
LEFT OUTER JOIN "categories"
ON ("blogs"."category_id" = "categories"."id")
LIMIT 21

SELECT ("blogs_tags"."blog_id") AS "_prefetch_related_val_blog_id",
       "tags"."id",
       "tags"."name"
  FROM "tags"
 INNER JOIN "blogs_tags"
    ON ("tags"."id" = "blogs_tags"."tags_id")
 WHERE "blogs_tags"."blog_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)

```

Notice that Django actually performs two queries here, we can do better by writing our own query :)

```sql

SELECT b.id,
       b.title AS blog_title,
       b.created_at AS blog_created_at,
       b.is_published AS blog_is_published,
       b.category_id AS blog_category_id,
       b.content AS blog_content,
       c.id AS category_id,
       c.name AS category_name,
       bt.blog_id AS blog_tag_blog_id,
       count(t.id) AS tag_count,
       jsonb_agg(jsonb_build_object('tag_id', t.id, 'tag_name', t.name)) AS tag_json
FROM blogs b
LEFT JOIN categories c ON b.category_id = c.id
LEFT JOIN blogs_tags bt ON b.id = bt.blog_id
LEFT JOIN tags t ON bt.tags_id = t.id
GROUP BY b.id,
         c.id,
         bt.blog_id
ORDER BY b.id;


```

As you can see the query is quite complex, we'll need to run the query every time when we need to access the data, thankfully views provide a convenient way to make it simple.

Let's see how we can create a Materialized Views

```sql

CREATE materialized VIEW mv_blogs_with_categories_and_tags_combined AS
SELECT b.id,
       b.title AS blog_title,
       b.content AS blog_content,
       b.created_at AS blog_created_at,
       b.is_published AS blog_is_published,
       c.id AS category_id,
       c.name AS category_name,
       count(t.id) AS tag_count,
       jsonb_agg(jsonb_build_object('tag_id', t.id, 'tag_name', t.name)) AS tags
FROM blogs b
LEFT JOIN categories c ON b.category_id = c.id
LEFT JOIN blogs_tags bt ON b.id = bt.blog_id
LEFT JOIN tags t ON bt.tags_id = t.id
GROUP BY b.id,
         c.id,
         bt.blog_id
ORDER BY b.id WITH NO DATA;


--- create a unique index, this is needed when we try to refresh the materialized view concurrently
CREATE UNIQUE INDEX ON mv_blogs_with_categories_and_tags_combined (id);

--- refresh the materialized view, to populate the data into it.
REFRESH MATERIALIZED VIEW mv_blogs_with_categories_and_tags_combined;

--- refresh the materialized view concurrently, without locking the table
--- when we refresh the view without concurrently , a lock is acquired on the table
--- to prevent this we use concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_blogs_with_categories_and_tags_combined;


```

In the below screenshots you can see the execution times, when querying the materialized view table vs performing the complex query.

![without_materialized_views](https://raw.githubusercontent.com/sreevardhanreddi/django-materialized-views/master/docs/complex_query.png)

```
Execution Time: 25.874 ms

```

![with_materialized_views](https://raw.githubusercontent.com/sreevardhanreddi/django-materialized-views/master/docs/materialized_views.png)

```
Execution Time: 0.155 ms

```

## Speed Up ~ 160x

All the code is available here in this repo https://github.com/sreevardhanreddi/django-materialized-views

---

## Integration With Django

Now that the SQL is done, let us go through integrating it with Django

Create an empty migrations file using this command

```shell
python manage.py makemigrations --name materialized_blogs pg_app --empty

```

This file that is generated is used to create the migrations using Django's **migrate** command and it like show below

```python

# Generated by Django 3.2.6 on 2021-08-12 06:59

from django.db import migrations
from django.db.migrations.operations.special import RunSQL


class Migration(migrations.Migration):

    dependencies = [
        ("pg_app", "0001_initial"),
    ]

    operations = [
        migrations.RunSQL(
            """
            CREATE materialized VIEW mv_blogs_with_categories_and_tags_combined AS
            SELECT b.id,
                b.title AS blog_title,
                b.content AS blog_content,
                b.created_at AS blog_created_at,
                b.is_published AS blog_is_published,
                c.id AS category_id,
                c.name AS category_name,
                count(t.id) AS tag_count,
                jsonb_agg(jsonb_build_object('tag_id', t.id, 'tag_name', t.name)) AS tags
            FROM blogs b
            LEFT JOIN categories c ON b.category_id = c.id
            LEFT JOIN blogs_tags bt ON b.id = bt.blog_id
            LEFT JOIN tags t ON bt.tags_id = t.id
            GROUP BY b.id,
                    c.id,
                    bt.blog_id
            ORDER BY b.id WITH NO DATA;
            """,
            """
                DROP MATERIALIZED VIEW IF EXISTS mv_blogs_with_categories_and_tags_combined;
            """,
        ),
        migrations.RunSQL(
            """
            REFRESH MATERIALIZED VIEW mv_blogs_with_categories_and_tags_combined;
            """,
            """
            REFRESH MATERIALIZED VIEW mv_blogs_with_categories_and_tags_combined;
            """,
        ),
        migrations.RunSQL(
            """
            CREATE UNIQUE INDEX idx_mv_blogs_with_categories_and_tags_combined_id ON mv_blogs_with_categories_and_tags_combined (id);
            """,
            """
            DROP INDEX IF EXISTS idx_mv_blogs_with_categories_and_tags_combined_id
            """,
        ),
    ]


# RunSQL allows you to write code for migrating forwards and backwards
# i.e, applying migrations and unapplying them. here
# the first string in RunSQL is the forward SQL, the second is the reverse SQL


```

Once you have added the SQL commands, run `python manage.py migrate` this will apply the migrations to the database.

After this create model, which reflects the materialized view

```python

class BlogsWithCategoriesAndTagsCombined(models.Model):
    id = models.IntegerField(primary_key=True)
    blog_title = models.CharField(max_length=200, unique=True)
    blog_content = models.TextField()
    blog_created_at = models.DateTimeField()
    blog_is_published = models.BooleanField()
    category_id = models.IntegerField()
    category_name = models.CharField(max_length=200)
    tag_count = models.IntegerField()
    tags = models.JSONField()

    class Meta:
        managed = False
        db_table = "mv_blogs_with_categories_and_tags_combined"

    def __str__(self):
        return self.blog_title

```

Notice the meta class here, it has `managed = False` , this tells django to not create the table for this model in the database.

Once this is done, querying it is as simple as

```python

queryset = BlogsWithCategoriesAndTagsCombined.objects.all()

```

Since materialized views rely on the tables for the data, when a table updates, we'll have to manually refresh the materialized views, this can be done inside a **cron** job using **django-crontab** or **celery**.


This was originally posted here https://sreevardhanreddi.github.io/