openGauss你计算的表大小,有包含toast表么?

最近有一个同事问我说“openGauss中pg_relation_size函数在计算表的大小时是否包含了大字段的大小?”,经过思考后,自己觉得表的大小是不包含大字段的大小的,然后通过查看官网的文档说明,该函数含义为指定OID代表的表或者索引所使用的磁盘空间。如果只单独看这条定义,那么还是不清楚是否包含toast表,但是如果你看了数据库对象的其他函数,结合其他函数的解释应该可以得出结论在这里是不包含toast表的大小。即使知道结果了,还是想通过实际操作验证一下openGauss的pg_relation_size函数在计算表大小时未包含toast表大小。关于toast机制在这里就不做详细介绍,有兴趣的同学,可以自行查看相关资料。

准备测试用例

创建测试用例表

create table tbl_blog (id serial primary key,author varchar(50),title varchar(200),content text);

查看表结构

testdb=> \d+ tbl_blog
                                                      Table "test.tbl_blog"
 Column  |          Type          |                       Modifiers                       | Storage  | Stats target | Description
---------+------------------------+-------------------------------------------------------+----------+--------------+-------------
 id      | integer                | not null default nextval('tbl_blog_id_seq'::regclass) | plain    |              |
 author  | character varying(50)  |                                                       | extended |              |
 title   | character varying(200) |                                                       | extended |              |
 content | text                   |                                                       | extended |              |
Indexes:
    "tbl_blog_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

在这里我们可以看到author、title、content的storage列的值为extended,这是大多数toast数据类型的默认设置,表示允许行外存储和压缩,一般会先压缩,如果还是太大,就会行外存储。这里行外存储其实就是指,当行的记录的长度大于了TOAST_TUPLE_THRESHOLD(值为2KB)时,会触发TOAST,把大字段的列数据存储到TOAST表中。

查看tbl_blog关联的toast表的oid

testdb=> select oid,relname,reltoastrelid from pg_class where relname='tbl_blog';
  oid  | relname  | reltoastrelid
-------+----------+---------------
 24608 | tbl_blog |         24612
(1 row)

查询关联的toast表的表名及表数据

--查看toast表名
testdb=# select relname from pg_class where oid = 24612;
    relname
----------------
 pg_toast_24608
(1 row)

在这里可以看到TOAST表名,其实就是pg_toast+表的oid。

查看toast表的信息

testdb=# select tableoid ,* from pg_toast.pg_toast_24608;
 chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)
--查看TOAST表结构
testdb=> \d+ pg_toast.pg_toast_24608
TOAST table "pg_toast.pg_toast_24608"
   Column   |  Type   | Storage
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | 

这里简单的对toast表的字段说明一下

chunk_id:普通表通过TOAST pointer关联到一个被TOAST的列

chunk_seq:同一个chunk_id如果大于TOAST_MAX_CHUNK_SIZE,将被切片存储。这里存储切片后的序号

chunk_data:真实的数据,但是在这里展示的都是二进制值

模拟数据验证

content字段插入少许值

执行命令插入数据

insert into tbl_blog(author,title,content) values('墨竹','推荐Postgresql中一些好用的psql命令','psql客户端工具应该是dba非常频繁使用的的工具。');

查看表大小和toast表的大小

testdb=# select pg_relation_size(oid) as tb_size,pg_relation_size(reltoastrelid) as toast_size from pg_class where relname='tbl_blog';
 tb_size | toast_size
---------+------------
    8192 |          0
(1 row)
--同样在toast表的数据仍然为空
testdb=# select * from pg_toast.pg_toast_24608;
 chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)

在上面查询结果可以看到当插入的值比较小时,在toast表未查询到数据。然后我们再查看content列的长度,为46字节。

testdb=> select pg_column_size(id),pg_column_size(author),pg_column_size(title),pg_column_size(content) from tbl_blog;
 pg_column_size | pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------+----------------
              4 |              5 |             35 |             46
(1 row)

content字段插入大量值

执行命令插入数据,在这里省略了大量的数据,自行测试的时候,找一些数据就行。

