2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG210-PG表的物理存储：堆表/索引表存储格式

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

本文档风哥主要介绍PostgreSQL数据库表的物理存储格式，包括堆表存储、索引表存储、TOAST存储等内容，风哥教程参考PostgreSQL官方文档Table Storage内容，适合数据库管理员和开发者在学习和测试中使用。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 PostgreSQL表存储概念

PostgreSQL表存储是指PostgreSQL数据库中表数据的物理存储方式。PostgreSQL使用堆表（Heap Table）作为默认的表存储结构，同时支持多种索引类型来加速查询。表存储涉及数据页、元组、TOAST等概念。

PostgreSQL表存储的特点：

  • 使用堆表作为默认存储结构
  • 支持多种索引类型
  • 使用TOAST存储大对象
  • 支持MVCC（多版本并发控制）
  • 数据以页为单位存储

1.2 PostgreSQL堆表存储

堆表是PostgreSQL默认的表存储结构，其特点是：

  • 无序存储：数据按照插入顺序存储，不保证有序
  • 页结构：数据以8KB为单位的页存储
  • 元组结构：每个数据行对应一个元组
  • MVCC支持：通过元组头部信息实现多版本
  • 空闲空间管理：通过FSM（空闲空间映射）管理空闲空间

1.3 PostgreSQL索引表存储

PostgreSQL支持多种索引类型，每种索引有不同的存储结构：

  • B树索引：默认索引类型，适合等值查询和范围查询
  • 哈希索引：适合等值查询
  • GiST索引：适合地理空间数据和全文检索
  • GIN索引：适合JSON、数组等复合数据类型
  • BRIN索引：适合大型时序数据
风哥提示：了解PostgreSQL表的物理存储结构，有助于理解数据库的工作原理，为表设计和性能优化提供基础。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL表设计建议

PostgreSQL表设计建议：

# 表结构设计
– 合理设计字段类型：选择合适的数据类型
– 避免使用过大的字段：如TEXT存储小文本
– 合理设置字段长度：根据实际需要设置
– 使用合适的约束：主键、外键、唯一约束等

# 表存储设计
– 合理规划表空间：根据数据类型选择表空间
– 考虑分区表：对于大型表使用分区
– 合理设置填充因子：根据更新频率设置
– 考虑压缩：对于不经常更新的表

