Работа с 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

Вышла Postgres 17

С небольшой задержкой добрался посмотреть, что там интересного в новой версии Postgres 17.

Не буду спойлерить :)

Для детального изучения – release notes.

Я ещё посмотрел хороший доклад с конференции PGConf, где Павел Лузанов проходится по основным изменениям и даёт свои комментарии: где-то рассказывает подробнее о том, что ещё хотелось бы докрутить, где-то о причинах нововведений, в общем, живенько получилось.

#database #skills

Наконец-то дочитал отличную статью What Goes Around Comes Around... And Around..., посвящённую изменениям в области баз данных за последние 20 лет.

Статья поделена на два раздела: Модели данных и языки запросов и Архитектура баз данных.

Модели данных и языки запросов
В этой части рассматривается развитие ключевых моделей данных, каждая из которых внесла свой вклад в формирование современных систем управления базами данных. Среди них выделяются MapReduce, Key-Value хранилища, документоориентированные базы, колоночные базы, текстовые поисковые движки, а также векторные и графовые базы данных. Как правило, эти подходы возникали как ответы на конкретные задачи: ускорение обработки больших данных, обеспечение гибкости хранения слабоструктурированной информации, улучшение аналитики и моделирование сложных взаимосвязей.

Например, системы MapReduce сыграли важную роль в обработке огромных объёмов данных в распределённых средах, а Key-Value хранилища предоставили разработчикам инструмент для быстрого доступа к данным с минимальными накладными расходами. Документоориентированные базы данных предложили удобный способ работы с данными в формате JSON. В свою очередь, текстовые поисковые системы оказались незаменимыми в задачах индексации больших массивов текстовой информации, а графовые базы данных предоставили мощные инструменты для анализа сложных связей, например, в социальных сетях или рекомендательных системах.

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

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

Об архитектурах баз данных будет в следующем посте.
#database

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

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

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

Data Lakes и Lakehouses
Архитектуры Data Lake предоставляют возможность хранить данные в их необработанном виде, позволяя использовать их для самых разнообразных аналитических задач. Однако отсутствие управления метаданными и структурированности часто превращает такие системы в хаотичные хранилища. Lakehouses добавляют к Data Lake возможности традиционных аналитических баз данных, такие как структурированность, управление данными и поддержка транзакций, что делает их более универсальными. Их успех связан с решением проблем Data Lake, таких как отсутствие контроля над данными и низкая эффективность аналитики.

NewSQL системы
NewSQL системы появились как ответ на ограничения традиционных реляционных баз данных в масштабируемости и производительности. Эти системы предлагают преимущества транзакционных баз данных, такие как ACID-свойства, но при этом поддерживают горизонтальное масштабирование и высокую производительность для OLTP-нагрузок.
Внедрение NewSQL идёт медленно из-за высокой стоимости миграции с существующих систем и ограничений первых поколений таких решений.

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

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

#database

Databases in 2024: A Year in Review

Да, ещё один пост о базах :)

Любопытная, лёгкая и иногда захватывающая статья, посвящённая ретроспективе в области баз данных за 24 год.

Начинает автор с забавного: как Redis поменял лицензию более строгую, а Elasticsearch, наоборот, откатился на более открытую лицензию. Причиной всему облачные провайдеры, типа AWS, которые берут продукты и начинают их поставлять как сервис, тем самым мешая зарабатывать деньги компании-разработчику базы.

От себя добавлю, что уже давно есть более интересные альтернативы редису, о которых мы писали: DragonFly и KeyDB.

Продолжая статью, автор переходит в категорию "захватывающее" – о борьбе Snowflake и Databricks. Эти взаимоотношения можно описать, как в той самой поговорке про жабу и гадюку. В результате такой конкуренции продукты улучшаются и нам, как потребителям, это, конечно, на руку.

В этом году особенно часто на моём радаре появлялась DuckDB, которая упоминается в статье. Интересная база данных для аналитических запросов. Легко поднимается, написано куча расширений для Postgres, в результате достаточно просто начать использовать.

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

#database

Ответ на задачу — проектируем динамическую фильтрацию

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

