26.11.2025 16:10
slonik_pg 0 1200 Источник

Идеальная архитектура при работе с файлами в СУБД подразумевает, что файлы выносятся в отдельное хранилище. Вместе с тем не всегда пользователи следуют этому правилу: одним разработчикам так проще оперировать файлами, другие вынуждены реализовывать этот формат из-за требований к хранению исходного запроса и подписи, третьи не хотят поднимать и администрировать дополнительные хранилища. Таким образом, многие сталкиваются с необходимостью хранить данные в СУБД, но далеко не все понимают, каким способом это реализовать.

Варианты хранения файлов в СУБД PostgreSQL

В PostgreSQL пользователям доступно несколько вариантов хранения файлов в СУБД. Среди них:

  • простые таблицы;

  • pg_largeobject — каталог для больших объектов.

На каждом из них остановимся подробнее.

Простые таблицы

Обычные таблицы PostgreSQL позволяют работать с данными типа text, bytea, Jsonb.

При этом API PostgreSQL для работы с файлами формирует ряд простых команд, среди которых:

  • pg_ls_dir — получение списка файлов и директорий в указанной директории;

  • pg_stat_file — получение метаданных файла (размер, время изменения, права доступа и другие);

  • pg_read_file — чтение текстового файла и возвращение его содержимого в виде строки;

  • pg_read_binary_file — чтение файла (включая бинарные данные) и возвращение содержимого в формате bytea.

Примечание: полный список функций доступен здесь, но они не предназначены для работы с пользовательскими данными.

Вместе с тем, у простых таблиц есть два ограничения на работу с файлами.

Первое ограничение простых таблиц

Простые таблицы по умолчанию не подходят для работы с файлами (и любыми другими данными) объёмом более 1 ГБ. Так, в случае попытки их вставки будет выведена соответствующая ошибка.

create table user_data (
  id bigint
, data_bytea bytea
);
 
# insert into user_data (id, data_bytea)
Values (8, pg_read_binary_file('/tmp/file.1gb.bytea'));
ERROR: file length too large
Time: 669.132 ms
 
# select pg_read_binary_file('/tmp/file.1gb.bytea');
ERROR: file length too large
Time: 666.684 ms

Этот барьер можно обойти, если вставлять данные фрагментами. Например, дробить файлы объёмом 5 ГБ на части по 100 МБ и хранить их в виде отдельных строк. Это позволяет обойти ограничение на размер поля, но усложняет архитектуру приложения и требует создания дополнительных таблиц для сборки фрагментов.

insert into user_data (id, data_bytea)
Values (81, pg_read_binary_file('/tmp/file.1gb.bytea', 0, 1024*1024*100));
 
insert into user_data (id, data_bytea)
Values (82, pg_read_binary_file('/tmp/file.1gb.bytea', (1024*1024*100)+1, 1024*1024*100));
 
insert into user_data (id, data_bytea)
Values (83, pg_read_binary_file('/tmp/file.1gb.bytea', (1024*1024*100)+1, 1024*1024*100));

Второе ограничение простых таблиц

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

Может возникнуть ситуация, когда хранилище работает штатно, транзакции проходят, но в какой-то момент вставка новых данных (особенно параллельная) начинает «тормозить» или зависать.

Смоделируем ситуацию: запускаем множественную вставку тяжёлых данных.

insert into user_data values (777, repeat('b', 10000)::bytea);

Чтобы понять причину зависания, обратимся к системному представлению pg_stat_activity. Посмотрим, чего именно ждут активные процессы:

SELECT pid,
       extract(epoch from now() - state_change) as sec_state_change,
       state_change,
       wait_event,
       wait_event_type,
       state,
       query
FROM pg_stat_activity
WHERE query LIKE 'insert into user_data%'
  AND state = 'active';

Результат:

pid   | sec_state_change |     state_change      |  wait_event   | wait_event_type | state  |                    query
---------+------------------+-----------------------+---------------+-----------------+--------+----------------------------------------------
 1013836 | 196.148285       | 17:58:03.280158+03    | ClientRead    | Client          | idle   | SHOW TRANSACTION ISOLATION LEVEL
 1013866 | 403.011387       | 17:54:36.417056+03    | BufferIO      | IPC             | active | insert into user_data (id,data) values ( 777
 1013867 | 403.011375       | 17 17:54:36.417068+03 |               |                 | active | insert into user_data (id,data) values ( 777
 1013868 | 403.011407       | 17 17:54:36.417036+03 | BufferIO      | IPC             | active | insert into user_data (id,data) values ( 777
 1013869 | 403.011383       | 17:54:36.41706+03     | BufferIO      | IPC             | active | insert into user_data (id,data) values ( 777
 1013870 | 403.011375       | 17 17:54:36.417068+03 |               |                 | active | insert into user_data (id,data) values ( 777
 1013871 | 403.011384       | 17:54:36.417059+03    | BufferIO      | IPC             | active | insert into user_data (id,data) values ( 777
 1013872 | 403.011389       | 17:54:36.417054+03    | BufferIO      | IPC             | active | insert into user_data (id,data) values ( 777
 1013873 | 403.011077       | 17 17:54:36.417366+03 | BufferMapping | LWLock          | active | insert into user_data (id,data) values ( 777
 1013874 | 403.011070       | 17 17:54:36.417373+03 |               |                 | active | insert into user_data (id,data) values ( 777
 1013875 | 403.011138       | 17:54:36.417305+03    |               |                 | active | insert into user_data (id,data) values ( 777
(12 rows)

На уровне мониторинга мы видим массовые ожидания событий BufferMapping и BufferIO.

Это происходит из-за того, что при вставке больших значений PostgreSQL вынужден «на лету» дробить данные на TOAST-чанки, сжимать их и искать свободное место для записи.

  • BufferMapping означает, что процессы конкурируют за возможность выделить новые страницы в буферном кеше под эти данные.

  • BufferIO показывает, что система уперлась в производительность дисковой подсистемы при записи этих страниц.

Это обусловлено выполнением сложных процессов «под капотом» в момент записи объектов. Об особенностях хранения больших значений подробно рассказано в документации и профильных статьях (например, здесь и здесь). Мы лишь отметим, что при высокой нагрузке этот механизм становится «бутылочным горлышком».

Для каждой пользовательской таблицы создается всего одна TOAST-таблица, вне зависимости от того, сколько колонок базовой таблицы содержат большие данные. Значения из всех столбцов попадают в это единое хранилище, имеющее жесткие ограничения:

  • максимальный размер — 32 ТБ;

  • максимальное количество строк (чанков) — около 4,2 млрд (ограничение int4 для идентификатора чанка).

Важно учитывать, что лимит в 4,2 млрд записей — это общее ограничение для всех столбцов таблицы суммарно, а не для каждого по отдельности.

Примечание: TOAST поддерживает четыре стратегии хранения столбцов на диске:

PLAIN — для значений фиксированной длины, таких как integer;EXTERNAL — для внешнего хранения несжатых данных;EXTENDED — для внешнего хранения сжатых данных;MAIN — для хранения внутри, но сжатых данных.

Механизм TOAST срабатывает в тех случаях, когда значение строки, которое должно храниться в таблице, по размеру больше, чем значение TOAST_TUPLE_THRESHOLD (обычно это 2 КБ). Соответственно, TOAST будет сжимать и/или выносить значения поля за пределы таблицы до тех пор, пока значение строки не станет меньше 2 КБ или уменьшить объем станет невозможно.

В результате принцип распределения объектов следующий:

  • если записываемые данные помещаются в исходную пользовательскую таблицу, то есть размером до 2 КБ, мы их пишем прямо в колонку;

  • если данные больше 2 КБ — мы их помещаем в специальную TOAST-таблицу, а в основной пользовательской таблице делаем ссылку на нее. 

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

Преодолеть этот барьер можно с помощью секционирования (partitioning) основной таблицы. В этом случае для каждой секции будет создана своя TOAST-таблица, что отодвинет общий лимит. Однако это требует от администратора БД существенных усилий по проектированию и обслуживанию схемы данных.

Хранилище для больших объектов large object

Также для хранения больших объектов в PostgreSQL предусмотрена системная таблица pg_largeobject, в которой каждый файл идентифицируется по OID, назначаемому при его создании. 

Примечание: поскольку pg_largeobject — системная таблица, ее нельзя секционировать.

API для работы с pg_largeobject напоминает стандартные файловые операции: объекты можно создавать, читать, изменять и удалять. Как и в случае с TOAST, здесь действуют ограничения: максимальный размер объекта — 32 ТБ, а допустимое количество объектов (строк) — около 4,2 млрд.

Для наглядности разберём, как работает этот механизм. Важно понимать, что в самой пользовательской таблице данные файла не хранятся, там записывается только ссылка (OID) на объект.

-- 1. Создаем таблицу, где будет храниться только идентификатор (OID) файла
CREATE TABLE lo (
  id bigint PRIMARY KEY,
  lo_oid oid
);
 
-- 2. Загружаем файл в хранилище и записываем полученный OID в нашу таблицу
-- (Предполагается, что файл '/tmp/file.2gb.bytea' уже существует на сервере)
INSERT INTO lo (id, lo_oid) VALUES (1, lo_import('/tmp/file.2gb.bytea'));
 
-- 3. Проверяем данные в пользовательской таблице
SELECT * FROM lo;
-- Результат:
-- id | lo_oid
-- ---+------------
--  1 | 2032353971  <-- Это ссылка на объект
 
-- 4. Проверяем системную таблицу pg_largeobject, где физически лежат данные (разбитые на страницы)
SELECT loid, pageno, data FROM pg_largeobject WHERE loid = 2032353971 LIMIT 2;
-- Результат:
--    loid    | pageno | data
-- -----------+--------+-------
-- 2032353971 |   0    | \x58546f...
-- 2032353971 |   1    | \x81234b...
 
-- 5. Удаление объекта
-- ВАЖНО: Удаление строки из таблицы 'lo' НЕ удаляет сам файл из pg_largeobject.
-- Его нужно удалять явно функцией lo_unlink:
SELECT lo_unlink(2032353971);

Однако у этого подхода есть нюансы. При интенсивной вставке можно столкнуться с конкуренцией за генерацию идентификаторов (OID) или исчерпанием их лимита. На уровне мониторинга это проявляется как ожидание блокировки OidGen:

select pid,wait_event,wait_event_type,state, left(query,15) from pg_stat_activity where backend_type = 'client backend';
 
 pid  | wait_event | wait_event_type | state |      left
------+------------+-----------------+-------+-----------------
977260 | ClientRead | Client          | idle  | SHOW TRANSACTIO
977309 |            |                 | active| INSERT INTO lo
977310 | OidGen     | LWLock          | active| INSERT INTO lo
977311 |            |                 | active| INSERT INTO lo
977312 |            |                 | active| INSERT INTO lo
977313 |            |                 | active| INSERT INTO lo
977314 | BufferIO   | IPC             | active| INSERT INTO lo
977315 |            |                 | active| INSERT INTO lo
977316 | BufferMapping| LWLock        | active| INSERT INTO lo
977317 |            |                 | active| INSERT INTO lo
977318 | BufferIO   | IPC             | active| INSERT INTO lo
977426 |            |                 | active| select pid, wai
(12 rows)

Таким образом, в pg_largeobject:

  • можно хранить до 32 ТБ и 4,2 млрд объектов;

  • не поддерживается секционирование;

  • при удалении строки (oid) из пользовательской таблицы, данные в pg_largeobject остаются незатронутыми.

Кроме того, стоит отметить существенные эксплуатационные недостатки. Миграция объектов в другое хранилище затруднена из-за отсутствия встроенных удобных механизмов. Всё это делает pg_largeobject не самым удобным инструментом для полноценной работы с большими файлами в высоконагруженных системах.

Примечание: помимо этого в PostgreSQL для хранения больших объектов можно применить и различные самописные реализации, но они не гарантируют полной совместимости и корректной работы с СУБД.

Что может предложить Postgres Pro Enterprise для хранения больших объектов

В Postgres Pro Enterprise (начиная с 16-й версии) предусмотрены специализированные механизмы, которые снимают ограничения стандартного PostgreSQL и облегчают миграцию с Oracle. Для хранения данных в СУБД можно использовать три расширения:

  • pgpro_sfile — расширение для хранения больших объектов внутри БД;

  • pgpro_bfile — расширение для работы с файлами, хранящимися на диске;

  • dbms_lob — аналог пакета DBMS_LOB из Oracle, который использует pgpro_sfile и pgpro_bfile.

Теперь о каждом из них подробнее.

Модуль pgpro_sfile

Модуль pgpro_sfile позволяет хранить множество больших объектов (в терминологии расширения они называются sfile) и предоставляет функциональность, аналогичную BLOB в Oracle.

Ключевые особенности:

  • Снятие лимитов. Общий объём хранения и количество объектов ограничены только дисковым пространством и типом bigint (2^63 - 1). Это решает проблему 32 ТБ и 4 млрд объектов.

  • Структура хранения. Данные хранятся в наборе специальных таблиц (в схеме pgpro_sfile_data), управляемых модулем. Каждый объект разбивается на блоки, а блоки — на страницы размером ~8 КБ. В пользовательских таблицах хранятся только идентификаторы объектов sfile.

  • Производительность. Поддерживается параллельная запись и чтение. Например, можно вести запись в один большой объект из нескольких сессий одновременно, так как разные блоки могут записываться в разные секции хранилища параллельно.

  • Управление. Реализованы функции создания (sf_create), чтения (sf_read), записи (sf_write), обрезки (sf_trim) и удаления (sf_delete) объектов.

Подробное описание функций и архитектуры доступно в документации к модулю.

Модуль pgpro_bfile

pgpro_bfile — это расширение Postgres Pro Enterprise, предназначенное для работы с внешними файлами, которые хранятся на диске сервера. Модуль предоставляет возможности для управления ссылками на файлы вне базы данных, аналогичные типу BFILE из Oracle.

Тип bfile содержит два поля:

  • dir_id — ID каталога в таблице bfile_directories.

  • file_name — имя внешнего файла.

Ключевые особенности:

  • Безопасность. Доступ осуществляется через объекты-каталоги. Администратор создает "директорию" в БД (функция bfile_directory_create), привязанную к пути в файловой системе, и выдает права на чтение/запись конкретным пользователям (bfile_grant_directory).

  • Доступ к данным. Модуль предоставляет функции для чтения (bfile_read), записи (bfile_write), копирования и получения свойств файлов.

  • Виртуализация. Доступ к файлам осуществляется через функции виртуальных файлов Postgres, поэтому на них распространяются стандартные ограничения и настройки СУБД.

В целом модуль pgpro_bfile — оптимальное решение для работы с файлами любых размеров на внешнем файловом хранилище, когда сами данные не требуется хранить внутри базы.

Модуль dbms_lob

dbms_lob — это расширение, которое эмулирует интерфейс пакета DBMS_LOB из Oracle. Оно служит «оберткой» над pgpro_sfile и pgpro_bfile и критически важно для проектов миграции, так как позволяет переносить код приложений с минимальными изменениями.

Модуль оперирует следующими типами данных:

  • CLOB — тип для хранения текстовых данных большого объёма (например, длинные документы, XML/JSON-файлы). Является аналогом Oracle CLOB и хранит данные как стандартный тип text.

  • BLOB — тип для хранения бинарных данных (изображения, видео, PDF) в таблицах расширения pgpro_sfile. Является заменой Oracle BLOB и позволяет хранить файлы условно неограниченного размера.

  • BFILE — тип данных для ссылки на файл в файловой системе сервера, предоставляемый расширением pgpro_bfile. Является аналогом BFILE в Oracle.

dbms_lob предоставляет обширный API для взаимодействия с большими объектами, включая функции:

  • создание и преобразование: empty_blob(), empty_clob(), to_blob(), to_clob();

  • открытие и закрытие: open(), close(), isopen();

  • чтение: getlength(), read(), substr(), instr();

  • изменение: write(), writeappend(), erase(), trim();

  • операции с несколькими LOB: compare(), append(), copy().

Таким образом, модуль dbms_lob для Postgres Pro Enterprise не только полностью повторяет функциональность DBMS_LOB в Oracle, но и позволяет хранить данные как внутри базы, так и вне её. Это делает его лучшим решением при миграции приложений с Oracle.

Что в итоге

Хранение файлов в СУБД часто считается неоптимальным паттерном из-за роста нагрузки на БД и усложнения эксплуатации. Однако, если бизнес-требования диктуют такой подход, у пользователей PostgreSQL и Postgres Pro Enterprise есть надёжные инструменты.

Если стандартные механизмы PostgreSQL (TOAST, Large Objects) накладывают ограничения по объёму (32 ТБ) и количеству объектов, то специализированные модули Postgres Pro Enterprise (pgpro_sfile, dbms_lob) позволяют преодолеть эти барьеры, обеспечивая хранение петабайтов данных и предоставляя удобные интерфейсы для разработки и миграции с Oracle.

Комментарии (0)