pg toast

TOAST：The Oversized-Attribute Storage Technique 即行外存储

TOAST策略

PLAIN：不使用TOAST，即禁止压缩和行外存储
MAIN：优先压缩，尽量不用行外存储
EXTENDED：允许压缩和行外存储
EXTERNAL：只行外存储，不压缩

测试

建表

create  table t1 
(id int primary key,
a varchar(50),
b text);

查看表

\d+ t1

                                                   Table "public.t1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer               |           | not null |         | plain    |             |              |
 a      | character varying(50) |           |          |         | extended |             |              |
 b      | text                  |           |          |         | extended |             |              |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Access method: heap

修改字段存储类型

-- alter table t1 alter column b set storage plain;
-- alter table t1 alter column b set storage main;
alter table t1 alter column b set storage external;
-- alter table t1 alter column b set storage extended;

查看toast表

select relname,'pg_toast_'||relfilenode,reltoastrelid from pg_class where relname='t1';
或
SELECT relname FROM pg_class 
 WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE relname = 't1');

external测试

insert into t1 select 1,'a',repeat('b', 8);
insert into t1 select 2,'a',repeat('b', 16);
insert into t1 select 3,'a',repeat('b', 32);
insert into t1 select 4,'a',repeat('b', 64);
insert into t1 select 5,'a',repeat('b', 128);
insert into t1 select 6,'a',repeat('b', 256);
insert into t1 select 7,'a',repeat('b', 512);
insert into t1 select 8,'a',repeat('b', 1024);
insert into t1 select 9,'a',repeat('b', 1995);
insert into t1 select 10,'a',repeat('b', 1996);
select chunk_id,chunk_seq,length(chunk_data)  from pg_toast.pg_toast_39689;
insert into t1 select 11,'a',repeat('b', 1997);
insert into t1 select 12,'a',repeat('b', 1998);
insert into t1 select 13,'a',repeat('b', 1999);
insert into t1 select 14,'a',repeat('b', 2000);
insert into t1 select 15,'a',repeat('b', 2001);
insert into t1 select 16,'a',repeat('b', 2048);

extended 测试

insert into t1 select 16,'a',repeat('b', 2048);
insert into t1 select 17,'a',repeat('b', 4096);
insert into t1 select 18,'a',repeat('b', 8192);
insert into t1 select 19,'a',repeat('b', 16384);
insert into t1 select 20,'a',repeat('b', 32768);
insert into t1 select 21,'a',repeat('b', 65536);
insert into t1 select 22,'a',repeat('b', 131072);
insert into t1 select 23,'a',repeat('b', 262144);
select chunk_id,chunk_seq,length(chunk_data)  from pg_toast.pg_toast_39689;
 chunk_id | chunk_seq | length
----------+-----------+--------
    39753 |         0 |   1996
    39753 |         1 |   1013

external：数据超过2KB左右（可能略小于2K，测试显示超过1996则发生toast），就会启用行外存储

extended:先压缩，如果压缩后长度还超过2K（可能略小于2K，测试显示超过1996则发生toast），则将其移到TOAST表单独存储

toast优点：可以存储超长超大字段，物理上分离，非热字段toast可以加快访问

toast缺点：Toast字段的索引可能会失败，热字段toast势必存在更大的IO成本