Postgresql之toast

如果一行太长一个页存不下时，pg使用了一个特殊的机制称作TOAST(The Oversized Attributes Storage Technique)。
TOAST使用了多种策略来存储超长的行，一是移动长属性到一个单独的表，二是压缩长属性使其存储在原地。或者可以同时使用两种策略。

• plain 没有使用toast
• extended 允许压缩，并存储到单独的toast表
• external 不压缩单独存储到toast表
• main 先压缩，如果压缩还存不下移动到toast表

一般情况下，一个数据页最少要存放4行数据，因此一行如果超过了4分之1的页大小，大约2000字节，就必须使用toast机制处理行，可以使用表级别的存储参数toast_tuple_target修改toast的阈值。
大致处理流程如下：

1. 首先处理设置了external和extended策略的属性，按照指定的策略执行。
2. 如果经过上面的处理行还不满足要求，按照external和extended策略处理剩余的属性到toast表。
3. 如果还是不满足要求，尝试使用main策略压缩属性，保持属性留在原页。
4. 如果还不满足要求，main属性会被移动到toast表。

可用如下sql修改列的存储策略

ALTER TABLE t ALTER COLUMN d SET STORAGE external;
复制

toast表位于单独的pg_toast模式中，不在search_path中，因此用快捷命令\dt+看不到toast表

示例：

mydb=# create table t2(c1 varchar(1000),c2 varchar(2500),c3 int);
CREATE TABLE
mydb=# \d t2
                          Table "myq.t2"
 Column |          Type           | Collation | Nullable | Default 
--------+-------------------------+-----------+----------+---------
 c1     | character varying(1000) |           |          | 
 c2     | character varying(2500) |           |          | 
 c3     | integer                 |           |          | 

#查询表的列存储策略
mydb=# 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 = 't2'::regclass AND attnum > 0;
 attname |     atttypid      | storage  
---------+-------------------+----------
 c1      | character varying | extended
 c2      | character varying | extended
 c3      | integer           | plain
(3 rows)


#查询表对应的toast表
SELECT relnamespace::regnamespace, relname
FROM pg_class
WHERE oid = (
SELECT reltoastrelid
FROM pg_class WHERE relname = 't2'
);
 relnamespace |    relname     
--------------+----------------
 pg_toast     | pg_toast_24629
(1 row)


#修改表t2的c2列存储策略为external
mydb=# ALTER TABLE t2 ALTER COLUMN c2 SET STORAGE external;
ALTER TABLE

#插入数据
mydb=# insert into t2 values(repeat('c1',500),repeat('abcdefghijklmnopqrstuvwxyz',90),12345);
INSERT 0 1

#查询toast表的内容
mydb=# 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_24629;
 chunk_id | chunk_seq | length |        ?column?         
----------+-----------+--------+-------------------------
    24634 |         0 |   1996 | abcdefghij...klmnopqrst
    24634 |         1 |    344 | uvwxyzabcd...qrstuvwxyz
(2 rows)

复制