PHP小码农

Appearance

BYTEA vs 大对象 ​

存二进制数据到 PostgreSQL，只有两条路：bytea 字段，或者大对象（Large Object）。前两节把大对象讲透了，这一节做选型：什么时候用 bytea，什么时候必须上大对象，两者的边界在哪里。

先说结论：绝大多数场景应该用 bytea。大对象只在两个条件下才有明显优势——单个对象超过 1 GB，或者需要高频随机读写对象内部的一小段。除此之外，大对象带来的复杂度（事务约束、孤儿对象、不支持逻辑复制、备份注意事项）都是净负担。

本节围绕一张对比表展开，把各个维度的差异拆开，然后给出决策清单、两种格式的互转 SQL、以及真实的 PHP / Python 上传下载示例。

前置知识

建议先阅读：lo_create / lo_import / lo_export 与大对象操作

基础概念 ​

两条路的本质差异 ​

bytea 是一个普通的数据类型，它的值就是行的值本身，跟着行一起存储、一起事务、一起删除。

大对象是一套独立的存储机制，它的内容存在系统表里，业务表只保存一个 oid 引用。

text
── bytea ──────────────────────────────

  表 photos
  ┌────┬──────────┬───────────────────────┐
  │ id │  title   │       raster          │
  ├────┼──────────┼───────────────────────┤
  │  1 │ a.png    │ \x89504e47...(内联或TOAST) │
  └────┴──────────┴───────────────────────┘
                       ↑ 数据在这里

── 大对象 ─────────────────────────────

  表 photos
  ┌────┬──────────┬───────┐
  │ id │  title   │ raster│        ← 只存一个 OID
  ├────┼──────────┼───────┤
  │  1 │ a.png    │ 16385 │
  └────┴──────────┴───────┘
                    │
                    │ 引用
                    ▼
  系统表 pg_largeobject
  ┌───────┬────────┬──────────────┐
  │ loid  │ pageno │     data     │  ← 数据在这里，分页存放
  ├───────┼────────┼──────────────┤
  │ 16385 │   0    │ \x89504e47.. │
  │ 16385 │   1    │ ...          │
  └───────┴────────┴──────────────┘

这个结构差异，直接决定了两者在下面所有维度上的表现。

上限：1 GB vs 4 TB ​

最直观的差别是容量上限。

类型单值上限依据
bytea（以及所有 varlena 类型）1 GBvarlena 长度字段为 32 位有符号，最大值 2³⁰−1 字节
大对象约 4 TB分页存储 + 64 位偏移函数

bytea 的 1 GB 上限不是"PostgreSQL 配置的"，而是类型本身的编码限制——所有变长类型（text、jsonb、bytea……）共享同一个 32 位头部，其中一位用作标志位，剩余 31 位表示长度，所以理论上限是 2³¹−1 字节，但实际可用上限被限制在 2³⁰−1 ≈ 1 GB。

大对象没有这个限制，因为它的长度不是"存在值头部"，而是"由最大页号决定"，配合 lo_lseek64 / lo_tell64 / lo_truncate64 的 64 位偏移，可寻址空间达到 TB 级。

这条是选型的第一个分水岭：单个对象超过 1 GB，bytea 直接出局。

TOAST：bytea 的"幕后分片" ​

bytea 看起来是"整值存储"，但那只是逻辑视角。物理上，当值超过了单页能容纳的大小，PostgreSQL 会启用 TOAST（The Oversized-Attribute Storage Technique） 把它搬到独立的 TOAST 表里。

理解 TOAST 的几个常量很重要：

常量默认值含义
BLCKSZ8 kB数据块大小
TOAST_TUPLE_THRESHOLD2 kB行大小超过它就触发 TOAST
TOAST_TUPLE_TARGET2 kB压缩后希望压缩到的目标大小
TOAST_MAX_CHUNK_SIZE约 2000 字节每个 TOAST chunk 的最大尺寸

触发条件：当一行的大小超过 TOAST_TUPLE_THRESHOLD（约 2 kB）时，PostgreSQL 会尝试压缩该行中的可 TOAST 字段；如果压缩后仍然超限，就把这些字段的值切成分片（chunk）搬进关联的 TOAST 表，主表行里只留一个指针。

