PostgreSQL数据库中表的物理大小, 妙懂

数据库中表的物理大小

这是一个很有意思的话题。尤其是在我们做物理设计和空间大小评估的时候。

PostgreSQL中对于稍长一点的列，直接使用了TOAST表来存储，默认是会对表中的数据进行压缩的。关于TOAST, 以后或有时间专门做简单介绍。

先看看相关函数的定义：

---

如果想列一下所有的相关size函数：

 1mydb=# \df pg_*size
 2                                  List of functions
 3   Schema   |          Name          | Result data type | Argument data types | Type
 4------------+------------------------+------------------+---------------------+------
 5 pg_catalog | pg_column_size         | integer          | "any"               | func
 6 pg_catalog | pg_database_size       | bigint           | name                | func
 7 pg_catalog | pg_database_size       | bigint           | oid                 | func
 8 pg_catalog | pg_indexes_size        | bigint           | regclass            | func
 9 pg_catalog | pg_relation_size       | bigint           | regclass            | func
10 pg_catalog | pg_relation_size       | bigint           | regclass, text      | func
11 pg_catalog | pg_table_size          | bigint           | regclass            | func
12 pg_catalog | pg_tablespace_size     | bigint           | name                | func
13 pg_catalog | pg_tablespace_size     | bigint           | oid                 | func
14 pg_catalog | pg_total_relation_size | bigint           | regclass            | func
15(10 rows)

这里以table相关size为例，重点介绍一下

 1* pg_size_pretty ( bigint ) → text
 2这个好理解，对人可读的大小输出
 3* pg_total_relation_size(regclass)
 4table大小，但是它同时包括了相关的索引大小以及涉及到的toast表的相应大小。
 5* pg_table_size ( regclass ) → bigint
 6这个呢，**把索引大小排除在外**，但是同时包含了**toast**表相应大小，还包括了FSM, VM文件相关的大小
 7* pg_indexes_size
 8表的索引的总大小
 9* pg_relation_size ( relation regclass [, fork text ] ) → bigint
10计算一次"fork"出来的该表大小。注意在大多数情况下，使用更高级的函数 `pg_total_relation_size`或`pg_table_size`更方便，它们将所有分叉(forks)的大小相加。)        使用一个参数，这将返回关系的主数据分叉的大小。第二个参数可以用来指定要检查哪个分叉:        
11--  `main`返回关系的主数据分叉的大小。
12--  `fsm` 返回与该关系关联的空闲空间映射FSM的大小。
13--  `vm` 返回与该关系相关联的可见性映射VM的大小。
14--  `init` 返回初始化分叉的大小，如果有的话，与关系表相关。

一张图总结

用一张图，可以表示如下：

总的计算公式：

table size = relation size (main, fsm, vm , init) + toast table (total size)
total relation size = table_size + index_size

举一个实际的例子:

 1postgres=# alter table t alter col2 set storage external;
 2ALTER TABLE
 3
 4CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
 5    SELECT string_agg(substring('abcdefghijiklmnopqrstuvwxyz', round(random() * 25)::integer, 1), '') FROM generate_series(1, $1);
 6$$ language sql;
 7
 8postgres=# insert into t select n, random_string(3800) ||  n from generate_series(1, 1000) as n;
 9INSERT 0 1000
10postgres=# select pg_total_relation_size(16435);
11 pg_total_relation_size
12------------------------
13                4186112
14(1 row)
15
16postgres=# select pg_total_relation_size('t') total, pg_table_size('t') table, pg_indexes_size('t') indexes, pg_table_size('t')+pg_indexes_size('t') as sum;
17  total  |  table  | indexes |   sum
18---------+---------+---------+---------
19 4317184 | 4276224 |   40960 | 4317184
20(1 row)
21
22postgres=# select pg_relation_size('t') default, pg_relation_size('t', 'main') main, pg_relation_size('t', 'fsm') fsm, pg_relation_size('t', 'vm') vm, pg_relation_size('t', 'init') init;
23 default | main  |  fsm  | vm | init
24---------+-------+-------+----+------
25   57344 | 57344 | 24576 |  0 |    0
26(1 row)

如何计算对应的toast表相关的空间大小？(思考)

1toast size = <table size> - <relation size (main, fsm, vm , init)>
2从上例中，可以得到toast size = 4276224 - 57344 = 4218880

微信公众号：数据库杂记   个人微信: _iihero
我是iihero. 也可以叫我Sean.
iihero@CSDN(https://blog.csdn.net/iihero) 
Sean@墨天轮 (https://www.modb.pro/u/16258)
iihero@zhihu (https://www.zhihu.com/people/iihero)
数据库领域的资深爱好者一枚。SAP数据库技术专家与架构师，PostgreSQL ACE.
水木早期数据库论坛发起人db2@smth. 早期多年水木论坛数据库版版主。
国内最早一批DB2 DBA。前后对Sybase, PostgreSQL, HANA, 
Oracle, DB2, SQLite均有涉猎。曾长期担任CSDN相关数据库版版主。
三本著作：<<Java2网络协议内幕>> <<Oracle Spatial及OCI高级编程>> 
<<Sybase ASE 15.X全程实践>>
兴趣领域：数据库技术及云计算、GenAI

业余专长爱好：中国武术六段 陈式太极拳第13代传人（北京陈式太极拳第5代传人）
职业太极拳教练，兼任北京陈式太极拳研究会副秘书长。
如果想通过习练陈式太极拳强身健体，也可以与我联系。