# 示例：合理的表设计
CREATE TABLE fgedu_fgedus (
id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedu_data;

# 填充因子设置
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount NUMERIC(10,2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) WITH (fillfactor=80);

2.2 PostgreSQL存储优化

PostgreSQL存储优化建议：

# 存储参数优化
– shared_buffers：设置为物理内存的25%
– work_mem：根据查询复杂度设置
– maintenance_work_mem：设置为较大值（如1GB）
– random_page_cost：SSD设置为1.1，HDD设置为4.0

# 表存储优化
– 定期执行VACUUM：回收空间，更新统计信息
– 合理设置autovacuum参数：根据表大小和更新频率
– 使用合适的填充因子：减少页分裂
– 考虑表压缩：使用pg_repack等工具

# 示例：autovacuum配置
ALTER TABLE fgedu_fgedus SET (
autovacuum_enabled = true,
autovacuum_vacuum_threshold = 50,
autovacuum_analyze_threshold = 50,
autovacuum_vacuum_scale_factor = 0.1,
autovacuum_analyze_scale_factor = 0.05
);

2.3 PostgreSQL索引策略

PostgreSQL索引策略建议：

  • 按需创建索引：根据查询模式创建索引
  • 选择合适的索引类型：根据数据类型和查询模式
  • 合理设置索引列顺序：将选择性高的列放在前面
  • 避免过度索引：过多索引会影响写入性能
  • 定期维护索引：重建或重新索引
风哥教程针对风哥教程针对风哥教程针对生产环境建议：在设计表结构时，应该考虑存储效率和查询性能，合理设计字段类型和索引策略。学习交流加群风哥QQ113257174

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

3.1 PostgreSQL堆表存储详细结构

3.1.1 数据页结构

# PostgreSQL数据页结构

# 数据页大小：8KB（默认）
# 页头：24字节
# 元组：可变长度
# 特殊空间：可变长度

# 页头结构
– PageHeaderData (24 bytes)
– pd_lsn：页面最后修改的LSN
– pd_checksum：页面校验和
– pd_flags：页面标志
– pd_lower：元组开始位置
– pd_upper：元组结束位置
– pd_special：特殊空间开始位置
– pd_pagesize_version：页面大小和版本
– pd_prune_xid：需要清理的最老事务ID

# 元组结构
– HeapTupleHeaderData (23 bytes)
– t_xmin：插入事务ID
– t_xmax：删除或更新事务ID
– t_cid：命令ID
– t_xvac：清理事务ID
– t_ctid：当前元组ID
– t_infomask：信息掩码
– t_infomask2：额外信息掩码
– t_hoff：元组头部长度
– 数据部分：字段数据

3.1.2 空闲空间映射

# 空闲空间映射（FSM）

# FSM文件：OID_fsm
# 作用：跟踪表的空闲空间
# 结构：层次化的二叉树

# FSM使用场景
– 插入数据时寻找合适的页
– VACUUM操作时更新空闲空间信息

# 查看FSM信息
SELECT relname, pg_size_pretty(pg_relation_size(relname || ‘_fsm’))
FROM pg_class
WHERE relname LIKE ‘fgedu_%’;

3.2 PostgreSQL索引表存储详细结构

3.2.1 B树索引结构

# B树索引结构

# 索引页类型
– 叶子页：存储索引键和元组指针
– 非叶子页：存储索引键和指向下层页的指针

# B树索引特点
– 平衡树结构
– 有序存储
– 支持范围查询
– 适合等值查询

# B树索引页面结构
– 页头：与数据页类似
– 索引项：键值 + 指针
– 特殊空间：B树特定信息

# 示例：创建B树索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

# 查看索引大小
SELECT pg_size_pretty(pg_indexes_size(‘fgedu_fgedus’));

3.2.2 其他索引类型

# 其他索引类型

# 哈希索引
CREATE INDEX idx_fgedu_fgedus_fgeduname ON fgedu_fgedus USING hash(fgeduname);

# GiST索引
CREATE INDEX idx_fgedu_locations_point ON fgedu_locations USING gist(point);

# GIN索引
CREATE INDEX idx_fgedu_products_tags ON fgedu_products USING gin(tags);

# BRIN索引
CREATE INDEX idx_fgedu_fgfgfgsales_date ON fgedu_fgfgfgsales USING brin(sale_date);

# 查看索引类型
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = ‘fgedu_fgedus’;

3.3 PostgreSQL TOAST存储

3.3.1 TOAST概念

# TOAST（The Oversized-Attribute Storage Technique）

# TOAST作用：存储超过页大小的大对象
# TOAST策略：
– PLAIN：不压缩，不存储在TOAST表
– EXTENDED：先压缩，再存储在TOAST表
– EXTERNAL：不压缩，存储在TOAST表
– MAIN：压缩，存储在主表

# TOAST表
– 自动创建：当表包含大字段时
– 命名规则：pg_toast_+ OID
– 存储：大字段数据

# 示例：TOAST表查看
SELECT relname, relkind
FROM pg_class
WHERE relname LIKE ‘pg_toast%’;

3.3.2 TOAST使用

# TOAST使用

# 创建包含大字段的表
CREATE TABLE fgedu_documents (
id SERIAL PRIMARY KEY,
title VARCHAR(100),
content TEXT,
data BYTEA
);

# 查看TOAST表
SELECT c.relname AS toast_table
FROM pg_class t
JOIN pg_class c ON c.reltoastrelid = t.oid
WHERE t.relname = ‘fgedu_documents’;

# 插入大数据
INSERT INTO fgedu_documents (title, content)
VALUES (‘Test Document’, repeat(‘Hello World ‘, 10000));

# 查看TOAST存储
SELECT
id,
title,
pg_column_size(content) AS size,
pg_column_size(content) > 8192 AS toasted
FROM fgedu_documents;

风哥提示：TOAST是PostgreSQL处理大对象的重要机制，了解TOAST的工作原理有助于优化大字段的存储和查询性能。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL表存储实战案例

4.1.1 大表优化实战

# 大表优化实战

# 环境信息
– 表名：fgedu_fgfgfgsales
– 数据量：1000万行
– 表大小：10GB

# 问题：查询性能慢，存储碎片化

# 优化步骤
1. 分析表状态
SELECT
relname,
pg_size_pretty(pg_total_relation_size(relname)) AS total_size,
pg_size_pretty(pg_indexes_size(relname)) AS index_size,
pg_size_pretty(pg_relation_size(relname)) AS data_size
FROM pg_class
WHERE relname = ‘fgedu_fgfgfgsales’;

2. 执行VACUUM ANALYZE
VACUUM ANALYZE fgedu_fgfgfgsales;

3. 重建索引
REINDEX TABLE fgedu_fgfgfgsales;

4. 分区表改造
CREATE TABLE fgedu_fgfgfgsales_partitioned (
id SERIAL,
sale_date DATE,
amount NUMERIC(10,2),
customer_id INTEGER
)
PARTITION BY RANGE (sale_date);

CREATE TABLE fgedu_fgfgfgsales_2024 PARTITION OF fgedu_fgfgfgsales_partitioned
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);