sql
-- 查看某张表的 TOAST 表及其大小（photos 的 TOAST 表约 12 MB）
SELECT relname,
       pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast_size
FROM pg_class
WHERE relname = 'photos' AND reltoastrelid <> 0;

bytea 列的存储策略默认为 EXTENDED（允许压缩 + 允许外存），这意味着：

  • 数据会被尝试压缩（bytea 内容如果是文本型数据，压缩率可能很高）；
  • 压缩后仍然超限时搬到 TOAST 表。
sql
-- 查看列的存储策略：id 为 p，title / raster 均为 x
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'photos'::regclass AND attnum > 0;

attstorage 的取值含义：

值策略含义
pPLAIN不允许压缩，不允许外存（定长类型）
eEXTENDED允许压缩，允许外存（默认，最灵活）
xEXTERNAL允许外存，但不压缩
mMAIN优先压缩，实在放不下才外存

关键点来了：bytea 通过 TOAST 也能实现"分片存储"，但它和你想要的完全不同。

TOAST 的分片是透明的、不可寻址的——你不能对 TOAST 的某个 chunk 做定点读写。每次访问 bytea 值，PostgreSQL 都需要把整个值（或者至少是访问所需的部分）解压、重组。所以：

  • TOAST 让 bytea 突破了单页限制，但没有突破 1 GB 的总上限；
  • TOAST 的读取仍然是"整值语义"，不支持按偏移随机访问；
  • 对 bytea 做 substring 会先把值拉出来（可能触发 TOAST 解压和 detoast），代价随对象大小增长。

这正是大对象存在的根本原因：大对象的"页"是显式的、可寻址的；TOAST 的"chunk"是隐式的、不可寻址的。

sql
CREATE TABLE t_bytea (
    id  serial PRIMARY KEY,
    raw bytea
);

-- bytea 列默认是 EXTENDED
ALTER TABLE t_bytea ALTER COLUMN raw SET STORAGE EXTERNAL;

如果二进制内容已经被压缩过（例如 JPEG、ZIP、MP4），压缩是白费 CPU，可以显式改成 EXTERNAL 跳过压缩尝试。

sql
-- 观察 TOAST 带来的存储开销（stored_bytes < logical_bytes 说明发生了压缩）
SELECT id,
       octet_length(raw)                              AS logical_bytes,
       pg_column_size(raw)                            AS stored_bytes
FROM t_bytea;

stored_bytes < logical_bytes 说明发生了压缩；如果两者接近而 stored_bytes 略大，说明只做了 TOAST 外存没有压缩收益。

语法与用法 ​

bytea 的两种输入输出格式 ​

bytea 有两种文本表示格式，由参数 bytea_output 决定输出格式（默认 hex）：

格式外观说明
hex\x48656c6c6f十六进制，自 PostgreSQL 9.0 起为默认
escapeHello / \\001八进制转义，老格式

两种格式在输入时都被接受，但只在必要时使用 escape：

sql
-- hex 输入（推荐），输出 \x48656c6c6f
SELECT '\x48656c6c6f'::bytea;

-- escape 输入（老式，仅在需要时用）
SELECT E'\\101\\102\\103'::bytea;

escape 格式的输出规则比较绕（可打印字符原样输出，反斜杠本身要写成 \\，其他字节写成 \ooo 八进制），容易出错。建议始终使用 hex 格式。

decode() 与 encode() ​

从字符串生成 bytea、把 bytea 转回字符串，用 decode() / encode()：

sql
-- 字符串 → bytea
SELECT decode('48656c6c6f', 'hex');
SELECT decode('SGVsbG8=', 'base64');
SELECT decode('Hello', 'escape');
sql
-- bytea → 字符串（输出如 48656c6c6f）
SELECT encode('\x48656c6c6f'::bytea, 'hex');
SELECT encode('\x48656c6c6f'::bytea, 'base64');
SELECT encode('\x48656c6c6f'::bytea, 'escape');

