2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG213-PG大对象存储：BLOB/CLOB类型与TOAST机制

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：17

本文档风哥主要介绍PostgreSQL数据库的大对象存储，包括BLOB/CLOB类型、TOAST机制等内容，风哥教程参考PostgreSQL官方文档Large Objects内容，适合数据库管理员和开发者在学习和测试中使用。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 PostgreSQL大对象概念

大对象是指存储在数据库中的大型数据，如图片、视频、文档等。PostgreSQL支持两种大对象存储方式：

  • TOAST：用于存储超过页大小（8KB）的字段
  • Large Objects：用于存储非常大的对象（GB级）
PostgreSQL大对象的特点：

  • 支持存储大型二进制数据
  • 支持存储大型文本数据
  • TOAST机制自动处理大字段
  • Large Objects适合非常大的对象
  • 支持流操作

1.2 PostgreSQL BLOB/CLOB类型

PostgreSQL中的大对象类型：

  • BYTEA：存储二进制数据，适合较小的二进制数据
  • TEXT：存储文本数据，适合较大的文本数据
  • JSONB：存储JSON数据，适合半结构化数据
  • Large Objects：使用oid类型引用，适合非常大的对象

1.3 PostgreSQL TOAST机制

TOAST（The Oversized-Attribute Storage Technique）是PostgreSQL用于存储大字段的机制，其工作原理：

  • 压缩：对大字段进行压缩
  • 外存：将大字段存储在TOAST表中
  • 自动处理：对用户透明，自动处理大字段
  • 存储策略：支持PLAIN、EXTENDED、EXTERNAL、MAIN四种策略
风哥提示：TOAST机制是PostgreSQL处理大字段的重要技术，了解TOAST的工作原理有助于优化大对象的存储和查询性能。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL大对象配置

PostgreSQL大对象配置建议：

# 大对象配置参数

# TOAST相关配置
large_object_share_memory = 1MB # 大对象共享内存

# 自动清理大对象
autovacuum_max_workers = 3 # 自动清理工作进程数
autovacuum_naptime = 10min # 自动清理间隔

# 示例：创建支持大对象的表
CREATE TABLE fgedu_documents (
id SERIAL PRIMARY KEY,
title VARCHAR(100),
content TEXT,
data BYTEA,
json_data JSONB
);

# 示例：设置TOAST存储策略
CREATE TABLE fgedu_large_texts (
id SERIAL PRIMARY KEY,
content TEXT
) WITH (toast.storage_strategy = ‘extended’);

2.2 PostgreSQL大对象性能优化

PostgreSQL大对象性能优化建议：

# 大对象性能优化

# 存储优化
– 使用合适的TOAST存储策略
– 对不经常访问的大对象使用EXTERNAL策略
– 对经常访问的大对象使用EXTENDED策略
– 合理设置TOAST压缩

# 查询优化
– 避免SELECT *，只查询需要的字段
– 使用部分索引
– 考虑使用分区表
– 合理使用缓存

# 写入优化
– 批量写入大对象
– 避免频繁更新大对象
– 使用事务批量操作

# 示例：优化大对象存储
CREATE TABLE fgedu_images (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
image BYTEA
) WITH (
toast.storage_strategy = ‘external’,
autovacuum_enabled = true,
autovacuum_vacuum_scale_factor = 0.05
);

2.3 PostgreSQL大对象监控

PostgreSQL大对象监控建议：

  • TOAST表大小：监控TOAST表的大小
  • 大对象数量：监控Large Objects的数量
  • 大对象大小：监控单个大对象的大小
  • TOAST访问：监控TOAST表的访问情况
  • 存储空间：监控大对象占用的存储空间
风哥教程针对风哥教程针对风哥教程针对生产环境建议：建立完善的大对象监控体系，及时发现和解决大对象相关问题。定期分析大对象使用情况，优化存储策略。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 PostgreSQL大对象管理

3.1.1 Large Objects管理

# Large Objects管理

# 创建大对象
SELECT lo_create(0); — 返回oid

# 导入大对象
SELECT lo_import(‘/path/to/file’); — 从文件导入

# 导出大对象
SELECT lo_export(oid, ‘/path/to/file’); — 导出到文件

# 删除大对象
SELECT lo_unlink(oid); — 删除大对象

# 查看大对象
SELECT
oid,
lomowner,
lomacl
FROM pg_largeobject_metadata;

# 示例：使用Large Objects
— 创建大对象
SELECT lo_create(0) INTO oid;
— 写入数据
— 读取数据
— 删除大对象
SELECT lo_unlink(oid);

