Store video files in PostgreSQL BYTEA

Shiji Pan

12 months ago

Video files can be stored as PostgreSQL BYTEA data.

1. Core Compatible Type & PostgreSQL BYTEA Fundamentals

PostgreSQL does not have a native BLOB type; it uses the BYTEA (Binary Data) data type as the primary solution for storing large binary files like videos. BYTEA supports unlimited binary data size (constrained only by disk storage), making it fully compatible with all mainstream video formats (AVI, MP4, WMV, MOV, MPEG, FLV, etc.).

Key characteristics of PostgreSQL BYTEA for video storage:

Binary encoding: PostgreSQL supports two BYTEA encoding modes (hex default, escape legacy) – hex encoding is recommended for video files to avoid data corruption.
TOAST mechanism: PostgreSQL’s TOAST (The Oversized-Attribute Storage Technique) automatically compresses and stores large BYTEA data (like videos) out-of-line, optimizing read/write performance.
No size limits: Unlike other databases (MySQL LONGBLOB: 4GB, SQL Server VARBINARY: 2GB), BYTEA has no hard size limits (only limited by available disk space).

2. Key Operational Steps for Video Storage

2.1 Table Creation

Create a dedicated table for video storage with a BYTEA column and auxiliary metadata columns (optimize query efficiency):

Use BYTEA (no subtype selection needed) to support all video file sizes.
Include metadata columns: file_name (VARCHAR), file_format (VARCHAR), file_size (BIGINT), upload_date (TIMESTAMP) etc.

SQL Code for Table Creation:

-- Create video storage table with BYTEA column (unlimited size)
CREATE TABLE video_storage (
    id SERIAL PRIMARY KEY,
    video_bytea BYTEA NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_format VARCHAR(50) NOT NULL,
    file_size BIGINT NOT NULL,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.2 Video File Storage (Write)

SQL Code to Insert Video BYTEA Data:

-- Option 1: Insert via pg_read_binary_file() (server-side file access)
INSERT INTO video_storage (video_bytea, file_name, file_format, file_size)
VALUES (
    pg_read_binary_file('/path/to/your/video.mp4'),
    'sample_video.mp4',
    'MP4',
    pg_stat_file('/path/to/your/video.mp4').size
);

-- Option 2: Parameterized insert (for application integration)
INSERT INTO video_storage (video_bytea, file_name, file_format, file_size)
VALUES ($1, 'sample_video.mp4', 'MP4', 104857600); -- 100MB file size

Use DBBlobEditor (GUI/CLI) to Batch import video files into PostgreSQL BYTEA.

2.3 Video File Retrieval (Read)

SQL Code to Extract BYTEA Data:

-- Select BYTEA data for specific video
SELECT 
    video_bytea,
    file_name,
    file_format
FROM 
    video_storage
WHERE 
    id = 1; -- Target video ID

-- Export BYTEA data to local file (using pg_write_binary_file)
SELECT pg_write_binary_file(
    '/path/to/exported_video.mp4',
    (SELECT video_bytea FROM video_storage WHERE id = 1)
);

-- Alternative: Use pg_dump for batch export
pg_dump -U your_user -d your_database -t video_storage --where="id=1" -f /path/to/export_dump.sql

Use DBBlobEditor (GUI/CLI) to Batch export PostgreSQL BYTEA to VIDEO files.

3. Key Best Practices

TOAST Optimization: Ensure TOAST is enabled (default) for the video_storage table – set STORAGE=EXTERNAL for pre-compressed video formats (MP4) to avoid redundant compression.
File Access Control: Restrict pg_read_binary_file/pg_write_binary_file permissions to trusted users (grant via GRANT EXECUTE ON FUNCTION pg_read_binary_file(text) TO your_user;).
Query Efficiency: Use SELECT file_name, file_format FROM video_storage for listing videos (avoid fetching BYTEA column unless needed).
Backup Strategy: Use pg_dump with --blobs flag to include BYTEA data in backups, or use WAL archiving for point-in-time recovery.

4. Common Issues & Solutions

Issue: pg_read_binary_file() permission denied.
- Solution: Ensure the PostgreSQL server user has read access to the file, and the file path is within data_directory (or set log_directory to allow external paths).
Issue: Slow BYTEA read/write for large videos.
- Solution: Increase shared_buffers and work_mem in postgresql.conf, and use SSD storage for the database data directory.
Issue: BYTEA data corruption during insert/export.
- Solution: Use hex encoding (default in PostgreSQL 9.0+) and validate file size before/after transfer (compare file_size with octet_length(video_bytea)).