Поради по роботі з БД у Django

ORM відкриває розробникам чудові можливості, але таке абстрагування доступу до БД має свою ціну. Якщо ви заглибитесь у цю тему і спробуєте змінити деякі налаштування за замовчуванням, ви побачите, що насправді можна отримати ще більше користі.

У статті дізнаємось як покращити свою роботу з базами даних у Django.

Агрегація з filter

Якщо ми хотіли отримати щось на зразок загальної кількості користувачів та активних користувачів до Django 2.0, то застосовували умовні вирази.

from django.contrib.auth.models import User
from django.db.models import (
    Count,
    Sum,
    Case,
    When,
    Value,
    IntegerField,
)
User.objects.aggregate(
    total_users=Count('id'),
    total_active_users=Sum(Case(
        When(is_active=True, then=Value(1)),
        default=Value(0),
        output_field=IntegerField(),
    )),
)

У Django 2.0 з'явився аргумент filter для агрегатних функцій, з яким все стало набагато простіше:

from django.contrib.auth.models import User
from django.db.models import Count, F
User.objects.aggregate(
    total_users=Count('id'),
    total_active_users=Count('id', filter=F('is_active')),
)

Якщо ви використовуєте PostgreSQL, то запити виглядатимуть так:

SELECT
    COUNT(id) AS total_users,
    SUM(CASE WHEN is_active THEN 1 ELSE 0 END) AS total_active_users
FROM
    auth_users;
		
SELECT
    COUNT(id) AS total_users,
    COUNT(id) FILTER (WHERE is_active) AS total_active_users
FROM
    auth_users;

У другому запиті використовуємо вираз FILTER (WHERE …).

Результати QuerySet у вигляді namedtuples

namedtuples та ORM у Django, починаючи з версії 2.0, заслуговують на увагу.

У Django 2.0 додали новий атрибут до values_list під назвою named. Якщо встановити значення named як true, буде повернено queryset як список namedtuples:

> user.objects.values_list(
    'first_name',
    'last_name',
)[0]
('Haki', 'Benita')
> user_names = User.objects.values_list(
    'first_name',
    'last_name',
    named=True,
)
> user_names[0]
Row(first_name='Haki', last_name='Benita')
> user_names[0].first_name
'Haki'
> user_names[0].last_name
'Benita'

Користувацькі функції

ORM у Django дуже потужний та багатофункціональний, але він не може встигати за усіма постачальниками баз даних. На щастя, ORM можна розширювати користувацькими функціями.

Припустимо, що у нас є модель Report з полем duration. Ми хочемо знати середню тривалість усіх звітів:

from django.db.models import Avg
Report.objects.aggregate(avg_duration=Avg('duration'))
> {'avg_duration': datetime.timedelta(0, 0, 55432)}

Усе чудово, але саме по собі середнє значення ні про що не говорить. Спробуймо також отримати стандартне відхилення:

from django.db.models import Avg, StdDev
Report.objects.aggregate(
    avg_duration=Avg('duration'),
    std_duration=StdDev('duration'),
)
ProgrammingError: function stddev_pop(interval) does not exist
LINE 1: SELECT STDDEV_POP("report"."duration") AS "std_dura...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Упс...У PostgreSQL stddev не підтримує інтервали – нам необхідно перетворити інтервал на число, перш ніж ми зможемо застосувати STDDEV_POP до нього.

Один з варіантів – отримати epoch (епоху) з duration:

SELECT
    AVG(duration),
    STDDEV_POP(EXTRACT(EPOCH FROM duration))
FROM 
    report;
      avg       |    stddev_pop    
----------------+------------------
 00:00:00.55432 | 1.06310113695549
(1 row)

Як реалізувати таке у Django? Ви вгадали – користувацька функція:

# 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'))),
)
{'avg_duration': datetime.timedelta(0, 0, 55432),
 'std_duration': 1.06310113695549}

Зверніть увагу на вираз F при виклику Epoch.

Затримка інструкції

Перейдемо до найпростішої, але найважливішої поради. Усі ми люди, тому робимо помилки. Ми не можемо організувати обробку кожного випадку, тому повинні встановити межі. На відміну від Tornado, asyncio та Node, які не блокуються, Django, зазвичай, використовує синхронні worker processes.Тобто коли користувач виконує тривалу операцію, робочий процес блокується, і ніхто інший не може використовувати його, поки він не буде завершений.

Напевно, ніхто насправді не використовує Django у продакшині лише з одним worker process, але нам все одно треба переконатися, що запит не забирає занадто багато ресурсів тривалий час.

У застосунках на Django більшість часу витрачається на очікування запитів до БД. Тому встановлення затримки на SQL-запити — гарне рішення.

Ми встановлюємо глобальний тайм-аут у нашому wsgi.py файлі так:

