2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG095-PG存储优化：表/索引存储格式选择

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

本文档风哥主要介绍PostgreSQL的存储优化，特别是表和索引的存储格式选择，包括存储格式的概念、类型、选择建议和优化策略。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL存储格式的概念

PostgreSQL存储格式是指PostgreSQL存储表和索引数据的方式，包括表的存储格式和索引的存储格式。不同的存储格式具有不同的特点和适用场景，选择合适的存储格式可以提高数据库的性能和存储效率。更多视频教程www.fgedu.net.cn

PostgreSQL存储格式的主要特点：

  • 影响数据的存储方式和访问效率
  • 影响存储空间的使用
  • 影响查询性能
  • 影响数据压缩率
  • 影响写入性能

1.2 PostgreSQL存储格式类型

PostgreSQL支持的存储格式类型包括：

# PostgreSQL存储格式类型
– 表存储格式：
– HEAP：传统的堆存储格式
– TOAST：大对象存储格式
– COLUMNAR：列式存储格式（通过扩展支持）

– 索引存储格式：
– B-Tree：默认的索引存储格式
– Hash：哈希索引存储格式
– GiST：通用搜索树索引存储格式
– SP-GiST：空间分区通用搜索树索引存储格式
– GIN：通用倒排索引存储格式
– BRIN：块范围索引存储格式
– Bloom：布隆过滤器索引存储格式

# 表存储格式特点
– HEAP：适合随机读写，是默认的存储格式
– TOAST：适合存储大对象，自动压缩和拆分大字段
– COLUMNAR：适合分析型查询，按列存储数据

# 索引存储格式特点
– B-Tree：适合等值查询和范围查询，是默认的索引存储格式
– Hash：适合等值查询，不支持范围查询
– GiST：适合空间数据和全文搜索
– SP-GiST：适合非平衡数据结构，如IP地址
– GIN：适合全文搜索和数组查询
– BRIN：适合大型表的范围查询，存储空间小
– Bloom：适合集合成员查询，存储空间小

1.3 PostgreSQL存储格式的优势

PostgreSQL存储格式的优势包括：

  • 灵活性：支持多种存储格式，适应不同的应用场景
  • 性能优化：根据数据类型和查询模式选择合适的存储格式
  • 存储效率：通过压缩和优化存储方式，减少存储空间
  • 可扩展性：支持自定义存储格式和索引类型
  • 兼容性：不同存储格式之间可以相互转换

Part02-生产环境规划与建议

2.1 PostgreSQL存储优化规划

PostgreSQL存储优化规划要点：

# 存储优化规划步骤
1. 分析数据类型：了解数据的类型和特点
2. 分析查询模式：了解查询的类型和频率
3. 选择存储格式：根据数据类型和查询模式选择合适的存储格式
4. 配置存储参数：设置合适的存储参数
5. 测试存储性能：在测试环境中测试存储性能
6. 监控和调整：监控存储性能，根据需要调整存储格式和参数

# 数据类型分析
– 结构化数据：适合使用HEAP存储格式
– 大对象数据：适合使用TOAST存储格式
– 分析型数据：适合使用COLUMNAR存储格式

# 查询模式分析
– 随机读写：适合使用HEAP存储格式和B-Tree索引
– 范围查询：适合使用B-Tree索引或BRIN索引
– 全文搜索：适合使用GIN索引
– 空间数据：适合使用GiST索引
– 集合成员查询：适合使用Bloom索引

2.2 存储格式选择建议

PostgreSQL存储格式选择建议：

# 表存储格式选择建议
– HEAP：
– 适用场景：一般OLTP应用，随机读写频繁
– 特点：读写性能均衡，适合大多数应用
– 建议：作为默认存储格式

– TOAST：
– 适用场景：存储大对象，如文本、图像等
– 特点：自动压缩和拆分大字段，减少存储空间
– 建议：当表中包含大字段时使用

