第一次意识到表空间不是“锦上添花”的功能是某次凌晨的磁盘告警。当时生产库里所有业务表和索引都堆在默认目录里根盘眼看就满另一块专门准备的SSD却空着。我用CREATE TABLESPACE建了一个指向SSD的表空间再用一条ALTER TABLE SET TABLESPACE把最热的几张表挪过去十几分钟后告警解除。从那以后我做任何PG集群规划都会把表空间作为第一件事处理。这篇文章就从实际使用出发把PG里和表空间相关的命令串一遍怎么建、怎么改、怎么删、怎么查以及在移动数据时那些文档不会明说的坑。1. 表空间在PG里的真实定位一条从对象到磁盘目录的映射链1.1 表空间不是分区而是一个命名目录引用很多人第一次听到“表空间”都会下意识拿分区去类比其实两者完全不是一个层次。表空间是一个集群级对象它在逻辑层只做一件事把一个用户可读的名字比如ts_ssd绑定到一个文件系统目录比如/data/pg_tblspc/ts_ssd。表、索引、物化视图、TOAST数据在创建时如果指定了这个表空间物理文件就会落到那个目录下。这个抽象最直接的意义在于数据库层面可以按对象粒度决定“落盘位置”而不是整库塞在一个目录里。日志大表放机械盘热索引放SSD临时文件放本地NVMe全都可以通过表空间实现。而且表空间是集群级对象不属于某个数据库多个库可以共享同一个表空间这一点在做统一资源池时特别有用。需要说明的是表空间不改变表的逻辑结构。表还是那张表分区还是那些分区索引还是在原表名下只是物理文件换了目录。它解决的是存储分布问题不是数据组织问题。想清楚这一点后面所有命令的行为就都能理解了。1.2 物理路径拆解从目录符号链接到真实文件表空间在实际磁盘上是通过符号链接挂载到数据目录里的。你到$PGDATA/pg_tblspc/下看一眼就能明白ls -l /var/lib/postgresql/16/main/pg_tblspc/输出通常长这样lrwxrwxrwx 1 postgres postgres 33 Jan 10 10:21 16417 - /data/pg_tblspc/ts_ssd左边这个数字16417就是表空间的OID右边是被指向的真实目录。也就是说PostgreSQL进程看到一个表空间OID就去pg_tblspc目录里找对应的符号链接然后进入链接指向的物理目录操作文件。再往里走一步表空间的真实文件路径一般是/data/pg_tblspc/ts_ssd/PG_16_202307071/5/16385这一段可以拆成四层来看表空间目录本身、目录版本标识、数据库OID、关系文件编号。中间那个PG_16_202307071是目录版本号和当前大版本强相关这样不同大版本的数据文件不会混在一起。我见过有人手动跑到pg_tblspc里找文件发现全是数字没有名字其实没必要手摸文件。想知道一个表空间指向哪儿用后面第5章的pg_tablespace_location函数更可靠。1.3 什么情况下你该考虑表空间表空间不是建来好看的下面这些场景再不搞就真的晚了根盘空间不足热数据需要搬到大容量磁盘上。同一张实例里既有频繁随机读的索引又有顺序写的日志表希望IO模型分离。临时文件和临时表经常把系统盘写满导致实例直接夯住。做容量规划和故障隔离希望每个表空间对应独立的挂载点方便监控和快照。备份恢复时希望不同重要级别的数据能走不同的备份策略。从命令数量看表空间相关命令比索引、约束命令少很多但每一条都牵涉数据落地位置操作前想清楚动机比你背语法更重要。2. 建、改、删表空间命令背后的权限边界与目录校验2.1 CREATE TABLESPACE最小可执行示例先做文件系统侧的准备工作。表空间指向的目录必须提前创建好而且要求非常严mkdir -p /data/pg_tblspc/ts_ssd chown postgres:postgres /data/pg_tblspc/ts_ssd chmod 700 /data/pg_tblspc/ts_ssd然后执行创建CREATE TABLESPACE ts_ssd OWNER app_user LOCATION /data/pg_tblspc/ts_ssd WITH (random_page_cost 1.1);这个语句有三段关键信息。OWNER指定表空间的所有者后续对表空间的ALTER、DROP默认由所有者操作。LOCATION必须是一个绝对路径而且指向的那个目录必须已经存在。WITH子句可以写表空间参数最常用的是IO成本因子后面单独说。创建完成后可以马上验证一下SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;如果ts_ssd出现在结果里且路径正确说明建成功了。2.2 权限边界哪些人能干哪些事表空间是集群级对象默认情况下只有超级用户能创建。实际运维中大多数公司也都是由DBA统一建好表空间再给业务用户授权使用。权限边界很简单记住这张表操作最低权限要求CREATE TABLESPACE默认仅超级用户在表空间中创建表/索引对目标表空间有CREATE权限且对所在数据库有CREATE权限ALTER TABLESPACE RENAME / OWNER表空间所有者或超级用户DROP TABLESPACE表空间所有者或超级用户给业务账号授权建表空间里的对象用GRANT CREATE ON TABLESPACE ts_ssd TO app_user;如果你执行CREATE TABLE ... TABLESPACE ts_ssd时报了permission denied for tablespace ts_ssd多半就是少了这一步授权。很多新手第一次接触表空间就是在这里卡住的。2.3 目录校验和“空目录”到底有多严CREATE TABLESPACE对目录的检查很死板但这些都是保护措施。官方要求是目录必须存在、必须为空、必须由运行PostgreSQL的操作系统用户拥有权限必须是0700。实际碰到最多的三类报错ERROR: directory /data/pg_tblspc/ts_ssd does not exist这个最简单先mkdir -p再执行创建。ERROR: directory /data/pg_tblspc/ts_ssd is not empty说明目录里已经有文件了。注意只要里面有文件不管是什么临时文件、隐藏文件PG都会拒绝使用。我之前有一次建表空间失败排查半天发现是有人在目录里放了一个README.txt。先清空目录再创建。还有一种不是报错但后果严重的情况目录属主和权限不对。如果目录不是postgres用户所有或者权限是0777这种开放权限表空间虽然可能建成功但后续PostgreSQL进程读写时可能直接报权限错误或者在重启后找不到文件。所以chown和chmod别省按文档的0700来。另外表空间目录不要放在$PGDATA内部也不要和其他表空间目录互相嵌套。负责PG数据目录的目录结构已经够复杂了别再制造循环引用。2.4 ALTER 和 DROP 的正确姿势修改表空间信息用ALTER TABLESPACE支持的变更包括重命名、换属主、修改参数ALTER TABLESPACE ts_ssd RENAME TO ts_ssd_v2; ALTER TABLESPACE ts_ssd OWNER TO new_app; ALTER TABLESPACE ts_ssd SET (random_page_cost 1.1); ALTER TABLESPACE ts_ssd RESET (random_page_cost);注意重命名只改逻辑名字表空间OID不变符号链接指向的物理目录也不变。已有的对象不会因为改名而移动位置所以改名操作本身很轻量基本无锁风险。删除表空间用DROP TABLESPACE ts_old;这句话最常出现的报错是ERROR: tablespace ts_old cannot be dropped because some objects depend on it只要表空间里还有任何表、索引、物化视图或者还有数据库把它当默认表空间DROP就失败。这是保护机制不是BUG。删除前必须先迁移或删除依赖对象具体清理顺序留到第4章讲。3. 高频日常建库、建表、建索引时如何精准落盘3.1 三个CREATE语句的TABLESPACE子句日常开发中最常写的是建库、建表、建索引时指定表空间。三条语句长得差不多CREATE DATABASE db_report TABLESPACE ts_ssd; CREATE TABLE metrics ( day date NOT NULL, source text, value numeric ) TABLESPACE ts_ssd; CREATE INDEX idx_metrics_day ON metrics (day) TABLESPACE ts_ssd;CREATE DATABASE指定表空间时新数据库的系统表和默认对象都会落到这个表空间里相当于给整个新库定了一个默认落盘位置。CREATE TABLE指定表空间最直观就是这张表的所有文件都放那儿。CREATE INDEX指定表空间可以做到索引和表分离。这里有个小细节如果建索引时不写TABLESPACE子句索引会默认跟着表走除非当前会话设置了default_tablespace。所以看到“索引建完发现和表不在一个表空间”时先查一下default_tablespace是不是被谁改过。3.2 default_tablespace会话级的“默认落点”default_tablespace的作用是给当前会话一个默认表空间后面所有不带TABLESPACE子句的建表、建索引语句都会落到这个默认位置SET default_tablespace ts_ssd; SHOW default_tablespace;设置完之后像下面这种普通建表语句就不需要再写表空间子句了CREATE TABLE metrics_tmp ( id bigint, val text );这张表会落在ts_ssd里。这个参数有两个边界要记住它对系统目录不生效对临时表也不生效。临时表走的是temp_tablespaces和default_tablespace是两条线。另外它还可以绑定到角色上比如ALTER ROLE app_user SET default_tablespace ts_ssd;这样app_user一登录默认落点就自动指向ts_ssd适合给不同业务账号分配不同存储资源。3.3 一个“只放索引”的表空间设计案例有人看到索引可以单独放表空间就以为把索引全挪到SSD就能让查询变快其实没那么简单。走普通索引扫描时索引和表的数据都需要IO索引在SSD、表在机械盘整体速度还是会被慢的那一边拖住。真正有价值的是配合覆盖索引使用。假设metrics这张大表留在HDD但某类高频查询只需要day、source、value三列并且通过day过滤。这种情况下建一个覆盖索引放SSDCREATE INDEX idx_metrics_day_covering ON metrics (day) INCLUDE (source, value) TABLESPACE ts_ssd;如果查询只需要这三列并且索引的可见性映射允许PostgreSQL可以直接用索引回答不用回表。这时数据块几乎都在SSD上查询速度才有可能拉开差距。反过来说如果只是把普通索引搬到SSD表还在HDD实际收益往往很有限。我在项目里见过有人把所有索引都塞进SSD结果SSD容量吃紧查询没有明显变快最后又把大部分索引挪回去了。设计表空间之前先分析访问模式再决定让哪些数据“住”在快盘上。4. 挪数据与删空间ALTER类命令的锁、文件拷贝与清理顺序4.1 ALTER TABLE / ALTER INDEX SET TABLESPACE 的基本用法把对象从一个表空间搬到另一个表空间核心命令是ALTER TABLE metrics SET TABLESPACE ts_ssd; ALTER INDEX idx_metrics_day SET TABLESPACE ts_ssd;执行后表的数据文件会在目标表空间目录下重建和表关联的索引、TOAST数据通常也会一并移动到新表空间。如果只想单独移动某一个索引就用ALTER INDEX ... SET TABLESPACE。这里最需要提前知道的是锁行为ALTER TABLE SET TABLESPACE在重写期间会持有排他锁表在迁移完成前无法被正常读写。这个锁随表大小、磁盘速度、跨盘拷贝耗时增长10GB的表和1TB的表完全不是一回事。实战中我习惯加锁超时保护避免操作排队导致业务链路被拖死SET lock_timeout 10s; ALTER TABLE metrics SET TABLESPACE ts_ssd;如果10秒内拿不到锁语句直接失败并报错而不是傻等着挂起。对线上环境来说“快速失败”通常比“缓慢成功”更安全。4.2 同一文件系统的硬链接 vs 跨文件系统拷贝ALTER TABLE ... SET TABLESPACE的底层动作本质是在新目录构建一份完整的文件集然后切换指向。两种典型情况源表空间和目标表空间在同一个文件系统上较新版本会尝试利用硬链接让新目录里的文件直接复用已有的数据块速度非常快。跨文件系统移动时数据必须物理拷贝。比如从机械盘迁到新挂载的SSD基本就是实打实的读写下数据。有一次我迁移一张约120GB的表到新挂载的SSD当时跨文件系统拷贝整整花了接近半小时期间应用侧几乎都在阻塞。后来同一张表在同一块磁盘的不同目录之间换表空间几秒就完成了。这个差异会让“感觉上很快”和“实际上很慢”形成巨大的预期偏差。所以操作前一定用系统命令评估一下df -h /data/ssd du -sh /var/lib/postgresql/16/main/base/16385/16391看目标盘剩余空间是否大于表大小。跨文件系统迁移时磁盘峰值占用大约是原来的表大小加上新位置的一份拷贝空间不够直接别动。4.3 DROP TABLESPACE 前必须完成的清理链删表空间最怕的就是“明明好像没有表了还是删不掉”。所以正确顺序是先查依赖、再迁移或删除、最后删表空间。先找到所有落在该表空间的对象SELECT n.nspname AS schema, c.relname AS object_name, c.relkind FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.reltablespace (SELECT oid FROM pg_tablespace WHERE spcname ts_old) ORDER BY n.nspname, c.relname;查出来之后逐个迁走ALTER TABLE app_user.some_table SET TABLESPACE ts_keep;如果表空间本身是某个数据库的默认表空间还需要先改数据库默认位置ALTER DATABASE db_report SET TABLESPACE ts_keep;ALTER DATABASE ... SET TABLESPACE会改变数据库默认表空间并把数据库中那些未单独指定表空间的对象一并迁移过去。这个操作同样有锁和耗时问题放在低峰期做。全部清理干净后再执行DROP TABLESPACE ts_old;。我在操作分区表时还踩过另一个坑对父表执行SET TABLESPACE想当然以为所有子分区都会跟着走结果查了一下发现并不是所有版本都递归处理。安全做法是迁移完父表后用第5章里的查询把整个分区家族都检查一遍缺哪个补哪个。5. 怎么查表空间目录寻址、空间占用与临时文件疏散5.1 目录寻址pg_tablespace_location 和 \db最常用的查询就是系统表和\db元命令SELECT oid, spcname, pg_tablespace_location(oid) AS location FROM pg_tablespace;输出大致长这样oidspcnamelocation1663pg_default1664pg_global16417ts_ssd/data/pg_tblspc/ts_ssdpg_default和pg_global这两个内置表空间没有独立目录所以pg_tablespace_location返回空。在psql里直接敲\db也能看到类似信息还带权限和参数选项。日常排查表空间问题第一件事就是确认表空间OID和物理目录的对应关系这套查询比跑去看符号链接可靠得多因为不用登录服务器也能执行。5.2 空间计算pg_tablespace_size 与 OS 层 du想知道一个表空间已经占了多少物理空间用SELECT pg_size_pretty(pg_tablespace_size(ts_ssd)) AS total_size;这个函数会遍历该表空间下所有文件并累加大小结果很准但大表空间跑起来会比较慢建议低峰期使用。它返回的是数据库视角下的逻辑占用和操作系统的du结果会有一定出入因为还有一些目录、临时文件之类不在对象统计范围内。系统层的快速估算用du -shL /var/lib/postgresql/16/main/pg_tblspc/*注意这里要用-L跟随符号链接否则看到的只是链接本身的大小会得到一个完全没意义的数字。我见过有人用du -sh /var/lib/postgresql/16/main/pg_tblspc/*所有目录显示都是4K就是因为没跟符号链接。做监控时逻辑大小和物理可用空间要一起看。pg_tablespace_size只说明数据库对象占了多少但磁盘上还有WAL、归档日志、系统文件等df -h仍然要看。5.3 找出表空间里的“大头”一个表空间被占满之前通常只有少数几个表或索引在吃空间。找出来其实不难SELECT n.nspname AS schema, c.relname AS relation, ts.spcname AS tablespace, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace LEFT JOIN pg_tablespace ts ON ts.oid c.reltablespace WHERE c.relkind IN (r, i, m) ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;这个查询把表和索引都算进来了relkind分别是r表、i索引、m物化视图。如果某行里tablespace是空说明对象用的是所在数据库的默认表空间并没有显式指定。想进一步确认某个数据库默认表空间指向哪里SELECT d.datname, ts.spcname FROM pg_database d LEFT JOIN pg_tablespace ts ON ts.oid d.dattablespace;这套查询配合起来基本能把“对象到底在哪一层”看得清清楚楚。5.4 temp_tablespaces临时表和临时文件的疏散临时表空间太容易被忽视了。大排序、哈希连接、索引构建都可能产生临时文件如果临时文件落在和系统盘同一个目录写满之后整个实例都危险。temp_tablespaces就是专门控制临时对象和临时文件落点的参数ALTER SYSTEM SET temp_tablespaces ts_tmp; SELECT pg_reload_conf(); SHOW temp_tablespaces;ALTER SYSTEM改的是全局配置pg_reload_conf()之后新会话生效。临时文件的创建位置会在当前值的基础上选择已经运行中的长事务不一定会立刻受影响所以设置完别急着把所有旧连接全杀了。也可以写多个表空间PostgreSQL会轮询使用充分利用多块磁盘的IO能力ALTER SYSTEM SET temp_tablespaces ts_tmp1, ts_tmp2;我建议临时文件不要放到跨网络的共享存储上否则大查询的排序性能会非常难看。本地NVMe是最理想的选择。验证临时表实际落点可以建一张临时表再查CREATE TEMP TABLE _ts_check(id int); SELECT c.relname, ts.spcname FROM pg_class c LEFT JOIN pg_tablespace ts ON ts.oid c.reltablespace WHERE c.relname _ts_check;如果spcname显示的是你配置的临时表空间说明temp_tablespaces已经生效。这种方法也适合确认其他对象的落盘位置。我在日常运维中最常用的其实不是复杂的监控平台而是把第5.3的查询存成一个固定脚本每次处理完表空间变更后跑一遍确认对象都到了预期目录。很多问题都出在“看起来执行成功但对象留在老位置”。建议你在做完任何表空间相关操作后都用\db和pg_tablespace_location复核一遍。另外表空间命名最好带上用途和介质比如ts_log_hdd、ts_idx_ssd半年后你会感谢这个命名习惯。