Чуть более месяца назад в Москве состоялась крупнейшая конференция постгресового сообщества PGConf.Russia 2019, собравшая в МГУ свыше 700 человек. Мы решили выложить видео и расшифровку лучших докладов. Выступление Ивана Фролкова с разбором типичных ошибок при работе с PostgreSQL было отмечено лучшим на конференции, поэтому мы начнем с него.

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



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

Главный вывод, который я смог сделать из того, что я видел, довольно неожиданный: фактически любое приложение при должной настойчивости можно заставить работать. Был замечательный проект (я не могу упоминать все компании, с которыми мы работали), в котором еще более замечательное приложение создавало таблицы миллионами. Выглядело это так: в понедельник система работает неплохо, а уже в пятницу она практически не работает. На выходные дни запускают VACUUM FULL, и в понедельник она опять работает хорошо. Оказывается, над PostgreSQL можно вот так издеваться, и всё это довольно долго будет жить и работать. Другой товарищ сделал странную вещь: у него всё было построено на триггерах, процедур не было вообще. То есть большую часть таблиц трогать нельзя, сделать что-либо не получалось, но и эта база жила.

Он объяснял это так: «база переходит из одного консистентного состояния в другое консистентное. Если я повторно вкачу данные, она сломается. Но так как у меня триггеры и уникальный ключ, я данные повторно вкатить не могу». Подход дикий, но в то же время некоторый смысл в этом есть. Может, делать надо было по-другому, но учитывать особенности заказчиков тоже надо. Первая ошибочка, о которой я буду говорить, это:



Вот реальный пример, с которым я сталкивался. На слайде вы видите, как именовалась одна и та же сущность в разных колонках. Можно было бы еще и с пробелами. Другие объекты именовались так же непоследовательно. Если вам что-то нужно взять в другой таблице, то нужно посмотреть, как оно там называется, то же самое ли это. Если у вас есть id_user и user_id в одной таблице, работа начинается с исследования: что бы это всё значило.

У других клиентов все объекты именовались так: две буквы, дальше пять цифр. Сразу скажу, это была не «1С». Зачем они так делали — не знаю: никакой логики в этом не прослеживалось, но мое дело оптимизировать запросы.

Еще пример: часть названий на русском, часть на нерусском, но с каким-то русским акцентом. Это затрудняет понимание и плодит новые ошибки. Сам я стараюсь называть колонки так, как будто рассчитываю на сервис, который из этих имен колонок будет автоматически делать нормальные названия столбцов в каком-нибудь отчете. В реальной жизни последовательно именовать, к сожалению, не очень получается — и у меня в том числе. Особенно это сложно при коллективной разработке. Но стремиться надо.



Еще важная причина именовать последовательно: имена объектов доступны через запросы к метаданным, то есть имена это тоже данные. Вы сможете написать запрос и выбрать, скажем, все картинки — вообще все картинки — из базы.



Понятные метаданные — это очень удобно. Особенно если учесть типичные проблемы с документацией — а по моему опыту документация обычно либо отсутствует, либо неполная, либо неверная, либо и то, и другое: потому что задача написать хорошую документацию по сложности сравнима с задачей написания самого кода. Так что лучше, когда код самодокументированный. И логичное, последовательное именование объектов этому способствует, а когда что-то непонятно, приходится писать код сниппета и смотреть, как это работает. Один раз это ничего, два — ничего, но когда ты весь день только этим и занимаешься, это выматывает.



Реальный случай: у очень серьезной организации, с которой мы работали, была база — документооборот на Oracle. Мы ее перенесли в Postgres. Одним из условий договора было то, что мы наложим FOREIGN KEYs. Их там не было и, к сожалению, наложить нам их не удалось: оказалось что в таблицах очень много каких-то «левых» строк, и что с ними делать, никто не знает, включая заказчика.

Когда нужно не progress-bar-ы смотреть, а работать с документами на выплату денег, то ситуация грустная. Очень хорошо помогает, когда по договору программист сам оплачивает ошибки, и желательно, чтобы суммы были большие — тогда просветление наступает в течение минут, наверное, пятнадцати. Сразу появляются constraint-ы, сразу всё начинает проверяться.

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



С другой стороны, часто можно услышать, что constraint-ы снижают производительность. Да, снижают, но, если вы хотите иметь корректные данные, для вас других вариантов просто нет. Если у вас приложение, учитывающее количество посещений магазина покупателями, то тогда могут остаться неточности, которые на статистике не особенно скажутся, а если мы считаем деньги, constraint-ы нужны обязательно.

Имена constraint-ы обычно генерируются ORM-ом или системой, и именованием constraint-ов обычно никто специально не заморачивается — а зря! Когда вы будете в дальнейшем обрабатывать ошибку, то по имени constraint-а, вы сможете дать внятное сообщение пользователю, классифицировать ошибку и сообщить, нужно ли повторно пытаться выполнить операцию, или что эту операцию выполнять уже не обязательно, или что повторять ее просто нельзя.

Еще одна вещь, которую я тоже не встречал, но которую очень рекомендую: на все важные финансовые (и не только финансовые) операции проверок должно быть как минимум две. Дело в том, что рано или поздно вы полезете что-то менять в коде, и очень может быть, что одну из проверок вы сломаете. Тогда вас спасет вторая. Если сделаете три — тоже неплохо.



