2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG072-PG表空间：规划与存储优化

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

本文档风哥主要介绍PostgreSQL表空间的规划与存储优化，包括表空间的基础概念、生产环境规划、实施方案、实战案例和经验总结。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL表空间的概念

PostgreSQL表空间是数据存储的物理位置，用于管理数据库对象的存储位置。表空间允许将不同的数据库对象存储在不同的磁盘或存储设备上，从而优化存储和性能。更多视频教程www.fgedu.net.cn

PostgreSQL表空间的核心概念：

  • 表空间是物理存储位置的抽象
  • 每个表空间对应一个文件系统目录
  • 数据库对象（表、索引等）可以存储在指定的表空间
  • 表空间提供了存储管理的灵活性
  • 表空间可以跨文件系统和存储设备

1.2 PostgreSQL表空间的类型

PostgreSQL表空间主要分为以下类型：

# PostgreSQL表空间类型
– pg_default：默认表空间，存储用户创建的数据库对象
– pg_global：全局表空间，存储系统共享的系统表
– 用户自定义表空间：根据业务需求创建的表空间

# 表空间用途分类
– 数据文件表空间：存储表数据
– 索引表空间：存储索引数据
– 临时表空间：存储临时数据
– 日志表空间：存储WAL日志（可选）
– 备份表空间：存储备份数据（可选）

1.3 PostgreSQL表空间的优势

PostgreSQL表空间的主要优势：

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

  • 存储优化：可以根据数据类型和访问模式选择合适的存储设备
  • 性能提升：将频繁访问的数据存储在高速存储设备上
  • 管理灵活性：可以根据业务需求调整存储布局
  • 容量管理：可以在不同存储设备之间分配空间
  • 隔离性：不同业务的数据可以存储在不同的表空间
  • 备份策略：可以针对不同表空间制定不同的备份策略
风哥提示：合理使用表空间可以显著提升PostgreSQL数据库的性能和管理效率，特别是在大型系统中。

Part02-生产环境规划与建议

2.1 PostgreSQL表空间规划

PostgreSQL表空间规划要点：

# 表空间数量规划
– 小型系统：1-3个表空间
– 中型系统：3-6个表空间
– 大型系统：6+个表空间

# 表空间命名规范
– 数据文件表空间：data_业务模块
– 索引表空间：index_业务模块
– 临时表空间：temp_实例名
– 日志表空间：log_实例名
– 备份表空间：backup_实例名

# 表空间用途规划
– 系统表空间：使用默认表空间
– 核心业务数据：使用高速存储
– 历史数据：使用大容量存储
– 索引：使用高速存储
– 临时数据：使用高速存储

# 表空间大小规划
– 预估数据量和增长趋势
– 预留足够的存储空间（建议预留30%以上）
– 定期监控表空间使用情况

2.2 存储设备规划

存储设备规划要点：

# 存储设备类型
– SSD：用于频繁访问的数据和索引
– NVMe：用于极高性能要求的场景
– HDD：用于大容量、低访问频率的数据
– SAN：用于企业级存储需求
– NAS：用于备份和归档数据

# 存储设备分配
– 系统表空间：SSD
– 数据文件表空间：根据访问频率选择SSD或HDD
– 索引表空间：SSD
– 临时表空间：SSD
– 日志表空间：SSD
– 备份表空间：HDD或NAS

# 存储RAID级别
– 系统表空间：RAID 10
– 数据文件表空间：RAID 10或RAID 5
– 索引表空间：RAID 10
– 临时表空间：RAID 0或RAID 10
– 日志表空间：RAID 10
– 备份表空间：RAID 5或RAID 6

2.3 性能优化规划

性能优化规划要点：

# I/O优化
– 分散I/O负载：将不同表空间分布在不同的存储设备
– 避免I/O竞争：将读写频繁的表空间分离
– 优化存储参数：根据存储设备类型调整参数

# 表空间布局优化
– 表和索引分离：将表和索引存储在不同的表空间
– 热数据和冷数据分离：将频繁访问的数据存储在高速存储
– 临时数据单独存储：将临时表空间存储在高速存储

