PHP小码农

Appearance

表空间创建与使用（Creating and Using Tablespaces） ​

分区表把「数据淘汰」变成了「元数据操作」，而表空间解决的是另一个维度的运维问题：这些数据文件到底落在哪块盘上。当一个集群里同时存在「每天被扫几十万次的热分区」与「只读备查的历史分区」时，把两者放在同一块磁盘上，等于让冷数据白白占用热数据最宝贵的缓存与带宽。

本页从零开始讲清表空间的创建、授权、在分区表上的实际用法，以及如何安全地下线一个不再需要的表空间。所有命令与前置条件均以 PostgreSQL 18 为准。

前置知识

阅读本节前，建议先了解：Tablespace（表空间原理）、分区表最佳实践、创建与附加分区

基础概念 ​

表空间是「目录别名」，不是存储配额 ​

表空间（Tablespace） 是 PostgreSQL 里对一个文件系统目录的命名引用。它不复制数据、不做压缩、不设容量上限，只做一件事：告诉数据库「这个对象的文件应该写在哪个路径下」。

必须先纠正一个来自 Oracle 的直觉：PostgreSQL 的表空间不是「逻辑存储容器」，它没有 segment、没有 extent、没有 quota。它是纯粹的物理路径重定向。

text
用户视角：            CREATE TABLE orders PARTITION OF ... TABLESPACE ts_hot;
系统实际做的事：      把 orders 的数据文件从
                        $PGDATA/base/16384/16385
                     改成
                        /data/pg/tblspc_hot/PG_18_202509251/16384/16390

页格式不变、WAL 不变、MVCC 不变。变的只有 inode 所在的目录。

三个内置表空间与自定义表空间 ​

集群初始化后，pg_tablespace 里一定有三条记录，其中两条是内置的：

表空间类型pg_tablespace_location() 返回值实际路径用途
pg_default内置空字符串$PGDATA/base默认表空间，普通对象都落这里
pg_global内置空字符串$PGDATA/global共享系统目录专用，不可用于用户对象
自定义（如 ts_hot）用户创建真实绝对路径/data/pg/tblspc_hot/PG_18_*/由用户决定放什么

内置表空间的 location 是空字符串——这是一个常见困惑点。它们没有独立的挂载点，就是 PGDATA 里的两个子目录；只有用户创建的表空间才会在 $PGDATA/pg_tblspc/ 下生成软链接。

创建表空间后，文件系统长什么样 ​

text
$PGDATA/
├── base/                          ← pg_default 的物理位置
│   └── 16384/                     ← 数据库 OID
│       └── 16385                  ← 表的 relfilenode
├── global/                        ← pg_global 的物理位置
└── pg_tblspc/                     ← 所有自定义表空间的软链接集中地
    └── 16405 -> /data/pg/tblspc_hot
                                  ↑ 软链接名 = 表空间 OID

/data/pg/tblspc_hot/               ← 真实挂载点（你提供的 LOCATION）
└── PG_18_202509251/               ← 版本子目录，由 PostgreSQL 自动创建
    └── 16384/                     ← 数据库 OID
        └── 16390                  ← 表的 relfilenode

注意两点：

  1. LOCATION 只到挂载点为止，PG_18_YYYYMMDD/ 这一层是 PostgreSQL 自己建的，你不要手动创建，也不要在里面手动放文件。
  2. 软链接名是 OID 而不是名字。这解释了为什么 ALTER TABLESPACE ... RENAME 是纯元数据操作——改名不会动磁盘上的任何东西。

目录必须先存在，且必须由 PostgreSQL 操作系统用户拥有

CREATE TABLESPACE 不会创建目录。官方原文：The directory must exist (CREATE TABLESPACE will not create it), should be empty, and must be owned by the PostgreSQL system user. The directory must be specified by an absolute path name.

也就是说 mkdir、chown、chmod 这三件事必须在数据库外面先做完，否则命令一定失败。

表空间与对象的关系是「各自独立」的 ​

这是分区表场景里最容易踩的坑：同一张表的表数据、索引、TOAST 表可以分别落在不同的表空间里，而且它们互不跟随。