支持的格式：base64、escape、hex。注意 decode() 的输出总是 bytea，而 encode() 的输出总是 text——这是两个函数唯一的方向性区别，但方向容易记反（decode 是"把文本解码成二进制"，encode 是"把二进制编码成文本"）。

与 bytea 相关的常用函数 ​

函数签名说明
octet_length()octet_length(bytea) → integer字节数（不触发 detoast 的估算，实际会返回精确长度）
length()length(bytea) → integer对 bytea 等价于 octet_length
substring()substring(bytea from int for int) → bytea取子串
get_byte()get_byte(bytea, int) → integer取某个字节的值（0~255）
set_byte()set_byte(bytea, int, int) → bytea设置某个字节，返回新值
get_bit()get_bit(bytea, int) → integer取某一位
set_bit()set_bit(bytea, int, int) → bytea设置某一位，返回新值
md5()md5(bytea) → textMD5 摘要
sha256()sha256(bytea) → byteaSHA-256 摘要
convert_to()convert_to(text, encoding) → bytea文本按指定编码转字节
convert_from()convert_from(bytea, encoding) → text字节按指定编码转文本

需要特别注意 set_byte() 和 set_bit() 的语义：它们不是原地修改，而是返回一个新的 bytea。要真正生效，必须把返回值写回列：

sql
-- 错误：丢弃了返回值
SELECT set_byte(raw, 0, 255) FROM t_bytea WHERE id = 1;

-- 正确：写回
UPDATE t_bytea SET raw = set_byte(raw, 0, 255) WHERE id = 1;

这正是 bytea 做局部修改昂贵的体现：哪怕只改 1 个字节，也要"读出整值 → 生成新值 → 写回整值"，对于 500 MB 的 bytea，这个操作代价极高。而大对象的 lo_put 只改一页。

互转：bytea ↔ 大对象 ​

PostgreSQL 提供了两条方向的转换：

sql
-- bytea → 大对象；大对象 → bytea（返回 \xdeadbeef）
SELECT lo_from_bytea(0, '\xdeadbeef') AS loid;
SELECT lo_get(16389);

迁移 bytea 到 LO：

sql
-- 假设已有 t_bytea(id, raw)，要迁移到 LO 形式
CREATE TABLE t_lo (
    id   integer PRIMARY KEY,
    loid oid
);

INSERT INTO t_lo (id, loid)
SELECT id, lo_from_bytea(0, raw)
FROM t_bytea;

迁移 LO 回 bytea（只在对象小于 1 GB 时可行）：

sql
CREATE TABLE t_bytea (
    id   integer PRIMARY KEY,
    raw  bytea
);

INSERT INTO t_bytea (id, raw)
SELECT id, lo_get(loid)
FROM t_lo;

如果某个大对象超过 1 GB，这一步会失败（ERROR: invalid memory alloc request size ...）。

大对象 → bytea 的方向有损（受 1 GB 上限约束），bytea → 大对象的方向是无损的。迁移前务必先统计最大对象尺寸：

sql
SELECT max((max(pageno) + 1) * 2048) AS max_logical_bytes
FROM pg_largeobject
GROUP BY loid
ORDER BY max_logical_bytes DESC
LIMIT 1;

备份与恢复的差异 ​

工具 / 选项对大对象的处理
pg_dump（默认）包含大对象
pg_dump -b / --large-objects显式包含（--blobs 为已废弃别名）
pg_dump -B / --no-large-objects显式排除（--no-blobs 为已废弃别名）
pg_dump -n schema不导出大对象
pg_dump --data-only包含大对象（大对象被视为数据）
pg_dump --schema-only不包含大对象

pg_dump 把大对象视为数据（data）而非 schema 对象，因此：

  • --data-only 时导出；
  • --schema-only 时不导出；
  • 但只要指定了 -n（限定 schema），大对象就不会被导出——因为大对象不属于任何 schema。

而 bytea 是普通列，跟着表走，-n 限定 schema 时会正常导出。

bash
# 只导数据，包含大对象
pg_dump --data-only mydb > data.sql

# 限定 schema —— 大对象会丢失！
pg_dump -n public mydb > public_only.sql