# 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;
        """)

Чому саме wsgi.py? Так ми впливаємо лише на worker processes, при цьому не торкаючись аналітичних запитів, завдань cron тощо.

Якщо ви використовуєте постійні підключення до бази даних, то таке налаштування не призведе до додаткових витрат у кожному запиті.

Затримку можна також встановити на користувацькому рівні:

postgresql=#> alter user app_user set statement_timeout TO 30000;
ALTER ROLE

Примітка: Мережеве з'єднання також забирає багато часу, тому переконайтеся, що ви встановили тайм-аут при виклику віддаленого сервісу.

LIMIT

Наступна порада також пов'язана зі встановленням меж. Іноді ми хочемо дозволити користувачам робити звіти та експортувати їх в електронну таблицю. Якщо на продакшині виникає якась дивна поведінка, ми, зазвичай, звинувачуємо саме такий функціонал, адже тут все залежить від користувача.

Тоді на допомогу приходить LIMIT.

Обмежимо певний запит ста рядками:

# bad example
data = list(Sale.objects.all())[:100]

Найгірше, що ми могли зробити: вивантажити у пам'ять усі рядки, щоб повернути лише 100.

Спробуймо знову:

data = Sale.objects.all()[:100]

Вже краще. Django використає обмеження в SQL, щоб отримати тільки 100 рядків.

Припустимо ми додали обмеження, користувачі під контролем і усе добре. Але досі лишається проблема: користувач робить запит на всі рядки, а ми повертаємо йому лише 100. Тепер він думає, що всього є 100 рядків, але це не так.

Замість того, щоб сліпо повертати перші 100 рядків, переконаємось, що є більше ніж сто рядків (зазвичай, після фільтрації) і викидаємо виключення:

LIMIT = 100
if Sales.objects.count() > LIMIT:
    raise ExceededLimit(LIMIT)
return Sale.objects.all()[:LIMIT]

Так працюватиме, але ми просто додали ще один запит. Можна зробити краще:

LIMIT = 100
data = Sale.objects.all()[:(LIMIT + 1)]
if len(data) > LIMIT:
    raise ExceededLimit(LIMIT)
return data

Замість вибірки ста рядків, ми отримуємо 100 + 1 = 101 рядок. Якщо 101 рядок існує, це вже означає, що є понад 100 рядків. Іншими словами: щоб переконатися, що результат запиту не перевищує LIMIT, треба лише отримати LIMIT + 1 рядок.

Запам'ятайте такий трюк: він може стати вам у пригоді.

Select for update … of

Загальний шаблон для роботи з транзакціями виглядає так:

from django.db import transaction as db_transaction
...
with db_transaction.atomic():
  transaction = (
        Transaction.objects
        .select_related(
            'user',
            'product',
            'product__category',
        )
        .select_for_update()
        .get(uid=uid)
  )
    ...

Робота з транзакціями, зазвичай, вимагає деяких властивостей від user та product, тому часто використовується select_related, щоб здійснити з'єднання (join) та зберегти деякі запити.

Щоб оновити транзакцію, варто встановити блокування, щоб переконатися, що ніхто не змінює транзакцію в той самий час.

На перший погляд тут немає проблеми. Але на продакшені ми виконали деякі ETL-процеси для обслуговування таблиць user та product. Ці процеси здійснювали оновлення та вставку до таблиць так, що вони також отримали блокування.

Тож в чому проблема? Коли select_for_update використовується разом з select_related, Django спробує встановити блокування всіх таблиць в запиті.

Код, що використовується для отримання транзакцій, намагається встановити блокування як на таблицю транзакції, так і таблиці users, product та category. Як тільки ETL блокує останні три таблиці, транзакції не виконуються.

Як нам заблокувати лише потрібну таблицю — таблицю транзакцій? На щастя, у Django 2.0 з'явилася нова опція у select_for_update:

from django.db import transaction as db_transaction
...
with db_transaction.atomic():
  transaction = (
        Transaction.objects
        .select_related(
            'user',
            'product',
            'product__category',
        )
        .select_for_update(
            of=('self',)
        )
        .get(uid=uid)
  )
  ...

У select_for_update додали of. Використовуючи of, ми можемо явно вказати, які таблиці хочемо заблокувати. self — спеціальне ключове слово, яке вказує, що ми хочемо заблокувати модель, над якою працюємо (у нашому прикладі — Transaction).

Зараз така опція доступна лише у PostgreSQL та Oracle.

FK індекси

При створенні моделі, Django автоматично створить B-Tree індекс для будь-якого зовнішнього ключа. B-Tree індекси можуть бути заважкими та не завжди потрібними.

Класичний приклад — модель для відношення M2M:

class Membership(Model):
    group = ForeignKey(Group)
    user = ForeignKey(User)

У такій моделі Django неявно створить два індекси — один для user і один для group.

Інший поширений шаблон у моделях M2M: додати унікальне обмеження для двох полів. У нашому випадку це означає, що користувач може бути членом тільки однієї групи і лише один раз:

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

unique_together також створить індекс для обох полів. Тобто ми отримаємо одну модель з двома полями та трьома індексами.

Нам можуть і не знадобитися індекси зовнішнього ключа, тоді ми залишаємо лише індекс, створений обмеженням unique. Усе залежить від завдання.

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

Видалення надлишкових індексів дозволить здійснювати insert і update швидше, до того ж база даних стане легшою, що завжди на користь.

Порядок стовпців у складеному індексі

Індекси, що містять більше одного стовпця — складені. У складених індексах B-Tree перша колонка індексується, використовуючи деревоподібну структуру. З листя першого рівня створюється нове дерево другого рівня і так далі.

Порядок стовпців у індексі важливий.

У прикладі вище ми спочатку отримаємо дерево для груп, а потім у кожній групі дерево для всіх користувачів.

Основне правило для складених індексів B-Tree — робити вторинні індекси якомога меншими. Іншими словами, на першому місці повинні стояти стовпці з великою кількістю елементів (більш чіткими значеннями).

У нашому прикладі доцільно припустити, що користувачів більше, ніж груп, тому розмістивши спочатку стовпець user, ми зменшимо вторинний індекс для group.

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

Проте не варто сліпо слідувати вказаному правилу. Остаточну індексацію слід оптимізувати відповідно до ситуації. Головне тут — знати про неявні індекси і значення порядку стовпців у складених індексах.

BRIN-індекси

B-Tree індекс структуровано як дерево. Швидкість пошуку одного значення дорівнює висоті дерева + 1 для випадкового доступу до таблиці. Саме тому B-Tree індекси ідеальні для обмеження unique та (деяких) запитів діапазону.

Недоліком B-Tree індексів є їх розмір – B-Tree індекси можуть ставати завеликими.

Нерідко здається, що альтернатив немає, але бази даних пропонують інші типи індексів для конкретних юзкейсів.

Починаючи з Django 1.11, з'явилась нова мета-опція створення індексів для моделі. Ми отримали можливість взаємодіяти з іншими типами індексів.

У PostgreSQL є дуже корисний тип індексу – BRIN (Block Range Index, індекс блокових зон). За певних обставин індекси BRIN можуть бути більш ефективними, ніж індекси B-Tree.

Спершу звернемося до офіційної документації:

BRIN- індекси призначені для обробки дуже великих таблиць, в яких деякі стовпці мають природну кореляцію з їх фізичним розташуванням у таблиці.

Щоб зрозуміти про що йде мова, треба познайомитись з принципами роботи індексів BRIN. Як випливає з назви, BRIN-індекс створить міні-індекс для діапазону суміжних блоків в таблиці. Такий індекс дуже малий і вказує лише чи дійсно значення не в діапазоні або чи може бути у діапазоні індексованих блоків.

Поглянемо як працюють індекси BRIN на практиці. Припустимо, у нас є наступні значення у стовпці, кожне з яких – один блок:

1, 2, 3, 4, 5, 6, 7, 8, 9

Створимо діапазон для кожних трьох суміжних блоків:

[1,2,3], [4,5,6], [7,8,9]

У кожному діапазоні виокремимо мінімальне і максимальне значення:

[1–3], [4–6], [7–9]

Спробуймо відшукати значення 5, використовуючи індекс:

• [1–3] — точно не тут.
• [4–6]  — може бути тут.
• [7–9] — точно не тут.

Помічаємо, що з індексами ми обмежили наш пошук до блоків 4-6.

Оглянемо ще один приклад: на цей раз значення в стовпці не будуть добре відсортовані:

[2,9,5], [1,4,7], [3,8,6]

Наш індекс з мінімальним і максимальним значенням у кожному діапазоні:

[2–9], [1–7], [3–8]

Знову спробуємо відшукати значення 5:

• [2–9] – може бути тут.
• [1–7]  – може бути тут.
• [3–8] – може бути тут.

Тут індекс зайвий – мало того, що він взагалі не обмежив пошук, ми насправді змушені були здійснити більше операцій читання, тому що витягли не лише індекс, а і всю таблицю.

Повернемося до документації:

...стовпці мають деяку природну кореляцію з їх фізичним розташуванням у таблиці

У цьому головна особливість індексів BRIN. Щоб отримати від них максимальну користь, значення у стовпці повинні бути приблизно відсортовані або згруповані на диску.

Тепер повернемося до Django: яке з наших полів часто індексується і, швидше за все, буде природно сортуватися на диску? Правильна відповідь: auto_now_add.

Поглянемо на дуже поширений шаблон у моделях Django:

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

При використанні auto_now_add Django автоматично заповнить поле часом створення рядка. Поле created – також добре підходить для запитів, тому часто індексується.

Додамо BRIN-індекс для created:

from django.contrib.postgres.indexes import BrinIndex
class SomeModel(Model):
    created = DatetimeField(
        auto_now_add=True,
    )
    class Meta:
        indexes = (
            BrinIndex(fields=['created']),
        )

Щоб отримати уявлення про різницю в розмірі, для прикладу візьмемо таблицю з ~2 мільйонами рядків з датою, яка відсортована на диску у природному порядку:

• B-Tree індекс: 37 MB
• BRIN індекс: 49 KB

Так-так, тут все правильно.

При створенні індексів, окрім їх розміру, варто звертати увагу ще на багато нюансів. Але тепер, з підтримкою індексів у Django 1.11, ми з легкістю зможемо інтегрувати нові типи індексів у наші застосунки, зробивши їх легшими та швидшими.