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;

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

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

//往上面创建的实验表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'));
 

//继续插入数据，并查看数据
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'));

//继续插入更多的数据并查看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'));

（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做的稍好一些。