暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

PostgreSQL 11 preview - TOAST切片阈值表级可配置

digoal 2018-05-19
652

作者

digoal

日期

2018-05-19

标签

PostgreSQL , toast阈值表级动态设置 , 增强 , 11


背景

还记得我在13年的时候写过几篇关于变长字段优化的CASE,如果变长字段不怎么被更新,建议放到TOAST存储,因为放TOAST后,TUPLE中就只存指向TOAST的指针了。

《PostgreSQL large row|column performance tuning case》

那么多大的数据会放到TOAST呢?之前是代码中TOAST_TUPLE_THRESHOLD常量控制的,大概是PAGE_SIZE/4, 8K的PAGE_SIZE,就是2K。

也就是说,只有变长字段(s)的值占用的空间超过2K(而且是指pglz压缩后超过2K),才会写入TOAST中。

那么问题就来了,如果变长字段的值都小于2K,并且又不怎么变化,是不是得在数据库安装前,修改一下源码中的TOAST_TUPLE_THRESHOLD定义,再编译。这种静态编译太麻烦了。不适合灵活的业务。

方法如下:

《HOW to Change PostgreSQL's TOAST_TUPLE_THRESHOLD》

PostgreSQL 11给出了一个很好的特性,支持表级动态设置TOAST_TUPLE_THRESHOLD,终于不用静态编译了。

Add storage parameter toast_tuple_target to control the minimum length before TOAST storage will be considered for new rows (Simon Riggs)

The default TOAST threshold has not been changed.

toast_tuple_target (integer)

The toast_tuple_target specifies the minimum tuple length required before we try to move long column values into TOAST tables, and is also the target length we try to reduce the length below once toasting begins. This only affects columns marked as either External or Extended and applies only to new tuples - there is no effect on existing rows. By default this parameter is set to allow at least 4 tuples per block, which with the default blocksize will be 2040 bytes. Valid values are between 128 bytes and the (blocksize - header), by default 8160 bytes. Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. This parameter cannot be set for TOAST tables.

语法如下:

将t_loc1表的TOAST阈值设置为128字节:

postgres=# alter table t_loc1 set (toast_tuple_target=128); ALTER TABLE

参考

《HOW to Change PostgreSQL's TOAST_TUPLE_THRESHOLD》

《PostgreSQL large row|column performance tuning case》

《how difference when update a table's column which it in TOAST or BASETABLE(in-line)》

《TOAST,The Oversized-Attribute Storage Technique》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

文章转载自digoal,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论