text
orders 分区表
├── orders_2026_09            (表数据)  → ts_hot
├── orders_pkey               (父索引)  → 无存储，仅元数据
├── orders_2026_09_pkey       (子索引)  → ts_hot_idx      ← 不随表走！
└── pg_toast_16390            (TOAST 表) → 跟随主表迁移  ← 这个会跟随
对象显式指定方式迁移已有对象
表 / 物化视图CREATE TABLE ... TABLESPACEALTER TABLE ... SET TABLESPACE
索引CREATE INDEX ... TABLESPACEALTER INDEX ... SET TABLESPACE
TOAST 表无法直接指定随主表迁移，无独立命令
数据库CREATE DATABASE ... TABLESPACE无（需重建或 ALTER DATABASE 无效）

ALTER TABLE 不会自动迁移索引。这一点在分区表上后果更大，因为索引可能分布在几十个分区上。

语法与用法 ​

创建表空间 ​

text
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

其中 tablespace_option 目前只有四个可选参数：

text
seq_page_cost = value
random_page_cost = value
effective_io_concurrency = value
maintenance_io_concurrency = value

修改表空间 ​

text
ALTER TABLESPACE name RENAME TO new_name

ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )

ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )

删除表空间 ​

text
DROP TABLESPACE [ IF EXISTS ] name

一个完整的最小示例 ​

LOCATION 指向的目录必须先存在且为空（CREATE TABLESPACE 不会创建它）：

bash
sudo mkdir -p /data/pg/tblspc_hot
sudo chown postgres:postgres /data/pg/tblspc_hot
sudo chmod 700 /data/pg/tblspc_hot
ls -A /data/pg/tblspc_hot            # 应无任何输出
sql
CREATE TABLESPACE ts_hot LOCATION '/data/pg/tblspc_hot';
text
postgres=# \db
             List of tablespaces
    Name    |  Owner   |          Location
------------+----------+----------------------------
 pg_default | postgres |
 pg_global  | postgres |
 ts_hot     | postgres | /data/pg/tblspc_hot
(3 rows)

内置表空间的 Location 显示为空白，与 pg_tablespace_location() 返回空字符串一致。

详细说明 ​

建表空间的前置条件与事务限制 ​

CREATE TABLESPACE 有三条硬性前提，缺一条都会失败：

sql
-- ① 目录不存在 → 报错
CREATE TABLESPACE ts_bad LOCATION '/data/pg/not_created_yet';
-- ② 目录属主不是 postgres → 报错
CREATE TABLESPACE ts_bad LOCATION '/tmp/root_only';
text
ERROR:  directory "/data/pg/not_created_yet" does not exist
ERROR:  could not set permissions on directory "/tmp/root_only": Operation not permitted

更隐蔽的是事务限制。官方 Notes 明确写道：CREATE TABLESPACE cannot be executed inside a transaction block. 它涉及在 pg_tblspc/ 下建立软链接并在文件系统上做检查，无法被安全回滚；DROP TABLESPACE 有完全一样的限制。

sql
-- 错误！在事务块内执行会被拒绝
BEGIN;
CREATE TABLESPACE ts_hot LOCATION '/data/pg/tblspc_hot';
COMMIT;
text
ERROR:  CREATE TABLESPACE cannot run inside a transaction block
bash
# 正确做法：用单条命令执行，psql 默认自动提交
psql -c "CREATE TABLESPACE ts_hot LOCATION '/data/pg/tblspc_hot'"

表空间命名、属主与权限 ​

表空间名有两条规则：不能与集群中已有表空间重名；不能以 pg_ 开头（该前缀保留给系统表空间）。

sql
-- 错误！pg_ 前缀被保留
CREATE TABLESPACE pg_mine LOCATION '/data/pg/tblspc_mine';
text
ERROR:  unacceptable tablespace name "pg_mine"
DETAIL:  The prefix "pg_" is reserved for system tablespaces.

创建表空间只能是超级用户，但可以把属主交给普通用户，或保留所有权只授予 CREATE 权限——后者通常是更好的选择，因为改属主会让该用户获得 ALTER/DROP 表空间的完整权力：

sql
-- 创建时直接指定属主，或事后改属主
CREATE TABLESPACE ts_hot OWNER app_owner LOCATION '/data/pg/tblspc_hot';
ALTER TABLESPACE ts_hot OWNER TO app_owner;

