当一行数据的长度超过4K or 8K(根据配置),就会使用Toast,没办法关闭。
Postgresqluses a fixed page size (commonly 8 kB),and does not allow tuples to span multiple pages.
也就是说,在安装postgresql的时候,就会指定block size,block size会指定page size的大小,当记录超过一个page size的时候,pg是不允许一行数据跨page存储,就会使用到toast。
SELECT a.attrelid,a.attname AS NAME,a.* FROM pg_class c,pg_attribute a WHERE a.attrelid = c.oid AND c.relname = 'dxy_web_event_test'; -- table name SELECT a.attrelid,a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod),(SELECT substring(pg_catalog.pg_get_expr(d.adbin,d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),a.attnotnull,a.attnum,(SELECT c.collname FROM pg_catalog.pg_collation c,pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,NULL AS indexdef,NULL AS attfdwoptions,a.attstorage,CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,pg_catalog.col_description(a.attrelid,a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;
p: Value must always be stored plain.
e: Value can be stored in a "secondary" relation (if relation has one,see pg_class.reltoastrelid).
m: Value can be stored compressed inline.
x: Value can be stored compressed inline or stored in "secondary" storage.
4种Toast策略:
- PLAIN:避免压缩和行外存储。只有那些不需要TOAST策略就能存放的数据类型允许选择(例如int类型),而对于text这类要求存储长度超过页大小的类型,是不允许采用此策略的
- EXTENDED:允许压缩和行外存储。一般会先压缩,如果还是太大,就会行外存储
- EXTERNA:允许行外存储,但不许压缩。类似字符串这种会对数据的一部分进行操作的字段,采用此策略可能获得更高的性能,因为不需要读取出整行数据再解压。
- MAIN:允许压缩,但不许行外存储。不过实际上,为了保证过大数据的存储,行外存储在其它方式(例如压缩)都无法满足需求的情况下,作为最后手段还是会被启动。因此理解为:尽量不使用行外存储更贴切。 现在我们通过实际操作来研究TOAST的细节: