PostgreSQL TOAST 行外存储

1 背景知识

本章节主要介绍 TOAST 技术的原理和使用方法。

1.1 什么是 TOAST？

1. 普通数据行的存储技术。
   • PostgreSQL数据块是数据在文件存储中的基本单位，默认的大小为8KB。
   • PostgreSQL 不允许一行数据跨块存储。
   • PostgreSQL 无法直接存储非常大的字段值。
2. TOAST 主要用于存储一个大字段的值。
   • TOAST 是 The Oversized-Attribute Storage Technique（超尺寸属性存储技术）的缩写。
   • TOAST 技术将大型字段值压缩或切片成多个物理行，这种存储方式叫行外存储。

1.2 TOAST 适用场景

TOAST 适用于数据类型可能是变长的属性，能够存储较大的字段值的数据类型。

1. text。
2. xml。
3. jsonb、json。

1.3 不适用TOAST 场景

TOAST 不适用于数据类型可能是整数、浮点数、固定长度的数据，只能存储较小的字段值的数据类型。

1. Character。
2. Integer。
3. smallserial。

1.4 TOAST 存储方式解析

1. 在使用 TOAST 时， blog 表的 centent 字段不存储任何实际值，只存储 Toast Pointer 。
2. Toast Pointer 指向 TOAST 表。
3. TOAST 表中存储的是行外值。
   • 行外值分裂成2k每个块chunk_data。
   • chunk_data 最大为 TOAST_MAX_CHUNK_SIZE（2K）。
   • chunk_data 作为独立的行存储在从属于所属表的 TOAST 表中。
4. 如果一个表中有任何一个列使用了 TOAST 技术，那么该表将有一个与之关联的 TOAST 表。

1.5 Toast Pointer

1.5.1 变长标头

长度为2bytes。用于指示数据类型是否是变长类型，例如为 varchar 等。

1.5.2 标志位

1. 大小为2比特（2bit）
2. 第一个 1bit 表示是否压缩。
3. 第二个 1bit 表示是否启用行外存储。
4. 如果两个都是零，那么表示既未压缩也未行外存储。

1.5.3 字段逻辑长度