Часто возникает вопрос: где проверять корректность данных. На клиенте или на сервере? По-моему, очевидно, что проверять нужно и там, и там. У вас ошибка в клиенте, тогда сервер не
пропустит, или у вас ошибка на сервере, тогда хотя бы клиент поможет отследить ее. Вопрос несколько дискуссионный, и мы плавно переходим к теме: где держать логику базы: в приложении или в базе?

В базе удобно потому, что, по моему опыту, бизнес регулярно выдает срочные правки: сию секунду убрать или вставить то-то и то-то. Если у вас логика в компилируемом коде, то вам нужно собрать, задеплоить, посмотреть, что получилось. Часто это уже просто невозможно. В базе это делать удобней. Но есть известный афоризм: опытные программисты на фортране пишут на фортране на любом языке. Процентов 80 серверного кода написаны совершенно в процедурном стиле: у нас есть функция «получить_юзера()» и она возвращает тип «юзер», а если «получить_список_юзеров()», то она возвращает массив «юзеров». На Java такие вещи писать действительно удобнее, чем на SQL или pgsql.



С другой стороны: зачем вам функция «получить_юзера()»? Вы просто берете его в таблице или в представлении. Раз у вас реляционная база, то и писать надо, как мне кажется, реляционно. Тут важно, во-первых, четко определиться с какими данными мы работаем: если данные у нас мусорные или полумусорные, то и результат будет соответствующий, и убиваться, наверное, особо не следует. Если данные для нас важны, если это деньги, имущество или юридические операции, то нужны constraint-ы и чем больше, тем лучше. Повторю: лучше не выполнить операцию, чем выполнить ее неправильно. И не надо писать процедурный код в реляционной базе: сильно пожалеете.



Я видел таблицу с 30 тысяч строк (товары), в которой запрос «покажите список актуальных товаров» выполнялся около секунды. Видимо, им удалось создать «красивую и сложную» схему БД. Лично я считаю, что, если вы делаете что-то сильно мудреное, то, скорее всего, вы либо делаете что-то не так, либо у вас действительно очень, очень сложная задача. Если у вас какой-нибудь магазин или обычное приложение для учета людей, то вряд ли там есть очень сложные взаимоотношения между сущностями.

Когда я начинал свой профессиональный путь, таблица в DBF-файле в 60 мегабайт в банковской системе казалась очень большой, а сейчас 60 мегабайт это вообще ничто — железо стало лучше, софт стал лучше, всё работает быстрее, но остается вопрос: откуда у вас столько данных? Очень большие, пухлые базы становятся такими обычно из-за архивов. В любой СУБД и в PostgreSQL много усилий потрачено на то, чтобы обеспечить консистентную конкурентную работу приложений. Архив скорее всего не меняется, и большинство возможностей СУБД для работы с ним вообще не нужны. Стоит подумать о вынесении его наружу СУБД.



То и дело с этаким комиссарским прищуром задают вопрос: а потянет ли PostgreSQL базу такого-то объема. Но тут сама постановка вопроса странная: положить данных в базу можно сколько угодно, сколько хватит места на диске, столько и будет лежать. Вопрос в том, как, например, бэкапить архивов в петабайт, куда вы положите полный backup и сколько вы будете его снимать. Я сильно подозреваю, что хотя бы частично эти требования к объемам связаны с желанием продавцов оборудования продать вам побольше.

Если вы храните документы в базе, вы их там обрабатывать вряд ли будете: экселевскую таблицу можно, конечно, модифицировать и на сервере, но это странное занятие. Скорее всего такие файлы будут вообще readonly. Лучше хранить ссылки на документы, а сами в каком-то внешнем хранилище. В конце концов можно хранить цифровую подпись таблицы — чтобы ее не поменяли (если вы решите соответствующие законодательные вопросы).

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



Еще одна причина того, что база большая — ненужные индексы. Баз без индексов я не встречал, зато довольно часто встречал базы, где несколько индексов на одни и те же колонки в одном и том же порядке. База это позволяет сделать. Когда вы создаете индекс, пожалуйста, посмотрите, не дублирует ли он уже имеющийся. Посмотреть, какие индексы не нужны, можно, заглянув в pg_stat_user_indexes, чтобы понять насколько активно индекс используется. Может, он вообще не требуется.

Я натыкался и на ситуации (кстати, типичные), когда очень большая таблица не секционирована. Во всех СУБД большие таблицы лучше секционировать, но в PostgreSQL это особенно актуально из-за нашего любимого VACUUM-а. Я бы посоветовал секционировать таблицы начиная, наверное, со 100 гигабайт. Может быть начиная с 50. Я видел и несекционированные терабайтные таблицы, и они жили, правда, на SSD. Но это многовато, лучше было бы их порезать.



И еще одно наблюдение: практически все базы большого объема это архивы append only. Живые, меняющиеся данные попадаются в таких базах редко. Определитель с тем, что у вас — если архив, то можно подумать о том, как его вынести куда-то. И, кстати, можно к нему же из базы обеспечить доступ. Тогда и приложение менять не надо: для него ничего не изменится.

Некоторые из этих наблюдение из разряда «лучше быть богатым и здоровым, чем бедным и больным». Часто, во-первых, есть унаследованный код. Во-вторых, что-то неожиданное произошло, о чем-то не подумали, и получается что все не так красиво, как хотелось бы. Но тем не менее: не надо сильно мудрить. Помните, что если вы сильно мудрите, то, скорее всего, делаете что-то не то.

[Продолжение следует.]