Советы по оптимизации работы с PostgreSQL для Python

Оптимизация конфига

Изначально Postgres после установки запускается с минимальным конфигом для поддержки слабых устройств. Чтобы это исправить, нужно открыть postgresql.conf.

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

Ввести параметры системы и выбрать для чего используется Postgres.

Структура таблиц

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

Также выбор типа и размера поля может ускорить обработку запросов, так как чем меньше объем, тем быстрее.

Индексы

Индексы нужны в Postgres для повышения производительности поиска путем ускорения доступа к данным. Структуры в БД для фильтрации данных.

Postgres не так много индексов. Основные простые индексы: btree, gin

Но также нельзя злоупотреблять индексами: замедляют обновление и ставку. Увеличивают размер бд.

Создание индекса:

CREATE INDEX index_name ON table_name USING {Название индекса}(имя столбца)

CREATE INDEX index_name ON table_name USING GIST (column_name)

B-tree:

Данный индекс в Postgres используется как основной. Подходит для большинства задач, где нужен поиск по сравнению. Работает как бинарное дерево.

Благодаря B-tree можно проиндексировать разные поля, чтобы  сравнивать и сортировать их. Сюда можно отнести int, str, bool, datetime и другие.

Пример:

SELECT id, likes FROM posts WHERE topic = ”kpop”

Без индексов Postgres пришлось бы пройти полностью по таблице posts и сверять поле topic с заданным значением. Однако из-за индекса Postgres использует index search по индексу бинарного дерева. Работает как бинарный поиск.

GIN

Данный индекс в Postgres используется для создания индексов для текста. Добавьте несколько миллионов текстовых документов. Некоторые ключевые слова могут появляться миллионы раз. И если это обычное b-tree, ключ будет сохранен миллионы раз.
Однако с GIN каждый ключ сохраняется уникально и присваивается списку документов. Ключ организован в стандартное B-tree. Каждая запись имеет свой указатель к списку документов и указывает на все записи с одним ключом. 

Оптимизация запросов

И последнее: оптимизируем запросы.

Если обнаружили тяжёлые запросы, то рекомендую сделать для них Explain или Analyze. Посмотрите на данные фразы:

— Seq Scan — означает, что запрос выполняется через последовательный перебор строк таблицы.

— Nested Loops — соединение с вложенными циклами.

Например: Seq Scan ON test (cost=0.00..50.00 ROWS=30)

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

Также если Seq Scan по таблице, где строк более нескольких десятков тысяч и при этом есть FILTER – в таком случае необходимо посмотреть на поля в FILTER и найти подходящий индекс.

Логика чтения плана запроса проста:

1) Смотрим на самый большой по стоимости оператор

2) Это Seq Scan или Nested Loops?

3) Смотрим следующий по стоимости оператор

Оптимизация чаще всего происходит через добавление индекса либо через упрощение запроса.

Дополнительные советы

Список дополнительных способов ускорить работу с Базой данный:

• Вместо частого обращения к БД использовать брокер сообщений
• Редактирование индексов
• С ORM перейти на чистый SQL
• Кеширование через денормализацию данных, когда сохраняем данные в угоду скорости
• Кэширование редко изменяющихся таблиц

• Вместо сканирования всей выборки COUNT можно использовать EXISTS
• Избавиться от DISTINCT, ускорит выборку

Полезные инструменты

Cледующий сервис для чтения планов:

https://tatiyants.com/pev

Для анализа нагрузки сервера удобная команда: htop

А для красивых графиков можно использовать Grafana

https://mcs.mail.ru/docs/additionals/cases/cases-monitoring/case-psql-exporter