PostgreSQL结构

1.PostgreSQL结构

PostgreSQL 作为一个单机的关系型数据库，与单机Oracle的架构是比较相似的，与MySQL的InnoDB引擎也比较像。据我目前的了解，单机数据库的整体架构都差不太多，都是包含一个主的进程，一些辅助进程，以及一个大的共享内存池。

1.1实例架构

1.2内存结构

1.3数据库进程

主要有3种进程
1.PostgreSQL服务器进程
2.后端进程
3.后台进程

1.4进程结构

PostgreSQL是一个多进程架构的客户端/服务器模式的关系型数据库管理系统。PG数据库中的一系列进程组合进来就是PostgreSQL服务端。这些进程可以细分为以下几大类：

• postgres server进程 -是PG数据库中所有进程的父进程。
• backend进程 - 每个客户端对于一个backend进程，处于这个客户端中的所有请求。
• background进程 - 包含多个后台进程，比如做脏块刷盘的BACKGROUND
  WRITER进程，做垃圾清理的AUTOVACUUM进程，做检查点的CHECKPOINTER进程等。
• replication相关进程 - 处理流复制的进程。
• background workder进程 - PG9.3版本增加，执行由用户自定义开发的逻辑。

从上图可以看出，PG数据库中有一个主的postgres server进程，针对每个客户端有一个backend postgres进程，另外有一系列的background后台进程（针对不同的功能模块）。所以这些进程都对应一个共享内存shared memory。

ps -ef|grep postgres
memcach+   2244   2211  0 14:37 ?        00:00:00 /opt/gitlab/embedded/bin/postgres -D /var/opt/gitlab/postgresql/data
memcach+   2300   2244  0 14:37 ?        00:00:00 postgres: checkpointer
memcach+   2301   2244  0 14:37 ?        00:00:00 postgres: background writer
memcach+   2302   2244  0 14:37 ?        00:00:00 postgres: walwriter
memcach+   2303   2244  0 14:37 ?        00:00:00 postgres: autovacuum launcher
memcach+   2304   2244  0 14:37 ?        00:00:00 postgres: stats collector
memcach+   2305   2244  0 14:37 ?        00:00:00 postgres: logical replication launcher

1.5逻辑架构

\l 查看数据库

postgres=# \l
                                                        数据库列表
   名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限     
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 huayu     | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =Tc/postgres         +
           |          |          |                                |                                | postgres=CTc/postgres
 sample_db | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
(5 行记录)

\c 切换数据库

postgres=# \c huayu
您现在已经连接到数据库 "huayu",用户 "postgres".
huayu=# select user;
 current_user
--------------
 postgres
(1 行记录)

查看对应的oid

huayu=# create table t1(id int);
CREATE TABLE
huayu=# \d pg_class
       数据表 "pg_catalog.pg_class"
        栏位         |   类型    | 修饰词
---------------------+-----------+--------
 relname             | name      | 非空
 relnamespace        | oid       | 非空
 reltype             | oid       | 非空
 reloftype           | oid       | 非空
 relowner            | oid       | 非空
 relam               | oid       | 非空
 relfilenode         | oid       | 非空
 reltablespace       | oid       | 非空
 relpages            | integer   | 非空
 reltuples           | real      | 非空
 relallvisible       | integer   | 非空
 reltoastrelid       | oid       | 非空
 relhasindex         | boolean   | 非空
 relisshared         | boolean   | 非空
 relpersistence      | "char"    | 非空
 relkind             | "char"    | 非空
 relnatts            | smallint  | 非空
 relchecks           | smallint  | 非空
 relhasoids          | boolean   | 非空
 relhaspkey          | boolean   | 非空
 relhasrules         | boolean   | 非空
 relhastriggers      | boolean   | 非空
 relhassubclass      | boolean   | 非空
 relrowsecurity      | boolean   | 非空
 relforcerowsecurity | boolean   | 非空
 relispopulated      | boolean   | 非空
 relreplident        | "char"    | 非空
 relfrozenxid        | xid       | 非空
 relminmxid          | xid       | 非空
 relacl              | aclitem[] |
 reloptions          | text[]    |
索引：
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

#查看oid
huayu=# select oid from pg_class where relname='t1';
  oid
-------
 49239
(1 行记录)

#查看文件存储位置
huayu=# select pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/24578/49239
(1 行记录)

# 查看表结构
huayu=# \d pg_database
  数据表 "pg_catalog.pg_database"
     栏位      |   类型    | 修饰词
---------------+-----------+--------
 datname       | name      | 非空
 datdba        | oid       | 非空
 encoding      | integer   | 非空
 datcollate    | name      | 非空
 datctype      | name      | 非空
 datistemplate | boolean   | 非空
 datallowconn  | boolean   | 非空
 datconnlimit  | integer   | 非空
 datlastsysoid | oid       | 非空
 datfrozenxid  | xid       | 非空
 datminmxid    | xid       | 非空
 dattablespace | oid       | 非空
 datacl        | aclitem[] |
索引：
    "pg_database_datname_index" UNIQUE, btree (datname), 表空间 "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), 表空间 "pg_global"
表空间："pg_global"


# 查看数据库的oid
huayu=# select oid ,datname from pg_database;
  oid  |  datname
-------+-----------
     1 | template1
 12400 | template0
 24577 | sample_db
 24578 | huayu
 12401 | postgres
(5 行记录)

1.6物理架构

默认表空间是base表空间

huayu=# select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions
------------+----------+--------+------------
 pg_default |       10 |        |
 pg_global  |       10 |        |
(2 行记录)

变更系统配置

huayu=# alter system set authentication_timeout = '80';
ALTER SYSTEM

fsm: Free Space Map
vm: Visibility Map

2.死元组

2.1死元组：如何产生

死元组:做DML操作产生的无效数据

huayu=# select * from t1;
 id
----
(0 行记录)


huayu=# insert into t1 values (1);
INSERT 0 1
huayu=# insert into t1 values (2);
INSERT 0 1
huayu=# select * from t1;
 id
----
  1
  2
(2 行记录)

huayu=# create extension pageinspect;
CREATE EXTENSION
huayu=# select * from heap_page_items(get_raw_page('t1',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |  10081 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
(2 行记录)

huayu=# update t1 set id=22 where id =2;
UPDATE 1
# 查看页存储数据  ，因为更新数据，实际两条数据，但是页存储是3条数据，第二条数据无效数据
huayu=# select * from heap_page_items(get_raw_page('t1',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |  10081 |  10083 |        0 | (0,3)  |       16385 |        256 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |  10083 |      0 |        0 | (0,3)  |       32769 |      10240 |     24 |        |       | \x16000000
(3 行记录)

# 这是物理存储文件 8kb 
huayu=# select pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/24578/49242
(1 行记录)

2.2 清除死元组的作用：

1.释放存储资源
2.减少无效数据，减少磁盘扫描时间，提高文件读取效率

3.vacuum

3.1 vacuum作用

清除死元组
跟踪FM free space map 信息
更新VM visibility map 信息
冻结表中的行
定期更新统计信息

3.2 vacuum处理流程

可见性视图

huayu=# select * from heap_page_items(get_raw_page('t1',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 |  10081 |  10083 |        0 | (0,3)  |       16385 |        256 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 |  10083 |      0 |        0 | (0,3)  |       32769 |      10240 |     24 |        |       | \x16000000
(3 行记录)


huayu=# select pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/24578/49242
(1 行记录)


huayu=# vacuum t1;
VACUUM
huayu=# select * from heap_page_items(get_raw_page('t1',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 |  10080 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |      3 |        2 |      0 |        |        |          |        |             |            |        |        |       |
  3 |   8128 |        1 |     28 |  10083 |      0 |        0 | (0,3)  |       32769 |      10496 |     24 |        |       | \x16000000
(3 行记录)

关系对象的名称、占用的页面数和估计的元组（行）数。
表示关系对象在磁盘上占用的页面数。页面是数据库存储的基本单位，PostgreSQL 中一个页面通常大小为 8KB。

huayu=# select relname,relpages, reltuples from pg_class where relname ='t1';
 relname | relpages | reltuples
---------+----------+-----------
 t1      |        1 |         2
(1 行记录)

3.3 vacuum full 清理过程

huayu=# create table t5 as select * from pg_class;
SELECT 349
huayu=# insert into t5 select * from t5;
INSERT 0 349
huayu=# insert into t5 select * from t5;
INSERT 0 698
huayu=# insert into t5 select * from t5;
INSERT 0 1396
huayu=# insert into t5 select * from t5;
INSERT 0 2792
huayu=# insert into t5 select * from t5;
INSERT 0 5584
huayu=# select count(1) from t5;
 count
-------
 11168

FreeSpaceMap（FSM，空闲空间映射）是PostgreSQL数据库中的一种空间管理机制，它用于跟踪数据库表或索引中的空闲空间

huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
 number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
             270 | 57 bytes          |               0.69
(1 行记录)


huayu=# delete from t5 where relfilenode > 10000;
DELETE 5408
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
 number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
             270 | 57 bytes          |               0.69
(1 行记录)


huayu=# vacuum t5;
VACUUM
# 清除死元组  释放空闲空间  ，没有进行块的合并
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
 number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
             267 | 4048 bytes        |              49.42
(1 行记录)

huayu=# vacuum full t5;
VACUUM
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
 number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
             133 | 0 bytes           |               0.00
(1 行记录)


huayu=# insert into t5 select * from t5 limit 10;
INSERT 0 10
huayu=# select count(*) as "number of pages", pg_size_pretty(cast(avg(avail) as bigint)) as "Av.freespace size",round(100 * avg(avail)/8192,2)as "Av.freespace ratio" from pg_freespace('t5');
 number of pages | Av.freespace size | Av.freespace ratio
-----------------+-------------------+--------------------
             134 | 1 bytes           |               0.01
(1 行记录)

3.4 autovacuum

• 主要作用：

核心后台进程
周期完成vacuum的工作

• 开启参数

autoacuum=on
track_count=on
查看配置文件

huayu=# show autovacuum;
 autovacuum
------------
 on
(1 行记录)


huayu=# show track_counts;
 track_counts
--------------
 on
(1 行记录)


huayu=# show autovacuum_analyze_scale_factor;
 autovacuum_analyze_scale_factor
---------------------------------
 0.1
(1 行记录)


huayu=# show autovacuum_analyze_threshold;
 autovacuum_analyze_threshold
------------------------------
 50
(1 行记录)


huayu=# show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 50
(1 行记录)


huayu=# show autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 行记录)

触发条件

计算触发条件
autovacuum vacuum =0.2x1000+50=250;
autovacuum Analyze=0.1x1000+50=150;

触发测试

huayu=# create table t2 (id int,name text);
CREATE TABLE
huayu=# insert into t2 values(generate_series(1,1000),'aaaaa'||generate_series(1,1000));
INSERT 0 1000
huayu=# select count(1) from t2;
 count
-------
  1000
(1 行记录)


huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
 relname | relpages | reltuples
---------+----------+-----------
 t2      |        6 |      1000
(1 行记录)


huayu=# update t2 set name='bbb' where id<152;
UPDATE 151
huayu=#
huayu=# SELECT schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables where relname='t2';
 schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze
------------+-----------+-----------+-----------+------------+------------+-------------+-----------------+--------------+-------------------------------
 public     |      1000 |       151 |         0 |       1000 |        151 |             |                 |              | 2024-10-24 10:17:26.577289+08
(1 行记录)


huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
 relname | relpages | reltuples
---------+----------+-----------
 t2      |        7 |      1000
(1 行记录)


huayu=# update t2 set name='bbb' where id<121;
UPDATE 120
huayu=# SELECT schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables where relname='t2';
 schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze
------------+-----------+-----------+-----------+------------+------------+-------------+-----------------+--------------+-------------------------------
 public     |      1000 |       271 |         0 |       1000 |        271 |             |                 |              | 2024-10-24 10:36:26.854663+08
(1 行记录)


huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
 relname | relpages | reltuples
---------+----------+-----------
 t2      |        7 |      1000
(1 行记录)


huayu=# update t2 set name='bbb' where id<90;
UPDATE 89
huayu=# SELECT schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables where relname='t2';
 schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze
------------+-----------+-----------+-----------+------------+------------+-------------+-------------------------------+--------------+-------------------------------
 public     |      1000 |       360 |         0 |       1000 |         89 |             | 2024-10-24 10:43:26.892215+08 |              | 2024-10-24 10:36:26.854663+08
(1 行记录)


huayu=# select relname,relpages, reltuples from pg_class where relname ='t2';
 relname | relpages | reltuples
---------+----------+-----------
 t2      |        7 |      1000
(1 行记录)