postgresql_internals-14 学习笔记（四）TOAST 超尺寸字段存储技术_postgresql的大字段存在哪了

        TOAST之前一直没太弄懂，这一节单独拆出来学习。

一、 引入原因

       pg中的每个行只能存在一个page里，不能跨page存储。因此对于一些非常长的行，就需要使用到 TOAST（The OverSized Attribute Storage Technique，超尺寸字段存储技术）将大字段值压缩或者拆分为多行到单独表存储。对于用户来说，这是完全透明的。

二、 TOAST策略

1. 多长算超尺寸

       一般来说，pg的目标是在每个页中（8K）至少存4个行，因此如果单行大小超过页的1/4大小（2K），就会对其中的字段使用TOAST策略，也可以在表级设置 toast_tuple_threshold参数（默认2K）自行调整。

2. 4种策略

TOAST有2个大方向处理表中超长的行 —— 压缩、行外存储。

根据倾向程度不同，又分为4种策略：

• PLAIN：不使用TOAST，即禁止压缩和行外存储。只有真的不需要使用TOAST的数据类型才可以选此策略，例如 int,date,boolean等。
• MAIN：优先压缩，尽量不用行外存储。只有在压缩后行内容还是超长时，才会使用行外存储。
• EXTENDED：允许压缩和行外存储。一般也会先压缩，但不像MAIN策略要求严格。
• EXTERNAL：只行外存储，不压缩。对于读取部分字符串类型的操作，一般这种策略性能会更高，因为不需要整行压缩再解压。

可以自己定义用哪种策略

ALTER TABLE 表名 ALTER COLUMN 列名 SET STORAGE external;

例如：

create table t(a int,b numeric,c text,d json);
 
SELECT attname, atttypid::regtype,
CASE attstorage
WHEN 'p' THEN 'plain'
WHEN 'e' THEN 'external'
WHEN 'm' THEN 'main'
WHEN 'x' THEN 'extended'
END AS storage
FROM pg_attribute
WHERE attrelid = 't'::regclass AND attnum > 0;

ALTER TABLE t ALTER COLUMN d SET STORAGE external;

       对于索引，TOAST只支持压缩，不支持行外存储。因此，通常数据库对索引字段长度都是有限制的。

3. 处理流程

• 首先处理external和extended策略的字段，从最长的字段开始。extended策略会先压缩，如果压缩后长度还超过2K，则将其移到TOAST表单独存储。external策略则不压缩，直接将其移到TOAST表单独存储。
• 如果经过第一步后，TOAST表中的行仍然超长，pg会对TOAST表中的行循环执行第一步，直到长度小于toast_tuple_target表级参数（默认也是2K）。
• 如果行还超长，接下来会处理main策略字段，压缩以使其尽量放在原表中。
• 如果还是超长，压缩后的main策略字段也会移到TOAST表单独存储。

三、 TOAST表

       支持TOAST的数据类型应当是可变长度的 variable-length。

       TOAST表均位于pg_toast schema下，它默认不在search_path中，因此TOAST表通常是隐藏的。对于临时TOAST表 pg_temp_N，则位于pg_toast_temp_N schema下，一一对应。

SELECT relnamespace::regnamespace, relname 
FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE relname = 't');

\d+ pg_toast.pg_toast_16523

• chunk_id：标识toast表的oid字段
• chunk_seq：chunk的序列号，与chunk_id组成主键pg_toast_16523_index，加速访问toast表
• chunk_data：存储toast表实际数据

来插入一些数据

insert into t(c) values(repeat('A',5000));

select * from  pg_toast.pg_toast_16523;

toast表中没有结果，说明处理后没有超长（因为全是A非常好压缩）

这次换成难以压缩的随机字符

UPDATE t SET c = (SELECT string_agg( chr(trunc(65+random()*26)::integer), '') FROM generate_series(1,5000)) RETURNING left(c,10) || '...' || right(c,10);

SELECT chunk_id,chunk_seq,length(chunk_data),
left(encode(chunk_data,'escape')::text, 10) || '...' || right(encode(chunk_data,'escape')::text, 10)
FROM pg_toast.pg_toast_16523;