这是 bytea 在备份维度上的一个实在优势：它天然跟着表的 schema 走，不会因为备份范围参数而"神秘消失"。 如果必须用大对象，备份脚本要特别留意不要用 -n。

逻辑复制的差异 ​

这是最硬的一条差异。官方文档在"逻辑复制的限制"中明确写道：

Large objects are not replicated. There is no workaround for that, other than storing data in normal tables.

翻译过来：大对象不被复制，且没有变通方案，唯一办法是把数据存进普通表。

text
逻辑复制（publication / subscription）
  ├── 表数据             ✅ 复制
  ├── 序列（PG 10+）      ✅ 复制
  ├── 大对象             ❌ 不复制，无变通方案
  └── bytea 列           ✅ 随表一起复制

这意味着：如果你的架构需要逻辑复制、逻辑订阅、或者基于逻辑解码的 CDC，那么大对象根本不可用。 这是选型时的一票否决项，必须先确认。

需要注意的是，这与流复制（物理复制）不同：物理复制基于 WAL，大对象的所有变更都会随 WAL 复制到备库。所以主备高可用场景下大对象没问题，只有逻辑复制不行。

详细说明 ​

全面对比表 ​

把各维度集中到一张表：

维度bytea大对象（LO）
单值上限1 GB（2³⁰−1）约 4 TB
存储介质表堆文件 / TOAST 表系统表 pg_largeobject
寻址粒度整值（TOAST 不可寻址）2 kB 逻辑页，按偏移可寻址
流式读写不支持，整值进出支持，loread / lowrite 分块
随机访问差（需 detoast 整值）好（索引定位到页）
局部修改代价高（整值重写）低（只改涉及的页）
事务开销低（随行事务）中（描述符 + 额外系统表操作）
生命周期随行自动删除独立，需 lo_unlink / vacuumlo
权限粒度列级 GRANT对象级 GRANT ON LARGE OBJECT
备份随表导出，受 -n 保护视为数据，-n 时丢失
流复制支持支持（走 WAL）
逻辑复制支持不支持，无变通方案
客户端支持所有语言原生支持需专门 API（libpq / JDBC / PHP pgsql）
使用复杂度低高
内存占用整值载入内存可按分块控制

内存与并发表现 ​

bytea 的读写是整值语义，这意味着一次 SELECT raw FROM t WHERE id = 1 会把整个值载入内存（服务端 detoast + 客户端接收）。当值达到几百 MB 时：

  • 服务端需要临时分配同等大小的内存；
  • work_mem 相关的排序、哈希操作会受影响（大值可能溢出到磁盘）；
  • 客户端需要同样大的缓冲区；
  • 网络传输是一次性的、不可中断的大块。

大对象则天然分块：

sql
BEGIN;
SELECT lo_open(16391, 262144) AS fd \gset

-- 每次读 64 kB，内存峰值恒定
SELECT loread(:fd, 65536);

SELECT lo_close(:fd);
COMMIT;

无论是 10 MB 还是 100 GB 的对象，内存峰值都只和单次读取的块大小有关。 这是大对象在高并发、大对象场景下的结构性优势。

权限粒度 ​

bytea 走的是列级权限：

sql
-- 整列授权
GRANT SELECT (raw) ON t_bytea TO reader;
GRANT UPDATE (raw) ON t_bytea TO writer;

大对象走的是对象级权限：

sql
-- 针对具体的大对象授权
GRANT SELECT ON LARGE OBJECT 16391 TO reader;
GRANT UPDATE ON LARGE OBJECT 16391 TO writer;

两者各有优劣：

  • bytea：粒度是列，一行内的所有值共享同一套权限。适合"整列都是同一类数据"。
  • 大对象：粒度是单个对象，可以只授权某一个文件。适合"文件是独立的资源，权限按文件走"。

但大对象的对象级授权意味着每新增一个对象就要授权一次（或依赖属主身份），在高频写入场景下会成为负担。

客户端支持 ​

bytea 在所有语言里都是一等公民：

