lvorySQL 2025生态大会暨PostgreSQL高峰论坛：PostgreSQL快速入门与实战分享

IvorySQL

2025生态大会暨PostgreSQL高峰论坛PostgreSQL快速入门与实战分享阎书利标题一：PostgreSQL数据库发展标题二：PostgreSQL数据库体系结构标题三：PostgreSQL日常运维标题四：PostgreSQL实战案例目录CONTENTSIvorySQL

2025生态大会暨PostgreSQL高峰论坛个人介绍阎书利云和恩墨数据库技术顾问，

电信行业服务部运维负责人。目前主要从事于PG及PG系国产数据库的运维

。PGACE，

中国PG分会认证讲师，

ITPUB认证专家。IvorySQL

2025生态大会暨PostgreSQL高峰论坛

PostgreSQL数据库发展IvorySQL

2025生态大会暨PostgreSQL高峰论坛PostgreSQL也称为Postgres，是一种免费开源关系数据库管理系统（RDBMS），PostgreSQL

具有事务原子性、一致性、隔离性、持久性（ACID）属性，

自动可更新视图，物化视图，触发器，外键和存储过程。它支持所有主流操作系统，包括Windows、Linux、macOS、

FreeBSD和OpenBSD

，并可处理从单台机器到数据仓库、数据湖或具有许多并发用户的Web

服务等各种工作负载。•20世纪80年代，著名数据库科学家MichaelStonebraker为解决Ingres中的数据关系维护问题，启动了Post-Ingres项目，这就是Postgres的开端;•1994年，两名伯克利大学的研究生Andrew

Yu和Jolly

Chen向Postgres中加入了现在的SQL语言的解释器，从而将Postgres改名为Postgres95;•1996年，随着Postgres95的不断完善，正式更名为PostgreSQL，并沿用至今。What

is

PostgreSQL？PostgreSQL的优点开源免费

&活跃社区•完全开源：

可自由使用

、修改和分发，

无商业许可限制

。•强大的社区支持：

全球开发者共同维护，

问题响应快，

文档丰富

。功能丰富

&高度可扩展•支持复杂数据类型：•JSON/JSONB（支持文档存储和查询）•数组

、几何图形（GIS）、UUID

等

。•高级

SQL

兼容性：•窗口函数

、CTE（公共表表达式）、递归查询

。•可编程性：•支持多种编程语言（

PL/pgSQL

、Python

、Perl

等）

编写存储过程

。•

自定义函数

、触发器和运算符

。可靠性与数据完整性•崩溃恢复：

WAL（预写式日志）

确保数据安全，

支持时间点恢复（

PITR）

。•数据校验：

支持

CHECK

约束

、外键约束

、唯一约束等

。PostgreSQL的优点跨平台&

兼容性•支持所有主流操作系统：

Linux

、Windows

、macOS

等

。•兼容标准SQL：

易于从其他数据库（如

MySQL

、Oracle）

迁移

。•外部数据集成：

通过

FDW（外部数据包装器）

连接其他数据库（如

MongoDB

、MySQL）

。适用场景广泛•OLTP：

高并发事务处理（如电商

、金融系统）

。•数据分析：

复杂报表和聚合计算

。•GIS

系统：

通过

PostGIS

处理空间数据

。•文档存储：

JSONB

类型支持灵活的

NoSQL

式查询

。关系型DBMS排名PostgreSQL版本历程PostgreSQL

全球开发小组在其主要版本首次发布后，

会为其提供5年的支持

。此后，

将发布最终的小版本，

并停止对软件的支持（即软件生命周期结束）

。PostgreSQL版本策略PostgreSQL衍生产品PostgreSQL版本特性12查询性能和空间利用率的改进；

SQL/JSON

路径表达式支持13通过B树索引条目的重复数据删除节省空间并提升性能，

提高使用聚合或分区表的查询性能，

使用扩展统计信息时改善查询规划14

为公用表表达式添加了SQL

标准的SEARCH

和CYCLE

子句，

允许将

DISTINCT

添加到GROUPBY15实现SQL

标准的MERGE语句

。多范围类型

