PostgreSQL資料庫體系結構-存儲結構

存儲結構

聚簇邏輯結構

聚簇實體結構

聚簇

資料庫

資料檔案

表空間

資料檔案内部結構

tuple的讀寫方法

1、資料庫聚簇邏輯結構（Logical Structure of Database Cluster）

database cluster --資料庫聚簇，是一組資料庫的集合，而不是多個資料庫伺服器
database --資料庫，是一組資料庫對象的集合
database object
--資料庫對象，是一種資料結構，用于存儲查詢資料

database cluster > database > database object 包含與被包含的關系。
object identifiers (OIDs) --對象辨別，OIDs存儲在system catalog。不同對象類型的OID存儲在不同的catalog中。database的OID存儲在pg_database中；table的OID存儲在pg_class中
Database cluster , Database Users Schemas Tablespace Table 總體上來說，這些邏輯對象是使用與被使用的關系，不是隸屬關系

postgres=# select oid,datname,dattablespace from pg_database;
oid | datname | dattablespace 
-------+-----------+---------------
13212 | postgres | 1663
1 | template1 | 1663
13211 | template0 | 1663
(3 rows)
postgres=# select oid,relname,relowner from pg_class where relname='test_rep';
oid | relname | relowner 
-------+----------+----------
57362 | test_rep | 49156
(1 row)
注1：system catalog相關描述參考文檔
<https://www.postgresql.org/docs/11/bki.html>
注2：OID是僞列

2、資料庫聚簇實體結構（Physical Structure of Database Cluster）

database cluster實體上是一個 base directory（PGDATA），包括一些子目錄和檔案

[pg@pg data]$ tree -L 2
.
├── backup_label.old
├── base
│   ├── 1
│   ├── 13211
│   └── 13212
├── current_logfiles
├── global
│   ├── 1136
│   ├── 1136_fsm
│   ├── 1136_vm
│   ├── pg_control
│   ├── pg_filenode.map
│   └── pg_internal.init
├── pg_commit_ts
├── pg_dynshmem
├── pg_hba.conf
├── pg_ident.conf
├── pg_log
├── pg_logical
│   ├── mappings
│   ├── replorigin_checkpoint
│   └── snapshots
├── pg_multixact
│   ├── members
│   └── offsets
├── pg_notify
│   └── 0000
├── pg_replslot
├── pg_serial
├── pg_snapshots
├── pg_stat
├── pg_stat_tmp
│   ├── db_0.stat
│   ├── db_13212.stat
│   └── global.stat
├── pg_subtrans
│   └── 0000
├── pg_tblspc
│   ├── 16388 -> /opt/postgres/data/tb1
│   └── 49155 -> /tbs
├── pg_twophase
├── PG_VERSION
├── pg_wal
│   ├── 000000090000000000000077
│   └── archive_status
├── pg_xact
│   └── 0000
├── postgresql.auto.conf
├── postgresql.auto.conf.bak20181230
├── postgresql.conf
├── postmaster.opts
├── postmaster.pid
├── recovery.done
├── serverlog
├── tablespace_map.old
└── tb1

2.1、資料庫聚簇布局結構（Layout of a Database Cluster）

base --存放資料庫的子目錄
global --包括聚簇範圍的表，如pg_database

pg_control --控制檔案，用于存儲全局控制資訊 pg_filenode.map --系統表的OID與具體檔案名進行硬編映射 pg_internal.init --緩存系統檔案，加快系統表讀取速度

1136 --對象資料檔案，每個表和索引都存儲為單獨的檔案，以表或索引的filenode number指令（pg_class.relfilenode）
1136_fsm --資料檔案對應的FSM(free space map)件，用map方式來辨別哪些block是空閑的
1136_vm --資料檔案對應的VM(visibility map)PostgreSQL中在做多版本并發控制時是通過在元組頭上标“已無效”來實作删除或更新的，最後通過VACUUM功能來清理效資料回收空閑空間。在做VACUUM時就使用VM快速查找包含無效元組的block。VM僅是個簡單的bitmap,一個bit對應一block。
pg_hba.conf --用戶端網絡通路控制配置檔案
pg_log --預設錯誤日志輸出位置
pg_logical --配置邏輯複制時，存儲邏輯解碼的資料狀态
pg_tblspc --存放非預設表空間路徑，軟連接配接的形式
pg_wal --wal日志路徑
postgresql.auto.conf --參數檔案，使用alter system指令修改的參數檔案，優先級高，會覆寫postgresql.conf參數值
postgresql.conf --參數配置檔案
postmaster.opts --記錄上次啟動伺服器時使用的指令行選項的檔案
postmaster.pid --啟動pg後産生的檔案，記錄啟動pg的資訊
tb1
--非預設表空間

