Следим за Postgres

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

Автор провел большую работу и собрал в статье набор полезных запросов для postgres. Статья будет отличной шпаргалкой, потому что многие запросы без пол литра не разобрать.

Для отслеживания размера БД автор приводит запросы для получения:
– размера табличных пространств
– размера базы данных
– размера схем в базе данных
– размера таблиц

Это всё цветочки и, как правило, можно посмотреть в любом клиенте.
А когда не проходит какой-нибудь запрос или миграция виснет, тут уже становится интереснее. Где-то что-то блокируется.

Для отслеживания блокировок автор приводит запрос для получения заблокированных запросов и снятия блокировок.

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

Рекомендуем также заглянуть в комментарии, там делятся интересным опытом. Например, запрос для отладки блокировок. Или совсем специфичное – запрос, который позволит прикинуть сколько занимает одна запись на диске.

#skills #database

Где бы ещё сохранить данные?

Greenplum – распределённая база данных на основе postgres с открытым исходным кодом.

Статья Introduction To Greenplum Architecture будет отличным введением для понимания, что это за зверь такой.

Автор начинает с довольно скучного введения, так что сразу переходите к разделу Greenplum Overall Architecture.

По сути, Greenplum – это кластер баз данных, состоящий из отдельных Postgres. Кластер состоит из:
– master-ноды, которая является входной точкой для всей БД и обеспечивает единый интерфейс для взаимодействия с кластером
– standby-ноды – резервного мастера для обеспечения высокой доступности
– нескольких segment-нод – рабочих лошадок, где хранятся и обрабатываются данные

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

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

#skills #database

Postgres – как хранить строки?

Если каждый раз при проектировании базы данных задаётесь вопросом, чем отличаются char, varchar или text и какой тип данных использовать для хранения строк, то эта заметка для вас.

Автор рассказывает о каждом типе данных, отмечает важные моменты:
– тип данных не влияет на производительность
– при ограничении длины текстового поля лучше использовать check constraint

В сухом остатке: используйте для хранения строк text.

#database

Работа с json в PostgreSQL

Цикл супер-полезных практических заметок о работе с json в PostgreSQL.

Затрагиваются вопросы:
— чем json отличается от jsonb
— как парсить json. В том числе: извлечение поля из json-объекта, получение тип данных, проверка существования поля или значения
— как разложить json по колонкам
— как конвертировать в json. В том числе: перевод строки в json, создание json-объекта из наборов ключей и значений
— как индексировать json. В том числе: всю колонку или отдельно взятое поле
— как сделать красивый вывод json
— как изменять json. В том числе: конкатенация двух json, удаление определенных полей или null-значений

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

#database

О производительности Postgres

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

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

– логировать медленные запросы – можно настроить логирование запросов, которые выполнялись дольше заданного времени. В ту же сторону – логировать EXPLAIN-планы медленных запросов

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

#database

TOAST – проблемы откуда не ждали

Для хранения длинных записей в Postgres используется механизм TOAST.

Колонки с длинными значениями не хранятся в самой таблице. Если значение больше 2 Кб, то данные разбиваются на чанки и отправляются в связные тост-таблицы, скрытые от пользователя. А в исходной таблице хранится специальный указатель на чанки в тост-таблице.

И нам этом можно было бы и закончить. Просто интересный факт, связанный с особенностью хранения данных.

Но есть нюансы. Отметим практически значимые.
– В тост-таблице не может быть больше, чем 2^32 значений, то есть можно просто упереться в верхнее значение
– TOAST не поддерживает UPDATE. То есть каждая операция обновления вашего большого JSON приводит к INSERT в тост-таблицу и её распуханию.
– Независимо сколько в вашей таблице колонок, тост-таблица всегда одна.

Но дело не только в количестве значений в тост-таблице. Сам механизм накладывает определенные издержки.
JSON в Postgres уже давно не в новинку и активно используется. И, как правило, обсуждения проблем TOAST крутятся именно вокруг JSON полей.

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

Во второй же рассказывают, как оптимизировали JSON, чтобы повысить его производительность и уменьшить влияние TOAST. Также дают пару советов: всегда использовать JSONB и никогда не хранить в нём ID.

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

#skills #database

Посмотрите на keydb

Мы уже упоминали keydb – интересную альтернативу redis, на которую можно бесшовно переехать.

Хоть переезд и обещают бесшовным, но логичен вопрос – зачем? В статье ребята рассказывают о двух киллер-фичах, ради которых можно устроить переезд.

Речь идёт о режимах active replica и multi-master. Они позволяют получить совместимый с Redis распределённый отказоустойчивый KeyDB, но при этом писать в любую ноду, читать из любой ноды. Это как раз то, чего в redis будет сложновато добиться.

Пожалуй, самая интересная и ценная часть статьи – это проблемы, с которыми можно всё-таки столкнуться, используя keydb и тех случаях, когда keydb вам, вероятно, не подойдёт.
Авторы столкнулись:
– c неожиданным поведением некоторых команд
– c out of memory, когда городили хитроумный кластер с множеством мастеров и реплик
– с проблемой, когда всё ломала клиентская библиотека

#skills #database

Временные интервалы в postgres

Недавно столкнулись с задачей анализировать временные интервалы и подготавливать данные, находящиеся в Postgres, для построения графиков.

В статье автор на реальных примерах демонстрирует интересные возможности postgres:
– функция generate_series генерирует различные хитрые ряды
– функция date_bin позволяет группировать временные метки по различным хитрым интервалам
– функция width_bucket считает количество значений в динамических интервалах
– на закуску unnest вместе с ORDINALITY

Все описанные в статье примеры можно легко воспроизвести.

Ещё из интересного, автор для своих примеров использует специальное расширение postgis для написания питоновского кода. Так, конечно, делать нельзя, но может оказаться полезным для проведения экспериментов или в демонстрационных целях.

#skills #database

Говоря о поисковых движках, мы писали о том, как затаскивали Manticore Search на проект. Начиналось всё очень даже позитивно. Нам нужен был разухабистый поиск по понятным, но сложным правилам.

Закончилось всё не так позитивно. Через некоторое время мы словили критический для нас баг, о котором доблестно отчитались в баг репорте на GitHub.

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

А вывод из этой истории такой: экспериментировать технологиями хорошо, полезно и нужно. Но в продакшн стоит брать проверенные, «скучные» технологии.

#skills #database

Оптимизатор join в PostgreSQL

Тема из разряда: вон оно как бывает в Postgres. Ну, или, может, об этом все знают, а я один такой тёмный.

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

Но бывают случаи, когда нужно сделать большое количество джоинов. Например, когда подбиваем какой-нибудь отчёт.

Оказывается, что если сделать больше 8 джоинов (параметр по умолчанию), то оптимизатор не будет строить планы выполнения запросов, а просто выполнит все джоины в том порядке, в каком они написаны.

Число, после которого оптимизатор сдаётся и ничего не делает, задается параметром join_collapse_limit. С ним можно поэкспериментировать с помощью SET join_collapse_limit и посмотреть, как будет меняться время работы EXPLAIN запроса.

В тему нюансов работы с Postgres у нас был пост, чем отличаются: char, varchar, и text и какие бывают нежданчики в json-полях.

#database