– COLUMNAR：
– 适用场景：分析型查询，如数据仓库
– 特点：按列存储，适合聚合查询
– 建议：当主要进行分析型查询时使用

# 索引存储格式选择建议
– B-Tree：
– 适用场景：等值查询和范围查询
– 特点：平衡树结构，查询效率高
– 建议：作为默认索引存储格式

– Hash：
– 适用场景：等值查询
– 特点：查询速度快，不支持范围查询
– 建议：当只需要等值查询时使用

– GiST：
– 适用场景：空间数据和全文搜索
– 特点：支持复杂数据类型
– 建议：当需要处理空间数据或全文搜索时使用

– SP-GiST：
– 适用场景：非平衡数据结构，如IP地址
– 特点：适合特定类型的数据
– 建议：当处理非平衡数据结构时使用

– GIN：
– 适用场景：全文搜索和数组查询
– 特点：支持多值数据类型
– 建议：当需要处理全文搜索或数组时使用

– BRIN：
– 适用场景：大型表的范围查询
– 特点：存储空间小，查询效率适中
– 建议：当处理大型表的范围查询时使用

– Bloom：
– 适用场景：集合成员查询
– 特点：存储空间小，查询速度快
– 建议：当需要处理集合成员查询时使用

2.3 存储优化策略

PostgreSQL存储优化策略：

  • 选择合适的存储格式：根据数据类型和查询模式选择合适的存储格式
  • 配置存储参数：设置合适的存储参数，如TOAST压缩级别
  • 使用分区表：对于大型表，使用分区表提高查询性能
  • 使用合适的索引：根据查询模式选择合适的索引类型
  • 定期维护：定期执行VACUUM和ANALYZE操作，保持存储效率
  • 监控存储使用：监控存储空间使用情况，及时调整存储策略
风哥提示：存储优化是PostgreSQL性能调优的重要组成部分，需要根据数据类型和查询模式选择合适的存储格式和索引类型。学习交流加群风哥QQ113257174

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

3.1 表存储优化

3.1.1 HEAP存储格式配置

# HEAP存储格式配置
$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_employees_heap (
id serial PRIMARY KEY,
name text NOT NULL,
department text NOT NULL,
salary numeric NOT NULL,
hire_date date NOT NULL
) WITH (storage_type = ‘heap’);”

# 查看表存储格式
$ sudo -u pgsql psql -d fgedu_production -c “\d+ fgedu_employees_heap”

# 插入测试数据
$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_employees_heap (name, department, salary, hire_date) VALUES (‘John Doe’, ‘IT’, 8000, ‘2020-01-01’), (‘Jane Smith’, ‘HR’, 6000, ‘2020-02-01’);”

# 查询测试
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM fgedu_employees_heap;”

3.1.2 TOAST存储格式配置

# TOAST存储格式配置
$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_documents (
id serial PRIMARY KEY,
title text NOT NULL,
content text NOT NULL,
created_at timestamp NOT NULL
) WITH (toast.compression = ‘pglz’);”

# 查看表存储格式
$ sudo -u pgsql psql -d fgedu_production -c “\d+ fgedu_documents”

# 插入测试数据
$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_documents (title, content, created_at) VALUES (‘Document 1’, repeat(‘This is a test document. ‘, 1000), NOW());”

# 查询测试
$ sudo -u pgsql psql -d fgedu_production -c “SELECT id, title, length(content) FROM fgedu_documents;”

3.1.3 COLUMNAR存储格式配置

# 安装列存储扩展
$ sudo -u pgsql psql -c “CREATE EXTENSION columnar;”

# COLUMNAR存储格式配置
$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_fgfgfgfgsales_columnar (
id serial PRIMARY KEY,
product_id integer NOT NULL,
quantity integer NOT NULL,
amount numeric NOT NULL,
sale_date date NOT NULL
) USING columnar;

# 查看表存储格式
$ sudo -u pgsql psql -d fgedu_production -c “\d+ fgedu_fgfgfgfgsales_columnar”