3.1.2 TOAST表管理

# TOAST表管理

# 查看TOAST表
SELECT
c.relname AS toast_table,
t.relname AS main_table
FROM pg_class t
JOIN pg_class c ON c.reltoastrelid = t.oid
WHERE t.relname LIKE ‘fgedu_%’;

# 查看TOAST表大小
SELECT
t.relname AS table_name,
c.relname AS toast_table,
pg_size_pretty(pg_total_relation_size(c.oid)) AS toast_size
FROM pg_class t
JOIN pg_class c ON c.reltoastrelid = t.oid
WHERE t.relname LIKE ‘fgedu_%’;

# 优化TOAST表
VACUUM FULL VERBOSE pg_toast.pg_toast_12345;

# 重建TOAST表
ALTER TABLE fgedu_documents ALTER COLUMN content SET STORAGE EXTENDED;

3.2 PostgreSQL TOAST配置

3.2.1 TOAST存储策略

# TOAST存储策略

# 存储策略类型
– PLAIN：不压缩，不存储在TOAST表
– EXTENDED：先压缩，再存储在TOAST表（默认）
– EXTERNAL：不压缩，存储在TOAST表
– MAIN：压缩，存储在主表

# 设置存储策略
ALTER TABLE fgedu_documents ALTER COLUMN content SET STORAGE EXTENDED;
ALTER TABLE fgedu_images ALTER COLUMN image SET STORAGE EXTERNAL;

# 查看存储策略
SELECT
attname,
attstorage
FROM pg_attribute
WHERE attrelid = ‘fgedu_documents’::regclass
AND attnum > 0;

# 存储策略建议
– 小字段：PLAIN
– 文本字段：EXTENDED
– 二进制字段：EXTERNAL
– 频繁访问的字段：MAIN

3.2.2 TOAST压缩

# TOAST压缩

# 压缩配置
— PostgreSQL自动处理压缩
— 压缩算法：pglz

# 查看压缩效果
SELECT
id,
pg_column_size(content) AS original_size,
pg_column_size(compress(content)) AS compressed_size
FROM fgedu_documents;

# 压缩建议
– 文本数据：适合压缩
– 已经压缩的数据（如图片、视频）：不适合压缩
– 频繁访问的数据：权衡压缩和解压缩开销

3.3 PostgreSQL大对象备份

3.3.1 大对象备份

# 大对象备份

# 使用pg_dump备份
pg_dump -Fc –blobs fgedudb > fgedudb.dump

# 使用pg_dumpall备份
pg_dumpall –blobs > fgedudb_all.sql

# 备份Large Objects
pg_dump -t pg_largeobject -t pg_largeobject_metadata fgedudb > lo_backup.sql

# 恢复备份
pg_restore -d fgedudb fgedudb.dump

# 验证备份
SELECT count(*) FROM pg_largeobject;

3.3.2 大对象清理

# 大对象清理

# 清理未引用的Large Objects
VACUUM FULL pg_largeobject;

# 清理TOAST表
VACUUM FULL VERBOSE pg_toast.pg_toast_12345;

# 查找未使用的Large Objects
SELECT oid
FROM pg_largeobject_metadata
WHERE oid NOT IN (
SELECT DISTINCT lo
FROM (
SELECT unnest(lo_col) AS lo FROM your_table
) t
);

# 删除未使用的Large Objects
SELECT lo_unlink(oid)
FROM pg_largeobject_metadata
WHERE oid NOT IN (
SELECT DISTINCT lo
FROM (
SELECT unnest(lo_col) AS lo FROM your_table
) t
);

风哥提示：大对象备份是数据库备份的重要组成部分，建议在备份策略中包含大对象的备份，确保数据的完整性。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL大对象实战案例

4.1.1 存储图片文件

# 存储图片文件