、并行顺序扫描优化

、内置逻辑复制冲突解决

。16数组抽样与乱序操作

、逻辑复制性能优化17提升了vacuum

性能

、I/O层和查询执行的性能，

扩展了JSON

功能，

为MERGE

提供了更多功能，并改进了COPY

操作；

增强了逻辑复制以实现高可用性和升级，

并改进了安全性

、操作

、监控和分析PostgreSQL全球使用情况PostgreSQL服务商（国外）PostgreSQL服务商（国内）PostgreSQL国内社区o中国开源软件推进联盟PostgreSQL分会PostgresConf.CN中国大会oPG中文社区PostgreSQL中国技术大会PostgreSQL学习资源o官网文档/docs/o书籍推荐•PostgreSQL指南:

内幕探索(译:张文升

、冯若航

、刘阳明)•PostgreSQL

9X之巅

(译:范翊

、彭煜伟

、唐成)•

PostgreSQL服务器编程(译:戚长松)•PostgreSQL修炼之道-从小工到专家第2版

(唐成)•

PostgreSQL实战(谭峰

、张文升)•快速掌握PostgreSQL版本新特性（彭冲

、高云龙

、阎书利

、类延良）PostgreSQL邮件列表Mail

List从源码查看相关逻辑IvorySQL

2025生态大会暨PostgreSQL高峰论坛 Post

greSQL数据库体系结构PostgreSQL的体系结构PostgreSQL的逻辑结构PostgreSQL的物理结构PostgreSQL的数据目录/docs/current/limits.htmlPostgreSQL限制PostgreSQL表的文件类型PostareSQL里的每个表(relation)可能会有四种类型•第一种类型为主数据文件，

如果表数据未超过编译时设置的segsize(默认1G)，否则会生成filenode.2,filenode.3的新文件

，依此类推;•第二种类型filenode_fsm为空闲空间信息文件;•第三种类型filenode_vm为可见性信息文件;•第四种类型filenode_init通常为不记录WAL日志的表与索引;droptabletab_test_file;createunloggedtabletab_test_file(idint,nametext,create_timetimestamp(0)withouttimezonedefaultclock_timestamp());insertintotab_test_file(id,name)selectgenerate_series(1,10000000),'abc’

;updatetab_test_filesetid=id+1;postgres=#selectpg_relation_filepath('tab_test_file’

);postgres=#\!ls-ltr$PGDATA/base/13008/98648*-rw

1

postgrespostgres0Jun

13

10:50/home/postgres/data-16/base/13008/98648_init-rw

1postgrespostgres32768Jun

13

11:00/home/postgres/data-16/base/13008/98648_vm-rw

1postgrespostgres344064Jun

13

11:00/home/postgres/data-16/base/13008/98648_fsm-rw

1postgrespostgres230842368Jun

13

11:01/home/postgres/data-16/base/13008/98648.1-rw

1

postgrespostgres

1073741824Jun

13

11:01/home/postgres/data-16/base/13008/98648PostgreSQL系统表的映射文件通常情况下，

PostgreSQL中每张表在磁盘上都有与之相关的文件，

而这些文件的名字便是relfilenode

，我们可以通过pg_class的relfilenode字段去查询。postgres=#selectrelname,oid,relfilenode,reltablespacefrompg_classwhererelnamein('pg_database','pg_class','pg_type','pg_namespace','pg_attribute','t');PostgreSQL系统表的映射文件对于一张普通表，

其relfilenode和oid默认是一样的

。系统表有的也是

。但当我们对该表进行了例如vacuum

full

、truncate之类的操作，

表重建后，

那么relfilenode便会发生变化

。击编pg_class视图里，

通过reltablespace字段我们也可以发现分为两类系统表：

一类是pg_type

、

pg_attribute

、pg_proc和pg_class，

它们是非共享的表，

在内核中我们称为Nail表

。而另一类则是reltablespace为1664的，

即在pg_global表空间里的共享表

。有一部分特殊的表我们会发现其对应的表relfilenode为0，

官方文档的解释为：

0表示这是一个“

映射

”关系，

