PostgreSQL数据库存储结构

数据组织结构

数据库 Database

与MySQL类似，每台PG Server上运行的实例可以管理多个Database。这些Database的集合被称为一个Database Cluster。

每个PG实例初始化后会自动创建三个初始数据库：template0、template1和postgres。

• template0：用于逻辑备份恢复和创建特殊编码的数据库。template0不能被修改。
• template1：创建其他用户数据库的模板。
• postgres：默认创建的一个常规数据库。

System catalog

System catalog中存放了数据库元数据相关的系统表。这些表命名都以pg_开头。

从所有数据库中都可以访问这些系统表。

模式 Schema

模式是所有数据库对象的一个逻辑集合。PG数据库中的Schema与MySQL中的类似。每个Schema对应一个数据库。每个数据库对象都有自己所属的模式。

默认的模式是public。所有元数据表所属的模式是pg_catalog。

表空间 Tablespace

PG数据库中的表空间是指存储数据库数据的文件系统目录。

多个数据库可以共用同一个表空间。每个数据库的数据也可以存储到多个不同的表空间中。

PG数据库初始化时，会自动创建两个表空间：pg_default和pg_global。

• pg_default：对应$PGDATA/base目录。默认使用的用户表空间。
• pg_global：对应$PGDATA/global目录。System catalog中所有元数据系统表存放的表空间。

PG数据库中创建自定义表空间时需要指定文件系统目录路径，创建成功后会自动在$PGDATA/pg_tblspc下生成链接文件。

关系 Relation

关系（relation）在PG数据库中是对表、索引、普通视图、物化视图、序列等所有数据库对象的统称。

分支 Forks

关系（relation）的所有信息都存储在分支（Fork）中。分支（Fork）是磁盘上的一个或多个文件。

有以下几种分支（Fork）类型：

• the main fork：主分支存储了实际的表行数据或者索引行数据。除了视图以外所有类型的关系都有主分支。主分支的文件名以数字编号表示，记录在pg_class系统表的relfilenode字段中。
• the initialization fork：初始化分支仅对不记录WAL日志的关系生效，例如以CREATE UNLOGGED TABLE语句创建的表（这种表在数据库故障时会丢失数据，因为没有日志用来恢复）。初始化分支的文件名以数字编号_init表示，其中的数字编号记录在pg_class系统表的relfilenode字段中。
• free space map：空闲空间映射用于跟踪数据页中的的剩余可用空间。空闲空间映射的文件名以数字编号_fsm表示，其中的数字编号记录在pg_class系统表的relfilenode字段中。
• visibility map：可见性映射可以快速展示某个数据页是否需要被VACUUM或者FROZEN。可见性映射的文件名以数字编号_vm表示，其中的数字编号记录在pg_class系统表的relfilenode字段中。

如果某个分支文件的大小超过了1G，就会自动生成新的分支文件。假设第一个文件名为FILEID_fsm，新文件命名方式依次为FILEID_fsm.1、FILEID_fsm.2、FILEID_fsm.3，以此类推。

示例：

tpcc=> select datname,oid from pg_database;datname  |  oid  
-----------+-------template1 |     1template0 | 12810postgres  | 12815tpcc      | 17153
(4 rows)tpcc=> select relname,oid,relfilenode from pg_class where relname='bmsql_customer';relname     |  oid  | relfilenode 
----------------+-------+-------------bmsql_customer | 17165 |       17165
(1 row)[omm@dbhost PG_9.2_201611171_dn_6001]$ cd /data/cluster/data/dn/dn_6001/pg_tblspc/17152/PG_9.2_201611171_dn_6001
[omm@dbhost PG_9.2_201611171_dn_6001]$ ll -h
total 32K
drwx------ 2 omm omm 16K Sep 20 17:05 17153
drwx------ 2 omm omm   6 Sep 20 16:57 pgsql_tmp[omm@dbhost PG_9.2_201611171_dn_6001]$ ll -h 17153/17165*
-rw------- 1 omm omm 1.0G Sep 20 17:07 17153/17165
-rw------- 1 omm omm 1.0G Sep 20 17:08 17153/17165.1
-rw------- 1 omm omm 1.0G Sep 20 17:09 17153/17165.2
-rw------- 1 omm omm 406M Sep 20 17:09 17153/17165.3
-rw------- 1 omm omm 896K Sep 20 17:09 17153/17165_fsm
-rw------- 1 omm omm  56K Sep 20 17:09 17153/17165_vm

数据页 Page

数据页（Page）即数据块（Block），是PG数据库中读写操作的最小IO单位。每个数据页默认大小为8K。

TOAST

行数据必须存储在单个数据页中。如果行数据太长单个数据页放不下，就可以把长字段存放到一个TOAST表中（类似于将吐司切片），或者将长字段进行压缩。

PG数据库中的TOAST机制支持以下四种策略：

• plain：不使用TOAST策略。
• extended：压缩长字段、并把长字段存放到另外的TOAST表中。
• external：不压缩长字段，仅把长字段存放到另外的TOAST表中。
• main：先压缩长字段。如果压缩后仍然放不到单个数据页中，再把长字段存放到另外的TOAST表中。

