Комментарии 53
Самое простое, как это сделать - просто сделать слепок структуры БД и отобразить в виде разных файлов ( каждый объект - свой файл ). Из доступных инструментов - pgCodeKeeper, DataGrip и др.
Самое простое — это модифицировать БД с помощью миграционных скриптов. Тогда буквально будет поэтапная история всех изменений.
Самое простое — это модифицировать БД с помощью миграционных скриптов. Тогда буквально будет поэтапная история всех изменений.
Именно так мы сейчас и делаем:
храним отдельно файлики со скриптами ( на каждый объект свой файл)
эти файлики - и есть миграции для liquibase
Точнее, мы ещё тестируем весь функционал, потому что он в себя включает много разных аспектов
Уточните пожалуйста, отдельный файл с миграциями именно на объект (например, таблицу)? То есть скрипты организованы по объектам, а не по версиям?
Обычно рекомендуют версионную организацию ченджсетов - на каждую версию системы создаётся файл x.y.z.xml, и в нём миграции этой версии.
Почему выбрали именно по-объектную модель?
В целом, спасибо, советы хорошие. Однако:
Сделайте специальную схему dev на проде
То есть разрешить криворуким ребятам запускать что-то прямо на продакшен-базе? Предполагается что у них в это время будет ещё и доступ к реальным таблицам (на чтение?)?. Но.. ведь.. дедлоки.. блокировки.. ресурсы.. безопасность..
Что касается советов по структуре - тут никогда не будет одного правильного решения. Рано или поздно понадобится запускать запросы по разным схемам. У меня две базы и в них нормально писались разные данные пару лет. А потом всё-таки понадобилось объединять их в некоторых запросах. Добавил пару таблиц через FDW. Потом ещё пару. Потом еще десять. И теперь сижу и думаю что с этим делать..
То есть разрешить криворуким ребятам запускать что-то прямо на продакшен-базе? Предполагается что у них в это время будет ещё и доступ к реальным таблицам (на чтение?)?. Но.. ведь.. дедлоки.. блокировки.. ресурсы.. безопасность..
Вопрос безапасности данных - да, это понятное дело... Но предполагаю, что доступ не у всех же будет :)
Тут вопрос имнно в том, что могут быть проблемы с планировщиком/ статистикой именно на проде и на продовских данных. Да, можно деанонимизировать данные и сделать копию для тестов ( ну у нас например 2 самых больших проекта сейчас: 2Тб и 670Гб ), не будем же мы делать копии для теста)
Дедлоков тут быть не будет как таковых.
На счёт ресурсов - ну выполнишь 1 раз запрос для получения аналайза и всё.
Что касается советов по структуре - тут никогда не будет одного правильного решения.
Поэтому я и назвал рекомендациями. Понятное дело, что универсальных решений нет, универсальных подходов тоже.
2 ТБ - это небольшая база и ее просто регулярно копировать и/или настроить репликацию (можно и асинхронную) на тестовую машину. Потому что производительности исходной (продуктивной) машины для тестов может банально не хватить и она банально загнется.
Вот если бы БД у вас была 2 ПБ, тут да - надо было бы что-то придумывать с тестами (замечу - и придумывают, и реализовывают). И все равно, запускать разработчиков на прод можно лишь в крайнем случае (<1%), когда никакие тестовые среды уже не помогают отловить проблему.
Не понял смысла в создании схемы для расширения
Расширения создают свои таблицы, функции, типа и прочие объекты
И вряд-ли вы захотите, чтобы они мазолили Вам глаза, когда будете открывать дерево объектов Вашей БД.
Для примера - установить postgis в public, где у вас своих 200 функций. А теперь попробуйте глазами найти нужную Вам функцию :)
Ну и опять-же, это нормализация структуры. Каждый объект - на своём месте. Это очень удобно.
+ Это возможность удобно отключать ненужные объекты в pg_dump-e. ( ведь объекты для расширений ставятся установкой расширений )
Я бы просто советовал всегда и всегда хранить время в utc как красавчик постгрес и делает. Все же таймзона это зона ui, я ведь могу и часики перевести прямо во время дороги например.
А как-же агрегаты по времени ?
это хорошо, но требует всегда про это помнить и преобразоывать в utc перед вставкой. А так за нас это БД сделает. удобно
даже больше скажу.
утверждение: "всегда хранить время c таймзоной", тоже может создавать нетривиальные проблемы.
например, изменение таймзоны в конкретной географической точке.
и тогда получается, что события сохраненные вчера в 17ч (+3), сегодня эти же события стали 16ч (+2), потому что сегодня ночью таймзона сменилась.
вообще, в случае микросервисов, и взаимодействия бд только с одним приложением, проблема с таймзоной внутри бд перестает быть актуальной. конкретное приложение становится ответственным за правильную работу с таймзоной. и соответственно можно хранить время в бд без таймзоны.
Поэтому просто время со смещением хранить смысла нет и timetz тип не рекомендован к использованию. Имеет смысл хранить время с географической точкой или именем региона. Например, "17:00 Europe/London" вполне нормально можно расчитать для каждой даты, в то время как "17:00+01" уже может привести к ошибке.
Если же мы храним не просто время, а время вместе с датой, то "2024-02-28 17:00+01" уже имеет смысл, поскольку противоречия снимаются.
Мб Вы не до конца прочитали...
PostgreSQL-ю всё равно, в какой таймзоне вы пихаете данные - всё кастанётся в UTC.
А когда будете делать select - получите дататайм в таймзоне, которая у Вас настроена в сессии
Есть БД, которые умеют хранить время с регионом таймзоны в одном поле?
вообще, в случае микросервисов, и взаимодействия бд только с одним приложением, проблема с таймзоной внутри бд перестает быть актуальной. конкретное приложение становится ответственным за правильную работу с таймзоной. и соответственно можно хранить время в бд без таймзоны.
Вот у меня в данный момент боевой проект.
Всё работало круто, всё было нормально, потому что было в одной таймзоне.
И как только началось расширение в другие регионы - случилось то что случилось. Вот правим ошибки
Да-да, поднимите руку у кого ещё не было проблем из-за того что, внезапно, пришлось работать с другими таймзонами?
Сотрудник уехал в командировку в Японию и сидит с рабочим ноутбуком, на котором всё еще лондонское время. А рядом у него телефон с открытым дашбордом, но телефон время уже сам перевёл. А в офисе у него остался залоченный, но включённый десктоп, на котором в браузере открыт тот же дашборд. Кто первым сойдёт с ума - юзер, тестировщик или програмист?
Это просто опыт.
Теперь, проектируя интерфейсы для фронта вы будете знать, что на фронте есть не только время, но и таймзона. И время с бека нужно отдавать/принимать с таймзоной. Фронт в свою очередь, должен работать с этим с учетом своей локали. Так надо делать всегда.
А вот надо ли хранить время в бд вместе с таймзоной - вопрос открытый.
И оба вопроса хоть и пересекаются между собой, но друг другу не мешают.
ну я тут отпишу в двух абзацах:
1) Вообще современные (широко используемые фронтенд-фремворки) прекрасно умеют кушать дататайм с таймзоной и сразу кастить в свою локальную таймзону
Конечно, я не фронтендер, но уточнял у "своих" - мне сказали, что всё само делается. Так что в принципе, без разницы абсолютно
2) С другой стороны, мы однажды столкнулись с проблемой агрегации ( сумма за сутки ). Агрегация происходила в БД, а там своя таймзона.
Решение было простое - фронт прокидывает в хэдерах запроса свою таймзону, а бэкенд сначала переопределяет полученную таймзону как сессионную таймзону, а потом уже выполняет запрос :)
Нормальный бриф адекватного курса DBA. Всё по теме.
Админ должн быть только у Админа кластера
Остальное лишнее. К проду у разработчиков не должно быть доступа вообще в общем случае. А частности рассматриваются отнюдь не в публичном пространстве. Но даже в частных случаях у лиц, не являющихся админами БД, должен быть только и исключительно доступ на чтение. Безо всякой записи/модификации/удаления.
Миграции - соответствующим инструментом, под соответствующим аккаунтом.
Вот именно для каких-то тестов именно на проде ( и только на проде ) - отдельная схема.
Мы заливаем миграции через liquibase, но и даже у пользователя, под которым выполняются миграции - не админка.
Тесты на проде...
Остановите Землю, я сойду!
Рекомендую использовать следующий подход: создавайте роли, настраивайте их как групповые политики и давайте разрешения на именно на роли.
Есть замечательный пост про "простоту и удобство" управления ролями в постгрес )
Статья полезная, спасибо
Насчёт использования CTE вместо временных таблиц совет совсем неоднозначный.
Я широко использую именно временные таблицы - с ним проще отлаживать запросы, можно посмотреть состояние на каждом шаге.
Ну и код выглядит и читается проще, на мой вкус.
Плюс планировщик по CTE не имеет статистики и периодически чудачит. А по временной таблице можно построить статистику.
Я абсолютно согласен, что имеет смысл использовать временные таблицы, когда это делать надо
Согласен, что код чище, Но скорость меньше..
На счёт статистики, если планировщик ошибается на 20-30% - проблем вообще не должно быть. Если очень большая ошибка - стоит посмотреть, почему он так считает и разобраться
ну и вообще постгресс всегда материализует CTE
Я могу ошибаться, но материализованные представления умеют в статистику сразу
Так было только до 11й версии (т.е. очень давно). Начиная с 12й, по умолчанию не материализует, но при необходимости даёт возможность явно материализовать при помощи ключевого слова.
Правда, статистики по результату материализации CTE всё равно не бывает.
чтобы такие ручки для разных модулей
Что за "ручки" такие? Вы тащите в статью какой-то ваш внутренний сленг?
По статье:
Нормализация. Делать нормализацию только потому, что "так учили" - сомнительно. Бывает приходится сознательно денормализовывать данные.
Отдельные схемы для разных модулей. Крайне сомнительно. Особенно, если данные используются далеко не одним модулем.
Нормализация. Делать нормализацию только потому, что "так учили" - сомнительно. Бывает приходится сознательно денормализовывать данные.
А кто сказал, что надо всегда делать 6 уровней нормализации
Это всегда рекомендательные советы и не более, понятное дело, что ради оптимизации иногда надо идти на денормализацию.
Отдельные схемы для разных модулей. Крайне сомнительно. Особенно, если данные используются далеко не одним модулем.
А кто сказал, что модули строго изолированы ?
Это все конечно прекрасно, по каждому пункту. Дело за малым - объяснить всё это новому поколению разработчиков и менеджеров.
Большинство этих советов в любых субд работают.
Про временные таблицы фактическая ошибка — написано что они почему-то всегда работают с диском, что ерунда: это такие же таблицы, как и все остальные. У них есть недостатки, но совсем другие.
Создание временной таблицы ( create temp table ) в любом случае идёт на диск.
Как видно на практике, молодые разработчики боятся использовать `многоэтажные` CTE ( with (),() select ) и пытаются пробиться через использование временных таблиц.
В большинстве случаев этого делать не стоит.
И в который раз повторюсь, что есть разные случаи и в некоторых стоит работать именно через временные таблицы ( или только через них )
У меня есть следующий случай. Вначале я делаю некоторую выборку из нескольких таблиц, а потом мне нужно с этими исходными результатами сделать серию выборок из довольно больших таблиц. Первоначальная выборка не очень большая, но если делать её в CTE все 7-8 раз, для каждого набора - то как-то слишком получается.
Поэтому я в одной сессии делаю первоначальную выборку во временную таблицу, а все остальные джойню по очереди с ней, в той же сессии.
Полезная статья, прям выжимка, как я люблю)
Добавил бы, что советы изначально думать о таймзонах, ограничивать доступы и хранить весь код в git - они в целом универсальные
Рекомендации при работе с PostgreSQL