个人简介
微信公众号：数据库杂记 个人微信: _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表是针对原表的列而言，随机性好，就可以轻易达到长度阈值。
一个表出现这么多文件，数量如果太多的话，对表的相关文件访问性能会受到很大影响。这是由文件系统的特性决定的。
对比看下Oracle
在别的数据库(如Oracle)中，看到：
这是相当恐怖的，换到PG中，是不可想象的。
而最近在某群里头，看到实际生产环境中PG里居然出现了56万张表（原因之一是非常大量采用了子分区），结果可想而知。
小结
总之，不能不受限制的在PG中创建大量的数据库表。需要综合考虑。文件太多，系统要拉垮！