Рекомендации при работе с PostgreSQL03.03.2024 22:00

Приветствую, current_user ()!

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

Это не правила, а скорее полезные рекомендации, которые помогут Вам избежать неприятных ситуаций в будущем. Представленный опыт — мой собственный, и, конечно же, он может быть немного субъективным, так как я относительно далёк от статуса супер-разработчика :)

Тут будут рассмотрены пункты, которые касаются именно разработки и её организации (подходу). Мы не будет затрагивать конфигурацию серверов, бэкапы/реплики/шарды и прочее.

Я попытаюсь быть кратким. Просто коротко и по пунктам:

Нормализация структуры

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

Используйте отдельные схемы для разных модулей

Скажем, вам необходимо реализовать систему рассылки пуш-уведомлений на android-устройства клиентов. Вы пишете сервис, который будет работать с FCM. Пишете функции в вашей БД, которые будут выдавать списки token-текст (да там много полей вообще).

Так вот, необходимо, чтобы такие ручки для разных модулей (бэкендов) лежали в отдельных схемах.

вот такой бредовый пример:

Используйте группы ролей и назначайте гранты именно на роли

Предположим, что вы реализовали пункт выше, теперь ваш сервис должен подключаться к БД и крутить свои запросы. Но под каким пользователем? Не будет же он бегать под postgres или admin?)
Мы создадим для него отдельного юзера и пускай логинится под ним. Хмм, но ведь ещё права дать надо? Ну дадим.

Рекомендую использовать следующий подход: создавайте роли, настраивайте их как групповые политики и давайте разрешения на именно на роли.

Например вот такие роли:

• api_role — для доступа к внутренним апи-функциям

• client_api_role — для доступа мобильных клиентов

• admin_api_role — для доступа сайта (ну например это админка)

• notify_role — для доступа системам уведомлений

• developer — то что доступно разработчикам (например какая-то часть таблиц доступна на чтение на проде)

  А потом уже создавайте пользователей и подключайте их к ролям. А гранты выдавайте именно на роли. Не делайте их персональными.

Пример для группы `Разработчики`

В любом случае придерживайтесь следующих правил:

1. Для каждого нового бэкенда (приложения) — свой юзер

2. Админ должн быть только у Админа кластера + у некоторых разработчиков (например техлид)

Используйте расширения

Postgresql — это мощная СУБД. Но и она не может быть универсальной. Не надо полагать, что всё делается штатными средствами postgresql. Так-же не надо пробовать писать свои приблуды/системы чего-либо.

Много готовых и хороших инструментов уже давно есть и широко используются. Берём, изучаем и применяем — ничего сложного.

Не знаете, что именно лучше использовать в том или ином случае — пробуем тестировать, раскручивать и смотреть всё, или-же можно обратиться за помощью, например на HabrQA или ТГ канал по PostgreSQL

Используйте разные схемы для разных расширений

И, возвращаясь, к первой рекомендации — для каждого расширения используйте свою схему (или хотя-бы все расширения в одну общую схему расширений) :

-- создадим схему
CREATE SCHEMA hstore;

-- установим расширение в его схему
CREATE EXTENSION hstore WITH SCHEMA hstore;

-- не забываем сразу подключить схему в serach_path
ALTER SYSTEM SET search_path = '$user', 'public', 'hstore';

Используйте pg_notify

Предположим, что у Вас есть бэкенд, который должен брать данные из БД и их обрабатывать.
Стандартное решение будет каким-то таким:
update table set processed_at = now() returning *;

И это довольно рабочий вариант, только когда выполнять? К примеру раз в 5 секунд? — Да, этот вариант подходит.

Но что, если те записи, которые нам надо вытащить возникаю не так часто (реже чем раз в 5 сек), но реагировать на них мы должны очень быстро ?

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

Быстро, круто и без грязи в системе.

Храните дататайм в timestamptz

Возьмите за привычку в БД всегда хранить timestampTZ и когда-нибудь Вам скажут большое спасибо за это.
Это очень частая ошибка как новичков, так и разработчиков с опытом.

Да, это не правило, бывает, что надо использовать именно без таймзоны. Но если вы не знаете что использовать — используйте with time zone.

