Подсчеты быстрее в Postgres

Запросы на подсчет, вероятно, являются наиболее часто используемыми агрегированными запросами в реляционной базе данных. Подсчет — это фундаментальная операция, необходимая для многих CRUD-приложений, таких как сортировка, пагинация и поиск. Но подсчет записей может стать ужасно медленным по мере роста вашего набора данных. К счастью, существуют стратегии решения этой проблемы. В этой статье мы рассмотрим несколько подходов.

Настройка данных

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

CREATE TABLE sample_data (id int);
INSERT INTO sample_data SELECT generate_series(1, 200000);

Простой запрос на подсчет

Давайте разберем план запроса простого запроса на подсчет.

Он запускает элементарное Seq Scan для таблицы. Если вы не знакомы с чтением планов запросов, то я настоятельно рекомендую прочитать статью по ссылке ниже:

Анатомия плана запросов PostgreSQL

Arctype Team ・ Apr 10 ’21 ・ 10 min read

#postgres #база данных #sql

Для понимания Scans в целом, вам также может быть полезна эта статья:

Анализ сканов в PostgreSQL

Arctype Team for Arctype ・ Oct 21 ’21 ・ 5 min read

#руководство #postgres #схема #база данных

Параллельная обработка для подсчетов

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

INSERT INTO sample_data SELECT generate_series(200000, 4000000);
SET max_parallel_workers_per_gather = 4;

А затем попробуйте проанализировать план, как показано ниже:

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

Почему индексы не помогают при простом count.

Первое, что сделает любой пользователь базы данных, это добавит индекс, чтобы ускорить выполнение запроса. Давайте попробуем сделать это здесь.

CREATE INDEX id_btree ON sample_data USING BTREE(id)

В отличие от других запросов, здесь индекс не помогает.

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

SET enable_seqscan = OFF;

Подсчет узлов в структуре данных BTree занимает O(n), где n — количество строк, а также занимает дополнительную память — O(h), где h — высота дерева. Также увеличивается стоимость доступа при прямом обращении к индексу, из-за чего предпочтительнее последовательное сканирование обычных кортежей/данных таблицы.

Подсчет с помощью условия Where

Если в таблице есть предложение where, то планировщику очень легко использовать индекс, поскольку он сокращает большое количество строк из результата. Рассмотрим полный индекс и частичный индекс.

Полный индекс

Поскольку у нас уже есть полный индекс, запрос count с предложением where по столбцу id будет очень быстрым.

SELECT COUNT(id) FROM sample_data WHERE id = 200000

Частичный индекс

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

CREATE INDEX id_btree ON sample_data USING BTREE(id) WHERE id = 200000;

Частичные индексы быстрее, их легче кэшировать из-за их размера и проще поддерживать.

Счетчики отличий и дубликатов

По умолчанию запрос count считает все, включая дубликаты. Давайте рассмотрим distinct, который часто используется наряду с count.

SELECT DISTINCT(id) FROM sample_data

Эта команда использует сканирование только по индексу, но все равно занимает около 3,5 секунд. Скорость зависит от многих факторов, включая кардинальность, размер таблицы и то, кэшируется ли индекс. Теперь попробуем подсчитать количество уникальных строк в таблице.

SELECT COUNT(DISTINCT(id)) FROM sample_data

Эта команда не использует индекс и прибегает к последовательному сканированию. Как планировщик, вы были бы правы, выбрав последовательное сканирование, поскольку полный переход по индексу более затратен, как мы уже видели выше.

В реляционных системах баз данных действует правило никогда не иметь дело с distinct запросами. Мы никогда не сможем полностью избавиться от distinct, но при правильном моделировании базы данных (перемещение дублирующихся строк в другую таблицу и использование отношения «один ко многим», хранение уникальных подсчетов отдельно и т.д.) запрос с предложением where будет выполняться быстрее.

Приблизительный подсчет

В большинстве реальных случаев нам никогда не понадобится точное количество строк в таблице. Мы можем захотеть подсчитать определенные строки с помощью условия where (что можно решить с помощью индекса), но никогда — полный подсчет строк. Типичная рабочая нагрузка OLTP не добавляет миллион строк за день. Может быть, несколько тысяч строк, распределенных по разным временным окнам. В PostgreSQL есть способы получить приблизительный подсчет строк вместо фактического и при этом не нарушить условия использования в бизнесе.

Получение приблизительных подсчетов с помощью статистики

Планировщик запросов на основе затрат в PostgreSQL использует статистическую информацию для планирования запросов. Мы можем использовать эту информацию для получения приблизительного количества строк.

SELECT
    reltuples::bigint AS estimate
FROM
    pg_class
WHERE
    oid = 'public.sample_data' ::regclass;

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

Поддержание статистики в актуальном состоянии

Обсуждение внутреннего устройства процесса Vaccuum выходит за рамки этого блога, но для большинства OLTP-нагрузок настройки по умолчанию Auto Vacuum работают просто отлично. Если на таблицу приходится большая нагрузка данных, мы можем выполнить ручной Vacuum Analyze <table_name> при необходимости. Процесс Vacuum делает то, чем он назван. Он очищает и обновляет таблицу статистики самой последней и точной информацией.

Переосмысление проблемы и внешние решения

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

Использование кэша на уровне приложения

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

Использование переменной с помощью триггеров/крючков.

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

Hooks: Секретная функция, питающая экосистему Postgres

Arctype Team for Arctype ・ Mar 11 ・ 7 min read

#программирование #postgres #учебник #продуктивность

Предупреждение о триггерах! Полное руководство по триггерам SQL — настройка отслеживания БД в Postgres

Daniel Lifflander for Arctype ・ Jan 13 ’21 ・ 10 min read

#sql #postgres #база данных

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

Кэш, например Redis

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

Архитектура систем баз данных для мира микросервисов

Arctype Team for Arctype ・ Aug 5 ’21 ・ 11 min read

#руководство #база данных

Redis — это система, которая подходит для такого типа N-Tier архитектуры. Она:

• Быстрая.
• Безопасна для потоков
• Масштабируемая (с использованием шардинга)
• Постоянная (может быть включена)

Все отдельные сервисы могут вызывать Redis на основе паттерна Saga или паттерна API Composition для обновления значений.

Он очень широко используется в качестве кэша в мире микросервисов, но мы должны помнить, что привлечение еще одной внешней системы приводит к увеличению кривой обучения, обслуживания и устранения неполадок. Если у вас нет сложной N-уровневой системы, то лучше использовать более простые решения.

Заключение

Мы подробно рассмотрели, как работают «счетные& запросы под поверхностью и как их можно сделать быстрее. Подведем итоги:

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