Тот самый список правил
Почти за каждым из пунктов ниже стоит печальная история, полная страданий и превознемоганий. А словом «боль!» помечены пункты, выработанные историями, при воспоминании о которых я все еще вздрагиваю по ночам.
- Версионируйте схему базы данных
Схема базы данных — это код, который вы написали. Она должна лежать в системе контроля версий и версионироваться с остальным проектом. В случае PostgreSQL мне больше всего для этих целей понравился Pyrseas. Он превращает схему со всеми специфичными для PostgreSQL объектами в yaml файл, который версионируются. С таким файлом удобно работать в ветках и сливать изменения, в отличие от чистого SQL. Финальным шагом yaml файл сравнивается со схемой базы данных и автоматически генерируется миграция на SQL.
- Боль! Никогда не применяйте изменения сразу на боевую базу
Даже если изменение простое, невероятно срочное и очень хочется. Вначале нужно применить его на базе разработчиков, закомитить в ветку, изменения применить на базе транка (идентичной боевой базе). И только потом, когда все хорошо в транке, применять на боевой базе. Это долго, параноидально, но спасает от многих проблем.
- Боль! Перед тем, как написать delete или update, напишите where
А еще перед тем, как запустить код, выдохните, просчитайте до трех и удостоверьтесь, что вы в сессии нужной базы. Про truncate я вообще молчу, без трех «Отче наш» даже не думайте запускать, аминь!
UPD. koropovskiy: полезнее выставлять set autocommit off для текущего сеанса.
tgz: или перед каждым update и delete пишите begin.
- Test Driven Development
Вначале всегда пишите тесты, а потом создавайте объекты базы данных. Речь идет про любые объекты: схемы, таблицы, функции, типы, расширения — никаких исключений! Вначале это кажется тяжко, но впоследствии вы много раз скажете себе спасибо. Даже при первичном создании схемы легко что-то упустить. А при рефакторинге таблиц через полгода только написанные вами тесты уберегут от внезапного выстрела в ногу в какой-нибудь функции. В случае PostgreSQL есть замечательное расширение pgTAP. Я рекомендую для каждой схемы создавать дополнительно схему «имя_схемы_tap», в которой писать функции для тестирования. А потом просто прогонять тесты через pg_prove.
- Не забывайте настроить PITR
Я боюсь выступить в роли Капитана Очевидности, но у любой базы должен быть настроен бэкап. При том желательно такой, чтобы иметь возможность восстанавливать базу на любой момент времени. Это необходимо не только для восстановления при сбоях, но и дает много интересных возможностей разработчикам для работы в определенных временных срезах базы. В PostgreSQL для этого есть barman.
- Согласованность данных
Несогласованные данные в базе никогда не приводили ни к чему хорошему. Даже небольшое их количество может легко превратить всю базу в мусор. Поэтому никогда не стоит пренебрегать нормализацией и ограничениями вроде внешних ключей и проверок. Используйте денормализованную форму (например, jsonb) только удостоверившись, что не получается реализовать схему в нормализованном виде с приемлемым уровнем сложности и производительности — денормализованный вид потенциально может привести к несогласованным данным. На все доводы сторонников денормализации отвечайте, что нормализацию придумали не просто так и молчите с многозначительным видом.
- Создавайте внешние ключи deferrable initially deferred
В таком случае вы откладываете проверку ограничения на конец транзакции, что позволяет безнаказанно получать несогласованность в ходе ее выполнения (но в конце все согласуется или вызовет ошибку). Более того, меняя флаг внутри транзакции на immediate, можно принудительно сделать проверку ограничения в нужный момент транзакции.
UPD. В комментариях указывают, что deferrable — неоднозначная практика, которая упрощает ряд задач импорта, но усложняет процесс отладки внутри транзакции и является плохой практикой для начинающих разработчиков. Хоть я упрямо склоняюсь к тому, что лучше иметь deferrable ключи, чем не иметь их, учитывайте альтернативный взгляд на вопрос.
- Не используйте схему public
Это служебная схема для функций из расширений. Для своих нужд создавайте отдельные схемы. Относитесь к ним как к модулям и создавайте новую схему для каждого логически обособленного набора сущностей.
- Отдельная схема для API
Для функций, которые вызываются на стороне приложения, можно создать отдельную схему «api_v_номер_версии». Это позволит четко контролировать, где лежат функции, являющиеся интерфейсами к вашей базе. Для наименования функций в этой схеме можно использовать шаблон «сущность_get/post/patch/delete_аргументы».
- Триггеры для аудита
Лучше всего триггеры подходят для аудита действий. Так же рекомендую создать универсальную триггерную функцию, для записи любых действий произвольной таблицы. Для этого нужно вытащить данные о структуре целевой таблицы из information_schema и понять, old или new строка будет вставляться в зависимости от производимого действия. За счет такого решения код становитсялюбовным и прельстивымболее поддерживаемым.
Если же вы планируете использовать триггеры для подсчета регистра накоплений, то будьте аккуратны в логике — одна ошибка и можно получить неконсистентные данных. Поговаривают, это очень опасное кунг-фу.
- Боль! Импорт данных в новую схему
Самое ужасное, но регулярно происходящее событие в жизни разработчика баз данных. В PostgreSQL очень помогают FDW, тем более их хорошо прокачали в 9.6 (если их разработчики озаботятся, то FDW могут строить план на удаленной стороне). Кстати, есть такая удобная конструкция как «import foreign schema», которая спасает от написания оберток над кучей таблиц. Так же хорошей практикой является иметь набор функции, сохраняющие набор SQL команд для удаления и восстановления существующих в базе внешних и первичных ключей. Импорт рекомендую осуществлять, вначале написав набор view с данными, идентичных по структуре целевым таблицам. И из них сделать вставку, используя copy (не insert!). Всю последовательность SQL команд лучше держать в отдельном версионируемом файле и запускать их через psql с ключом -1 (в единой транзакции). Кстати, импорт — это единственных случай, когда в PostgreSQL можно выключить fsync, предварительно сделав бэкап и скрестив пальцы.
- Не пишите на SQL:1999
Нет, правда, с тех пор много воды утекло: целое поколение выпустилось из школы, мобильники из кирпичей превратились в суперкомпьютеры по меркам 1999 года. В общем, не стоит писать так, как писали наши отцы. Используйте «with», с ним код становится чище и его можно читать сверху вниз, а не петлять среди блоков join'ов. Кстати, если join делается по полям с одинаковым названием, то лаконичнее использовать «using», а не «on». Ну и конечно, никогда не используйте в боевом коде offset. А еще есть такая прекрасная вещь «join lateral», про которую часто забывают — и в этот момент в мире грустит котенок.
UPD. Используя «with» не забывайте, что результат его выполнения создает CTE, которое отъедает память и не поддерживает индексы при запросе к нему. Так что употребленные слишком часто и ни к месту «with» могут негативно сказаться на производительности запроса. Поэтому не забывайте анализировать запрос через планировщик. «with» особенно хорош, когда нужно получить таблицу, которая будет по-разному использоваться в нескольких частях запроса ниже. И помните, «with» радикально улучшает читабельность запроса и в каждой новой версии PostgreSQL работает все эффективнее. При прочих равных — предпочитайте именно эту конструкцию.
- Временные таблицы
Если можете написать запрос без временных таблиц — не раздумывайте и напишите! Обычно CTE, создаваемое конструкцией «with», является приемлемой альтернативой. Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл… и да, еще один грустный котенок на планете.
- Боль! Самый страшный антипаттерн в SQL
Никогда не используйте конструкции вида
select myfunc() from table;
Время выполнения такого запроса возрастает в линейной зависимости от количества строк. Такой запрос всегда можно переписать в нечто без функции, применяемой к каждой строке, и выиграть пару порядков в скорости выполнения.
- Главный секрет запросов
Если ваш запрос работает медленно на тестовом компьютере, то в продакшене он работать быстрее не будет. Тут самая лучшая аналогия про дороги с автомобилями. Тестовый компьютер — это дорога с одним рядом. Продакшен сервер — дорога с десятью рядами. По десяти рядам в час пик проедет куда больше машин без пробок, чем по одной полосе. Но если ваша машина — старое ведро, то как Феррари она не поедет, сколько свободных полос ей не давай.
- Используй индексы, Люк!
От того, сколь правильно вы их создадите и будете использовать, зависит, будет запрос выполняться десятые доли секунды или минуты. Я рекомендую ознакомиться с сайтом Маркуса Винанда по устройству b-tree индексов — это лучшее общедоступное объяснение по сбалансированным деревьям, которое я видел в Интернете. И книжка у него тоже крутая, да.
- group by или window function?
Нет, понятно, window function может больше. Но иногда агрегацию можно посчитать и так и так. В таких случаях я руководствуюсь правилом: если агрегация считается по покрывающим индексам — только group by. Если покрывающих индексов нет, то можно пробовать window function.
- set_config
set_config можно использовать не только для выставление настроек для postgresql.conf в рамках транзакции, но и для передачи в транзакцию пользовательской переменной (если ее заранее определить в postgresql.conf). С помощью таких переменных в транзакции можно очень интересно влиять на поведение вызываемых функций.
- FTS и триграммы
Они чудесны! Они даруют нам полнотекстовый и нечеткий поиск при сохранении всей мощи SQL. Просто не забывайте ими пользоваться.
- Вызов собственных исключений
Зачастую, в большом проекте приходится вызывать много исключений со своими кодами и сообщениями. Чтобы в них не запутаться, есть вариант создать для исключений отдельный тип вида «код — текст исключения», а так же функции для их вызова (обертка над «raise»), добавления и удаления. А если вы покрыли все свои объекты базы тестами, то вы не сможете случайно удалить код исключения, который уже где-либо используется.
- Много паранойи мало не бывает
Хорошая практика — не забывать настроить ACL на таблицы, а функции запускать с «security definer». Когда функции работают только на чтение, фэншуй требует выставлять у них флаг «stable».
- Боль! Вишенка на торте
UPD. Никогда нельзя перенаправлять пользователя приложения через сервер в базу данных, взаимно-однозначно транслируя пользователя приложения в пользователя БД. Даже если вам кажется, что при этом можно настроить в БД безопасность для пользователей и их групп штатными средствами PostreSQL, никогда не делайте так, это ловушка! При такой схеме нельзя использовать пулы соединений, и каждый подключенный пользователь приложения будет отъедать ресурсоемкое соединение к базе данных. Базы данных держат сотни соединений, а сервера — тысячи и именно по этой причине в приложениях используют балансировщики нагрузки и пулы соединений. А при трансляции один к одному каждого пользователя в базу данных при росте нагрузки придется ломать схему и все переписывать.
Комментарии (42)
dezconnect
31.10.2016 18:03-9Капитан Очевидность, залогинтесь.
cornhedgehog
01.11.2016 15:51+5Можно, конечно, тешить себя мыслью, что Хабрахабр — это такой своеобразный Олимп, где сидят только наикрутейшие профессионалы, но это не так — хабр читают и обычные джуны, которым всё вышеописанное в новинку. Так зачем же кричать «очевидно»? Кому-то, может, и не очевидно совсем.
xenohunter
01.11.2016 17:11+3Могут быть и не только junior-разработчики, но и люди из других областей, которые хотят что-то новое освоить. И вот такие подборки — это очень ценный ресурс для них. То есть, для нас.
mayorovp
31.10.2016 19:09Согласованности можно достичь и для денормализованных данных — при помощи составных первичных и внешних ключей. Разумеется, если денормализованные данные — это просто данные с дублированием, а не данные без схемы.
Впрочем, для последних можно попробовать использовать вычисляемый атрибут как внешний ключ.
potapuff
31.10.2016 21:04+2Подскажите, откуда взялось, что «Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл…»? Нигде в документации об этом не написано.
Повлияет ли на производительность, если временную таблицу создавать в tablespace на RAM-диске?
zip_zero
01.11.2016 01:07+1Извините, я ошибся. Копнул чуть глубже в документацию:
Each table and index is stored in a separate file. For ordinary relations, these files are named after the table or index's filenode number, which can be found in pg_class.relfilenode. But for temporary relations, the file name is of the form tBBB_FFF, where BBB is the backend ID of the backend which created the file, and FFF is the filenode number.
И внимание. При старте (если это действительно только при старте, то похоже на баг) postmaster через
RemovePgTempFiles() в конечном итоге вызывает unlink() для всего, что выглядит как файл временной таблицы.
potapuff
01.11.2016 10:52Судя по форумам tablespace на RAM-диске дает очень высокую вероятность убить всю базу.
"...losing any tablespace would prevent the database server from starting,
even if it was only for temporary things" https://www.postgresql.org/message-id/51806248.60401%40darrenduncan.net
Сообщение датировано 2013 годом. Интересно, как сейчас дела обстоят?
negodnik
31.10.2016 23:27+1> Не используйте схему public
> Это служебная схема для функций из расширений. Для своих нужд создавайте отдельные схемы. Относитесь к ним как к модулям и создавайте новую схему для каждого логически обособленного набора сущностей.
Почему?potapuff
31.10.2016 23:52С помощью схем в PG можно логически группировать таблицы. Писать запросы не мешает, так как всегда можно указать все схемы в schema_search_path сессии и не указывать при именовании таблиц.
Как бонус можно создать таблицы с одинаковым именем в разных схемах для горячих и исторических данных. Тогда можно не меняя запросы подключатся между ними просто указав параметры в сессии.negodnik
01.11.2016 00:01+3Статья про безопасность. Так и не понял, чем это чревато, использовать public. Не каких лишает возможностей, а чем чревато.
darthunix
01.11.2016 15:36+2Это, скорее, правило хорошего тона. Но теоретически, можно получить проблемы конфликта имен при установке расширений, которые используют схему public для своих функций. Маловероятно, но возможно.
nmk2002
01.11.2016 01:19+2А еще перед тем, как запустить код, выдохните, просчитайте до трех и удостоверьтесь, что вы в сессии нужной базы.
Ох, вспоминается случай из жизни. Правда не про СУБД, но в тему торопливости. 2 недели без выходных работал в командировке над проектом. Последний день. Около 6 часов вечера. С утра должен быть самолет домой. Сделал все, кроме небольшого сервиса, который никак не хотел работать. Я решил, что самая хорошая идея — переустановка софта. Заметил, что нахожусь не в той ssh сессии только когда полностью удалил все с одной из важнейших боевых машин. Первая мысль — накрылся мой перелет и грядет нарушение визового режима. Но в итоге это была моя самая быстрая установка и настройка за всю жизнь: уже к 9 вечера все было настроено вместе с тем небольшим сервисом.
oYASo
01.11.2016 02:01+2Кстати, а что там нынче хорошего читают про PostgreSQL? Eng/рус, все равно, главное — специфика и тонкости работы. Что док есть, я знаю.
olshevskiy87
01.11.2016 13:24+1возможно, здесь вы найдете что-нибудь интересное (blogs, articles, tutorials)
kxl
01.11.2016 15:57darthunix п.22… именно поэтому к базе данных создают несколько подключений под одним пользователем, в которые заворачивают сотни пользователей с сервера. Не делайте так, одним словом, иначе придется при росте нагрузки все переписывать.
Не совсем понятно, что не нужно делать? Создавать на каждого пользователя приложения — пользователя в БД? Или наоборот?
А так, советы подходят почти к каждой СУБД, но спасибо за наводку на специфичные для Postgesql инструменты.darthunix
01.11.2016 16:19Я и правда не очень четко выразился. Речь о том, что создавать для каждого пользователя приложения своего пользователя в БД — очень плохая практика. При дальнейшем росте нагрузок (а они растут внезапно и незапланированно) это обернется невозможностью использовать пулы соединений к базе.
koropovskiy
01.11.2016 15:57п7 совсем неясен. зачем несогласованность в транзакции? кому от этого лучше?
deferrable initially deferred только затруднит поиск ошибки.darthunix
01.11.2016 16:07Вы получите согласованность на конец выполнения транзакции и (опционально) на любой момент внутри ее. Это позволяет упростить жизнь при вставке данных одной транзакцией в разные таблицы, где из-за внешних ключей нужно иначе было бы учитывать порядок вставки. Плюс это иногда удобно при импорте данных через insert.
koropovskiy
01.11.2016 21:30это совсем не похоже ни на технику безопасности ни на «хорошую практику»
применимо к приложению это создает исключения только на этапе коммита, вместо генерации исключения на этапе insert/update/delete, что точно плохо сказывается на диагностике неполадки.
К тому же, поддерживать согласованность всегда полезно для выстраивания логики кода. Гораздо сложнее выстрелить себе в ногу при длинных транзакциях
kxl
01.11.2016 22:25Например, при обмене между базами (не через репликацию)- загружаешь последовательно данные из пришедшего пакета. А в нем данные идут сначала по одной таблице, потом по другой… Тут не обеспечишь на каждой вставке согласованность — только для всей транзакции.
koropovskiy
01.11.2016 22:41+1Согласен. Их же не зря придумали и есть случаи, когда использование Defferable оправдано и полезно.
К тому же, при наступлении подобного вашему случая, достаточно указать set constraints all deferred; точно на время подобной вставки. Что будет явно означать, что ожидаются неконсистентные данные, которые можно коммитить только после полной загрузки.
Но. Статья претендует на набор граблей/хороших практик и «как надо, а как не надо делать», а в ней написаны достаточно спорные вещи. Отношение к Deferrable тому пример.
Или рекомендация использовать With. Что тоже совсем не полезно для запросов. Ниже об этом уже написали.
Или «Боль! Перед тем, как написать delete или update, напишите where»
гораздо полезнее писать SET AUTOCOMMIT OFF! Всегда!
Потому что только это позволит оценить последствия до их применения. Хотя бы по количеству удаленных записей.
Плохо учить падаванов спорным практикам. Их приходится потом мучительно переучивать.
В остальном пункты здравые, спасибо автору за статью. Я себе занес в список ссылок, который стоит показывать разработчикам :)darthunix
02.11.2016 14:14Я внес замечания в статью по поводу with, deferrable и set autocommit off. Спасибо большое за замечания и советы.
yurikgl
01.11.2016 15:57Боль! Перед тем, как написать delete или update, напишите where
Я select пишу.
Никогда нельзя перенаправлять клиента с сервера в базу под собственным, уникальным пользователем БД.
На самом деле зависит от задачи. Если изначально клиентов мало, то журналирование производится гораздо проще. Кто, когда с какого ip и т.д. Это моё сугубо личное ИМХО.darthunix
01.11.2016 16:30Если количество клиентов гарантированно останется небольшим (десяток-другой) и увеличиваться никогда не будет, то да. Главное, не ошибиться.
lokks
01.11.2016 15:57А можно, если не сложно, на 14й пункт какой-нибудь простой пример.
Что именно за функции имеются в виду?olshevskiy87
01.11.2016 17:09Что именно за функции имеются в виду?
предполагаю, что имеются ввиду функции с категориями изменчивости volatile (по умолчанию) и stable. в случае использования категории immutable с постоянными аргументами функция может быть выполнена всего один раз.
подробнее можно почитать здесь.
stalkerg
01.11.2016 17:20+1Это необходимо не только для восстановления при сбоях, но и дает много интересных возможностей разработчикам для работы в определенных временных срезах базы. В PostgreSQL для этого есть barman.
Если у вас нагруженная БД то хранение WAL для PITR это боль. Кроме того создание инкрементального бекапа при помощи braman это отдельная боль (если у вас БД больше 100 гигов). Большую часть этих проблем я решаю тут pg_arman .
darthunix
02.11.2016 03:59Кроме того создание инкрементального бекапа при помощи braman это отдельная боль (если у вас БД больше 100 гигов)
А можно поподробнее про подводные камни на 100 ГБ? У меня БД на 60 ГБ, хочу узнать, к чему готовиться через пару лет.stalkerg
08.11.2016 12:55Для того что бы создать инкремент нужно вычитать ВСЮ бд т.е. прочитать каждую страницу и сверить её LSN.
Чем больше БД тем дольше занимает этот процесс. ptrack умеет отслеживать изменения памяти на лету с почти нулевым оверхедом (1-2%), а pg_arman умеет читать эту информации для того, что бы без каких либо лишних движений делать бекап.
bolk
01.11.2016 18:11Используйте «with», с ним код становится чище и его можно читать сверху вниз, а не петлять среди блоков join'ов.
Только оптимизатор Постгреса (в отличие от Оракла) не умеет заглядывать за «with», это для него как отдельный запрос, так что такое разбиение может (и скорее всего будет) работать существенно медленнее исходного запроса.
Если можете написать запрос без временных таблиц — не раздумывайте и напишите! Обычно CTE, создаваемое конструкцией «with», является приемлемой альтернативой. Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл…
А какая база делает иначе? Как СУБД может предугадать сколько данных в таблице будет? Вроде логично всё. Не перепутаны ли тут временные таблицы с временными вьхами?
KlimovDm
Вы не поясните выкрик «Боль!» в заголовках? Это те грабли, на которые вы сами наступали?
darthunix
И не просто наступал, но и героически преодолевал последствия. До сих пор вздрагиваю.