Пример использования с таймзоной

Пример использования без таймзоны

Немного о том, как хранится timestamptz в БД:
PostgreSQL не хранит у себя таймзону, которую Вы ей скармливаете, она преобразует всё в UTC и хранит у себя именно так.
А когда надо на клиента отдать — кастит в зону клиента. Когда вы ей пихаете фильтр по времени — она кастит Ваш фильтр в UTC и работает с ним.
Думаю, теперь стало понятно.

Немного о программировании, мониторинге и оптимизации

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

Не забудьте выставить правильную конфигурацию сервера

Хоть этот пункт вообще само собой разумеющееся, но, да-да, конфигурация PostgreSQL изначально такая, что Ваш кластер может запуститься на чугунном утюге. Но не надо так делать :)

Как только вы сделали кластер, который будет являться девом/продом, и даже пусть там будет всего 1–2 клиента — конфигурируйте сервер под ваше железо и задачи. На самом деле достаточное количество проблем так или иначе связано с конфигурацией кластера.

Информацию о том, какие конфиги надо сразу смотреть, какие можно позже — можно найти в интернете.

Одно правило — не переусердствуйте, иначе сервер может и сознание потерять :)
Ради примера:
дать клиенту 10 Гб оперативки под операции + подключить 100 клиентов
или установить shared_buffers = 100% оперативки, а потому думать, почему падает система
или просто установить этот параметр нереально большим и увидеть в pg_stat_activity LWLock: BufferMapping

Используйте метрики. Пользуйтесь pg_stat_statements

Так-же рекомендую сразу установить расширения для мониторинга запросов (например pg_stat_statements).

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

А вообще настройте аллерты на какие-нибудь метрики!

В функциях лучше использовать CTE и длинные запросы, чем временные таблицы

Возможно, этот пункт звучит некорректно.

PostgreSQL всегда временные таблицы кладёт на диск, это займёт много времени. Используйте табличные выражения. Да и лучше всё писать одним оператором. Потому что даже в стандартной изоляцией функции (с Read committed), вы в разных операторах в функции будете наблюдать разные данные.

Не делайте секции везде, где только можно (и где нельзя тоже)

Да да, секции — это круто. Но вот их использовать я бы рекомендовал только в некоторых случаях:

• огромнейшая таблица

• для быстрого удаления

• если у Вас 100% один запрос уйдёт в определённую секцию

• разделение отработанных данных

  • наверное это частный случай пункта выше. Например, у Вас есть задачи для внешнего сервиса. Что отработало — в архив по месяцам, что не отработало — в оперативную табличку (attach by default)

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

Не забывайте делать дополнительную статистику

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

Приведу пример:
У Вас в таблице пациенты: ФИО, пол, признак беременности, другие поля

Из данных, которые знаем мы: 90% пациентов — девушки, из которых 50% — беременны
Что знает БД после сбора статистики: 10% — парни, а 45% — беременные люди.

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

Пример, возможно, не самый удачный. Но вот в аналитических задачах расширенная статистика поможет планировщику не споткнуться и не выбирать nested loop, а выбрать какой-нибудь hash join.

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

Старайтесь не пихать всё в TOAST

Наверное сразу стоит добавить и тот пункт, что не надо хранить в TOAST (например json/text с дефолтным параметром хранения) аналитические данные.
Можно напороться на очень большие проблемы с производительностью.

Буквально пол года назад была на моей практике такая проблема. Оказалось, что для построения отчёта мы брали 1 поле для вычисления из json-a (который довольно большой).
Вынесли значение из жсон в табличку. Запрос начал выполняться не 3 часа, а всего 1–4 сек.

Используйте explain, а не create index

Да-да, очень часто бывают проблемы, когда запрос начал тормозить и программист сразу А почему бы не сделать индекс отдельный ?.

Это очень больная тема. Сядьте, запустите explain analyze, проанализируйте его полностью и найдите узкие места.

Поверьте, это очень помогает. а ещё повышает чсв :)

Да и вообще, вопрос на счёт использования определённых индексов под определённые задачи, а так-же о конфигурации индексов — это прям отдельная тема