-- 推荐：所有权留在 DBA 手里，只把「能用」的权限发出去（可 REVOKE 回收）
GRANT CREATE ON TABLESPACE ts_hot TO app_writer;

权限生效后的效果是：app_writer 可以写 ... TABLESPACE ts_hot，但不能执行 ALTER TABLESPACE 或 DROP TABLESPACE。

sql
-- 验证权限是否已授予
SELECT spcname, spcacl FROM pg_tablespace WHERE spcname = 'ts_hot';
text
 spcname |          spcacl
---------+---------------------------
 ts_hot  | {postgres=UC/postgres,app_writer=C/postgres}

表空间级代价参数（PostgreSQL 15+） ​

如果一块盘明显比另一块快（比如 SSD 与 HDD 混用），规划器默认的 random_page_cost = 4 会让它在 SSD 上严重低估索引扫描的性价比。表空间级参数就是用来修正这一点的。

官方文档：Setting these values for a particular tablespace will override the planner's usual estimate of the cost of reading pages from tables in that tablespace.

参数默认值含义
seq_page_cost1.0顺序读一页的相对代价
random_page_cost4.0随机读一页的相对代价
effective_io_concurrencyPG 18 起为 16同时可发起的并发 I/O 数
maintenance_io_concurrencyPG 18 起为 16维护操作（VACUUM、建索引）的并发 I/O 数
sql
-- 建表空间时直接带上代价参数：这块盘是 SSD；也可事后 SET / RESET
CREATE TABLESPACE ts_ssd
    LOCATION '/data/pg/tblspc_ssd'
    WITH (random_page_cost = 1.1, effective_io_concurrency = 200);

ALTER TABLESPACE ts_ssd SET (random_page_cost = 1.1);
ALTER TABLESPACE ts_ssd RESET (random_page_cost);
sql
-- 查看某个表空间上的参数覆盖
SELECT spcname, spcoptions FROM pg_tablespace WHERE spcname = 'ts_ssd';
text
 spcname |                     spcoptions
---------+-----------------------------------------------------
 ts_ssd  | {random_page_cost=1.1,effective_io_concurrency=200}

表空间级参数是 PostgreSQL 15 才引入的

在 PostgreSQL 14 及更早版本上，CREATE TABLESPACE ... WITH (...) 与 ALTER TABLESPACE ... SET (...) 都会直接报语法错误。如果你的集群版本低于 15，只能改全局的 random_page_cost。

建表、建索引时指定表空间 ​

最基础的用法就是在 DDL 里带 TABLESPACE 子句——表数据、索引、唯一约束与主键的索引都要各自指定：

sql
-- 表数据放热盘
CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    email      text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
) TABLESPACE ts_hot;

-- 索引放另一块盘（比如更快的 NVMe）
CREATE INDEX users_email_idx ON users (email) TABLESPACE ts_hot_idx;

-- 唯一约束/主键背后的索引
ALTER TABLE users
    ADD CONSTRAINT users_email_key UNIQUE (email) USING INDEX TABLESPACE ts_hot_idx;

在分区表上，TABLESPACE 可以同时在父表和子分区上指定：

sql
-- 父表指定表空间：它自己不存数据，但会成为「未来分区的默认表空间」
CREATE TABLE orders (
    id         bigint NOT NULL,
    created_at timestamptz NOT NULL,
    amount     numeric(12,2)
) PARTITION BY RANGE (created_at)
  TABLESPACE ts_hot;

-- 单个分区显式指定表空间（覆盖父表设置）
CREATE TABLE orders_2026_09 PARTITION OF orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')
    TABLESPACE ts_hot;
sql
-- 验证：分区表本身也有 reltablespace 记录
SELECT c.relname, c.relkind, t.spcname AS tablespace
FROM pg_class c
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relname IN ('orders', 'orders_2026_09');
text
    relname     | relkind | tablespace
----------------+---------+------------
 orders         | p       | ts_hot
 orders_2026_09 | r       | ts_hot
(2 rows)

default_tablespace 与 temp_tablespaces ​

不想在每个 DDL 里手写 TABLESPACE，可以用会话级参数：