所有的TOAST表都存放在单独的pg_toast模式下。

tpcc=> select attname,atttypid::regtype,case attstoragewhen 'p' then 'plain'when 'e' then 'extended'when 'm' then 'main'when 'x' then 'extended'end as storage 
from pg_attribute where attrelid='bmsql_customer'::regclass and attnum > 0;attname     |          atttypid           | storage  
----------------+-----------------------------+----------c_w_id         | integer                     | plainc_d_id         | integer                     | plainc_id           | integer                     | plainc_discount     | numeric                     | mainc_credit       | character                   | extendedc_last         | character varying           | extendedc_first        | character varying           | extendedc_credit_lim   | numeric                     | mainc_balance      | numeric                     | mainc_ytd_payment  | numeric                     | mainc_payment_cnt  | integer                     | plainc_delivery_cnt | integer                     | plainc_street_1     | character varying           | extendedc_street_2     | character varying           | extendedc_city         | character varying           | extendedc_state        | character                   | extendedc_zip          | character                   | extendedc_phone        | character                   | extendedc_since        | timestamp without time zone | plainc_middle       | character                   | extendedc_data         | character varying           | extended
(21 rows)

数据文件结构

大小不超过1GB的表或索引都会以单个数据文件的形式存放在base或者自定义表空间路径下。

每个表数据文件由多个固定大小的数据页/块（page/block）组成。每个数据页默认大小为8K。

每个数据页中存储了三种数据：header data、line pointer、heap tuple。

• header data：占用数据页头部24 bytes空间，存放了pd_lsn、pd_checksum、pd_lower、pd_upper、pd_special等关于当前数据页本身的元数据信息。
• line pointer：每个line pointer指向同一个数据页中唯一一个heap tuple。每个line pointer占用4 bytes空间。
• heap tuple（堆元组）：heap tuple中存储了用户的行记录数据、以及相关的事务ID信息。每个heap tuple由一个TID（tuple identifier）唯一标识。

Line pointer从数据页的顶部往下新增，Heap tuple从数据页的底部往上新增。两者之间的空洞区域为数据页的剩余可用空间。

文件系统布局

PG数据库使用OID数字标识（Object Identifier）来管理数据库对象。

查询数据库对象的OID信息：

tpcc=> select datname,oid from pg_database;datname  |  oid  
-----------+-------template1 |     1template0 | 12810postgres  | 12815tpcc      | 17153
(4 rows)tpcc=>  select relname,oid,relfilenode from pg_class where relname='bmsql_customer';relname     |  oid  | relfilenode 
----------------+-------+-------------bmsql_customer | 17165 |       17165
(1 row)

PG数据目录下的文件一览（这里实际是GaussDB的数据目录，供参考）：

[omm@dbhost ~]$ ll /data/cluster/data/dn/dn_6001/
total 4984
drwx------ 6 omm omm      58 Sep 20 11:05 base
-rw------- 1 omm omm    1277 Sep 13 10:25 cacert.pem
-rw------- 1 omm omm      72 Sep 20 16:33 gaussdb.state
drwx------ 3 omm omm    4096 Sep 20 17:05 global
-rw------- 1 omm omm     354 Sep 13 10:39 gs_gazelle.conf
-rw------- 1 omm omm 4915200 Sep 13 10:39 gswlm_userinfo.cfg
drwx------ 2 omm omm       6 Sep 13 10:39 hotpatch
-rw------- 1 omm omm      48 Sep 13 10:39 paxosinfo
-rw------- 1 omm omm      48 Sep 13 10:39 paxosinfo.backup
drwx------ 2 omm omm      26 Sep 13 10:39 pg_clog
drwx------ 2 omm omm      26 Sep 13 10:39 pg_csnlog
-rw------- 1 omm omm    1024 Sep 13 10:39 pg_ctl.lock
drwx------ 2 omm omm       6 Sep 13 10:39 pg_errorinfo
-rw------- 1 omm omm    4600 Sep 20 16:31 pg_hba.conf
-rw------- 1 omm omm    4538 Sep 13 10:40 pg_hba.conf.bak
-rw------- 1 omm omm    1024 Sep 13 10:39 pg_hba.conf.lock
-rw------- 1 omm omm    1685 Sep 13 10:40 pg_ident.conf
drwx------ 4 omm omm      39 Sep 13 10:39 pg_llog
drwx------ 3 omm omm      24 Sep 20 11:08 pg_location
drwx------ 2 omm omm      35 Oct 10 11:19 pg_logical
drwx------ 4 omm omm      36 Sep 13 10:39 pg_multixact
drwx------ 2 omm omm      26 Sep 20 16:33 pg_notify
drwx------ 2 omm omm       6 Sep 13 10:39 pg_replslot
drwx------ 2 omm omm       6 Sep 13 10:39 pg_serial
drwx------ 2 omm omm       6 Sep 13 10:39 pg_snapshots
drwx------ 2 omm omm      25 Oct 10 11:19 pg_stat_tmp
drwx------ 2 omm omm      45 Sep 20 13:44 pg_tblspc
drwx------ 2 omm omm       6 Sep 13 10:39 pg_twophase
-rw------- 1 omm omm       4 Sep 13 10:39 PG_VERSION
drwx------ 3 omm omm   16384 Oct  9 19:29 pg_xlog
-rw------- 1 omm omm   35562 Sep 20 16:33 postgresql.conf
-rw------- 1 omm omm   35561 Sep 20 15:10 postgresql.conf.bak
-rw------- 1 omm omm    1024 Sep 13 10:39 postgresql.conf.lock
-rw------- 1 omm omm      87 Sep 20 16:33 postmaster.opts
-rw------- 1 omm omm     107 Sep 20 16:33 postmaster.pid
-rw------- 1 omm omm       0 Sep 13 10:39 postmaster.pid.lock
-rw------- 1 omm omm    4367 Sep 13 10:25 server.crt
-rw------- 1 omm omm    1766 Sep 13 10:25 server.key
-rw------- 1 omm omm      56 Sep 13 10:39 server.key.cipher
-rw------- 1 omm omm      24 Sep 13 10:39 server.key.rand
drwx------ 5 omm omm      67 Sep 13 10:39 undo

