Как хранить файлы в postgresql

В PostgreSQL файлы можно хранить двумя основными способами: в виде больших объектов (BLOBs) или в виде бинарных данных в столбцах таблиц.

1. Хранение файлов как больших объектов (BLOBs):

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

   Пример создания таблицы для хранения BLOBs:

   1

   2

   3

   4

   CREATE TABLE files (
     id SERIAL PRIMARY KEY,
     file_data BYTEA
   );

   Expand code

   Пример добавления BLOB в таблицу:

   1

   INSERT INTO files (file_data) VALUES ('\x66756c6c5f66696c650a', '\x6c6f72656d20697073756d20646f6c6f')

   Expand code

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

2. Хранение файлов как бинарных данных в столбцах таблиц:

   Вместо хранения файлов в виде BLOBs, файлы можно сохранять в столбцы таблицы в формате bytea или других типов данных, таких как text или varchar. Однако, этот способ не рекомендуется для хранения больших файлов, так как это может привести к увеличению размера базы данных и ухудшению производительности.

   Пример создания таблицы для хранения файлов как бинарных данных:

   1

   2

   3

   4

   5

   CREATE TABLE files (
     id SERIAL PRIMARY KEY,
     file_name VARCHAR(255),
     file_data BYTEA
   );

   Expand code

   Пример добавления файла в таблицу:

   1

   INSERT INTO files (file_name, file_data) VALUES ('example.txt', '\x5468697320697320616e206578616d706c6520746578742066696c65')

   Expand code

   Для работы с файлами в виде бинарных данных в столбцах таблицы также можно использовать SQL-запросы и функции.

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

Чаще всего файлы в PostgreSQL не кладут. Файл хранят на диске или в объектном хранилище — S3, Yandex Object Storage, MinIO, — а в таблицу записывают путь и метаданные:

CREATE TABLE attachments (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  file_key text NOT NULL,      -- путь или ключ в хранилище
  file_name text NOT NULL,
  mime_type text,
  size_bytes bigint,
  created_at timestamptz DEFAULT now()
);

Почему так делают:

• база растёт медленнее, резервные копии делаются быстрее;
• файлы отдаёт веб-сервер или CDN, приложение и база не нагружаются;
• хранилище дешевле дискового места базы.

Если файлы нужно хранить в базе

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

Тип bytea

Бинарные данные в обычном столбце:

CREATE TABLE documents (
  id serial PRIMARY KEY,
  name text,
  data bytea
);

Записывают файл из приложения параметром запроса. На Python с psycopg:

with open('contract.pdf', 'rb') as f:
    cur.execute(
        'INSERT INTO documents (name, data) VALUES (%s, %s)',
        ('contract.pdf', f.read()),
    )

Предел одного значения bytea — 1 ГБ, но файлы больше десятков мегабайт так хранить неудобно: запрос целиком читает файл в память.

Large Objects

Механизм больших объектов хранит файлы до 4 ТБ и читает их частями. В таблице лежит только идентификатор объекта типа oid. Минус: при удалении строки объект не удаляется сам, его нужно стирать функцией lo_unlink(), иначе база копит мусор.

Что выбрать

Проектирование таблиц и типы данных разбирают в курсе «SQL для разработчиков».