PostgreSQL-基础知识(集簇、数据库、表)

说了PostgreSQL的这么多操作之后，我们也应该适时的明白它其中的原理了，这样才能更深刻地掌握它。我是这么想的。做为最初学习它的目的，仅仅是为了拿到证书，从而，在我的简历上增添重要的一笔之外，别无它求。但现在，我改变了这个想法。学习了几个月之后，我想我喜欢上了PostgreSQL，当然，我也不排斥Oracle或者其他的数据库系统。

本文参考The Internals of PostgreSQL了中的内容。本来想，在直接照抄翻译的内容的，但是看了几遍，总觉得理解起来不是很通顺，坑坑巴巴的，难受。其中，加了一些我理解的，大家参考。

数据库集簇、数据库和数据表

不知道为什么，在中文文档中，将database cluster翻译为“数据库集簇”。与其它高可用数据库集群不同的是，这里的“集簇”表示多个逻辑的数据库在同一个数据库实例中。也就是说，这里的实例，是一个数据库集合。下图展示了PostgreSQL的一个实例包括的内容。不难看出，与其他数据库系统的差别是显而易见的。

1 PostgreSQL的逻辑结构

逻辑的，那它就是有一定规律的。我们只要掌握了这种规律，那就抓住了它的要义。

PostgreSQL数据库集簇（database cluster）是一组数据库（database）的集合，是由一个PostgreSQL服务器管理。这里的数据库集簇，并非是指“一组数据库服务器”。一个PostgreSQL服务器运行在单个主机上，管理一个数据库集群。

对于这么绕口的描述，最好的理解方法，就是你动手安装它、使用它、熟悉它。

PostgreSQL不像Oracle，Oracle在单机上安装，一般只有一个实例，一个实例就是一个数据库（数据库软件管理数据库实例），多个实例就是多个数据库，但是，即使你在单机上创建了多个实例，它们也只是各自独立运行的，与集群没有任何关系；而Oracle的集群（RAC），实质则是位于不同服务器的Oracle实例节点同时访问同一个Oracle数据库。

图1-1展示了PostgreSQL数据库集簇的逻辑结构。

PostgreSQL数据库是数据库对象的集合。这里的对象是指用于存储或引用数据的一种数据结构。如表、索引、序列、视图、函数等。在PostgreSQL数据库中，数据库本身也是数据库对象，它们在逻辑上是彼此分离的。如database1的table、index属于database1，database2的table、index属于database2。

图1.1 的逻辑结构

PostgreSQL中，所有数据库对象在内部都由各自的对象标识符(object identifier, oid)管理，这些对象标识符是无符号的4字节整数。数据库对象和相应的oid之间的关系存储在适当的系统目录中，具体取决于对象的类型。例如，数据库的oid和堆表的oid分别存储在pg_database和pg_class中，因此可以通过如下查询来找出想要知道的oid:

postgres=# select oid,datname from pg_database where datname = 'postgres';
  oid  |  datname
-------+-----------
 13892 | postgres
     1 | template1
 13891 | template0
(3 rows)

postgres=# select oid,relname from pg_class where relname = 'tbl';
  oid  | relname
-------+---------
 16384 | tbl
(1 row)

postgres=#

2 PostgreSQL的物理结构

PostgreSQL数据库集簇，其实在本质上，是一个文件目录，即基础目录（你看的见的），它包含了一系列的子目录与文件。当你执行initdb命令，进行PostgreSQL的初始化操作时，就会在指定的目录下创建基础目录，生成一个新的数据库集簇。通常，基础目录的路径被配置到环境变量PGDATA中。

[postgres@pgccc01 ~]$su - postgres

# 初始化
[postgres@pgccc01 ~]$ initdb -D $PGDATA -U postgres

图1-2，是一个PostgreSQL数据库集簇的示例。

base子目录中的每一个子目录都对应一个数据库，数据库中的每个表和索引都至少在相应子目录下存储为一个文件；还有几个包含特定数据的子目录，以及配置文件，如global、pg_wal、postgresql.conf等。

虽然，PostgreSQL数据库支持表空间，但是该术语的含义，与其他关系型数据库管理系统（Relational Database Management System，RDBMS）不同。在PostgreSQL中，表空间是一个在基础目录之外包含一些数据的目录。

图1.2 数据库集簇

2.1 PostgreSQL数据库集簇

PostgreSQL数据库集簇在官方文档73.1. Database File Layout中有详细描述。复制槽。

表1-1 PostgreSQL数据库集簇的文件和目录布局

文件/目录	解释
PG_VERSION	包含PostgreSQL主版本号的文件
base	包含每个数据库子目录的base子目录
current_logfiles	记录日志收集器当前写入的日志文件的文件
global	包含集群范围表的子目录，如pg_database
pg_commit_ts	包含事务提交时间戳数据的子目录
pg_dynshmem	包含动态共享内存子系统使用的文件的子目录
pg_logical	包含用于逻辑解码的状态数据的子目录
pg_multixact	包含多事务状态数据的子目录(用于共享行锁)
pg_notify	包含LISTEN/NOTIFY状态数据的子目录
pg_replslot	包含复制槽位数据的子目录
pg_serial	包含提交的可序列化事务信息的子目录
pg_snapshots	存放导出快照的子目录
pg_stat	包含统计子系统永久文件的子目录
pg_stat_tmp	包含统计子系统临时文件的子目录
pg_subtrans	包含子事务状态数据的子目录
pg_tblspc	包含表空间的软链接的子目录
pg_twophase	包含准备好的事务的状态文件的子目录
pg_wal	存放WAL(预写日志)文件的子目录.在版本10中，它是从pg_xlog重命名的。
pg_xact	包含事务提交状态数据的子目录.在版本10中，它是从pg_clog重命名的。
postgresql.conf	数据库实例的主配置文件，基本上所有配置参数都在此文件中。
postgresql.auto.conf	用于存储由ALTER SYSTEM设置的配置参数的文件
postmaster.opts	记录服务器上次启动时使用的命令行选项的文件
postmaster.pid	一个锁文件，记录当前postmaster进程ID (PID)、集群数据目录路径、postmaster启动时间戳、端口号、unix域套接字目录路径(可能为空)、第一个有效的listen_address (IP地址或*，如果不监听TCP则为空)和共享内存段ID(此文件在服务器关闭后不存在)。
pg_clog/ (Version 9.6 or earlier)	包含事务提交状态数据的子目录。在版本10中，它被重命名为pg_xact。
pg_xlog/ (Version 9.6 or earlier)	包含WAL(预写日志)段文件的子目录。在版本10中，它被重命名为pg_wal。

2.2 数据库布局

一个数据库与base子目录下的一个子目录对应，且该子目录的名称与相应的数据库的oid相同。例如，postgres数据库的OID是13892，则对应的子目录名称是13892。

[postgres@pgccc01 ~]$ cd $PGDATA
[postgres@pgccc01 pgdata]$ ls -ld base/13892
drwx------ 2 postgres postgres 8192 Aug 15 17:53 base/13892
[postgres@pgccc01 pgdata]$

2.3 与表和索引关联的相关文件的布局

每个小于1GB的表或索引是一个单独的文件，存储在它所属的数据库目录下。作为数据库对象的表和索引在内部由各个oid管理，而这些数据文件则由变量relfilenode管理。表和索引的relfilenode值基本上与对应的oid匹配，但并不总是匹配，细节如下所述。

如表 tbl 的 oid 和 relfilenode:最原始创建的表是匹配的。

postgres=# select oid,relfilenode from pg_class where relname = 'tbl';
  oid  | relfilenode
-------+-------------
 16384 |       16384
(1 row)

从上面的结果可以看出，oid和relfilenode的值是相等的。你还可以看到表tbl的数据文件路径是'base/13892/16384'。

[postgres@pgccc01 ~]$ cd $PGDATA
[postgres@pgccc01 pgdata]$ ls -la base/13892/16384
-rw------- 1 postgres postgres 368640 Aug 15 17:55 base/13892/16384

通过执行一些命令(例如TRUNCATE、REINDEX、CLUSTER)可以改变表和索引的relfilenode值。例如，如果我们截断tbl表，PostgreSQL会为该表分配一个新的relfilenode(24579)，删除旧的数据文件(16384)，并创建一个新的文件(24579)。

postgres=# truncate tbl;
TRUNCATE TABLE
postgres=# select oid,relfilenode from pg_class where relname = 'tbl';
  oid  | relfilenode
-------+-------------
 16384 |       24579
(1 row)

在9.0或更高版本中，内置函数pg_relation_filepath很有用，因为该函数返回具有指定OID或名称的关系的文件路径名。

postgres=# select pg_relation_filepath('tbl');
 pg_relation_filepath
----------------------
 base/13892/24579
(1 row)

从上图可以看出，旧的数据文件16384不存在了。

当表和索引的文件大小超过1GB时，PostgreSQL会创建一个名为relfilenode的新文件 1 并使用它。如果新文件已经填满，下一个名为relfilenode的新文件 2 将被创建，以此类推。

[postgres@pgccc01 pgdata]$ ls -la base/13892/24579*
-rw------- 1 postgres postgres 1073741824 Aug 18 15:22 base/13892/24579
-rw------- 1 postgres postgres 1073741824 Aug 18 15:23 base/13892/24579.1
-rw------- 1 postgres postgres 1073741824 Aug 18 15:24 base/13892/24579.2
-rw------- 1 postgres postgres  403554304 Aug 18 15:25 base/13892/24579.3
... ...

在构建PostgreSQL时，可以使用选项 --with-segsize来更改表和索引的最大文件大小。

仔细查看数据库的子目录，你会发现每个表都有两个相关的文件，分别以' _fsm'和' _vm'后缀。它们称为空闲空间映射(free space map)和可见性映射(visibility map)，分别存储了表文件中每一页上的空闲空间容量和可见性信息(更详细的信息，请参见5.3.4节和6.2节)。索引只有单独的空闲空间映射，没有可见性映射。

一个具体的例子如下所示:

postgres=# select pg_relation_filepath('tbl');
 pg_relation_filepath
----------------------
 base/13892/24579
(1 row)

postgres=# \q
[postgres@pgccc01 pgdata]$ ls -la base/13892/24579*
-rw------- 1 postgres postgres 1073741824 Aug 18 15:22 base/13892/24579
-rw------- 1 postgres postgres 1073741824 Aug 18 15:23 base/13892/24579.1
-rw------- 1 postgres postgres 1073741824 Aug 18 15:24 base/13892/24579.2
-rw------- 1 postgres postgres  403554304 Aug 18 15:25 base/13892/24579.3
-rw------- 1 postgres postgres     909312 Aug 18 15:20 base/13892/24579_fsm
-rw------- 1 postgres postgres     114688 Aug 18 15:24 base/13892/24579_vm
[postgres@pgccc01 pgdata]$

它们在内部也可以称为每个关系的分支；空闲空间映射_fsm是表/索引数据文件的第一个分支(分支编号为1)，可见性映射_vm是表数据文件的第二个分支(分支编号为2)。数据文件的分支编号为0。

2.4 表空间

在PostgreSQL中，表空间是基础目录之外的一个额外的数据区域。在8.0版本引入了该功能。

图1.3展示了表空间的内部布局，以及与主数据区的关系。

表空间是在发出CREATE tablespace语句时指定的目录下创建的，并且在该目录下将创建特定于版本的子目录(例如，PG_14_202011044)。特定于版本的命名方法如下所示。

PG _ 'Major version' _ 'Catalogue version number'

例如，如果你在'/home/postgres'创建一个表空间'tbs_baoyw'，则会在表空间下创建一个子目录，例如'PG_14_202107181'。

postgres=# create tablespace tbs_baoyw location '/home/postgres';
CREATE TABLESPACE

[postgres@pgccc01 ~]$ ls -la /home/postgres/tbs_baoyw
total 0
drwx------  3 postgres postgres  29 Aug 18 16:02 .
drwx------. 7 postgres postgres 198 Aug 18 16:01 ..
drwx------  3 postgres postgres  19 Aug 18 16:03 PG_14_202107181

表空间目录通过pg_tblspc子目录的符号链接寻址，链接名与表空间的OID值相同。

[postgres@pgccc01 ~]$ ls -la $PGDATA/pg_tblspc
total 4
drwx------   2 postgres postgres   32 Aug 18 16:04 .
drwx------. 19 postgres postgres 4096 Aug 18 14:51 ..
lrwxrwxrwx   1 postgres postgres   24 Aug 18 16:02 24589 -> /home/postgres/tbs_baoyw

如果在表空间下创建一个新的数据库(OID为16387)，则在版本子目录下创建其目录。

$ ls -l /home/postgres/tblspc/PG_14_202011044/
total 4
drwx------ 2 postgres postgres 4096 Apr 21 10:10 16387

如果您创建了一个新的表，它属于在基础目录下创建的数据库，首先在特定版本的子目录下创建与现有数据库OID相同的新目录，然后在创建的目录下放置新的表文件。

postgres=# create tablespace new_tblspc location '/home/postgres';
CREATE TABLESPACE
postgres=# create table newtbl (id int,name text) tablespace new_tblspc;
CREATE TABLE
postgres=# select pg_relation_filepath('newtbl');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/24582/PG_14_202107181/13892/24583
(1 row)

postgres=#

3 堆表文件的内部布局

在数据文件内部(堆表和索引，以及空闲空间映射和可见性映射)，它被划分为固定长度的页(或块)，默认为8192字节(8 KB)。每个文件中的页从0开始顺序编号，这种编号称为块号(block number)。如果文件已经填满，PostgreSQL会在文件末尾添加一个新的空页来增加文件大小。

页面的内部布局取决于数据文件类型。在本节中，表的布局将被描述为后续章节需要的信息。

图1.4 堆表文件的页面布局

表中的页包含三种数据，描述如下。

1.堆元组heap tuple(s)——堆元组是一个记录数据本身。它们从页面底部开始按顺序堆叠。tuple的内部结构将在5.2节和第9章中描述，因为我们需要了解PostgreSQL中的并发控制(CC)和WAL。

2.行指针line pointer(s)——行指针长度为4字节，保存着指向每个堆元组的指针。它也称为项指针(item pointer)。

行指针形成一个简单的数组，起到索引元组的作用。每个索引从1开始顺序编号，称为偏移量(offset number)。当一个新的元组被添加到页面时，一个新的行指针也被压入数组，指向新的行。

3.头部数据header data:在页面的开头分配，由PageHeaderData结构定义的首部数据。长度为24字节，包含了页的一般信息。该结构的主要变量描述如下。

pd_lsn:该变量存储了上次修改该页所写入的XLOG记录的LSN。它是一个8字节无符号整数，与WAL(预写日志)机制有关。详情见第9章。

pd_checksum:该变量存储了该页的校验和值。(请注意，9.3或更高版本支持该变量;在之前的版本中，这部分存储了页面的timelineId。)

Pd_lower, pd_upper - Pd_lower指向行指针的末尾，而pd_upper指向最新的堆元组的开始。

pd_special:该变量用于索引。在表中的页中，它指向页的末尾。(在索引内的页面中，它指向特殊空间的开始，该数据区域仅由索引保存，并包含根据索引类型类型(如B-tree、GiST、GiN等)的特定数据。)

行指针结束和最新元组开始之间的空白称为空闲空间或空洞。

要在表中标识元组，内部会使用tuple identifier (TID)。TID由一对值组成:包含元组的页的块号，以及指向元组的行指针的偏移量。其用法的一个典型例子是index。详情参见1.4.2节。

结构PageHeaderData定义在src/include/storage/bufpage.h中。

在计算机科学领域，这种类型的页称为槽页(slot page)，其中的行指针对应于一个槽数组。

此外，堆元组的大小大于2 KB(约为8 KB的1/4)，使用一种称为TOAST (oversize - attribute Storage Technique)的方法存储和管理。详细信息请参考PostgreSQL文档 TOAST 。

4 写和读元组的方法

在本章的最后，我们将介绍读写堆元组的方法。

4.1 写堆元组

假设一个表由一页组成，其中只包含一个堆元组。该页的pd_lower指向第一行指针，而行指针和pd_upper都指向第一个堆元组。如图1.5(a)所示。

当第二个元组被插入时，它被放在第一个元组之后。第二行指针被压入到第一行指针上，它指向第二个元组。pd_lower指向第二行指针，而pd_upper指向第二个堆元组。见图1.5(b)。该页中的其他首部数据(例如，pd_lsn、pg_checksum、pg_flag)也会重写为适当的值。更多细节请参见5.3节和第9章。

图1.5 写一个堆元组。

4.2 读取堆元组

下面概述两种典型的访问方法，顺序扫描和b树索引扫描。

顺序扫描 - 通过扫描每个页中的所有行指针，顺序读取所有页中的所有元组。见图1.6(a)。

B-tree索引扫描 - 索引文件包含索引元组，每个元组由一个索引键和一个指向目标堆元组的TID组成。如果已经找到了包含您要查找的键的索引元组，PostgreSQL将使用获得的TID值读取所需的堆元组。(在b树索引中查找索引元组的方法在这里不解释，因为它很常见，并且这里的空间有限。请参阅相关材料。)例如，在图1.6(b)中，获得的索引元组的TID值为` (block = 7, Offset = 2) `。这意味着目标堆元组是表中第7页中的第2个元组，因此PostgreSQL可以读取所需的堆元组，而无需对页面进行不必要的扫描。

图1.6 顺序扫描和索引扫描。

索引内部

本文档不详细解释索引。要理解它们，我建议阅读下面这些有价值的文章:

Indexes in PostgreSQL — 1

Indexes in PostgreSQL — 2

Indexes in PostgreSQL — 3 (Hash)

Indexes in PostgreSQL — 4 (Btree)

Indexes in PostgreSQL — 5 (GiST)

Indexes in PostgreSQL — 6 (SP-GiST)

Indexes in PostgreSQL — 7 (GIN)

Indexes in PostgreSQL — 9 (BRIN)

PostgreSQL还支持TID-Scan、Bitmap-Scan和Index-Only-Scan。

TID- scan是一种通过指定元组的TID直接访问元组的方法。例如，要在表的第0个页面中找到第1个元组，执行如下查询:

ampledb=# SELECT ctid, data FROM sampletbl WHERE ctid = '(0,1)';
 ctid  |   data    
-------+-----------
 (0,1) | AAAAAAAAA
(1 row)

第7章将详细介绍Index-Only-Scan。

总结一下：

1.PostgreSQL数据库是数据库对象的集合。这里的对象是指用于存储或引用数据的一种数据结构。如表、索引、序列、视图、函数等。在PostgreSQL数据库中，数据库本身也是数据库对象，它们在逻辑上是彼此分离的。如database1的table、index属于database1，database2的table、index属于database2。逻辑结构。

2.PostgreSQL数据库集簇，其实在本质上，是一个文件目录，即基础目录（你看的见的），它包含了一系列的子目录与文件。当你执行initdb命令，进行PostgreSQL的初始化操作时，就会在指定的目录下创建基础目录，生成一个新的数据库集簇。通常，基础目录的路径被配置到环境变量PGDATA中。物理结构。

3.在不转换数据库 或 数据库用户的情况下，所有的操作都在数据库 postgres 用户 postgres 下。

4.当你创建表空间时，会生成两个目录。一个是创建时指定目录下生成特定版本的子目录，如PG_14_202107181；另一个是在$PGDATA/pg_tblspc下生成链接名与表空间oid值相同的软链接寻址。

5.对于表的TRUNCATE/REINDEX/CLUSTER操作都可以改变其relfilenode值，并生成新relfilenode值，删除旧的relfilenode值相关的数据文件，创建新的relfilenode值的数据文件。

6.每个表都有两个相关的文件，分别以' _fsm'和' _vm'后缀。它们称为空闲空间映射(free space map)和可见性映射(visibility map)，分别存储了表文件中每一页上的空闲空间容量和可见性信息。

7.数据库，属于用户 postgres

8.表空间和表，属于 postgres