Как получить нагрузку в БД на пустом месте

Рассказ пойдёт от имени бэкенд-разработчика. Несмотря на горизонтальную масштабируемость архитектуры с очередями, воркерами и stateless-интерфейсами, временами база данных становится слабым местом, поскольку некоторые программисты пишут такие запросы, что страшно глянуть.

Посмотрите на следующий план запроса, в котором найдёте и CTE, и APPEND , и GROUP BY , и ORDER BY . Представьте, каково это оптимизировать.

Как получить нагрузку в БД на пустом месте

Обсудим обыкновенные запросы в упрощённом варианте.

Пересмотрите схему данных

В первом примере разработчик сохраняет JSON в словаре – в PostgreSQL это тип hstore . Дальше разворачивает значение в строку внутри подзапроса, а во внешнем запросе делает поиск поля token и сравнивает его с конкретным параметром.

Как получить нагрузку в БД на пустом месте

Такие операции приводят к последовательному сканированию ( SeqScan ) всей таблицы. Какое оправдание нашёл человек: из-за JSON внутри hstore индекс не сделаешь, то есть не оптимизируешь.

Решение оказалось простым. Анализ показал, что JSON с таким токеном у нас меньше 1%, поэтому колонку убрали и вынесли таблицу в отдельную сущность.

Осторожнее с CTE

Часто разработчики заблуждаются насчёт CTE-команды WITH : это не представление (VIEW), которое подставляется в следующий запрос. Вначале исполняется WITH , а только потом его результат передаётся в другой запрос. Поэтому такой запрос сканирует все документы на диске, чтобы во внешнем запросе разработчик получил единственный необходимый файл:

Как получить нагрузку в БД на пустом месте

Как уменьшить выборку WITH из кучи строк до одной? Перенесите туда проверку условия. По этому полю был маленький индекс B-Tree.

Как получить нагрузку в БД на пустом месте

Результат такой оптимизации:

Как получить нагрузку в БД на пустом месте

Избавляйтесь от лишних данных

Рассмотрим хранение ненужных данных на примере списка системных новостей. Представьте, разработчик вносит в отдельную колонку типа hstore список идентификаторов пользователей, подписавшихся на показ этой новости – людей сотни тысяч. Пришло время, когда один пользователь устроил глобальную отписку, и без проверки выполняется вот такой запрос:

Как получить нагрузку в БД на пустом месте

Так как в PostgreSQL при обновлении записи оригинал помечается на удаление и создаётся новая строка, подобный запрос по факту скопировал почти всю таблицу с гигабайтами данных, нагрузив диск и процессор.

Что не так с этим запросом? На первый взгляд, идеальное обновление по первичному ключу:

Как получить нагрузку в БД на пустом месте

На самом деле, запрос рассчитывал метрики на основании действий пользователя в личном кабинете. Но чаще результат расчёта равнялся значению, сохранённому в базе данных, а обновления становились бесполезными. Добавление проверки content != :new content на порядок сократило количество UPDATE :

Как получить нагрузку в БД на пустом месте

Проверьте код на бэкенде

Следующий пример безобидного запроса: установка нового значения и ключа в hstore . Проблема в том, что разработчик запускал этот запрос при итерации, добавляя туда кучу новых идентификаторов. В масштабе 100 тысяч пользователей словарь увеличивался и давал непомерную для конкретного сервиса нагрузку:

Как получить нагрузку в БД на пустом месте

Вариант оптимизации – сформировать на бэкенде один запрос и отправить сразу все изменения в базу.

Следите за размерами IN

Иногда разработчик, казалось бы, оправданно делает гигантский запрос, вместо того чтобы тысячу раз обращаться к базе данных в цикле за дополнительной информацией:

Как получить нагрузку в БД на пустом месте

Но здесь подвела куча индексов в одной таблице. Между индексом на companyid и простым B-Tree на companyid + groupname PostgreSQL выбрал первое: прочитал миллионы операций по companyid и отфильтровал результат по groupname . Хотя groupname содержал всего тысячу элементов.

Для решения проблемы сперва уменьшили индекс, поскольку groupname встречался не у всех компаний:

Как получить нагрузку в БД на пустом месте

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

Исправляйте структуру запроса

Помните пример с системными новостями? Рассмотрим похожий, только с привязкой к двум сущностям: пользователям и компаниям.

Как получить нагрузку в БД на пустом месте

