前言
一直想通过简单的实验来加强对TOAST表机制的理解。希望本文能做到。
1、环境准备:
建两张样表，分别添加些数据
mydb=# create table t1(id int primary key, col2 char(2050));
CREATE TABLE
mydb=# create table t2(id int primary key, col2 varchar(2050));
CREATE TABLE
mydb=# create index idx_t1_col2 on t1(col2);
CREATE INDEX
mydb=# create index idx_t2_col2 on t2(col2);
CREATE INDEX
mydb=# insert into t1 select n, 'test' || n from generate_series(1, 10) as n;
INSERT 0 10
mydb=# insert into t2 select n, 'test' || n from generate_series(1, 10) as n;
INSERT 0 10
找到对应的toast表：
mydb=# select oid, relname, reltoastrelid, (select relname from pg_class where oid=a.reltoastrelid) as toastname from pg_class a where relname in ('t1', 't2');
oid | relname | reltoastrelid | toastname
-------+---------+---------------+----------------
33008 | t1 | 33011 | pg_toast_33008
33015 | t2 | 33018 | pg_toast_33015
(2 rows)
mydb=# select count(*) from pg_toast.pg_toast_33008;
count
-------
0
(1 row)
mydb=# select count(*) from pg_toast.pg_toast_33015;
count
-------
0
(1 row)
2、四种存储格式
2.1、t1,t2的存储格式
mydb=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
col2 | character(2050) | | | | extended | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (id)
"idx_t1_col2" btree (col2)
Access method: heap
mydb=# \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
col2 | character varying(2050) | | | | extended | | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (id)
"idx_t2_col2" btree (col2)
Access method: heap
并不是所有的字段都可以使用TOAST来存储，在PostgreSQL中字段(field)类型有定长的如int4,int8等,也有变长的字段类型如varchar,text等，变长的数据类型最大可以存储1GB的数据，这个可以从PostgreSQL的源代码得到证实，变长字段最多可以选择使用32BIT的列头,预留2BIT作为FLAG，还有30BIT存储字段长度2^30刚好等于1GB。
对于定长的字段类型，存储格式如下：
PLAIN
prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.
对于变长的字段类型，除了可以使用PLAIN格式，还可以使用如下存储格式：
EXTENDED
allows both compression and out-of-line storage.
This is the default for most TOAST-able data types.
Compression will be attempted first, then out-of-line storage if the row is still too big.
EXTERNAL
allows out-of-line storage but not compression.
Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.
MAIN
allows compression but not out-of-line storage.
(Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)
mydb=# create table t(id int);
CREATE TABLE
select oid, relname, reltoastrelid, (select relname from pg_class where oid=a.reltoastrelid) as toastname from pg_class a where relname ='t';
oid | relname | reltoastrelid | toastname
-------+---------+---------------+-----------
33024 | t | 0 |
(1 row)
mydb=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
id | integer | | | | plain | | |
Access method: heap
表t只有定长类型int型字段，因而不会有对应的toast表，这个比较明显。而上边的t1, t2都有变长或者较长的字符串类型，所以有对应的TOAST表。
2.2、t2上的实例
看看当前t2表中的列的长度，都不到(TOAST_TUPLE_THRESHOLD 2KB)，所以不会用到toast表。t2表中的col2列使用的是extended的storage方式。
2.2.1、extended storage
extended方式：先尝试压缩，如果还是太长了，就进行线外存储。允许压缩，也允许线外。
mydb=# \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
col2 | character varying(2050) | | | | extended | | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (id)
"idx_t2_col2" btree (col2)
Access method: heap
mydb=# select pg_column_size(id),pg_column_size(col2) from t2;
pg_column_size | pg_column_size
----------------+----------------
4 | 6
4 | 6
4 | 6
4 | 6
4 | 6
4 | 6
4 | 6
4 | 6
4 | 6
4 | 7
(10 rows)
CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyz', round(random() * 25 + 0.5)::integer, 1), '') FROM generate_series(1, $1); $$ language sql;
mydb=# insert into t2 values(11, random_string(2048));
INSERT 0 1
mydb=# select pg_column_size(id),pg_column_size(col2) col2_size from t2 order by col2_size desc limit 2;
pg_column_size | col2_size
----------------+-----------
4 | 2010
4 | 7
(2 rows)
mydb=# select count(*) from pg_toast.pg_toast_33015;
count
-------
2
(1 row)
mydb=# \d+ pg_toast.pg_toast_33015
TOAST table "pg_toast.pg_toast_33015"
Column | Type | Storage
------------+---------+---------
chunk_id | oid | plain
chunk_seq | integer | plain
chunk_data | bytea | plain
Owning table: "public.t2"
Indexes:
"pg_toast_33015_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Access method: heap
mydb=# select tableoid, ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_33015;
tableoid | ctid | chunk_id | chunk_seq | length
----------+-------+----------+-----------+--------
33018 | (0,1) | 33028 | 0 | 1996
33018 | (0,2) | 33028 | 1 | 14
(2 rows)
两个加起来，刚好是列长: 2010.
2.2.2、关于压缩: 一个简单的模拟实验：
-- 将t2添加长数据，直到toast有值
drop index idx_t2_col2;
truncate table t2;
alter table t2 alter col2 type text;
mydb=# insert into t2 values(1, random_string(20));
INSERT 0 1
mydb=# update t2 set col2 = col2 || col2;
UPDATE 1
此后不断的update. 大概执行12次以后
mydb=# select length(col2) from t2;
length
--------
327680
(1 row)
另一边不断查询：
select ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_33015;
--直到有值
mydb=# select ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_33015;
ctid | chunk_id | chunk_seq | length
-------+----------+-----------+--------
(0,1) | 33068 | 0 | 1996
(0,2) | 33068 | 1 | 1784
(2 rows)
可以看到，压缩的很厉害：
327680的总长，压缩到只有: 1996 + 1784个Bytes.
mydb=# select pg_total_relation_size('t2') total, pg_table_size('t2') table_size, pg_relation_size('t2') relation_size, pg_indexes_size('t2') index_size, pg_total_relation_size('pg_toast.pg_toast_33015') toast_size;
total | table_size | relation_size | index_size | toast_size
-------+------------+---------------+------------+------------
49152 | 32768 | 8192 | 16384 | 24576
(1 row)
total: 49152 = 32768 + 16384 (table_size + index_size)
table_size: 32768 = 8192 + 24576 (relation_size + toast_size)
我们把t2中的storage方式改成：main或者external再试一下：
main: 只允许压缩
external: 只允许线外
如何查看类型的默认存储格式？
mydb=> select typname,typstorage from pg_type order by typname;
p : plain
m : main
e : external
x : extended
如何修改类型的存储格式？
alter table tbl_article alter column {$column_name} set storage { PLAIN | MAIN | EXTERNAL | EXTENDED } ;
2.2.3、main storage
alter table t2 alter column col2 set storage MAIN;
mydb=# truncate table t2;
TRUNCATE TABLE
mydb=# insert into t2 values(1, random_string(2048));
INSERT 0 1
mydb=# select tableoid, ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_33015;
tableoid | ctid | chunk_id | chunk_seq | length
----------+------+----------+-----------+--------
(0 rows)
mydb=# select pg_total_relation_size('pg_toast.pg_toast_33015');
pg_total_relation_size
------------------------
8192
(1 row)
mydb=# select id, length(col2) from t2;
id | length
----+--------
1 | 2016
(1 row)
mydb=# insert into t2 values(2, random_string(2049));
INSERT 0 1
mydb=# insert into t2 values(3, random_string(2049));
INSERT 0 1
mydb=# insert into t2 values(4, random_string(2049));
INSERT 0 1
mydb=# select pg_total_relation_size('pg_toast.pg_toast_33015');
pg_total_relation_size
------------------------
8192
(1 row)
mydb=# select pg_total_relation_size('t2');
pg_total_relation_size
------------------------
81920
(1 row)
mydb=# insert into t2 values(5, repeat('ab', 1020));
INSERT 0 1
mydb=# select id, length(col2) from t2;
id | length
----+--------
1 | 2016
2 | 2005
3 | 2013
4 | 2016
5 | 2040
(5 rows)
mydb=# select pg_total_relation_size('t2');
pg_total_relation_size
------------------------
98304
(1 row)
上边可以发现，它的内容不会出现在toast里边。
2.2.4、external storage
external 方式，允许线外存储，但是不允许压缩。
alter table t2 alter column col2 set storage EXTERNAL;
mydb=# select pg_total_relation_size('t2');
pg_total_relation_size
------------------------
98304
(1 row)
mydb=# select pg_total_relation_size('pg_toast.pg_toast_33015');
pg_total_relation_size
------------------------
8192
(1 row)
mydb=# truncate table t2;
TRUNCATE TABLE
mydb=# insert into t2 values(1, random_string(2049)), (2, random_string(2049)), (3, random_string(2049)), (4, random_string(2049)), (5, repeat('ab', 1020));
INSERT 0 5
mydb=# select pg_total_relation_size('pg_toast.pg_toast_33015');
pg_total_relation_size
------------------------
57344
(1 row)
mydb=# select pg_total_relation_size('t2');
pg_total_relation_size
------------------------
114688
(1 row)
mydb=# select pg_total_relation_size('t2') total, pg_table_size('pg_toast.pg_toast_33015') toast_size, pg_indexes_size('t2') index_size, pg_table_size('t2') table_size;
total | toast_size | index_size | table_size
--------+------------+------------+------------
114688 | 40960 | 49152 | 65536
(1 row)
mydb=# select pg_total_relation_size('t2') total, pg_table_size('pg_toast.pg_toast_33015') toast_size, pg_indexes_size('pg_toast.pg_toast_33015') toast_index_size, pg_indexes_size('t2') index_size, pg_table_size('t2') table_size;
total | toast_size | toast_index_size | index_size | table_size
--------+------------+------------------+------------+------------
114688 | 40960 | 16384 | 49152 | 65536
(1 row)
mydb=# select pg_total_relation_size('t2') total, pg_table_size('pg_toast.pg_toast_33015') toast_size, pg_indexes_size('pg_toast.pg_toast_33015') toast_index_size, pg_indexes_size('t2') index_size, pg_table_size('t2') table_size, pg_relation_size('t2') relation_size;
total | toast_size | toast_index_size | index_size | table_size | relation_size
--------+------------+------------------+------------+------------+---------------
114688 | 40960 | 16384 | 49152 | 65536 | 8192
(1 row)
table_size + index_size = total
table_size = relation_size + toast_size + toast_index_size;
mydb=# select tableoid, ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_33015;
tableoid | ctid | chunk_id | chunk_seq | length
----------+-------+----------+-----------+--------
33018 | (0,1) | 33039 | 0 | 1996
33018 | (0,2) | 33039 | 1 | 12
33018 | (0,3) | 33040 | 0 | 1996
33018 | (0,4) | 33040 | 1 | 30
33018 | (0,5) | 33041 | 0 | 1996
33018 | (0,6) | 33041 | 1 | 21
33018 | (1,1) | 33042 | 0 | 1996
33018 | (1,2) | 33042 | 1 | 44
(8 rows)
mydb=# insert into t2 values(6, repeat('ab', 1025));
INSERT 0 1
mydb=# select count(*) from t2;
count
-------
6
(1 row)
mydb=# select count(*) from pg_toast.pg_toast_33015;
count
-------
10
(1 row)
mydb=# select tableoid, ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_33015;
tableoid | ctid | chunk_id | chunk_seq | length
----------+-------+----------+-----------+--------
33018 | (0,1) | 33039 | 0 | 1996
33018 | (0,2) | 33039 | 1 | 12
33018 | (0,3) | 33040 | 0 | 1996
33018 | (0,4) | 33040 | 1 | 30
33018 | (0,5) | 33041 | 0 | 1996
33018 | (0,6) | 33041 | 1 | 21
33018 | (1,1) | 33042 | 0 | 1996
33018 | (1,2) | 33042 | 1 | 44
33018 | (1,3) | 33043 | 0 | 1996
33018 | (1,4) | 33043 | 1 | 54
(10 rows)
mydb=# select id, length(col2) from t2;
id | length
----+--------
1 | 2008
2 | 2026
3 | 2017
4 | 1991
5 | 2040
6 | 2050
(6 rows)
-- 仔细核对，会发现id=4的那条记录长度只有1991，所以不会放到toast表里头。
文章转载自数据库杂记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