sql
-- 本会话内未显式指定表空间的表/索引，都建到 ts_hot
SET default_tablespace = ts_hot;
CREATE TABLE metrics (id bigint, val numeric);   -- 自动落到 ts_hot
text
SET
CREATE TABLE
行为说明
空字符串（默认）使用当前数据库的默认表空间
名字不存在不报错，回落到当前数据库的默认表空间
无 CREATE 权限建表时报错
临时表 / 建库不适用，分别走 temp_tablespaces 与模板库
建分区表若为非空值，该表空间会成为此分区表未来分区的默认表空间（即使之后 default_tablespace 又改了）

最后一行来自官方原文：If this parameter is set to a value other than the empty string when a partitioned table is created, the partitioned table's tablespace will be set to that value, which will be used as the default tablespace for partitions created in the future, even if default_tablespace has changed since then.

临时对象（临时表、临时索引、排序溢出文件）走的是另一条路径：

sql
SET temp_tablespaces = ts_tmp;
-- 也可以给多个：每次建临时对象时随机挑一个
SET temp_tablespaces = 'ts_tmp1, ts_tmp2, ts_tmp3';
SHOW temp_tablespaces;
text
 temp_tablespaces
------------------
 ts_tmp
(1 row)

temp_tablespaces 的容错行为与 default_tablespace 不同：交互式设置时指定不存在的表空间会报错，但使用 postgresql.conf 里预先设好的值时，不存在的项会被静默忽略。

已有对象的迁移：ALTER TABLE / INDEX ... SET TABLESPACE ​

已经存在的表要换盘，用 ALTER TABLE：

sql
ALTER TABLE users SET TABLESPACE ts_cold;

这是一个重写操作：PostgreSQL 把整张表的文件逐块复制到新路径，期间持有 ACCESS EXCLUSIVE 锁，并产生等量的 WAL。表越大，停机越久。索引不会跟随，必须单独迁移；批量处理时用生成脚本的方式：

sql
-- 一次性找出某张表及其所有索引，生成迁移脚本
SELECT format('ALTER INDEX %I SET TABLESPACE ts_cold_idx;', c.relname) AS stmt
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'users'::regclass
  AND c.reltablespace <> (SELECT oid FROM pg_tablespace WHERE spcname = 'ts_cold_idx')
ORDER BY c.relname;

TOAST 表会跟随主表迁移，不需要也不能单独处理：

sql
-- 验证 TOAST 表是否已跟随（reltoastrelid → 该 TOAST 表的 reltablespace）
SELECT t.spcname AS toast_tablespace
FROM pg_class c
JOIN pg_class tc ON tc.oid = c.reltoastrelid
LEFT JOIN pg_tablespace t ON t.oid = tc.reltablespace
WHERE c.relname = 'users';
text
 toast_tablespace
------------------
 ts_cold
(1 row)

分区表与表空间：父表上的 SET TABLESPACE「什么都不搬」 ​

这是分区表 + 表空间最容易误解的一处。对分区表（partitioned table）执行：

sql
ALTER TABLE orders SET TABLESPACE ts_cold;

已经存在的分区不会被移动。 分区表的父表本身没有存储（relfilenode = 0），所以「搬文件」这件事无从谈起。这条命令真正的作用是修改分区表的默认表空间，从而影响此后新建的分区：

text
ALTER TABLE orders SET TABLESPACE ts_cold;

  ✗ orders_2026_01 ~ orders_2026_09  原地不动，仍在 ts_hot
  ✓ 之后 CREATE TABLE ... PARTITION OF orders  → 默认落到 ts_cold

因此分区的冷热分层必须逐个分区执行，并批量生成脚本：

sql
-- 生成迁移脚本（按月分区，迁移 2026 年 1 月之前的分区）
SELECT format('ALTER TABLE %I SET TABLESPACE ts_cold;', c.relname) AS stmt
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'orders'::regclass
  AND c.relname < 'orders_2026_01'
ORDER BY c.relname;
text
            stmt
-----------------------------
 ALTER TABLE orders_2025_01 SET TABLESPACE ts_cold;
 ALTER TABLE orders_2025_02 SET TABLESPACE ts_cold;
 ...

