PostgreSQL中的统计信息及表膨胀

PostgreSQL中的统计信息及表膨胀

pg中的统计信息

pg中的统计信息主要看这3个表：pg_class、pg_stat_all_tables、pg_stats

-- pg_class看pages和tuples

select relname,relpages,reltuples::bigint from pg_class where relname='test'\gx
-[ RECORD1 ]------
relname   | test
relpages  | 1688
reltuples | 600000

--pg_stat_all_tables看活元组、死元组，上次统计信息收集时间

select relname,n_live_tup,n_dead_tup,last_analyze,last_autoanalyze from pg_stat_all_tables where relname='test'\gx
-[ RECORD1 ]----+------------------------------
relname          | test
n_live_tup       | 600000
n_dead_tup       | 0
last_analyze     | 2025-01-1515:10:38.553057+08
last_autoanalyze | [null]

--pg_stats看列的统计信息，每个字段都需要了解含义

select *  from pg_stats where tablename='test' and attname='id'\gx
-[ RECORD1 ]----------+-------
schemaname             | public
tablename              | test
attname                | id
inherited              | f
null_frac              | 0
avg_width              | 70
n_distinct             | -1
most_common_vals       | [null]
most_common_freqs      | [null]
histogram_bounds       | [null]
correlation            | [null]
most_common_elems      | [null]
most_common_elem_freqs | [null]
elem_count_histogram   | [null]

统计信息不及时很有可能会造成SQL执行计划改变，引起SQL性能问题。

此时就需要查看 pg_stat_all_tables 表的 last_autovacuum,last_autoanalyze 时间来判断表的收集是不是滞后了。

为什么要调整？因为表的统计信息收集阈值 autovacuum_analyze_scale_factor 默认值是0.1，也就是数据变化达到10%时，才会收集统计信息。例如一个10亿数据的表，数据变化达到1亿时才会收集，频率可能太低了。

应该结合是否核心业务表、SQL表连接个数、SQL复杂度、表访问频率、月初越界问题、数据倾斜问题等判断是否需要针对性的调整表的 autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor 以提高统计信息收集频率，降低SQL执行计划突变的概率，同时也要避免收集过于频繁老是跑vacuum浪费资源。

参数调整参考（请考虑表上的业务数据模型！）：

参数名	默认值	建议值
autovacuum_vacuum_scale_factor	0.2	0.04
autovacuum_analyze_scale_factor	0.1	0.02

表膨胀

某个东西阻止了死元组回收

抛开autovacuum配置问题和一些边缘场景，常见的阻塞是：

• 长事务。注意：不是同一个表的长事务也会阻止死元组回收；查询语句也会造成这个问题

• 复制槽。复制槽延迟和死的复制槽会造成这个问题

  这两种常见都是比较好解决的

  • terminate长事务会话
  • 删除复制槽或者让消费端分析消费为什么这么慢

update并发高导致表膨胀

频繁的update导致表膨胀是因为死元组生成的速度快于vacuum回收的速度。一般来说这个场景的表的pg_stat_all_tables.n_tup_upd比较高。

fillfactor原理：

fillfactor相当于表或索引的水位线，在INSERT数据时，插入到page的fillfactor线就到下一页去插入。fillfactor本身是为了给update留一定的空间，防止update频繁的去寻找新的page。

虽然表和索引都有fillfactor，他们的目的是一样的（为了update），但是具体细节有很大区别：

• 表：如果表的某个page上还有留有空间，那么update可以在这个page中进行，不需要申请新的page或者到其他有空闲空间的page上去。不仅如此，因为PG 独有的HOT特性，页内更新不会更新索引，当然也就会减缓索引膨胀

• 索引：不同的数据行或者相同数据行的页外更新，会新生成索引条目。fillfactor给索引页留下余量，会极大的减缓索引分离问题。

当然，fillfactor的设置跟业务模型是息息相关的，如果数据类似日志那样是递增且完全没有更新的，那么表和索引的fillfactor设置成100无可厚非。但是大部分业务表总是有更新的，表和索引fillfactor就不应该设置成100，如果是频繁的update，那么fillfactor应该设置得更低。

然而，pg默认的fillfactor如下：

表默认 fillfactor=100
索引默认 fillfactor=90

推荐设置：

alter table test set (fillfactor=80);
alter index pk_test set (fillfactor=90);
--以上命令只会对新page生效，对存量page需要做repack

--repack:
1.检查是否有长事务，有则先处理长事务
2.nohup pg_repack -d testdb --table test -p 5432 -no-kill-backend > pg_repack_test_log.log 2>&1 &