pg数据库学习知识要点分析-1

知识要点1 对象标识OID

在PostgreSQL内部，所有的数据库对象都通过相应的对象标识符（object identifier，oid）进行管理，这些标识符是无符号的4字节整型。数据库对象与相应oid 之间的关系存储在对应的系统目录中，依具体的对象类型而异。例如数据库和堆表对象的 oid 分别存储在pg_database和pg_class中，因此，当你希望找出oid时，可以执行以下查询：

​​​​​​sampledb=# SELECT datname, oid FROM pg_database WHERE datname = 'sampledb';
​​​​ sampledb=# SELECT relname, oid FROM pg_class WHERE relname = 'sampletbl';
​​​​ 

OID不变，但是relfilenode在进行ddl操作后会发生变化。

知识要点2 软件物理结构

1 PGBASE,PGDATA,CONF文件。

4个配置文件

pg_hba.conf 控制PG数据库客户端认证

pg_ident.conf控制用户名映射。

postgresql.conf配置参数

postgresql.auto.conf存储使用alter system调整的参数

表空间文件结构布局

select pg_relation_filepath(sampletbl);

postgres=# select pg_relation_filepath('pg_class');
 base/13593/1259
 

PG中表空间是基础PGDATA目录之外的附加数据区域。8.0版本引入该功能。

本身的初始化数据目录要对外面的表空间有一个管理，那么pg_tblspc就实现了功能。

create tablespace tbs1 location '/home/postgres/datafile/tbs1';

[postgres@localhost pg_tblspc]$ pwd
/home/postgres/PGDATA/pg_tblspc
[postgres@localhost pg_tblspc]$ ls -la
lrwxrwxrwx  1 postgres postgres   28 May  4 15:34 16388 -> /home/postgres/datafile/tbs1
[postgres@localhost pg_tblspc]$ 

create table test02 (id int) tablespace tbs1;

insert into test02 select 3;

postgres=# select oid,spcname from pg_tablespace;
  oid  |  spcname   
-------+------------
  1663 | pg_default
  1664 | pg_global
 16388 | tbs1
(3 rows)

postgres=# select oid,reltablespace,relfilenode,relowner from pg_class where relname='test02';
  oid  | reltablespace | relfilenode | relowner 
-------+---------------+-------------+----------
 16392 |         16388 |       16392 |       10
(1 row)

[postgres@localhost 13593]$ pwd
/home/postgres/datafile/tbs1/PG_12_201909212/13593  --这个目录是个啥？？
[postgres@localhost 13593]$ ls
16392

postgres=# 

表文件路径查询：

select pg_relation_filepath('test02');

[postgres@localhost 13593]$ psql
psql (12.18)
Type "help" for help.

postgres=# select pg_relation_filepath('test02');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16388/PG_12_201909212/13593/16392
(1 row)

postgres=# 

执行 CREATE TABLESPACE语句会在指定的目录下创建表空间。在该目录下还会创建版本特定的子目录（例如PG_9.4_201409291）。版本特定的命名方式为：
​​​​​​PG_主版本号_目录版本号​​

例如在/pg/data/ 目录中创建表空间 new_TBLSPC 对应的OID为 16384，则会在表空间下创建一个名为 pg_version_banben的字目录。

cd /pg/data/pg_11.2_20220121/16384

如果在表空间中创建新表，则新表对应的OID值 创建在/pg/data/pg_11.2_20220121/16384中，而且对应的数据段大小可以在初始化时制定大小，超过大小后分裂为OID.1,OID.2文件（热点快数据优化？）。还有表及其他对象对应的管理文件（空闲空间映射，可见性映射等fsm和vm文件）

$PGDATA/pg_tblspc/16384   -------------> /pg/data/

堆表文件的内部布局

数据文件包括（堆表，索引，空间空间映射，可见性空间映射文件 ）内部被划分为固定大小的页

或者叫做区块 8KB。从0开始编号叫做区块号。

为了识别表中的元组，数据库内部会使用元组标识符（tuple identifier TID）

• TID：由一组值组成分别为元组所属页面的区块号和指向元组的行指针偏移号，TID典型应用为索引。
• 大小超过2kb的堆元组会使用toast 超大属性存储技术。

PG进程和内存架构

C/S架构，客户端/服务器风格

pgserver实际上是一些列协同工作的进程集合。

[postgres@localhost ~]$ ps -ef|grep postgres |grep -v grep|grep 1*
postgres 30957     1  0 May03 ?        00:00:00 /home/postgres/postgresql/bin/postgres -D /home/postgres/PGDATA
postgres 30958 30957  0 May03 ?        00:00:00 postgres: logger   
postgres 30960 30957  0 May03 ?        00:00:00 postgres: checkpointer   
postgres 30961 30957  0 May03 ?        00:00:00 postgres: background writer   
postgres 30962 30957  0 May03 ?        00:00:00 postgres: walwriter   
postgres 30963 30957  0 May03 ?        00:00:00 postgres: autovacuum launcher   
postgres 30964 30957  0 May03 ?        00:00:00 postgres: stats collector   
postgres 30965 30957  0 May03 ?        00:00:00 postgres: logical replication launcher   
[postgres@localhost ~]$ 

本地内存区域和共享内存区域。

[postgres@localhost ~]$ ipcs -ma|grep postgres
0x0052e2c1 1376262    postgres   600        56         6                       
[postgres@localhost ~]$ 

本地内存区域：由每个后端进程分配供自己使用 类似pga。

如何去查看进程占用的内存？？？

linux用top

1 PG时CS架构，采用多进程架构。

2 PG服务进程是所有进程的父进程。postgres server process.

3 后端进程，backed process负责处理客户端发出的查询语句。

• 重写器：重写器会根据存储在pg_rules中的规则对查询进行转换。
• pg不支持提示hint。

4 各种后台进程background process 负责执行各种数据库管理任务（例如清理过程和存档过程）

5 各种复制进程（replication associate process负责复制流。）

6 pg_ctl start会启动postgres server process父进程，它会在内存中分配共享内存区域，启动各种后台进程。如果有必要还要启动replication并等待客户端的连接请求。有客户端连接它就会启动一个后端进程，然后由后端进程处理该客户端的所有查询请求。（oracle 1-1的专享连接模式）

7 PG没有原生的资源池，可以采用池化中间件pgbouncer pgpool-II

PG后台进程解释

登录pg服务器，使用ps -ef|grep postgres可以发现有很对pg进程以下简要解释下各个后台进程的含义。

background writer 本进程负责吧共享池中的脏页刷新到磁盘中。

checkpointer负责检查点。

autoavcuum launcher自动清理死元组清理工作

WAL writer 预写日志管理。

statistic collector收集统计信息，例如pg_STAT_ACTIVITY,PG_STAT_DATABASE等。

logging collector日志采集统计

archiver wal日志归档。

backed process，例如10.228.11.1:577423 progres格式。

单表查询的代价估计

所有被执行的操作都有着相应的代价函数

cost_seq_scan（）顺序扫描

cost_index（）索引扫描

对于cost，pg定义为3种，1 启动代价，运行代价，总代价。

启动代价：在读取到第一条元组前花费的代价。索引扫描：读取目标表索引页获取第一个元组的代价。

运行代价：获取全部元组的代价。

总代价：前面2个的和。explain，命令只显示启动代价和总代价。

postgres=# explain select * from test02;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on test02  (cost=0.00..1.01 rows=1 width=4)
(1 row)

启动代价为0.00，总代价为1.01

顺序扫描代价评估

postgres=# create table tbl(id int primary key,data int);
CREATE TABLE
postgres=# create index tbl_data_idx on  tbl(data);
CREATE INDEX
postgres=# insert into tbl select generate_series(1,10000),generate_series(1,10000);
INSERT 0 10000
postgres=# analyze;
ANALYZE
postgres=# \d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 data   | integer |           |          | 
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (id)
    "tbl_data_idx" btree (data)

postgres=# 

postgres=# select relpages,reltuples from pg_class where relname='tbl';
 relpages | reltuples 
----------+-----------
       45 |     10000
(1 row)

postgres=# 

run_cost=（cpu_tuple_cos+cpu_operator_cost）*N（tuples）+seq_page_cost*N（pages）

而3个cost值在配置文件postgresql.conf中都是可以设置的，存在默认值的。

0.01/0.0025/1.0

（0.01+0.0025）*1000+1.0*45=170

postgres=# explain select * from tbl where id<8000;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..170.00 rows=7999 width=8)
   Filter: (id < 8000)-----表级别过滤谓词，并不影响扫描的page数。
(2 rows)

postgres=# 

索引扫描代价评估

postgres=# select relpages,reltuples from pg_class where relname='tbl_data_idx';
 relpages | reltuples 
----------+-----------
       30 |     10000
(1 row)
 

事务标识（并发控制-一致性和隔离性）

ACID

原子性一个事务要不全成功要不全失败。

一致性：

隔离性：

持久性：wal日志

每种技术都有不同的变体，在MVCC中每个写操作都会创建一个新版本的数据项，并保留其就版本（undo，page保持标记等），当其他事务读取数据对象时，系统会选择一个版本让它读取，确保事务间相互隔离。（读不阻塞写，写不阻塞读MVCC）

PG的MVCC是怎么样的那？

快照隔离。（snapshot isolation SI）

像oracle mysql都是用undo来实现SI。而PG采用的是新数据被插入到相关的表页中，读取对象时，postgresql根据可见性规则（mysql+read view+undo？），为每个事务选择合适的对象版本作为响应。

每个事务开始时，事务管理器会为其分配一个事务标识 tid,最大值42亿，32位无符号整型。

select txid_current();--查看当前事务的txid。012表示预留的txid。

txid可以互相比较大小，例如txid=100 则小于100的属于过去，大于100的属于未来。

因为txid在逻辑上是无限的，而实际系统中的txid空间不足（4B整型的取值空间大小约42亿），因此PostgreSQL将txid空间视为一个环。对于某个特定的txid，其前约21亿个txid属于过去，其后约21亿个txid属于未来

提交日志PG_XACT

postgresql在提交日志中CLOG，中保存事务的状态，提交日志分配在内存中，并用于事务处理的全过程，

事务状态

postgresql定义了4种事务状态，in_process，commited，aborted和sub commited。

提交日志如何工作

提交日志是一个数组，在共享内存中一些列8K页面组成。数组的序列号代表的事务的标识tid，其内容则是事务的状态，

T1：txid 200提交；txid 200的状态从IN_PROGRESS变为COMMITTED。
T2：txid 201中止；txid 201的状态从IN_PROGRESS变为ABORTED。
txid 不断前进，当 CLOG空间耗尽无法存储新的事务状态时，就会追加分配一个新的页面。
当需要获取事务的状态时，PostgreSQL将调用相应内部函数读取CLOG，并返回所请求事务的状态。
5.4.3 提交日志的维护
当PostgreSQL关机或执行存档过程时，CLOG数据会写入pg_clog子目录下的文件中（注意，在10.0版本中，pg_clog被重命名为pg_xact）。这些文件被命名为0000，0001等。文件的最大尺寸为256 KB。例如当CLOG使用8个页面时，从第1页到第8页的总大小为64 KB，这些数据会写入文件0000（64 KB）中，而当CLOG使用37个页面时（296 KB），数据则会写入0000和0001两个文件中，其大小分别为256 KB和40 KB。
当PostgreSQL启动时会加载存储在pg_clog（pg_xact）中的文件，用其数据初始化CLOG。
CLOG的大小会不断增长，因为只要CLOG一填满就会追加新的页面。但并非所有数据都是必要的。