别忘了这些分区上的子索引——它们同样不跟随，需要按分区逐个（或用脚本批量）迁移：

sql
-- 把所有「非默认表空间的索引」拉出来核对
SELECT n.nspname AS schema_name, c.relname AS index_name,
       t.spcname AS tablespace, pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'i'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 10;

批量搬迁：ALTER ... ALL IN TABLESPACE ​

当整个表空间要被淘汰时，逐个对象迁移不现实。PostgreSQL 提供了按表空间批量搬迁的语法：

text
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ...] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ...] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
sql
-- 把 ts_old 里的所有表搬到 ts_new；索引要单独来一次
ALTER TABLE ALL IN TABLESPACE ts_old SET TABLESPACE ts_new;
ALTER INDEX ALL IN TABLESPACE ts_old SET TABLESPACE ts_new;

-- 也可以只搬属于某个角色（及其成员）的表
ALTER TABLE ALL IN TABLESPACE ts_old OWNED BY app_owner SET TABLESPACE ts_new;

NOWAIT 的语义是：遇到任何需要等待的锁就立即失败，而不是排队等待，适合「宁可失败也不要长时间阻塞」的运维窗口。注意这两条命令都不会递归进入分区内部——分区是独立的表，会被「ALL IN TABLESPACE」覆盖到，但只对某个父表执行 SET TABLESPACE 时分区不会跟着走（见上一节）。

sql
-- 快速失败模式：拿不到锁就报错，方便重试
ALTER TABLE ALL IN TABLESPACE ts_old SET TABLESPACE ts_new NOWAIT;
text
ERROR:  timed out waiting for lock held by another transaction

巡检：\db、系统目录与路径核对 ​

表空间的日常巡检分三层：表空间本身 → 表空间里的对象 → 磁盘上的真实路径。

sql
-- 第一层：列出所有表空间及其大小
SELECT spcname, pg_get_userbyid(spcowner) AS owner,
       pg_tablespace_location(oid) AS location,
       pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace
ORDER BY pg_tablespace_size(oid) DESC;
text
  spcname   |  owner   |     location      |  size
------------+----------+-------------------+---------
 pg_default | postgres |                   | 42 GB
 ts_hot     | postgres | /data/pg/tblspc_hot | 118 GB
 ts_cold    | postgres | /data/pg/tblspc_cold| 903 GB
 pg_global  | postgres |                   | 8 MB
(4 rows)
sql
-- 第二层：找出某张表的物理路径，并核对它落在哪个表空间
SELECT c.relname, t.spcname AS tablespace,
       pg_relation_filepath(c.oid) AS relative_path,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relname IN ('users', 'orders');
text
 relname | tablespace |         relative_path         | total_size
---------+------------+-------------------------------+------------
 users   | ts_cold    | pg_tblspc/16417/16384/16385   | 903 GB
 orders  | ts_hot     |                               | 0 bytes
(2 rows)

orders 是分区表，relative_path 为空、size 为 0——这与「分区表没有自己的存储」完全吻合。

bash
# 第三层：怀疑元数据与磁盘不一致时，直接从文件系统核对
ls -l $PGDATA/pg_tblspc/                       # 软链接指向哪里
du -sh /data/pg/tblspc_hot/PG_18_*/ 2>/dev/null # 真实目录里的版本子目录
text
lrwxrwxrwx 1 postgres postgres 20 Sep 25 10:12 16417 -> /data/pg/tblspc_cold
lrwxrwxrwx 1 postgres postgres 19 Sep 25 10:12 16405 -> /data/pg/tblspc_hot
118G    /data/pg/tblspc_hot/PG_18_202509251/

安全下线一个表空间 ​

DROP TABLESPACE 的限制比 CREATE 还多，值得逐条记住。官方原文：

A tablespace can only be dropped by its owner or a superuser. The tablespace must be empty of all database objects before it can be dropped. It is possible that objects in other databases might still reside in the tablespace even if no objects in the current database are using the tablespace. Also, if the tablespace is listed in the temp_tablespaces setting of any active session, the DROP might fail due to temporary files residing in the tablespace.

