PostgreSQL问答-第20230210期

目录
Q1. vacuum是否可以在存储过程里执行？
Q2. pg获取周数的函数，有没有算开年第0周的那种？
Q3. pg可以导出统计信息么？
Q4. pg有什么好的方法治理表的膨胀问题？

Q1. vacuum是否可以在存储过程里执行？

问题解答
不可以，具体如下

postgres=# create table t(id int);
CREATE TABLE

postgres=# create or replace procedure test()
postgres-# as $procedure$
postgres$# begin
postgres$#     execute 'vacuum analyze t';
postgres$#     return ;
postgres$# end;
postgres$# $procedure$ language plpgsql;
CREATE PROCEDURE

postgres=# call test();
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL statement "vacuum analyze t"
PL/pgSQL function test() line 3 at EXECUTE

但是vacuum可以包装在脚本里执行

[xmaster@mogdb-kernel-0005 ~]$ psql <<EOF
vacuum analyze t;
EOF

VACUUM

Q2. pg获取周数的函数，有没有算开年第0周的那种？

问题解答
PostgreSQL 开年都是从第一周开始，比如某年的开年元旦恰好在周末的尾巴上，也是以第一周计算。以周一为周计算。但如果开年元旦在周日-周六的任何一个日期，都算第一周。默认应该是以周四所在的那一周为第一周。

也就是to_char里面的iw模式。

举个例子，我拿2023-02-10为例。这里返回的是第6周，我用6周乘以一周七天再减去2023/01/01的那天再减去2023一月份的31天，正好得到的就是10号。

postgres=# select to_char('2023-02-10'::date,'iw') ww;
 ww 
----
 06
(1 row)

postgres=# select 7*6-1-31 as day;
 day 
-----
  10
(1 row)

2023-01-01正好为周日，根据如下计算，再加上这一天，则正好是2023年的总天数

postgres=# select to_char('2023-01-01'::date,'iw') ww;
 ww 
----
 52
(1 row)

postgres=# select 52*7+1;
 ?column? 
----------
      365
(1 row)

Q3. pg可以导出统计信息么？

问题解答

PostgreSQL的pg_dbms_stats插件，可以将统计信息导出，导入（锁定到内存），并且修改PLANNER的行为（从元数据pg_stats中读取改成从dbms_stats中读取统计信息）。从而达到统计信息的导出，锁定目的。
插件地址为： https://github.com/ossc-db/pg_dbms_stats

Q4. pg有什么好的方法治理表的膨胀问题？

问题解答

1. 为表配置 Fill Factor 参数，对于 UPDATE 频繁的表，可以将其配置为 75 或更低，让 HOT更新率更高。
2. 为经常update和insert的表单独配置更为激进的 autovacuum/vacuum 参数，降低触发阈值，少量多次。
3. 使用 pg_cron / contab 手工调度 vacuum 任务，安排 reindex concurrently 任务，减少性能损失
4. 用 pg_repack 定期（例如每天） 彻底在线重整此表，清除膨胀。