clog是如何删除的那？？？

CLOG存储着事务的状态。当更新pg_database.datfrozenxid时， PostgreSQL会尝试删除不必要的CLOG文件。注意，相应的CLOG页面也会被删除。
图 6.7 给出了一个例子。如果 CLOG 文件 0002 中包含最小的 pg_database.datfro zenxid，则可以删除旧文件（0000 和0001），因为存储在这些文件中的所有事务在整个数据库集簇中已经被视为冻结了。

元组

读取

两种典型的读取方式：顺序扫描，索引扫描。

通过扫描每一页中的行指针，依次读取所有页面的数据。

B树扫描 ：索引文件包含 索引元组，索引元组由一对健值组成，健值和TID。

pg还支持TID扫描 ，位图扫描 ，仅索引扫描。TID扫描时一种通过索引元祖直接获取数据的扫描。

或者例如使用select语句指定。

select * from test02 where ctid='(0,1)'---第0个页面的第一个元组信息。

postgres=# select * from test02 where ctid='(0,1)';
 id 
----
  3
(1 row)

postgres=# explain select * from test02 where ctid='(0,1)';
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on test02  (cost=0.00..1.01 rows=1 width=4)
   Filter: (ctid = '(0,1)'::tid)
(2 rows)

postgres=# explain select * from test02 where ctid='(0,2)';
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on test02  (cost=0.00..1.01 rows=1 width=4)
   Filter: (ctid = '(0,2)'::tid)
(2 rows)

postgres=# select * from test02 where ctid='(0,2)';
 id 
----
(0 rows)

postgres=#

元祖包含的隐藏列

txid xmin xmax ctid 数据。（前面只显示核心的4个字段。）

postgres=# select txid_current(),xmin,xmax,ctid from pg_class limit 10;
          488 |   36 |    0 | (0,46)
          488 |  273 |    0 | (0,47)
          488 |    1 |    0 | (1,19)
          488 |    1 |    0 | (1,20)
          488 |    1 |    0 | (1,21)
          488 |    1 |    0 | (1,22)
          488 |    1 |    0 | (1,23)
          488 |    1 |    0 | (1,24)
          488 |    1 |    0 | (1,25)
          488 |    1 |    0 | (1,26)

postgres=# 

postgres=# 

元组的增删改FSM：用于插入和更新元组的自由空间映射。

通常不需要的元组，在POSTgres中被称为死元组。

增：

在插入中，新元组直接插入目标表的page中，如图所示：

Tuple_1：
· t_xmin设置为99，因为此元组由txid=99的事务所插入。
· t_xmax设置为0，因为此元组尚未被删除或更新。
· t_cid设置为0，因为此元组是由txid=99的事务所执行的第一条命令插入的。
· t_ctid设置为(0,1)，指向自身，因为这是该元组的最新版本。

pageinspect
PostgreSQL自带了一个第三方贡献的扩展模块pageinspect，可用于检查数据库页面的具体内容。
​​​​​​testdb=# CREATE EXTENSION pageinspect;
​​​​

CREATE EXTENSION 
​​​​

testdb=# CREATE TABLE tbl (data text);
​​​​

CREATE TABLE
​​​​

testdb=# INSERT INTO tbl VALUES(A);


​​​​INSERT 0 1
​​​​

testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid
​​​​                FROM heap_page_items(get_raw_page(tbl, 0));
​​​​

tuple | t_xmin | t_xmax | t_cid | t_ctid
​​​​-------+--------+--------+-------+--------
​​​​    

1 |      99 |       0 |      0 | (0,1)
​​​​(1 row)​​

12.18环境

postgres=# CREATE EXTENSION pageinspect;
​​​​ 
ERROR:  could not open extension control file "/home/postgres/postgresql/share/extension/pageinspect.control": No such file or directory
postgres-# 
 