insert into tbl_blog(author,title,content) values('墨竹','推荐Postgresql中一些好用的psql命令','E.1.1. Overview 
PostgreSQL 17 contains many new features and enhancements, including:
....省略大量文字
Add worker type column to pg_stat_subscription (Peter Smith) §');

查看表大小和toast表的大小

testdb=# select pg_relation_size(oid) as tb_size,pg_relation_size(reltoastrelid) as toast_size from pg_class where relname='tbl_blog';
 tb_size | toast_size
---------+------------
    8192 |       8192
(1 row)
--在这里由于chunk_data太大不方便展示,就截取了一部分
testdb=> select chunk_id,chunk_seq,substring(chunk_data,0,20) from pg_toast.pg_toast_24608;
 chunk_id | chunk_seq |                substring
----------+-----------+------------------------------------------
    24618 |         0 | \x3a35000000452e312e312e204f007665727669
    24618 |         1 | \x219b6c75652066021387926b946a114457696e
    24618 |         2 | \x015620ce63113712450475697383ae4380606f
    24618 |         3 | \x2204a824e26d616e69e47075443a6f66710973
(4 rows)

当再次插入数据时,表的大小未变化,但是toast表的变为了8KB,说明这个时候已经出发toast机制,将content列的数据存储到toast表。当查看toast表中的数据时,发现已经有数据并且由于插入的太大,对插入的数据进行了切分。最后再来查看一下列的长度,为7286字节,确实是需要切分的。

testdb=# select pg_column_size(id),pg_column_size(author),pg_column_size(title),pg_column_size(content) from tbl_blog;
 pg_column_size | pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------+----------------
              4 |              7 |             45 |             65
              4 |              7 |             45 |           7286
(2 rows)

再次插入数据

再次把第2次的数据重新插入一次

insert into tbl_blog(author,title,content) select author,title,content from tbl_blog where id = 2;

查看表大小和toast表的大小

testdb=> select pg_relation_size(oid) as tb_size,pg_relation_size(reltoastrelid) as toast_size from pg_class where relname='tbl_blog';
 tb_size | toast_size
---------+------------
    8192 |      24576
(1 row)
--在这里由于chunk_data太大不方便展示,就截取了一部分
testdb=> select chunk_id,chunk_seq,substring(chunk_data,0,20) from pg_toast.pg_toast_24608;
 chunk_id | chunk_seq |                substring
----------+-----------+------------------------------------------
    24618 |         0 | \x3a35000000452e312e312e204f007665727669
    24618 |         1 | \x219b6c75652066021387926b946a114457696e
    24618 |         2 | \x015620ce63113712450475697383ae4380606f
    24618 |         3 | \x2204a824e26d616e69e47075443a6f66710973
    24620 |         0 | \x3a35000000452e312e312e204f007665727669
    24620 |         1 | \x219b6c75652066021387926b946a114457696e
    24620 |         2 | \x015620ce63113712450475697383ae4380606f
    24620 |         3 | \x2204a824e26d616e69e47075443a6f66710973
(8 rows)

当我们再次对content字段插入大量值时,发现表的大小未变化,但是toast表大小翻了3倍。

查看列的长度

testdb=>  select pg_column_size(id),pg_column_size(author),pg_column_size(title),pg_column_size(content) from tbl_blog;
 pg_column_size | pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------+----------------
              4 |              5 |             35 |             46
              4 |              5 |             35 |           7286
              4 |              5 |             35 |           7286
(3 rows)

其实通过这三次的插入数据,观察pg_relation_size函数计算表的大小和toast表的大小,其中表的大小一直未变,toast表的大小从0->8192->24576,就可以判断出pg_relation_size函数计算表的大小时是不包含toast表的大小,如果你忽略这点的话,可能导致最终统计的数据不准确。

下面是计算数据库表/索引大小相关的函数。

pg_relation_size(oid)
描述:指定OID代表的表或者索引所使用的磁盘空间。

pg_indexes_size(regclass)
描述:附加到指定表的索引使用的总磁盘空间。

pg_table_size(regclass)
描述:指定的表使用的磁盘空间,不计索引(但是包含TOAST,自由空间映射和可见性映射)

pg_total_relation_size(regclass)
描述:指定的表使用的总磁盘空间,包括所有的索引和TOAST数据

总结

从这个测试实验中,我们就可以很清晰的知道,pg_relation_size函数只统计表对象的大小,未包含toast表大小,因此如果我们需要统计表大小,建议使用pg_total_relation_size函数更精确一点,该函数的统计包括了索引和toast表;另外如果你使用了pg_table_size来统计表大小,需要注意该统计不包含索引,可能需要使用pg_indexes_size来单独计算索引的大小。

参考
https://github.com/digoal/blog/blob/master/201103/20110329_01.md

本文作者:墨竹

Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