DEV Community: sreevardhanreddi

Faster Reads With Materialized Views

sreevardhanreddi — Fri, 13 Aug 2021 11:14:30 +0000

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/

Concurrency in Python

sreevardhanreddi — Sun, 03 May 2020 12:59:21 +0000

Concurrency in Python

Concurrency is the ability of different parts or units of a program, algorithm, or problem to be executed out-of-order or in a partial order, without affecting the final outcome. This allows for parallel execution of the concurrent units, which can significantly improve the overall speed of the execution.

Let's look at an example code, which is not concurrent.


import requests

urls = [
    "https://docs.python.org/3/",
    "https://github.com/trending/",
    "https://www.youtube.com/feed/trending/",
    "https://stackoverflow.com/questions/",
    "https://docs.djangoproject.com/en/3.0/",
    "https://requests.readthedocs.io/en/master/",
    "https://docs.aiohttp.org/en/stable/",
    "https://pypi.org/",
    "https://itsfoss.com/",
    "https://www.freecodecamp.org/news/",
    "https://gist.github.com/discover",
]

def get_req(url):
    res = requests.get(url)
    print(res.status_code, url)

def synchronous_function():
    for url in urls:
        get_req(url)

synchronous_function()

The above program sends a http get request to all the urls specified in the list of the urls synchronously.
what does synchronously mean here?
It means that each request is sent only after the previous http request completes i.e after the previous request returns a response. So for the current request to complete it has to wait for the previous request to complete. Hence the code is executed in a sequential manner. But what if a single url takes a lot of time to respond? the whole code execution is paused until we get a response. Can we make it better? Yes, we can !!.

Let us rewrite the code so that each url request is independent of another url request.

import threading

def get_req(url):
    res = requests.get(url)
    print(res.status_code, url)

def threaded_function():
    threads = []
    for url in urls:
        t = threading.Thread(target=get_req, args=(url,))
        t.start()
        threads.append(t)

    for thread in threads:
        thread.join()

threaded_function()

The above code uses python's threading module and uses threads. Threads are small set of individual programs that are executed independently, multiple threads can run at overlapping time intervals. Unlike other programming languages threads in python are different. They do not run parallelly, only one thread runs at a particular time due to GIL.

Let's look at the code which uses multithreading, the implementation goes like this :

for every url we create a new thread and make a new http get request.
the thread starts running i.e it is executed by python.

The implementation using a multithreading paradigm is better because all the urls run in different threads and don't have to wait for other urls to finish, each thread is now independent of other and all of them run concurrently in overlapping time.

Let's dig deeper on what overlapping time here means

assume there are 2 urls and 2 threads
we know that python can execute only one thread at a time
thread 1 sends http request and waits for a response for url 1
while thread 1 is waiting for the response thread 2 sends another http request and waits for the response, so when thread 2 is waiting we would have already got the response for thread 1 url and later we get the response for thread 2 url.

So by using multithreading, we can significantly reduce the waiting time for blocking operations such as Network and IO operations.

But there is another problem here, usually, multithreaded programs are hard to implement and design.

Python 3.5 introduced async/await. Asynchronous programming improves the performance for blocking operations by running concurrently. So when these features were introduced there weren't many libraries to leverage its power. Libraries like aiohttp, httpx etc support async/await.

Let's re-write the above program to use async/await


import asyncio

import httpx

async def async_get(url):
    client = httpx.AsyncClient()
    res = await client.get(url)
    print(res.status_code, url)
    await client.aclose()


async def async_func():
    async_funcs = [async_get(url) for url in urls]
    await asyncio.gather(*async_funcs)

asyncio.run(async_func())

The above version looks a lot cleaner than the threading version and performs as good as the threading version.

Here you can find the complete code with benchmarks


import asyncio
import threading
import time

import httpx
import requests


urls = [
    "https://docs.python.org/3/",
    "https://github.com/trending/",
    "https://www.youtube.com/feed/trending/",
    "https://stackoverflow.com/questions/",
    "https://docs.djangoproject.com/en/3.0/",
    "https://requests.readthedocs.io/en/master/",
    "https://docs.aiohttp.org/en/stable/",
    "https://pypi.org/",
    "https://itsfoss.com/",
    "https://www.freecodecamp.org/news/",
    "https://gist.github.com/discover",
]


def get_req(url):
    res = requests.get(url)
    # print(res.status_code, url)


def synchronous_function():
    for url in urls:
        get_req(url)


def threaded_function():
    threads = []
    for url in urls:
        t = threading.Thread(target=get_req, args=(url,))
        t.start()
        threads.append(t)

    for thread in threads:
        thread.join()


def time_taken_for(func):
    start_time = time.perf_counter()
    func()  # or func.__call__()
    end_time = time.perf_counter()
    total_time = end_time - start_time
    print(
        "total time taken for function {} : {:.3f} seconds".format(
            func.__name__, total_time
        ),
    )


async def async_get(url):
    client = httpx.AsyncClient()
    res = await client.get(url)
    # print(res.status_code, url)
    await client.aclose()


async def async_func():
    async_funcs = [async_get(url) for url in urls]
    await asyncio.gather(*async_funcs)


def time_taken_for_async_req():
    start_time = time.perf_counter()
    asyncio.run(async_func())
    end_time = time.perf_counter()
    total_time = end_time - start_time
    print("total time taken for async function : {:.3f} seconds".format(total_time),)


if __name__ == "__main__":
    time_taken_for(synchronous_function)
    time_taken_for(threaded_function)
    time_taken_for_async_req()



# total time taken for function synchronous_function : 10.250 seconds

# total time taken for function threaded_function : 1.369 seconds

# total time taken for async function : 1.315 seconds