# 创建表结构
CREATE TABLE fgedu_images (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
image BYTEA,
upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 插入图片
— 使用psql的\lo_import
\lo_import ‘/path/to/image.jpg’ — 返回oid
INSERT INTO fgedu_images (name, image) VALUES (‘test’, lo_get(12345));

— 使用应用程序插入
— 示例代码（Python）
“””
import psycopg2
with open(‘/path/to/image.jpg’, ‘rb’) as f:
image_data = f.read()
conn = psycopg2.connect(fgedudb=’fgedudb’, fgedu=’fgedu’)
cur = conn.cursor()
cur.execute(“INSERT INTO fgedu_images (name, image) VALUES (%s, %s)”, (‘test’, image_data))
conn.commit()
“””

# 查询图片
SELECT id, name, pg_size_pretty(pg_column_size(image)) AS size
FROM fgedu_images;

# 执行结果
id | name | size
—+——+——
1 | test | 1.2 MB

4.2 PostgreSQL大对象工具使用

4.2.1 使用lo_*函数

# 使用lo_*函数

# 导入文件
SELECT lo_import(‘/path/to/file.txt’);

# 导出文件
SELECT lo_export(12345, ‘/path/to/output.txt’);

# 创建大对象
SELECT lo_create(0);

# 删除大对象
SELECT lo_unlink(12345);

# 读取大对象
SELECT lo_get(12345);

# 示例：完整操作
— 创建大对象
SELECT lo_create(0) INTO oid;
— 写入数据
— 读取数据
SELECT lo_get(oid);
— 删除大对象
SELECT lo_unlink(oid);

4.3 PostgreSQL大对象常见问题

PostgreSQL大对象常见问题及解决方法：

# 常见问题1：TOAST表过大

# 症状：TOAST表占用大量空间

# 解决方法
– 分析大字段使用情况
SELECT
column_name,
pg_size_pretty(avg(pg_column_size(column_name)))
FROM fgedu_documents
GROUP BY column_name;

– 优化存储策略
ALTER TABLE fgedu_documents ALTER COLUMN content SET STORAGE EXTERNAL;

– 清理TOAST表
VACUUM FULL VERBOSE pg_toast.pg_toast_12345;

# 常见问题2：Large Objects泄漏

# 症状：未引用的Large Objects占用空间

# 解决方法
– 查找未使用的Large Objects
SELECT oid
FROM pg_largeobject_metadata
WHERE oid NOT IN (
SELECT DISTINCT lo
FROM (
SELECT unnest(lo_col) AS lo FROM your_table
) t
);

– 删除未使用的Large Objects
SELECT lo_unlink(oid)
FROM pg_largeobject_metadata
WHERE oid NOT IN (
SELECT DISTINCT lo
FROM (
SELECT unnest(lo_col) AS lo FROM your_table
) t
);

# 常见问题3：大对象查询性能慢

# 症状：查询包含大对象的表性能慢

# 解决方法
– 避免SELECT *
– 只查询需要的字段
– 使用部分索引
– 考虑使用分区表
– 优化存储策略

风哥教程针对风哥教程针对风哥教程针对生产环境建议：定期监控大对象的使用情况，及时清理未使用的大对象，优化存储策略，确保大对象的存储和查询性能。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL大对象最佳实践

PostgreSQL大对象最佳实践：

  • 选择合适的存储方式：根据数据大小和访问模式选择
  • 合理设置TOAST策略：根据数据类型和访问频率
  • 避免存储过大的对象：考虑使用文件系统存储
  • 定期清理大对象：删除未使用的大对象
  • 备份大对象：确保大对象的备份
  • 监控大对象使用：及时发现和解决问题
  • 优化查询：避免全表扫描和SELECT *
  • 合理设计表结构：将大对象与其他字段分离
风哥提示：大对象存储是PostgreSQL的重要功能，合理的大对象管理可以提高数据库性能和可靠性。建议根据实际需求，选择合适的大对象存储方式。

5.2 PostgreSQL大对象检查清单

# 大对象检查清单
– [ ] 大对象存储方式是否选择正确
– [ ] TOAST存储策略是否合理
– [ ] 大对象是否定期清理
– [ ] 大对象备份是否包含在备份策略中
– [ ] 大对象查询是否优化
– [ ] 大对象存储是否影响数据库性能
– [ ] TOAST表大小是否合理
– [ ] Large Objects是否存在泄漏

# 大对象维护清单
– [ ] 定期分析大对象使用情况
– [ ] 定期清理未使用的大对象
– [ ] 定期优化TOAST表
– [ ] 定期备份大对象
– [ ] 监控大对象存储增长
– [ ] 优化大对象查询性能

5.3 PostgreSQL大对象工具推荐

PostgreSQL大对象工具推荐：

  • lo_import/lo_export：导入导出大对象
  • lo_create/lo_unlink：创建删除大对象
  • pg_dump/pg_restore：备份恢复大对象
  • pg_size_pretty：查看大对象大小
  • pg_column_size：查看列大小
  • VACUUM：清理大对象
  • ALTER TABLE：修改TOAST存储策略
  • psql：使用\lo_import和\lo_export
持续改进：大对象存储技术在不断发展，PostgreSQL也在不断优化大对象处理机制。建议关注PostgreSQL的版本更新，及时了解和使用新的大对象特性和优化方法。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息