較長的描述參考官方文檔

https://www.postgresql.org/docs/current/storage-file-layout.html

提示：
檢視控制檔案記錄
[pg@pg global]$ pg_controldata 
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6577238410286647636
Database cluster state: in production
pg_control last modified: Wed 16 Jan 2019 01:52:26 PM CST
Latest checkpoint location: 0/77271BC0
Prior checkpoint location: 0/77271B18
Latest checkpoint's REDO location: 0/77271BC0
Latest checkpoint's REDO WAL file: 000000090000000000000077
Latest checkpoint's TimeLineID: 9
Latest checkpoint's PrevTimeLineID: 9
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:681
Latest checkpoint's NextOID: 81960
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 548
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Wed 16 Jan 2019 01:52:26 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending locs timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: on
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: d689960231e71d87b4ea9a8324398a84ce89709786d3c2cb1a18bbede989b1db
[pg@pg global]$ 
檢視pg_filenode.map記錄
[pg@pg global]$ hexdump pg_filenode.map 
0000000 2717 0059 0023 0000 04ee 0000 04ee 0000
0000010 0b94 0000 0b94 0000 04bd 0000 04bd 0000
0000020 0470 0000 0470 0000 04ec 0000 04ec 0000
0000030 04ed 0000 04ed 0000 04be 0000 04be 0000
0000040 095c 0000 095c 0000 1770 0000 1770 0000
0000050 0e08 0000 0e08 0000 17d4 0000 17d4 0000
0000060 0b1e 0000 0b1e 0000 0b1f 0000 0b1f 0000
0000070 0b96 0000 0b96 0000 0b97 0000 0b97 0000
0000080 0fdc 0000 0fdc 0000 0fdd 0000 0fdd 0000
0000090 0a74 0000 0a74 0000 0a75 0000 0a75 0000
00000a0 0a86 0000 0a86 0000 0a87 0000 0a87 0000
00000b0 0a6f 0000 0a6f 0000 0a70 0000 0a70 0000
00000c0 095d 0000 095d 0000 0471 0000 0471 0000
00000d0 04d0 0000 04d0 0000 04d1 0000 04d1 0000
00000e0 0a89 0000 0a89 0000 0a8a 0000 0a8a 0000
00000f0 0b95 0000 0b95 0000 0e09 0000 0e09 0000
0000100 1771 0000 1771 0000 1772 0000 1772 0000
0000110 17e2 0000 17e2 0000 17e3 0000 17e3 0000
0000120 0000 0000 0000 0000 0000 0000 0000 0000
*
00001f0 0000 0000 0000 0000 9ee5 3d9a 0000 0000
0000200
[pg@pg global]$

2.2、資料庫布局結構（Layout of Databases）

資料庫存放在base目錄下，資料庫目錄名以OIDs命名
[pg@pg data]$ tree -L 1 base
base
├── 1
├── 13211
└── 13212

2.3、資料檔案布局結構

單個檔案大小小于1G的表或索引，存儲在所屬資料庫目錄下，每個表或索引都有自己的OID。

資料庫内部，管理表和索引通過OID

資料庫外部，管理資料檔案（表和索引對應的）通過可變的relfilenode

