【PostgreSQL的变长字段数据超过多少会写入到TOSAST表】

通常PostgreSQL里如果一个元祖的变长字段的数据量，超过2KB，则PostgreSQL会尝试进行压缩，把元组控制在2KB之内，如果不能满足2KB之内的需求，就需要独立的toast表来存储了。

一、toast表相关的列存储参数

这个是否尝试压缩是受列上的Storage这一参数决定的，如果想要用toast但是又不想压缩，可以把列的storage参数设置为external。

postgres=# \d+ test_toast 
                                        Table "public.test_toast"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer |           |          |         | plain    |             |              | 
 name   | text    |           |          |         | extended |             |              | 
Access method: heap

这个列的存储策略有如下四个可供调整的值。

plain：该列仅存储在堆中且未压缩。
extended：如有必要，可以压缩该列并将其存储在 toast 中。
external：该列可以存储在 toast 中，但不能压缩。有时，可以使用此模式以更高的磁盘空间消耗为代价来提高性能（避免压缩/解压缩）。
main：该列仅存储在堆中，但与普通模式不同，允许压缩。

对于某类字段的默认存储类型，pg_type的typstorage中有明确的定义

postgres=# select typname,typstorage from pg_type where typname in ('int4','text','');
 typname | typstorage 
---------+------------
 int4    | p
 text    | x
(2 rows)

可以用如下方式进行调整

postgres=# alter table test_toast alter column name set storage external;
ALTER TABLE
postgres=#  \d+ test_toast 
                                        Table "public.test_toast"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer |           |          |         | plain    |             |              | 
 name   | text    |           |          |         | external |             |              | 
Access method: heap

二、2KB的大小如何是如何计算的

这个2KB的阈值其实受源码里的TOAST_TUPLE_THRESHOLD限制的，

/*
 * These symbols control toaster activation.  If a tuple is larger than
 * TOAST_TUPLE_THRESHOLD, we will try to toast it down to no more than
 * TOAST_TUPLE_TARGET bytes through compressing compressible fields and
 * moving EXTENDED and EXTERNAL data out-of-line.
 *
 * The numbers need not be the same, though they currently are.  It doesn't
 * make sense for TARGET to exceed THRESHOLD, but it could be useful to make
 * it be smaller.
 *
 * Currently we choose both values to match the largest tuple size for which
 * TOAST_TUPLES_PER_PAGE tuples can fit on a heap page.
 *
 * XXX while these can be modified without initdb, some thought needs to be
 * given to needs_toast_table() in toasting.c before unleashing random
 * changes.  Also see LOBLKSIZE in large_object.h, which can *not* be
 * changed without initdb.
 */
#define TOAST_TUPLES_PER_PAGE	4
#define TOAST_TUPLE_THRESHOLD	MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)
#define TOAST_TUPLE_TARGET		TOAST_TUPLE_THRESHOLD

根据代码定义可以看到：

TOAST_TUPLE_THRESHOLD = MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)

而MaximumBytesPerTuple函数的定义如下：

/*
 * Find the maximum size of a tuple if there are to be N tuples per page.
 */
#define MaximumBytesPerTuple(tuplesPerPage) \
	MAXALIGN_DOWN((BLCKSZ - \
				   MAXALIGN(SizeOfPageHeaderData + (tuplesPerPage) * sizeof(ItemIdData))) \
				  / (tuplesPerPage))

在C语言代码里，我们有时会遇到一行代码太长而影响阅读或者与要求的编码规范不符的情况，此时需要将这行代码分成多行来写。在编译时，\后面的换行符将被忽略，当做一行处理。

所以

MaximumBytesPerTuple(tuplesPerPage) = MAXALIGN_DOWN((BLCKSZ - MAXALIGN(SizeOfPageHeaderData + (tuplesPerPage) * sizeof(ItemIdData))) / (tuplesPerPage))
TOAST_TUPLE_THRESHOLD 
= MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)
= MAXALIGN_DOWN((BLCKSZ - MAXALIGN(SizeOfPageHeaderData + (TOAST_TUPLES_PER_PAGE) * sizeof(ItemIdData))) / (TOAST_TUPLES_PER_PAGE))

而定义里TOAST_TUPLES_PER_PAGE=4，并且默认BLCKSZ = 8KB

PageHeaderData --在 page 头部，24 个字节长度，记录 page 的元数据信息。所以SizeOfPageHeaderData= 24 bytes

ItemIdData --在 page header 之后，一个记录（偏移量，长度）对的数组，指向实际 tuple 项，每个 4 字节。所以sizeof(ItemIdData)=4 bytes

所以上述的TOAST_TUPLE_THRESHOLD可以转换为下边

TOAST_TUPLE_THRESHOLD
= MAXALIGN_DOWN((BLCKSZ - MAXALIGN(24bytes  + 4 * 4bytes)) / 4）
= MAXALIGN_DOWN((8KB - MAXALIGN(24bytes + 4 * 4bytes)) / 4）
~=2KB

因此如果变长字段的storage参数为external，表字段很长存储超过2KB的时候，就会触发行外存储，把数据存储到toast表里。如果是extended则会尝试进行压缩，把元祖控制在2KB之内，如果不能满足2KB之内的需求，就需要独立的toast表来存储。

三、如何调整元祖存储到toast表的这个阈值

如果对于2KB的设置觉得不符合需求的话，可以修改源码中的TOAST_TUPLE_THRESHOLD定义，再编译。不过这种静态编译比较麻烦。

而针对这一问题，PostgreSQL11版本增加了一个特性，支持表级动态设TOAST_TUPLE_THRESHOLD，这样就不需要调整源码实现了，也更加灵活了。

调整的语法如下，单位是bytes

postgres=# alter table test_toast  set (toast_tuple_target = 4096);  
ALTER TABLE
postgres=# \d+ test_toast 
                                        Table "public.test_toast"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer |           |          |         | plain    |             |              | 
 name   | text    |           |          |         | external |             |              | 
Access method: heap
Options: toast_tuple_target=4096

本网站所收集的部分公开资料来源于AI生成和互联网，转载的目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。