语言bytea大对象
PHP（pgsql）直接以字符串传递pg_lo_* 系列函数
PHP（PDO_PGSQL）以字符串/Lob 传递无专用 API，需 SQL 层函数
Python（psycopg2）memoryview / bytesconnection.lobject() 工厂
Java（JDBC）byte[] 或 setBinaryStream()LargeObjectManager
Go（lib/pq / pgx）[]byte无专用 API，需 SQL 层函数

PDO_PGSQL 没有大对象的专用 API——这是很多 PHP 项目的实际约束。在 PDO 下要做大对象，只能用 SQL 层的 lo_create / lo_unlink，读写则无法流式（PDO 的 PDO::PARAM_LOB 配合 bytea 才是 PDO 的推荐路径）。

决策清单 ​

按下面的顺序逐条判断：

text
1. 单个对象是否超过 1 GB？
   ├── 是 → 必须用大对象
   └── 否 → 继续

2. 是否需要逻辑复制 / 逻辑订阅 / CDC？
   ├── 是 → 必须用 bytea（大对象不支持）
   └── 否 → 继续

3. 是否需要频繁读写对象内部的局部片段？
   ├── 是 → 大对象更合适
   └── 否 → 继续

4. 是否需要流式处理以避免内存峰值？
   ├── 是 → 大对象更合适
   └── 否 → 继续

5. 其他所有情况 → 用 bytea

再补一条经验法则：如果对象的典型大小在几百 KB 到几十 MB，且是"整体写入、整体读取"的模式（图片、缩略图、配置文件、小文档），bytea 几乎总是更好的选择。

性能对比的实测思路 ​

要为自己的场景选型，最可靠的方式是实测。下面给出一个可复现的思路。

准备测试数据：

sql
CREATE TABLE bench_bytea (id integer PRIMARY KEY, raw  bytea);
CREATE TABLE bench_lo    (id integer PRIMARY KEY, loid oid);

-- 各插入 1000 个 1 MB 的对象
INSERT INTO bench_bytea (id, raw)
SELECT g, decode(repeat('ab', 524288), 'hex')   -- 1 MB
FROM generate_series(1, 1000) g;

INSERT INTO bench_lo (id, loid)
SELECT g, lo_from_bytea(0, decode(repeat('ab', 524288), 'hex'))
FROM generate_series(1, 1000) g;

测整值读取：

sql
-- bytea：整值
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(octet_length(raw)) FROM bench_bytea;

-- 大对象：只读 1 kB
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(octet_length(lo_get(loid, 0, 1024))) FROM bench_lo;

测局部访问：

sql
-- bytea 取中间 1 kB（会触发 detoast）
EXPLAIN (ANALYZE, BUFFERS)
SELECT substring(raw from 500000 for 1024) FROM bench_bytea WHERE id = 1;

-- 大对象取中间 1 kB（索引定位到页）
EXPLAIN (ANALYZE, BUFFERS)
SELECT lo_get(loid, 500000, 1024) FROM bench_lo WHERE id = 1;

观察的指标：

指标观察方式
执行时间EXPLAIN (ANALYZE) 的 Execution Time
缓冲区访问EXPLAIN (ANALYZE, BUFFERS) 的 shared hit/read
表物理大小pg_total_relation_size()
内存峰值客户端监控 / 服务端 pg_stat_activity

经验性的预期：

  • 整值读写小对象（< 10 MB）：bytea 通常更快，因为它是一次行访问，而大对象需要 lo_open + loread + lo_close 三次往返。
  • 局部读取大对象（> 100 MB）：大对象明显更快，因为它不需要 detoast 整值。
  • 存储空间：bytea 会尝试压缩，若数据可压缩则可能占用更少空间；大对象不压缩，但稀疏存储可能省空间。

迁移方案 ​

bytea → 大对象：

sql
BEGIN;

CREATE TABLE files_new (id integer PRIMARY KEY, name text, loid oid);

INSERT INTO files_new (id, name, loid)
SELECT id, name, lo_from_bytea(0, raw)
FROM files_old;

-- 校验数量一致
SELECT (SELECT count(*) FROM files_new) AS new_cnt,
       (SELECT count(*) FROM files_old) AS old_cnt;

COMMIT;

大对象 → bytea：

sql
BEGIN;