Чтобы пользователь при входе в личный кабинет видел свои сообщения, а попадая в компанию – соответствующее окружение, написали такой запрос:

Как получить нагрузку в БД на пустом месте

На hstore навешивают GIN-индекс, который возвращает битовую карту, или при большом количестве данных – слабую карту с идентификаторами страниц. Но выполнение сортировки для компании с сотней тысяч записей переполнило рабочую память и привело к такому сумасшествию на диске:

Как получить нагрузку в БД на пустом месте

Поскольку GIN-индекс хранит данные без определённого порядка, пришлось исправить структуру запроса: когда сделали первичным ключом userid + companyid + id , скорость выполнения выросла на глазах.

Как получить нагрузку в БД на пустом месте

Назначайте тайм-ауты

Для некоторых запросов медленная скорость – нормальное явление. Представьте: ваш сервис делает расчёты и собирает отчёт по конкретным категориям, а пользователь сам выбирает период для выборки.

Стояло ограничение времени выполнения в 100 секунд на SQL Provider: через заданное время прилетала ошибка сервера баз данных, на что пользователь повторял запрос. Но при этом провайдер не завершал первый запрос, и на сервере выполнялось уже два одинаковых тяжёлых запроса, которые боролись за системные ресурсы.

Как удалось заставить провайдер завершать запрос по истечении тайм-аута? Установили переменную сессии statement_timeout в 100 секунд.

Как получить нагрузку в БД на пустом месте

Проверяйте на существование правильно

Когда разработчикам говорят проверить существование хоть одной записи в базе данных, некоторые вычисляют общее количество строк с проверкой кучи условий и пишут return count > 0 :

Как получить нагрузку в БД на пустом месте

Вместо этого используйте EXISTS с константой, поскольку оператор завершает запрос, как только встретит подходящую запись:

Как получить нагрузку в БД на пустом месте

Используйте BRIN-индекс

Иногда от бизнеса прилетает задание посчитать общее количество. Вы хоть раз интересовались, сколько получили всего сообщений на почте или в мессенджере? А кому-то подобное понадобилось, и запрос выглядел жутко:

Как получить нагрузку в БД на пустом месте

Позднее выяснилось, что в пользовательском интерфейсе эта информация не выводится, потому как фронтенд-разработчики убрали её при редизайне, тем самым сделали оптимизацию.

А если итоговое количество нужно? Например, в админке сайта человек анализирует посещаемость в определённые периоды. Такой запрос выполняется медленно без индекса по дате:

Как получить нагрузку в БД на пустом месте

Но целесообразно ли навешивать огромный индекс B-Tree на маленькую табличку? Последовательное сканирование занимало здесь 5–6 минут. На помощь пришёл Block Range Index (BRIN). BRIN-индекс возвращает номера страниц с подходящими данными и выполняет проверку всей страницы на соответствие условиям.

Как получить нагрузку в БД на пустом месте

Поскольку он не хранит идентификаторы строк и значения, его размер гораздо меньше: у BRIN-индекса – 72 KB, а для сравнения, у B-Tree – 216 Mb.

Вот результат оптимизации с помощью BRIN-индекса:

Как получить нагрузку в БД на пустом месте

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

Сокращайте количество сохраняемой информации

Уменьшайте не только индексы, но и таблицы. Допустим, в одной колонке у вас JSON со старыми данными, а в другой – с новыми.

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

Как получить нагрузку в БД на пустом месте

Как ещё уменьшить время на бэкапы? Замените тип timestamp на date , если время в таблице нулевое. Так, разработчики получили экономию 1,3 гигабайт на пустом месте и сократили время на ежедневное резервное копирование.

Как получить нагрузку в БД на пустом месте

Оптимизируйте LIKE

Некоторые считают, что для запроса с LIKE нельзя создать индекс. Мнение ошибочное, и здесь вам пригодится индекс SP-GIST, который разделит значения на отдельные группы. Для строк он создаст отдельные ветки на каждую букву:

Как получить нагрузку в БД на пустом месте

SP-GIST подойдёт для списков товаров, услуг, городов или других строк. При автодополнении на сайте вы передаёте в запрос перед знаком % то, что ввёл пользователь, а планировщику даже не придётся лезть в кучу, ведь все данные уже в индексе.

