PostgreSQL（二十三）TOAST技术

目录

一、TOAST简介

二、TOAST的存储方式

1、存储方式概述

2、实验：创建TOAST表

三、TOAST的4种压缩策略

1、策略说明

2、TOAST表额外的三个字段

四、TOAST表的计算方式

1、说明

2、实验：计算表大小

五、TOAST表的特点

1、优点

2、缺点

3、访问成本的计算

六、PG TOAST技术与Oracle大对象存储方式的对比

---

一、TOAST简介

1、TOAST技术：The OverSized Attribute Storage Technique，超尺寸字段在PG的存储方式。

2、TOAST技术产生背景：元组不允许跨页面存储。

3、TOAST技术特点：它会将大字段值压缩或者分散为多个物理行来存储。

4、对于用户来说，这一技术实现是透明的，无需关注。

二、TOAST的存储方式

1、存储方式概述

        PG的部分类型数据支持toast，因为有些字段类型是不会产生大字段数据（比如date、time、boolean等）。支持TOAST的数据类型应当是可变长度的（variable-length）。

        假如PG的表中任何一个字段存在TOAST，这个表都会有这一个相关联的TOAST表，TOAST表的OID被存储在pg_class.reltoastrelid里

        超尺寸字段超出的数值将会被分割成chunks，并且最多toast_max_chunk_size 个byte(缺省是2KB)

        当某列存储的数据长度超过toast_tuple_threshold值(通常是2KB)，就会触发TOAST存储。

        TOAST将会压缩或者移动字段值直到超出部分比toast_tuple_targer值小(这个值通常也是2KB)。

2、实验：创建TOAST表

（1）创建模拟表，包含允许TOAST的字段

--创建表时使用text数据类型，可实现列值长度超过2KB就自动产生toast表来存储
CREATE TABLE toast_t(id int,vname varchar(48),remark text);

（2）更改列的压缩方式为main

//语法：
ALTER TABLE ${tab_name} ALTER COLUMN ${col_name}
SET STORAGE {PLAIN | EXTENDED | MAIN(缺省) | EXTERNAL };//示例：
ALTER TABLE toast_t ALTER COLUMN remark SET STORAGE main;

（3）查看Toast表的名字

//查看toast表的oid
SELECT relname,relfilenode,reltoastrelid FROM pg_class WHERE relname='toast_t';//根据toast表oid查看其名字
SELECT relname FROM pg_class WHERE oid = '$reltoastrelid';

三、TOAST的4种压缩策略

1、策略说明

2、TOAST表额外的三个字段

//查看TOAST表结构，TOAST表属于pg_toast模式
\d+ pg_toast.pg_toast_16385

四、TOAST表的计算方式

1、说明

        在PG中，计算一个表的大小时要注意统计TOAST的大小，因为对超长字段存储时，在基础表上可能只存了20%，另外的数据都存到了TOAST里面去了，计算大小时要结合起来看。

        索引也是一样，对于表里有extended或者external类型的，会创建TOAST表，两者的关联是通过pg_class里的OID去关联的。

2、实验：计算表大小

（1）第一次插入：remark列的值小于2k的数据

        此时remark列值长度小于2KB，所以不会触发TOAST存储，TOAST表的大小为0。

//往上面创建的实验表toast_t中插入数据
ALTER TABLE toast_t ALTER COLUMN remark SET STORAGE extended;
INSERT INTO toast_t SELECT generate_series(1,4),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',1000);//查看表中列值大小：
SELECT pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from toast_t limit 10; //查看基础表大小
SELECT pg_size_pretty(pg_relation_size('toast_t'));//查看TOAST表大小
SELECT pg_size_pretty(pg_relation_size('16396'));

（2）第二次插入：remark列的值刚好比2k大一点的数据

        由结果观察可知，此时TOAST表的大小不再为0，而toast_t表的大小没有变化。

        这说明，当remark列值超过2kb左右时，触发了TOAST存储方式，且在列尺寸超过2k的时候就会把数据存放到toast表中，不影响基础表的大小。

//继续插入数据，并查看数据
INSERT INTO toast_t SELECT generate_series(3,4),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',5500);
SELECT pg_column_size(id),pg_column_size(vname),pg_column_size(remark) FROM toast_t limit 10;
SELECT pg_size_pretty(pg_relation_size('toast_t'));
SELECT pg_size_pretty(pg_relation_size('16396'));

（3）第三次插入：插入更大的数据

        可以看到后插入的数据随着字段内容的增多，toast 段一直在变大，而基础表的大小并没有变化。

//继续插入更多的数据并查看toast表大小：
INSERT INTO toast_t SELECT generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',10000);
SELECT pg_column_size(id),pg_column_size(vname),pg_column_size(remark) FROM toast_t limit 10;
SELECT pg_size_pretty(pg_relation_size('toast_t'));
SELECT pg_size_pretty(pg_relation_size('16396'));

五、TOAST表的特点

1、优点

（1）可以存储超长超大字段，避免之前不能直接存储的限制 ；

（2）物理上与普通表是分离的，检索查询时不检索到超长字段时，会极大地加快速度；

管理小tip：

       鉴于TOAST表的特性，可能会出现：超长列只有部分存储数据在原始表中，而有部分存储在TOAST表中。

        为了底层存储区分开方便管理，可以设置将超长列的全部数据都存储在Toast表中:

ALTER TABLE ${tab_name} ALTER COLUMN ${col_name} SET STORAGE  external;

（3）update情景，该表的Toast数据没有被更新时，只需要更新普通表，不用去更新TOAST表；

2、缺点

（1）对大字段的索引创建是一个问题，有可能会失败，通常不建议在大字段上创建，而是选择全文检索。

（2）大字段的更新会有点慢，其它类型的数据库也会存在相同问题。

（3）在全表扫描情况下，有TOAST表的存在，会大大增加访问成本。

3、访问成本的计算

使用explain不会统计toast访问部分的代价。需要单独使用explain统计访问toast表的代价。

Toast表的成本统计案例如下：

（1）创建表toast_t1，插入数据，remark值小于2kb，不触发TOAST存储机制

CREATE TABLE toast_t1 (id int, vname varchar(48), remark text);
INSERT INTO toast_t1 SELECT generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',2000);
\d+ toast_t1
SELECT relname,oid,reltoastrelid FROM pg_class WHERE relname = 'toast_t1';
SELECT pg_size_pretty(pg_relation_size('toast_t1'));     （查原始表的存储大小）
SELECT pg_size_pretty(pg_relation_size('16440'));     （查TOAST表的存储大小）

 2、创建表 toast_t2，插入数据，remark值大于2kb，触发TOAST存储机制

CREATE TABLE toast_t2 (id int, vname varchar(48), remark text);
ALTER TABLE toast_t2 ALTER COLUMN remark SET STORAGE external;
INSERT INTO toast_t2 SELECT generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',2000);
\d+ toast_t2
SELECT relname,oid,reltoastrelid FROM pg_class WHERE relname = 'toast_t2';
SELECT pg_size_pretty(pg_relation_size('toast_t2'));     （查原始表的存储大小）
SELECT pg_size_pretty(pg_relation_size('16445'));     （查TOAST表的存储大小）

（3）查询toast_t1所有列扫描的代价

toast_t1没有触发TOAST表存储，因此14就是t2全表扫描的全部代价（450行）

EXPLAIN ANALYZE select * from toast_t1;

 （4）查询toast_t2所有列扫描的代价（加上访问TOAST部分的成本）

toast_t2触发了TOAST表存储，因此需要将基表与TOAST表的全表扫描代价相加，即：14+39=53

EXPLAIN ANALYZE select * from toast_t2;

综上可知，t2的全表扫描代价远高于t1表（20004>1350）。    

        因此，不触发TOAST表、将长数据与其他数据存储在基表中，是全表扫描时更好的存储方式。 

六、PG TOAST技术与Oracle大对象存储方式的对比

        Oracle11g版本中推出了针对LOB（Blob、Clob）字段处理的新技术：SecureFiles。PG的TOAST技术与它有相似之处。

        SecureFiles技术也是指定另外的 segment 来存储LOB字段，而不是在原表中存储，且可以设置 enable_storage_in_row 来指定表中存储。

        经测试，在Oracle数据库中，将其中一个100GB的LOB字段转换为SecureFiles，并采用压缩技术之后，最终只消耗30GB空间，大大压缩了存储空间。

        而SecureFiles技术还涉及到以下内容：

（1）新的网络协议

       SecureFiles提供一种新的Client/Server（C/S）方式的内部读写机制，有效提高了大量数据传输的效率。

（2）简化物理属性设计和管理

      SecureFiles提供了大量自动化的物理属性机制，免去了大量物理属性设计和管理工作。例如：CHUNK属性为可变长，最大能支持到64M；Oracle能自动进行碎片整理等。

（3）自动管理redo和undo

      SecureFiles还可以自动进行redo和undo的管理，避免大量不必要的redo和 undo信息的产生。

        由上可知，该技术在性能、可管理性、易用性等方面，具有如下具体特点和优势：

（1）提供数据去重、压缩和透明加密功能

（2）SecureFiles不仅可以有效降低LOB字段存储空间消耗，提高访问效率，而且提高了LOB字段的数据安全性。

小结：oracle在大对象存储压缩上，比pg做的稍好一些。