-- 先检查是否有超过 1 GB 的对象，预期返回 0
SELECT count(*) AS too_big
FROM (
    SELECT loid, (max(pageno) + 1) * 2048 AS logical_bytes
    FROM pg_largeobject
    GROUP BY loid
) t
WHERE logical_bytes > 1073741824;

确认没有超限对象后再转换：

sql
CREATE TABLE files_back (id integer PRIMARY KEY, name text, raw bytea);

INSERT INTO files_back (id, name, raw)
SELECT id, name, lo_get(loid)
FROM files_new_corrected
WHERE loid IS NOT NULL;

COMMIT;

混合方案：小对象用 bytea，大对象用 LO

如果数据尺寸分布很宽，可以按阈值分流：

sql
CREATE TABLE files (
    id        integer PRIMARY KEY,
    name      text,
    is_lo     boolean NOT NULL,
    raw       bytea,      -- is_lo = false 时使用
    loid      oid,        -- is_lo = true  时使用
    CONSTRAINT chk_storage CHECK (
        (is_lo AND loid IS NOT NULL AND raw IS NULL) OR
        (NOT is_lo AND raw IS NOT NULL AND loid IS NULL)
    )
);

这个设计用一个 CHECK 约束保证"两种存储互斥"，代价是查询时需要分支处理。除非数据确实双峰分布，否则不建议引入这种复杂度。

实战示例 ​

PHP：用 bytea 上传与下载 ​

php
<?php
$conn = pg_connect("host=localhost dbname=mydb user=app");

// 建表
pg_query($conn, "CREATE TABLE IF NOT EXISTS uploads (
    id      serial PRIMARY KEY,
    name    text NOT NULL,
    raw     bytea NOT NULL
)");

// 上传：把客户端本地文件读成字节串，用 bytea 参数传入
$data = file_get_contents('/tmp/upload.png');
if ($data === false) {
    throw new RuntimeException('read local file failed');
}

$res = pg_query_params(
    $conn,
    "INSERT INTO uploads (name, raw) VALUES ($1, $2)",
    ['upload.png', $data]
);
if ($res === false) {
    throw new RuntimeException(pg_last_error($conn));
}

echo "inserted: " . pg_affected_rows($res) . "\n";

// 下载：把 bytea 取出来写到客户端文件
$res = pg_query_params($conn, "SELECT raw FROM uploads WHERE id = $1", [1]);
$row = pg_fetch_assoc($res);
file_put_contents('/tmp/download.png', pg_unescape_bytea($row['raw']));

echo "downloaded\n";
?>

注意 pg_unescape_bytea()：PHP 的 pgsql 扩展在某些配置下会把 bytea 的值以 escape 格式返回，用这个函数可以还原成原始字节。

对于较大的 bytea，file_get_contents 会一次性把整个文件读进内存；如果文件可能比较大，应改用流式读取的行协议，或者改用大对象路径：

php
<?php
$conn = pg_connect("host=localhost dbname=mydb user=app");

pg_query($conn, "BEGIN");

$oid = pg_lo_create($conn);
$lob = pg_lo_open($conn, $oid, "w");

// 分块写入，避免整文件载入内存
$fh = fopen('/tmp/big.bin', 'rb');
while (!feof($fh)) {
    $chunk = fread($fh, 1024 * 1024);
    if ($chunk === false || $chunk === '') {
        break;
    }
    pg_lo_write($lob, $chunk);
}
fclose($fh);

pg_lo_close($lob);
pg_query($conn, "COMMIT");

// 关联到业务表
pg_query_params($conn, "INSERT INTO uploads_lo (name, loid) VALUES ($1, $2)",
                ['big.bin', $oid]);
?>

Python（psycopg2）：用 bytea 上传与下载 ​

python
import psycopg2

conn = psycopg2.connect("dbname=mydb user=app host=localhost")
cur = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS uploads (
        id   serial PRIMARY KEY,
        name text NOT NULL,
        raw  bytea NOT NULL
    )
