Store images in PostgreSQL BYTEA

by

Posted on by

PostgreSQL BYTEA Type Features

  • BYTEA (byte array) is PostgreSQL’s native binary data type (replaces legacy BLOB support)
  • Supports up to 1GB of binary data (limited by PostgreSQL’s page size and TOAST compression)
  • Two storage formats: Hex (default in PostgreSQL 9.0+) and Escape (legacy format)
  • Integrated with TOAST (The Oversized-Attribute Storage Technique) for automatic compression of large BYTEA values
  • Distinct from pg_largeobject (for very large files >1GB, stored as multiple chunks)
  • Binary-safe (no character set conversion, ideal for image/binary file storage)

Step 1: Create a Table with BYTEA Column

Basic Table (BYTEA for Standard Image Storage)

CREATE TABLE image_storage (
    image_id SERIAL PRIMARY KEY, -- Auto-increment integer primary key
    image_name VARCHAR(100) NOT NULL,
    image_bytea BYTEA NOT NULL, -- BYTEA for image binary data
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Optimized Table (With TOAST Compression)

-- Explicitly enable TOAST compression (default enabled, but explicit for clarity)
CREATE TABLE image_storage (
    image_id SERIAL PRIMARY KEY,
    image_name VARCHAR(100) NOT NULL,
    image_bytea BYTEA NOT NULL STORAGE EXTERNAL COMPRESS, -- Optimize for large images
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) WITH (autovacuum_enabled = on);

Step 2: Insert Images into PostgreSQL BYTEA

Method 1: Using psql/PGAdmin (Local File)

-- Prerequisite: Connect to target database (psql command)
\c your_database_name;

-- Method A: Insert using pg_read_binary_file (server-side file)
INSERT INTO image_storage (image_name, image_bytea)
VALUES (
    'product_photo.jpg',
    pg_read_binary_file('/path/to/product_photo.jpg')
);

-- Method B: Insert using \lo_import (large object import, convert to BYTEA)
\lo_import '/path/to/product_photo.jpg' -- Returns a large object OID (e.g., 12345)
INSERT INTO image_storage (image_name, image_bytea)
VALUES ('product_photo.jpg', lo_get(12345));
\lo_unlink 12345; -- Clean up temporary large object
COMMIT;

Note:

1. pg_read_binary_file() requires the file to be on the PostgreSQL server (not client)

2. The PostgreSQL server process must have read permission to the file path

3. For client-side files: Use psql’s \copy or PGAdmin’s import wizard

Method 2: Using DBBlobEditor

Insert Single Image into PostgreSQL BYTEA

  1. Connect to your PostgreSQL database in DBBlobEditor
  2. Navigate to the “image_storage” table in the target schema
  3. Click “+” button to add a record, and then Edit image stored in PostgreSQL BYTEA field

Batch insert Images into PostgreSQL BYTEA

Step 3: Retrieve and Restore PostgreSQL BYTEA to Image

Method 1: Using PostgreSQL Query + Python Script

-- Step 1: Get BYTEA data (PostgreSQL query)
SELECT image_bytea FROM image_storage WHERE image_id = 1;

-- Step 2: Restore to file (Python script)
import psycopg2

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="your_database_name",
    user="postgres",
    password="your_password"
)

cur = conn.cursor()
cur.execute("SELECT image_bytea FROM image_storage WHERE image_id = 1")
bytea_data = cur.fetchone()[0]

# Write to file
with open("exported_photo.jpg", "wb") as f:
    f.write(bytea_data)

cur.close()
conn.close()

Method 2: DBBlobEditor Export

Retrieve and Restore Single Image

  1. Connect to PostgreSQL database in DBBlobEditor
  2. Query the “image_storage” table and locate the target image_bytea column
  3. Double click to View image stored in PostgreSQL BYTEA field
  4. Click menu “File” -> “Save” to save BYTEA data as a local image (JPG/PNG/GIF/etc.) file

Batch Export PostgreSQL BYTEA to Images

PostgreSQL Specific Notes

  • For images >1GB: Use pg_largeobject instead of BYTEA (split large files into chunks)
  • Adjust max_wal_size in postgresql.conf for large BYTEA writes (prevents WAL log overflow)
  • Grant necessary privileges: pg_read_server_files (for pg_read_binary_file), INSERT/SELECT on tables
  • Use HEX format (default) over Escape format for better performance with modern PostgreSQL versions
  • TOAST compression is auto-enabled for BYTEA columns >2KB (reduces storage by 30-70% for images)
  • Avoid indexing BYTEA columns (use image_id/image_name for filtering; use pg_trgm for name search)

Related Guides