代码改变世界

PostgreSQL的TOAST表

  abce  阅读(39)  评论(0编辑  收藏  举报

TOAST 管理代码可识别四种不同的策略,用于在磁盘上存储可以被TOAST 的列。

仅当要存储在表中的行值宽于TOAST_TUPLE_THRESHOLD字节(通常为2kB)时,才会触发TOAST管理代码。TOAST代码将压缩和/或移动字段值out-of-line,直到行值短于TOAST_TUPLE_TARGET字节(通常也是2kB,可调)或者不能获得更多增益。在UPDATE操作期间,未更改字段的值通常保持原样; 因此,如果没有任何out-of-line值发生更改,则具有out-of-line值的行的更新不会产生任何TOAST成本。

策略

压缩

行外存储

说明

数据种类

extended

yes

yes

默认策略。

首先尝试压缩,然后在行仍然太大的情况下进行out-of-line存储。

默认用于text、json、jsonb、大对象和二进制对象

main

yes

no

支持压缩、不支持out-of-line存储。(实际上,仍然会为这些列执行out-of-line存储,但只有在没有其他方法使行足够小以适合页面时才作为最后的手段)

NUMERIC类型

external

no

yes

如果超出尺寸,就移到toast表且不压缩

这用于hyperloglog中的rollup表,以及已经压缩的自定义数据类型,例如自定义图像数据类型。

plain

no

no

数据总是存在main fork。如果元组超出页大小,就报错。也就是说不使用TOAST技术。

interger、boolean、timestamptz、以及其它小数据类型

 

如果想查看所有你所有的列、存储类型、数据类型,可以使用以下查询:

1
2
3
4
5
6
7
8
9
10
11
SELECT
   table_name,
   column_name,
   attstorage,
   atttypid::regtype
FROM
   information_schema.columns
   JOIN pg_attribute ON (
       columns.table_name = pg_attribute.attrelid::regclass::text
       AND columns.column_name = pg_attribute.attname
   );

TOAST 表位于一个名为 pg_toast 的单独模式中;它不包含在搜索路径中,因此 TOAST 表通常是隐藏的。对于临时表,类似于 pg_temp_N,使用的是 pg_toast_temp_N 模式。

 

查看列的TOAST策略

默认情况下,根据列的数据类型选择 TOAST 策略。如果想查看使用的是哪个TOAST策略,可以在psql中执行\d+命令:

1
2
3
4
5
6
7
8
9
10
abce=# create table t_toast(id int, title text, contents text);
CREATE TABLE
abce=# \d+ t_toast
                                          Table "public.t_toast"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id       | integer |           |          |         | plain    |             |              |
 title    | text    |           |          |         | extended |             |              |
 contents | text    |           |          |         | extended |             |              |
Access method: heap

或者借助系统目录来查看:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
abce=# SELECT attname, atttypid::regtype,
  CASE attstorage
    WHEN 'p' THEN 'plain'
    WHEN 'e' THEN 'external'
    WHEN 'm' THEN 'main'
    WHEN 'x' THEN 'extended'
  END AS storage
FROM pg_attribute
WHERE attrelid = 't_toast'::regclass AND attnum > 0;
 attname  | atttypid | storage 
----------+----------+----------
 id       | integer  | plain
 title    | text     | extended
 contents | text     | extended
(3 rows)

 

修改列的TOAST策略

可以使用关键字 set storage 来设置列的TOAST策略。

1
2
3
4
5
6
7
8
9
10
11
12
abce=# alter table t_toast alter column contents set storage external;
ALTER TABLE
abce=# \d+ t_toast
                                          Table "public.t_toast"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id       | integer |           |          |         | plain    |             |              |
 title    | text    |           |          |         | extended |             |              |
 contents | text    |           |          |         | external |             |              |
Access method: heap
 
abce=#

 

查看哪些表使用了TOAST

如果使用了text/varchar/jsonb/bytea类型,pg会预先创建一个TOAST表,防止用户后面插入大的数据值。因此,如果你四处查看,你会看到我们所有text表的 TOAST 表,如果它从未被使用过,它可能只有 0 页。

可以查看哪些原表具有TOAST表,以及TOAST表的大小:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
   c.relname AS source_table_name,
   c.relpages AS source_table_number_of_pages,
   c.reltuples AS source_table_number_of_tuples,
   c.reltoastrelid AS toast_table_oid,
   t.relname AS toast_table_name,
   t.relpages AS toast_table_number_of_pages,
   t.reltuples AS toast_table_number_of_tuples
FROM
   pg_class c
   JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE
   t.relpages > 0;

比如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
abce=# SELECT
   c.relname AS source_table_name,
   c.relpages AS source_table_number_of_pages,
   c.reltuples AS source_table_number_of_tuples,
   c.reltoastrelid AS toast_table_oid,
   t.relname AS toast_table_name,
   t.relpages AS toast_table_number_of_pages,
   t.reltuples AS toast_table_number_of_tuples
FROM
   pg_class c
   JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE
   t.relpages > 0;
 source_table_name | source_table_number_of_pages | source_table_number_of_tuples | toast_table_oid | toast_table_name | toast_table_number_of_pages | toast_table_number_of_tuples
-------------------+------------------------------+-------------------------------+-----------------+------------------+-----------------------------+------------------------------
 t_toast           |                            0 |                            -1 |           16409 | pg_toast_16406   |                         240 |                          482
 pg_statistic      |                           26 |                           410 |            2840 | pg_toast_2619    |                           3 |                           13
 pg_proc           |                           99 |                          3330 |            2836 | pg_toast_1255    |                           1 |                            3
 pg_rewrite        |                           14 |                           145 |            2838 | pg_toast_2618    |                          63 |                          278
(4 rows)
 
abce=#
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~
点击右上角即可分享
微信分享提示