Store audio files in PostgreSQL BYTEA

by

Posted on by

Audio (MP3, WAV, Ogg, WMA, M4A, AAC, etc.) files can be stored as PostgreSQL BYTEA data.

1. Key Overview

PostgreSQL uses BYTEA (Binary Data) type (instead of native BLOB) for storing mp3/wav/ogg audio files (up to 1 GB max for BYTEA). Storing audio as BYTEA integrates audio data with business records (e.g., podcast clips linked to content IDs) and leverages PostgreSQL’s robust transactional support and open-source flexibility.

2. Prerequisites
  • PostgreSQL (10+ recommended for better BYTEA performance)
  • psql/PGAdmin or DBBlobEditor (simplifies BYTEA import/export)
  • CREATE TABLE, INSERT, SELECT privileges on target database
3. Implementation Steps
3.1 Create Audio Table with BYTEA Column
CREATE TABLE audio_postgresql_bytea (
    audio_id SERIAL PRIMARY KEY,
    audio_name VARCHAR(255) NOT NULL,
    audio_format VARCHAR(10) CHECK (audio_format IN ('mp3', 'wav', 'ogg')),
    file_size BIGINT NOT NULL,
    audio_bytea BYTEA NOT NULL,
    upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.2 Insert Audio
3.2.1 Use psql/PGAdmin (SQL-based) to Insert Audio
-- Insert audio file to BYTEA via psql (using pg_read_binary_file)
INSERT INTO audio_postgresql_bytea (audio_name, audio_format, file_size, audio_bytea)
VALUES (
    'customer_voice_001',
    'mp3',
    200000,
    pg_read_binary_file('/path/to/customer_voice.mp3')
);

Note: Ensure the file path is accessible by PostgreSQL server, and adjust pg_read_binary_file permissions in postgresql.conf if needed.

3.2.2 Use DBBlobEditor (GUI/CLI) to Insert Audio
3.3 Retrieve Audio
3.3.1 Use psql/PGAdmin (SQL-based) to Retrieve Audio
-- Export BYTEA to audio file via psql (using pg_write_binary_file)
SELECT pg_write_binary_file('/path/exported_audio.bin', audio_bytea)
FROM audio_postgresql_bytea 
WHERE audio_id = 1;

Note: Rename the exported .bin file to .mp3/.wav/.ogg (matching stored format) for playback. Ensure pg_write_binary_file has write permissions to the target directory.

3.3.2 Use DBBlobEditor (GUI/CLI) to Retrieve Audio
4. Key Tips
  • BYTEA supports up to 1 GB – use large objects (LO) for audio files over 1 GB (not recommended for regular use).
  • Index metadata (audio_name/audio_format), never index BYTEA columns.
  • Avoid audio files >100MB in PostgreSQL BYTEA (use S3/MinIO for large files).
  • DBBlobEditor CLI is suitable for automated scripts (Bash/Shell) to import/export audio files in PostgreSQL.
  • Use bytea_output = 'hex' (default) for consistent binary data handling in PostgreSQL.
Related Guides