PG_插件笔记

发表于 本文字数： 2.6k 阅读时长 ≈ 2 分钟

插件awesome：https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47

德哥打包好的docker镜像：https://github.com/digoal/blog/blob/master/202307/20230710_03.md

pg_freespacemap

可用于查看数据库表对应数据块的空余空间的比例和数量；

pg_freespace(rel regclass IN, blkno bigint IN) returns int2

pg_freespace(rel regclass IN, blkno OUT bigint, avail OUT int2)

  • rel - 表名

  • blkno - 数据块号

  • avail - 可用空间bytes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE EXTENSION pg_freespacemap;
drop table t1;
create table t1(id serial, name varchar);
insert into t1(name) select n from generate_series(1,1000) as n;
-- 第一次查看空闲关系映射 发现都是0，因为插入的时候只要一个块不够了 就再申请一个，多一条也申请一个8k的块
SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');
VACUUM t1;-- 一定执行vacuum 否则数据不更新；
-- 这时候发现插入了1000行后，最后一个块并没有被写满
SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');
-- 现在我们删除第一条记录
delete from t1 where ID = 1;
vacuum t1;
-- 我们发现第一个写满的块出现了free space
SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');

-- 下次插入新数据的位置可能是块后空余空间 也可能是块内删除处的空间
insert into t1(name) select n from generate_series(1,10) as n;
vacuum t1;
SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');

image-20231215014042241 image-20231215014105613

image-20231215102032554 image-20231215102058917

注意：

​ 统计并不完全准确，因为他是按照1/256的精度进行计算，而且每次插入数据库并不会立即更新，要么等待autovacuum自动执行后查询，要么手动vacuum tablename 后在查询；

​ 另外统计结果对于基础表来说是OK的，对于索引来说是无意义的，因为avail都是0；

pageinspect

参考文档：https://www.postgresql.org/docs/16/pageinspect.html

从pg_freespacemap中得知了数据的空闲空间占比后，详细的可以通过 pageinspect 进行查看；

General Functions

  • get_raw_page(relname text, fork text, blkno bigint) returns bytea

    获取某张表的某号页的bytea值

    • relname - 表名
    • fork - main / fsm / vm 分别代表 表/索引数据、free space map 空闲磁盘空间映射数据、
    • blkno - 某张表的第几号页
    1
    select * from heap_page_items(get_raw_page('t1','main',0));
    image-20231215140535527

  • get_raw_page(relname text, blkno bigint) returns bytea

    同上get_raw_page(relname text, fork text, blkno bigint) returns bytea,fork默认为’main’;

  • page_header(page bytea) returns record

    展示Postgres堆和索引页的共有属性，显示使用标准页面结构的堆表、索引页面的页头信息；

    1
    select * from page_header(get_raw_page('t1', 0));
    image-20231215140801367

  • page_checksum(page bytea, blkno bigint) returns smallint

    计算page的checksum值

    1
    SELECT page_checksum(get_raw_page('t1', 0), 0);
    image-20231215141336825

  • fsm_page_contents(page bytea) returns text

  • heap_page_items()

  • 查数据库大小

    1
    2
    3
    4
    5
    6
    select * from pg_database;

    select pg_size_pretty(pg_database_size('database_name'));
    select pg_size_pretty(pg_total_relation_size('table_name'));
    select pg_size_pretty(pg_relation_size('table_name'));
    select pg_size_pretty(pg_indexes_size('index_name'));

pg_filedump

pg_stat_statements

pg_reorg

pg_repack

参考文档：https://reorg.github.io/pg_repack/