其磁盘文件名取决于底层状态

。对于这种访问十分频繁的系统表，

不希望每次都是从一些其它的系统表去查询，

这样性能便会非常低，

它们便是通过pg_filenode.map文件去进行管理的

。pg_filenode.map在base的目录每个库的目录以及global目录下均有一个

。辑您的副标题PostgreSQL系统表的映射文件在global和base对应数据库的pg_filenode.map文件中，

将这些系统表的oid与relfileno做映射，文件的大小为512，

刚好是一个OS

disk

sector的大小

。这个文件最多存放62条系统catalog表的记录PostgreSQL表文件内部存储格式PostgreSQL元组写入和读取PostgreSQL的进程状态红色为主数据库进程

蓝色为后台进程绿色为客户端后段进程IvorySQL

2025生态大会暨PostgreSQL高峰论坛

PostgreSQL数据库日常运维PostgreSQL安装方式按应用场景选择：1.源码编译安装2.Docker安装3.二进制安装4.解压安装5.RPM离线安装6.YUM安装数据库的常用环境变量

核心连接变量PGHOST

、PGPORT

、PGDATABASE

、PGUSER

数据库服务启动变量PGHOME

、PGDATA

、PGPORT

、LD_LIBRARY_PATH（指定查找共享库（动态链接库）路径）

服务器配置变量PGDATA

、PGLOGPostgreSQL登录方式psql

命令行工具登录psql-U

username-d

database-h

host

-p

port常用参数：•-U：

指定用户名•-d：

指定数据库名•-h：

指定主机地址（默认为

localhost）•-p：

指定端口（默认为

5432）•-W：

强制提示输入密码如果配置了

pg_h

ba.conf

为trust

认证，

可以直接登录使用

pgAdmin

图形界面1.打开

pgAdmin2.右键点击"Servers"→

"Create"→

"Server"3.输入连接信息（主机

、端口

、用户名

、密码等）4.点击

"Save"保存连接其他客户端工具如

DBeaver

、Navicat

、DataGrip

等，

都支持

PostgreSQL

连接，

配置方式类似

。PostgreSQL如何查看参数--

查看特定参数showparameter_name;--

查看参数其它信息select

name,setting，

min_val，

max_val

from

pg_settings

wherename=

'parameter_name’

;--使用current_setting函数selectcurrent_setting(‘parameter_name’

);--角色和数据库绑定的配置项select*from

pg_db_role_setting;上下文类型描述修改要求示例参数internal只读参数

，在PostgreSQL编译时确定无法修改，

需要重新编译data_directory,server_versionpostmaster需要完全重启PostgreSQL服务才能生效修改后必须重启shared_buffers,max_connectionssuperuser需要超级用户权限才

能修改可由超级用户动态修改log_statement,maintenance_work_

memuser普通用户可以修改任何用户可修改，

仅影响当前会话：

setxxx=xxx；search_path,client_encodingbackend影响单个后端进程只能在进程启动时设置random_page_costsuperuser-backend需要超级用户权限且

只能在后端启动时设

置超级用户设置，

仅对新连接有效deadlock_timeoutsighup发送SIGHUP信号即

可重新加载不需要重启，

重新加载配置即可log_min_duration_sta

tementPostgreSQL如何更改参数PostgreSQL如何更改参数Postmaster级别需要重启数据库生效internal需要重新编译其余类型参数，

如果是当前会话可以生效的，

直接set

parameter_name=xxx即可；其余的类型的可以有如下两种方式修改：1.数据库里ALTER

SYSTEM

SET

parameter_name=‘xxx’

;使用ALTER

SYSTEM

修改的参数会写入

postgresql.auto.conf2.更改$PGDATA/postgresql.conf配置文件，

增添参数配置

。参数配置里，

以最下边的为准

。生效方法：pg_ct

l

reload–D

$PGFDATAselect

pg_reload_conf();优先级由小到大•

work_mem=

1MB•work_mem=2MB•

pg_ct

l

start-o"-c

work_mem='3MB’”•alter

role

all

set

work_mem='4MB’

;•alter

database

postgres

set

work_mem=

'5MB

’;•

alter

role

user1

set

work_mem=

'6MB

’;•

set

work_mem

=

'7MB

’;postgres=#

begin;BEGINpostgres=#set

local

work_mem=

'8MB

';SETPostgreSQL参数的优先级PostgreSQL版本升级策略大版本升级、小版本升级大版本一般涉及到元数据变更

，

系统表

，

系统函数等

，

数据库里有调整

。●

pg_dump/

pg_dumpall、pg_

restore导入导出●

逻辑复制●

pg_

upgrade

升级小版本一般只涉及到二进制文件的变更

，

数据库里无元数据变更

。1

.

编译安装新版本源码程序到新目录2

.

重新编译所有第三方extension扩展3.

关闭数据库服务4

.

使用符号链接替换旧版本PGHOME目录5.

启动新版本服务进行检查6.

确认无误后移除旧版本service

postgres-12stopservice

postgres-12start

service

postgres-12

restartservice

postgres-12

reloadPostgreSQL服务管理数据库服务关闭方式pg_ct

l

stop关闭数据库时，

可以通过“-m”参数定义关闭方式：smart：

缺省模式，

等待所有客户端断开fast：

强制模式，

对未断开的客户端进行回滚，

类似oracle中的immediate选项immediate：

强制模式，

但不回滚，

重启时需要自动恢复逻辑备份物理备份内容：1.直接复制数据库的文件2.包括数据文件

、WAL

日志

、配置文件等逻辑备份内容：1.以

SQL

或特定格式导出数据库结构和数据2.不包含物理存储细节PostgreSQL数据库备份物理备份备份优点•备份/恢复速度快：

直接操作文件系统•完整性高：

完全一致的数据库快照•支持时间点恢复(PITR)：

结合

WAL

日志可实现精确恢复•适合大数据量：

性能影响小缺点•占用空间大：

包含所有数据库文件•不灵活：

不能选择性地恢复单个对象•版本依赖：

通常需要相同或兼容的

PostgreSQL版本典型工具•pg_basebackup•rsync

+WAL

归档•Barman•pgBackRest物理备份

(Physical

Backup)特点1.备份内容：1.直接复制数据库文件系统的文件2.包括数据文件

、WAL

日志

、配置文件等2.备份级别：1.整个数据库集群级别（无法单独备份单个数据库或表）3.实现方式：1.文件系统级别的复制2.使用pg_basebackup

工具或其他备份工具3.基于持续归档的备份方案PostgreSQL数据库备份PostgreSQL数据库备份逻辑备份(Logical

Backup)特点1.备份内容：1.以

SQL

或特定格式导出数据库结构

和数据2.不包含物理存储细节2.备份级别：1.可以备份整个集群

、单个数据库

、特定表或查询结果3.实现方式：1.通过数据库连接导出数据2.使用

SQL

命令重建对象优点•灵活性强：

可选择备份/恢复特定对象•跨版本兼容：

可在不同版本间迁移•可读性强：

备份文件是

SQL

或文本格式•占用空间小：

只包含有效数据缺点•备份/恢复速度慢：

需要执行

SQL

语句•不包含数据库配置：

如用户权限等需要单独备份•一致性挑战：

大型数据库备份时可能有变化典型工具•pg_dump/pg_dumpall•psql

里执行

copy

、\copy•第三方工具如

pgAdmin

的备份功能PostgreSQL数据库备份选择策略

使用物理备份的场景：1.大型生产数据库2.需要快速恢复整个集群3.要求时间点恢复能力（

PITR）4.灾难恢复方案

使用逻辑备份的场景：1.小型数据库或特定对象备份2.跨版本迁移3.开发环境数据复制4.需要人工查看或编辑备份内容

混合策略：1.生产环境建议同时使用两种方式2.物理备份作为主备份方案3.逻辑备份作为补充，

用于特定恢复需求PostgreSQL数据库备份演示物理备份演示（以pg_basebackup备份方式为例）pg_basebackup-D/data1-Fp-Xs-v-P-h

x.x.x.x-p

5432

-U