檢視pg_class擷取relfilenode和oid資訊
postgres=# select relname,relfilenode,oid,relnamespace from pg_class where relname='test_table';
relname | relfilenode | oid | relnamespace 
------------+-------------+-------+--------------
test_table | 81961 | 81961 | 81960
(1 row)
注意1：
relfilenode可變，并不總是與OID相比對，如truncate指令會改變relfilenode
postgres=> truncate table test_table;
postgres=> select relname,relfilenode,oid,relnamespace from pg_class where relname='test_table';
relname | relfilenode | oid | relnamespace 
------------+-------------+-------+--------------
test_table | 81967 | 81961 | 81960
(1 row)
通過pg_class可找到oid對應的object name
postgres=# select oid,relname from pg_class where oid in ( 2610,1255);
oid | relname 
------+----------
1255 | pg_proc
2610 | pg_index
(2 rows)
通過内置函數pg_relation_filepath(relation regclass)可以找到對應的實體檔案
postgres=> SELECT pg_relation_filepath('test_table');
pg_relation_filepath 
----------------------
base/13212/81967
(1 row)

檢視實體檔案
通過内置函數pg_relation_filepath(relation regclass)可以找到對應的實體檔案
postgres=> SELECT pg_relation_filepath('test_table');
pg_relation_filepath 
----------------------
base/13212/81967
(1 row)

[pg@pg ~]$ ls -atl $PGDATA/base/13212/81967
-rw------- 1 pg pg 0 Jan 24 14:01 /opt/postgres/data/base/13212/81967

當一個表或索引超過 1 GB 時, 它被劃分為G大小的段，檔案命名為relfilenode、relfilenode.1、relfilenode.2......

注意：The maximum file size of tables and indexes can be changed using the configuration, option --with-segsize when building PostgreSQL.
每個資料檔案都有兩個相關字尾檔案，'_fsm' and '_vm',分别對應free space map and visibility map，索引的資料檔案隻有'_fsm'。
Unlogged tables and indexes have a third fork, known as the initialization fork, which is stored in a fork with the suffix _init

eg:
postgres=#  create unlogged table test_unlog(a int);
CREATE TABLE
postgres=# insert into test_unlog select generate_series(1,1000);
INSERT 0 1000
postgres=# SELECT pg_relation_filepath('test_unlog');
 pg_relation_filepath 
----------------------
 base/13212/102776
(1 row)
[pg@pg 13212]$  ls -atl 102776*
-rw------- 1 pg pg 40960 Mar 20 17:35 102776
-rw------- 1 pg pg 24576 Mar 20 17:35 102776_fsm
-rw------- 1 pg pg     0 Mar 20 17:35 102776_init

實體檔案空間大小
pg_total_relation_size(regclass)
bigint
Total disk space used by the specified table, including all indexes and TOAST data
pg_table_size(regclass)
bigint
Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)
pg_indexes_size(regclass)
bigint
Total disk space used by indexes attached to the specified table
pg_relation_size(relation regclass, fork text)
bigint
Disk space used by the specified fork ('main', 'fsm', 'vm', or 'init') of the specified table or index

postgres=> select pg_size_pretty(pg_table_size('test_toast'));
pg_size_pretty 
----------------
4016 kB
(1 row)

2.4、表空間

tablespace

--獨立于base目錄，是一個額外的資料存儲區data area

如下兩個存儲區表示

**cluster>'base'>database>object

cluster>'tablespace'>[database]>object**

表空間路徑在CREATE TABLESPACE 時，其目錄下會自動建立子目錄，格式如下
PG_'Major version'_'Catalogue version number'
如：
[pg@pg /]$ tree -L 1 /tbs
/tbs
└── PG_10_201707211
表空間的類型有，預設表空間pg_default，系統共享表空間pg_global，自定義表空間
postgres=# select oid,spcname,pg_tablespace_location(oid) from pg_tablespace;
oid | spcname | pg_tablespace_location 
-------+------------+------------------------
1663 | pg_default | 
1664 | pg_global | 
16388 | tb1 | /opt/postgres/data/tb1
49155 | pitrtbs | /tbs
(4 rows)
所有的自定義表空間目錄，都會在$PGDATA/pg_tblspc中建立符号連結，連結名與該表空間的OID相同
select oid,spcname,spcowner,spcoptions from pg_tablespace;
如：
[pg@pg /]$ tree -L 1 $PGDATA/pg_tblspc
/opt/postgres/data/pg_tblspc
├── 16388 -> /opt/postgres/data/tb1
└── 49155 -> /tbs
如果建立的表屬于base下的資料庫，會在表空間下建立一個與已存在資料庫oid相同名字的一個目錄，然後将資料檔案放在此目錄下
如：
[pg@pg /]$ tree -L 3 /tbs
/tbs
└── PG_10_201707211
└── 13212
├── 49163
├── 49166
└── 49168

