Ровно год назад с рассказа о нашем сервисе визуализации планов запросов мы начали публикацию на Хабре серии статей, посвященных работе с PostgreSQL и его особенностям. Это уже пройденные нами «грабли», интересные наработки, накопившиеся рекомендации, применяемые в разработке «Тензора» — те вещи, которые помогают нам делать СБИС более эффективным.
СБИС — это система полного цикла управления бизнесом — от кадрового учета, бухгалтерии, делопроизводства и налоговой отчетности, до таск-менеджмента, корпоративного портала и видеокоммуникаций. Поэтому каждый из 1 500 000 клиентов-организаций находит что-то полезное для себя и использует наши сервисы на постоянной основе — что дает ежемесячно более миллиона активных клиентов.
И все их данные надо где-то хранить и эффективно извлекать. Поэтому еще в далеком 2012 году мы сделали ставку на PostgreSQL, и теперь это основное хранилище данных наших сервисов:
Чтобы упорядочить накопившиеся знания, за минувший год мы опубликовали более 60 статей, в которых делимся своим реальным опытом, проверенным практикой «сурового энтерпрайза». Возможно, какие-то из них вы пропустили, поэтому под катом мы собрали дайджест, где каждый разработчик и DBA найдет что-то интересное для себя.
Для удобства все статьи разбиты на несколько циклов:
Первое, чему необходимо научиться для разработки эффективных запросов — это оценивать их «ресурсоемкость». Для этого мы разработали и выложили в публичный доступ удобный сервис визуализации планов explain.tensor.ru.
26.11 — О чем молчит EXPLAIN, и как его разговорить (+38, ✓128)
11.02 — Массовая оптимизация запросов PostgreSQL (видео) (+28, ✓131)
26.03 — Рецепты для хворающих SQL-запросов (видео) (+23, ✓143)
05.06 — Понимаем планы PostgreSQL-запросов еще удобнее (+25, ✓88)
29.07 — Вооруженным глазом: наглядно о проблемах PostgreSQL-запроса (+32, ✓69)
10.08 — Правильно [c]читаем параллельные планы PostgreSQL (+17, ✓33)
03.09 — PostgreSQL Query Profiler: как сопоставить план и запрос (видео) (+13, ✓59)
29.10 — Анализируем «слона» по частям (+19, ✓24)
09.12 — CTE x CTE (+8, ✓35)
10.12 — вредные JOIN и OR (+20, ✓108)
11.12 — статистика всему голова (+10, ✓54)
12.12 — сизифов JOIN массивов (+14, ✓37)
19.12 — передача наборов и выборок в SQL (+8, ✓95)
24.12 — обновляем большую таблицу под нагрузкой (+14, ✓126)
20.01 — редкая запись долетит до середины JOIN (+18, ✓119)
27.01 — ударим словарем по тяжелому JOIN (+8, ✓107)
02.03 — меняем данные в обход триггера (+24, ✓61)
10.03 — сказ об итеративной доработке поиска по названию (+17, ✓82)
12.03 — сражаемся с ордами «мертвецов» (+32, ✓106)
31.03 — вычисление условий в SQL (+26, ✓65)
27.04 — навигация по реестру (+22, ✓74)
14.05 — насколько глубока кроличья нора? пробежимся по иерархии (+19, ✓83)
24.06 — подозрительные типы (+40, ✓60)
28.06 — накручиваем себе проблемы (+21, ✓56)
08.07 — SELF JOIN vs WINDOW (+14, ✓32)
14.07 — Unreal Features of Real Types, или Будьте осторожны с REAL (+9, ✓10)
04.08 — «Должен остаться только один!» (+24, ✓80)
20.08 — уникальные идентификаторы (+21, ✓67)
01.10 — «Бесконечность — не предел!», или Немного о рекурсии (+18, ✓47)
07.10 — убираем медленные и ненужные сортировки (+27, ✓91)
10.11 — работаем с отрезками в «кровавом энтерпрайзе» (+27, ✓64)
18.11 — DBA-детектив, или Три дела о потерянной производительности (видео) (+16, ✓45)
30.12 — рисуем морозные узоры на SQL (+24, ✓52)
13.01 — собираем «цепочки» с помощью window functions (+11, ✓40)
31.01 — пишем while-цикл прямо в запросе, или «Элементарная трехходовка» (+8, ✓97)
19.06 — 1000 и один способ агрегации (+12, ✓74)
28.07 — красивые отчеты по «дырявым» данным — GROUPING SETS (+8, ✓28)
05.09 — курсорный пейджинг с неподходящей сортировкой (+18, ✓64)
23.09 — PostgreSQL 13: happy pagination WITH TIES (+40, ✓45)
19.10 — ломаем мозг об дерево — упорядочиваем иерархию с рекурсией и без (+16, ✓62)
20.12 — вычищаем клон-записи из таблицы без PK (+13, ✓45)
25.12 — когда пасует VACUUM — чистим таблицу вручную (+21, ✓98)
15.01 — перенос значений SEQUENCE между базами PostgreSQL (+11, ✓43)
19.02 — находим бесполезные индексы (+19, ✓114)
19.03 — грамотно организовываем синхронизации и импорты (+11, ✓48)
20.05 — мониторинг базы PostgreSQL — кто виноват, и что делать (+23, ✓100)
27.05 — в погоне за пролетающими блокировками (+18, ✓41)
10.06 — классифицируем ошибки из PostgreSQL-логов (+9, ✓38)
15.06 — кто скрывается за блокировкой (+11, ✓48)
09.01 — БД мессенджера (ч.1): проектируем каркас базы (+3, ✓62)
09.01 — БД мессенджера (ч.2): секционируем «наживую» (+5, ✓67)
22.01 — PubSub почти бесплатно: особенности NOTIFY в PostgreSQL (+20, ✓76)
13.02 — Фантастические advisory locks, и где они обитают (+11, ✓34)
13.04 — Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB (+19, ✓78)
22.04 — Экономим копеечку на больших объемах в PostgreSQL (+11, ✓44)
03.06 — Как мы в СБИС автоматический расчет себестоимости делали (видео) (+7, ✓17)
17.08 — У меня зазвонил телефон. Кто говорит?.. Поможет «слон» (+10, ✓29)
25.08 — Телепортация тонн данных в PostgreSQL (+11, ✓60)
Если вам интересна какая-то конкретная тема, которую мы еще не затронули — напишите в комментариях. Вдруг мы с ней тоже уже сталкивались — расскажем подробно.
СБИС — это система полного цикла управления бизнесом — от кадрового учета, бухгалтерии, делопроизводства и налоговой отчетности, до таск-менеджмента, корпоративного портала и видеокоммуникаций. Поэтому каждый из 1 500 000 клиентов-организаций находит что-то полезное для себя и использует наши сервисы на постоянной основе — что дает ежемесячно более миллиона активных клиентов.
И все их данные надо где-то хранить и эффективно извлекать. Поэтому еще в далеком 2012 году мы сделали ставку на PostgreSQL, и теперь это основное хранилище данных наших сервисов:
- почти 9000 баз общим объемом 1PB
- свыше 200TB данных клиентов
- 1500 разработчиков работают с БД
Чтобы упорядочить накопившиеся знания, за минувший год мы опубликовали более 60 статей, в которых делимся своим реальным опытом, проверенным практикой «сурового энтерпрайза». Возможно, какие-то из них вы пропустили, поэтому под катом мы собрали дайджест, где каждый разработчик и DBA найдет что-то интересное для себя.
Для удобства все статьи разбиты на несколько циклов:
- Анализ запросов
Наглядно демонстрируем все тайныEXPLAIN [ANALYZE]. - SQL Antipatterns и оптимизация SQL
Понимаем как [не] надо решать те или иные задачи в PostgreSQL и почему. - SQL HowTo
Пробуем подходы к реализации сложных алгоритмов на SQL для развлечения и с пользой. - DBA
Присматриваем за базой, чтобы ей легко дышалось. - Прикладные решения
Решаем с помощью PostgreSQL конкретные бизнес-задачи.
Анализ запросов в PostgreSQL
Первое, чему необходимо научиться для разработки эффективных запросов — это оценивать их «ресурсоемкость». Для этого мы разработали и выложили в публичный доступ удобный сервис визуализации планов explain.tensor.ru.
26.11 — О чем молчит EXPLAIN, и как его разговорить (+38, ✓128)
...
Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса — к консультанту по PostgreSQL, почти всегда звучит одинаково: «Почему запросы выполняются на базе так долго?»
…
Но, как сказано в той же документации, «Понимание плана — это искусство, и чтобы овладеть им, нужен определённый опыт, …» Но можно обойтись и без него, если воспользоваться подходящим инструментом!
11.02 — Массовая оптимизация запросов PostgreSQL (видео) (+28, ✓131)
...
В докладе представлены некоторые подходы, которые позволяют следить за производительностью SQL-запросов, когда их миллионы в сутки, а контролируемых серверов PostgreSQL — сотни.
Какие технические решения позволяют нам эффективно обрабатывать такой объем информации, и как это облегчает жизнь обычного разработчика.
26.03 — Рецепты для хворающих SQL-запросов (видео) (+23, ✓143)
...
Многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.
В этом случае каждому отдельному разработчику не придется искать вариант оптимизации самостоятельно, опираясь исключительно на свой опыт — мы можем ему подсказать, что тут происходит, в чем может быть причина, и как можно подойти к решению. Что мы и сделали.
05.06 — Понимаем планы PostgreSQL-запросов еще удобнее (+25, ✓88)
...
Новые возможности explain.tensor.ru:
- Поддержка плана вместе с запросом, в том числе в JSON/YAML-форматах
- Расширенная визуализация Planning/Execution Time и I/O Timing.
- Новые фичи из PostgreSQL 13: Planning buffers, Incremental Sort
- Улучшения UI/UX: скриншоттинг, рекомендации на узлах плана, удаление из архива.
29.07 — Вооруженным глазом: наглядно о проблемах PostgreSQL-запроса (+32, ✓69)
...
Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом. В этом нам помогут различные варианты визуализации: сокращенный текстовый вид, круговая диаграмма, плитка, диаграмма выполнения.
10.08 — Правильно [c]читаем параллельные планы PostgreSQL (+17, ✓33)
...
Рассматриваем странности со временем исполнения узлов при активации параллельного выполнения.
В наш век закончившейся «гонки мегагерцев» и победивших многоядерных и многопроцессорных систем такое поведение является непозволительной роскошью и расточительностью. Поэтому, начиная с версии PostgreSQL 9.6, при отработке запроса часть операций может выполняться несколькими процессами одновременно.
03.09 — PostgreSQL Query Profiler: как сопоставить план и запрос (видео) (+13, ✓59)
...
Какие соображения помогают нам превращать сложно читаемый кусок лога сервера в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана.
29.10 — Анализируем «слона» по частям (+19, ✓24)
...
Очередные улучшения юзабилити explain.tensor.ru: «гистограммы» на узлах, полезная статистика для «мега»-планов, персональный архив и «генеалогия» планов.
SQL Antipatterns и оптимизация SQL
09.12 — CTE x CTE (+8, ✓35)
...
JOIN нескольких CTE — почти всегда зло. Небольшая заметка, как его можно избежать в конкретном примере.Тут надо вспомнить, что CTE Scan является аналогом Seq Scan — то есть никакой индексации, а только полный перебор.
…
В данном случае нам еще сильно повезло, что для соединения был выбран Hash Join, а не Nested Loop, поскольку тогда мы получили бы не один-единственный проход CTE Scan, а 10K!
10.12 — вредные JOIN и OR (+20, ✓108)
...
Разбираем на примере конкретного запроса несколько методик оптимизации и учимся использовать «ленивые» вычисления в PostgreSQL:
- оптимизируем
JOIN + LIMIT 1 BitmapOrvsUNION- «прячем под
CASE» сложные условия
11.12 — статистика всему голова (+10, ✓54)
...
В столбце ratio как раз показывается отношение «в разах» между планировавшимся на основании статистики и фактически прочитанным количеством записей. Чем больше это значение — тем хуже статистика отражает реальное положение дел в вашей таблице.
Приводит это к выбору неэффективного плана и, как следствие, дичайшей нагрузке на базу. Чтобы ее оперативно убрать, достаточно все-таки прислушаться к рекомендациям мануала и пройтиANALYZEпо основным таблицам.
12.12 — сизифов JOIN массивов (+14, ✓37)
...
Иногда возникает задача «склеить» внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными «по столбцам».Вспоминаем о расширенных возможностях работы с массивами:
WITH ORDINALITYMulti-argument UNNEST
19.12 — передача наборов и выборок в SQL (+8, ✓95)
...
Периодически у разработчика возникает необходимость передать в запрос набор параметров или даже целую выборку «на вход». Иногда попадаются очень странные решения этой задачи.Сравниваем разные варианты передачи данных в запрос:
- сериализованное представление массива/матрицы +
unnest - JSON +
json_populate_recordset/json_to_recordset TEMPORARY TABLE- переменные сессии
24.12 — обновляем большую таблицу под нагрузкой (+14, ✓126)
...
Как стоит поступить (а как точно не надо), если в «многомиллионной» активно используемой таблице PostgreSQL нужно обновить большое количество записей — проинициализировать значение нового поля или скорректировать ошибки в существующих записях? А при этом сохранить свое время и не потерять деньги компании из-за простоя.Почему «один
UPDATE» и ORDER BY + LIMIT — это печально для подобной задачи, а сегментное обновление и предварительно рассчитанные вычисления — в самый раз.20.01 — редкая запись долетит до середины JOIN (+18, ✓119)
...
Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.Разбираем на моделях способы оптимизации
Такие запросы любят «кушать» процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот — чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN…
JOIN + GROUP BY и JOIN + LIMIT с помощью CASE и LATERAL.27.01 — ударим словарем по тяжелому JOIN (+8, ✓107)
...
Итоговые выводы:
- если надо сделать JOIN с многократно повторяющимися записями — лучше использовать «ословаривание» таблицы
- если ваш словарь ожидаемо маленький и читать вы из него будете немного — можно использовать json[b]
- во всех остальных случаях hstore + array_agg(i::text) будет эффективнее
02.03 — меняем данные в обход триггера (+24, ✓61)
...
Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.Почему «быстро отключить и снова включить триггер» — плохая идея. Как его обойти с помощью переменных сессии.
10.03 — сказ об итеративной доработке поиска по названию (+17, ✓82)
...
Что вообще обычно подразумевает пользователь, когда говорит про «быстрый» поиск по названию? Почти никогда это не оказывается «честный» поиск по подстроке типаКак ищут строки: pg_trgm, FTS,... LIKE '%роза%'— ведь тогда в результат попадают не только'Розалия'и'Магазин Роза', но и'Гроза'и даже'Дом Деда Мороза'.
Пользователь же подразумевает на бытовом уровне, что вы ему обеспечите поиск по началу слова в названии и покажете более релевантным то, что начинается на введенное. И сделаете это практически мгновенно — при подстрочном вводе.
text_pattern_ops, btree + UNION ALL. И как можно неаккуратно все разломать: пейджинг, подзапросы, DISTINCT.12.03 — сражаемся с ордами «мертвецов» (+32, ✓106)
...
Как оградить свои
UPDATE'ы от лишней работы с диском и блокировок с помощью объединения операций и IS DISTINCT FROM.31.03 — вычисление условий в SQL (+26, ✓65)
...
SQL — это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе.
- ускоряем триггер за счет выноса проверки из функции в
WHEN. - оптимизируем
OR/AND-цепочку с помощьюCASE - упрощаем написание сложных условий
27.04 — навигация по реестру (+22, ✓74)
...
Все будет очень просто, на уровне Капитана Очевидность — делаем просмотр реестра событий с сортировкой по времени.
- плохо: считать сегменты на бизнес-логике
- плохо: использовать
LIMIT + OFFSET - хорошо: использовать «курсоры», но делать это аккуратно
14.05 — насколько глубока кроличья нора? пробежимся по иерархии (+19, ✓83)
...
В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений «предок — потомок» — это и организационная структура предприятия (все эти филиалы, отделы и рабочие группы), и каталог товаров, и участки работ, и география точек продаж, ...Пишем сложный запрос, чтобы извлекать минимум данных при проходах по «дереву».
24.06 — подозрительные типы (+40, ✓60)
...
Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике.
28.06 — накручиваем себе проблемы (+21, ✓56)
...
Рассматриваем причины накрутки
serial при ON CONFLICT и счетчика транзакций при ROLLBACK.08.07 — SELF JOIN vs WINDOW (+14, ✓32)
...
Ускоряем запрос в 100 раз с помощью оконных функций на примере мониторинга блокировок.
14.07 — Unreal Features of Real Types, или Будьте осторожны с REAL (+9, ✓10)
...
Я решил бегло пробежаться по коду доступных мне SQL-запросов, чтобы посмотреть, насколько часто в них используется тип REAL. Достаточно часто используется, как оказалось, и не всегда разработчики понимают опасности, стоящие за ним. И это несмотря на то, что в Интернете и на Хабре достаточно много хороших статей про особенности хранения вещественных чисел в машинной памяти и о работе с ними. Поэтому в этой статье я постараюсь применить такие особенности к PostgreSQL, и попробую «на пальцах» рассмотреть связанные с ними неприятности, чтобы разработчикам SQL-запросов было легче избежать их.
04.08 — «Должен остаться только один!» (+24, ✓80)
...
Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использованияGROUP/DISTINCTиLIMITвместе с ними.
Вот если вы написали в запросе «сначала соедини эти таблички, а потом выкинь все дубли, должен остаться только один экземпляр по каждому ключу» — именно так и будет работать, даже если соединение вовсе не было нужно.
20.08 — уникальные идентификаторы (+21, ✓67)
...
Рассматриваем эффективность и проблемы различных способов получить уникальные идентификаторы в базе и их проблемы:
- таблица счетчиков
- объект
SEQUENCE - псевдотип
serial GENERATED-столбцы- генерируемый UUID
- скрытые системные поля:
tableoid/ctid/oid - «честное» время
clock_timestamp
01.10 — «Бесконечность — не предел!», или Немного о рекурсии (+18, ✓47)
...
Рекурсия — очень мощный и удобный механизм, если над связанными данными делаются одни и те же действия «вглубь». Но неконтролируемая рекурсия — зло, которое может приводить или к бесконечному выполнению процесса, или (что случается чаще) к «выжиранию» всей доступной памяти.
СУБД в этом отношении работают по тем же принципам — "сказали копать, я и копаю". Ваш запрос может не только затормозить соседние процессы, постоянно занимая ресурсы процессора, но и «уронить» всю базу целиком, «съев» всю доступную память. Поэтому защита от бесконечной рекурсии — обязанность самого разработчика.
07.10 — убираем медленные и ненужные сортировки (+27, ✓91)
...
«Просто так» результат SQL-запроса возвращает записи в том порядке, который наиболее удобен серверу СУБД. Но человек гораздо лучше воспринимает хоть как-то упорядоченные данные — это помогает быстро сравнивать соответствие различных датасетов.Учимся опознавать типовые кейсы и делаем запрос чуть быстрее:
Поэтому со временем у разработчика может выработаться рефлекс «Дай-ка я на всякий случай это вот отсортирую!»
- нехватка work_mem
- сортировка уже отсортированного
- вложенная отладочная сортировка
- Index Scan вместо сортировки
- UNION ALL вместо сортировки
- сортировки для оконных функций
10.11 — работаем с отрезками в «кровавом энтерпрайзе» (+27, ✓64)
...
Давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:
- поиск отрезков, пересекающих точку/интервал
- слияние отрезков по максимальному перекрытию
- подсчет количества отрезков в каждой точке
18.11 — DBA-детектив, или Три дела о потерянной производительности (видео) (+16, ✓45)
...
Дедукция и индукция помогут нам вычислить, что же все-таки хотел получить от СУБД разработчик, и почему это получилось не слишком оптимально. Итак, сегодня нас ждут:
- Дело о непростом пути вверх
Разберем в live-видео на реальном примере некоторые из способов улучшения производительности иерархического запроса.- Дело о худеющем запросе
Увидим, как можно запрос упростить и ускорить в несколько раз, пошагово применяя стандартные методики.- Дело о развесистой клюкве
Восстановим структуру БД на основании единственного запроса с 11JOINи предложим альтернативный вариант решения на ней той же задачи.
SQL HowTo
30.12 — рисуем морозные узоры на SQL (+24, ✓52)
...
Немного SQL-магии: математика, рекурсия, псевдографика.
13.01 — собираем «цепочки» с помощью window functions (+11, ✓40)
...
Иногда при анализе данных возникает задача выделения «цепочек» в выборке — то есть упорядоченных последовательностей записей, для каждой из которых выполняется некоторое условие.
Традиционные решения предусматривают разные варианты «self join», когда выборка соединяется с собой же, либо использование некоторых фактов «за пределами данных» — например, что записи должны иметь строго определенный шаг (N+1, «за каждый день», ...).
Первый вариант зачастую приводит к квадратичной сложности алгоритма от количества записей, что недопустимо на больших выборках, а второй может легко «развалиться», если каких-то отсчетов в исходных данных вдруг не окажется.
Но эту задачу нам помогут эффективно решить оконные функции в PostgreSQL.
31.01 — пишем while-цикл прямо в запросе, или «Элементарная трехходовка» (+8, ✓97)
...
Периодически возникает задача поиска связанных данных по набору ключей, пока не наберем нужное суммарное количество записей.
В статье рассмотрим реализацию на PostgreSQL «наивного» варианта решения такой задачи, «поумнее» и совсем сложный алгоритм «цикла» на SQL с условием выхода от найденных данных, который может быть полезен как для общего развития, так и для применения в других похожих случаях.
19.06 — 1000 и один способ агрегации (+12, ✓74)
...
Рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.
- совместные агрегаты
- вложенные запросы
FILTER-агрегаты- агрегаты от условия
- агрегация в массив
DISTINCT + OVER- сложный агрегат с помощью рекурсии
28.07 — красивые отчеты по «дырявым» данным — GROUPING SETS (+8, ✓28)
...
В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций иGROUPING SETS.
Чтобы сэкономить производительность дисковой подсистемы нашей базы и занимаемый базой объем, постараемся как можно больше данных представить в виде NULL — их хранение практически «бесплатно», поскольку занимает лишь бит в заголовке записи.
05.09 — курсорный пейджинг с неподходящей сортировкой (+18, ✓64)
...
Пусть у нас есть реестр документов, с которым работают операторы или бухгалтеры. Традиционно, при подобном отображении используется или прямая (новые снизу) или обратная (новые сверху) сортировка по дате и порядковому идентификатору, назначаемому при создании документа —ORDER BY dt, idилиORDER BY dt DESC, id DESC.
Но что если пользователю зачем-то захотелось «нетипичного» — например, отсортировать одно поле «так», а другое «этак» —ORDER BY dt, id DESC? Но второй индекс мы создавать не хотим — ведь это замедление вставки и лишний объем в базе.
Можно ли решить эту задачу, эффективно используя только индекс(dt, id)?
23.09 — PostgreSQL 13: happy pagination WITH TIES (+40, ✓45)
...
Используем новые возможности PostgreSQL 13 для упрощения организации постраничной навигации по реестру.
19.10 — ломаем мозг об дерево — упорядочиваем иерархию с рекурсией и без (+16, ✓62)
...
… чтобы для вывода упорядочить элементы дерева в соответствии с иерархией, уж точно придется воспользоваться рекурсией! Или нет? Давайте разберемся, а заодно решим на SQL пару комбинаторных задач.
DBA
20.12 — вычищаем клон-записи из таблицы без PK (+13, ✓45)
...
Случаются ситуации, когда в таблицу без первичного ключа или какого-то другого уникального индекса по недосмотру попадают полные клоны уже существующих записей.
Например, пишутся в PostgreSQL COPY-потоком значения хронологической метрики, а потом внезапный сбой, и часть полностью идентичных данных приходит повторно.
Как избавить базу от ненужных клонов?
25.12 — когда пасует VACUUM — чистим таблицу вручную (+21, ✓98)
...
VACUUMможет «зачистить» из таблицы в PostgreSQL только то, что никто не может увидеть — то есть нет ни одного активного запроса, стартовавшего раньше, чем эти записи были изменены.
А если такой неприятный тип (продолжительная OLAP-нагрузка на OLTP-базе) все же есть? Как почистить активно меняющуюся таблицу в окружении длинных запросов и не наступить на грабли?
15.01 — перенос значений SEQUENCE между базами PostgreSQL (+11, ✓43)
...
Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение «автоинкремент»-поля типа serial, если в таблице могли быть какие-то удаления, и «просто подставить max(pk)» уже не подходит?
19.02 — находим бесполезные индексы (+19, ✓114)
...
Регулярно сталкиваюсь с ситуацией, когда многие разработчики искренне полагают, что индекс в PostgreSQL — это такой швейцарский нож, который универсально помогает с любой проблемой производительности запроса. Достаточно добавить какой-нибудь новый индекс на таблицу или включить поле куда-нибудь в уже существующий, а дальше (магия-магия!) все запросы будут эффективно таким индексом пользоваться.
Во-первых, конечно, или не будут, или не эффективно, или не все. Во-вторых, лишние индексы только добавят проблем с производительностью при записи.
19.03 — грамотно организовываем синхронизации и импорты (+11, ✓48)
...
При сложной обработке больших наборов данных (разные ETL-процессы: импорты, конвертации и синхронизации с внешним источником) часто возникает необходимость временно «запомнить», и сразу быстро обработать что-то объемное.
Чтобы справиться с ними в PostgreSQL (да и не только в нем), можно использовать некоторые возможности для оптимизаций, которые позволят обработать все быстрее и с меньшим расходом ресурсов.
20.05 — мониторинг базы PostgreSQL — кто виноват, и что делать (+23, ✓100)
...
На что обращать внимание при мониторинге PostgreSQL-базы и как трактовать полученные данные:
- состояние соединений
- блокировки
- transactions per second (TPS)
- количество операций над записями
- использование кэша данных
- самый длительный запрос/транзакция
27.05 — в погоне за пролетающими блокировками (+18, ✓41)
...
Шансов поймать блокировки «в моменте» крайне мало, да и длиться они могут всего по несколько секунд, но ухудшая при этом плановое время выполнения запроса в десятки раз. А хочется-то не сидеть и ловить происходящее в онлайн-режиме, а в спокойной обстановке разобраться постфактум,кого из разработчиков покаратьв чем именно была проблема — кто, с кем и из-за какого ресурса базы вступил в конфликт.
Но как? Ведь, в отличие от запроса с его планом, который позволяет детально понять, на что пошли ресурсы, и сколько времени это заняло, подобных наглядных следов блокировка не оставляет после себя…
Разве что короткую запись в логе:А давайте попробуем зацепиться именно за нее!process ... still waiting for ...
10.06 — классифицируем ошибки из PostgreSQL-логов (+9, ✓38)
...
Если мы не хотим потом хвататься за голову, то возникающие в логах PostgreSQL ошибки недостаточно просто «считать поштучно» — их надо аккуратно классифицировать. Но для этого нам придется решить нетривиальную задачу индексированного поиска регулярного выражения, наиболее подходящего для строки.
15.06 — кто скрывается за блокировкой (+11, ✓48)
...
Научимся трактовать собранные блокировки и узнавать, кто именно может скрываться за конкретной матрицей конфликтов, и почему результат выглядит именно так.
Решения для PostgreSQL
09.01 — БД мессенджера (ч.1): проектируем каркас базы (+3, ✓62)
...
Как можно перевести бизнес-требования в конкретные структуры данных на примере проектирования «с нуля» базы для мессенджера.
09.01 — БД мессенджера (ч.2): секционируем «наживую» (+5, ✓67)
...
Мы удачно спроектировали структуру нашей PostgreSQL-базы для хранения переписки, прошел год, пользователи активно ее наполняют, вот в ней уже миллионы записей, и… что-то все начало подтормаживать.
Дело в том, что с ростом объема таблицы растет и «глубина» индексов — хоть и логарифмически. Но со временем это заставляет сервер для выполнения тех же задач чтения/записи обрабатывать в разы больше страниц данных, чем в начале.
Вот тут на помощь и приходит секционирование. Мы рассмотрим не конкретные скрипты для реализации секционирования «в железе», а сам подход — что и как стоит «порезать на дольки», и к чему такое желание приводит.
22.01 — PubSub почти бесплатно: особенности NOTIFY в PostgreSQL (+20, ✓76)
...
Если ваши микросервисы уже используют общую базу PostgreSQL для хранения данных, или ей пользуются несколько экземпляров одного сервиса на разных серверах, можно относительно «дешево» получить возможность обмена сообщениями (PubSub) между ними без интеграции в архитектуру Redis, RabbitMQ-кластера или встройки в код приложения другой MQ-системы.
Для этого мы не будем писать сообщения в таблицы БД, поскольку это вызывает слишком большие накладные расходы сначала на запись передаваемого, а потом еще и на зачистку от уже прочитанного.
Передавать и получать данные мы станем с помощью механизма NOTIFY/LISTEN, а модельную реализацию соберем для Node.js.
13.02 — Фантастические advisory locks, и где они обитают (+11, ✓34)
...
В PostgreSQL существует очень удобный механизм рекомендательных блокировок, они же — advisory locks. Мы в «Тензоре» используем их во многих местах системы, но мало кто детально понимает, как конкретно они работают, и какие проблемы можно получить при неправильном обращении.
13.04 — Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB (+19, ✓78)
...
Рассматриваем традиционные подходы масштабирования производительности на конкретном примере:
- Секционирование
- Эволюция и рефакторинг БД
- «Размазываем» пиковую нагрузку
- Кэшируем, что можно
«Терабайт-в-сутки» только звучит страшно. Если вы все делаете правильно, то это всего лишь 2^40 байт / 86400 секунд = ~12.5MB/s, что держали даже настольные IDE-винты. :)
А если серьезно, то даже при десятикратном «перекосе» нагрузки в течение суток, вы спокойно можете уложиться в возможности современных SSD.
22.04 — Экономим копеечку на больших объемах в PostgreSQL (+11, ✓44)
...
Продолжая тему записи больших потоков данных, поднятую предыдущей статьей про секционирование, в этой рассмотрим способы, которыми можно уменьшить «физический» размер хранимого в PostgreSQL, и их влияние на производительность сервера.
Речь пойдет про настройки TOAST и выравнивание данных. «В среднем» эти способы позволят сэкономить не слишком много ресурсов, зато — вообще без модификации кода приложения.
03.06 — Как мы в СБИС автоматический расчет себестоимости делали (видео) (+7, ✓17)
...
Как суровую прагматику требований бизнеса перенести на разработку высоконагруженных сервисов, как бороться с конкурентным доступом к данным, как это все аккуратно обходить и при этом не «отстрелить себе ногу».
17.08 — У меня зазвонил телефон. Кто говорит?.. Поможет «слон» (+10, ✓29)
...
Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро — тогда появляется масса возможностей.
25.08 — Телепортация тонн данных в PostgreSQL (+11, ✓60)
...
Выжимаем максимум пропускной способности из PostgreSQL:
- Как балансировать «писателей» и управлять соединениями на бизнес-логике?
- Как настроить СУБД и ОС?
- Как избавиться от блокировок?
Если вам интересна какая-то конкретная тема, которую мы еще не затронули — напишите в комментариях. Вдруг мы с ней тоже уже сталкивались — расскажем подробно.
