Типичные ошибки при работе с PostgreSQL. Часть 1

    Чуть более месяца назад в Москве состоялась крупнейшая конференция постгресового сообщества 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. Живые, меняющиеся данные попадаются в таких базах редко. Определитель с тем, что у вас — если архив, то можно подумать о том, как его вынести куда-то. И, кстати, можно к нему же из базы обеспечить доступ. Тогда и приложение менять не надо: для него ничего не изменится.

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

    [Продолжение следует.]
    Postgres Professional
    349,00
    Российский вендор PostgreSQL
    Поделиться публикацией

    Комментарии 38

      +8
      В базе удобно потому, что, по моему опыту, бизнес регулярно выдает срочные правки: сию секунду убрать или вставиь то-то и то-то.

      Мне кажется. с таким же успехом можно просто заходить на сервер и править код. Какая разница то, срочные правки же!

        +2
        Править код на проде — последнее дело. Не знаю, почему это так популярно в среде разработчиков БД. Еще и преподносится как хорошая практика…
          –1
          Вот вам пример из жизни

          Бизнесу надо запретить почти всем пользователям выполнять операцию, которая раньше была вполне себе легитимной. Например сделать это новой настройкой уровня доступа. Естественно рассылаются новые должностные инструкции пользователям, согласовывается новое ТЗ/ЧТТ на изменение функционала поддерживаемой системы.
          И конечно ждать его величество Бизнес очень не любит.

          Что лучше?
          Воткнуть триггер сегодня чтобы пользователи гарантированно перестали делать чего они не должны, и затем выкатить новый релиз по стандартной схеме?
          или уповать на новые инструкции в ожидании релиза?
          или запускать процесс обычного нового ЧТТ/ТЗ по аварийной схеме с максимально быстрой выкаткой?
            0
            Лично я на подобные случаи стараюсь иметь несколько вариантов решения:
            1) механизм релиза-хотфикса, который можно катить в любое время и который не блокируется текущими доработками
            2) механизм для запуска миграций

            Оба варианта выше в любом выкатываются из репозитория и предварительно проходят Code Review.

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

              Да достаточно попасть под действие Sarbanes-Oxley Act (например, начать листиться на американской бирже и работать с деньгами), и у разрабов не будет никакого write-доступа ни к данным, ни к рабочему коду, все деплои только через код-ревью в 4 глаза.

                0
                Да, SOX быстро заставляет даже не думать о «бизнес захотел быстро и я пошел на прод»
                  0
                  А вас не затруднит привести пункт в этом акте относительно этого вопроса?
                    0

                    Если честно, то я даже и не читал этот закон. К нам в один прекрасный день пришли аудиторы, посмотрели на разрабов, провели интервью с ними, посмотрели, как мы храним и пишем код, как релизим, и т.п… А через полгода из материнской компании спускают требования следовать рекомендациям, а там и отбор доступа у разрабов в прод, и вот это вот все. Но радует, что CI/CD-пайпалайн и зеленый мастер удалось отстоять.

                      0
                      Вообще в самом акте про доступ разработчиков вообще ничего не говорится, он про деньги.
                      В комментариях вполне разумные требования — чтобы на продакшене не болтался непойми кто, чтобы пароли менялись, чтобы бывшие сотрудники не сохраняли доступ и т.д.
                      Помимо прочего требуется иметь дизастер рикавери план — что делать в случае факапа. Судя по комментариям факап представляется такой мерзостью, о которой и думать противно; вот никто и не думает.
                  0
                  То есть вы за вариант «запускать процесс обычного нового ЧТТ/ТЗ по аварийной схеме с максимально быстрой выкаткой»? просто потому что Бизнес захотел быстро?

                  Вариант с отсутствием доступа вообще весь тред делает бессмысленным, поэтому предлагаю его не рассматривать.
                  Пусть без прямого доступа, но передать временную «миграцию» отдельно от общего решения всегда можно. Отличаться от прямого доступа это будет только в части того кто будет запускать скрипт.
                    0
                    Да, именно такой вариант. Если что-то надо срочно для бизнеса, то можно форсить рабочий процесс, а не начинать работать мимо процесса, CI, тестов и всего прочего, что делает разработку надежнее.

                    На своих проектах я стараюсь добиться того, чтобы накат, в том числе и bugfix-ов и hotfix-ов, был не болью, а быстрым и понятным процессом.
                      0
                      На своих проектах я стараюсь добиться того, чтобы накат, в том числе и bugfix-ов и hotfix-ов, был не болью, а быстрым и понятным процессом.

                      с подобным стремлением нельзя не согласиться.
                  +1

                  А что мешает делать это через систему конфигурации или, о боже, добавить в приложение настройки прав для пользователей, которые можно хранить в бд? Более того, почти все системы так и делают.


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

                    0
                    А что мешает начать читать внимательно?

                    Бизнес не требовал раньше подобное разграничение доступов. Почему же его надо было заранее делать? Теперь ограничение потребовалось. Вся суть примера в изменившихся требованиях и вариантах их реализации.

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

                    Идея оставить заказчика наедине с его проблемами до ближайшего релиза не предоставляя обходных решений еще хуже.
                    Дело не в плохости «добавить на скорую руку какой-то триггер». Если триггер взвели и одновременно с этим в системе контроля версий он тоже появился и в реализуемом постоянном решении это учтено, то получаем win-win. Бизнес доволен оперативностью и спокойно ждет оплаченную доработку. Разработка довольна, что не надо делать все аврально.
                      0
                      Если триггер взвели и одновременно с этим в системе контроля версий он тоже появился и в реализуемом постоянном решении это учтено, то получаем win-win.

                      Так же можно и сразу менять код на проде, с бекпортом в репу. Проблема в том, что этот код не тестируется, не проходит CI и прочее-прочее-прочее. И, обычно, приводит к еще большим авралам.


                      Бизнес не требовал раньше подобное разграничение доступов. Почему же его надо было заранее делать?

                      Я, конечно, дико извиняюсь, но есть вещи, которые подразумеваются в системе для бизнеса. Эта как раз одна из них.

                        0
                        Можно любую ситуацию доводить до абсурда. Менять код БД на несколько порядков проще чем менять код JavaEE какой-нибудь. Об этом и говорится в статье.

                        Мой изначальный коммент был про то, что возможны ситуации, когда изменение кода в БД на проде даст быстрый желаемый результат без тяжелых последствий.
                        Это не значит что надо переходить на Production Driven Development.
                        Это значит, что при рассмотрении конкретной ситуации, надо учитывать подобную возможность.
                        Если система доставки спроектирована как рассказывает VladimirVerstov
                        выше, то подобная возможность не будет использоваться.
                        Если система доставки нового кода от 4х недель и больше, то нельзя догматично отбрасывать возможность спасти больше данных заказчика прямым вмешательством (и конечно работать в сторону уменьшения времени доставки).
                          0
                          Мой изначальный коммент был про то, что возможны ситуации, когда изменение кода в БД на проде даст быстрый желаемый результат без тяжелых последствий.

                          Количество таких ситуаций настолько мало, что рассматривать их всерьез не имеет смысла. То есть, если ваше приложение изначально разрабатывалось с прицелом на использование триггеров, то это еще пол беды (но это опять же сравнимо с Production Driver Development), а если нет, то вы вполне вероятно отхватите столько багов, что вполне возможно, дождетесь следующего релиза.

                            0
                            «Количество таких ситуаций настолько мало»
                            Мало.
                            Но одного раза может быть достаточно для прикрытия бизнеса.
                –1
                Мне хотелось бы увидеть метод, как можно реализовать какое-нибудь «немедленно уберите Пупкиных отовсюду, сию же секунду!!!11»
                Ответ «через недельку, бог даст, выкатим» не считается — ну или пусть программист оплачивает потери бизнеса самостоятельно.
                0

                Имхо, все эти рекомендации актуальны практически для любой реляционной субд. Не считая мелких различий, общие принципы остаются везде одни и те же. Начиная с антикварных Адабас и SystemR образца 1971 года.
                И вообще, спасибо товарищам Бойсу и Кодду, за создание стройной и красивой теории баз данных.

                  0
                  Правила полезные, нужно однозначно их распечатать, чтобы были как знаки в ПДД — всегда на виду. Спасибо!
                    +2
                    Не надо это никуда печатать. Тут всё довольно спорно.
                      0
                      К сожалению, в расшифровку не попало вступление. В нем я рассказывал, что
                      1. Я не самый умный
                      2. После выступления обязательно найдутся три типа граждан
                      2.1 «Вот она, правда!»
                      2.2 «Это спорно»
                      2.3 «Это полный бред»
                        0
                        Ваши 3 типа граждан это такие:
                        2.1 — данных 100 МБ
                        2.2 — данных 100 ГБ
                        2.3 — данных 100 ТБ
                        Ваши рекомендации для 2.1, просто в статье это не указано (может я пропустил)
                          0
                          Ну вот видите, как вы славно умеете классифицировать. Это же замечательно!
                    0
                    Про «подкорячивание кода на проде наживую» не согласен. Ничем не лучше непроверямого код фикса и выката версии приложения в те же сроки — приведет к тем же проблемам (что-то не протестировали нормально и оно подохло вместе с данными)
                      +1

                      Уже 10 лет как не смешно про удобство кода в базе, я уже не говорю про "долгую" компиляцию и выкладку, когда все на микросервисах с автодеплоем. Это базизм головного мозга — когда ты базист, код в базе кажется самым удобным)))

                        0
                        А, ну а как же. Приходили подобные люди, устроили себе сплитбрейн на одной базе.
                          –1
                          Если вам требуется «select 1 from dual», то оно конечно хорошо и можно заряжать ваш ORM. Но когда надо производить реальные манипуляции с данными, я не понимаю, как ваш «уютный гавнакодик» может работать быстрее, чем код внутри базы.
                            0
                            Немного забавно, но я про скорость работы ничего не говорил и тем более об ORM. Задача СУБД — хранить данные, но если она как-то где-то умеет хранить спокойно лежащие данные быстрее, ну ладно.
                              –1
                              Забавно то, что буква У в слове СУБД как бы намекает, что речь идёт не только о хранении). Чтобы хранить спокойно лежащие данные достаточно регулярных файлов на файловой системе.
                              0
                              О нет, ведь скорость работы, которая отличается на десятки милисекунд это же самое главное!
                              Ни качество разработки, ни скалируемость, ни выразительность, ни удобство, ни стабильность — а именно скорость.

                              Ага.
                                –1
                                Как по мне, так язык ADA достаточно выразителен и удобен. Стабильность и скалируемость база даёт из коробки. Вообще всё дело просто в объёме данных, с которыми требуется что-то сделать за один логический блок бизнес процесса. И конечно это не про миллисекунды. Как только объём данных логического блока становится каким-то заметным, бороться приходится за эффективность и попытки сделать это сторонним кодом не выдерживают испытаний жизнью.
                                0
                                На небольшом проекте работать быстрее возможно не будет. Но если приложение рассчитанно на горизонтальное масштабирование, то его его легче произвести с логикой в приложении, чем масштабировать транзакции БД.
                              0
                              половина статьи о консистентности данных и половина о том что не надо хранить архив в базе, но если архив не в базе консистентность может пропасть и где потом искать счёт не понятно, из хороших решений только иметь продакшен базу и точно такую же для архива, и данные переодически переносить из одной в другую, тоесть все истории изменений и продакшен базе создаються, но переодически чистяться, база будет большой, но это же архив туда не так часто лезут, зато всегда можно быть уверенным что данные легко найдуться
                                0
                                Вы не могли бы несколько развернуть свою мысль? Как-то у вас не вполне понятно получилось.
                                  0
                                  если по поводу архивации то перенос данных в другое место и проверка что ничего не потерялось наверно самое сложное особенно если структура архива совершенно другая и мне кажется что будет намного проще если архив и база с текущими данными будут иметь одну и туже структуру, основные таблицы + аудит таблицы, все изменения данных хранятся в рабочей базе также как и в архивной и переодически самые старые потираются освобождая место и кладуться в архивную, будет получаться что в архиве все данные из рабочей базы на момент архивации + предыдущие изменения, архивная база конечно будет огромной, но зато ничё не пропадёт, остаётся вопрос что делать если структура данных меняется, но тут видимо ничего не поделаешь ничего не удалять/переименовывать, а только добавлять новое
                                    0
                                    Если я все правильно уловил в вашем посте, то, боюсь, вы меня не вполне поняли.

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое