版权声明:
本文作者是日本的Hironobu SUZUKI
原文地址
中文版图书 PostgreSQL指南:内幕探索
本文旨在学习目的,禁止用于商业目的。
数据库集群、数据库和表
本文将介绍以下内容
- 数据库集群的逻辑结构
- 数据库集群的物理结构
- 堆表文件的内部布局
- 将数据写入表格和读取表格数据的方法
1. 数据库集群的逻辑结构
数据库集群database cluster
是由 PostgreSQL 服务器管理的数据库集合。
如果你现在第一次听到这个定义,你可能会好奇,但是术语数据库集群
在 PostgreSQL 中并不意味着一组数据库服务器
。
PostgreSQL 服务器在单个主机上运行,并管理单个数据库集群。
图1.1显示了数据库集群的逻辑结构。
数据库是数据库对象的集合。
在关系数据库理论中,数据库对象是一种用于存储或引用数据的数据结构。
(堆)表是一个典型的例子,还有更多类似于索引、序列、视图、函数等的例子。
在 PostgreSQL 中,数据库本身也是数据库对象,并且在逻辑上彼此分离。所有其他数据库对象(例如,表、索引等)都属于各自的数据库。
PostgreSQL 中的所有数据库对象都由各自的对象标识符 object identifiers OIDs
进行内部管理,这些对象标识符是无符号的4字节整数。
数据库对象和各自 oid 之间的关系存储在适当的系统目录中,具体取决于对象的类型。
例如,数据库的oid和堆表的oid分别存储在 pg_database 和 pg_class 中,因此您可以通过发出以下查询来查找您想知道的 oid:
# db
SELECT datname, oid FROM pg_database WHERE datname = 'sampledb';
# heap table
SELECT relname, oid FROM pg_class WHERE relname = 'sampletbl';
2. 数据库集群的物理结构
数据库集群基本上就是一个称为基本目录base directory
的目录,它包含一些子目录和许多文件。
如果您执行 initdb 实用程序来初始化一个新的数据库集群,那么将在指定的目录下创建一个基本目录。
虽然它不是强制的,但是基本目录的路径通常被设置为环境变量的PGDATA。
图1.2显示了 PostgreSQL 中的数据库集群示例。
数据库是基本子目录下的子目录,每个表和索引都(至少)存储在它所属的数据库的子目录下的一个文件。
还有几个包含特定数据和配置文件的子目录。
尽管 PostgreSQL 支持表空间,但这个术语的含义与其他 RDBMS 不同。
- PostgreSQL 中的表空间是包含基本目录之外的一些数据的目录。
2.1 数据库集群的布局
表1.1列出了部分文档中的主要文件和子目录:
文件 | 描述 |
---|---|
PG_VERSION | 包含 PostgreSQL 主版本号的文件 |
pg_hba.conf | 控制 pogresql 客户端认证的文件 |
pg_ident.conf | 控制 PostgreSQL 用户名映射的文件 |
postgresql.conf | 用于设置配置参数的文件 |
postgresql.auto.conf | 用于存储在 altersystem (9.4或更高版本)中设置的配置参数的文件 |
postmaster.opts | 记录服务器上次启动时使用的命令行选项的文件 |
表1.1: 基本目录下文件和子目录的布局(来自官方文档)
子目录 | 描述 |
---|---|
base/ | 包含每个数据库子目录的子目录 |
global/ | 包含集群范围表的子目录,如 pg_database 和 pg_control |
pg_commit_ts/ | 包含事务提交时间戳数据的子目录。版本9.5或更高版本 |
pg_clog/ | (版本9.6或更早) 包含事务提交状态数据的子目录。 |
pg_dynshmem/ | 包含动态共享内存子系统使用的文件的子目录。9.4或更高版本 |
pg_logical/ | 包含逻辑解码状态数据的子目录。版本9.4或更高版本 |
pg_multixact/ | 包含多事务状态数据(用于共享行锁)的子目录 |
pg_notify/ | 包含 LISTEN/NOTIFY 状态数据的子目录 |
pg_repslot/ | 子目录包含replication slot 复制槽 ,9.4或较后版本 |
pg_serial/ | 包含已提交的可序列化事务(9.1或更高版本)信息的子目录 |
pg_snapshots/ | 包含导出快照(9.2或更高版本)的子目录。 PostgreSQL 函数 pg_export_snapshot 在这个子目录中创建一个快照信息文件 |
pg_stat/ | 包含统计子系统的永久文件的子目录 |
pg_stat_tmp/ | 包含统计子系统的临时文件的子目录 |
pg_subtrans/ | 包含子事务状态数据的子目录 |
pg_tblspc/ | 包含指向表空间的符号链接的子目录 |
pg_twophase/ | 包含已准备事务的状态文件的子目录 二段锁 |
pg_wal/ | (Version 10或更高版本) ,包含 WAL (Write Ahead Logging)段文件的子目录pg_xlog |
pg_xact/ | 包含事务提交状态数据的子目录。 |
pg_xlog/ | 包含 WAL (Write Ahead Logging)段文件的子目录 |
2.2 数据库布局
数据库是base子目录下的子目录; 数据库目录的名称与相应的 oid 相同。
例如,当数据库 sampledb 的 OID 为16384时,其子目录名为16384。
cd $PGDATA
ls -ld base/16384
2.3 索引和表的关联文件布局
大小小于**1 GB* 的每个表或索引都是存储在它所属的数据库目录下的单一文件。
作为数据库对象的表和索引由各个 oid 内部管理,而这些数据文件由变量 relfilename 管理。
表和索引的文件码值基本上但并不总是匹配各自的 oid,下面将详细描述。
SELECT relname, oid, relfilenode FROM pg_class WHERE relname = 'sampletbl';
-- return
relname | oid | relfilenode
-----------+-------+-------------
sampletbl | 18740 | 18740
从上面的结果中,可以看到 oid 和 relfilename 值都是相等的。您还可以看到表 sampletbl 的数据文件路径为base/16384/18740
。
su postgres
cd $PGDATA
ls -la base/16384/18740
通过发出一些命令(例如,TRUNCATE、 REINDEX、 CLUSTER) ,表和索引的文件码值可以更改。
例如,如果我们清空表 sampletbl,PostgreSQL 将一个新的文件 relode (18812)分配给该表,删除旧的数据文件(18740) ,并创建一个新的文件(18812)。
TRUNCATE sampletbl;
SELECT relname, oid, relfilenode FROM pg_class WHERE relname = 'sampletbl';
-- return
relname | oid | relfilenode
-----------+-------+-------------
sampletbl | 18740 | 18812
在版本9.0或更高版本中,内置函数 pg _ relation _ filepath 非常有用,因为该函数返回具有指定 OID 或名称的关系的文件路径名。
SELECT pg_relation_filepath('sampletbl');
-- return
pg_relation_filepath
----------------------
base/16384/18812
(1 row)
当表和索引的文件大小超过1 GB 时,PostgreSQL 创建一个名为 relfilename.1的新文件并使用它。
如果新文件已经填满,那么将创建下一个名为 relfilename.2的新文件,依此类推。
cd $PGDATA
ls -la -h base/16384/19427*
# return
-rw------- 1 postgres postgres 1.0G Apr 21 11:16 data/base/16384/19427
-rw------- 1 postgres postgres 45M Apr 21 11:20 data/base/16384/19427.1
- 在构建 PostgreSQL 时,表和索引的最大文件大小可以使用配置选项——
with-segsize
更改。
仔细查看数据库子目录,您会发现每个表都有两个分别以fsm
和vm
作为后缀的相关文件。
这些被称为自由空间映射free space map
和可见性映射visibility map
,
分别存储表文件中每个页的自由空间容量和可见性信息。索引只有单独的自由空间映射,没有可见性映射。
下面是各具体的例子
cd $PGDATA
ls -la base/16384/18751*
-- return
-rw------- 1 postgres postgres 8192 Apr 21 10:21 base/16384/18751
-rw------- 1 postgres postgres 24576 Apr 21 10:18 base/16384/18751_fsm
-rw------- 1 postgres postgres 8192 Apr 21 10:18 base/16384/18751_vm
它们在内部也可以被称为每个关系的分支;
自由空间映射是表/索引数据文件的第一个分支(分支数字为1) ,
可见性映射是表的数据文件的第二个分支(分支数字为2)。
数据文件的fork编号为0。
2.4 表空间 table space
PostgreSQL 中的表空间是基本目录之外的附加数据区域。这个功能已经在8.0版本中实现。
图1.3显示了表空间的内部布局以及与主数据区域的关系。
在发出 CREATE TABLESPACE 语句时指定的目录下创建表空间,并在该目录下创建特定于版本的子目录
例如,如果在‘/home/postgres/tblspc’创建一个表空间‘ new _ tblspc’,其旧的是16386,那么将在表空间下创建一个子目录,
如PG_14_202011044
。
$ ls -l /home/postgres/tblspc/
# return
total 4
drwx------ 2 postgres postgres 4096 Apr 21 10:08 PG_14_202011044
表空间目录通过 pg_tblspc 子目录中的符号链接寻址,链接名称与表空间的 OID 值相同。
ls -l $PGDATA/pg_tblspc/
# return
total 0
lrwxrwxrwx 1 postgres postgres 21 Apr 21 10:08 16386 -> /home/postgres/tblspc
如果创建一个新表,它属于在base/目录下创建的数据库,
首先,在版本特定的子目录下创建一个名称与现有数据库 OID 相同的新目录,
然后将新表文件放在创建的目录下。
CREATE TABLE newtbl (.....) TABLESPACE new_tblspc;
SELECT pg_relation_filepath('newtbl');
-- return
pg_relation_filepath
---------------------------------------------
pg_tblspc/16386/PG_14_202011044/16384/18894
3. 堆表文件的内部布局
在数据文件(堆表和索引,以及空闲空间映射和可见性映射)中,它被分为固定长度的页 page(或块 block) ,默认为8192字节(8 KB)。
每个文件中的那些页按顺序从0开始编号,这样的号码称为块号。
如果文件已经填满,PostgreSQL 会在文件末尾添加一个新的空白页以增加文件大小。
页的内部布局取决于数据文件类型。在这一部分中,表格布局被描述为在下面的章节中需要的信息。
一个表格中的一个页包含三种数据,描述如下:
-
heap tuple(s) 堆元组
堆元组是记录数据本身。
它们是按照页面底部的顺序入栈的。
元组的内部结构描述需要并发控制Concurrency Control(CC)和 预写日志 Write Ahead Log(WAL) 作为知识准备,后面的章节会进行介绍。 -
line pointer(s) 行指针
行指针的长度为4字节,并保存指向每个堆元组的指针。它也被称为item pointer 项集指示器.
指针形成一个简单的数组,它起到元组索引的作用。每个索引按顺序从1开始编号,并调用offset number 偏移数.
当一个新的元组被添加到页中时,一个新的行指针也会被推送到数组中以指向新的元组 -
header data 报头数据
由结构定义的头数据
在页的开头分配。它有24个字节长,包含有关页的一般信息。结构的主要变量描述如下- pd_lsn
此变量存储由此页上次更改所写入的 XLOG 记录的 LSN。它是一个8字节的无符号整数,与 WAL (预写式日志)机制相关 - pd_checksum
此变量存储此页的校验和值 - pd_lower, pd_upper
pd_lower 指向行指针的末尾,pd_upper 指向最新堆元组的开头 - pd_special
此变量用于索引。在表中的页面中,它指向页面的末尾。(在索引内的页中,它指向特定空间的开头,即仅由索引保存的数据区域,并根据索引类型(如 B 树、 GiST、 GiN 等)包含特定数据。)
- pd_lsn
在直线指针的末尾和最新元组的开始之间的空白区域称为自由空间或空洞 free space or hole。
要标识表中的元组,内部使用元组标识符(TID)。
TID 包含一对值: 包含元组的页的块号和指向元组的行指针的偏移量。使用它的一个典型例子是 index
- 在
src/include/storage/bufpage.h
中定义了 PageHeaderData 结构。 - 在计算机科学领域,这种类型的页被称为槽页slotted page,线指针对应于槽数组slot array。
- 此外,使用 TOAST (超大型对象存储技术 The Oversized-Attribute Storage Technique)方法存储和管理大小大于2 KB (大约8 KB 的1/4)的堆元组
4. 元组的读和写方法
4.1 写堆元组
假设一个由一个页组成的表只包含一个堆元组
这个页的 pd_lower 指向第一个行指针,行指针和 pd_upper 指向第一个堆元组
当插入第二个元组时,它位于第一个元组之后。第二行指针被推送到第一行指针上,并指向第二个元组。
Pd_lower 更改为指向第二行指针,pd_upper 更改为指向第二个堆元组。
此页面中的其他头部数据(例如,pd_lsn、 pg_checksum、 pg_flag)也被重写为适当的值
如下图
4.2 读取堆元组
这里概述了两种典型的存取方法,顺序扫描
和 b树索引扫描
:
- Sequential scan
扫描每页中的所有行指针,顺序读取所有页中的所有元组 - B-tree index scan
索引文件包含索引元组,每个索引元组由指向目标堆元组的索引键和 TID 组成。
如果找到了包含键的索引元组,则 PostgreSQL 使用获得的 TID 值读取所需的堆元组。例如,在图中,获得的索引元组的 TID 值为(block = 7,Offset = 2)
。这意味着目标堆元组是表中第7个页面中的第2个元组,因此 PostgreSQL 可以读取所需的堆元组,而无需在页面中进行不必要的扫描
- 本文篇幅有限,将在之后,单开一篇详细介绍posgtesql中的各种索引。