Приветствую, current_user()!
Добро пожаловать в увлекательное путешествие по миру разработки с PostgreSQL – мощной системой управления базами данных. В этой статье мы погрузимся в ключевые аспекты создания кода и структуры для PostgreSQL с акцентом на эффективных стратегиях разработки.
Это не правила, а скорее полезные рекомендации, которые помогут Вам избежать неприятных ситуаций в будущем. Представленный опыт - мой собственный, и, конечно же, он может быть немного субъективным, так как я относительно далёк от статуса супер-разработчика :)
Тут будут рассмотрены пункты, которые касаются именно разработки и её организации (подходу). Мы не будет затрагивать конфигурацию серверов, бэкапы/реплики/шарды и прочее.
Я попытаюсь быть кратким. Просто коротко и по пунктам:
Нормализация структуры
Все мы при изучении программирования на языке SQL учили, что есть уровни нормализации в базах данных. Но ведь мы должны оптимизировать не только расположение данных в разных объектах, но так‑же и эти объекты ( таблицы, функции, роли, доступы ) в разных схемах
Используйте отдельные схемы для разных модулей
Скажем, вам необходимо реализовать систему рассылки пуш-уведомлений на android-устройства клиентов. Вы пишете сервис, который будет работать с FCM. Пишете функции в вашей БД, которые будут выдавать списки token-текст ( да там много полей вообще ).
Так вот, необходимо, чтобы такие ручки для разных модулей ( бэкендов ) лежали в отдельных схемах.
вот такой бредовый пример:
logging
тут всё что связано с логгированием: таймскейл, функция для записи или выдачи лога и т.п.
payments_common
общие конфиги/ручки/ прочее для систем оплаты
payments_tinkoff
конфиги и ручки, которые касаются именно системы оплаты Тинькова
payments_sber
аналогично для Сбера
notifications
конфиги и ручки для пересылки уведомлений клиентам ( можно ещё разбить дополнительно на FCM/SMS/e-mail/пуши в браузере и т.д. )
Используйте группы ролей и назначайте гранты именно на роли
Предположим, что вы реализовали пункт выше, теперь ваш сервис должен подключаться к БД и крутить свои запросы. Но под каким пользователем? Не будет же он бегать под postgres или admin?)
Мы создадим для него отдельного юзера и пускай логинится под ним. Хмм, но ведь ещё права дать надо? Ну дадим.
Рекомендую использовать следующий подход: создавайте роли, настраивайте их как групповые политики и давайте разрешения на именно на роли.
Например вот такие роли:
api_role - для доступа к внутренним апи-функциям
client_api_role - для доступа мобильных клиентов
admin_api_role - для доступа сайта ( ну например это админка )
notify_role - для доступа системам уведомлений
developer - то что доступно разработчикам ( например какая-то часть таблиц доступна на чтение на проде )
А потом уже создавайте пользователей и подключайте их к ролям. А гранты выдавайте именно на роли. Не делайте их персональными.
Пример для группы `Разработчики`
Допустим, вы даёте некоторым разработчикам ReadOnly в прод ( не на все таблицы, понятное дело )
Проще ведь создать роль developer, а потом 1 раз выполнить:grant select ... to developer
, и при добавлении нового разработчика Вам будет достаточно выполнить: grant developer to "new user"
В любом случае придерживайтесь следующих правил:
Для каждого нового бэкенда ( приложения ) - свой юзер
Админ должен быть только у Админа кластера + у некоторых разработчиков ( например техлид )
Используйте расширения
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.
Пример использования с таймзоной
Торги на бирже начинаются в 10 утра по МСК.
Если Ваш клиент из Гонконга заходит на биржу в 13 дня:
у него 13:00:00 +08, а в МСК это 08:00:00 +03, как мы видим, биржа должна быть закрыта. Если бы мы тут использовали без таймзоны - пришлось бы руками писать вычисление того, открыта биржа или сколько времени до её открытия.
Дополню: если в БД у нас хранится, что биржа открывается в 12:00:00 +03, то при select '12:00:00 +03' - '13:00:00 +08' нам выдаст, что до открытия осталось 4 часа.
Пример использования без таймзоны
Мы хотим в нашем приложении отображать кнопку `Получить новогодний бонус` 1 января с 09:00 до 10:00.
Вот как раз этот период времени нам надо хранить в БД без привязки к таймзоне.
И тогда у нас что для 9 утра по МСК, что для 9 утра по Гонконгу будет выдаваться попадание в интревал:
set local time zone $client_timezone;
select now()::timestamp <@ tsrange('2024-01-01 09:00:00', '2024-01-01 10:00:00');
Немного о том, как хранится 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, можно обеспечить стабильность, производительность и безопасность как базы данных, так и кодовой базы Вашего проекта.