可以看到，这次有结果了，pg将它拆成了3行才存下。

四、 表大小计算

        对于用到了toast的表，原表大小可能只占实际很小的一部分，需要加上行外存储，索引也一样。

仅查看表大小（单位MB，不包括索引和toast）

select pg_size_pretty(pg_relation_size('表名'));

表+索引+toast 大小

select pg_size_pretty(pg_total_relation_size('表名'));

表+toast 大小

psql命令版

\dt+ tab_name

等价于

select pg_size_pretty(pg_table_size('表名'));

分类查询大小

SELECT *, 
       Pg_size_pretty(total_bytes) AS total, 
       Pg_size_pretty(index_bytes) AS index, 
       Pg_size_pretty(toast_bytes) AS toast, 
       Pg_size_pretty(table_bytes) AS table 
FROM   (SELECT *, 
               total_bytes - index_bytes - Coalesce(toast_bytes, 0) AS 
               table_bytes 
        FROM   (SELECT c.oid, 
                       nspname                               AS table_schema, 
                       relname                               AS TABLE_NAME, 
                       c.reltuples                           AS row_estimate, 
                       Pg_total_relation_size(c.oid)         AS total_bytes, 
                       Pg_indexes_size(c.oid)                AS index_bytes, 
                       Pg_total_relation_size(reltoastrelid) AS toast_bytes 
                FROM   pg_class c 
                       LEFT JOIN pg_namespace n 
                              ON n.oid = c.relnamespace 
                WHERE  relkind = 'r') a 
        WHERE  table_schema = 'public' 
        ORDER  BY total_bytes DESC) a;

五、 TOAST优缺点

1. 优点

• 可以存储超长超大字段，避免之前不能直接存储的限制
• 物理上与普通表是分离的，查询、更新不涉及到Toast字段时会极大加快速度，快于未使用Toast的中等字段，因为实际它处理的数据更少

2. 缺点

1. Toast字段的索引创建有可能会失败，且通常不建议，全文检索是一个解决方案
2. Toast字段的查询、更新效率还是较低，其它DB也存在相同问题，因此并不建议将非常长的数据存入数据库

参考

PGCE课程《TOAST技术》

https://mp.weixin.qq.com/s?__biz=MzUyOTAyMzMyNg==&mid=2247488278&idx=1&sn=673b883bee4ed7db812949cfb2ae9618&chksm=fa663b27cd11b2316570e6f5e7c293fc2f383ac91db2fc4b7a915d6e5a32fad778cadb368d50&mpshare=1&scene=1&srcid=1124847gcWeTT0UlmMBiAPbA&sharer_sharetime=1669301108667&sharer_shareid=1a32625a0cee9a1f3987aa62eea3fa03&exportkey=n_ChQIAhIQo87cU%2FyB9UHGhNCHePLplRKZAgIE97dBBAEAAAAAAMMPLiyt3VwAAAAOpnltbLcz9gKNyK89dVj0LzgT0MDt8zRP7qF%2BoxzLY5qT%2B296V2kFZeqV7itcqXOwhFp2dgBIkHLfZmA7U3ReGiVPIUwcvNjJWbbQ%2FlUUajMW26SVYPeA1dSrBaXwZdqv8nx4H7uk2oAcV4zuX2bNgu6qFZawx4UMREksEcmQB7C5lT4%2Fp8GxLHgZ8z4vFmmEAAnzh%2BwwaQq4K93FSGdLdi6afqvyuQVV1MaFFIHPGu4gaJOxT%2BsO%2FXC4zheXpjwrgO%2FhaZ4qk5wLfUvfecHIyz1xc8jDopaVMclixgbBg1dUaiF3lKf%2FDPqvnN4dndxAUE8PzeNF5xDzwAopOslaQznk&acctmode=0&pass_ticket=kzk5I%2BXZvqEw9TUp7%2FycXwF%2FSduQOq0Ws1QstZUUyGEdRUvjLiasoIjsJUjggk8wjYTUvNFzkouGxnKhQ%2BOxVA%3D%3D&wx_header=0#rd