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.
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
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 :)
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
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.
Execution Time: 25.874 ms
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
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
# 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
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
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/
Top comments (0)