条件违反时的现象
必须是属主或超级用户权限错误
表空间里没有任何对象ERROR: tablespace "ts_x" is not empty
所有数据库里都没有对象（不只是当前库）同上，且更隐蔽
没有活动会话的 temp_tablespaces 指向它可能因临时文件而失败
不能在事务块内执行ERROR: DROP TABLESPACE cannot run inside a transaction block

判空有一个专用函数——它返回「在该表空间中存有对象的数据库 OID 集合」，只要返回任何一行，就说明非空：

sql
-- 只要有任何输出，就说明仍有对象挂在这个表空间上
SELECT pg_tablespace_databases(oid) AS db_oid,
       pg_tablespace_databases(oid)::regdatabase AS db_name
FROM pg_tablespace
WHERE spcname = 'ts_old';
text
 db_oid | db_name
--------+---------
  16384 | appdb
(1 row)
sql
-- 错误！表空间里还有表
DROP TABLESPACE ts_old;
text
ERROR:  tablespace "ts_old" is not empty
sql
-- 正确序列：先清空对象（不能在事务块内执行 DROP）
ALTER TABLE ALL IN TABLESPACE ts_old SET TABLESPACE ts_new;
ALTER INDEX ALL IN TABLESPACE ts_old SET TABLESPACE ts_new;
bash
# 最后删掉（单条命令、自动提交）
psql -c "DROP TABLESPACE ts_old"

删除表空间只删软链接，不删目录

DROP TABLESPACE 成功后，$PGDATA/pg_tblspc/<oid> 软链接会消失，pg_tablespace 里的记录也会消失，但/data/pg/tblspc_old 这个目录以及里面的 PG_18_*/ 版本目录仍然留在磁盘上。这是有意设计的：PostgreSQL 不替你删数据目录，避免误删。清理需要你自己用 rm -rf 完成——并且一定要在确认表空间已真正为空、且没有备份/恢复需求之后。

实战示例 ​

下面走一遍完整流程：把一张大表从默认表空间迁到 SSD，再把它的索引迁到另一块盘，最后验证并清理。

第 1 步：在磁盘上准备目录 ​

bash
sudo mkdir -p /data/ssd/pg_tblspc
sudo chown postgres:postgres /data/ssd/pg_tblspc
sudo chmod 700 /data/ssd/pg_tblspc

# 确认目录存在、为空、属主正确
ls -ld /data/ssd/pg_tblspc
ls -A /data/ssd/pg_tblspc
text
drwx------ 2 postgres postgres 4096 Sep 25 10:10 /data/ssd/pg_tblspc
（第二行无输出，说明目录为空）

第 2 步：创建表空间 ​

sql
-- 注意：不能在事务块内执行
CREATE TABLESPACE ts_ssd
    LOCATION '/data/ssd/pg_tblspc'
    WITH (random_page_cost = 1.1, effective_io_concurrency = 200);
text
CREATE TABLESPACE

第 3 步：授予业务用户使用权限 ​

sql
GRANT CREATE ON TABLESPACE ts_ssd TO app_writer;
SELECT spcname, spcacl FROM pg_tablespace WHERE spcname = 'ts_ssd';
text
 spcname |                spcacl
---------+---------------------------------------
 ts_ssd  | {postgres=UC/postgres,app_writer=C/postgres}
(1 row)

第 4 步：迁移大表及其索引 ​

SET TABLESPACE 是重写操作，先估算代价与 WAL 量：

sql
SELECT pg_size_pretty(pg_total_relation_size('orders_flat')) AS table_size,
       current_setting('wal_compression') AS wal_compression;
text
 table_size | wal_compression
------------+-----------------
 42 GB      | off
(1 row)
sql
-- 持有 ACCESS EXCLUSIVE 锁，期间该表不可读写
ALTER TABLE orders_flat SET TABLESPACE ts_ssd;

-- 索引不会跟随，必须单独迁移
ALTER INDEX orders_flat_pkey SET TABLESPACE ts_ssd;
ALTER INDEX orders_flat_created_at_idx SET TABLESPACE ts_ssd;
text
ALTER TABLE
ALTER INDEX
ALTER INDEX

索引较多时用生成脚本的方式批量执行：

