PostgreSQL目录结构之base目录

base目录

对于集簇里的每个数据库，在$PGDATA/base里都有一个子目录对应，子目录的名字为该数据库在 pg_database里的 OID。

#查看各个数据库的oid
testdb=# select oid, datname from pg_database;
  oid  |  datname  
-------+-----------
 13025 | postgres
 16384 | testdb
     1 | template1
 13024 | template0
(4 rows)

每一张表的数据（大部分）又是放在 $PGDATA/base/{dboid}/{relfilenode} 这个文件里面，relfilenode一般情况下和和tboid一致，但有些情况下也会变化，如TRUNCATE、REINDEX、CLUSTER以及某些形式的ALTER TABLE。

#查询relowner
testdb=# select oid,rolname from pg_authid where rolname='postgres';
 oid | rolname  
-----+----------
  10 | postgres
(1 row)
#查询pg_class
testdb=# select relname,relowner,relfilenode from pg_class;
                    relname                    | relowner | relfilenode 
-----------------------------------------------+----------+-------------
 testtb                                        |       10 |       16385
 testtb_index                                  |       10 |       16388
 testtb2                                       |       10 |       16389
 ...

包含文件

在$PGDATA/base/{dboid}中通常会包含三种文件：例如16385、16385_fsm、16385_vm，分别是该数据库对应表的数据或索引文件、其对应的空闲空间映射文件、其对应的可见性映射文件。

数据文件

在表或者索引超过1GB之后，它就被划分成1GB大小的段。 第一个段的文件名和文件节点相同，随后的段被命名为 filenode.1、filenode.2等等。这样的安排避免了在某些有文件大小限制的平台上的问题。

#创建测试表
testdb=# create table testtb4(id int,name varchar(64));
CREATE TABLE
#查看表文件存储位置
testdb=# select pg_relation_filepath('testtb4');
 pg_relation_filepath 
----------------------
 base/16384/16403
(1 row)
#插入大量数据
testdb=# insert into testtb4 SELECT generate_series(1,20000000) as key, md5(random()::text);
INSERT 0 20000000
#查看目录文件
[root@ff1c61e75696 base]# ll 16384 |grep 16403
-rw------- 1 postgres postgres 1073741824 Oct 30 09:11 16403
-rw------- 1 postgres postgres  291594240 Oct 30 09:13 16403.1
-rw------- 1 postgres postgres     352256 Oct 30 09:09 16403_fsm

TOAST表

如果一个表的列中可能存储相当大的项，那么该表就会有个与之相关联的TOAST（The Oversized-Attribute Storage Technique）表，它用于存储无法保留在在表行中的域值的行外存储。如果表有TOAST表，该表的pg_class.reltoastrelid链接到它的TOAST表。

TOAST表的存储策略：

• PLAIN：避免压缩和行外存储。只有那些不需要TOAST策略就能存放的数据类型允许选择（例如int类型），而对于text这类要求存储长度超过页大小的类型，是不允许采用此策略的
• EXTENDED：允许压缩和行外存储。一般会先压缩，如果还是太大，就会行外存储
• EXTERNA：允许行外存储，但不许压缩。类似字符串这种会对数据的一部分进行操作的字段，采用此策略可能获得更高的性能，因为不需要读取出整行数据再解压
• MAIN：允许压缩，但不许行外存储。不过实际上，为了保证过大数据的存储，行外存储在其它方式（例如压缩）都无法满足需求的情况下，作为最后手段还是会被启动。因此理解为：尽量不使用行外存储更贴切

TOAST字段：

• chunk_id：用来表示特定TOAST值的OID，可以理解为具有同样chunk_id值的所有行组成原表的TOAST字段的一行数据
• chunk_seq：用来表示该行数据在整个数据中的位置
• chunk_data：实际存储的数据。

#创建测试表
CREATE TABLE
#查看表详情
testdb=# \d+ testtb3
                                   Table "public.testtb3"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+---------+-----------+----------+---------+----------+--------------+-------------
 id      | integer |           |          |         | plain    |              | 
 content | text    |           |          |         | extended |              | 
#查看toastoid
testdb=# select relname,relfilenode,reltoastrelid from pg_class where relname='testtb3';
 relname | relfilenode | reltoastrelid 
---------+-------------+---------------
 testtb3 |       16393 |         16396
(1 row)
#查看toast表详情，其中pg_toast.pg_toast_{relfilenode}就是该表名
testdb=# \d+ pg_toast.pg_toast_16393;
TOAST table "pg_toast.pg_toast_16393"
   Column   |  Type   | Storage 
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain
#插入一条数据
testdb=# insert into testtb3 values(1, 'content');
INSERT 0 1
testdb=# select * from testtb3;
 id | content 
----+---------
  1 | content
(1 row)
#查询pg_toast.pg_toast_16393，发现是没有数据的
testdb=# select * from pg_toast.pg_toast_16393;
 chunk_id | chunk_seq | chunk_data 
----------+-----------+------------
(0 rows)
#再插入一条字段超长的数据
insert into testtb3 values(2, repeat(md5(random()::text),20000));
testdb=# select id,length(content) from testtb3;
 id | length 