# 插入测试数据
$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_fgfgfgfgsales_columnar (product_id, quantity, amount, sale_date) SELECT i % 100, i % 10, i * 100, NOW() – (i || ‘ days’)::interval FROM generate_series(1, 1000000) i;”

# 查询测试
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT product_id, SUM(amount) FROM fgedu_fgfgfgfgsales_columnar GROUP BY product_id;”

3.2 索引存储优化

3.2.1 B-Tree索引配置

# B-Tree索引配置
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”

# 查看索引信息
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_employees”

# 查询测试
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”

3.2.2 BRIN索引配置

# BRIN索引配置
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_fgfgfgfgsales_sale_date ON fgedu_fgfgfgfgsales USING brin(sale_date);”

# 查看索引信息
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_fgfgfgfgsales”

# 查询测试
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgfgsales WHERE sale_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;”

3.2.3 GIN索引配置

# GIN索引配置
$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_tags (
id serial PRIMARY KEY,
name text NOT NULL,
tags text[]
);”

$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_tags_tags ON fgedu_tags USING gin(tags);”

# 插入测试数据
$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_tags (name, tags) VALUES (‘Product 1’, ‘{electronics, gadget, tech}’), (‘Product 2’, ‘{clothing, fgapparel, fashion}’);”

# 查询测试
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_tags WHERE tags @> ‘{tech}’;”

3.3 存储参数配置

3.3.1 TOAST参数配置

# TOAST参数配置
$ sudo vi /postgresql/data/postgresql.conf

# 修改TOAST参数
toast_tuple_target = 2048 # TOAST阈值，默认2048字节
toast_compression = ‘pglz’ # TOAST压缩算法，可选pglz或lz4

# 保存并退出

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

# 验证修改结果
$ sudo -u pgsql psql -c “SHOW toast_tuple_target;”

toast_tuple_target
——————–
2048
(1 row)

$ sudo -u pgsql psql -c “SHOW toast_compression;”

toast_compression
——————-
pglz
(1 row)

3.3.2 表存储参数配置

# 表存储参数配置
$ sudo -u pgsql psql -d fgedu_production -c “ALTER TABLE fgedu_employees SET (
fillfactor = 80,
autovacuum_enabled = true,
toast.compression = ‘lz4’
);”

# 查看表存储参数
$ sudo -u pgsql psql -d fgedu_production -c “\d+ fgedu_employees”

# 索引存储参数配置
$ sudo -u pgsql psql -d fgedu_production -c “ALTER INDEX idx_fgedu_employees_salary SET (fillfactor = 90);”

# 查看索引存储参数
$ sudo -u pgsql psql -d fgedu_production -c “\d+ idx_fgedu_employees_salary”

Part04-生产案例与实战讲解

4.1 表存储优化案例

4.1.1 案例描述

场景：一个存储大量文档的数据库，需要优化表存储格式以减少存储空间和提高查询性能。

4.1.2 实施方案

# 1. 分析数据特点

# 查看表结构
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_documents”

Table “public.fgedu_documents”
Column | Type | Collation | Nullable | Default
———–+—————————–+———–+———-+————————————–
id | integer | | not null | nextval(‘fgedu_documents_id_seq’::regclass)
title | text | | not null |
content | text | | not null |
created_at | timestamp without time zone | | not null |

# 查看数据大小
$ sudo -u pgsql psql -d fgedu_production -c “SELECT pg_size_pretty(pg_total_relation_size(‘fgedu_documents’));”

pg_size_pretty
—————-
100 MB

# 2. 优化TOAST存储参数

$ sudo -u pgsql psql -d fgedu_production -c “ALTER TABLE fgedu_documents SET (toast.compression = ‘lz4’);”

# 3. 重建表以应用新的存储参数

$ sudo -u pgsql psql -d fgedu_production -c “VACUUM FULL fgedu_documents;”

