核心专业区
数据库深度探秘
PostgreSQL
日常维护监控排错

数据库审计

image-20220913221350917

索引维护

索引膨胀之后不容易缩，采取重建索引的方式

image-20220913221607964

image-20220913221657891

在线回收空间

image-20220913221720804

在线加列

10以后的版本还是需要设置超时

image-20220913221835192

清理wal日志（就是redo）

删除之后会导致数据库起不来

image-20220913222032709

image-20220913222234020

清理审计日志

image-20220913222425403

进程维护

image-20220913222443316

序列耗尽

image-20220913222724448

审计日志分析

image-20220913222812126

用pgBadger去分析

image-20220913222705329

freeze预测与解决

大表分区

分区方法：pg_pathman 和pg12之后内置分区

image-20220913222944750

冷热分离存储

image-20220913223252686

清理未使用对象，表索引

image-20220913223506910

长事务的清理

image-20220913223523002

postgres=# show restart_after_crash ;
 restart_after_crash 
---------------------
 on
(1 row)

不能kill ，上面这个配置之后才会自动拉起

锁等待清理

image-20220913223859497

小版本升级

image-20220913223923405

有可能需要重建索引

大版本升级

image-20220914130708180

image-20220914130836788

监控

image-20220914130902123

锁等待，长事务，数据库年龄，活跃会话

image-20220914131052551

优化

image-20220914131203006 image-20220914131219666

image-20220914131540692

TOP sql

image-20220914131604073

image-20220914131656450

image-20220914131711676

image-20220914131722965

image-20220914131740660

image-20220914131831231

image-20220914131844591

image-20220914132045019

image-20220914131931306

image-20220914132144981

image-20220914132207976

image-20220914132215241

image-20220914132223988

image-20220914132230488

image-20220914132240603

image-20220914132253855

image-20220914132302808

image-20220914132311231

image-20220914132340752

image-20220914132357240

image-20220914132410841

image-20220914132427833

image-20220914132442050

image-20220914132512387

image-20220914132518808

image-20220914132527007

image-20220914132532785

image-20220914132539393

image-20220914132603667

image-20220914132631064

image-20220914132643249

image-20220914132724137

image-20220914132735788

优化，没有加索引，没有加对索引

什么情况下推荐索引：

扫描大量记录，返回的数量少的；

通过数据库统计信息

seq_scan和seq_tup_read

image-20220914133016377

image-20220914133231146

image-20220914133420399

image-20220914133448065

image-20220914133522453

image-20220914133643454 image-20220914133702361

image-20220914133737753

image-20220914133752048

image-20220914133813106

image-20220914133839125

image-20220914133901390

image-20220914133917406

image-20220914133946539

image-20220914134151040

image-20220914134219250

image-20220914134232457

上报错误

image-20220914134301309

image-20220914134338997

当前会话对应进程

image-20220914134420756

异常问题上报

image-20220914134548864

postgrsql参数配置查找消耗高SQL以及SQL优化