Create postgres tables for store binary files

Беседа № 2:

Вот несколько способов создания таблицы в PostgreSQL для хранения бинарных файлов:

1. Простой метод с использованием bytea:

CREATE TABLE files (

    id SERIAL PRIMARY KEY,

    filename VARCHAR(255) NOT NULL,

    content BYTEA NOT NULL,

    file_size BIGINT NOT NULL,

    mime_type VARCHAR(100),

    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    hash VARCHAR(64)

);2. Расширенная версия с дополнительными метаданными:

CREATE TABLE file_storage (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    filename VARCHAR(255) NOT NULL,

    content BYTEA NOT NULL,

    file_size BIGINT NOT NULL,

    mime_type VARCHAR(100),

    upload_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    last_modified TIMESTAMP WITH TIME ZONE,

    hash VARCHAR(64),

    user_id INTEGER,

    is_public BOOLEAN DEFAULT FALSE,

    metadata JSONB

);

-- Создание индексов

CREATE INDEX idx_file_storage_user_id ON file_storage(user_id);

CREATE INDEX idx_file_storage_mime_type ON file_storage(mime_type);3. Пример с партиционированием для больших объемов:

CREATE TABLE file_storage_partitioned (

    id BIGSERIAL,

    filename VARCHAR(255) NOT NULL,

    content BYTEA NOT NULL,

    file_size BIGINT NOT NULL,

    mime_type VARCHAR(100),

    upload_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

) PARTITION BY RANGE (upload_date);

-- Создание партиций

CREATE TABLE file_storage_y2023m01

    PARTITION OF file_storage_partitioned

    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE file_storage_y2023m02

    PARTITION OF file_storage_partitioned

    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');4. Пример с использованием Large Object:

-- Создание таблицы с Large Object

CREATE TABLE large_files (

    id SERIAL PRIMARY KEY,

    filename VARCHAR(255) NOT NULL,

    file_oid OID,

    file_size BIGINT NOT NULL,

    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);Примеры работы с таблицей:

1. Вставка файла (bytea):

-- Вставка файла

INSERT INTO files (filename, content, file_size, mime_type, hash)

VALUES (

    'example.txt',

    pg_read_binary_file('/path/to/file')::bytea,

    pg_read_binary_file('/path/to/file')::bytea,

    (SELECT length(pg_read_binary_file('/path/to/file'))),

    'text/plain',

    (SELECT md5(pg_read_binary_file('/path/to/file')::bytea))

);2. Извлечение файла:

-- Сохранение файла на диск

COPY (SELECT content FROM files WHERE id = 1)

TO '/path/to/save/file' WITH BINARY;3. Пример с Large Object:

-- Вставка Large Object

BEGIN;

SELECT lo_import('/path/to/file') AS file_oid \gset

INSERT INTO large_files (filename, file_oid, file_size)

VALUES ('example.txt', :file_oid, pg_read_binary_file('/path/to/file')::bytea);

COMMIT;

-- Извлечение Large Object

SELECT lo_export(file_oid, '/path/to/save/file')

FROM large_files

WHERE id = 1;Рекомендации:

1. Для небольших файлов (до 1 МБ) используйте BYTEA

2. Для больших файлов (> 1 МБ) рассмотрите Large Object или хранение на файловой системе

3. Используйте индексы для оптимизации поиска

4. Контролируйте размер базы данных

5.考考虑использование партиционирования для больших объемов

Ограничения:

- PostgreSQL имеет ограничение на размер BYTEA (1 ГБ)

- Large Object позволяет хранить файлы до 4 ТБ

Производительность:

- BYTEA быстрее для малых файлов

- Large Object эффективнее для больших файлов

Пример с Python (psycopg2):

import psycopg2

from psycopg2.extensions import AsIs

def upload_file(filename, file_path):

    conn = psycopg2.connect("dbname=yourdb user=youruser")

    cur = conn.cursor()

    

    with open(file_path, 'rb') as file:

        binary_file = file.read()