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

标签

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》