repuser–R-D后跟备份的目标路径-F后可跟p或者t，

p(plain)-原样复制

，t(tar)-打包为tar-X后跟字母，

指示WAL日志传输方式：

none-不包含

，fetch-备份后获取，

stream-实时流式传输stream最适合生产环境-P显示进度信息-v输出详细消息-R生成recovery.conf(

12+)或standby.signal文件PostgreSQL数据库备份演示物理备份演示（以pg_basebackup备份方式为例）pg_basebackup-D/home/postgres/bak_01-Fp-Xs-P-v-RPostgreSQL数据库备份演示逻辑备份演示（以pg_dump/pg_dumpall/copy为例）#备份单个数据库pg_dump-U

username-ddbname-fdbname_backup.sql#备份整个集群pg_dumpall-U

postgres-ffull_backup.sql#备份特定表pg_dump-U

username-ddbname-ttablename-ftable_backup.sql可直接psql–f跟SQL文件，

应用备份的SQL，

或者使用pg_restore恢复数据

，根据逻辑备份的保存文件类型选择

。#使用copy导入导出表的数据COPY

(SELECT

url,delete_flag

FROM

tab

WHERE

delete_flag=

'

1'

)TO'/home/postgres/bak_tab

1.csv'WITH(FORMATcsv,

HEADERtrue);

’COPYtabbb(url,delete_flag)

FROM'/home/postgres/bak_tab

1.csv'WITH(FORMATcsv,

HEADERtrue,

DELIMITER

',');PostgreSQL数据库的vacuum操作PostgreSQL数据库的MVCC基于事务ID

、行级多版本

、无回滚段（一次update，

产生记录的两个版本，

两个版本都存储于页面内部，

数据可见性用隐藏列

结合帮助判断）因此，

数据页里可能同时存在当前正在使用的版本数据，

以及历史的版本数据

。如果不做处理，

则可能导致数据文件占用大量空间，

表存在膨胀，

而且在扫描的时候，

耗费大量的资源去处理历史版本数据，

SQL性能会很低

。此外，

PostgreSQL数据库使用32位事务号，

最大容纳42亿左右的事务号，

事务号是循环使用的，当事务号耗尽后又会从3开始循环使用

。事务环被分为两个半圆，

当前事务号过去的21亿事务属于过去的事务号，

当前事务号往前的21亿属于未来的事务号，

未来的事务号对当前事务是不可

见的

。两个有效的事务号之间相差不能大于2^31

。如果事务ID耗尽，

则会出现如下报错，

数据

读取可能出现问题

。ERROR:databaseisnotacceptingcommands

to

avoidwraparound

data

loss

in

database

"emdb"HINT:Stopthepostmasterandvacuumthatdatabase

in

single-user

modePostgreSQL数据库的vacuum操作对上述问题PostgreSQL数据库引入的一个vacuum的功能

。vacuum的核心作用：1.回收死元组空间1.

标记被删除或更新记录所占用的空间为可重用2.

解决"表膨胀"问题（表文件增大但实际数据未增加）2.更新统计信息1.

刷新

PostgreSQL

查询优化器使用的数据分布统计2.

确保执行计划的高效性3.冻结事务ID1.

防止事务ID回卷（防止32位事务ID耗尽）2.

标记旧元组为"冻结"状态，

避免事务ID循环问题4.更新可见性映射1.

维护可见性映射(VM)文件，

加速仅索引扫描类型命令示例特点适用场景标准VACUUMVACUUM

[表名]-

不锁表-只回收空间到空闲空间映射-不减少磁盘文件大小常规维护，在线操作，操

作前需要评估业务繁忙程

度。VACUUMFULLVACUUM

FULL[表名]-重写整个表文件-完全回收空间到操作系统-

需要排他锁严重表膨胀时，维护窗口

期使用

。相当于表重建。AUTOVACUU

Mautovacuum守护进程-后台自动执行-基于阈值触发-增量式处理日常自动维护PostgreSQL数据库的vacuum操作VACUUM

