PG中一个表的3~8个文件是怎么来的？

个人简介

微信公众号：数据库杂记   个人微信: _iihero
我是iihero. 也可以叫我Sean.
iihero@CSDN(https://blog.csdn.net/iihero) 
Sean@墨天轮 (https://www.modb.pro/u/16258)
iihero@zhihu (https://www.zhihu.com/people/zhou-mo-xu)
数据库领域的资深爱好者一枚。SAP数据库技术专家与架构师，PostgreSQL ACE.
水木早期数据库论坛发起人db2@smth. 早期多年水木论坛数据库版版主。
国内最早一批DB2 DBA。前后对Sybase, PostgreSQL, HANA, 
Oracle, DB2, SQLite均有涉猎。曾长期担任CSDN相关数据库版版主。
三本著作：<<Java2网络协议内幕>> <<Oracle Spatial及OCI高级编程>> 
<<Sybase ASE 15.X全程实践>>
兴趣领域：数据库技术及云计算、GenAI

业余专长爱好：中国武术六段 陈式太极拳第13代传人（北京陈式太极拳第5代传人）
职业太极拳教练，兼任北京陈式太极拳研究会副秘书长。
如果想通过习练陈式太极拳强身健体，也可以与我联系。

准备函数

在PG数据库创建一张表，对应它的物理文件可以达到3~8个。很多初学者可能不太注意。认识到这种物理对应关系，在实际的使用过程中，还是十分有意义的。

我们先不急着总结，先看下边的实验：

我们先创建一个函数，用于生成随机性比较强的字符串。

CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyz', round(random() * 25 + 0.5)::integer, 1), '') FROM generate_series(1, $1); 
$$ language sql;

创建目标表

接着，创建目标表，为了便于集中观察，我们试图将表和其它对象都放到一个特定的表空间里。建表如下：

1mydb=# create table t(id int primary key, col2 varchar(4000)) tablespace myts;
2CREATE TABLE
3
4mydb=# insert into t select n, random_string(2800) from generate_series(1, 100) as n;
5INSERT 0 100

这时，看看相关文件：

1[22:07:31-postgres@centos2:/pgccc/myts/PG_14_202107181/21371]$ ls
237796  37799  37799_fsm  37800
3mydb=# select pg_relation_filepath('t'), pg_relation_filepath('t_pkey');
4            pg_relation_filepath             | pg_relation_filepath
5---------------------------------------------+----------------------
6 pg_tblspc/29575/PG_14_202107181/21371/37796 | base/21371/37801
7(1 row)

1mydb=# select oid, relname from pg_class where oid in (37796, 37799, 37800, 37801);
2  oid  |       relname
3-------+----------------------
4 37799 | pg_toast_37796
5 37800 | pg_toast_37796_index
6 37796 | t
7 37801 | t_pkey
8(4 rows)
9

纠正表结构

这样一看，似乎t_pkey：37801文件不在myts上边。两种方式纠正：

建表时直接指下index_parameters.

 1index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
 2
 3[ INCLUDE ( column_name [, ... ] ) ]
 4[ WITH ( storage_parameter [= value] [, ... ] ) ]
 5[ USING INDEX TABLESPACE tablespace_name ]
 6
 7PRIMARY KEY index_parameters
 8
 9或者
10UNIQUE ( column_name [, ... ] ) index_parameters |
11  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
12  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters

重新建表如下：

1mydb=# create table t(id int primary key using index tablespace myts, col2 varchar(4000)) tablespace myts;
2CREATE TABLE
3mydb=# insert into t select n, random_string(2800) from generate_series(1, 100) as n;
4INSERT 0 100

 1[22:10:47-postgres@centos2:/pgccc/myts/PG_14_202107181/21371]$ ls
 237904  37907  37907_fsm  37908  37909
 3
 4select oid, relname from pg_class where oid in (37904, 37907, 37908, 37909);
 5  oid  |       relname
 6-------+----------------------
 7 37907 | pg_toast_37904
 8 37908 | pg_toast_37904_index
 9 37904 | t
10 37909 | t_pkey
11(4 rows)
12

看看上边的结果, toast相关的有三个文件。index有一个文件。t表有一个。

重新查看相关文件

好，接着，我们对t表再执行一个vacuum:

 1mydb=# vacuum t;
 2VACUUM
 3[22:21:00-postgres@centos2:/pgccc/myts/PG_14_202107181/21371]$ ls -li
 4total 400
 534506811 -rw------- 1 postgres postgres   8192 Oct 18 22:20 37904
 634506822 -rw------- 1 postgres postgres  24576 Oct 18 22:22 37904_fsm
 734506821 -rw------- 1 postgres postgres   8192 Oct 18 22:22 37904_vm
 834506814 -rw------- 1 postgres postgres 303104 Oct 18 22:20 37907
 934506820 -rw------- 1 postgres postgres  24576 Oct 18 22:20 37907_fsm
1034506823 -rw------- 1 postgres postgres   8192 Oct 18 22:22 37907_vm
1134506816 -rw------- 1 postgres postgres  16384 Oct 18 22:20 37908
1234506817 -rw------- 1 postgres postgres  16384 Oct 18 22:20 37909

看到没有，8个文件都有了。toast出现了fsm, vm，表文件也出现了fsm, vm。这就是8个文件的来历。

提示一下，上边的随机字符串函数是为了让toast表及其索引出现内容的一种方式。因为toast表是针对原表的列而言，随机性好，就可以轻易达到长度阈值。

一个表出现这么多文件，数量如果太多的话，对表的相关文件访问性能会受到很大影响。这是由文件系统的特性决定的。