什么是吐司？
你有没有想过 Postgres 如何存储超过块大小的行？提醒一下，默认块大小为 8KB。
Postgres 使用一种称为 TOAST 的机制来实现 超大属性存储技术。
当一条记录变得太大而无法存储在一个块中时，Postgres 会将它“放在一边”存储在一个 toast 表中。记录将被分割成块，因此主表（称为堆）将包含一个指针（chunk_id），指向 toast 表中的正确块。
该块将存储在多行中，对于一个 chunk_id，我们可以在此 toast 表中拥有多行。因此，这个 toast 表由 3 列组成：
- chunk_id：堆中引用的块的编号
- chunk_seq：一个块的每个段的数量
- chunk_data : 每个段的数据部分
实际情况要复杂一些，实际上 Postgres 会尽量避免将数据存储在 toast 表中。如果行超过TOAST_TUPLE_THRESHOLD（2Kb），它将尝试压缩列以尝试将行放入块中。更准确地说，大小必须小于TOAST_TUPLE_TARGET（默认为 2Kb，我们稍后会讨论）。
如果幸运的话，压缩线将适合堆。如果没有，它将尝试从最大到最小压缩列，并将它们存储在 toast 部分，直到剩余的列适合堆的一行。1
还要注意，如果压缩增益太小，它认为花费资源尝试压缩是没有用的。因此，它在不压缩的情况下存储数据。2
使用 meta 命令显示表的特征时，您是否注意过“Storage”列\d+ table？
stackoverflow=# \d+ posts
Table "public.posts"
Column | Type | Collation | Nullable | Default | Storage |
---------------+---------+-----------+----------+---------+----------+
id | integer | | not null | | plain |
posttypeid | integer | | not null | | plain |
score | integer | | | | plain |
viewcount | integer | | | | plain |
body | text | | | | extended |
在此示例中，该列采用值plain或extended。事实上，根据数据类型，有 4 个可能的值：
- plain：该列仅存储在堆中，没有压缩。
- 扩展：如果需要，可以压缩列并存储在 toast 中。
- external：该列可以存储在 toast 中，但只能不压缩。有时，此模式可用于提高性能（避免压缩/解压缩），但会消耗更多磁盘空间。
- main：该列仅存储在堆中，但与普通模式不同，允许压缩。
乍一看，我们可能认为优势主要在于有机会存储超过块大小的行并压缩数据以节省磁盘空间。
还有另一个好处：当更新一行时，如果“toasted”列没有被修改，Postgres 不需要更新 toast 表。这避免了必须解压缩和重新压缩 toast 并将所有这些内容写入事务日志。
我们将看到另一个优点是 Postgres 可以避免在不必要的情况下读取 toast。
JSONB 示例
为了研究这个，我们将使用 JSONB 类型。一般来说，我不推荐使用这种类型：
- 您失去了拥有架构的优势：
- 编写查询变得更加复杂
- 没有关于 json 字段的键的统计信息
- 当我们存储每一行的键时，存储效率的损失
- JSONB 没有部分更新。如果您更改密钥，则必须解除并烤整个JSONB
- No partial detoast：如果我们只想读取 JSONB 的一个键，我们将不得不detoast整个 JSONB 3
但是，JSON 可能有用的一些例外情况：
- 当我们不需要搜索多个字段并通过另一列检索 json 时。（我们不需要关于 json 键的统计信息）。
- 而且，当很难将 json 放入关系模式中时。有些情况会涉及到有很多列，其中大部分位于
NULL.
例如，为了存储产品特性，规范化版本意味着使用大量列，其中大部分是NULL. 假设您正在存储产品，电视将具有特定特征（屏幕类型、尺寸等）。洗衣机还具有其他特定特征（旋转速度、接受的重量……）。
因此，我们可以考虑使用“正常”列，包括模型、价格、参考等，以及包含所有特征的列。我们将通过引用访问该行，因此我们将恢复存储在 json 中的产品的所有特征。
我将通过将一些列移动到jsonb列（在本例中为 jsonfield 列）来重用 Stackoverflow 帖子表：
\d posts
Unlogged table "public.posts"
Column | Type | Collation | Nullable | Default
-----------------------+-----------------------------+-----------+----------+---------
id | integer | | not null |
posttypeid | integer | | not null |
acceptedanswerid | integer | | |
parentid | integer | | |
creationdate | timestamp without time zone | | not null |
score | integer | | |
viewcount | integer | | |
body | text | | |
owneruserid | integer | | |
lasteditoruserid | integer | | |
lasteditordisplayname | text | | |
lasteditdate | timestamp without time zone | | |
lastactivitydate | timestamp without time zone | | |
title | text | | |
tags | text | | |
answercount | integer | | |
commentcount | integer | | |
favoritecount | integer | | |
closeddate | timestamp without time zone | | |
communityowneddate | timestamp without time zone | | |
jsonfield | jsonb | | |
这是一个简单的聚合：
SELECT
avg(viewcount),
avg(answercount),
avg(commentcount),
avg(favoritecount)
FROM posts;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10265135.77..10265135.78 rows=1 width=128) (actual time=170221.557..170221.558 rows=1 loops=1)
Buffers: shared hit=1 read=9186137
I/O Timings: read=138022.290
-> Seq Scan on posts (cost=0.00..9725636.88 rows=53949888 width=16) (actual time=0.014..153665.913 rows=53949886 loops=1)
Buffers: shared hit=1 read=9186137
I/O Timings: read=138022.290
Planning Time: 0.240 ms
Execution Time: 170221.627 ms
(8 rows)
该查询读取 70 GB，执行大约需要 2 分 50 秒。
现在是相同的查询，但这次使用 json 中存在的键。
SELECT
avg((jsonfield ->> 'ViewCount')::int),
avg((jsonfield ->> 'AnswerCount')::int),
avg((jsonfield ->> 'CommentCount')::int),
avg((jsonfield ->> 'FavoriteCount')::int)
FROM posts;
QUERY PLAN
------------------------------------------------------------------------------
Aggregate (cost=11883632.41..11883632.42 rows=1 width=128)
(actual time=520917.028..520917.030 rows=1 loops=1)
Buffers: shared hit=241116554 read=13625756
-> Seq Scan on posts (cost=0.00..9725636.88 rows=53949888 width=570)
(actual time=0.972..70569.365 rows=53949886 loops=1)
Buffers: shared read=9186138
Planning Time: 0.118 ms
Execution Time: 520945.395 ms
(10 rows)
查询执行大约需要 8 分 40 秒。但是，读取的块数似乎有点疯狂：
Seq Scan 和以前一样指示 70Gb。但是，父节点指示读取超过 1.9 TB！
这是默认设置的表格大小。您应该知道，对于某些记录，Postgres 将压缩堆中的行（内联压缩）或将其压缩并将其放置在 toast 中。
SELECT
pg_size_pretty(pg_relation_size(oid)) table_size,
pg_size_pretty(pg_relation_size(reltoastrelid)) toast_size
FROM pg_class
WHERE relname = 'posts';
table_size | toast_size
------------+-----------
70 GB | 33 GB
(1 row)
如何解释 1.9 TB 读取？
出于好奇，我进行了相同的查询，但使用单个聚合，我得到了大约 538 GB。
有几个问题：
- 我怎么知道 Postgres 是否会读取 toast？
- 为什么“标准列”版本和 jsonb 字段之间的执行时间差异如此之大？
- 节点中的计数器
Aggregate对应什么？
要回答第一个问题，只需阅读pg_statio_user_tables视图。
在执行查询之前：
select relid,schemaname,relname,heap_blks_read,heap_blks_hit,toast_blks_read,toast_blks_hit from pg_statio_all_tables where relname in ('posts','pg_toast_26180851');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | toast_blks_read | toast_blks_hit
----------+------------+-------------------+----------------+---------------+-----------------+----------------
26180851 | public | posts | 422018238 | 87673549 | 129785076 | 628153337
26180854 | pg_toast | pg_toast_26180851 | 129785076 | 628153337 | |
(2 rows)
后 ：
relid | schemaname | relname | heap_blks_read | heap_blks_hit | toast_blks_read | toast_blks_hit
----------+------------+-------------------+----------------+---------------+-----------------+----------------
26180851 | public | posts | 431204376 | 87673549 | 134156898 | 686299551
26180854 | pg_toast | pg_toast_26180851 | 134156898 | 686299551 | |
(2 rows)
这给了我们：
SELECT
pg_size_pretty(
((431204376 + 87673549) - (422018238 + 87673549) ) * 8*1024::bigint
) heap_buffers,
pg_size_pretty(
((134156898 + 686299551) - (129785076 + 628153337) ) * 8*1024::bigint
) toast_buffers;
heap_buffers | toast_buffers
--------------+---------------
70 GB | 477 GB
(1 row)
Postgres 读取吐司。但是，计数器表明 Postgres 会多次读取 toast。
如果我做同样的计算，但这次只在一个字段上聚合，我会得到 119 GB（~ 477 GB / 4）我猜 Postgres 会读取每个函数的 toast。
那么，执行时间的差异是由于以下几个原因：
- Postgres 必须读取并解压（解压缩）吐司
- 对 jsonb 执行附加操作以访问该值
对于第一个查询，Postgres 不必读取 toast。一方面，它要读取的数据较少，另一方面，它不必操作 json 来识别键并提取要计算的值。
最后，聚合节点的计数器必须与将读取 json 的每个函数的解压缩数据相对应。事实上，如果我们将聚合减去表的seqscan，得到toast部分，我们有：
- 单个字段 468 GB
- 936 GB，两个字段翻倍
- 4 个字段 1873 GB（大约 4 x 468 GB）
这就解释了为什么价值如此之高。
高级设置
现在，我们将鼓励 Postgres 将最大量的数据放入 toast，这要归功于 Postgres 版本 11 中引入的toast_tuple_target选项。
此选项允许您控制数据在toast中存储的阈值。
此外，在 Postgres 14 下，我借此机会使用了 lz4 压缩算法（参数default_toast_compression）。该算法提供与 pglz 相似的压缩率，但速度更快（请参阅 PostgreSQL 14 中新的 LZ4 TOAST 压缩是什么，速度有多快？）。
CREATE TABLE posts_toast
WITH (toast_tuple_target = 128) AS
SELECT *
FROM posts;
这是桌子和吐司表的大小：
SELECT
pg_size_pretty(pg_relation_size(oid)) table_size,
pg_size_pretty(pg_relation_size(reltoastrelid)) toast_size
FROM pg_class
WHERE relname = 'posts_toast';
table_size | toast_size
------------+------------
59 GB | 52 GB
总的来说，放着吐司的桌子大小大致相同。在第一个表的示例中，您应该记住引擎还会压缩堆中的数据。
让我们再次播放我们的聚合查询：
SELECT
avg(viewcount),
avg(answercount),
avg(commentcount),
avg(favoritecount)
FROM posts_toast;
这次查询读取 59 GB 并且需要 2 分 17 秒。在这个例子中，我们节省了大约 20% 的执行时间。
如果存储在吐司中的部分更大，我们可以节省更多。在堆中读取的数据量会小得多。
出于好奇，我还执行了聚合来自 json 字段的数据的查询。我得到7分17秒的执行时间。
结论
总结几个数字：
- 标准聚合，标准存储：2min 50s
- 聚合类型jsonb，标准存储：8min 40s
- 标准聚合，存储toast_tuple_target = 128 : 2min 17s
- 聚合类型 jsonb，存储toast_tuple_target = 128 : 7min 17s
我们可以看到使用 JSON 比使用标准类型要昂贵得多。Postgres 必须执行更多操作才能访问 json 键的值。
此外，它必须解压缩 toast 中的数据才能访问它。但是，我们也可以使用toast_tuple_target参数来在 toast 中推送更多信息。因此，在某些情况下，这可以通过避免读取 toast 来减少读取的数据量。
奖金
在 Postgres 中，一切都随着每个版本而发展。TOAST 没有逃避这个规则。因此，一些新功能可能会出现在下一个版本中：
- 第一个补丁被提议有更多关于 toast 的统计信息： pg_stat_toast。这个想法是对压缩进行统计：压缩增益，在 toast 中的内联或单独存储......
- 第二个补丁叫做 Pluggable toaster。这一项更为重要。它建议延长“烤面包机”。这个想法是能够根据类型（尤其是 JSONB）提出不同的“烤面包机” 。