Используйте визуализаторы планов

Просмотр плана запроса в том-же PGAdmin-е не очень удобен. Да, в нём можно всё рассмотреть, но есть более удобные инструменты, которые облегчат эту задачу.

Например, я использую этот.

Внимательно смотрите на признаки изменчивости и параллелизма создаваемых функций

К большому сожалению, на это никто не обращает внимания.

Возьмите за основу всегда проверять изменчивость функций (volatile/stable/immutable).
В своей практике я натыкался на грабли, когда писал внутренние апи-функции (вспомогательные функции) с признаком volatile (который дефолтный), которые просто выдавали инфу.
И всякий раз это приходилось исправлять, когда на проде такие функции превращали время выполнения обычных OLTP-запросов из 0.05–0.1 сек в 4–6 сек.

С параллелизмом по большей части не сталкивался с проблемами, но всё же лучше тоже не оставлять этот параметр дефолтным (только прочитайте заранее, когда надо, а когда нет).

Так-же не забывайте, что функции с параметром volatile работают в собственном снапшоте (снимок в MVCC) и могу видеть базу более актуальную информацию, чем материнский оператор, такое поведение может поломать у вас что-нибудь в бизнес-логике.

Вот банальный пример

-- не описываю прям всё, чисто ради примера

-- создадим таблицу
create table users(id, created_at);

-- генерим даныне
insert into users()... generate_series(1,10)

-- создаём функцию волатайл, которая возвращает created_at по ид
create function get_user_created_at(user_id) returning created_at ....

-- делаем запрос:
select created_at, get_user_created_at(id), pg_sleep(1)
from users

-- в параллельной транзакции делаем
update users set created_at - now()

-- ну и в запросе, который написан на 13 строке
-- мы увидим расхождения в created_at,
-- поскольку функция волатайл отрабатывает в своём снапшоте
-- и увидит там created_at из запроса в 17 строке

Реализуйте блокировки правильно

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

Организация написания кода

И так, пришло время приступить к последнему блоку. Тут больше описана часть именно организации написания кода, его оформления и т.д.

Сделайте специальную схему devна проде

Допустим, у Вас на проекте некоторым разработчикам БД разрешается доступ в прод с правами readOnly, например им надо будет выполнять какую-нибудь отладку, часто воспроизводить запросы (для того-же explain analyze).

Так вот, советую делать специальную схему, например dev. Дайте туда полный доступ, пусть копируют, модифицируют и гоняют функции в своей песочнице (ну не делать же нам полную копию прода для теста).

Замечу, что надо будет напомнить разработчикам, чтобы таблички они создавали с параметром UNLOGGED, дабы те не уходили на реплику.

Держите код в репозитории и версионируйте его

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

Создайте отдельную папку и храните код в репозитории, всегда имейте историю модификации тех или иных объектов в БД.

Самое простое, как это сделать — просто сделать слепок структуры БД и отобразить в виде разных файлов (каждый объект — свой файл). Из доступных инструментов — pgCodeKeeper, DataGrip и др.

У нас же сейчас тестируется система на pg_dump + pg_query_go.

Заливайте в прод только через инструменты, не надо делать этого руками

И вот, когда код уже лежит в репозитории — из него можно делать миграции и заливать их в прод.

Используйте CICD инструменты и сделайте выкатку изменений БД в прод через специальные утилиты.
Примером таких утилит является: liquibase, flyway, pgCodeKeeper, pg-migrator и тонна других.

Всё зависит от того, как вы формируете миграции и что из себя представляет ваша БД.

Никогда не вносите в прод изменения, не фиксируя их в коде репозитория или где-либо ещё. Проблем нахватаетесь, гарантия 100%.

Пишите везде комментарии

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

Да да, пункт банальный до безобразия.

Пользуйтесь КроссРевью

Думаю, тут комментарии излишни

Договоритесь с командой о Едином стиле оформления кода

И казалось бы, что 3 последние пункта идут как `само собой разумеющееся` — очень часто это не соблюдается.

Если уж возникают проблемы — используйте линтеры, например pgFormatter.

Заключение

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

© Habrahabr.ru