Database Ghosts Tables

2023-12-29

database , django , performance

Cover image

Database views

Database views is a virtual tables or we can say computed tables, those are very useful for performance / simplicity / security

performance: virtual tables can have indexes
simplicity: avoid complex joins and store flatten data in one table
security: show only certain columns, imagine you have public API and you want to hide this sensitive data that got accessed instead of limiting the data on every endpoint just create a virtual table that show only wanted data

it has some limitation

you can only perform read operation (no create/update/delete)

NOTE its not supported in MySQL

Django HowTo

1. Create the materialized table using SQL

Django is database agnostic and this concept not exist in all DBMS, so you want to create it using SQL

CREATE MATERIALIZED VIEW product_sales AS
SELECT
    product_id,
    SUM(product.price) as total_sales
FROM
    order_product
JOIN product ON order_product.product_id = product.id
GROUP BY product_id;

add index

CREATE INDEX product_sales_idx ON product_sales (product_id);

refresh the materialized, This must done periodically

REFRESH MATERIALIZED VIEW product_sales;

2. Access table from Django ORM

from django.db import models 

class ProductSales(models.Model):
    product_id = models.IntegerField()
    total_sales = models.DecimalField(max_digits=10, decimal_places=2) 
    class Meta:
        managed = False
        db_table = 'product_sales'

to query this materialized

top_selling_products = ProductSales.objects.all()

for SQL part you can make it from inside django

first create empty migration

python manage.py makemigrations <app-name> --empty

then write code like this

# In your Django app, create a new migration file
from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        # dependencies if any
    ]

    operations = [
        migrations.RunSQL(
            """
            CREATE MATERIALIZED VIEW my_materialized_view AS
            SELECT ...
            """,
            """
            DROP MATERIALIZED VIEW my_materialized_view
            """
        ),
    ]