类型对比PostgreSQL数据库的autovacuum最主要的几个autovacuum相关的参数配置。autovacuum=on#启用自动vacuumautovacuum_vacuum_threshold=50#触发vacuum的死元组最小数量

autovacuum_analyze_threshold=50#触发analyze的变更最小数量

autovacuum_vacuum_scale_factor=0.2#表大小的触发比例autovacuum_max_workers=3#最大autovacuum进程数触发场景：1.当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+

autovacuum_vacuum_threshold2.指定表上事务的最大年龄配置参数autovacuum_freeze_max_age，

默认为2亿，

达到这个阀值将触发autovacuum进程，

从而避免wraparound

。3.数据库中的活动度和更新频率:autovacuum

会根据数据库中的活动度和表的更新频率动态地触发VACUUM

操作，

以维护表的性能和空间

。PostgreSQL-13版本增加的autovacuum_vacuum_insert_threshold

和autovacuum_vacuum_insert_scale_factor

。针对部分大表，

可以单独在表级别设置autovacuum相关的参数，

从而达到最佳效果

。PostgreSQL数据库的autovacuum可以使用类似的SQL去检查表的死元祖数量，

上次vacuum和autovacuum的时间，

结合表的大小，

判断是否表膨胀严重，

是否需要人工介入进行处理，

或者调整autovacuum的相关参数

。SELECT

relname,

n_dead_t

up,

last_vacuum,

last_autovacuum

FROM

pg_stat_all_tablesWHERE

n_dead_t

up>

1000;SELECTschemaname,relname,pg_size_pretty(pg_total_relation_size(relid))AStotal_size,n_dead_t

up,n_live_t

up,CASEWHEN

(n_live_t

up+

n_dead_t

up)

>0THEN

n_dead_t

up::float/(n_live_t

up+

n_dead_t

up)ELSE0ENDASdead_tuple_ratioFROM

pg_stat_user_tablesWHERE

(n_live_t

up+

n_dead_t

up)>0

--

只统计有元组的表AND

n_dead_t

up::float/(n_live_t

up+

n_dead_t

up)>0.2;PostgreSQL日常巡检应该关注哪些？1.调度程序发起job，

或通过agent接受请求，

按要求返回数

据

。

采集数据包括很多项，

包括系统的资源信息

、磁盘

、

网络等，

数据库的相关信息。2.将采集回来的数据按时间戳存入数据库为以后图形化展示做数据储备。3.对采集的数据进行阈值判断，

需定义通用的阈值。4.

阈值判断发出告警，

告警方式有多种选择：

面板展示

、

邮件

、短信。5.启动预定义动作，

例如部分收集异常诊断信息的命令或者脚本。痛点问题相关监控配置—可以结合脚本输出关键指标，

如长时间未使用的索引，

形成完善的监控告警体系，

防患于未然。eg.开源方案：

监控项脚本+node_exporter/postgres_exporter

+Prometheus+Grafana

（+Altermanager）构建有效的监控和告警策略IvorySQL

2025生态大会暨PostgreSQL高峰论坛

PostgreSQL数据库实战案例S1.对数据库的各种操作，

什么时候做合适？操作前，

需要考虑的问题：是否会影响业务？是否会带来性能影响？是否会引起阻塞？操作大概需要多长时间？操作的进度

…

…数据库里的一些变更操作1.增加删除索引，索引重建2.表的DDL操作-增加删除列3.表的DDL操作-更改列的类型4.Vacuum/vacuumfull操作5.analyze操作S1.对数据库的各种操作，

什么时候做合适？如何正确创建索引（锁的兼容矩阵）其中LOCKBIT_ON

的定义为：#defineLOCKBIT_ON(lockmode)(1<<(lockmode))采用位移运算加或运算，让我们更直观地认识，各种类型的锁之间相互冲突情况。LOCKBIT_ON(locktype)的作用是将将整数1

向左移动

lockmode指定的位数，创造一个位掩码。所以，第一个一级锁的AccessShareLock对应的冲突关系，原始值1：00000001左移8位：00000001，（将1

向左移动8位）LOCKBIT_ON(AccessExclusiveLock)=1<<8=00000001代表AccessExclusiveLock这个锁的冲突情况的bitmask值为00000001。跟除了