# 4. 验证优化效果

$ sudo -u pgsql psql -d fgedu_production -c “SELECT pg_size_pretty(pg_total_relation_size(‘fgedu_documents’));”

pg_size_pretty
—————-
60 MB

# 5. 测试查询性能

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_documents WHERE title LIKE ‘%test%’;”

4.2 索引存储优化案例

4.2.1 案例描述

场景：一个大型销售表，需要优化索引存储格式以提高范围查询性能。

4.2.2 实施方案

# 1. 分析数据特点

# 查看表结构
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_fgfgfgfgsales”

Table “public.fgedu_fgfgfgfgsales”
Column | Type | Collation | Nullable | Default
———–+———+———–+———-+————————————–
id | integer | | not null | nextval(‘fgedu_fgfgfgfgsales_id_seq’::regclass)
product_id | integer | | not null |
quantity | integer | | not null |
amount | numeric | | not null |
sale_date | date | | not null |

# 查看数据大小
$ sudo -u pgsql psql -d fgedu_production -c “SELECT pg_size_pretty(pg_total_relation_size(‘fgedu_fgfgfgfgsales’));”

pg_size_pretty
—————-
5 GB

# 2. 创建BRIN索引

$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_fgfgfgfgsales_sale_date_brin ON fgedu_fgfgfgfgsales USING brin(sale_date);”

# 3. 查看索引大小

$ sudo -u pgsql psql -d fgedu_production -c “SELECT pg_size_pretty(pg_indexes_size(‘fgedu_fgfgfgfgsales’));”

pg_size_pretty
—————-
10 MB

# 4. 测试查询性能

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgfgsales WHERE sale_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;”

# 5. 比较B-Tree索引和BRIN索引

# 创建B-Tree索引
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_fgfgfgfgsales_sale_date_btree ON fgedu_fgfgfgfgsales USING btree(sale_date);”

# 查看索引大小
$ sudo -u pgsql psql -d fgedu_production -c “SELECT pg_size_pretty(pg_indexes_size(‘fgedu_fgfgfgfgsales’));”

pg_size_pretty
—————-
500 MB

# 测试查询性能
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgfgsales WHERE sale_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;”

4.3 存储优化性能测试案例

4.3.1 案例描述

场景：需要测试不同存储格式和索引类型的性能，以确定最佳存储策略。

学习交流加群风哥微信: itpux-com

4.3.2 实施方案

# 1. 创建测试表

# HEAP存储格式
$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_test_heap (
id serial PRIMARY KEY,
name text NOT NULL,
value numeric NOT NULL,
created_at timestamp NOT NULL
) WITH (storage_type = ‘heap’);”

# COLUMNAR存储格式
$ sudo -u pgsql psql -d fgedu_production -c “CREATE TABLE fgedu_test_columnar (
id serial PRIMARY KEY,
name text NOT NULL,
value numeric NOT NULL,
created_at timestamp NOT NULL
) USING columnar;”

# 2. 插入测试数据

$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_test_heap (name, value, created_at) SELECT ‘Test ‘ || i, random() * 10000, NOW() – (i || ‘ days’)::interval FROM generate_series(1, 1000000) i;”

$ sudo -u pgsql psql -d fgedu_production -c “INSERT INTO fgedu_test_columnar (name, value, created_at) SELECT ‘Test ‘ || i, random() * 10000, NOW() – (i || ‘ days’)::interval FROM generate_series(1, 1000000) i;”

# 3. 创建索引

# B-Tree索引
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_test_heap_value ON fgedu_test_heap(value);”

# BRIN索引
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_test_heap_created_at_brin ON fgedu_test_heap USING brin(created_at);”

# 4. 测试查询性能

# 测试HEAP存储格式的查询性能
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT AVG(value) FROM fgedu_test_heap WHERE created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;”

# 测试COLUMNAR存储格式的查询性能
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT AVG(value) FROM fgedu_test_columnar WHERE created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;”

