## 一、邏輯儲存結構

邏輯儲存結構通常指表、索引、檢視、函式等邏輯物件（也稱資料庫物件）

![image-20220120153531236](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220120153531236.png)

> 參考之前寫的文章[資料庫物件](https://www.modb.pro/db/178399)

## 二、物理儲存結構

物理儲存結構表示資料庫在物理層面上是如何儲存的。

在初始化`initdb`資料庫集簇(database cluster)時會在指定目錄下建立基礎目錄（即通常所說的資料目錄，會被配置到環境變數PGDATA中）。所有相關組成資料庫的物理檔案的分佈：

![image-20220120154053628](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220120154053628.png)

![image-20220121100752130](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121100752130.png)

| 子目錄/檔案          | 描述                                                         |
| -------------------- | ------------------------------------------------------------ |
| base                 | 預設的資料庫目錄                                             |
| global               | 資料庫集簇中的所有表                                         |
| pg_commmit_ts        | 事務提交的時間戳目錄                                         |
| pg_dynshmem          | 動態共享記憶體目錄                                             |
| pg_logical           | 邏輯解碼的狀態資料                                           |
| pg_multixact         | 多個事務的狀態目錄                                           |
| pg_notify            | 包含系統發出的 LISTEN/NOTIFY 的目錄                          |
| pg_replslot          | 複製槽目錄                                                   |
| pg_serial            | 已提交的可序列化事務的資訊目錄                               |
| pg_snapshots         | 快照目錄(pg_export_snapshot函式匯出的快照資訊檔案)           |
| pg_stat              | 統計目錄                                                     |
| pg_stat_tmp          | 臨時統計目錄                                                 |
| pg_subtrans          | 子事務的狀態資料                                             |
| pg_tblspc            | 各個使用者自建表空間指向的軟連結符號目錄                       |
| pg_twophase          | 兩階段提交目錄                                               |
| pg_wal               | wal segment 目錄                                             |
| pg_xact              | 事務提交狀態的目錄                                           |
| pg_hba.conf          | 基於主機的認證配置檔案，用於配置允許哪些IP的主機通過哪種方式訪問資料庫 |
| pg_ident.conf        | 基於主機的對映檔案                                           |
| PG_VERSION           | 包含PostgreSQL主版本號                                       |
| postgresql.auto.conf | 儲存使用 `ALTER SYSTEM`修改的配置引數                        |
| postgresql.conf      | 預設的資料庫引數檔案                                         |
| postmaster.opts      | 記錄伺服器上次啟動時的相關密令資訊                           |
| postmaster.pid       | 當前資料庫執行的程式檔案                                     |

下面介紹一些常用的的目錄及檔案

### 1、base 目錄

##### 1.1 概述

base目錄儲存使用者建立的資料庫檔案，及隸屬於使用者資料庫的所有關係，比如表、索引等

##### 1.2 一級目錄
以資料庫oid命名的目錄
![image-20220121104303669](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121104303669.png)

##### 1.3 二級檔案

儲存某個資料庫內的所有relation，包括表、索引、檢視等。

二級子檔案分為三大類：

- 以`oid`命名的主資料檔案；
- 以`_fsm`結尾的空閒空間對映檔案;
- 以`_vm`結尾的可見性對映檔案。

![image-20220121105840757](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121105840757.png)

```plsql
-- 以 上圖中的 1247 為例：1247是普通表 pg_type 的oid
postgres=# select oid,relname,relkind from pg_class where oid = 1247;
 oid  | relname | relkind
------+---------+---------
 1247 | pg_type | r
(1 row)
--- relkind 值為 r：表示普通表
```

>[pg_class 表](https://www.postgresql.org/docs/14/catalog-pg-class.html)參考

- **主資料檔案**（具體介紹請看第三節`堆表檔案的內部佈局`）

- **FSM(FREE SPACE MAP)  - 空閒空間對映檔案**

- 儲存相應表或索引檔案中每個頁面上的空閒空間資訊。hash索引除外

- 詳情可檢視`src/backend/storage/freespace/README`

- 可使用擴充套件 `pg_freespace` 檢視特定表或索引上的空閒空間資訊。

```plsql
    lmj_test=> \c lmj_test postgres
    You are now connected to database "lmj_test" as user "postgres".
    lmj_test=# create extension pg_freespacemap ;
    CREATE EXTENSION
    lmj_test=# create table tab_fsm(id int,name varchar);
    CREATE TABLE
    lmj_test=# insert into tab_fsm select id,md5(id::varchar) from generate_series(1,10000) as id;
    INSERT 0 10000
    lmj_test=# select *,round(100*avail/8192 ,2) as "freespace ratio" from pg_freespace('tab_fsm') where avail !=0;
     blkno | avail | freespace ratio
    -------+-------+-----------------
        83 |  5440 |           66.00
    (1 row)
    ```

詳情可檢視官網：[pg_freespacemap擴充套件](https://www.postgresql.org/docs/current/pgfreespacemap.html)

- **VM(VISIBILITY MAP)可見性對映檔案**

- 儲存頁面的可見性資訊，包含所有活躍事務的元組，也包含需要凍結的一些元組

- 可使用擴充套件 `pg_freespace` 檢視

```plsql
    lmj_test=# create extension pg_visibility ;
    CREATE EXTENSION
    lmj_test=# create table tab_vm as select * from tab_fsm ;
    SELECT 10000
    -- 刪除前
    lmj_test=# select * from pg_visibility_map('tab_vm') where blkno = 0;
     blkno | all_visible | all_frozen
    -------+-------------+------------
         0 | t           | f
    -- 刪除 id<100 的元組
    lmj_test=# delete from tab_vm where id<100;
    DELETE 99
    -- 刪除後重新檢視:all_visible t-->f，此時元組不可見
    lmj_test=# select * from pg_visibility_map('tab_vm') where blkno = 0;
     blkno | all_visible | all_frozen
    -------+-------------+------------
         0 | f           | f
    (1 row)
    ```

詳情可檢視 [pg_visibility擴充套件](https://www.postgresql.org/docs/current/pgvisibility.html)

- 每一個堆元組都儲存兩位：如果第一位被設定，那麼表示頁面都是可見的，既然可見，那麼該頁面不包含任何清理的元組資訊。索引掃描可以使用索引元組及逆行查詢。如果第二位被設定，那麼vacuum將會跳過所有的頁面訪問，提高vacuum的速度。

>/*   src/backend/access/heap/visibilitymap.c */
    >
    >The visibility map is a bitmap with two bits (all-visible and all-frozen)per heap page. 
    >
    > * A set all-visible bit means that all tuples on the page areknown visible to all transactions, and therefore the page doesn't need tobe vacuumed. 
    > * A set all-frozen bit means that all tuples on the page arecompletely frozen, and therefore the page doesn't need to be vacuumed evenif whole table scanning vacuum is required (e.g. anti-wraparound vacuum).The all-frozen bit must be set only when the page is already all-visible.

- 索引沒有可見性對映檔案，只有空閒空間對映檔案

### 2、global 目錄

##### 2.1 概述

儲存pg_control及資料庫叢集維度的資料庫及其關係，非客戶維度的資料

![image-20220121151538988](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121151538988.png)

```plsql
lmj_test=# select oid,relname,relkind from pg_class where oid = 6115 ;
 oid  |            relname            | relkind
------+-------------------------------+---------
 6115 | pg_subscription_subname_index | i
(1 row)
```

##### 2.2 pg_control檔案

pg_control檔案記錄資料庫叢集控制資訊，包括initdb初始化、WAL和checkpoint的資訊

### 3、pg_wal 目錄

##### 3.1 概述

pg_wal是WAL機制中的wal日誌儲存目錄

| PG 版本 | 目錄名  |
| ------- | ------- |
| <10     | pg_xlog |
| >=10    | pg_wal  |

##### 3.2 WAL(write ahead log) 機制

資料庫重做日誌。從名字上看也可以簡單的理解為，在資料寫入磁碟前先記錄日誌。

##### 3.3 檔案結構

![image-20220121152431759](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121152431759.png)

![image-20220121152304412](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121152304412.png)

- **wal segment 檔案（wal段檔案）**

- 儲存著資料庫行記錄明細，每一條記錄明細都是服務於資料庫恢復操作的，確保前後資料一致。首先針對資料的任意一次修改操作均被記錄在wal段檔案中，包括insert、update和delete，其次系統的一些管理行為也會被記錄在wal段檔案中，例如事務提交和vacuum等行為。

- **檔名**：26個十六進位制數由三部分組成

![image-20220121154244804](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121154244804.png)

![image-20220121154414959](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121154414959.png)

LSN(Log Sequnce Number)日誌序列號：記錄 wal 日誌的絕對位置，是一個不斷增長的8位元組(64 bit )的長數字

- 注意區分資料庫log日誌檔案和wal檔案。從**內容**上：資料庫日誌是記錄資料庫層面上的操作，比如記錄啟動資料庫等操作（記錄日誌的內容可自行定義），而針對**資料**的任意一次修改操作均被記錄在wal段檔案中。從**作用**上：資料庫日誌-當系統發生錯誤時可以從 log 中查詢 error，並解決問題；而wal日誌用於資料庫備份恢復及流複製等，與Oracle中的Redo Log功能相同。

- **檔案大小**：預設16MB，可以在`initdb`時通過`--wal-segments`選項來配置wal檔案大小

- **.history**

.history檔案內容包括原.history檔案，當前時間線切換記錄和切換原因，作用於資料庫的時間點恢復行為。當資料庫引擎從多個時間線的備份中恢復時，資料庫從.history檔案中找到從pg_control的start_timeline到指定的recovery_target_timeline間的所有wal段檔案進行恢復。

- **archive_status 目錄**

archive_status是wal段檔案的備份目錄，包括.ready和.done檔案。超出wal_keep_segments數目限制的wal日誌會在archive_status目錄內被標記，歸檔操作完成後被進一步移除。

- **.ready**

.ready是同名wal段檔案在archive_status目錄內的標記檔案，代表該wal段檔案**可被歸檔**。wal段檔案在資料目錄中的儲存檔案數量是有上限的，可以通過[wal_keep_size](http://postgres.cn/docs/14/runtime-config-replication.html#GUC-WAL-KEEP-SIZE) 引數來約束，因此資料庫引擎在wal段檔案個數達到上限後會在archive_status目錄內增加可移除的wal段檔案的標記檔案，檔名是原wal段檔名後增加.ready字尾，等待歸檔工具進行歸檔。

- **.done**

.done是同名wal段檔案在archive_status目錄內的標記檔案，代表該wal段檔案**已被歸檔，可以被清理**。資料庫引擎預設通過archive_command命令對.ready檔案進行歸檔，歸檔成功與否取決於archive_command命令返回true還是false，當archive_command返回true時，代表與.ready檔案同名的wal段檔案已被歸檔，引擎再將該檔案的副檔名重新命名為.done，**等待資料庫引擎在下一次的checkpoint時進一步清理原wal段檔案**。

### 4、pg_xact 目錄

##### 4.1 概述

事務提交日誌儲存目錄，預設256KB

##### 4.2 commit log

![image-20220121162120282](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121162120282.png)

事務提交日誌儲存資料庫的單個事務執行狀態。Commit Log由共享記憶體中一組8KB的page組成，每個page包含一列陣列，每個陣列元素包含XID和該事物的實時狀態。當page不足時，建立新的page來儲存新的事務。

### 5、配置檔案

- **postgresql.conf**

資料庫初始化時預設的配置檔案

- **postgresql.auto.conf**

儲存使用 `ALTER SYSTEM`修改的配置引數，優先順序別高於`postgresql.conf`檔案

- **pg_hba.conf**

pg_hba.conf檔案負責客戶端的連線和認證，起到防火牆的作用。格式：`TYPE  DATABASE  USER  ADDRESS  METHOD`

## 三、表空間的佈局

### 3.1 預設的表空間
兩個預設的表空間：`pg_default`和`pg_global`
```plsql
postgres=# select oid,spcname from pg_tablespace;
 oid  |  spcname
------+------------
 1663 | pg_default
 1664 | pg_global
(2 rows)

postgres=# create table tab_default_tsp(id int,name varchar);
CREATE TABLE
-- reltablespace=0，表示使用的是預設表空間
postgres=# select oid,relname,relfilenode,reltablespace from pg_class where relname = 'tab_default_tsp';
  oid  |     relname     | relfilenode | reltablespace
-------+-----------------+-------------+---------------
 16391 | tab_default_tsp |       16391 |             0
(1 row)
```
- 所有建立的資料庫，如果沒有明確指定表空間，那麼使用預設的表空間 `pg_default`。用於管理整個資料庫叢集的表預設被儲存在`pg_global`表空間中

```plsql
  create database lmj_test owner lmj;
  
  postgres=# SELECT db.oid,db.datname,tsp.spcname from pg_database db inner join pg_tablespace tsp on db.dattablespace=tsp.oid where db.datname = 'lmj_test';
    oid  | datname  |  spcname
  -------+----------+------------
   16385 | lmj_test | pg_default
  (1 row)
  ```

- 用於管理整個資料庫叢集的表預設被儲存在`pg_global`表空間中；`pg_global`表空間的物理位置為`$PGDATA\global`目錄。

- `pg_global`不能被當作預設的表空間

![image-20220121165248545](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121165248545.png)

### 3.2 使用者建立的表空間

執行`CREATE TABLESPACE`語句會在指定的目錄下建立表空間，在該目錄下還會建立版本特定的子目錄。版本特定的命名方式為`PG_主版本號_目錄版本號`

![image-20220121163425572](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121163425572.png)

使用者建立的表空間對應一個目錄

```plsql
--# 在 /soft/pg14/data6000/lmj_tsp_test 目錄下建立了 lmj_tsp_test 表空間
postgres=# create tablespace lmj_tsp_test owner lmj location '/soft/pg14/data6000/lmj_tsp_test';
CREATE TABLESPACE

--# 新建的表空間對應的 oid = 16397
postgres=# select oid,spcname from pg_tablespace;
  oid  |   spcname
-------+--------------
  1663 | pg_default
  1664 | pg_global
 16397 | lmj_tsp_test
(3 rows)

--# 在目錄下建立了版本特定的子目錄-PG_14_202107181
postgres=# \! ls -l /soft/pg14/data6000/lmj_tsp_test
total 0
drwx------. 2 postgres postgres 6 Jan 20 17:20 PG_14_202107181

--# $PGDATA/pg_tblspc 目錄下有與新建表空間oid相同的符號連結指向實際的表空間目錄
postgres=# \! ls -l $PGDATA/pg_tblspc
total 0
lrwxrwxrwx. 1 postgres postgres 32 Jan 20 17:20 16397 -> /soft/pg14/data6000/lmj_tsp_test

--#1. 在新建的表空間 lmj_tsp_test 下建立 tab_new_tsp 表
postgres=# create table tab_new_tsp(id int,name varchar) tablespace lmj_tsp_test;
CREATE TABLE

---# 使用 pg_relation_filepath 函式查詢該表對應的目錄
postgres=# select pg_relation_filepath('tab_new_tsp');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16397/PG_14_202107181/13892/16403
(1 row)

--#2. 在新建的表空間 lmj_tsp_test 下建立 db_new_tsp 資料庫
create database db_new_tsp owner lmj tablespace lmj_tsp_test;

---# 在版本特定的子目錄下建立相應的資料庫目錄
postgres=# \! ls -l $PGDATA/lmj_tsp_test/PG_14_202107181 | grep 16408
drwx------. 2 postgres postgres 8192 Jan 20 17:47 16408
```

## 四、表和索引相關檔案的佈局

### 4.1 資料庫相關知識

- 任何的新初始化資料庫都有一個統一的存放目錄 base 用來存放預設的資料庫：postgres、template1、template0。

- 當建立資料庫時不指定`TEMPLATE`，將使用預設模板資料庫`template1`
- 在資料庫中有一個特殊的資料型別叫做 `oid`(object identifier)物件識別符號，每個資料庫都有對應的唯一的 oid 值。整個資料庫結構和資料庫物件都是通過 `oid`來管理的。

### 4.2  表在資料庫中的管理

建立一張表 tab_test,`oid=16414`

```plsql
postgres=# \c lmj_test lmj
You are now connected to database "lmj_test" as user "lmj".
lmj_test=> create table tab_test (id int,name varchar);
CREATE TABLE

lmj_test=> select oid,relname,relfilenode from pg_class where relname = 'tab_test';
  oid  | relname  | relfilenode
-------+----------+-------------
 16414 | tab_test |       16414
(1 row)

lmj_test=> select pg_relation_filepath('tab_test');
 pg_relation_filepath
----------------------
 base/16385/16414
(1 row)
```

- 預設不會給建立的空表分配空間，只分配一個檔名稱

```shell
  lmj_test=> \! ls -l $PGDATA/base/16385/16414
  -rw-------. 1 postgres postgres 0 Jan 20 18:24 /soft/pg14/data6000/base/16385/16414
  ```

- 在第一次執行`INSERT`操作後，將會分配一個頁面的大小（8K）

```shell
  lmj_test=> insert into tab_test values(1,'lmj');
  INSERT 0 1
  lmj_test=> \! ls -l $PGDATA/base/16385/16414
  -rw-------. 1 postgres postgres 8192 Jan 20 18:32 /soft/pg14/data6000/base/16385/16414
  ```

- **表在內部通過 `oid` 管理，外部通過 `refilenode` 管理**

注：表的 oid 不一定等於 refilenode，如下

```shell
  # 對錶進行 truncate 操作
  lmj_test=> truncate TABLE tab_test ;
  TRUNCATE TABLE
  # 表的 oid 不等於 filenode，表的 filenode 的值發生改變
  lmj_test=> select oid,relname,relfilenode from pg_class where relname = 'tab_test';
    oid  | relname  | relfilenode
  -------+----------+-------------
   16414 | tab_test |       16419
  (1 row)
  
  # 查詢表的儲存位置 - 以 filenode 為名的檔案
  lmj_test=> select pg_relation_filepath('tab_test');
   pg_relation_filepath
  ----------------------
   base/16385/16419
  (1 row)
  # 此時表的 oid和 filenode 的目錄下的檔案都為空
  lmj_test=> \! ls -l $PGDATA/base/16385/16414
  -rw-------. 1 postgres postgres 0 Jan 20 18:34 /soft/pg14/data6000/base/16385/16414
  lmj_test=> \! ls -l $PGDATA/base/16385/16419
  -rw-------. 1 postgres postgres 0 Jan 20 18:34 /soft/pg14/data6000/base/16385/16419
  
  # 插入資料
  lmj_test=> insert into tab_test values(1,'lmj');
  INSERT 0 1
  
  # 以 oid 命名的檔案大小仍為0，filenode命名為檔案大小為 8K
  lmj_test=> \! ls -l $PGDATA/base/16385/16414
  -rw-------. 1 postgres postgres 0 Jan 20 18:34 /soft/pg14/data6000/base/16385/16414
  lmj_test=> \! ls -l $PGDATA/base/16385/16419
  -rw-------. 1 postgres postgres 8192 Jan 20 18:35 /soft/pg14/data6000/base/16385/16419
  
  ```

- 表預設單個檔案大小為1G。如果超過1G，將會使用`relfilenode.編號`（編號從1開始）來命名新的檔案，直到新檔案填滿1G，以此類推。也可以在編譯時使用 `--eith-segsize`來指定

## 五、堆表檔案的內部佈局

### 5.1 堆表檔案的頁面佈局

![image-20220121163500580](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121163500580.png)

表的頁面包含三種型別的資料：

- 堆元組(heap tuples) ：堆元組就是資料記錄本身。它們從頁面底部開始依序堆疊

- 行指標(line pointer)：每個行指標佔4個位元組，儲存著指向堆元組的指標。它們也被稱為**專案指標（item pointer）**。行指標簡單地組織為一個陣列，扮演了元組索引的角色。每個索引項從1開始依次編號，稱為**偏移號（offset number）**。當向頁面中新增新元組時，一個相應的新行指標也會被放入陣列中，並指向新新增的元組。

- 首部資料(header data)：頁面的起始位置分配了由結構`PageHeaderData`定義的首部資料。它的大小為24個位元組，包含關於頁面的後設資料。該結構的主要成員變數為：

| 成員變數            | 描述                                                         |
  | ------------------- | ------------------------------------------------------------ |
  | pd_lsn              | 該頁面最近一次變更所寫入XLOG記錄對應的LSN。它是一個8位元組無符號整數 |
  | pd_checksum         | 頁面的校驗和                                                 |
  | pd_flags            | 標記位（資料頁面中的格式化的空間或未格式化的空間）           |
  | pd_lower            | 空閒空間的起始位置                                           |
  | pd_upper            | 空間空間的結束位置                                           |
  | pd_special          | 特殊空間的偏移量                                             |
  | pd_pagesize_version | 頁面的大小以及頁面的版本編號                                 |
  | pd_prune_xid        | 頁面中最老元組的xid                                          |

> /* @src/include/storage/bufpage.h */ 
>
> 在 bufpage.h 檔案中檢視標準的頁面定義

### 5.2 使用 pg_filedump 工具分析

```plsql
lmj_test=# select pg_relation_filepath('tab_test');
 pg_relation_filepath
----------------------
 base/16385/16451
(1 row)
```

![image-20220121172112029](https://gitee.com/rabbit-lmj/pics/raw/master/image-20220121172112029.png)

```plsql
lmj_test=# select * from tab_test ;
 id |    name
----+------------
  1 | lmj
  1 | PostgreSQL
(2 rows)
-- 顯示格式化的塊內容轉儲
[postgres@node1 pg_filedump]$ ./pg_filedump -d $PGDATA/base/16385/16451 | tail -n 10
  1f90: 00000000 00000000 00000000 00000000  ................
  1fa0: 00000000 00000000 00000000 00000000  ................
  1fb0: 00000000 00000000 fd020000 00000000  ................
  1fc0: 00000000 00000000 02000200 02091800  ................
  1fd0: 01000000 17506f73 74677265 53514c00  .....PostgreSQL.
  1fe0: fc020000 00000000 00000000 00000000  ................
  1ff0: 01000200 02091800 01000000 096c6d6a  .............lmj
```

PostgreSQL 資料庫學習 - 1.資料庫體系結構之儲存結構

相關文章