实验

postgres=# create table test(id int);
CREATE TABLE

postgres=# insert into test select 1;
INSERT 0 1

postgres=# select txid_current(),xmin,xmax,ctid from test limit 10;
 txid_current | xmin | xmax | ctid  
--------------+------+------+-------
          491 |  490 |    0 | (0,1)

postgres=# insert into test select 2;
INSERT 0 1
postgres=# select txid_current(),xmin,xmax,ctid from test limit 10;
 txid_current | xmin | xmax | ctid  
--------------+------+------+-------
          493 |  490 |    0 | (0,1)  ----490事务插入
          493 |  492 |    0 | (0,2)   ----492事务插入

postgres=# update test set id=10;
UPDATE 2

postgres=# select txid_current(),xmin,xmax,ctid from test limit 10;
 txid_current | xmin | xmax | ctid  
--------------+------+------+-------
          495 |  494 |    0 | (0,3)
          495 |  494 |    0 | (0,4)  --全部被494事务更新update。

删

在删除操作中，目标元组只是在逻辑上被标记为删除。目标元组的t_xmax字段将被设置成delete命令事务的txid。

假设tuple_1被事务111删除，在这种情况下Tuple_1的首部字段被t_xmax设置成111，如果事务txid=111已经提交，就不一定要tuple_1元组，通常不需要的元组此时已经成为死元组。

死元组最终将从页面中被移除，清除死元组的过程称为：VACUUM。

改

在更新操作中，PG在逻辑上实际执行的是删除最新的元组，并插入一条新的元组。

假设由txid=99的事务插入的行，被txid=100的事务更新两次。
当执行第一条UPDATE命令时，Tuple_1的t_xmax被设为txid 100，在逻辑上被删除，然后Tuple_2被插入，接下来重写Tuple_1的t_ctid以指向Tuple_2。Tuple_1和Tuple_2的头部字段设置如下。
Tuple_1：
· t_xmax被设置为100。
· t_ctid从(0,1)被改写为(0,2)。
Tuple_2：
· t_xmin被设置为100。
· t_xmax被设置为0。
· t_cid被设置为0。
· t_ctid被设置为(0,2)。
当执行第二条UPDATE命令时，和第一条UPDATE命令类似，Tuple_2被逻辑删除，Tuple_3被插入。Tuple_2和Tuple_3的首部字段设置如下。
Tuple_2：
· t_xmax被设置为100。
· t_ctid从(0,2)被改写为(0,3)。
Tuple_3：
· t_xmin被设置为100。
· t_xmax被设置为0。
· t_cid被设置为1。
· t_ctid被设置为(0,3)。
与删除操作类似，如果txid=100的事务已经提交，那么Tuple_1和Tuple_2就成了死元组，而如果txid=100的事务中止，Tuple_2和Tuple_3就成了死元组。

事务快照

select txid_current_snapshot();

xmin：xmax：xip_list。

postgres=# select txid_current_snapshot();
 489:489:

postgres=# 

事务快照是一个数据集，存储某个特定事务在某个特定时间所看到的事务状态信息。哪些事务处于活跃状态（事务正在进行或者还没开始）。事务快照在PostgreSQL内部的文本表示格式为100：100，

100：100 意味着txid<100的事务处于非活跃状态，txid>=100的事务处于活跃状态。

清理过程VACUUM

为了移除死元组，清理过程有另种模式分别为并发清理与完整清理，清理过程会删除表文件每个页面的死元组而其他事务可以在运行时继续读取该表。

完整清理：不仅移除死元组，还会对活的元组进行碎片整理，此时表不可访问。

在8.0以前需要手动清理，直到出现autovacuum守护进程实现自动化。

由于清理过程需要全表扫描，因此代价过于高昂。 可见性映射提高了（VM）移除死元组的效率，并在后期的版本中VM增强。

postgresql  FDW

需要进行配置扩展。