LOCKBIT_ON(AccessExclusiveLock)的做与运算，最后结果里有1位不为0，冲突。一个二级锁的RowShareLock对应的冲突关系，LOCKBIT_ON(ExclusiveLock)|LOCKBIT_ON(AccessExclusiveLock)=10000000｜00000001=10000001只有和LOCKBIT_ON(AccessExclusiveLock)和LOCKBIT_ON(AccessExclusiveLock)做与运算才为真，表示冲突。如何正确创建索引（锁的冲突）操作跟操作之间是否有冲突，需要看锁类型之间是否有冲突，这个是由冲突数组定义的。具体实现，

由LockCheckConflicts()函数进行。如何正确创建索引（索引的创建流程）1.校验新索引的Catalog元数据|语法解析--

nl

l

成IndexSt

mt结构-

-校--

型的索引,在pg_am中查找"btree"对应的handler查找pg_attribute，

校验create

index中指定的索引列是否存在，

如果存在记录attno，

并且根据atttypid（表示表中列的字段类型）

在pg_opclass里查找对应的比较函数2.在文件系统中创建索引文件|

生成oid-

-

l

唯一oid

，过程是：

生成一个新的oid

，然后查找pg_class的索引

，如果不存在就返回这个oid

-

-

l

i

加lo

relcache文件系统中生成新的文件base/xxx/xxx

。xlog记录类型为XLogInsert(RM_SMGR_ID,XLOG_SMGR_CREATE

|

XLR_SPECIAL_REL_UPDATE)g到及写xon添以at创建索引文件正在创建的ree成cach件生re文更新本地的为新的索引及比较函数是否支持该类列核引内验索校验er析d解aqhs的的Tree索引验创校将如何正确创建索引（索引的创建流程）3.创建新索引的元数据|

i--

lo

，

等等插入pg_depend4.用函数btbuild构建B-tree索引|

--依次读取排好序的tuple，

填充到B-Tree的叶子节点上，

自下向上插入B-Tree组数组le-t，的-需序e索引pagee生成索引元Trup构建B扫描自下向上sortkeyTree要用引Bn将对构通lcss的依赖进程生效ache对o更对e赖变件相关的rheap的依g元数据的文对引引ta索索ca新该使得录了使记为元数据lalii，l的ss关echaa插插数nv息列元信的的效关用象失相引对e身件为ch本文作ca引引引re索索索PostgreSQL里可以使用create

index

concurrently

在线创建索引(CIC)功能，

降低创建索引在表上申请的锁的级别，

ShareUpdateExclusiveLock级别的锁和RowExclusiveLock不冲突，

不会阻塞表上的DML操作

。如何正确创建索引（创建索引锁级别）如何正确创建索引（CIC的锁级别）1.插入元数据|在系统表中插入索引的元数据，

包括pg_class

、pg_index，索引信息标记为非法状态（

INVALID

），

然后开启两个事务，

进行两次扫描2.第一次扫描|开启事务1，

拿到当前snapshot

1|扫描test_tab

1表前，

等待所有修改过test

1表（写入

、删除

、更新）

的事务结束|

ab

1表

，并建立中间状态的索引（

INVALID

）

|

_之t

t修_t

L（，

、i

_新1的）

务结束

（

le

，

待，

，将其合并到索引），|apshot的事务结束2之间变更的记录事务2之前开启的持有在snapshot1到snapsh等号见本可后版引束到索结拿引以建索可创新中引更索步up结上t_拿描3索引的事dx更改这个删除的DMab1表ab1表执改过testab后ab到次扫描tesnapsho次扫描te启事务2第二次扫1t结束事扫描te如何正确创建索引（CIC的使用注意）1.CIC执行速度慢需要两次扫描表

。所以不考虑锁阻塞的情况下它的执行时间可能会比正常创建索引慢很多

。2.执行失败后可能存在INVALID索引因为第一次扫描并建立中间状态的索引（INVALID）

后，

索引实际上就对后面的DML起作用了，

所以如果是在第二SCAN阶段，

