Рекомендации при работе с PostgreSQL

Приветствую, 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;

-- не забываем сразу подключить схему в search_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-200-500Гб

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

  • например хранение логов вызова от клиента, зачем нам данные полугодовой давности?

• если у Вас 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()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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