sql
SELECT format('ALTER INDEX %I.%I SET TABLESPACE ts_ssd;', n.nspname, c.relname) AS stmt
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indrelid = 'orders_flat'::regclass
ORDER BY c.relname;

第 5 步：验证迁移结果 ​

sql
SELECT c.relname, c.relkind,
       t.spcname AS tablespace,
       pg_relation_filepath(c.oid) AS relative_path
FROM pg_class c
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relname IN ('orders_flat', 'orders_flat_pkey',
                    'orders_flat_created_at_idx', 'pg_toast_16420')
ORDER BY c.relname;
text
         relname           | relkind | tablespace |         relative_path
---------------------------+---------+------------+--------------------------------
 orders_flat               | r       | ts_ssd     | pg_tblspc/16421/16384/16420
 orders_flat_created_at_idx| i       | ts_ssd     | pg_tblspc/16421/16384/16423
 orders_flat_pkey          | i       | ts_ssd     | pg_tblspc/16421/16384/16422
 pg_toast_16420            | t       | ts_ssd     | pg_tblspc/16421/16384/16424
(4 rows)

pg_toast_16420（TOAST 表）跟随主表自动迁移，而两条索引需要第 4 步的显式命令。最后从磁盘层面确认：

bash
du -sh /data/ssd/pg_tblspc/PG_18_*/
text
42G     /data/ssd/pg_tblspc/PG_18_202509251/

第 6 步：把临时文件引导走、下线旧表空间 ​

排序溢出、哈希溢出产生的临时文件默认落在数据库的默认表空间，可能挤占数据盘。同时把旧表空间排空并删除：

sql
-- 建一个专门放临时文件的表空间；全局配置写进 postgresql.conf 后重载生效
CREATE TABLESPACE ts_tmp LOCATION '/data/ssd/pg_tblspc_tmp';
ALTER SYSTEM SET temp_tablespaces = 'ts_tmp';
SELECT pg_reload_conf();

-- ① 判空：有任何输出就说明还不能删
SELECT pg_tablespace_databases(oid) AS db_oid
FROM pg_tablespace WHERE spcname = 'ts_old';

-- ② 若上面有输出，先把对象搬走（索引要单独来一次）
ALTER TABLE ALL IN TABLESPACE ts_old SET TABLESPACE ts_ssd;
ALTER INDEX ALL IN TABLESPACE ts_old SET TABLESPACE ts_ssd;

-- ③ 删除（不能在事务块内）
DROP TABLESPACE ts_old;
text
 db_oid
--------
(0 rows)
DROP TABLESPACE

确认软链接已消失、但目录仍需人工清理：

bash
ls -l $PGDATA/pg_tblspc/            # 原 ts_old 的软链接已不在
ls -ld /data/hdd/pg_tblspc_old      # 目录仍存在

确认数据已全部迁走、备份策略已覆盖之后，才手工删除旧目录：

bash
sudo rm -rf /data/hdd/pg_tblspc_old

注意事项 ​

  • 不要以为 CREATE TABLESPACE 会帮你建目录。 官方明确说明它不会创建目录，且目录应当为空，否则会报 ERROR: directory "..." does not exist。

  • 不要试图在事务块里建或删表空间。 两者都被明确禁止。

    sql
    -- 错误！
BEGIN;
CREATE TABLESPACE ts_hot LOCATION '/data/pg/tblspc_hot';
COMMIT;
    text
    ERROR:  CREATE TABLESPACE cannot run inside a transaction block

  • 不要给表空间起 pg_ 开头的名字。 会报 ERROR: unacceptable tablespace name "pg_mine"，pg_ 前缀保留给系统表空间。

  • 不要假设 ALTER TABLE ... SET TABLESPACE 会自动迁移索引。 索引必须逐条单独迁移，在分区表上更是几十条。

    sql
    -- 不推荐：以为表走了索引就跟着走了
ALTER TABLE users SET TABLESPACE ts_cold;
-- 别忘了：ALTER INDEX users_email_idx SET TABLESPACE ts_cold_idx;

  • 不要以为在分区表父表上 SET TABLESPACE 会搬数据。 它只修改未来的默认表空间，已有分区原地不动。

    sql
    -- 不推荐：以为历史分区都被搬走了
