9 Django советов для работы с Базами данных

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

В этой статье я собираюсь поделиться 9 советами по работе с базами данных в Django.

Aggregation с 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 для этого был добавлен аргумент фильтра для агрегатных функций:

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)

В 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'

Пользовательские функции

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

Скажем, у нас есть модель Report с полем duration. И нам нужно найти среднюю duration по всем Report:

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 для поля interval — нам нужно преобразовать interval в число, прежде чем мы сможем применить к нему 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 блокируется, и никто другой не может использовать его, пока он не будет выполнен.

Я уверен, что никто на самом деле не запускает Django с одним рабочим процессом, но мы всегда должны быть уверены, что один запрос не может занять слишком много ресурсов и слишком долго выполняться.

В большинстве приложений Django большую часть времени тратится на ожидание запросов к базе данных. Поэтому, нам надо установить ограничение на время ожидания для SQL-запросов.

Далее пример того как установить время ожидания глобально (global timeout ). Для этого в файле  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

ПРИМЕЧАНИЕ: Другое распространенное место, где требуется ограничение по времени, это все процессе связанные с сетевыми запросами. Поэтому убедитесь, что при создание нового сетевого запроса всегда устанавливается время ожидания:

import requests
response = requests.get(
    'https://api.slow-as-hell.com',
    timeout=3000,
)

Лимиты

Это в некоторой степени связано с последним пунктом об установлении ограничение по времени. Иногда мы хотим позволить пользователям создавать отчеты и, возможно, экспортировать их в электронную таблицу.

Нередко встречается пользователь, который считает разумным получить вообще все данные с незапамятных времен в середине рабочего дня. Также этот пользователь может открыть другую вкладку и повторить попытку, когда первая попытка «зависла».

В этом случае нам помогут лимиты данных.

Например ограничить запрос с 100 строк:

# очень плохой пример
data = list(Sale.objects.all())[:100]

Это будет худшее, что можно сделать. Вы просто загрузили в память все строки и далее вернули первые 100.

Другой пример:

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

Это намного лучше. Django будет добавит ключевое слово limit в SQL запрос для выборки только 100 строк.

Теперь допустим, что мы добавили лимит, пользователи находятся под контролем, и у нас все хорошо. Но у нас все еще есть одна проблема — пользователь запросил все данные, а мы дали ему толко 100 строк. Теперь пользователь думает, что было только 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 строк, мы выбираем 100 + 1 = 101 строк. Нам достаточно знать, что в запросе более 100 строк. Или, другими словами, выборка строк LIMIT + 1 — это минимум, что нам нужно, чтобы убедиться, что в результате запроса не более строк LIMIT.

Опиция 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)
  )
    ...

Управление транзакцией обычно включает в себя некоторые свойства пользователя и продукта, поэтому мы часто используем select_related для принудительного объединения и сохранения некоторых запросов.

Запросы на транзакции update также включает в себя создание блокировки, чтобы убедиться, что никто не манипулирует данными в этот момент.

Теперь вы видите проблему? НЕТ? Мы тоже.

У нас было несколько процессов, работающих ночью, выполняющих обслуживание таблиц продуктов и пользователей. Эти процессы выполняли обновления и вставки в таблицы, поэтому они также создавали блокировки для таблиц.

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

Код, который мы использовали для создания транзакции, пытался блокировать как таблицы транзакций, так и таблиц пользователей, продуктов и категорий. Как только процесс заблокировал последние три таблицы в середине ночи, транзакции начали генерировать ошибки.

После того как мы лучше поняли проблему, мы начали искать способы блокировки только необходимой таблицы — таблицы транзакций. К счастью, в Django 2.0 появилась новая опция 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(
            of=('self',)
        )
        .get(uid=uid)
  )
  ...

Новая опция  of была добавлена в select_for_update. С ее помощью мы можем явно указать, какие таблицы мы хотим заблокировать.  self  — это специальное ключевое слово, указывающее, что мы хотим заблокировать модель, над которой мы работаем, в данный момент, в нашем случае это была Transaction.

В настоящее время эта функция доступна только для бд PostgreSQL и Oracle.

FK Indexes

При создании модели 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 также создаст индекс для обоих полей. Таким образом, мы получаем одну модель с двумя полями и тремя индексами.

В зависимости от задач, которых мы выполняем с этой моделью, мы можем удалить не нужные индексы FK и сохранять только тот, который создан уникальным ограничением:

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

Удаление избыточных индексов ускорит операции вставки (insert) и обновление (update), плюс наша база данных станет меньше по размеру, что всегда хорошо.

Порядок столбцов в составном индексе

Индексы с более чем одним столбцом называются составными индексами (composite index). В составных индексах B-Tree первый столбец индексируется с использованием древовидной структуры. Из листьев первого уровня создается новое дерево для второго уровня и так далее.

Порядок столбцов в индексе очень важен.

В приведенном выше примере мы сначала получили бы дерево для групп, а для каждой группы — другое дерево для всех пользователей.

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

В нашем примере разумно предположить, что пользователей больше, чем групп, поэтому при первым должен располагаться индекс пользователей а вторым индекс для группы.

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

Это всего лишь условное практическое правило, и его следует принимать с небольшим допущением. Окончательная индексация должна быть оптимизирована для конкретного случая использования. Главное здесь — знать о неявных индексах и понимания значения порядка столбцов в составных индексах.

BRIN indexes

Индекс B-Tree структурирован как дерево. Стоимость поиска одного значения — это высота дерева + 1 для произвольного доступа к таблице. Это делает индексы B-Tree идеальными для уникальных ограничений и запросов диапазона (range).

Недостатком B-Tree индекса является его размер — индексы B-Tree могут как правило занимают много места.

Весьма распространено думать, что для B-Tree нет альтернатив, но на самом деле базы данных предлагают другие типы индексов для конкретных случаев использования.

Начиная с Django 1.11, появилась новая опция Meta для создания индексов для модели. Это дает возможность исследовать другие типы индексов.

PostgreSQL имеет очень полезный тип индекса, называемый BRIN (Block Range Index). В некоторых случаях индексы BRIN могут быть более эффективными, чем индексы B-Tree.

В первую очередь давайте посмотрим, что есть в официальной документация:

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

Чтобы понять это утверждение, важно понять, как работает индекс BRIN. Как следует из названия, индекс BRIN создаст мини-индекс для ряда соседних блоков в таблице. Индекс очень мал, и его предназначение только в том что бы он мог ответить находится или нет задание значение в диапазоне проиндексированных блоков.

Давайте рассмотрим упрощенный пример того, как работает BRIN, чтобы помочь нам понять его работу.

Скажем, у нас есть эти какие нибудь значения в столбце, каждый из которых представляет собой один блок:

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

Давайте создадим диапазоны для каждых 3 смежных блоков::

[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']),
        )

Чтобы понять разницу в размерах, я создал таблицу с ~ 2M строками с полем даты, которое естественно отсортировано на диске:

• B-Tree index: 37 MB
• BRIN index: 49 KB

Неплохой результат!

На самом деле при выборе и создании индексов следует учитывать гораздо больше, чем просто размер индекса. Но теперь, благодаря поддержке индексов в Django 1.11, мы можем легко использовать новые типы индексов в наших приложениях и делать их меньше и быстрее.

Оригинал