----+--------
  1 |      7
  2 | 640000
(2 rows)
#再查询pg_toast.pg_toast_16393，发现已经有数据
testdb=# select chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_16393;
 chunk_id | chunk_seq | length 
----------+-----------+--------
    16405 |         0 |   1996
    16405 |         1 |   1996
    16405 |         2 |   1996
    16405 |         3 |   1381
(4 rows)

可以发现在testtb3中数据长度为640000，但是在pg_toast.pg_toast_16393中所有行加起来1996*3+1381=7369远小于640000，是因为toast做了数据压缩，而且toast每行最大长度为1996。‬

FSM文件

每一个堆和索引关系（除了哈希索引）都有一个空闲空间映射（FSM）来保持对关系中可用空间的跟踪。它伴随着主关系数据被存储在一个独立的关系分支中，以关系的文件节点号加上一个_fsm后缀命名。例如，如果一个关系的文件节点是12345，那么FSM被存储在一个名为12345_fsm的文件中，该文件与主关系文件在同一个目录中。

空闲空间映射被组织成一棵FSM页面的树。底层FSM页面存储了在每一个堆（或索引）页面中可用的空闲空间，对于每一个这样的页面使用一个字节来表示。上层FSM页面则聚集来自于下层页面的信息。

在每一个FSM页面中是一个二叉树，存储在一个数组中，每一个节点一个字节。每个叶节点表示一个堆页面或者一个下层FSM页面。在每一个非叶节点中存储了它孩子节点中的最大值。因此叶节点中的最大值被存储在根中。

但是，在新建表时不会产生_fsm和_vm文件，直到pg执行VACUUM操作时，或者是第一次要使用_fsm或_vm文件时才会生成。

#查看表文件存储位置
testdb=# select pg_relation_filepath('testtb2');
 pg_relation_filepath 
----------------------
 base/16384/16389
(1 row)
#查看16389
[root@ff1c61e75696 16384]# tree|grep 16389
├── 16389
#手动执行vacuum
[root@ff1c61e75696 16384]# su postgres 
bash-4.2$ vacuumdb -t testtb testdb2
bash-4.2$ vacuumdb -t testtb2 testdb
vacuumdb: vacuuming database "testdb"
#再查看16389
[root@ff1c61e75696 16384]# tree|grep 16389
├── 16389
├── 16389_fsm
├── 16389_vm

pg提供了一个插件pg_freespacemap可供我们查询FSM，该插件提供了一种方法来检查空闲空间映射（FSM）。它提供了一个称为pg_freespace的函数，或者准确地说是两个重载的函数。这些函数显示空闲空间映射中为一个给定页面所记录的值，或者显示关系中所有页面的记录值。

默认情况下，使用仅限于超级用户和pg_stat_scan_tables 角色的成员。可以使用GRANT给其他人授予访问权限。

#安装插件
postgres=# create extension pg_freespacemap;
CREATE EXTENSION
#查看插件
testdb=# \dx
                       List of installed extensions
      Name       | Version |   Schema   |           Description            
-----------------+---------+------------+----------------------------------
 pg_freespacemap | 1.2     | public     | examine the free space map (FSM)
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
#查看可用函数
testdb=# \dxS+ pg_freespacemap
 Objects in extension "pg_freespacemap"
           Object description           
----------------------------------------
 function pg_freespace(regclass)
 function pg_freespace(regclass,bigint)
(2 rows)
#查看表的FSM
testdb=# SELECT * FROM pg_freespace('testtb2');
 blkno | avail 
-------+-------
     0 |  8000
(1 row)

VM文件

每一个堆关系都有一个可见性映射（VM）用来跟踪哪些页面只包含已知对所有活动事务可见的元组，它也跟踪哪些页面只包含未被冻结的元组。

pg提供了一个插件pg_visibility可供我们查询VM，该插件提供了一种方式来检查一个表的可见性映射（VM）以及页级别的可见性信息。它还提供了函数来检查可见性映射的完整性以及强制重建可见性映射。

#安装插件
postgres=# create extension pg_visibility;
CREATE EXTENSION
#查看插件
testdb=# \dx
                                      List of installed extensions
      Name       | Version |   Schema   |                          Description                           
-----------------+---------+------------+----------------------------------------------------------------
 pg_freespacemap | 1.2     | public     | examine the free space map (FSM)
 pg_visibility   | 1.2     | public     | examine the visibility map (VM) and page-level visibility info
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)
#查看可用函数
testdb=# \dxS+ pg_visibility
     Objects in extension "pg_visibility"
              Object description               
-----------------------------------------------
 function pg_check_frozen(regclass)
 function pg_check_visible(regclass)
 function pg_truncate_visibility_map(regclass)
 function pg_visibility(regclass)
 function pg_visibility(regclass,bigint)
 function pg_visibility_map(regclass)
 function pg_visibility_map(regclass,bigint)
 function pg_visibility_map_summary(regclass)
(8 rows)
#查看表可见的block
testdb=# SELECT * FROM pg_visibility('testtb2');
 blkno | all_visible | all_frozen | pd_all_visible 
-------+-------------+------------+----------------
     0 | t           | f          | t
(1 row)