1. 表示原始的未压缩的数据逻辑长度。
2. 大小为30比特``（30bit）。
3. 由字段的逻辑长度值可知 TOAST 数据类型的逻辑长度最多是 30bit，即 1GB(2^30-1字节） 之内。

1.5.4 字段物理长度

表示压缩后的数据长度，长度大小为 4bytes。

1.5.5 指针

1. 指针指向存储实际数据的 TOAST 表 中的位置。
2. chunk ID 为4bytes。
   3.Toast OID为4 bytes。

1.6 TOAST表结构

1.6.1 chunk_id

1. 用来表示特定 TOAST 值的 oid 。
2. 具有同样 chunk_id 值的所有行组成 blog 表的 TOAST 字段的一行数据。

1.6.2 chunk_seq

用来表示该行数据在整个数据中的位置。

1.6.3 chunk_data

1. 存放该 Chunk 实际的被分片的数据。
2. 在 chunk_id 和 chunk_seq 上有一个唯一索引，提供对值的快速检索。

1.7 TOAST 存储策略

1.7.1 触发 TOAST 存储的条件

向一个表中存储超过 2kb TOAST_TUPLE_THRESHOLD 字节的值时，触发 TOAST 策略：

1. 首先将尝试进行压缩。
2. 如果压缩后超过2kb，则进行外存储。
3. 如果压缩后在2kb以内，则不进行行外存储。

1.7.2 参数说明

1.8 环境准备

1.8.1 创建一张blog表

CREATE TABLE blog(content text);

1.8.2 blog 表的 oid 和 TOAST 表的 oid

 SELECT relname,relfilenode,reltoastrelid FROM sys_class WHERE relname='blog';

//屏幕输出：
relname | relfilenode | reltoastrelid 
---------+-------------+---------------
 blog    |       24660 |         24663

1.8.3 得出 TOAST 表和索引的名称

1. TOAST表名为pg_toast.pg_toast_<relfilenode>。

pg_toast.pg_toast_24660

2. TOAST 索引名为pg_toast.pg_toast_<relfilenode>_index。

pg_toast.pg_toast_24660_index

2 EXTENDED 存储策略允许压缩

2.1 环境准备

向blog表中插入一行大字段数据

TRUNCATE blog;
ALTER TABLE blog ALTER content SET STORAGE EXTENDED;
INSERT INTO blog SELECT repeattext),1000000) FROM generate_series(1,1;

2.2 查看物理长度和逻辑长度

1. 物理长度为366340。

SELECT sys_column_size(content) FROM blog;

//屏幕输出：366340

2. 逻辑长度为32000000。

test=# SELECT length(content) FROM blog;

//屏幕输出：32000000

2.3 结论

3 证明EXTERNAL存储策略不允许压缩

3.1 环境准备

向blog表中插入一行大字段数据

TRUNCATE blog ;
ALTER TABLE blog ALTER content SET STORAGE EXTERNAL;
INSERT INTO blog SELECT repeattext),1000000) FROM generate_series(1,1;

3.2 查看物理长度和逻辑长度

1. 物理长度为32000000。

SELECT sys_column_size(content) FROM blog;

//屏幕输出：
32000000

2. 逻辑长度为32000000。

SELECT length(content) FROM blog;

//屏幕输出：32000000

3.3 结论

4 计算 TOAST 表占用的空间

4.1 测试数据准备

DROP TABLE IF EXISTS blog ;
CREATE TABLE blog(content text);
INSERT INTO blog SELECT repeattext),1000000) FROM generate_series(1,1;

4.2 查看 TOAST 表和索引的名称

1. blog表和TOAST表的OID,

SELECT relname,relfilenode,reltoastrelid FROM sys_class WHERE relname = 'blog';

//屏幕输出：
relname | relfilenode | reltoastrelid 
---------+-------------+---------------
 blog    |       24683 |         24686

2. TOAST表索引的OID

select oid from sys_class where relname ='pg_toast_24683_index';

\\屏幕输出：24688

4.3 通过sys_relation_size('regclass') 计算占用空间

1. blog表（不包含TOAST表）占用空间: 8kb。

test=> SELECT sys_size_pretty(sys_relation_size(24683));

\\屏幕输出：8192 bytes

2. TOAST表（不包含索引）占用空间：376kB

test=> SELECT sys_size_pretty(sys_relation_size(24686));

\\屏幕输出：376 kB

3. TOAST索引占用空间：16kB

SELECT sys_size_pretty(sys_relation_size(24688));

\\屏幕输出：16 kB

4. TOSAT表fsm文件占用的空间：24kB

SELECT   sys_size_pretty(sys_relation_size(24686, 'fsm'));

\\屏幕输出：24kB

5. 总计占用空间：8kB + 376kB + 16kB +24kB =424kB

4.4 通过sys_total_relation_size （'表名'）计算占用的空间

1、TOAST表和索引占用的总空间: 416kB。

test=> SELECT sys_size_pretty(sys_total_relation_size(24686));

\\屏幕输出：416kB

2、blog表（包含TOAST表、索引）所占用的总空间：424kB。

test=> SELECT sys_size_pretty(sys_total_relation_size('blog'));

\\屏幕输出：424 kB

5 小结 & FAQ

5.1 TOAST 优势

1. 可以存储超长超大字段，避免之前不能直接存储的限制。
2. SELECT 语句使用较短的键值进行匹配的，那么执行器的大多数工作都将使用主表完成。
3. SQL语句的排序集也缩小了，并且排序将更多地在内存里完成。
4. 物理上与普通表是分离的，检索查询时不检索到大字段会极大地加快速度。
5. 更新普通表时，该表的大字段没有被更新时，不用去更新Toast表。

5.2 TOAST的缺点

1. 大字段的索引创建，有可能会失败。
2. 大字段的更新慢。

5.3 如何分析Toast Pointer

借助 pageinspect 查看，可查看 page 中各结构体的数值，得到 Toast Pointer 必须查看RAW值。

5.3.1 准备工作

1. blog 表中插入一行大字段数据。
2. 查看chunk_id。
3. 查看字段物理长度和逻辑长度。

TRUNCATE  blog;
INSERT INTO blog SELECT repeattext),1000000) from generate_series(1,1;
SELECT chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_1_<OID>;
SELECT sys_column_size(content) from blog;
SELECT length(content) from blog;

4. 查询Toast Pointer 的值。

SELECT lp,t_data from heap_page_items (get_raw_page('blog',0));

//屏幕输出：
\x01120448e80104970500aa400000a7400000

5.3.2 分析步骤

1. 字节转换。
   先取4个字节0xa7400000。由于X86架构下，使用小端存储，因此内存中的0xa7400000，实际的字节序为0x000040a7 。

• 转换前

\x0112 0448e801 04970500 aa400000 a7400000

• 转换后

\x000040a7 000040aa 00059704 01e84804 1201

2. 计算出Toast OID,占用4字节。

SELECT x'000040a7'::int;
16551

3. 计算出chunk OID,占用4字节。

SELECT x'000040aa'::int;
16554

（4）计算字段值的实际长度,占用4字节。

SELECT x'00059704'::int;

（5）计算字段值的逻辑长度，占用4字节。

SELECT x'01e84804'::int;

5.3.3 分析和结论

5.4 本文所有示意图