А что, если искомое в справочнике слово идёт не с начала строки? Используйте GIN-индекс, похожий на алфавитный указатель в книге: он указывает номера страниц, где вы вручную ищете нужное слово.

Как получить нагрузку в БД на пустом месте

В результате уйдёте от последовательных сканов и получите ускорение запроса в несколько раз:

Как получить нагрузку в БД на пустом месте

Применяйте Partial index

Partial index – индексы c ограничением по условию. Представьте, что нужна проверка операций компании за последний год по двум постоянным условиям:

Как получить нагрузку в БД на пустом месте

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

Как получить нагрузку в БД на пустом месте

Тогда запрос начнёт выполняться только по индексу, а использование диска упадёт до нуля:

Как получить нагрузку в БД на пустом месте

Разберитесь со сложной сортировкой

У продуктов встречаются очень сложные правила сортировки:

Как получить нагрузку в БД на пустом месте

И первым делом разработчики пытаются оптимизировать фильтрацию WHERE . В такой ситуации рассмотрите создание индекса для сложной сортировки. Для примера выше такая оптимизация сократила время выполнения и количество сканов:

Как получить нагрузку в БД на пустом месте

В стремлении написать всё зараз не забывайте, что подзапросы и UNION обходятся недёшево. Посмотрите на запрос:

Как получить нагрузку в БД на пустом месте

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

Для решения вопроса создают отдельную таблицу или назначают единственную из необходимых мастер-таблицей. Переносите в неё данные из остальных таблиц под другими категориями и получаете обыкновенный select * from mastertable вместо объединений.

Анализируйте код и планы запросов

Когда вы приходите к необходимости создания hints для планировщика запросов или костылей, чтобы объяснить PostgreSQL, что делать, то пересмотрите собственную схему и код. Возможно, там придётся всё менять.

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

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

Заключение

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

Вы пропустили

AEGIS Algorithms Android Angular Apache Airflow Apache Druid Apache Flink Apache Spark API API Canvas AppSec Architecture Artificial Intelligence Astro Authentication Authorization AutoGPT AWS AWS Aurora AWS Boto3 AWS EC2 AWS Lambda Azure Babylon.js Backend bash Beautiful Soup Bento UI Big Data Binary Tree Browser API Bun Career Cassandra Charts ChatGPT Chrome Extension Clean Code CLI ClickHouse Coding Codux Combine Compose Computer Context Fusion Copilot Cosmo Route CProgramming cron Cryptography CSS CTF Cypress DALL-E Data Analysis Data science Database dbt dbt Cloud deno Design Design Patterns Detekt Development Distributed Systems Django Docker Docker Hub Drizzle DRY DuckDB Express FastAPI Flask Flutter For Beginners Front End Development Game Development GCN GCP Geospatial Git GitHub Actions GitHub Pages Gitlab GMS GoFr Golang Google Google Sheets Google Wire GPT-3 GPT3 Gradio Gradle Grafana Graphic Design GraphQL gRPC Guidance HMS Hotwire HTML Huawei HuggingFace IndexedDB InfoSec Interview iOS Jackknife Java JavaScript Jetpack Compose JSON Kafka Kotlin Kubernetes LangChain Laravel Linux LlaMA LLM localStorage Logging Machine Learning Magento Math Mermaid Micro Frontends Mobile Mobile App Development mondayDB MongoDB Mongoose MySQL Naming NestJS NET NetMock Networks NextJS NLP Node.js Nodejs NoSQL NPM OOP OpenAI OTP Pandas PDF PHP Playwright Plotly Polars PostgreSQL Prefect Productivity Programming Prometheus Puppeteer Pushover Python Pytorch Quarkus Rabbitmq RAG Ramda Raspberry Pi React React Native Reactor Redis REST API Revolut Riverpod RProgramming Ruby Ruby on Rails Rust Scalene SCDB ScyllaDB Selenium Servers Sklearn SLO SnowFlake Snowkase Software Architecture Software Development Solara Solid Spring Boot SQL SQLite Streamlit SudoLang Supabase Swift SwiftUI Tailwind CSS Taipy Terraform Testing Transformers TURN TypeScript Ubuntu UI Design Unix UX UX Design Vim Vite VSCode Vue Web Architecture Web Components Web Development Web Frameworks Web Scraping Web-разработка Webassembly Websocket Whisper Widgets WordPress YAML YouTube Zed Наука о данных Разное Тренды

Современный подход к разработке с использованием Next.js