# 5. 测试写入性能

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE INSERT INTO fgedu_test_heap (name, value, created_at) VALUES (‘Test Insert’, 5000, NOW());”

$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE INSERT INTO fgedu_test_columnar (name, value, created_at) VALUES (‘Test Insert’, 5000, NOW());”

# 6. 分析测试结果

# 比较不同存储格式的查询性能
# 比较不同存储格式的写入性能
# 比较不同索引类型的性能
# 确定最佳存储策略

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议根据数据类型和查询模式选择合适的存储格式和索引类型。通过测试不同存储格式和索引类型的性能，确定最佳存储策略，以提高数据库的性能和存储效率。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL存储优化最佳实践

PostgreSQL存储优化最佳实践：

from oracle:www.itpux.com

  • 根据数据类型选择存储格式：根据数据的类型和特点选择合适的存储格式
  • 根据查询模式选择索引类型：根据查询的类型和频率选择合适的索引类型
  • 配置合适的存储参数：根据数据特点和查询模式配置合适的存储参数
  • 使用分区表：对于大型表，使用分区表提高查询性能
  • 定期维护：定期执行VACUUM和ANALYZE操作，保持存储效率
  • 监控存储使用：监控存储空间使用情况，及时调整存储策略
  • 测试性能：在测试环境中测试不同存储格式和索引类型的性能
  • 持续优化：根据业务需求和系统变化不断调整存储策略

5.2 存储优化常见问题

存储优化常见问题及解决方案：

  • 存储空间不足：解决方案：使用TOAST压缩，清理无用数据，使用分区表
  • 查询性能下降：解决方案：选择合适的存储格式和索引类型，优化查询语句
  • 写入性能下降：解决方案：调整存储参数，使用合适的存储格式
  • 索引过大：解决方案：选择合适的索引类型，如BRIN索引
  • TOAST溢出：解决方案：调整toast_tuple_target参数，使用合适的压缩算法
  • 分区表管理复杂：解决方案：使用自动分区，定期维护分区表
  • 存储格式转换困难：解决方案：使用CREATE TABLE fgedu_AS语句转换存储格式
  • 备份恢复时间长：解决方案：使用增量备份，优化存储格式

5.3 存储优化技巧

存储优化技巧：

  • 表存储优化：
    • 对于一般OLTP应用，使用HEAP存储格式
    • 对于存储大对象的表，使用TOAST存储格式
    • 对于分析型查询，使用COLUMNAR存储格式
    • 调整fillfactor参数，优化存储空间使用
    • 使用合适的TOAST压缩算法，减少存储空间
  • 索引存储优化：
    • 对于等值查询和范围查询，使用B-Tree索引
    • 对于大型表的范围查询，使用BRIN索引
    • 对于全文搜索和数组查询，使用GIN索引
    • 对于空间数据，使用GiST索引
    • 对于集合成员查询，使用Bloom索引
  • 分区表优化：
    • 对于大型表，使用分区表提高查询性能
    • 根据时间或范围进行分区
    • 定期清理旧分区数据
    • 使用自动分区功能
  • 存储参数优化：
    • 调整toast_tuple_target参数，优化TOAST存储
    • 调整toast_compression参数，选择合适的压缩算法
    • 调整fillfactor参数，优化索引和表的存储空间
    • 调整autovacuum参数，自动维护存储效率
风哥提示：存储优化是PostgreSQL性能调优的重要组成部分，需要根据数据类型和查询模式选择合适的存储格式和索引类型。通过测试不同存储格式和索引类型的性能，确定最佳存储策略，以提高数据库的性能和存储效率。from PostgreSQL:www.itpux.com

持续改进：存储优化是一个持续的过程，需要根据业务需求和系统变化不断调整和优化。建议建立存储优化的标准流程，定期审查和调整存储策略，以保持系统的最佳性能。

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

相关推荐

联系我们

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

微信号：itpux-com

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