索引创建失败了，

这个索引会一直影响DML（性能

、约束）

。3.冲突，

不允许同时执行create

index

concurrently

在线创建索引(CIC)功能无法并发执行，

因为这个操作在表上上的是ShareUpdateExclusiveLock锁，

四级锁，

自斥

。因此需要尽量选取在业务不忙的时候，

尽量避免创建索引过程中，

两次SCAN之前对被创建索引表实施长事务，

并且长事务中包含修改被创建索引的表

。在第二次SCAN前，

尽量避免开启长事务

。注意不要多个session同时对一张表做create

index

concurrently操作

，以防冲突

。并且及时关注是否有失效的索引

。如何正确创建索引1.评估要建的索引是否合理2.评估表的大小以及索引的预估大小，

大表的创建索引动作，

可能占用磁盘的大量io3.结合磁盘io以及其他主机资源评估创建的大概时长

。4.根据实际情况选择普通创建索引以及CIC，

两者需要申请锁的级别不一致

。普通创建索引会锁表，

期间表上的DML会被阻塞

。5.如何加快创建索引的速度，

可以临时j加大maintenance_work_mem以增加效率

。S2.PostgreSQL的表膨胀问题•

业务反馈，

某表行数较少，

但是涉及到的SQL很慢

。•

几个怀疑方向：1.表膨胀，

死元组过多等，

可以进一步分析是否有阻塞vacuum清理死元组的长事务，

或者失效复制槽等

。2.锁阻塞，

是否有高级别的锁没有释放，

导致select的1级锁获取不到

。3.资源

、负载问题，

导致所有的SQL都比较慢

。4.是否字段较复杂，

或者涉及到toast

。5.是否统计信息不准确，

或者走了效率不高的执行计划S2.PostgreSQL的表膨胀问题占用空间最大的部分，

来自于toast，

且存在大量死元祖，

表膨胀严重

。数据量和占用空间不符，存储所需空间超过原有数据量。同一条记录保留多个历史版本的物理元组以解决对同一条记录的读、写并发冲突（读事务和写事务工作在不同版本的物理元组上）。缺点：垃圾数据的问题。

占用存储空间、查询可能需要扫描不必要的数据页，浪费io资源。S2.PostgreSQL的表膨胀问题S2.PostgreSQL的表膨胀问题表膨胀的影响：1.浪费主机资源，

例如io等2.pg_class里的reltuples统计信息（估算值）

可能偏离较严重3.扫描表的时间太长，

进而影响SQL性能4.

占用大量磁盘空间5.影响备份时长

。6.增加维护成本（例如建索引，

加列等）S2.PostgreSQL的表膨胀问题•导致表膨胀严重的常见原因：•1.表的填充因子设置•2.VACUUM和VACUUM

FULL本身机制•3.AUTOVACUUM参数不合理死元组不能及时清理•4.一些原因导致vacuum后没清理死元组•5.表的统计信息问题•6.maintenance_work_mem参数设置太小死元组不能及时清理•7.磁盘性能问题导致死元组不能及时清理•8.表的特定数据类型影响VACUUM•9.是否误关闭了autovacuum的选项，

导致不能及时清理S2.PostgreSQL的表膨胀问题常见vacuum成功但无法回收死元组的原因：•1.失效复制槽•2.长事务导致•3.存在未提交的prepare事务•4.idle

in

transaction状态的事务•5.函数等内部结构涉及到表的访问•6.hot_standby_feedback参数问题•7.索引状态问题•8.表和索引的并发访问S2.PostgreSQL的表膨胀问题1.失效复制槽复制槽会保留那些在主库上已经被删除或更新的元组，

直到备库确认已经接收到这些元组的所有变更

。这种机制确保了数据的一致性和完整性，

但也意味着在某些情况下，死元组不会被立即清理，

从而导致表的膨胀和存储空间的浪费

。长时间失效的复制槽，

造成主库vacuum可以清理的元组非常少（1

）定期检查和清理不再需要的复制槽，

使用pg_drop_replication_slot()函数删除那些不再需要的复制槽SELECT*frompg_replication_slots;