生产案例 | 有趣的表大小
前言
昨天同事找到我说："灿哥，怎么一个表里只有85W数据，但是大小却有100GB呢？还有一个表，行数是0，但为什么大小又是49GB呢？"这立马引起了我的兴趣。
现象
表 I
先看看第一个表的情况（为了生产安全，此处做了脱敏，下文都用issue表替代）
postgres=# \dt+ issue
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------+-------+----------+-------------+---------------+-------+-------------
public | issue | table | postgres | permanent | heap | 98 GB |
(1 row)
postgres=# select count(*) from issue ;
count
--------
848699
(1 row)这种case根据过往经验，一般都是表里存在某些变长字段、大字段，比如text，json等。
postgres=# \d issue
Table "public.issue"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------------+-----------+----------+---------
issue_code | character varying(200) | | |
sub_system_code | character varying(128) | | |
scan_job_code | character varying(128) | | |
doc_id | character varying(128) | | |
module_name | character varying(512) | | |
version_name | character varying(512) | | |
detail | text | | |
create_time | timestamp without time zone | | | 看了一下表结构，确实存在一个detail的字段，类型是text，不难想到，应该是这个text字段很长。
postgres=# select avg_width,attname from pg_stats where tablename = 'issue';
avg_width | attname
-----------+-----------------
39 | issue_code
11 | sub_system_code
33 | scan_job_code
33 | doc_id
15 | module_name
17 | version_name
133 | detail
8 | create_time
(8 rows)这里分享个小技巧，我们日常使用的获取表大小的函数有很多种，比如pg_relation_size()、pg_table_size()等，但是假如我们知道这几个函数差异的话，我们便可以快速得到一些结论
参照此图，我们可以看到：
• pg_relation_size()会包含各个fork，也就是我们熟知的fsm、vm等，但不会包含toast的大小
• pg_table_size()会额外包含toast的大小，但不包括索引。关于pg_table_size()是如何计算表大小的，可以参照之前的源码分析《如何优雅地计算表大小》
• total_relation_size()会包括全部大小，包括索引
因此，我们可以使用pg_table_size() - pg_relation_size()，便差不多是toast的大小了（还有索引、fsm、vm等）
postgres=# select pg_size_pretty(pg_relation_size('issue'));
pg_size_pretty
----------------
811 MB
(1 row)
postgres=# select pg_size_pretty(pg_table_size('issue'));
pg_size_pretty
----------------
98 GB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('issue'));
pg_size_pretty
----------------
99 GB
(1 row)可以看到，pg_table_size计算出来的大小达到了98GB，但是pg_relation_size只有811MB，因此正是toast占据的大小。
看下toast表
postgres=# select relname from pg_class where oid = (select reltoastrelid from pg_class where relname = 'issue');
relname
----------------
pg_toast_41389
(1 row)
postgres=# \d pg_toast.pg_toast_41389
TOAST table "pg_toast.pg_toast_41389"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
Owning table: "public.issue2"
Indexes:
"pg_toast_41389_index" PRIMARY KEY, btree (chunk_id, chunk_seq)另外这里还有个有趣的case，假如你是11的版本（其他的版本没有试），\dt会报错，提示表不存在，有点迷惑，各位不要被这个骗了
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 11.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# create table issue2(issue_code varchar(200),sub_system_code varchar(128),scan_job_code varchar(128),doc_id varchar(128),module_name varchar(512),version_name varchar(512),detail text,create_time timestamp without time zone);
CREATE TABLE
postgres=# select relname from pg_class where oid = (select reltoastrelid from pg_class where relname = 'issue2');
relname
----------------
pg_toast_16406
(1 row)
postgres=# \dt pg_toast.pg_toast_16406
Did not find any relation named "pg_toast.pg_toast_16406".
postgres=# \d pg_toast.pg_toast_16406
TOAST table "pg_toast.pg_toast_16406"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea其实论原理，是因为\dt的元命令，在11版本里面就压根没有囊括toast，n.nspname !~ '^pg_toast'，所以自己改吧改吧就行
postgres=# \set ECHO_HIDDEN on
postgres=# \dt pg_toast.pg_toast_16406
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname OPERATOR(pg_catalog.~) '^(pg_toast_16406)$'
AND n.nspname OPERATOR(pg_catalog.~) '^(pg_toast)$'
ORDER BY 1,2;
**************************
Did not find any relation named "pg_toast.pg_toast_16406".自己稍作修改即可
postgres=# SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','s','t','')
AND c.relname OPERATOR(pg_catalog.~) '^(pg_toast_16406)$'
AND n.nspname OPERATOR(pg_catalog.~) '^(pg_toast)$'
ORDER BY 1,2;
Schema | Name | Type | Owner
----------+----------------+-------------+----------
pg_toast | pg_toast_16406 | TOAST table | postgres
(1 row)扯远了，回到主题。可以看到，正是这个toast占了这么大一坨，96GB。第一个案例还算常见。
postgres=# select pg_size_pretty(pg_relation_size('pg_toast.pg_toast_16406'));
pg_size_pretty
----------------
96 GB
(1 row)表 II
再看下第二个"行数为0，但表大小是39GB"的case，表如下
postgres=# select count(*) from operate_log ;
count
-------
0
(1 row)
postgres=# \dt+ operate_log
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+-------+-------------
public | operate_log | table | postgres | permanent | heap | 39 GB |
(1 row)
postgres=# select reltuples,relpages from pg_class where relname = 'operate_log';
reltuples | relpages
-----------+----------
0 | 5133527
(1 row)
postgres=# select pg_size_pretty(pg_relation_size('operate_log'));
pg_size_pretty
----------------
39 GB
(1 row)可以看到，这个表占据了5133527个数据块，行数为0，但是大小却有39GB，同时通过pg_relation_size说明不是toast占据的大小。这种根据以往经验一般是表膨胀的太厉害了，但又有点不同的是，膨胀的表至少会存在一些数据，即活元组。
再看一下pg_stat_all_tables系统表
postgres=# select * from pg_stat_all_tables where relname = 'operate_log';
-[ RECORD 1 ]-------+------------------------------
relid | 41381
schemaname | public
relname | operate_log
seq_scan | 302
seq_tup_read | 214958915
idx_scan | 4213431256
idx_tup_fetch | 214312425667
n_tup_ins | 122872830
n_tup_upd | 0
n_tup_del | 122999694
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2022-04-08 17:54:49.620141+08
last_analyze | 2022-04-12 16:35:49.101982+08
last_autoanalyze | 2022-03-11 03:05:12.739047+08
vacuum_count | 0
autovacuum_count | 39
analyze_count | 1
autoanalyze_count | 175这么看的话，就比较清晰了
• n_live_tup是0，表示表中已经没有活元组了
• n_dead_tup是0，表示表中没有死元组，即没有所谓的"膨胀"
• n_tup_ins表示自上一次统计以来，插入的元组数量
• n_tup_del表示自上一次统计以来，删除的元组数量
• last_autovacuum表示触发autovacuum的时间
这里n_tup_del是大于n_tup_ins，表示都被删除了，猜测一下应该是做了delete from operate_log全表删除的操作。
根据last_autovacuum的时间提示，翻了一下日志，确实存在autovacuum这个动作
2022-04-908 17:54:49.463 CST,,,87926,,62500611,15776,1,,2022-04-08 17:53:21 CST,2/23599531,0,LOG,00000, "autovacuum aggresive vacuum of table ""xxx.public.operate_log"" : index scans: 0使用pageinspect查看，的确只剩下了line pointer和free space map。
postgres=# SELECT * FROM heap_page_items(get_raw_page('operate_log', 2));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------
1 | 0 | 0 | 0 | | | | | | | | | |
2 | 0 | 0 | 0 | | | | | | | | | |
3 | 0 | 0 | 0 | | | | | | | | | |
4 | 0 | 0 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
...那么为什么表大小没有被回收呢？假如有持续关注的小伙伴，应该很快就想到了，是old_snapshot_threshold参数导致的，我们生产环境都是开了这个参数的。vacuum的最后一个阶段是truncate heap
VACUUM is currently truncating the heap so as to return empty pages at the end of the relation to the operating system. This occurs after cleaning up indexes.
即"将文件末尾的数据页返还操作系统"，对于这种delete from全部删除的情况下，经过vacuum后，自然所有的页都可以返还给操作系统，数据大小减少，但是开启了old_snapshot_threshold参数之后，表的大小是不会减少的
When this feature is enabled, freed space at the end of a relation cannot be released to the operating system, since that could remove information needed to detect the "snapshot too old" condition. All space allocated to a relation remains associated with that relation for reuse only within that relation unless explicitly freed (for example, with
VACUUM FULL).
看个例子
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
1h
(1 row)
postgres=# create table t2(id int,info text);
CREATE TABLE
postgres=# insert into t2 select n,'test' from generate_series(1,10000)as n;
INSERT 0 10000
postgres=# delete from t2;
DELETE 10000
postgres=# vacuum t2;
VACUUM
postgres=# \dt+ t2
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | t2 | table | postgres | 480 kB |
(1 row)
postgres=# drop table t2;
DROP TABLE
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
-1
(1 row)
postgres=# create table t2(id int,info text);
CREATE TABLE
postgres=# insert into t2 select n,'test' from generate_series(1,10000)as n;
INSERT 0 10000
postgres=# delete from t2;
DELETE 10000
postgres=# vacuum t2;
VACUUM
postgres=# \dt+ t2
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | t2 | table | postgres | 24 kB |
(1 row)不过奇怪的是既然都要delete from全部删除，直接truncate不就完事了，还是瞬时完成不会占用过多资源，也不会站着茅坑不拉屎，也不会受old_snapshot_threshold参数影响，看样子需要好好给开发科普一下了，至于这个表只能选择vacuum full或者truncate释放空间了。
小结
在生产中，我们一般都会开old_snapshot_threshold以预防表无限膨胀下去，但是开了就会有一些副作用，比如影响长事务索引可用、无法截断数据页减少表大小等。通过这个奇怪的delete *的案例，也侧面说明了truncate的好处，看样子开发规范又可以添加一条了！
要删除全表内容，使用truncate替代delete from，这样不仅可以减少不必要的资源消耗，瞬时完成，也可以避免可能发生的表大小无法降低的情况。
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
