Download to read offline
![Простое решение: N+1 запросов
for category in categories: latest_articles_by_category[category] =
( Article.objects .filter(category=category) .order_by('-
date_published')[:N])
Придется сделать по одному запросу к БД на каждую группу, сработает
относительно быстро для небольшого количества групп.
Проблем у Django ORM с таким решением не возникает.](https://image.slidesharecdn.com/c14da2e8-2b18-4be2-82e6-bdfaed10aa07-160802213805/85/django-and-postgresql-16-320.jpg)
![В Django-ORM нет поддержки оконных
функций и явных подзапросов
articles_subquery = ( Article.objects .filter(category_id__in=(1, 2, 3, 4))
.annotate(row=RawSQL( 'ROW_NUMBER() OVER (PARTITION BY %s ORDER BY %s
DESC)', [Article._meta.get_field('category').column,
Article._meta.get_field('date_published').column] )) )
articles = Article.objects.raw(
'SELECT * FROM ({}) AS s WHERE s.row <= %s'
.format(articles_subquery.query), [5])](https://image.slidesharecdn.com/c14da2e8-2b18-4be2-82e6-bdfaed10aa07-160802213805/85/django-and-postgresql-18-320.jpg)
![Простое решение: N+1 запросов
for category in categories: latest_articles_by_category[category] =
( Article.objects .filter(category=category) .order_by('-
date_published')[:N])
Придется сделать по одному запросу к БД на каждую группу, сработает
относительно быстро для небольшого количества групп.
Проблем у Django ORM с таким решением не возникает.](https://image.slidesharecdn.com/c14da2e8-2b18-4be2-82e6-bdfaed10aa07-160802213805/75/django-and-postgresql-16-2048.jpg)
![В Django-ORM нет поддержки оконных
функций и явных подзапросов
articles_subquery = ( Article.objects .filter(category_id__in=(1, 2, 3, 4))
.annotate(row=RawSQL( 'ROW_NUMBER() OVER (PARTITION BY %s ORDER BY %s
DESC)', [Article._meta.get_field('category').column,
Article._meta.get_field('date_published').column] )) )
articles = Article.objects.raw(
'SELECT * FROM ({}) AS s WHERE s.row <= %s'
.format(articles_subquery.query), [5])](https://image.slidesharecdn.com/c14da2e8-2b18-4be2-82e6-bdfaed10aa07-160802213805/75/django-and-postgresql-18-2048.jpg)
Uploaded byOleg Churkin
django-and-postgresql
Документ обсуждает интеграцию Django ORM с PostgreSQL, подчеркивая важные моменты для разработчиков, такие как управление схемой данных и ограничения. В нем рассматриваются проблемы, возникающие при миграции схем, а также способы оптимизации запросов и манипуляций с данными для повышения производительности. Также поднимаются вопросы работы с jsonb и EAV, отмечая плюсы и минусы каждого подхода.
django-and-postgresql
- 1. Возможно ли подружить Django-ORMи PostgreSQL? Что нужно знать современному python-разработчику, чтобы быстро и эффективно разрабатывать высоконагруженные web-проекты на Django и PostgreSQL. Чуркин Олег @ Rambler&Co
- 2. Как мы используемORM в связке с СУБД? • Создание и изменение схемы данных (описание моделей, миграции схемы и данных) • Манипуляция данными (QuerySet’ы и соответствующие SQL- запросы) А теперь поговорим о нюансах.
- 3. Contraints (ограничения) вDjango ORM • Не поддерживается CHECK: CHECK (price >= minimum_price) • Не поддерживается FOREIGN KEY ON DELETE (UPDATE) – Django обрабатывает подобные ограничения на уровне питоновского кода, что может привести к огромному количеству DELETE запросов при каскадном удалении. Что делать: • Указывать неподдерживаемые ограничения в миграциях • Обязательно проставлять on_delete параметр в конструкторах полей.
- 4. Indexes (индексы) вDjango ORM • Умеет создавать только индекс по умолчанию (btree). • Не поддерживается конкурентное (CONCURRENTLY) создание и удаление индексов. • Не поддерживаются частичные (PARTIAL) индексы: CREATE INDEX idx_name ON table (pubdate) WHERE project_id = 1; Что делать: • Создавать нужные индексы в миграциях, кроме конкурентных. • Ждать Django 1.10, где можно будет вручную управлять транзакциями в миграции (Migration.atomic) и использовать CREATE INDEX CONCURRENTLY.
- 5. Почему изменения схемыданных могут быть небезопасны? • Некоторые запросы ALTER TABLE берут ACCESS EXCLUSIVE LOCK, которая блокирует таблицу на чтение и запись. • Изменение схемы может быть заблокировано запросом в статусе IDLE IN TRANSACTION (ABORTED). • VACUUM FULL полностью блокирует таблицу. • CREATE (DROP) INDEX блокирует таблицу на запись. https://postgrespro.ru/doc/explicit-locking.html#LOCKING-TABLES
- 6. Памятка по небезопасныммиграциям Проблема Решение CREATE (DROP) INDEX CREATE (DROP) INDEX CONCURRENTLY Изменение типа данных в колонке Добавляем новую колонку, меняем код, чтобы он использовал обе, удаляем старую колонку Добавление колонки со значением по умолчанию Отдельно добавляем колонку и отдельно проставляем ей значение по умолчанию и заполняем этим значением
- 7. Памятка по небезопасныммиграциям Проблема Решение Добавление новой NOT NULL колонки Лучше даже не думать об этом. Добавление колонки с UNIQUE- ограничением Добавляем новую колонку, создаем уникальный индекс конкурентно, создаем ограничение на основе индекса VACUUM FULL Лучше посмотреть в сторону pg_repack
- 8. На что стоитобратить внимание Django- разработчикам • db_index=True вызывает неконкурентный CREATE INDEX. • CREATE INDEX CONCURRENTLY нельзя выполнить в транзакции, поэтому ждем Djano 1.10. • Не выключайте autocommit, следите, чтобы транзакции всегда заканчивались commit или rollback. • Лучше разбивать большие миграции на несколько маленьких. • После созданияудаления индексов необходимо выполнить ANALYZE TABLE.
- 9. Как лучше провестимиграцию схемы? • Отдельно от кода проекта (иногда в три этапа) • Завершаем фоновые задачи с медленными запросами • tail –f uwsg_production_log | grep –С 10 HARAKIRI!!! • pg_activity • New Relic
- 10.
- 11.
- 12. Классические ошибки советыдля Django-разрабочиков • Экономьте ресурсы: минимизируйте количество запросов и оптимизируйте их по скорости. • Экономьте память приложения и сетевой трафик. • Попробуйте сначала написать SQL, а потом воспроизвести этот запрос через ORM. • БД всегда быстрее чем Python: агрегацию, сортировку и удаление дубликатов выгоднее выполнять на уровне СУБД. • Используйте транзакции
- 13. Анилизируем количество икачество SQL-запросов • Django: логгирование в Django через логгер django.db.backends • Django: django-debug-toolbar илии django-devserver • PostgreSQL: log_min_duration_statement • PostgreSQL: pg_stat_statements • PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) • PgBadger
- 14. Пишите запрос (QuerySet)максимально соответствующий вашей задаче • Не забывайте про select_related и prefetch_related. • В циклах используйте iterator для экономия памяти. • Запрашивайте только необходимые поля через: values, values_list, only, defer. • Используйте exists() вместо count(). • Не используйте order_by(‘?’)
- 15. Стандартная задача: выбратьTOP-N элементов в каждой группе Пример: для каждой темы получить N последних опубликованных статей (сортируем по дате, группируем по теме) • Для каждой темы сделаем по одному запросу на новости: N + 1 • Один запрос с подзапросом в WHERE. • Оконные функции (WINDOW FUNCTIONS) • LATERAL JOIN
- 16. Простое решение: N+1запросов for category in categories: latest_articles_by_category[category] = ( Article.objects .filter(category=category) .order_by('- date_published')[:N]) Придется сделать по одному запросу к БД на каждую группу, сработает относительно быстро для небольшого количества групп. Проблем у Django ORM с таким решением не возникает.
- 17. Window functions tothe rescue! PosgreSQL 9.1: оконные функции (window functions) – позволяют выполнять вычисления над строками, которые находятся в "окне", т.е. над строками перед и после текущей строки результата запроса. SELECT * from ( SELECT a.*, row_number() OVER (PARTITION BY a.category_id ORDER BY a.date_published DESC) as row FROM article AS a JOIN category AS c ON a.category_id = c.id AND c.id IN (1, 2, 3, 4) ) as s WHERE s.row <= N;
- 18. В Django-ORM нетподдержки оконных функций и явных подзапросов articles_subquery = ( Article.objects .filter(category_id__in=(1, 2, 3, 4)) .annotate(row=RawSQL( 'ROW_NUMBER() OVER (PARTITION BY %s ORDER BY %s DESC)', [Article._meta.get_field('category').column, Article._meta.get_field('date_published').column] )) ) articles = Article.objects.raw( 'SELECT * FROM ({}) AS s WHERE s.row <= %s' .format(articles_subquery.query), [5])
- 19. И самый быстрыйвариант: LATERAL JOIN PosgreSQL 9.3: LATERAL JOIN – выполняет второй подзапрос в поле FROM для каждого элемента из первого подзапроса. SELECT sa.* FROM (SELECT * FROM category WHERE id IN (1, 2, 3, 4)) as c JOIN LATERAL ( SELECT * FROM article as a WHERE a.category_id = c.id ORDER BY a.date_published DESC, a.id DESC LIMIT 5 ) as sa ON TRUE;
- 20. Классическая паджинация В Djangoреализована в виде класса Paginator, проблемы: • COUNT (Execution time: 7145.938 ms) • OFFSET X – БД нужно прочитать с диска X записей после соответствующей сортировки; неверный список элементов, если новый элемент был вставлен на страницу, которую уже запросили.
- 21. Решение: KEYSET паджинация •Запоминаем идентификаторы первого (FIRST_TIMESTAMP) и последнего (LAST_TIMESTAMP) элемента на странице, например это может быть дата публикации. • Чтобы получить следующую страницу используем конструкцию WHERE table.date > LAST_TIMESTAMP ORDER BY date ASC LIMIT <PAGE SIZE> • Для предыдущей страницы: WHERE table.date < FIRST_TIMESTAMP ORDER BY date DESC LIMIT <PAGE SIZE>
- 22. KEYSET паджинация Плюсы: • Работаетощутимо быстрее на большом количестве данных. • Правильно обрабатывает изменение предыдущих страниц при добавлении новых элементов. Минусы: • Нельзя перейти на произвольную страницу, без предварительной подготовки данных, поэтому хорошо применима только для «бесконечного скроллинга». • Нет полноценной реализации для Django, django-infinite-scroll- pagination – слишком примитивен и давно не обновляется.
- 23. EAV vs JSONB Непростыерешения в реляционных СУБД. • А что если нам требуется динамически изменять схему данных? Возможность добавить произвольное поле в таблицу из админки. • В нашем интернет магазине тысячи товаров, у которых набор полей может отличаться. • Небольшой «тюнинг» для объектов в БД – слишком много булевых колонок.
- 24. Entity-Attribute-Value Старейший и оченьраспространенный паттерн. Состоит из трех таблиц:
- 25. Entity-Attribute-Value Плюсы: • Есть хорошаяреализация для Django: django-eav • Знакомая реляционная семантика Минусы: • Значения атрибутов не типизированы • Сложно задать обязательные аттрибуты (NOT NULL) • Любые сложные запросы превращаются в нагромождение JOIN’ов • Производительность на большом количестве данных
- 26. JSONB (PostgreSQL 9.4+) Плюсы: •Проще и понятней. • Быстрее чем EAV, особенно с GIN индексом. • Данные + индексы занимают меньше места (раза в 3) • Поддерживает хранение различных типов данных внутри JSON формата, а не только строки. Минусы: • Поддерживаются не все инструменты и подходы, которые мы используем для реляционных данных (не все индексы и ограничения, ограниченный функционал запросов). • Неочевидный синтаксис запросов (->>, ->, @>, ?&, ?|).
- 27. JSONB на стероидах JSQUERYextension: новые возможности по поиску данных в jsonb: • Поиск элемента в массиве • Поиск массивов с определенным размером • Поиск во вложенных элементах через wildcards • Добавляет возможность использовать CHECK CONSTRAINT! И многое другое: https://github.com/postgrespro/jsquery Но, не все так просто: приходится создавать дополнительное поле с типом данных jsquery.
- 28. JSONB в Django-ORM Вверсии 1.9 доступно JSONField, поддеживает основные операции: • contains • contained_by • has_key • has_any_keys • has_keys • values Запросы jsquery не поддерживаются.
- 29. Дружить или нет?Вот в чем вопрос. • ORM не поощряет использование сложного SQL. • Ограниченная работа с схемой данных. • Встроенные инструменты используют известные, но не самые быстрые подходы к обработке данных. • Используемые вами приложения тоже не особо заморачиваются с оптимизацией. Мой вердикт: только приятельские отношения. Быстрая разработка и простота – это прекрасно, но на больших данных (более 100 000 записей а таблицах) вы уже почувствуете дискомфорт.
- 30.