""")

# 上传：psycopg2 会自动把 bytes 适配为 bytea
with open('/tmp/upload.png', 'rb') as f:
    data = f.read()

cur.execute(
    "INSERT INTO uploads (name, raw) VALUES (%s, %s)",
    ('upload.png', psycopg2.Binary(data))
)
conn.commit()
print("inserted id:", cur.lastrowid if hasattr(cur, 'lastrowid') else 'n/a')

# 下载
cur.execute("SELECT raw FROM uploads ORDER BY id DESC LIMIT 1")
raw = cur.fetchone()[0]          # 返回 memoryview
with open('/tmp/download.png', 'wb') as f:
    f.write(bytes(raw))

conn.close()

psycopg2 从 bytea 读取时返回 memoryview，写入 bytes 时用 psycopg2.Binary() 适配（也可以直接传 bytes）。

Python（psycopg2）：用大对象流式处理 ​

python
import psycopg2

conn = psycopg2.connect("dbname=mydb user=app host=localhost")
conn.autocommit = False          # 大对象操作必须在事务内
cur = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS uploads_lo (
        id   serial PRIMARY KEY,
        name text NOT NULL,
        loid oid NOT NULL
    )
""")

# 创建并写入大对象（流式）
lobj = conn.lobject(0, 'wb')     # oid=0 表示新建
with open('/tmp/big.bin', 'rb') as f:
    while True:
        chunk = f.read(1024 * 1024)
        if not chunk:
            break
        lobj.write(chunk)
oid = lobj.oid
lobj.close()

cur.execute("INSERT INTO uploads_lo (name, loid) VALUES (%s, %s)",
            ('big.bin', oid))
conn.commit()

# 读取大对象（流式）
cur.execute("SELECT loid FROM uploads_lo ORDER BY id DESC LIMIT 1")
loid = cur.fetchone()[0]

lobj = conn.lobject(loid, 'rb')
with open('/tmp/big_out.bin', 'wb') as f:
    while True:
        chunk = lobj.read(1024 * 1024)
        if not chunk:
            break
        f.write(chunk)
lobj.close()

conn.commit()
conn.close()

psycopg2 的 lobject() 是 connection 上的工厂方法，返回一个 lobject 实例；mode 用 'rb' / 'wb' / 'rwb' 这样的字符串表示。它还提供了 lo_import() / lo_export()（操作服务器文件系统）。

JDBC：bytea 二进制流 ​

java
PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO uploads (name, raw) VALUES (?, ?)");
ps.setString(1, "upload.png");

InputStream in = new FileInputStream("/tmp/upload.png");
ps.setBinaryStream(2, in);
ps.executeUpdate();
in.close();

JDBC 用 setBinaryStream 时，驱动可能会把整个流缓冲到内存再发送，也可能流式发送——具体行为取决于驱动版本和 bytea 值的大小。对于超大文件，JDBC 的 LargeObjectManager 路径更可控。

用 lo_get 做局部读取以节省流量 ​

如果只关心业务行关联的大对象的"前 16 字节"，不要用 SELECT lo_get(loid)，而应指定长度：

sql
-- 差：读取整个对象
SELECT id, lo_get(loid) FROM files WHERE id = 1;

-- 好：只读文件头
SELECT id, lo_get(loid, 0, 16) AS header FROM files WHERE id = 1;
text
 id |             header
----+----------------------------------
  1 | \x89504e470d0a1a0a0000000d49484452
(1 row)

89 50 4E 47 是 PNG 的魔数，说明这是一个 PNG 文件。

用 md5 做完整性校验 ​

无论选哪种存储，都建议存一个校验和：

sql
-- bytea 场景
SELECT md5(raw) AS checksum FROM t_bytea WHERE id = 1;

-- 大对象场景
SELECT md5(lo_get(loid)) AS checksum FROM t_lo WHERE id = 1;
text
              checksum
----------------------------------
 d41d8cd98f00b204e9800998ecf8427e
(1 row)

大对象场景下 md5(lo_get(...)) 需要把整个对象读出来，对超大对象代价高。更好的做法是在应用层流式计算摘要，或者把校验和单独存在业务表的列里：

sql
ALTER TABLE files ADD COLUMN checksum text;

注意事项 ​

逻辑复制不支持大对象，这是硬限制