2.5、oid2name

oid2name — resolve OIDs and file nodes in a PostgreSQL data directory

[pg@pg base]$ oid2name 
All databases:
Oid Database Name Tablespace
----------------------------------
13212 postgres pg_default
13211 template0 pg_default
1 template1 pg_default
[pg@pg base]$ oid2name -s
All tablespaces:
Oid Tablespace Name
------------------------
1663 pg_default
1664 pg_global
16388 tb1
49155 pitrtbs
[pg@pg 13212]$ pwd
/opt/postgres/data/base/13212
[pg@pg 13212]$ ls -atl |head -5
total 14772
drwx------ 2 pg pg 8192 Feb 27 09:51 .
-rw------- 1 pg pg 136164 Feb 27 09:51 pg_internal.init
-rw------- 1 pg pg 32768 Feb 26 09:00 2610
-rw------- 1 pg pg 606208 Feb 23 11:23 1255
[pg@pg 13212]$ 
[pg@pg 13212]$ oid2name -d postgres -f 2610 -f 1255
From database "postgres":
Filenode Table Name
----------------------
2610 pg_index
1255 pg_proc
[pg@pg 13212]$

3、資料檔案内部結構

page --資料庫中最小的存儲單元。在資料檔案（堆表、索引、free space map and visibility map）内部，空間被配置設定成固定長度的pages使用，預設大小8192 byte (8 KB)
block numbers --每個page都是從0開始按順序編号使用（編号叫block numbers），如果page已被填滿，PostgreSQL會在檔案末尾添加一個新的空頁，以增加檔案大小。

block 0
|—————————————————————————————————————————|
|header_data(24byte)|pg_lsn|xxx|----------|
|—————————————————————————————————————————|
|pg_lower|pg_upper|line_pointer_1(4byte)--|
|—————————————————|———————————————————————|
|line_pointer_2|xxx|----------------------|
|—————————————————————————————————————————|
|--------------freespace------------------|
|-----------------freespace---------------|
|—————————————————————————————————————————|
|-----------|xxx|heap_tuple_2|heap_tuple_1|
|—————————————————————————————————————————|

page結構包括3部分

header data --在page頭部，24個位元組長度，記錄page的中繼資料資訊

pd_lsn --存儲page最新更改時，wal日志的lsn資訊 pd_checksum --存儲page的校驗值 pd_lower, pd_upper --pd_lower指向line pointer尾部;pd_upper指向最新heap tuple的頭部 pd_special --主要用于索引，在表中，指向最後一個page line pointer(s) --在header data之後。行指針的長度為4個位元組，用于儲存指向每個heap tuple的指針，是heap tuple的索引。每存放一個tuple就會有一個line pointer

heap tuple(s) --用來存放資料，從page底部開始使用（行資料）

tuple identifier (TID) --為了在表中辨別元組，在内部使用了元組辨別符（tid）。tid包含一對值，包含tuple的page的block numbers，以及指向該tuple的line pointer的偏移編号。這是一個典型的索引用法，用來查找tuple資料。此外，heap tuple總大小超過2KB（約1/4 8KB），使用一種TOAST (The Oversized-Attribute Storage Technique)的方式進行管理

以上可知，line pointer的末尾到最新heap tuple的頭部，這中間的部分叫 free space

4、tuple的讀寫方法

資料庫是如何進行讀寫操作的呢？下文給出一個簡單的描述，後期結合相關内容會有更詳細的描述

假設一個表隻包含一個page，一個page隻包括一個heap tuple

4.1、寫（write）

結構如下