# 备份策略优化
– 差异备份：针对不同表空间制定不同的备份策略
– 增量备份：减少备份时间和存储空间
– 备份验证：定期验证备份的有效性

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

3.1 PostgreSQL表空间创建

3.1.1 创建表空间目录

# 创建表空间目录结构
$ mkdir -p /postgresql/fgedutbss/data_business
$ mkdir -p /postgresql/fgedutbss/index_business
$ mkdir -p /postgresql/fgedutbss/temp_business
$ mkdir -p /postgresql/fgedutbss/log_business
$ mkdir -p /postgresql/fgedutbss/backup_business

# 设置目录权限
$ chown -R pgsql: pgsql /postgresql/fgedutbss/
$ chmod -R 700 /postgresql/fgedutbss/

# 验证目录创建
$ ls -la /postgresql/fgedutbss/

total 30
drwx——. 7 pgsql pgsql 4096 Apr 2 10:00 .
drwxr-xr-x. 3 pgsql pgsql 4096 Apr 2 09:00 ..
drwx——. 2 pgsql pgsql 4096 Apr 2 10:00 backup_business
drwx——. 2 pgsql pgsql 4096 Apr 2 10:00 data_business
drwx——. 2 pgsql pgsql 4096 Apr 2 10:00 index_business
drwx——. 2 pgsql pgsql 4096 Apr 2 10:00 log_business
drwx——. 2 pgsql pgsql 4096 Apr 2 10:00 temp_business

3.1.2 创建表空间

# 以超级用户登录
$ psql -U pgsql

# 创建数据文件表空间
postgres=# CREATE TABLESPACE data_business
postgres-# OWNER pgsql postgres-# LOCATION ‘/postgresql/fgedutbss/data_business’;

CREATE TABLESPACE

# 创建索引表空间
postgres=# CREATE TABLESPACE index_business
postgres-# OWNER pgsql postgres-# LOCATION ‘/postgresql/fgedutbss/index_business’;

CREATE TABLESPACE

# 创建临时表空间
postgres=# CREATE TABLESPACE temp_business
postgres-# OWNER pgsql postgres-# LOCATION ‘/postgresql/fgedutbss/temp_business’;

CREATE TABLESPACE

# 查看表空间
postgres=# \db

List of fgedutbss
Name | Owner | Location
—————–+———-+———————————-
backup_business | pgsql | /postgresql/fgedutbss/backup_business
data_business | pgsql | /postgresql/fgedutbss/data_business
index_business | pgsql | /postgresql/fgedutbss/index_business
pg_default | pgsql |
pg_global | pgsql |
temp_business | pgsql | /postgresql/fgedutbss/temp_business
(6 rows)

3.2 PostgreSQL表空间管理

3.2.1 修改表空间

# 修改表空间所有者
postgres=# ALTER TABLESPACE data_business OWNER TO postgres;

ALTER TABLESPACE

# 修改临时表空间参数
postgres=# ALTER SYSTEM SET temp_fgedutbss = ‘temp_business’;

ALTER SYSTEM

# 重新加载配置
postgres=# SELECT pg_reload_conf();

pg_reload_conf
—————-
t
(1 row)

# 查看临时表空间配置
postgres=# SHOW temp_fgedutbss;

temp_fgedutbss
——————
temp_business
(1 row)

3.2.2 监控表空间使用情况

# 查看表空间大小
postgres=# SELECT
postgres-# spcname AS fgedutbs_name,
postgres-# pg_size_pretty(pg_fgedutbs_size(spcname)) AS size
postgres-# FROM pg_fgedutbs;

fgedutbs_name | size
—————–+———
pg_default | 8192 kB
pg_global | 4096 kB
data_business | 0 bytes
index_business | 0 bytes
temp_business | 0 bytes
(5 rows)

# 查看表空间使用情况（详细）
postgres=# SELECT
postgres-# schemaname,
postgres-# tablename,
postgres-# fgedutbs,
postgres-# pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
postgres-# pg_size_pretty(pg_table_size(c.oid)) AS table_size,
postgres-# pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
postgres-# FROM pg_class c
postgres-# JOIN pg_namespace n ON n.oid = c.relnamespace
postgres-# WHERE c.relkind = ‘r’ AND n.nspname NOT IN (‘pg_catalog’, ‘information_schema’)
postgres-# ORDER BY pg_total_relation_size(c.oid) DESC;

