bytea und Large Objects

bytea — Binär-Daten als Spaltenwert

CREATE TABLE attachments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename    text NOT NULL,
    content_type text NOT NULL,
    data        bytea NOT NULL,
    size_bytes  integer NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

bytea ist Postgres’ Standard-Typ für Binär-Werte. Größenlimit: theoretisch 1 GB pro Wert, praktisch sollte man weit darunter bleiben.

-- Hex-Notation:
INSERT INTO attachments (filename, content_type, data, size_bytes)
VALUES ('logo.bin', 'application/octet-stream', '\x89504e470d0a1a0a', 8);

-- Aus einer Anwendung würde man normalerweise einen
-- Buffer/Bytes-Wert als Parameter übergeben — keine Hex-Strings.

In Anwendungen hängt’s vom Treiber ab:

import { readFile } from 'fs/promises';
const buffer = await readFile('logo.png');

await client.query(
  `INSERT INTO attachments (filename, content_type, data, size_bytes)
   VALUES ($1, $2, $3, $4)`,
  ['logo.png', 'image/png', buffer, buffer.length]
);

data, _ := os.ReadFile("logo.png")
_, err := conn.Exec(ctx,
    `INSERT INTO attachments (filename, content_type, data, size_bytes)
     VALUES ($1, $2, $3, $4)`,
    "logo.png", "image/png", data, len(data),
)

Lesen ist symmetrisch — der Treiber liefert ein Buffer/[]byte zurück.

TOAST — Postgres’ transparenter Big-Value-Storage

Werte, die größer als ~2 KB sind, lagert Postgres automatisch in eine separate TOAST-Tabelle aus (The Oversized-Attribute Storage Technique). Das ist transparent für die Anwendung — du fragst eine Zeile mit bytea ab und bekommst alles zurück, egal wie groß.

Konsequenz: kleine Inline-Werte sind schnell, große Werte sind langsamer (zusätzliche Indirektion). Komprimiert wird auch automatisch (LZ4 ab PG 14, vorher pglz).

SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'attachments'::regclass
  AND attnum > 0;

Werte für attstorage: p = plain (nie TOAST), e = external (TOAST ohne Kompression), m = main (Kompression bevorzugt), x = extended (Kompression + TOAST). bytea und text sind per Default x.

Large Objects — die Stream-Variante

Für wirklich große Werte (hunderte MB bis GB) hat Postgres ein zweites Mechanismus: Large Objects, mit einer eigenen API (lo_create, lo_open, lo_read, lo_write).

CREATE TABLE big_files (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename  text NOT NULL,
    blob_oid  oid                       -- Verweis auf das Large Object
);

-- Large Object aus Datei laden (Server-seitig — braucht Superuser oder
-- pg_read_server_files)
\lo_import 'huge.zip'
-- Liefert eine OID zurück, z. B. 16384

INSERT INTO big_files (filename, blob_oid) VALUES ('huge.zip', 16384);

Large Objects sind Datenbank-globale Objekte — sie leben nicht in einer Tabelle, sondern werden über oid referenziert. Anwendungen lesen sie über die Treiber-API stream-weise:

import { LargeObjectManager } from 'pg-large-object';

const man = new LargeObjectManager({ pg: client });
const [size, stream] = await man.openAndReadableStreamAsync(16384, 16384);
// stream ist ein normaler Node-Stream — pipe-bar

Streaming ist der Hauptvorteil — ein 5-GB-Blob kann gelesen werden, ohne dass alle 5 GB ins RAM müssen.

bytea vs. Large Objects — Vergleich

Faustregel:

• Werte bis ~10 MB → bytea. Einfacher, ein Standard-SQL.
• Werte über ~100 MB und Streaming nötig → Large Object.
• Werte dazwischen → meistens bytea, außer Streaming ist explizit gewünscht.

Aber wirklich: vorher die nächste Frage stellen.

Sollte das überhaupt in die DB?

Beide Optionen funktionieren, aber große Binär-Daten in der Datenbank haben Nachteile:

• Backups dauern länger. pg_dump einer DB mit 100 GB Bildern dauert lange — und blockiert eventuell.
• Replikation überträgt alles. Streaming-Replicas und logische Replikation kopieren alle Bytes mit.
• TOAST-Tabellen werden riesig und können den Cache-Hit-Rate verschlechtern für „normale” Queries.
• Backup-Versionen werden teuer. Tägliche Snapshots inklusive Bilder = Speicher-Explosion.

Die moderne Best Practice für Web-Apps:

CREATE TABLE attachments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename    text NOT NULL,
    content_type text NOT NULL,
    size_bytes  bigint NOT NULL,
    checksum_sha256 bytea NOT NULL,        -- 32 Bytes — okay in DB
    storage_url text NOT NULL,             -- s3://bucket/path/...
    created_at  timestamptz NOT NULL DEFAULT now()
);

Die echte Datei wandert in S3 / GCS / Azure Blob; die DB hält nur Metadaten und Checksum. Vorteile:

• DB bleibt klein und schnell.
• Object Storage ist günstiger und auf Streaming ausgelegt.
• CDN-Integration trivial.
• Backup der DB schließt nicht TB an Binär-Daten ein.

bytea macht trotzdem Sinn für kleine Werte: Hashes (32 Bytes), kryptographische Keys (256-512 Bytes), kleine Thumbnails (paar KB). Hier ist die Indirektion zu Object Storage Overhead.

Praktische bytea-Anwendungen

CREATE TABLE files (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename        text NOT NULL,
    sha256          bytea NOT NULL UNIQUE,   -- 32 Bytes, ideal für bytea
    storage_url     text NOT NULL
);

-- Lookup nach Hash:
SELECT * FROM files WHERE sha256 = '\x' ||
    encode(digest('test', 'sha256'), 'hex');

Hashes als bytea sind kompakter und schneller in Vergleichen als Hex-Strings (text mit 64 Zeichen). Für UNIQUE-Constraints oder Foreign Keys auf Hashes ist das die richtige Wahl.

CREATE EXTENSION pgcrypto;

CREATE TABLE secrets (
    id           bigserial PRIMARY KEY,
    owner_id     bigint NOT NULL,
    ciphertext   bytea NOT NULL
);

-- Verschluesseln beim Insert:
INSERT INTO secrets (owner_id, ciphertext)
VALUES (1, pgp_sym_encrypt('mein geheimnis', 'master-key'));

-- Entschluesseln beim Read:
SELECT pgp_sym_decrypt(ciphertext, 'master-key') FROM secrets WHERE id = 1;

pgcrypto (Built-in-Extension) bietet symmetrische und asymmetrische Verschlüsselung — Werte landen als bytea in der Tabelle.

Weiterführende Ressourcen

Externe Quellen

• Binary Data Types: bytea
• Large Objects
• TOAST – PostgreSQL Documentation
• pgcrypto Extension
• Binary String Functions

Verwandte Artikel

• Übersicht der Datentypen
• text, varchar und char
• Numerische Typen