二进制数据类型

PostgreSQL 用 bytea 保存任意字节序列，例如加密密文、压缩包、协议帧和小型图片。它与 text 不同：bytea 不按字符集解释内容，也不会因为字节值看起来像乱码而失真。

创建和写入 `bytea`

推荐使用参数化查询，由驱动把内存中的字节数组绑定为 bytea：

CREATE TABLE attachments (
  attachment_id bigint PRIMARY KEY,
  file_name text NOT NULL,
  content_type text NOT NULL,
  payload bytea NOT NULL,
  size_bytes bigint GENERATED ALWAYS AS (octet_length(payload)) STORED
);

SQL 字面量支持十六进制和转义格式。十六进制形式更容易阅读：

SELECT '\x89504e47'::bytea;
SELECT encode(payload, 'hex') FROM attachments;

encode 和 decode 支持 hex、base64、escape 等格式。Base64 是文本传输编码，不应误以为改变了数据库列类型。

`bytea` 与大对象

bytea 是表列，适合需要和行一起事务更新、备份和权限控制的中小型二进制数据。较大的文件会触发 TOAST，数据可能存放在关联的 TOAST 表中，但对应用仍表现为一个列。

PostgreSQL 的大对象（large object，OID 通过 lo_* 函数访问）适合流式读写超大对象或兼容旧应用。它们独立于普通表行，生命周期、备份和权限管理更复杂。新系统应先评估对象存储，再决定使用 bytea 还是大对象。

大小、TOAST 与事务

SELECT octet_length(payload)
FROM attachments
WHERE attachment_id = $1;

octet_length 返回字节数。大值会增加 WAL、备份和复制流量；一次查询返回许多大列也会消耗内存和网络。列表页通常只查询元数据，详情页再按需取 payload。

写入 bytea 与普通列一样参与事务。删除行后，空间回收仍受 MVCC、VACUUM 和长事务影响，不要把“删行”理解为立即归还磁盘。

安全和完整性

二进制列不等于安全存储。密码应使用专门的慢哈希方案，密钥应放在密钥管理系统；bytea 中保存的密文仍需 nonce、认证标签和密钥轮换策略。

可以保存摘要用于去重或完整性检查：

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT digest(payload, 'sha256') FROM attachments;

摘要不是加密。上传文件时还应校验大小、MIME 类型、文件签名和病毒扫描结果；不要仅信任客户端传来的 content_type。

索引和查询

对整段 bytea 建普通索引通常没有价值。若需要按内容去重，可保存固定长度的哈希并建立唯一索引：

ALTER TABLE attachments ADD COLUMN sha256 bytea;
CREATE UNIQUE INDEX attachments_sha256_uq ON attachments (sha256);

bytea 的比较按字节顺序进行，不适合全文搜索、模糊匹配或自然语言排序。应提取可检索的元数据，或使用专门的搜索系统。

常见误区

把二进制直接当 UTF-8 文本转换，遇到无效字节后损坏内容。
在 SQL 中拼接十六进制字符串，忘记 decode 或转义，造成数据错误和注入风险。
列表查询无条件 SELECT *，把大量 TOAST 数据全部传给客户端。
用 bytea 存密码明文或可逆加密密钥。
把大文件全部塞进数据库，却没有评估备份、复制和恢复时间。

创建和写入 bytea​

bytea 与大对象​

大小、TOAST 与事务​

安全和完整性​

索引和查询​

常见误区​