PostgreSQL体系结构--物理结构

物理结构

对应在操作系统中的组成的数据库目录和相关文件来构成的

一、 基础目录

$PGDATA：
base global pg_commit_ts pg_wal

pg_tblspc：
alternative database files

二、 目录布局

base 				--存放默认数据库的目录
global				--存放的数据库相关的字典视图或者表文件		
pg_commit_ts		    --事务存放的提交的时间戳数据
pg_dynshmem		--动态内存分配存放的空间(dynamic share memeory)
pg_hba.conf			--基于主机的配置文件
pg_ident.conf		    --基于对等认证的配置文件
pg_logical			--存储数据库内部状态的逻辑解码数据
pg_multixact		    --存放多事务状态的数据
pg_notify			    --消息通知目录(LISTEN状态目录)
pg_replslot			--存放复制槽的数据
pg_serial			    --提交的可串行化事务的状态数据
pg_snapshots		    --执行导出快照函数时的状态信息数据
pg_stat 			    --统计信息目录
pg_stat_tmp 		    --临时统计信息目录
pg_subtrans	        --子事务目录
pg_tblspc            --表空间映射目录
pg_twophase         --两阶段提交状态的数据
PG_VERSION	        --存放主版本编号的文件
pg_wal				--存储 WAL 文件的目录
pg_xact 			    --事务提交的状态数据
postgresql.auto.conf --存储通过 ALTER SYSTEM 命令修改的参数文件（可以手动修改）
postgresql.conf       --数据库的参数配置文件
postmaster.opts       --上一次数据库启动状态的命令
postmaster.pid        --存放当前数据库的主进程编号及相关目录及端口的信息

三、 表和索引

OID：对象标识符
数据库的OID→pg_database
表、索引、序列的OID→pg_class

创建表并查看表路径

postgres=# create table test(id int,name varchar);
CREATE TABLE
postgres=# select oid,relname from pg_class where relname='test';
  oid  | relname 
-------+---------
 16405 | test
(1 row)

postgres=# select pg_relation_filepath('test');
 pg_relation_filepath 
----------------------
 base/14187/16405
(1 row)

查看对应文件

[postgres@training ~]$ cd $PGDATA/base/14187
[postgres@training 14187]$ ls -lh 16405
-rw------- 1 postgres postgres 0 Feb 19 10:40 16405

插入数据，查看表大小

postgres=# insert into test values (1,'postgresql');
INSERT 0 1
postgres=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

查看对应文件

[postgres@training 14187]$ ls -lh 16405
-rw------- 1 postgres postgres 8.0K Feb 19 10:45 16405

刚开始创建表不会分配空间，只有插入数据才会为表分配空间

插入1000w数据

postgres=# insert into test select id,id||'dagedaged' from generate_series(1,10000000) as id;
postgres=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 498 MB
(1 row)

查看对应文件

[postgres@training 14187]$ ls -lh 16405*
-rw------- 1 postgres postgres 498M Feb 19 10:54 16405
-rw------- 1 postgres postgres 144K Feb 19 10:53 16405_fsm

插入1500w数据

postgres=# insert into test select id,id||'dageaged' from generate_series(10000000,25000000) as id;
INSERT 0 15000001
postgres=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 1244 MB
(1 row)

查看对应文件

[postgres@training 14187]$ ls -lh 16405*
-rw------- 1 postgres postgres 1.0G Feb 19 10:58 16405
-rw------- 1 postgres postgres 221M Feb 19 10:57 16405.1
-rw------- 1 postgres postgres 336K Feb 19 10:57 16405_fsm
postgres=# select oid,relfilenode from pg_class where relname='test';
  oid  | relfilenode 
-------+-------------
 16405 |       16405
(1 row)

表和索引都是通过oid来管理，而oid是通过relfilenode去管理，默认oid和relfilenode一致。如果超过1GB，第二文件将会以relfilenode.编号[从1开始]

清空表