schemaname | tablename | fgedutbs | total_size | table_size | index_size
————+———–+————+————+————+————
(0 rows)

3.3 存储优化实施

3.3.1 在指定表空间创建表和索引

# 切换到业务数据库
postgres=# \c fgedu_business

You are now connected to fgedudb “fgedu_business” as fgedu “postgres”.

# 在数据文件表空间创建表
fgedu_business=# CREATE TABLE fgedu_orders (
fgedu_business(# id SERIAL PRIMARY KEY,
fgedu_business(# customer_id INTEGER NOT NULL,
fgedu_business(# order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
fgedu_business(# total_amount DECIMAL(10,2) NOT NULL,
fgedu_business(# status VARCHAR(50) NOT NULL
fgedu_business(# ) TABLESPACE data_business;

CREATE TABLE fgedu_# 在索引表空间创建索引
fgedu_business=# CREATE INDEX idx_fgedu_orders_customer_id
fgedu_business-# ON fgedu_orders(customer_id)
fgedu_business-# TABLESPACE index_business;

CREATE INDEX

# 查看表和索引的表空间
fgedu_business=# \d+ fgedu_orders

Table “public.fgedu_orders”
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
————-+—————————–+———–+———-+————————————+———-+————–+————-
id | integer | | not null | nextval(‘fgedu_orders_id_seq’::regclass) | plain | |
customer_id | integer | | not null | | plain | |
order_date | timestamp without time zone | | | CURRENT_TIMESTAMP | plain | |
total_amount | decimal(10,2) | | not null | | plain | |
status | character varying(50) | | not null | | extended | |
Indexes:
“fgedu_orders_pkey” PRIMARY KEY, btree (id) TABLESPACE index_business
“idx_fgedu_orders_customer_id” btree (customer_id) TABLESPACE index_business

# 插入测试数据
fgedu_business=# INSERT INTO fgedu_orders (customer_id, total_amount, status) VALUES
fgedu_business-# (1, 100.50, ‘completed’),
fgedu_business-# (2, 200.75, ‘pending’),
fgedu_business-# (3, 50.25, ‘completed’),
fgedu_business-# (1, 150.00, ‘completed’),
fgedu_business-# (2, 75.50, ‘pending’);

INSERT 0 5

# 验证数据
fgedu_business=# SELECT * FROM fgedu_orders;

id | customer_id | order_date | total_amount | status
—-+————-+—————————-+————–+———–
1 | 1 | 2026-04-02 10:00:00.000000 | 100.50 | completed
2 | 2 | 2026-04-02 10:00:00.000000 | 200.75 | pending
3 | 3 | 2026-04-02 10:00:00.000000 | 50.25 | completed
4 | 1 | 2026-04-02 10:00:00.000000 | 150.00 | completed
5 | 2 | 2026-04-02 10:00:00.000000 | 75.50 | pending
(5 rows)

3.3.2 表空间迁移

# 创建新的表空间
postgres=# CREATE TABLESPACE data_business_new
postgres-# OWNER pgsql postgres-# LOCATION ‘/postgresql/fgedutbss/data_business_new’;

CREATE TABLESPACE

# 移动表到新表空间
fgedu_business=# ALTER TABLE fgedu_orders SET TABLESPACE data_business_new;

ALTER TABLE

# 移动索引到新表空间
fgedu_business=# ALTER INDEX idx_fgedu_orders_customer_id SET TABLESPACE index_business;

ALTER INDEX

# 查看表和索引的表空间
fgedu_business=# \d+ fgedu_orders

Table “public.fgedu_orders”
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
————-+—————————–+———–+———-+————————————+———-+————–+————-
id | integer | | not null | nextval(‘fgedu_orders_id_seq’::regclass) | plain | |
customer_id | integer | | not null | | plain | |
order_date | timestamp without time zone | | | CURRENT_TIMESTAMP | plain | |
total_amount | decimal(10,2) | | not null | | plain | |
status | character varying(50) | | not null | | extended | |
Indexes:
“fgedu_orders_pkey” PRIMARY KEY, btree (id) TABLESPACE index_business
“idx_fgedu_orders_customer_id” btree (customer_id) TABLESPACE index_business
Table space: “data_business_new”

风哥提示：表空间迁移操作会锁定表，在生产环境中应选择业务低峰期进行，并提前做好备份。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 高性能表空间配置案例

4.1.1 案例：为高频交易系统配置表空间

# 场景：高频交易系统，需要极高的I/O性能

# 1. 创建高速表空间（使用NVMe存储）
$ mkdir -p /nvme/fgedutbss/data_trade
$ mkdir -p /nvme/fgedutbss/index_trade
$ chown -R pgsql: pgsql /nvme/fgedutbss/

# 2. 创建表空间
postgres=# CREATE TABLESPACE data_trade
postgres-# OWNER pgsql postgres-# LOCATION ‘/nvme/fgedutbss/data_trade’;

CREATE TABLESPACE

postgres=# CREATE TABLESPACE index_trade
postgres-# OWNER pgsql postgres-# LOCATION ‘/nvme/fgedutbss/index_trade’;

CREATE TABLESPACE

# 3. 创建交易表
postgres=# CREATE TABLE fgedu_trades (
postgres(# id SERIAL PRIMARY KEY,
postgres(# trade_id VARCHAR(50) UNIQUE NOT NULL,
postgres(# symbol VARCHAR(10) NOT NULL,
postgres(# price DECIMAL(10,2) NOT NULL,
postgres(# quantity INTEGER NOT NULL,
postgres(# trade_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
postgres(# status VARCHAR(20) NOT NULL
postgres(# ) TABLESPACE data_trade;

CREATE TABLE fgedu_# 4. 创建高频访问索引
postgres=# CREATE INDEX idx_fgedu_trades_trade_time
postgres-# ON fgedu_trades(trade_time)
postgres-# TABLESPACE index_trade;

CREATE INDEX

postgres=# CREATE INDEX idx_fgedu_trades_symbol
postgres-# ON fgedu_trades(symbol)
postgres-# TABLESPACE index_trade;

CREATE INDEX

# 5. 优化存储参数
postgres=# ALTER TABLE fgedu_trades SET (fillfactor = 90);

ALTER TABLE

4.2 存储分层案例

4.2.1 案例：为电商系统配置存储分层

# 场景：电商系统，需要存储分层管理

# 1. 创建不同存储层级的表空间
# 热数据（SSD）
$ mkdir -p /ssd/fgedutbss/data_hot
$ mkdir -p /ssd/fgedutbss/index_hot

# 温数据（SAS）
$ mkdir -p /sas/fgedutbss/data_warm

# 冷数据（HDD）
$ mkdir -p /hdd/fgedutbss/data_cold

$ chown -R pgsql: pgsql /ssd/fgedutbss/
$ chown -R pgsql: pgsql /sas/fgedutbss/
$ chown -R pgsql: pgsql /hdd/fgedutbss/

# 2. 创建表空间
postgres=# CREATE TABLESPACE data_hot LOCATION ‘/ssd/fgedutbss/data_hot’;
postgres=# CREATE TABLESPACE index_hot LOCATION ‘/ssd/fgedutbss/index_hot’;
postgres=# CREATE TABLESPACE data_warm LOCATION ‘/sas/fgedutbss/data_warm’;
postgres=# CREATE TABLESPACE data_cold LOCATION ‘/hdd/fgedutbss/data_cold’;

# 3. 创建表并分配到不同表空间
# 热数据：订单表（频繁访问）
postgres=# CREATE TABLE fgedu_orders (
postgres(# id SERIAL PRIMARY KEY,
postgres(# customer_id INTEGER NOT NULL,
postgres(# order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
postgres(# total_amount DECIMAL(10,2) NOT NULL,
postgres(# status VARCHAR(50) NOT NULL
postgres(# ) TABLESPACE data_hot;

# 热数据索引
postgres=# CREATE INDEX idx_fgedu_orders_customer_id
postgres-# ON fgedu_orders(customer_id)
postgres-# TABLESPACE index_hot;

# 温数据：用户表（中等访问频率）
postgres=# CREATE TABLE fgedu_fgedus (
postgres(# id SERIAL PRIMARY KEY,
postgres(# name VARCHAR(100) NOT NULL,
postgres(# email VARCHAR(255) UNIQUE NOT NULL,
postgres(# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
postgres(# ) TABLESPACE data_warm;

# 冷数据：历史订单表（低访问频率）
postgres=# CREATE TABLE fgedu_orders_history (
postgres(# LIKE fgedu_orders INCLUDING ALL
postgres(# ) TABLESPACE data_cold;

4.3 表空间迁移案例

4.3.1 案例：将历史数据迁移到低成本存储

# 场景：将3年前的历史数据迁移到低成本HDD存储

# 1. 创建冷数据存储表空间
$ mkdir -p /hdd/fgedutbss/data_history
$ chown -R pgsql: pgsql /hdd/fgedutbss/

postgres=# CREATE TABLESPACE data_history LOCATION ‘/hdd/fgedutbss/data_history’;

# 2. 创建历史数据表
postgres=# CREATE TABLE fgedu_orders_history (
postgres(# LIKE fgedu_orders INCLUDING ALL
postgres(# ) TABLESPACE data_history;

# 3. 迁移3年前的数据
postgres=# INSERT INTO fgedu_orders_history
postgres-# SELECT * FROM fgedu_orders
postgres-# WHERE order_date < NOW() - INTERVAL '3 years'; # 4. 验证迁移 postgres=# SELECT COUNT(*) FROM fgedu_orders_history; count ------- 10000 (1 row) # 5. 删除原表中的历史数据 postgres=# DELETE FROM fgedu_orders postgres-# WHERE order_date < NOW() - INTERVAL '3 years'; # 6. vacuum原表 postgres=# VACUUM ANALYZE fgedu_orders;

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在进行表空间迁移时，应确保数据一致性和业务连续性，建议在业务低峰期进行，并提前做好备份。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL表空间管理最佳实践

PostgreSQL表空间管理最佳实践：

  • 合理规划表空间：
    • 根据数据类型和访问模式选择合适的表空间
    • 将表和索引存储在不同的表空间
    • 使用存储分层策略，提高存储效率
  • 存储设备选择：
    • 频繁访问的数据使用高速存储（SSD/NVMe）
    • 大容量、低访问频率的数据使用低成本存储（HDD）
    • 根据业务需求选择合适的RAID级别
  • 性能优化：
    • 分散I/O负载，避免I/O竞争
    • 优化表空间布局，提高访问效率
    • 定期监控表空间使用情况
  • 管理维护：
    • 定期备份表空间
    • 监控表空间使用情况，及时扩容
    • 定期进行表空间碎片整理

5.2 PostgreSQL表空间常见问题

PostgreSQL表空间常见问题及解决方案：

  • 表空间满：监控表空间使用情况，及时扩容或清理数据
  • 权限问题：确保表空间目录权限正确（postgres用户可访问）
  • 性能下降：检查存储设备性能，优化表空间布局
  • 迁移失败：确保目标表空间存在且权限正确，避免在业务高峰期迁移
  • 备份问题：针对不同表空间制定不同的备份策略

5.3 PostgreSQL表空间管理工具推荐

PostgreSQL表空间管理常用工具：

from oracle:www.itpux.com

  • psql：命令行工具，用于管理表空间
  • pgAdmin4：图形化管理工具，提供直观的表空间管理界面
  • PostgreSQL Control Center：企业级管理工具
  • 自定义监控脚本：用于监控表空间使用情况
  • 存储监控工具：监控存储设备性能和使用情况
风哥提示：PostgreSQL表空间管理是数据库性能优化的重要组成部分，需要根据业务需求和数据特性进行合理规划和配置。定期监控和维护表空间，确保系统的稳定运行。from PostgreSQL:www.itpux.com

持续改进：表空间管理是一个持续的过程，需要根据业务发展和数据增长不断调整和优化。建议建立定期审查机制，持续改进表空间管理策略。

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

相关推荐

联系我们

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

微信号：itpux-com

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