В задачах на проектирование чего-либо интервьюера интересует не столько сам ответ, сколько ход ваших мыслей. Вы можете не дойти до правильного ответа, или дойти с подсказкой. Рассмотрим потенциальные решения задачи и покритикуем их:
💡 Давайте присылать все данные на фронт и фильтровать там.
🚫 1кк записей передавать нецелесообразно. Более того, даже хранить фильтры на фронте не выйдет, так как они динамические и определяются конкретной выборкой. В любом случае, фильтровать должен бекенд.

💡В postgres можно спроектировать схему для хранения фильтров в связке со списком товаров, к которым эти фильтры можно применять.
🚫 Здесь не стали приводить конкретики, но отметим, что при таком подходе будут проблемы с динамическим обновлением счетчиков. А ещё такое решение несёт сложную ментальную нагрузку на разработчика.

💡Можно взять Elasticsearch или Manticore Search и подобное реализовать там.
❓По задаче требуется остаться с PostgreSQL и не вводить доп сущностей. Если можно поднять эластик, решение нормальное и можно обсудить детали.

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

Пример реализации такого решения с использованием полнотекстового поиска в postgres приведен в статье Faceted search using PostgreSQL full text search.

#database #skills #резюме

"All you need is Postgres" – наверняка слышали этот боевой клич

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

Репозиторий оказалася очень залипательным, можно походить по ссылочкам, узнать какие штуки бывают. Так, например, узнал про PGlite — Postgres in WASM. Просто берёшь и запускаешь базу прямо в браузере. Без всяких линуксовых виртуальных машин. Ну очень интересно!

Конечно, не стоит пытаться решать все проблемы с помощью Postgres, но ситуации бывают разные и знать о таких штуках может быть полезно.

#database

Postgres — как делать не надо

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

Вот несколько интересных моментов:
— Не используйте char(n) — у нас был отдельный пост о разнице между char, varchar и text.
— Не используйте serial
— Не используйте NOT IN
— Не используйте timestamp без timezone

#database

Life Altering Postgresql Patterns

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

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

🔹 UUID вместо автоинкремента — если работаешь с распределёнными системами или API, лучше сразу использовать uuid DEFAULT gen_random_uuid(). Избавит от проблем с конфликтами ID

🔹 created_at / updated_at — каскадное удаление может привести к неожиданным потерям данных. Лучше контролировать процесс вручную

🔹ON DELETE RESTRICT вместо CASCADE — защищает от случайного удаления связанных данных. Лучше удалять вручную при необходимости

🔹Используйте схемы — не нужно всё пихать в public, схемы помогают логически разделить данные

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

🔹Таблицы в единственном числе — user вместо users. Логичнее: одна строка = один объект. Хотя наверняка найдутся сторонники другого подхода

🔹Soft delete вместо удаления — автор убеждён, что хранение дешевле, чем восстановление данных, и почти всегда рекомендует soft delete (deleted_at TIMESTAMP NULL)

🔹 JSONB вместо сложных JOIN'ов — удобно для метаданных и настроек, если структура может меняться. Но я бы тут осторожно подходил к такому решению. Например, что будет со старыми данными, если формат json поменяется? А не будет ли проблем с TOAST? На эти темы у нас были отдельные посты: раз, два

🔹Понятные имена join-таблиц — просто объединяйте имена связываемых таблиц и не городите чего-то этакое

#database

Про шардирование Postgres и иллюзию ACID

Известно, что шардирование PostgreSQL – задача не из простых. Ребята из YDB в своей статье подсветили один важный нюанс.

Пока вы работаете с одним шардом – всё как в обычном Postgres. Но как только транзакция затрагивает больше одного шарда – вы рискуете столкнуться с неожиданностями. Например, можно получить ситуацию, когда данные на одном шарде уже обновились, а на другом – ещё нет. И тогда тот, кто читает эти данные, может увидеть нечто странное. Как в примере из статьи: Вася проверяет семейный счёт и видит, что на нём 150 рублей, хотя на самом деле должно быть 200. Просто один из шардов уже принял изменения, а второй ещё не успел.

Это происходит потому что двухфазный коммит даёт только атомарность, а не изоляцию. Распределённый снепшот тут не делается, и гарантии ACID превращаются в AC_D.

В общем, если планируете делать широкие транзакции не забывайте об этом факте.

#database