PostgreSQL Binary Data: bytea and Large Objects Cheat Sheet

CREATE TABLE app.assets (
  asset_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  media_type text NOT NULL,
  byte_count bigint NOT NULL CHECK (byte_count >= 0),
  sha256 bytea NOT NULL CHECK (octet_length(sha256) = 32),
  body bytea NOT NULL,
  CHECK (octet_length(body) = byte_count)
);

Note: A CHECK that detoasts and measures very large values adds write cost. Benchmark the invariant or validate size and digest in a trusted ingestion service.

WITH sample(bytes) AS (
  VALUES (decode('89504e470d0a1a0a', 'hex'))
)
SELECT encode(bytes, 'hex') AS hex_text,
       encode(bytes, 'base64') AS base64_text,
       octet_length(bytes) AS byte_count,
       decode(encode(bytes, 'base64'), 'base64') = bytes AS round_trip
FROM sample;

Note: Do not cast arbitrary bytea to text. Use convert_from only when bytes are known to be valid in the declared character encoding.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER TABLE app.assets
ADD COLUMN content_sha256 bytea
GENERATED ALWAYS AS (digest(body, 'sha256')) STORED;

CREATE INDEX assets_sha256_idx
ON app.assets (content_sha256);

SELECT asset_id, encode(content_sha256, 'hex') AS sha256
FROM app.assets
WHERE content_sha256 = decode('dffd6021bb2bd32bafce2ea9b9f04865ee0c3e2f2657b283980e24a345bd1dbf', 'hex');

Note: Adding a stored generated column rewrites or computes existing rows and the index build consumes I/O. Stage this migration for large tables.

SELECT asset_id,
       octet_length(body) AS logical_bytes,
       pg_column_size(body) AS datum_bytes,
       pg_size_pretty(pg_column_size(body)::bigint) AS datum_size
FROM app.assets
ORDER BY pg_column_size(body) DESC
LIMIT 20;

SELECT pg_size_pretty(pg_total_relation_size('app.assets')) AS table_with_indexes_and_toast;

Note: Size functions can detoast values and become expensive at scale. Sample bounded rows and use relation-level statistics for routine monitoring.

BEGIN;

WITH created AS (
  SELECT lo_from_bytea(0, decode('89504e470d0a1a0a', 'hex')) AS content_oid
)
INSERT INTO app.large_assets (media_type, content_oid)
SELECT 'image/png', content_oid
FROM created
RETURNING asset_id, content_oid;

COMMIT;

Note: A real streaming client should create, write in chunks, insert the reference, and handle rollback on the same connection and transaction.

BEGIN;

WITH removed AS (
  DELETE FROM app.large_assets
  WHERE asset_id = 42
  RETURNING content_oid
)
SELECT lo_unlink(content_oid)
FROM removed;

COMMIT;

Note: This assumes one reference owns one large object. If sharing is allowed, enforce a reference model and unlink only after a locked zero-reference check.

SELECT lomowner::regrole AS owner, count(*) AS object_count
FROM pg_largeobject_metadata
GROUP BY lomowner
ORDER BY owner::text;

SELECT count(*) AS referenced_objects,
       count(DISTINCT content_oid) AS distinct_references
FROM app.large_assets;

Note: Catalog counts do not prove referential completeness. Compare application references with metadata using a privileged, audited migration role.