chunk_id :标识TOAST表的OID字段chunk_seq :chunk的序列号,与chunk_id的组合唯一索引可以加速访问chunk_data :存储TOAST表的实际数据Toast有识别4种不同可存储toast的策略:
--plain避免压缩或行外存储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--extended允许压缩和行外存储(默认toast存储)EXTENDED allows both compression and out-of-line storage. This is the default for most TOASTable data types. Compression will be attempted first, then out-of-line storage if the row is still too big--external允许行外但不允许压缩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允许压缩但不允许行外存储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上述压缩采用的是LZ compression技术,
ALTER TABLE table_name ALTER COLUMN column_name SET STORAGE {PLAIN | EXTENDED | MAIN | EXTERNAL};postgres=# \d+ t_kenyon Table "public.t_kenyon"Column | Type | Modifiers | Storage | Stats target | Description--------+-------------------+-----------+----------+--------------+-------------dd | character varying | | extended | |Has OIDs: nopostgres=# alter table t_kenyon alter column dd set storage main;ALTER TABLEpostgres=# \d+ t_kenyon Table "public.t_kenyon"Column | Type | Modifiers | Storage | Stats target | Description--------+-------------------+-----------+---------+--------------+-------------dd | character varying | | main | |Has OIDs: no
[postgres@localhost ~]$ psqlpsql (9.2.3)Type "help" for help.postgres=# create table t_kenyon(id int);CREATE TABLEpostgres=# select relname,reltoastrelid from pg_class where relname = 't_kenyon';relname | reltoastrelid----------+---------------t_kenyon | 0(1 row)postgres=# \d+ t_kenyon Table "public.t_kenyon"Column | Type | Modifiers | Storage | Stats target | Description--------+---------+-----------+---------+--------------+-------------id | integer | | plain | |Has OIDs: no上面的字段没有toast表,因为字段int是定长的。
postgres=# select relname,reltoastrelid from pg_class where relname = 't_kenyon';relname | reltoastrelid----------+---------------t_kenyon | 16411(1 row)postgres=# select relname from pg_class where oid = 16411; relname ----------------pg_toast_16408(1 row)2.TOAST表计算大小
postgres=# drop table t_kenyon;DROP TABLEpostgres=# create table t_kenyon(id int,vname varchar(48),remark text);CREATE TABLEpostgres=# \d+ t_kenyon Table "public.t_kenyon"Column | Type | Modifiers | Storage | Stats target | Description--------+-----------------------+-----------+----------+--------------+-------------id | integer | | plain | |vname | character varying(48) | | extended | |remark | text | | extended | |Has OIDs: nopostgres=# select oid,relname,reltoastrelid from pg_class where relname = 't_kenyon'; oid | relname | reltoastrelid--------+----------+---------------121174 | t_kenyon | 121177(1 row)postgres=# insert into t_kenyon select generate_series(1,2000),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God',500);INSERT 0 2000postgres=# insert into t_kenyon select generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',2000);INSERT 0 2postgres=# select pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from t_kenyon limit 2;pg_column_size | pg_column_size | pg_column_size----------------+----------------+---------------- 4 | 29 | 851 4 | 29 | 851(2 rows)--查看基础表和Toast的大小postgres=# select pg_relation_size(121174);pg_relation_size------------------ 8192(1 row)postgres=# select pg_relation_size(121177);pg_relation_size------------------ 0(1 row)--文本数据量增多,这时可以看到toast表字段大小在2kb左右时有大小了postgres=# insert into t_kenyon select generate_series(3,4),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',4000);INSERT 0 2postgres=# select pg_relation_size(121174);pg_relation_size------------------ 8192(1 row)postgres=# select pg_relation_size(121177);pg_relation_size------------------ 0(1 row)postgres=# insert into t_kenyon select generate_series(5,6),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',5500);INSERT 0 2postgres=# select pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from t_kenyon;pg_column_size | pg_column_size | pg_column_size----------------+----------------+---------------- 4 | 29 | 851 4 | 29 | 851 4 | 29 | 1651 4 | 29 | 1651 4 | 29 | 2247 4 | 29 | 2247(6 rows)postgres=# insert into t_kenyon select generate_series(1,2),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',10000);INSERT 0 2postgres=# select pg_relation_size(121174);pg_relation_size------------------ 8192(1 row)postgres=# select pg_relation_size(121177);pg_relation_size------------------ 16384(1 row)postgres=# insert into t_kenyon select generate_series(7,8),repeat('kenyon here'||'^_^',2),repeat('^_^ Kenyon is not God,Remark here!!',20000);INSERT 0 2postgres=# select id,pg_column_size(id),pg_column_size(vname),pg_column_size(remark) from t_kenyon;id | pg_column_size | pg_column_size | pg_column_size----+----------------+----------------+---------------- 1 | 4 | 29 | 851 2 | 4 | 29 | 851 3 | 4 | 29 | 1651 4 | 4 | 29 | 1651 5 | 4 | 29 | 2247 6 | 4 | 29 | 2247 7 | 4 | 29 | 8056 8 | 4 | 29 | 8056(8 rows)postgres=# select pg_relation_size(121174);pg_relation_size------------------ 8192(1 row)postgres=# select pg_relation_size(121177);pg_relation_size------------------ 24576(1 row)可以看到后插入的数据随着字段内容的增多,toast段一直在变大。这个和Oracle存储的大字段内容比较像,Oracle存储Blob类的数据时也是指定另外的segment来存储,而不是在原表中存储,当然可以设置enable storage in row来指定表中存储,所以Oracle里的表异常大时一般不是水位线过高就是表字段里存了大数据类型了。
联系客服