postgres=# truncate table test;
TRUNCATE TABLE
postgres=# select oid,relfilenode from pg_class where relname='test';
  oid  | relfilenode 
-------+-------------
 16405 |       16411
(1 row)

查看对应文件

[postgres@training 14187]$ ls -lh 16405*
-rw------- 1 postgres postgres 0 Feb 19 11:04 16405
[postgres@training 14187]$ ls -lh 16411*
-rw------- 1 postgres postgres 0 Feb 19 11:04 16411

**思考：16405 16411文件大小为0，16405文件还有用吗？

插入数据

postgres=# insert into test select id,id||'dagedaged' from generate_series(1,1000000) as id;
INSERT 0 1000000
postgres=# select oid,relfilenode from pg_class where relname='test';
  oid  | relfilenode 
-------+-------------
 16405 |       16411
(1 row)

查看对应文件

[postgres@training 14187]$ ls -lh 16405*
ls: cannot access 16405*: No such file or directory
[postgres@training 14187]$ ls -lh 16411*
-rw------- 1 postgres postgres 50M Feb 19 11:08 16411
-rw------- 1 postgres postgres 32K Feb 19 11:08 16411_fsm

如果对表做truncate操作，relfilenode也将发生相应的改变，同时，原有的relfilenode在重新对表插入数据后将被丢弃。
默认的表和索引的大小可以通过 –with-segsize调整

四、 表空间布局

表空间：存储数据库的一个逻辑空间

创建数据库时可以指定数据库对象的表空间，如果不指定则使用默认的表空间

postgres=# \db
          List of tablespaces
    Name    |  Owner   |    Location    
------------+----------+----------------
 pg_default | postgres | 
 pg_global  | postgres |

pg_global：global目录，保存系统表

pg_default：base目录，template0和template1数据库默认的表空间

1. 未指定表空间的数据库

test_db=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# create table test1(id int,name varchar);
CREATE TABLE
mydb=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

mydb=# select pg_relation_filepath('test1');
 pg_relation_filepath 
----------------------
 base/16393/16420
(1 row)

2. 指定表空间的数据库

postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# create table test1(id int,name varchar);
CREATE TABLE
test_db=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

test_db=# select oid,datname from pg_database where datname='test_db';
  oid  | datname 
-------+---------
 16403 | test_db
(1 row)

test_db=# select pg_relation_filepath('test1');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16402/PG_12_201909212/16403/16414
(1 row)
[postgres@training 14187]$ cd $PGDATA/pg_tblspc
[postgres@training pg_tblspc]$ ll
total 0
lrwxrwxrwx 1 postgres postgres 14 Feb 19 00:13 16402 -> /data/tbs_test

表空间可以放在$ PGDATA目录下，也可以放在$ PGDATA目录外，对于表空间的管理，都是通过oid来管理，为了方便管理，postgresql将所有$PGDATA内部或者外部的表空间都放在pg_tblspc目录下，实际上该目录下存放的就是一个表空间的软链接。

五、 堆表文件的内部结构
pd_lsn：该页中最近变更wal记录的标识
pd_checksum：页面的校验和
pd_flags：标记位
pd_lower：空闲空间的起始位置
pd_upper：空闲空间的结束位置
pd_special：特殊空间的起始位置
pd_pagesize_version：页面大小及页面的版本信息
pd_prune_xid：可以修改的最早的元组的xid

六、 元组的读写方式

1. 如何写入一个堆元组？
   表由页构成，假设页面只有一个元组，页面中的第一个linep指向第一个元组，pd_lower指向第一个linep，pd_upper指向第一个元组
   

2. 读取数据（扫描块）
   顺序扫描：
   

索引扫描（B-Tree）

postgres=# select ctid,id,name from test where id=5;
 ctid  | id |    name    
-------+----+------------
 (0,5) |  5 | 5dagedaged
(1 row)

ctid：格式(blockid,itemid)，表示数据记录的物理行当信息，指的是一条记录位于哪个数据块的哪个位移上面。