klink0v | Postgres: bytea vs LO OID (Reply)

Некое продолжение предыдущего поста.

Полез я изучать матчасть на тему того как в Postgres-е в принципе могут храниться бинарные объекты. Нашел вот такую статью в их вики. Если вкратце, есть три способа.

Text + Base64
Bytea
Large Objects OID (BLOB)

Первый вариант сразу добавляет примерно треть к объему базы данных и не несёт каких-то значимых преимуществ, поэтому сразу нафиг. А вот между Bytea и LO OID выбор уже не столь очевиден. Не знаю что там творится у программистов, подозреваю что им по большому счёту пофиг что использовать. С точки зрения сисадмина же картина выглядит примерно так.

У LO OID (BLOB) есть два очевидных недостатка: не пролезают в логическую репликацию и очень долгий restore из дампа. В разы дольше чем с bytea, причём ещё и не параллелится. Чуть менее очевидный недостаток: такие BLOB-ы не привязаны к какой-то конкретной таблице, а являются принадлежностью всей базы в целом. То есть в случае миграции базы их не получится переносить "по частям", а объем там может быть ого-го.

Bytea на этом фоне выглядит интереснее, но и с ним не всё радужно. Если делать текстовый дамп, то в оном такие объекты многократно распухают в размере из-за необходимости перекодирования в HEX. И теоретически возможна ситуация, когда результирующее представление объекта вылезет за 1 ГБайт и дамп просто обломится, т.е. его не удастся снять в принципе. Есть ещё некоторые приколы с пользовательскими представлениями данных и нуль-терминальными строками, но это больше по части программистов. С точки зрения сисадмина Postgres при работе с таблицами, содержащими bytea-столбцы, начинает кушать больше оперативной памяти. И есть некоторые экзотические ограничения из серии что если размер bytea-полей превышает 2 Кбайта, то в таблице не может содержаться более четырех миллиардов таких строк. А размер одного bytea-объекта ограничен одним гигабайтом. Но это какие-то сильно нестандартные случаи, конкретно ко мне они отношения не имеют.

То есть получается смешная ситуация. В случае с bytea возникают проблемы с дампами, а при LO OID — с восстановлением из дампов. Шо то херня, шо это херня. Да и остальные достоинства-недостатки плюс-минус уравновешивают друг друга.

Поэтому как мне видится со своей колокольни, критерии принятия решения примерно следующие. Если нужна логическая репликация и/или нет возможности оперировать бинарными бэкапами на уровне физического представления (я имею в виду те которые pg_basebackup), то однозначно выбирать bytea. Во всех прочих случаях, а особенно если объекты весят больше пары килобайт, то LO OID всё же предпочтительнее. Хотя бы потому что они расходуют меньше вычислительных ресурсов в "повседневной" работе, плюс можно бэкапить и хранить реляции отдельно от BLOB-ов.

Всем правильного выбора хранения binary objects в postgres-е.