Все, кто имел дело с базами данных, знают, что там можно хранить бинарные данные большого объема. Чаще всего, это загруженные в БД файлы. Достаточно давно меня стал посещать такой вопрос - а какие накладные расходы на хранения файлов в БД? Понятно, что они есть, но вот какие и от чего они зависят? Но каждый раз были вопросы и задачи поважнее. И вот недавно произошло то, что рано или поздно должно было произойти - одновременно у двух заказчиков возник вопрос, который, упрощая, выглядит так: “Почему мы загрузили такой-то объем данных, а место на диске занято больше?”. У одного из заказчиков был Oracle, у второго - Postgres. Конечно, можно было бы ответить уклончиво: “У каждой БД существую накладные расходы на хранение данных, вот у вас эти накладные расходы вот такие. Разработчикам СУБД виднее, как все должно быть.” И, может быть, заказчика такой ответ и устроил, но сам я понимал, что это просто отписка, так как у нас нет конкретного ответа. Несолидно как-то. Поэтому, давайте разбираться.
Немного банальной и скучной теории
В базе данных Oracle существует тип данных - BLOB (Binary Large OBject). Это значит, что в любой таблице можно создать поле с типом BLOB и писать туда бинарные данные. При создании такого поля создается отдельный LOB сегмент, в который данные непосредственно пишутся. В исходной же таблице хранится только Lob Locator - указатель на данные. (тут мы специально не будем рассматривать случаи с хранением in row небольших данных до, примерно, 4000 байт). В Postgres ситуация отличается - там есть механизм хранения больших объектов, но все эти объекты хранятся в одной системной таблице с именем pg_largeobject. Мы же в своей таблице создаем поле типа loid (Large Object Identifier), в котором храним уникальный идентификатор большого объекта (Large Object, LOB).
Oracle
Итак, при создании BLOB поля создается: LOBSEGMENT - сегмент, в котором данные хранятся и LOBINDEX - индексы по LOB объектам. Лобиндексы занимают совсем немного места, в нашем случае объем лобиндекса составил 0.22 % от загруженного объема и, в среднем, около 85 байт на один LOB-объект.
Хранение данных в лобсегменте устроено достаточно просто: данные хранятся в блоках данных БД аналогично тому, как хранятся файлы в кластерах файловой системы диска - для хранения выделяется нужное количество блоков, но если в каком-то блоке осталось свободное место, оно больше не может использоваться другим файлом и остается просто неиспользованным.
Аналогия с файловой системой - если мы посмотрим размер файла в свойствах файла в Windows, то увидим две строки:
Размер: 4,61 КБ (4 726 байт)
На диске: 8,00 КБ (8 192 байт)
Так как размер кластера ФС (в моем случае) 4КБ - то 4,61 КБ не поместятся в один кластер(блок), поэтому для хранения файла выделяется два кластера и на диске этот файл занимает 2 * 4 КБ = 8 КБ.
Размер блока в LOB-сегменте зависит от параметра CHUNK, который задается при создании LOB-сегмента и должен быть кратен размеру блоку табличного пространства. По умолчанию он равен 8192 байт.
Этих знаний вполне достаточно, чтобы составить вот такой запрос по таблице с бинарными данными. Тут 8192 - размер блока. Если у вас данные хранятся в сегменте с другим значением параметра CHUNK, нужно указать его в запросе вместо 8192
select bin,
dbms_lob.getLength(bin) as LOBSIZE,
CEIL(dbms_lob.getLength(bin)/8192) as BLOCK_COUNT,
CEIL(dbms_lob.getLength(bin)/8192) * 8192 as SIZE_ON_DISK,
(CEIL(dbms_lob.getLength(bin)/8192) * 8192)-(dbms_lob.getLength(bin)) as UNUSED_SPACE
from media_file;
bin - это BLOB поле таблицы
LOBSIZE - размер LOBа, который хранится в строке
BLOCK_COUNT - количество блоков, затраченных на этот ЛОБ (размер ЛОБа, деленный на размер блока и округленный до целого в большую сторону)
SIZE_ON_DISK - это размер, которые эти выделенные блоки занимают на диске
UNUSED_SPACE - это SIZE_ON_DISK минус LOBSIZE.
Хорошо видно, что на каждый объект тратится какое-то количество место на диске, которое больше ничем не будет использовано.
График зависимости накладных расходов от размера файла будет выглядеть так
Таким образом накладные расходы на диск зависит от двух параметров:
От того, насколько кратен размер файла размеру блока. В абсолютных значениях размер неиспользуемого места каждого объекта находится в диапазоне от 1 до 8191 байт и статистически средним значением должно быть размер половины блока - 4096 (при стандартном размере блока).
От размера файла. Чем больше размер файла, тем меньше в процентном соотношении будет составлять это неиспользуемое место. Так, для файла размером около 1 МБ даже размер неиспользуемого места в 8191 байт составляет всего 0,78 процента. Однако чем меньше файл, тем ощутимее становятся эти потери и это особенно сильно будет проявляться на файлах маленького размера.
Postgres
Таблица pg_largeobject - это системная таблица, предназначенная для хранения больших объектов(подробное описание таблицы - https://postgrespro.ru/docs/postgresql/17/catalog-pg-largeobject). Хранит данные она не одним большим куском, а разбивая их на порции. Размер порции - BLCKSZ/4. Так как размер блока(страницы) данных в Постгрес можно поменять только при компиляции, то во всех стандартных установках он равен 8К, соответственно, размер порции большого объекта - 2К. Пока все выглядит логично - при размере чанка 2К в одном блоке данных поместятся 4 чанка.
Выполним запрос, чтобы увидеть, как хранится большой объект с id 6344453 и размером 8 451 байт.
select loid, pageno, data, length(data) from pg_largeobject
where loid=6344453
order by 1,2;
Видим, что таблица pg_largeobject состоит из трех полей: loid - ID большого объекта, pageno - номер чанка(куска) большого объекта, data - непосредственно бинарные данные с максимальным размером поля data - 2048.
В Postgres, в отличие от Оракла, таблица с большими объектами хранится в странице (блоке) данных по тем же правилам, что и все остальные данные. Подробное описание - https://postgrespro.ru/docs/postgresql/17/storage-page-layout. Не вдаваясь в излишние подробности, выделим для себя следующие факты:
Размер блока(страницы) данных - 8192 байт
Каждая страница содержит заголовок страницы 24 байта и массив идентификаторов для кортежей, размером 4 байта.
Размер чанка большого объекта - BLCKSZ/4 = 2048 байт
Один кортеж таблицы pg_largeobject весит чуть больше 2К, так как содержит еще поля loid, pageno (а также служебная поля - https://postgrespro.ru/docs/postgresql/17/ddl-system-columns)
Этого достаточно, чтобы понять - четыре кортежа pg_largeobject в одну страницу не поместятся. Поместят только три, при этом останется немного меньше 2048 (около 1900) байт.
Это свободное место доступно для записи, но для этого нужно найти подходящий по размеру кусок, а при больших размерах файла количество “хвостов” файлов (кусков меньше 2048 байт) будет на порядки меньше, чем количество таких занятых на три четверти страниц. То есть полезные три чанка ЛОБа (2048 * 3 = 6144) будут занимать страницу памяти размером 8192 и не будет найдено нужного блока данных, чтобы заполнить свободное место.
При неблагоприятных обстоятельствах (размеры файла полностью кратны 2048 или размер файла на порядок больше размера страницы) количество потерянного (свободного, но неиспользуемого) места будет стремиться к 25 процентам.
То есть в Postgres достаточно дорого хранить большие файлы. Чем меньше средний размер файлов, тем ниже накладные расходы. Накладные расходы около 10 процентов будет только у файлов менее 16 КБ. И только при среднем размере файлов около 2 КБ накладные расходы будут составлять единицы процентов.
Сам собой напрашивается вопрос - почему в качестве чанка большого объекта было выбрано blocksize/4 ? Ведь если сделать его на пару десятков байт меньше, на одну страницу данных помещалось бы как раз четыре кортежа большого объекта. И для методики хранения TOAST (The Oversized-Attribute Storage Technique, Методика хранения сверхбольших атрибутов), принцип работы которой аналогичен принципу работы хранения LOB, используется размер порции около 2000 - “чтобы на странице помещались четыре строки порций, то есть размер одной составляет порядка 2000 байт”, https://postgrespro.ru/docs/postgresql/current/storage-toast#STORAGE-TOAST-ONDISK. Ответа, само собой, у меня нет.
С Large Object в Postgers получается противоречивая ситуация - это единственный вариант хранить данные большого объема (до 4 ТБ), но это обойдется очень неэффективно с точки зрения использования диска. Если размер данных не очень большой, то для хранения данных меньше 1 ГБ более эффективно использовать тип данных bytea - https://postgrespro.ru/docs/postgresql/17/datatype-binary