— 迁移数据
INSERT INTO fgedu_fgfgfgsales_partitioned
SELECT * FROM fgedu_fgfgfgsales;

5. 验证性能
EXPLAIN ANALYZE
SELECT * FROM fgedu_fgfgfgsales_partitioned
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-03-31’;

4.2 PostgreSQL存储工具使用

4.2.1 使用pgstattuple查看表状态

# 使用pgstattuple查看表状态

# 安装扩展
CREATE EXTENSION pgstattuple;

# 查看表状态
SELECT * FROM pgstattuple(‘fgedu_fgedus’);

# 结果示例
-[ RECORD 1 ]——+——–
table_len | 8192
tuple_count | 10
tuple_len | 800
tuple_free | 7392
dead_tuple_count | 0
dead_tuple_len | 0
free_space | 7392
free_percent | 90.23

# 查看索引状态
SELECT * FROM pgstatindex(‘idx_fgedu_fgedus_email’);

# 结果示例
-[ RECORD 1 ]——+——–
version | 2
tree_level | 1
index_size | 8192
root_block_no | 1
internal_pages | 0
leaf_pages | 1
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 50.00
leaf_fragmentation | 0.00

4.3 PostgreSQL存储常见问题

PostgreSQL存储常见问题及解决方法：

# 常见问题1：表碎片化

# 症状：表大小增长，查询性能下降

# 解决方法
– 执行VACUUM FULL
VACUUM FULL fgedu_fgedus;

– 使用pg_repack
CREATE EXTENSION pg_repack;
SELECT pg_repack(‘fgedu_fgedus’);

# 常见问题2：TOAST表过大

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

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

– 考虑压缩或分区

# 常见问题3：索引膨胀

# 症状：索引大小异常增长

# 解决方法
– 重建索引
REINDEX TABLE fgedu_fgedus;

– 分析索引使用情况
SELECT
indexrelid::regclass AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_fgedu_indexes
WHERE schemaname = ‘public’;

风哥教程针对风哥教程针对风哥教程针对生产环境建议：定期监控表和索引的存储状态，及时发现和解决存储问题。建立存储维护计划，包括VACUUM、索引重建等操作。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL表存储最佳实践

PostgreSQL表存储最佳实践：

  • 合理设计表结构：选择合适的数据类型和字段长度
  • 优化存储参数：根据硬件和负载调整参数
  • 合理使用索引：根据查询模式创建索引
  • 定期维护：执行VACUUM和ANALYZE操作
  • 监控存储状态：定期检查表和索引的状态
  • 使用分区表：对于大型表使用分区
  • 合理使用TOAST：了解和优化TOAST存储
  • 备份策略：建立完善的备份策略
风哥提示：表存储是PostgreSQL性能的关键因素之一，合理的表设计和存储优化可以显著提高数据库性能。建议根据业务需求和数据特点，制定合适的存储策略。

5.2 PostgreSQL表存储检查清单

# 表存储检查清单
– [ ] 表结构是否合理
– [ ] 索引是否按需创建
– [ ] 填充因子是否设置合理
– [ ] TOAST使用是否优化
– [ ] 表空间是否合理规划
– [ ] 分区表是否使用得当
– [ ] 存储参数是否优化
– [ ] 定期维护是否执行

# 存储维护清单
– [ ] 定期执行VACUUM ANALYZE
– [ ] 定期重建索引
– [ ] 定期检查表碎片化
– [ ] 定期检查TOAST表大小
– [ ] 定期备份数据库
– [ ] 定期监控存储使用情况

5.3 PostgreSQL存储工具推荐

PostgreSQL存储工具推荐：

  • pgstattuple：查看表和索引的详细信息
  • pg_repack：在线重建表和索引
  • pg_freespacemap：查看空闲空间映射
  • pg_size_pretty：查看对象大小
  • pg_stat_fgedu_tables：查看表统计信息
  • pg_stat_fgedu_indexes：查看索引使用情况
  • VACUUM：清理垃圾数据
  • REINDEX：重建索引
持续改进：存储技术在不断发展，PostgreSQL也在不断优化存储机制。建议关注PostgreSQL的版本更新，及时了解和使用新的存储特性和优化方法。

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

相关推荐

联系我们

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

微信号：itpux-com

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