关于PostgreSQL的 Toast

前端之家收集整理的这篇文章主要介绍了关于PostgreSQL的 Toast前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

当一行数据的长度超过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的细节:

https://www.qcloud.com/community/article/213

原文链接:https://www.f2er.com/postgresql/194021.html

猜你在找的Postgre SQL相关文章