Следим за 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

TimescaleDB для хранения временных рядов

В статье ребята рассказывают, как и почему они выбрали TimescaleDB для хранения time series данных. По сути, это такая надстройка над Postgres.

TimescaleDB они сравнивают с ныне популярным ClickHouse и не столь популярным QuestDB. В статье приводятся бенчмарки, важные для решения задачи. В реальных задачах немаловажными являются не только технические, но и бизнесовые аргументы, такие как наличие экспертизы – их авторы также упоминают.

В общем неплохая статья для развития насмотренности.

А ещё именно для подобной задачи хорошо подойдет практика написания design doc.

#database

Порядок имеет значение

Захватывающая статья посвящена оптимизации хранения данных в Postgres. Оказывается, порядок столбцов в таблице влияет на занимаемое место на диске. Вот такие вот дела.

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

В статье на конкретных примерах демонстрируется, как меняется размер данных в зависимости от порядка столбцов. Отдельное внимание уделяется NUMERIC и TEXT. Эти типы данных требуют особого подхода, так как имеют переменную длину.

В итоге, для оптимизации хранения данных нужно располагать столбцы в таблице по порядку: от больших типов данных (BIGINT, TIMESTAMPTZ) к меньшим (INT, SMALLINT, BOOLEAN) и завершать переменными типами (NUMERIC, TEXT).

Вообще звучит неплохо. Благодаря подобным махинациям можно сэкономить до 10% памяти.

#database #skills

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

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

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

Мы обновили подборку всех наших постов по базам данных. Там много интересного.

UPD: в комментарии рассказали о еще одном полезном инструменте.

#tools #database

Боль от распухающей базы данных

Интересный кейс от ребят из Figma. Некоторое время назад они сидели на одной жирной Postgres.

Чтобы дать себе время на разработку целевого решения, ребята сначала подстелили сначала соломку:
– Накинули железа (ну конечно, а что ещё остается делать)
– Создали несколько read реплик
– Добавили PgBouncer в свою архитектуру
– Для новых фичей стали стараться использовать отдельные базы

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

Самая интересная задача тут – мигрировать данные без даунтайма.
Сделано это было в несколько шагов:
1. Подготовили клиентские приложения для работы с несколькими базами данных.
2. Настроили логическую репликацию таблиц для копирования данных из одной базы в другую. Тут, кстати, ещё интересный нюанс, логическая репликация может занимать ооооочень продолжительное время из-за долгого обновления индексов, поэтому на целевой бд индексы были удалены перед началом копирования и восстановлены после завершения копирования.
3. Короткая пауза активности на основной бд для полной синхронизации данных.
4. Назначение целевой бд как основной и перенаправление запросов к ней.

В общем, отличная статья в копилку насмотренности технологических решений.

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

#skills #database