ALTER TABLE orders SET TABLESPACE ts_cold;

  • 不要把表空间当磁盘配额或租户隔离手段。 不存在 ALTER TABLESPACE ... SET (max_size = ...) 这类语句，它既没有配额，也没有访问隔离能力。

  • 不要在数据库运行期间手工调整 pg_tblspc/ 下的软链接。 官方原文：Under no circumstances perform this operation while the server is running.

    bash
    # 错误！
ln -sfn /data/new_path $PGDATA/pg_tblspc/16405

  • 不要把表空间放到临时文件系统（如 RAM disk）上。 官方警告：Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster.

  • 不要以为 DROP TABLESPACE 会删掉磁盘目录。 它只删软链接和目录表记录，数据目录需要人工清理。

    bash
    # 删除成功后目录仍然存在，必须手工评估后清理
ls -ld /data/hdd/pg_tblspc_old

  • 不要只检查当前数据库是否为空就删表空间。 其他数据库里的对象同样会导致失败，用 SELECT pg_tablespace_databases(oid) FROM pg_tablespace WHERE spcname = 'ts_old'; 跨库检查。

  • 不要把表空间单独备份或单独迁移到另一个集群。 表空间是集群的一部分，依赖主数据目录里的元数据，拷贝目录无法独立使用。

最佳实践 ​

  • 优先为「快慢差异明显的盘」建表空间，而不是为「每个盘」建一个。 官方提醒：同一个逻辑文件系统上建多个表空间通常没有意义。

    sql
    -- 推荐：SSD 热数据 / HDD 冷数据，两块盘两个表空间
CREATE TABLESPACE ts_hot LOCATION '/data/ssd/pg_tblspc';
CREATE TABLESPACE ts_cold LOCATION '/data/hdd/pg_tblspc';

  • 表空间的所有权留给 DBA，日常只授予 CREATE 权限。 这样业务用户能用，但不能 ALTER/DROP。

    sql
    GRANT CREATE ON TABLESPACE ts_hot TO app_writer;

  • 把「目录准备」写成幂等脚本，纳入部署流程。 mkdir、chown、chmod 三步一次都不能省。

    bash
    install -d -o postgres -g postgres -m 700 /data/ssd/pg_tblspc

  • 在 PostgreSQL 15+ 上，为快盘表空间显式设置 random_page_cost 与 effective_io_concurrency。 让规划器知道这块盘的真实速度。

    sql
    ALTER TABLESPACE ts_ssd SET (random_page_cost = 1.1, effective_io_concurrency = 200);

  • 建分区表时先想清「未来分区默认落哪块盘」，用父表的 TABLESPACE 一次性定好。

    sql
    CREATE TABLE orders (...) PARTITION BY RANGE (created_at) TABLESPACE ts_hot;

  • 冷热分层时，用「迁移脚本生成 SQL」代替手写 DDL。 分区与索引数量多，手写必漏。

    sql
    SELECT format('ALTER TABLE %I SET TABLESPACE ts_cold;', c.relname)
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'orders'::regclass AND c.relname < 'orders_2026_01';

  • 大表迁移安排在低峰期，并提前评估 WAL 生成量与磁盘余量。 SET TABLESPACE 是重写，会产生等量 WAL。

    sql
    SELECT pg_size_pretty(pg_total_relation_size('orders_flat')) AS to_rewrite,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')::bigint) AS approx_wal;

  • 临时文件单独给一个表空间，避免排序溢出挤占数据盘。

    sql
    ALTER SYSTEM SET temp_tablespaces = 'ts_tmp';

  • 下线表空间走固定四步：判空 → 批量搬迁 → 检查会话 → 删除并清理目录。

    sql
    SELECT pg_tablespace_databases(oid) FROM pg_tablespace WHERE spcname = 'ts_old';

  • 把表空间清单与容量纳入日常监控。 表空间写满会导致相关对象无法写入。

    sql
    SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace ORDER BY pg_tablespace_size(oid) DESC;

下一节

下一节：表空间与性能（Tablespace and Performance）——讲清表空间到底能不能提速、表空间级代价参数如何影响执行计划，以及冷热分层在分区表上的落地策略。

参考链接 ​