block 24
|—————————————————————————————————————————|
|header_data(24byte)|pg_lsn|xxx|----------|
|—————————————————————————————————————————|
|pg_lower|pg_upper|line_pointer_1(4byte)--|
|—————————————————————————————————————————|
|---------------freespace-----------------|
|---------------freespace-----------------|
|—————————————————————————————————————————|
|----------------------------|heap_tuple_1|
|—————————————————————————————————————————|
如圖所示，配置設定一個序号為block 24的page（8192位元組），目前情況下
page的前24位元組存儲header data
之後的4位元組存儲line pointer 1
中間空間為free space
page末尾存放第一個heap tuple 1
此時
line pointer 1指向heap tuple 1
pg_lower指向line pointer 1
pg_upper指向heap tuple 1

現在插入一行資料,結構如下

block 24
|—————————————————————————————————————————|
|header_data(24byte)|pg_lsn|xxx|----------|
|—————————————————————————————————————————|
|pg_lower|pg_upper|line_pointer_1(4byte)--|
|—————————————————————————————————————————|
|line_pointer_2|--------------------------|
|—————————————————————————————————————————|
|------------freespace--------------------|
|--------------freespace------------------|
|—————————————————————————————————————————|
|---------------|heap_tuple_2|heap_tuple_1|
|—————————————————————————————————————————|
情況如下
在line pointer 1之後配置設定新4位元組存放line pointer 2
在heap tuple 1之前存放最新的heap tuple 2
此時
line pointer 1指向heap tuple 1
line pointer 2指向heap tuple 2
pg_lower指向line pointer 2
pg_upper指向heap tuple 2

4.2、讀（read）：分為兩種，順序讀和索引讀*

1順序讀（Sequential scan） 2索引讀（B-tree index scan） --TID value of the obtained index tuple is '(block = 24, Offset = 2)'

4.2.1、順序讀：Sequential scan

select * from test_table;
block 24
|—————————————————————————————————————————|
|header data(24byte)|pg_lsn|xxx|----------|
|—————————————————————————————————————————|
|pg_lower|pg_upper|line_pointer_1(4byte)--|
|—————————————————————————————————————————|
|line_pointer_2|--------------------------|
|—————————————————————————————————————————|
|------------freespace------ -------------|
|------------freespace--------------------|
|—————————————————————————————————————————|
|---------------|heap_tuple_2|heap_tuple_1|
|—————————————————————————————————————————|
此時
掃描block 1 的page，掃描line pointer 1...line pointer n
掃描block 2 的page，掃描line pointer 1...line pointer n
......
掃描block 24 的page，掃描line pointer 1找到需要的資料heap tuple1

4.2.2、索引讀：B-tree index scan

機構如下

select * from test_table where id=244;
block 24
|—————————————————————————————————————————|
|header data(24byte)|pg_lsn|xxx|----------|
|———————————————————————————————————————— |
|pg_lower|pg_upper|line_pointer_1(4byte)--|
|—————————————————————————————————————————|
|line_pointer_2|--------------------------|
|—————————————————————————————————————————|
|--------------freespace------------------|
|--------------freespace------------------|
|—————————————————————————————————————————|
|---------------|heap_tuple_2|heap_tuple_1|
|—————————————————————————————————————————|
此時
直接通過索引tuple記錄'(block = 24, Offset = 2)'，找到block 24的page，讀取line pointer 2，找到需要的heap tuple 2

Postgresql資料庫體系結構-存儲結構PostgreSQL資料庫體系結構-存儲結構

PostgreSQL資料庫體系結構-存儲結構

1、資料庫聚簇邏輯結構（Logical Structure of Database Cluster）

2、資料庫聚簇實體結構（Physical Structure of Database Cluster）

2.1、資料庫聚簇布局結構（Layout of a Database Cluster）

2.2、資料庫布局結構（Layout of Databases）

2.3、資料檔案布局結構

2.4、表空間

2.5、oid2name

3、資料檔案内部結構

4、tuple的讀寫方法

4.1、寫（write）

4.2、讀（read）：分為兩種，順序讀和索引讀*

4.2.1、順序讀：Sequential scan

4.2.2、索引讀：B-tree index scan

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述