🗄models

Best practice of models

Moving to Django 3.0's Field.choices Enumeration Types:

Old Way

from django.db import models

class Book(models.Model):
    UNPUBLISHED = 'UN'
    PUBLISHED = 'PB'
    STATUS_CHOICES = [
        (UNPUBLISHED, 'Unpublished'),
        (PUBLISHED, 'Published'),
    ]
    status = models.CharField(
        max_length=2,
        choices=STATUS_CHOICES,
        default=UNPUBLISHED,
    )


# QuerySet filters 
# unpublished_books = Book.objects.filter(status=Book.UNPUBLISHED)

intermediate

New way

Queryset Results as namedtuple

User.objects.values_list(
    'first_name',
    'last_name',
)[0]

('Santosh', 'Purbey')

user_names = User.objects.values_list(
    'first_name',
    'last_name',
    named=True,
)

user_names[0]

Row(first_name='Santosh', last_name='Purbey')

user_names[0].first_name
'Santosh'

user_names[0].last_name
'Purbey'

Custom Funtions

# common/db.py

from django.db.models import Func

class Epoch(Func):
    function = 'EXTRACT'
    template = "%(function)s('epoch' from %(expressions)s)"
    

from django.db.models import Avg, StdDev, F
from common.db import Epoch

Report.objects.aggregate(
    avg_duration=Avg('duration'),
    std_duration=StdDev(Epoch(F('duration'))),
)
    

Statement Timeout

# wsgi.py

from django.db.backends.signals import connection_created
from django.dispatch import receiver

@receiver(connection_created)
def setup_postgres(connection, **kwargs):
    if connection.vendor != 'postgresql':
        return

    # Timeout statements after 30 seconds.
    with connection.cursor() as cursor:
        cursor.execute("SET statement_timeout TO 30000;")

Note: When we call remote service then always set a timeout.

Select for update ... of

from django.db import transaction as db_transaction

with db_transaction.automic():
    transaction = (
        Transaction.objects
        .select_related(
            'user',
            'product',
            'product_category',
        )
        .select_for_update(
            of=('self',)
        )
        .get(uid=uid)
    )

FK indexes

class Membership(Model):
    group = ForeignKey(Group)
    user = ForeignKey(User)
    
     class Meta:
        unique_together = (
            ('group', 'user',)
        )

In the model above Django will implicitly create two indexes - one for user and one for group. The unique_together will also create an index on both fields. Wo we get one model with two fields and three indexes.

class Membership(models.Model):
    group = models.ForeignKey(Group, db_index=False)
    user = models.ForeignKey(User, db_inder=False)
    
    class Meta:
        unique_together = (('user', 'group'),)

Removing redundant indexes will make insert and updates faster, plus, our database is now lighter which is always a good thing. make the secondary indexes as small as possible. it's reasonable to assume there are more users than groups so putting the user column first will make the secondary index on group, smaller.

BRIN indexes

BRIN indexes can be more efficient then B-Tree indexes. The disadvantage of B-Tree index is it's size - B-Tree indexes can get big.

from django.contrib.postgres.indexes import BrinIndex


class SomeModel(Model):
    created = DatetimeField(
        auto_now_add=True,
    )

    class Meta:
        indexes = (
            BrinIndex(fields=['created']),
        )