数据目录：base, global, pg_tblspc, pg_location

• base路径下存放了默认表空间pg_default中的数据文件，不包含创建时指定了自定义路径的用户表空间。
• global路径下存放了系统表空间pg_global中的数据文件，主要是pg_开头的元数据表文件。

[omm@dbhost ~]$ ls /data/cluster/data/dn/dn_6001/base
1  12810  12815  pgsql_tmp

• pg_tblspc路径下存放的是各个用户表空间的软链接。

[omm@dbhost ~]$ ll /data/cluster/data/dn/dn_6001/pg_tblspc/
total 0
lrwxrwxrwx 1 omm omm 57 Sep 20 11:08 17144 -> /data/cluster/data/dn/dn_6001/pg_location/tablespace/tbs1
lrwxrwxrwx 1 omm omm 57 Sep 20 11:13 17145 -> /data/cluster/data/dn/dn_6001/pg_location/tablespace/tbs2
lrwxrwxrwx 1 omm omm 57 Sep 20 13:44 17152 -> /data/cluster/data/dn/dn_6001/pg_location/tablespace/tpcc
lrwxrwxrwx 1 omm omm 23 Oct 11 12:46 17680 -> /home/omm/tblspc/testbs

⭐️ 创建自定义表空间指定路径的两种方法：
gaussdb=> create tablespace tpcc relative location ‘tablespace/tpcc’;
gaussdb=> create tablespace testbs location ‘/home/omm/tblspc/testbs’;

• pg_location路径下存放了创建自定义表空间时通过RELATIVE LOCATION关键字指定的相对子路径。

[omm@dbhost ~]$ ls /data/cluster/data/dn/dn_6001/pg_location/tablespace/tpcc
PG_9.2_201611171_dn_6001[omm@dbhost ~]$ ls /data/cluster/data/dn/dn_6001/pg_location/tablespace/tpcc/PG_9.2_201611171_dn_6001/
17153  pgsql_tmp[omm@dbhost ~]$ ls /data/cluster/data/dn/dn_6001/pg_location/tablespace/tpcc/PG_9.2_201611171_dn_6001/17153 | grep 17165
17165
17165.1
17165.2
17165.3
17165_fsm
17165_vm

OID=17153对应数据库tpcc，OID=17165对应表bmsql_customer。

⭐️ 自定义表空间路径下会生成一个子目录，命名方式为PG_[Major version]_[Catalogue version number]。

函数pg_relation_filepath可用于查询给定名称或OID的相对磁盘路径：

tpcc=>  select relname,oid,relfilenode from pg_class where relname='bmsql_customer';relname     |  oid  | relfilenode 
----------------+-------+-------------bmsql_customer | 17165 |       17165
(1 row)tpcc=> select pg_relation_filepath(17165);pg_relation_filepath                 
------------------------------------------------------pg_tblspc/17152/PG_9.2_201611171_dn_6001/17153/17165
(1 row)tpcc=> 
tpcc=> select pg_relation_filepath('bmsql_customer');pg_relation_filepath                 
------------------------------------------------------pg_tblspc/17152/PG_9.2_201611171_dn_6001/17153/17165
(1 row)

日志目录：pg_clog, pg_xact, pg_wal, pg_xlog

• pg_clog（版本9.6和9.6之前）和pg_xact（版本10和10之后）路径下存放了事务提交状态的相关日志。
• pg_xlog（版本9.6和9.6之前）和pg_wal（版本10和10之后）路径下存放了WAL日志文件。

References
[1] https://www.interdb.jp/pg/
[2] https://edu.postgrespro.com/postgresql_internals-14_en.pdf
[3] https://www.postgresql.org/docs/14/storage-file-layout.html