官方原文："Large objects are not replicated. There is no workaround for that, other than storing data in normal tables." 需要逻辑复制的架构里不要用大对象。

bytea 的 1 GB 上限是类型级限制

不是参数，改配置也没用。超过就必须用大对象。

TOAST 不等于大对象

TOAST 让 bytea 能超过单页大小，但它是透明分片、不可寻址、不突破 1 GB。不要把 TOAST 当成"内置的大对象"。

pg_dump -n 会丢掉大对象

大对象不属于任何 schema，指定 -n 时不会导出。备份脚本要特别留意这一点。

decode() 和 encode() 的方向容易记反

decode(text, format) → bytea；encode(bytea, format) → text。

set_byte() / set_bit() 返回新值，不原地修改

必须把返回值写回列，否则修改丢失。

bytea_output = escape 的兼容性问题

老客户端依赖 escape 格式时，改成 hex 可能导致解析失败。升级前确认客户端兼容性。

不要对已压缩的数据启用压缩

JPEG、ZIP、MP4 等格式压缩率极低，白白消耗 CPU。可以把列改为 STORAGE EXTERNAL。

从大对象转回 bytea 前必须检查尺寸

超过 1 GB 的对象转换会报 invalid memory alloc request size。

lo_get 不带长度会读整个对象

即使对象有 4 TB，加不加 length 决定了内存是否爆掉。

PDO_PGSQL 没有大对象专用 API

用 PDO 的项目要读大对象，只能走 SQL 层函数，且拿不到流式接口。这通常是选 bytea 的理由。

PDO 的 bytea 也要注意转义

PDO 的参数绑定与 pg_escape_bytea 风格不同，混用容易产生损坏的数据。统一走参数绑定。

最佳实践 ​

  1. 默认选 bytea。它更简单、支持逻辑复制、随行事务、所有语言原生支持。只有当 1 GB 上限或随机访问需求成为真实瓶颈时才换大对象。

  2. 先量化对象尺寸分布，再决定选型：

    sql
    SELECT count(*)                                          AS total,
       count(*) FILTER (WHERE octet_length(raw) > 10485760) AS over_10mb,
       pg_size_pretty(max(octet_length(raw)))            AS max_size
FROM uploads;

  3. 有逻辑复制需求时，一律用 bytea。这条优先级最高，因为大对象在逻辑复制下无解。

  4. 二进制内容不要走文本编码往返。用参数绑定传 bytes，不要先 base64 再解码，既浪费空间又容易出错。

  5. bytea 单值尽量控制在几十 MB 以内。超过后 detoast 的代价会明显体现，考虑改用大对象。

  6. 对已压缩格式关闭 TOAST 压缩：

    sql
    ALTER TABLE uploads ALTER COLUMN raw SET STORAGE EXTERNAL;

  7. 为二进制列单独建表，与业务热数据分离。避免每次查询业务行都因为行太大而拉慢：

    sql
    CREATE TABLE upload_meta (id serial PRIMARY KEY, name text, size_bytes bigint);
CREATE TABLE upload_blob (id integer PRIMARY KEY REFERENCES upload_meta(id), raw bytea);

  8. 用 pg_total_relation_size() 监控 TOAST 占用，把它纳入容量规划：

    sql
    SELECT relname,
       pg_size_pretty(pg_relation_size(oid))            AS main,
       pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast
FROM pg_class
WHERE relname = 'uploads';

  9. 选定存储后存一个校验和，独立成列，便于校验与去重（可配合 sha256）。

  10. 迁移前先在测试环境演练，尤其要验证：尺寸是否超限、备份是否完整、恢复后能否正常读取。

  11. 备份脚本要显式验证大对象被包含（若使用大对象）。导入一个备份到临时库，比对 pg_largeobject_metadata 的行数是可靠做法：

    sql
    SELECT count(*) FROM pg_largeobject_metadata;

  12. 混合存储方案仅在数据双峰分布时才使用，并用 CHECK 约束保证两种存储互斥，避免出现"两边都写"或"两边都空"的脏数据。

下一节

继续学习：行级安全（Row-Level Security）

参考链接 ​